<?php
/**
 * One-time backfill: recompute trade_classification/drawdown_state_at_close/
 * classification_reason for every real closed trade, using
 * TradeClassificationEngine instead of the retired sp_classify_trade
 * stored procedure. 2026-08-18.
 *
 * Snapshots the CURRENT state to a JSON file first (revertable), then
 * writes account-by-account inside its own small transaction (one
 * transaction per account, not one giant transaction for the whole
 * table — keeps lock time short and means a failure on one account
 * doesn't roll back everything already-applied to prior accounts).
 *
 * Usage:
 *   php scripts/backfill_trade_classification.php --dry-run   (default, writes nothing)
 *   php scripts/backfill_trade_classification.php --apply     (writes for real)
 */
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../src/TradeClassificationEngine.php';

$dsn = "mysql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME . ";charset=utf8mb4";
$pdo = new PDO($dsn, DB_USER, DB_PASS, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

$apply = in_array('--apply', $argv, true);
echo $apply ? "=== APPLY MODE — writing for real ===\n" : "=== DRY RUN — nothing will be written (pass --apply to write) ===\n";

// ── Step 1: snapshot current state, always, regardless of --dry-run/--apply ──
$snapshotRows = $pdo->query("SELECT id, trade_classification, drawdown_state_at_close, classification_reason FROM trades WHERE status='closed' AND deleted_at IS NULL")->fetchAll();
$snapshotPath = __DIR__ . '/../docs/architecture/golden_value_logs/trade_classification_backfill_snapshot_' . date('Y-m-d_His') . '.json';
file_put_contents($snapshotPath, json_encode($snapshotRows, JSON_PRETTY_PRINT));
echo "Snapshot of " . count($snapshotRows) . " trades' pre-backfill state written to:\n  $snapshotPath\n\n";

// ── Step 2: iterate accounts, classify, write ──
$accounts = $pdo->query("SELECT DISTINCT account_id FROM trades WHERE status='closed' AND deleted_at IS NULL AND account_id IS NOT NULL")->fetchAll();

$totalTradesWritten = 0;
$accountsProcessed = 0;
$accountsErrored = 0;
$newCounts = [];
$changeMatrix = [];

$oldById = [];
foreach ($snapshotRows as $r) { $oldById[$r['id']] = $r['trade_classification'] ?? 'NULL'; }

foreach ($accounts as $row) {
    $accountId = $row['account_id'];
    try {
        $pdo->beginTransaction();
        $results = TradeClassificationEngine::classifyAccount($accountId, $pdo);
        foreach ($results as $tradeId => $result) {
            $newCounts[$result['classification']] = ($newCounts[$result['classification']] ?? 0) + 1;
            $old = $oldById[$tradeId] ?? 'NULL';
            $key = "$old -> {$result['classification']}";
            $changeMatrix[$key] = ($changeMatrix[$key] ?? 0) + 1;
            if ($apply) {
                TradeClassificationEngine::writeClassification($tradeId, $result, $pdo);
            }
            $totalTradesWritten++;
        }
        if ($apply) {
            $pdo->commit();
        } else {
            $pdo->rollBack(); // dry run — always undo, even though writeClassification() was skipped, to close the txn cleanly
        }
        $accountsProcessed++;
    } catch (\Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        $accountsErrored++;
        echo "ERROR on account $accountId: " . $e->getMessage() . "\n";
    }
}

echo "Accounts processed: $accountsProcessed (errored: $accountsErrored)\n";
echo "Trades " . ($apply ? "written" : "would be written") . ": $totalTradesWritten\n\n";

echo "=== New classification counts ===\n";
foreach ($newCounts as $c => $n) echo "  $c: $n\n";

echo "\n=== Change matrix (old -> new) ===\n";
arsort($changeMatrix);
foreach ($changeMatrix as $k => $n) echo "  $k: $n\n";

if ($apply) {
    echo "\n=== VERIFICATION: re-reading DB after write ===\n";
    $after = $pdo->query("SELECT trade_classification, COUNT(*) n FROM trades WHERE status='closed' AND deleted_at IS NULL GROUP BY trade_classification")->fetchAll();
    foreach ($after as $r) echo "  " . ($r['trade_classification'] ?? 'NULL') . ": {$r['n']}\n";
} else {
    echo "\nDry run complete. Nothing was written. Re-run with --apply to write for real.\n";
}
