<?php
declare(strict_types=1);

namespace App\Modules\Subscriptions\Services;

use App\Core\App;
use App\Core\Database;
use App\Core\Logger;

final class SubscriptionDataMigration
{
    private Database $db;
    private string $currentFY = '2026/2027';
    private int $currentFYStart = 2026;
    private int $firstFYStart = 2023; // Subscription system started 2023/2024
    private string $ts;

    // Base rates per year (before discount)
    private array $rates = [
        '2023/2024' => ['member' => '410.00', 'spouse' => '410.00', 'child' => '185.00', 'temporary' => '185.00'],
        '2024/2025' => ['member' => '410.00', 'spouse' => '410.00', 'child' => '185.00', 'temporary' => '185.00'],
    ];
    private array $defaultRates = ['member' => '492.00', 'spouse' => '492.00', 'child' => '222.00', 'temporary' => '222.00'];

    // Year-specific discounts (percentage applied to base rate)
    private array $yearDiscounts = [
        '2023/2024' => '50', // 50% discount
    ];

    private string $devFee = '35.00';

    private array $stats = [
        'members_processed' => 0,
        'pre_2023_deleted' => 0,
        'subscriptions_created' => 0,
        'orphaned_ids_fixed' => 0,
        'duplicates_removed' => 0,
        'amount_corrections' => 0,
        'discount_corrections' => 0,
        'fines_applied' => 0,
        'errors' => [],
    ];

    public function __construct(Database $db)
    {
        $this->db = $db;
        $this->ts = date('Y-m-d H:i:s');
    }

    public function run(bool $dryRun = false): array
    {
        Logger::info("SubscriptionDataMigration started", ['dry_run' => $dryRun]);

        // Phase 0: Remove subscriptions before the system start (2023/2024)
        $this->removePreSystemSubscriptions($dryRun);

        // Phase 1: Fix orphaned person_id references
        $this->fixOrphanedPersonIds($dryRun);

        // Phase 2: Remove exact duplicates
        $this->removeDuplicates($dryRun);

        // Phase 3: Fix incorrect amounts and apply 2023/2024 discount
        $this->fixAmountsAndDiscounts($dryRun);

        // Phase 4: Create missing subscriptions for all members and dependents
        $this->createMissingSubscriptions($dryRun);

        // Phase 5: Reset and reapply overdue fines
        if (!$dryRun) {
            $this->resetAndApplyFines();
        }

        Logger::info("SubscriptionDataMigration completed", $this->stats);
        return $this->stats;
    }

    private function removePreSystemSubscriptions(bool $dryRun): void
    {
        // Count rows before first FY
        $count = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM subscriptions WHERE financial_year < ?",
            [$this->firstFYStart . '/' . ($this->firstFYStart + 1)]
        );
        $cnt = (int)($count['cnt'] ?? 0);

        if ($cnt > 0 && !$dryRun) {
            // Only delete unpaid pre-system subscriptions; keep paid ones as historical record
            $this->db->query(
                "DELETE FROM subscriptions WHERE financial_year < ? AND status != 'paid'",
                [$this->firstFYStart . '/' . ($this->firstFYStart + 1)]
            );
            $deletedCount = $this->db->selectOne(
                "SELECT ROW_COUNT() as cnt"
            );
            $this->stats['pre_2023_deleted'] = (int)($deletedCount['cnt'] ?? $cnt);
        } else {
            $unpaid = $this->db->selectOne(
                "SELECT COUNT(*) as cnt FROM subscriptions WHERE financial_year < ? AND status != 'paid'",
                [$this->firstFYStart . '/' . ($this->firstFYStart + 1)]
            );
            $this->stats['pre_2023_deleted'] = (int)($unpaid['cnt'] ?? 0);
        }
    }

    private function fixOrphanedPersonIds(bool $dryRun): void
    {
        $types = [
            'spouse' => 'spouses',
            'child' => 'children',
            'temporary' => 'temporary_members',
        ];

        foreach ($types as $personType => $table) {
            $orphaned = $this->db->select(
                "SELECT s.id, s.member_id, s.person_id, s.person_name, s.financial_year
                 FROM subscriptions s
                 WHERE s.person_type = ?
                 AND NOT EXISTS (SELECT 1 FROM {$table} t WHERE t.id = s.person_id AND t.member_id = s.member_id)",
                [$personType]
            );

            foreach ($orphaned as $row) {
                // Match by name (includes archived records)
                $match = $this->db->selectOne(
                    "SELECT id FROM {$table} WHERE member_id = ? AND full_name_ar = ? ORDER BY is_archived ASC LIMIT 1",
                    [(int)$row['member_id'], $row['person_name']]
                );

                if (!$match) {
                    // Get first unused dependent of this type for this member+FY
                    $existingIds = $this->db->select(
                        "SELECT DISTINCT person_id FROM subscriptions
                         WHERE member_id = ? AND financial_year = ? AND person_type = ? AND id != ?",
                        [(int)$row['member_id'], $row['financial_year'], $personType, (int)$row['id']]
                    );
                    $usedIds = array_column($existingIds, 'person_id');

                    $match = $this->db->selectOne(
                        "SELECT id FROM {$table} WHERE member_id = ? AND id NOT IN (" .
                        (empty($usedIds) ? '0' : implode(',', array_map('intval', $usedIds))) . ") LIMIT 1",
                        [(int)$row['member_id']]
                    );
                }

                if ($match && !$dryRun) {
                    $this->db->update('subscriptions',
                        ['person_id' => (int)$match['id'], 'updated_at' => $this->ts],
                        'id = ?', [(int)$row['id']]
                    );
                }
                if ($match) {
                    $this->stats['orphaned_ids_fixed']++;
                } else {
                    // No matching dependent exists — delete orphan subscription
                    if (!$dryRun) {
                        $this->db->query("DELETE FROM subscriptions WHERE id = ?", [(int)$row['id']]);
                    }
                    $this->stats['orphaned_ids_fixed']++;
                }
            }
        }
    }

    private function removeDuplicates(bool $dryRun): void
    {
        $duplicates = $this->db->select(
            "SELECT member_id, financial_year, person_type, person_id, COUNT(*) as cnt, MIN(id) as keep_id
             FROM subscriptions
             GROUP BY member_id, financial_year, person_type, person_id
             HAVING cnt > 1"
        );

        foreach ($duplicates as $dup) {
            if (!$dryRun) {
                $this->db->query(
                    "DELETE FROM subscriptions
                     WHERE member_id = ? AND financial_year = ? AND person_type = ? AND person_id = ? AND id != ?",
                    [(int)$dup['member_id'], $dup['financial_year'], $dup['person_type'], (int)$dup['person_id'], (int)$dup['keep_id']]
                );
            }
            $this->stats['duplicates_removed'] += ((int)$dup['cnt'] - 1);
        }
    }

    private function fixAmountsAndDiscounts(bool $dryRun): void
    {
        // For each FY, ensure correct base_amount, discount_amount, and total_amount
        for ($year = $this->firstFYStart; $year <= $this->currentFYStart; $year++) {
            $fy = $year . '/' . ($year + 1);
            $discountPct = $this->yearDiscounts[$fy] ?? null;

            foreach (['member', 'spouse', 'child', 'temporary'] as $type) {
                $baseRate = $this->getRateForYear($fy, $type);
                $discount = $discountPct ? bcdiv(bcmul($baseRate, $discountPct, 4), '100', 2) : '0.00';
                $totalAmount = bcsub($baseRate, $discount, 2);

                // Find rows with wrong base, discount, or total (skip paid rows to preserve payment history)
                $wrong = $this->db->select(
                    "SELECT id, base_amount, discount_amount, total_amount, status FROM subscriptions
                     WHERE financial_year = ? AND person_type = ? AND status != 'paid'
                     AND (base_amount != ? OR discount_amount != ? OR total_amount != ?)",
                    [$fy, $type, $baseRate, $discount, $totalAmount]
                );

                foreach ($wrong as $w) {
                    if (!$dryRun) {
                        $this->db->update('subscriptions', [
                            'base_amount' => $baseRate,
                            'discount_amount' => $discount,
                            'total_amount' => $totalAmount,
                            'updated_at' => $this->ts,
                        ], 'id = ?', [(int)$w['id']]);
                    }
                    if (bccomp($w['discount_amount'], $discount, 2) !== 0) {
                        $this->stats['discount_corrections']++;
                    } else {
                        $this->stats['amount_corrections']++;
                    }
                }
            }
        }
    }

    private function createMissingSubscriptions(bool $dryRun): void
    {
        $members = $this->db->select(
            "SELECT id, full_name_ar, created_at, membership_type
             FROM members WHERE status = 'active' AND is_archived = 0 AND membership_type NOT IN ('honorary', 'seasonal')"
        );

        foreach ($members as $member) {
            $memberId = (int)$member['id'];
            $memberJoinFY = max($this->dateToFY($member['created_at']), $this->firstFYStart);
            $this->stats['members_processed']++;

            // Member subscription
            $this->ensureSubscriptionsExist(
                $memberId, 'member', $memberId, $member['full_name_ar'],
                $memberJoinFY, $dryRun
            );

            // Spouses
            $spouses = $this->db->select(
                "SELECT id, full_name_ar, COALESCE(join_date, created_at) as effective_date
                 FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                [$memberId]
            );
            foreach ($spouses as $sp) {
                $startFY = max($this->dateToFY($sp['effective_date']), $memberJoinFY);
                $this->ensureSubscriptionsExist(
                    $memberId, 'spouse', (int)$sp['id'], $sp['full_name_ar'],
                    $startFY, $dryRun
                );
            }

            // Children
            $children = $this->db->select(
                "SELECT id, full_name_ar, COALESCE(join_date, created_at) as effective_date
                 FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                [$memberId]
            );
            foreach ($children as $ch) {
                $startFY = max($this->dateToFY($ch['effective_date']), $memberJoinFY);
                $this->ensureSubscriptionsExist(
                    $memberId, 'child', (int)$ch['id'], $ch['full_name_ar'],
                    $startFY, $dryRun
                );
            }

            // Temporary members
            $temps = $this->db->select(
                "SELECT id, full_name_ar, COALESCE(join_date, created_at) as effective_date
                 FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                [$memberId]
            );
            foreach ($temps as $t) {
                $startFY = max($this->dateToFY($t['effective_date']), $memberJoinFY);
                $this->ensureSubscriptionsExist(
                    $memberId, 'temporary', (int)$t['id'], $t['full_name_ar'],
                    $startFY, $dryRun
                );
            }
        }
    }

    private function ensureSubscriptionsExist(
        int $memberId, string $personType, int $personId, string $personName,
        int $startFYYear, bool $dryRun
    ): void {
        // Never create before system start
        $startFYYear = max($startFYYear, $this->firstFYStart);

        for ($year = $startFYYear; $year <= $this->currentFYStart; $year++) {
            $fy = $year . '/' . ($year + 1);

            $existing = $this->db->selectOne(
                "SELECT id FROM subscriptions
                 WHERE member_id = ? AND financial_year = ? AND person_type = ? AND person_id = ?",
                [$memberId, $fy, $personType, $personId]
            );

            if ($existing) continue;

            $baseRate = $this->getRateForYear($fy, $personType);
            $discountPct = $this->yearDiscounts[$fy] ?? null;
            $discount = $discountPct ? bcdiv(bcmul($baseRate, $discountPct, 4), '100', 2) : '0.00';
            $totalAmount = bcsub($baseRate, $discount, 2);
            $devFee = ($personType === 'member') ? $this->devFee : '0.00';
            $status = ($year === $this->currentFYStart) ? 'pending' : 'overdue';

            if (!$dryRun) {
                $this->db->insert('subscriptions', [
                    'member_id' => $memberId,
                    'financial_year' => $fy,
                    'person_type' => $personType,
                    'person_id' => $personId,
                    'person_name' => $personName,
                    'base_amount' => $baseRate,
                    'development_fee' => $devFee,
                    'discount_amount' => $discount,
                    'total_amount' => $totalAmount,
                    'paid_amount' => '0.00',
                    'fine_amount' => '0.00',
                    'status' => $status,
                    'created_at' => $this->ts,
                    'updated_at' => $this->ts,
                    'created_by' => null,
                ]);
            }
            $this->stats['subscriptions_created']++;
        }
    }

    private function resetAndApplyFines(): void
    {
        // Reset all fines first (recalculate from scratch)
        $this->db->query(
            "UPDATE subscriptions SET fine_amount = '0.00', updated_at = ? WHERE status = 'overdue'",
            [$this->ts]
        );

        $app = App::getInstance();
        if (!$app->db()) {
            $app->setDb($this->db);
        }

        $members = $this->db->select(
            "SELECT DISTINCT member_id FROM subscriptions WHERE status = 'overdue'"
        );

        foreach ($members as $row) {
            $memberId = (int)$row['member_id'];
            try {
                $calc = SubscriptionCalculator::calculateLateFine($memberId, $this->currentFY);

                foreach ($calc['details'] as $detail) {
                    if (($detail['in_grace'] ?? false)) continue;
                    $fineAmount = $detail['fine_amount'] ?? '0.00';
                    if (bccomp($fineAmount, '0', 2) <= 0) continue;

                    $fyRows = $this->db->select(
                        "SELECT id, total_amount FROM subscriptions
                         WHERE member_id = ? AND financial_year = ? AND status = 'overdue'",
                        [$memberId, $detail['financial_year']]
                    );

                    $fyTotal = '0.00';
                    foreach ($fyRows as $fr) {
                        $fyTotal = bcadd($fyTotal, $fr['total_amount'], 2);
                    }
                    if (bccomp($fyTotal, '0', 2) <= 0) continue;

                    foreach ($fyRows as $fr) {
                        $proportion = bcdiv($fr['total_amount'], $fyTotal, 6);
                        $rowFine = bcmul($fineAmount, $proportion, 2);
                        $this->db->update('subscriptions', [
                            'fine_amount' => $rowFine,
                            'updated_at' => $this->ts,
                        ], 'id = ?', [(int)$fr['id']]);
                    }
                    $this->stats['fines_applied']++;
                }
            } catch (\Throwable $e) {
                $this->stats['errors'][] = "Fine calc failed for member {$memberId}: {$e->getMessage()}";
            }
        }
    }

    private function getRateForYear(string $fy, string $personType): string
    {
        if (isset($this->rates[$fy][$personType])) {
            return $this->rates[$fy][$personType];
        }
        return $this->defaultRates[$personType] ?? '0.00';
    }

    private function dateToFY(string $date): int
    {
        $ts = strtotime($date);
        $month = (int)date('n', $ts);
        $year = (int)date('Y', $ts);
        return ($month >= 7) ? $year : ($year - 1);
    }

    public function validate(): array
    {
        $issues = [];

        // Check 1: No subscriptions before system start
        $preSys = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM subscriptions WHERE financial_year < ? AND status != 'paid'",
            [$this->firstFYStart . '/' . ($this->firstFYStart + 1)]
        );
        if ((int)($preSys['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'pre_system_subscriptions', 'count' => (int)$preSys['cnt']];
        }

        // Check 2: 2023/2024 discount applied correctly
        $noDiscount = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM subscriptions
             WHERE financial_year = '2023/2024' AND discount_amount = '0.00' AND status != 'paid' AND status != 'exempt'"
        );
        if ((int)($noDiscount['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'missing_2023_discount', 'count' => (int)$noDiscount['cnt']];
        }

        // Check 3: Members without subscriptions for current FY
        $missing = $this->db->select(
            "SELECT m.id, m.membership_number
             FROM members m
             WHERE m.status = 'active' AND m.is_archived = 0
             AND m.membership_type NOT IN ('honorary', 'seasonal')
             AND NOT EXISTS (
                 SELECT 1 FROM subscriptions s WHERE s.member_id = m.id AND s.financial_year = ?
             )",
            [$this->currentFY]
        );
        if (!empty($missing)) {
            $issues[] = ['type' => 'missing_current_fy_members', 'count' => count($missing)];
        }

        // Check 4: Active spouses without subscription in current FY
        $missingSpouses = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM spouses sp
             JOIN members m ON m.id = sp.member_id AND m.status = 'active' AND m.is_archived = 0
                AND m.membership_type NOT IN ('honorary','seasonal')
             WHERE sp.is_archived = 0 AND sp.status = 'active'
             AND NOT EXISTS (
                 SELECT 1 FROM subscriptions s
                 WHERE s.member_id = sp.member_id AND s.financial_year = '{$this->currentFY}'
                 AND s.person_type = 'spouse' AND s.person_id = sp.id
             )"
        );
        if ((int)($missingSpouses['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'missing_spouse_subs', 'count' => (int)$missingSpouses['cnt']];
        }

        // Check 5: Active children without subscription in current FY
        $missingChildren = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM children ch
             JOIN members m ON m.id = ch.member_id AND m.status = 'active' AND m.is_archived = 0
                AND m.membership_type NOT IN ('honorary','seasonal')
             WHERE ch.is_archived = 0 AND ch.status = 'active'
             AND NOT EXISTS (
                 SELECT 1 FROM subscriptions s
                 WHERE s.member_id = ch.member_id AND s.financial_year = '{$this->currentFY}'
                 AND s.person_type = 'child' AND s.person_id = ch.id
             )"
        );
        if ((int)($missingChildren['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'missing_child_subs', 'count' => (int)$missingChildren['cnt']];
        }

        // Check 6: Duplicates
        $dupes = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM (
                SELECT member_id, financial_year, person_type, person_id
                FROM subscriptions GROUP BY member_id, financial_year, person_type, person_id HAVING COUNT(*) > 1
             ) d"
        );
        if ((int)($dupes['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'duplicates', 'count' => (int)$dupes['cnt']];
        }

        // Check 7: Orphaned person_ids
        $orphans = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM subscriptions s WHERE
             (s.person_type = 'spouse' AND NOT EXISTS (SELECT 1 FROM spouses WHERE id = s.person_id AND member_id = s.member_id))
             OR (s.person_type = 'child' AND NOT EXISTS (SELECT 1 FROM children WHERE id = s.person_id AND member_id = s.member_id))
             OR (s.person_type = 'temporary' AND NOT EXISTS (SELECT 1 FROM temporary_members WHERE id = s.person_id AND member_id = s.member_id))"
        );
        if ((int)($orphans['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'orphaned_person_ids', 'count' => (int)$orphans['cnt']];
        }

        // Check 8: Zero-amount non-exempt subscriptions
        $zeros = $this->db->selectOne(
            "SELECT COUNT(*) as cnt FROM subscriptions WHERE base_amount = '0.00' AND status NOT IN ('exempt','paid')"
        );
        if ((int)($zeros['cnt'] ?? 0) > 0) {
            $issues[] = ['type' => 'zero_amounts', 'count' => (int)$zeros['cnt']];
        }

        return $issues;
    }
}
