<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    // Phase 1: Remove duplicate subscription rows.
    // For each (member_id, financial_year, person_type, person_id) group with more than one row,
    // keep the "best" row (paid > exempt > overdue > pending) with the lowest ID as tiebreaker.
    // Delete the rest.

    $duplicates = $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
    ");

    $deleted = 0;
    foreach ($duplicates as $dup) {
        $personIdCondition = $dup['person_id'] === null
            ? 'person_id IS NULL'
            : 'person_id = ' . (int) $dup['person_id'];

        $rows = $db->select("
            SELECT id, status, paid_amount, payment_id
            FROM subscriptions
            WHERE member_id = ? AND financial_year = ? AND person_type = ? AND {$personIdCondition}
            ORDER BY
                FIELD(status, 'paid', 'exempt', 'overdue', 'pending') ASC,
                CASE WHEN paid_amount > 0 THEN 0 ELSE 1 END ASC,
                id ASC
        ", [(int) $dup['member_id'], $dup['financial_year'], $dup['person_type']]);

        // Keep the first row (best status), delete the rest
        $keepId = (int) $rows[0]['id'];
        foreach (array_slice($rows, 1) as $extra) {
            $db->delete('subscriptions', 'id = ?', [(int) $extra['id']]);
            $deleted++;
        }
    }

    // Phase 2: Handle NULL person_id rows — normalize to avoid unique constraint issues.
    // The SubscriptionGenerator sets person_id = member_id for person_type='member',
    // but SubscriptionSyncService sets person_id = NULL. Normalize to match the generator.
    $db->query("
        UPDATE subscriptions
        SET person_id = member_id
        WHERE person_type = 'member' AND person_id IS NULL
    ");

    // Phase 3: Re-check duplicates after normalization (NULL->member_id may have created new dupes)
    $duplicates2 = $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
    ");

    foreach ($duplicates2 as $dup) {
        $rows = $db->select("
            SELECT id, status, paid_amount, payment_id
            FROM subscriptions
            WHERE member_id = ? AND financial_year = ? AND person_type = ? AND person_id = ?
            ORDER BY
                FIELD(status, 'paid', 'exempt', 'overdue', 'pending') ASC,
                CASE WHEN paid_amount > 0 THEN 0 ELSE 1 END ASC,
                id ASC
        ", [(int) $dup['member_id'], $dup['financial_year'], $dup['person_type'], (int) $dup['person_id']]);

        $keepId = (int) $rows[0]['id'];
        foreach (array_slice($rows, 1) as $extra) {
            $db->delete('subscriptions', 'id = ?', [(int) $extra['id']]);
            $deleted++;
        }
    }

    // Phase 4: Add unique constraint to prevent future duplicates
    $db->raw("
        ALTER TABLE subscriptions
        ADD UNIQUE INDEX uq_subscription_member_year_person (member_id, financial_year, person_type, person_id)
    ");
};
