<?php
declare(strict_types=1);

/**
 * Comprehensive subscription data fix:
 *
 * 1. Fix total_amount for individual-format rows where total != base - discount
 *    (caused by retroactive wizard using wrong rates or including dev fee in total)
 * 2. Apply 50% discount to 2023/2024 rows that missed it
 * 3. Set development_fee = 35.00 on member rows that are missing it
 * 4. Fix paid status rows that have no payment_id (set status based on paid_amount)
 *
 * Only touches rows where the issue can be unambiguously identified.
 * Bundled old-format rows (total includes multiple family members) are NOT touched.
 */
return function (\App\Core\Database $db): void {
    $fixed = 0;

    // --- Step 1: Fix missing development_fee on member rows (unpaid only) ---
    $db->query(
        "UPDATE subscriptions SET development_fee = 35.00, updated_at = NOW()
         WHERE person_type = 'member' AND (development_fee IS NULL OR development_fee = 0)
         AND status IN ('pending', 'overdue')"
    );

    // --- Step 2: Apply 2023/2024 discount to rows that missed it ---
    // Only for individual-format rows (where total_amount is close to base_amount or base+devfee)
    $missing2023Discount = $db->select(
        "SELECT id, person_type, base_amount, development_fee, total_amount
         FROM subscriptions
         WHERE financial_year = '2023/2024'
         AND (discount_amount IS NULL OR discount_amount = 0)
         AND status IN ('pending', 'overdue')
         AND base_amount > 0"
    );

    foreach ($missing2023Discount as $row) {
        $base = (string) $row['base_amount'];
        $discount = bcdiv(bcmul($base, '50', 4), '100', 2);
        $newTotal = bcsub($base, $discount, 2);

        $db->query(
            "UPDATE subscriptions SET discount_amount = ?, total_amount = ?, updated_at = NOW() WHERE id = ?",
            [$discount, $newTotal, (int) $row['id']]
        );
        $fixed++;
    }

    // --- Step 3: Fix total_amount where it doesn't match base - discount ---
    // This catches rows where:
    //   - dev fee was included in total (difference = 35)
    //   - wrong rate was used for total (difference = 82 for member/spouse, 37 for child)
    // We ONLY fix rows that are individual-format (not bundled family rows)
    // A row is "bundled" if total_amount > base_amount * 2 (clearly includes other people)
    $calcMismatch = $db->select(
        "SELECT id, base_amount, discount_amount, total_amount, development_fee
         FROM subscriptions
         WHERE status IN ('pending', 'overdue')
         AND ABS(total_amount - (base_amount - COALESCE(discount_amount, 0))) > 0.01
         AND total_amount <= base_amount * 2
         AND base_amount > 0"
    );

    foreach ($calcMismatch as $row) {
        $base = (string) $row['base_amount'];
        $discount = (string) ($row['discount_amount'] ?? '0.00');
        $correctTotal = bcsub($base, $discount, 2);

        if (bccomp($correctTotal, '0', 2) <= 0) continue;

        $db->query(
            "UPDATE subscriptions SET total_amount = ?, updated_at = NOW() WHERE id = ?",
            [$correctTotal, (int) $row['id']]
        );
        $fixed++;
    }

    // --- Step 4: Fix status='paid' rows with no payment_id ---
    // Old retroactive entries marked 'paid' with paid_amount=0 and no payment_id
    // These are historical records — leave status as 'paid' (they were paid in reality)
    // but ensure they don't interfere with fine calculations (paid_amount should = total_amount)
    $paidNoPayment = $db->select(
        "SELECT id, total_amount, development_fee, person_type
         FROM subscriptions
         WHERE status = 'paid' AND payment_id IS NULL AND paid_amount = 0"
    );

    foreach ($paidNoPayment as $row) {
        // For paid historical records, set paid_amount = total so fine calc skips them correctly
        $paidAmt = (string) $row['total_amount'];
        $db->query(
            "UPDATE subscriptions SET paid_amount = ?, updated_at = NOW() WHERE id = ?",
            [$paidAmt, (int) $row['id']]
        );
        $fixed++;
    }

    // --- Step 5: Reset fine_amount to 0 for all unpaid rows ---
    // Fines will be recalculated on next page visit via OverdueFineApplicator::applyForMember()
    $db->query(
        "UPDATE subscriptions SET fine_amount = 0.00, updated_at = NOW()
         WHERE status IN ('pending', 'overdue') AND fine_amount > 0"
    );
};
