<?php
// Backfill is_first_payment for each deal based on earliest confirmed transaction.

require __DIR__ . '/../../vendor/autoload.php';
$app = require __DIR__ . '/../../app/bootstrap.php';

$pdo = $app->db->getPdo();

$pdo->beginTransaction();
try {
    $pdo->exec("UPDATE transactions SET is_first_payment = 0");

    $sql = "
        UPDATE transactions t
        JOIN (
            SELECT
                deal_id,
                MIN(CONCAT(DATE(COALESCE(payment_date, payment_time)), '-', LPAD(id, 10, '0'))) AS first_key
            FROM transactions
            WHERE status = 'confirmed'
            GROUP BY deal_id
        ) x ON t.deal_id = x.deal_id
        SET t.is_first_payment = CASE
            WHEN CONCAT(DATE(COALESCE(t.payment_date, t.payment_time)), '-', LPAD(t.id, 10, '0')) = x.first_key THEN 1
            ELSE 0
        END
        WHERE t.status = 'confirmed'
    ";
    $pdo->exec($sql);
    $pdo->commit();
    echo "Backfill completed for is_first_payment." . PHP_EOL;
} catch (\Throwable $e) {
    $pdo->rollBack();
    echo "Backfill failed: " . $e->getMessage() . PHP_EOL;
    exit(1);
}
