<?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 string $ts;

    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'];
    private string $devFee = '35.00';

    private array $stats = [
        'members_processed' => 0,
        'subscriptions_created' => 0,
        'orphaned_ids_fixed' => 0,
        'duplicates_removed' => 0,
        'amount_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 1: Fix orphaned person_id references
        $this->fixOrphanedPersonIds($dryRun);

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

        // Phase 3: Fix incorrect amounts
        $this->fixIncorrectAmounts($dryRun);

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

        // Phase 5: Apply overdue fines
        if (!$dryRun) {
            $this->applyFines();
        }

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

    private function fixOrphanedPersonIds(bool $dryRun): void
    {
        // Find spouse subscriptions where person_id doesn't match any spouse for that member
        $orphanedSpouses = $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 = 'spouse'
             AND s.person_id NOT IN (
                 SELECT sp.id FROM spouses sp WHERE sp.member_id = s.member_id
             )"
        );

        foreach ($orphanedSpouses as $os) {
            // Try to match by name (check active first, then archived)
            $match = $this->db->selectOne(
                "SELECT id FROM spouses WHERE member_id = ? AND full_name_ar = ? ORDER BY is_archived ASC LIMIT 1",
                [(int)$os['member_id'], $os['person_name']]
            );
            if (!$match) {
                // Try partial match or just get first unmatched spouse
                $existingIds = $this->db->select(
                    "SELECT DISTINCT person_id FROM subscriptions
                     WHERE member_id = ? AND financial_year = ? AND person_type = 'spouse' AND id != ?",
                    [(int)$os['member_id'], $os['financial_year'], (int)$os['id']]
                );
                $usedIds = array_column($existingIds, 'person_id');

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

            if ($match && !$dryRun) {
                $this->db->update('subscriptions',
                    ['person_id' => (int)$match['id'], 'updated_at' => $this->ts],
                    'id = ?', [(int)$os['id']]
                );
                $this->stats['orphaned_ids_fixed']++;
            } elseif ($match) {
                $this->stats['orphaned_ids_fixed']++;
            }
        }

        // Same for children
        $orphanedChildren = $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 = 'child'
             AND s.person_id NOT IN (
                 SELECT ch.id FROM children ch WHERE ch.member_id = s.member_id
             )"
        );

        foreach ($orphanedChildren as $oc) {
            $match = $this->db->selectOne(
                "SELECT id FROM children WHERE member_id = ? AND full_name_ar = ? ORDER BY is_archived ASC LIMIT 1",
                [(int)$oc['member_id'], $oc['person_name']]
            );
            if (!$match) {
                $existingIds = $this->db->select(
                    "SELECT DISTINCT person_id FROM subscriptions
                     WHERE member_id = ? AND financial_year = ? AND person_type = 'child' AND id != ?",
                    [(int)$oc['member_id'], $oc['financial_year'], (int)$oc['id']]
                );
                $usedIds = array_column($existingIds, 'person_id');

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

            if ($match && !$dryRun) {
                $this->db->update('subscriptions',
                    ['person_id' => (int)$match['id'], 'updated_at' => $this->ts],
                    'id = ?', [(int)$oc['id']]
                );
                $this->stats['orphaned_ids_fixed']++;
            } elseif ($match) {
                $this->stats['orphaned_ids_fixed']++;
            }
        }

        // Same for temporary members
        $orphanedTemps = $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 = 'temporary'
             AND s.person_id NOT IN (
                 SELECT t.id FROM temporary_members t WHERE t.member_id = s.member_id
             )"
        );

        foreach ($orphanedTemps as $ot) {
            $match = $this->db->selectOne(
                "SELECT id FROM temporary_members WHERE member_id = ? AND full_name_ar = ? ORDER BY is_archived ASC LIMIT 1",
                [(int)$ot['member_id'], $ot['person_name']]
            );
            if ($match && !$dryRun) {
                $this->db->update('subscriptions',
                    ['person_id' => (int)$match['id'], 'updated_at' => $this->ts],
                    'id = ?', [(int)$ot['id']]
                );
                $this->stats['orphaned_ids_fixed']++;
            } elseif ($match) {
                $this->stats['orphaned_ids_fixed']++;
            }
        }
    }

    private function removeDuplicates(bool $dryRun): void
    {
        // Find exact duplicates: same member_id, financial_year, person_type, person_id
        $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 fixIncorrectAmounts(bool $dryRun): void
    {
        // Fix subscriptions with 0.00 base_amount that should have real rates
        $zeroRows = $this->db->select(
            "SELECT id, member_id, financial_year, person_type FROM subscriptions
             WHERE base_amount = '0.00' AND status != 'exempt'"
        );

        foreach ($zeroRows as $row) {
            $rate = $this->getRateForYear($row['financial_year'], $row['person_type']);
            if (bccomp($rate, '0', 2) > 0 && !$dryRun) {
                $this->db->update('subscriptions', [
                    'base_amount' => $rate,
                    'total_amount' => $rate,
                    'updated_at' => $this->ts,
                ], 'id = ?', [(int)$row['id']]);
                $this->stats['amount_corrections']++;
            } elseif (bccomp($rate, '0', 2) > 0) {
                $this->stats['amount_corrections']++;
            }
        }

        // Fix subscriptions with wrong rates for their year (check against expected)
        foreach (['2023/2024', '2024/2025'] as $fy) {
            $expectedRates = $this->rates[$fy];
            foreach (['member', 'spouse', 'child', 'temporary'] as $type) {
                $wrong = $this->db->select(
                    "SELECT id FROM subscriptions
                     WHERE financial_year = ? AND person_type = ? AND base_amount != ? AND status != 'exempt' AND status != 'paid'",
                    [$fy, $type, $expectedRates[$type]]
                );
                foreach ($wrong as $w) {
                    if (!$dryRun) {
                        $this->db->update('subscriptions', [
                            'base_amount' => $expectedRates[$type],
                            'total_amount' => $expectedRates[$type],
                            'updated_at' => $this->ts,
                        ], 'id = ?', [(int)$w['id']]);
                    }
                    $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 = $this->dateToFY($member['created_at']);
            $this->stats['members_processed']++;

            // Generate member subscription for each missing FY
            $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) {
                $spJoinFY = $this->dateToFY($sp['effective_date']);
                // Spouse can't have subscription before the member joined
                $startFY = max($spJoinFY, $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) {
                $chJoinFY = $this->dateToFY($ch['effective_date']);
                $startFY = max($chJoinFY, $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) {
                $tJoinFY = $this->dateToFY($t['effective_date']);
                $startFY = max($tJoinFY, $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 {
        for ($year = $startFYYear; $year <= $this->currentFYStart; $year++) {
            $fy = $year . '/' . ($year + 1);

            // Check if subscription already exists
            $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;

            $rate = $this->getRateForYear($fy, $personType);
            $devFee = ($personType === 'member') ? $this->devFee : '0.00';

            // Determine status: current FY = pending, past FYs = overdue
            $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' => $rate,
                    'development_fee' => $devFee,
                    'discount_amount' => '0.00',
                    'total_amount' => $rate,
                    '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 applyFines(): void
    {
        // Boot App for RuleEngine access
        $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;

                    // Distribute fine proportionally across subscription rows for this FY
                    $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,
                            'status' => 'overdue',
                            '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: Members without subscriptions for current FY
        $missing = $this->db->select(
            "SELECT m.id, m.membership_number, m.full_name_ar
             FROM members m
             WHERE m.status = 'active' AND m.is_archived = 0
             AND m.membership_type NOT IN ('honorary', 'seasonal')
             AND m.id NOT IN (
                 SELECT DISTINCT member_id FROM subscriptions WHERE financial_year = ?
             )",
            [$this->currentFY]
        );
        if (!empty($missing)) {
            $issues[] = ['type' => 'missing_current_fy', 'count' => count($missing), 'members' => $missing];
        }

        // Check 2: Active spouses without subscription in current FY
        $missingSpouses = $this->db->select(
            "SELECT sp.id, sp.member_id, sp.full_name_ar
             FROM spouses sp
             JOIN members m ON m.id = sp.member_id AND m.status = 'active' AND m.is_archived = 0
             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 = ?
                 AND s.person_type = 'spouse' AND s.person_id = sp.id
             )",
            [$this->currentFY]
        );
        if (!empty($missingSpouses)) {
            $issues[] = ['type' => 'missing_spouse_subs', 'count' => count($missingSpouses), 'details' => $missingSpouses];
        }

        // Check 3: Active children without subscription in current FY
        $missingChildren = $this->db->select(
            "SELECT ch.id, ch.member_id, ch.full_name_ar
             FROM children ch
             JOIN members m ON m.id = ch.member_id AND m.status = 'active' AND m.is_archived = 0
             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 = ?
                 AND s.person_type = 'child' AND s.person_id = ch.id
             )",
            [$this->currentFY]
        );
        if (!empty($missingChildren)) {
            $issues[] = ['type' => 'missing_child_subs', 'count' => count($missingChildren), 'details' => $missingChildren];
        }

        // Check 4: Duplicates
        $dupes = $this->db->select(
            "SELECT member_id, financial_year, person_type, person_id, COUNT(*) as cnt
             FROM subscriptions
             GROUP BY member_id, financial_year, person_type, person_id
             HAVING cnt > 1"
        );
        if (!empty($dupes)) {
            $issues[] = ['type' => 'duplicates', 'count' => count($dupes), 'details' => $dupes];
        }

        // Check 5: Orphaned person_ids (check ALL records including archived)
        $orphans = $this->db->select(
            "SELECT s.id, s.member_id, s.person_type, s.person_id, s.financial_year
             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 (!empty($orphans)) {
            $issues[] = ['type' => 'orphaned_person_ids', 'count' => count($orphans), 'details' => $orphans];
        }

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

        return $issues;
    }
}
