<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    $cols = [
        'receipt_number' => "ALTER TABLE subscriptions ADD COLUMN receipt_number VARCHAR(50) NULL DEFAULT NULL AFTER payment_id",
        'paid_by'        => "ALTER TABLE subscriptions ADD COLUMN paid_by BIGINT UNSIGNED NULL DEFAULT NULL AFTER receipt_number",
    ];

    foreach ($cols as $col => $sql) {
        $exists = $db->selectOne(
            "SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = 'subscriptions' AND column_name = ?",
            [$col]
        );
        if (!$exists) {
            $db->raw($sql);
        }
    }

    // Backfill existing paid subscriptions from payments + receipts tables
    $db->raw("
        UPDATE subscriptions s
        JOIN payments p ON p.id = s.payment_id
        LEFT JOIN receipts r ON r.id = p.receipt_id
        SET s.receipt_number = r.receipt_number,
            s.paid_by = p.received_by_employee_id
        WHERE s.status = 'paid' AND s.payment_id IS NOT NULL AND s.receipt_number IS NULL
    ");
};
