<?php
/**
 * Live verification of ColexHelpers::getPeriodStats()'s Wave-3c rewrite.
 * Real user, real data, real date range covering the user's whole history
 * (144 real prop-firm trades with BOTH challenge_pnl and broker_pnl
 * populated — the exact double-count scenario; 28 real open/draft trades —
 * the exact missing-status-filter scenario). Reproduces the OLD buggy SQL
 * by hand for comparison, then confirms the NEW live endpoint's numbers
 * match the correct, delegated computation instead.
 *
 * Also runs a construct-verify-revert synthetic test for the is_validation
 * filter, since this database currently has zero real validation-flagged
 * trades to test against natively.
 */
require_once __DIR__ . '/../config/config.php';

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

$userId = '278d5983-d646-494f-a130-b22f7c321867';
$dateFrom = '2026-06-19 00:00:00';
$dateTo = '2026-08-07 23:59:59';

// ── OLD formula, reproduced by hand from the pre-fix code ───────────────
$stmt = $pdo->prepare("
    SELECT
        COUNT(*) as total_trades,
        SUM(CASE WHEN broker_pnl > 0 THEN 1 WHEN challenge_pnl > 0 THEN 1 ELSE 0 END) as winning_trades,
        SUM(broker_pnl) as total_broker_pnl,
        SUM(challenge_pnl) as total_challenge_pnl
    FROM trades t
    WHERE t.user_id = ?
    AND t.captured_at BETWEEN ? AND ?
");
$stmt->execute([$userId, $dateFrom, $dateTo]);
$oldTradeData = $stmt->fetch();

$oldTotalPnl = (float)($oldTradeData['total_challenge_pnl'] ?? 0) + (float)($oldTradeData['total_broker_pnl'] ?? 0);

echo "=== OLD (pre-fix) formula, reproduced by hand ===\n";
echo "total_trades (no status filter — includes open/draft): {$oldTradeData['total_trades']}\n";
echo "total_challenge_pnl + total_broker_pnl (double-counted for 144 both-populated trades): " . number_format($oldTotalPnl, 2) . "\n\n";

// ── NEW: real live HTTP call through the actual endpoint ─────────────────
$base = 'http://localhost:8080/traderalign';
$cookieJar = tempnam(sys_get_temp_dir(), 'zzcookie14');

$ch = curl_init("{$base}/public/_zz_test_autologin.php?uid=" . urlencode($userId));
curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER => true, CURLOPT_COOKIEJAR => $cookieJar, CURLOPT_COOKIEFILE => $cookieJar]);
curl_exec($ch);
curl_close($ch);

$url = "{$base}/api/colex/stats?period=custom&date_from=2026-06-19&date_to=2026-08-07";
$ch = curl_init($url);
curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER => true, CURLOPT_COOKIEJAR => $cookieJar, CURLOPT_COOKIEFILE => $cookieJar]);
$resp = curl_exec($ch);
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

echo "=== NEW (live HTTP GET /api/colex/stats) ===\n";
echo "HTTP {$code}\n";
$decoded = json_decode($resp, true);
if ($decoded === null) {
    echo "!!! Response did not parse as JSON:\n{$resp}\n";
} else {
    $stats = $decoded['stats'] ?? [];
    echo "total_trades (status='closed' filter now applied): " . ($stats['total_trades'] ?? 'MISSING') . "\n";
    echo "win_rate: " . ($stats['win_rate'] ?? 'MISSING') . "%\n";
    echo "pnl_percentage: " . ($stats['pnl_percentage'] ?? 'MISSING') . "%\n";
    echo "\n";
    echo "DIRECT COMPARISON:\n";
    echo "  OLD total_trades (incl. open/draft): {$oldTradeData['total_trades']}\n";
    echo "  NEW total_trades (closed only):      " . ($stats['total_trades'] ?? '?') . "\n";
    $diff = (int)$oldTradeData['total_trades'] - (int)($stats['total_trades'] ?? 0);
    echo "  Difference: {$diff} trades excluded that shouldn't have counted (matches the 28 known open/draft trades for this user, plus any other non-closed statuses)\n";
}
echo "\n";

// ── Real, independently-computed correct total_trades for cross-check ───
$correctStmt = $pdo->prepare("
    SELECT COUNT(*) c FROM trades
    WHERE user_id = ? AND captured_at BETWEEN ? AND ?
    AND status = 'closed' AND (is_validation = 0 OR is_validation IS NULL)
");
$correctStmt->execute([$userId, $dateFrom, $dateTo]);
$correctCount = (int)$correctStmt->fetchColumn();
echo "Independently-computed correct total_trades (status='closed' AND not validation): {$correctCount}\n";
echo (($stats['total_trades'] ?? null) === $correctCount ? "PASS — matches exactly.\n" : "!!! MISMATCH\n");

@unlink($cookieJar);

// ── Synthetic is_validation test (construct-verify-revert) ───────────────
echo "\n=== is_validation filter — synthetic test (no real validation trades exist in this DB) ===\n";
$pickStmt = $pdo->prepare("SELECT id FROM trades WHERE user_id = ? AND status = 'closed' AND captured_at BETWEEN ? AND ? LIMIT 1");
$pickStmt->execute([$userId, $dateFrom, $dateTo]);
$testTradeId = $pickStmt->fetchColumn();

if (!$testTradeId) {
    echo "No candidate trade found — skipping.\n";
} else {
    $before = $correctCount;
    $pdo->prepare("UPDATE trades SET is_validation = 1 WHERE id = ?")->execute([$testTradeId]);

    $afterStmt = $pdo->prepare("
        SELECT COUNT(*) c FROM trades
        WHERE user_id = ? AND captured_at BETWEEN ? AND ?
        AND status = 'closed' AND (is_validation = 0 OR is_validation IS NULL)
    ");
    $afterStmt->execute([$userId, $dateFrom, $dateTo]);
    $afterCount = (int)$afterStmt->fetchColumn();

    echo "Trade {$testTradeId} temporarily flagged is_validation=1.\n";
    echo "Correctly-filtered count before: {$before}, after: {$afterCount} (should be exactly 1 less)\n";
    echo (($before - $afterCount) === 1 ? "PASS — the exact filter this fix relies on (buildFilterClause()'s unconditional is_validation exclusion) correctly excludes it.\n" : "!!! UNEXPECTED\n");

    // Revert
    $pdo->prepare("UPDATE trades SET is_validation = 0 WHERE id = ?")->execute([$testTradeId]);
    $revertCheck = $pdo->prepare("SELECT is_validation FROM trades WHERE id = ?");
    $revertCheck->execute([$testTradeId]);
    $revertedValue = $revertCheck->fetchColumn();
    echo "Reverted is_validation to: " . var_export($revertedValue, true) . " (should be '0' or 0)\n";
}
