<?php
/**
 * TraderAlign — Daily Cron
 * Schedule: Daily at 03:00 UTC
 * Tasks: Draft expiry, verification sweep, hard-delete expired accounts
 */

require_once __DIR__ . '/../config/config.php';

// Fail closed if this deployment never set a real CRON_SECRET (i.e. no
// config/config.local.php was created) — refuse to run at all rather than
// being authenticatable with the publicly-known placeholder string that
// ships in the tracked config.php. This is a second, independent layer on
// top of actually generating a real secret (see config.local.example.php);
// confirmed live-exploitable with the placeholder during the 2026-08-19
// pre-launch security audit.
if (CRON_SECRET === 'change-this-to-a-random-string-in-production') {
    http_response_code(403);
    die('CRON_SECRET has not been configured for this environment. See config/config.local.example.php.');
}

// Verify cron secret with timing-safe comparison
$secret = $_GET['secret'] ?? '';
if (!hash_equals(CRON_SECRET, $secret)) {
    http_response_code(403);
    die('Unauthorized');
}

// Set execution time limit for long-running tasks
set_time_limit(300);

try {
    $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,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
    ]);
} catch (PDOException $e) {
    error_log("Cron DB connection failed: " . $e->getMessage());
    http_response_code(500);
    die('Internal server error');
}

$log = [];
$startTime = microtime(true);
$now = date('Y-m-d H:i:s');

// ── 1. Draft Expiry ─────────────────────────────────────
// Drafts older than their trading-style window get auto-cancelled
//
// Wave 6 (Single Source of Truth migration §Consolidated #9): was keyed
// purely on user_profiles.trading_style (a per-USER profile setting) —
// public/drafts.php's own urgency-badge UI, by contrast, resolves a
// draft's trading style from the STRATEGY actually attached to that
// draft (strategy_versions -> strategies.trading_style). A user's
// profile-level style and a specific draft's own strategy's style are
// not guaranteed to agree (a user can have multiple strategies with
// different styles, or a stale/never-set profile field) — when they
// disagreed, this cron job would silently expire a draft against the
// WRONG threshold from what its own UI badge was showing the trader.
// Reconciled: each draft's own strategy's trading_style is now the
// primary source (matching, and more precise than, drafts.php's own
// resolution — see that file's own comment), falling back to
// user_profiles.trading_style only for drafts with no strategy attached,
// same fallback order drafts.php itself would use if it read the
// profile at all. Threshold hour values themselves are unchanged.
try {
    $totalExpiredCount = 0;

    // Process each trading style individually to avoid complex CASE syntax issues
    $draftExpiryHours = [
        'scalper' => 4,
        'day_trader' => 24,
        'swing' => 168,
        'position' => 720,
    ];

    foreach ($draftExpiryHours as $style => $hours) {
        $stmt = $pdo->prepare(
            "UPDATE trades t
             LEFT JOIN strategy_versions sv ON sv.id = t.strategy_version_id
             LEFT JOIN strategies s ON s.id = sv.strategy_id
             LEFT JOIN user_profiles up ON up.user_id = t.user_id
             SET t.status = 'cancelled',
                 t.cancellation_reason = 'auto_expired'
             WHERE t.status = 'draft'
             AND t.deleted_at IS NULL
             AND COALESCE(s.trading_style, up.trading_style) = :style
             AND t.captured_at < DATE_SUB(NOW(), INTERVAL :hours HOUR)"
        );
        $stmt->execute([':style' => $style, ':hours' => $hours]);
        $count = $stmt->rowCount();
        if ($count > 0) {
            $log[] = "Expired {$count} drafts ({$style}, >{$hours}h)";
            $totalExpiredCount += $count;
        }
    }

    // Default expiry for drafts with no resolvable trading style (no
    // strategy attached AND no user_profiles.trading_style set)
    $stmt = $pdo->prepare(
        "UPDATE trades t
         LEFT JOIN strategy_versions sv ON sv.id = t.strategy_version_id
         LEFT JOIN strategies s ON s.id = sv.strategy_id
         LEFT JOIN user_profiles up ON up.user_id = t.user_id
         SET t.status = 'cancelled',
             t.cancellation_reason = 'auto_expired'
         WHERE t.status = 'draft'
         AND t.deleted_at IS NULL
         AND (COALESCE(s.trading_style, up.trading_style) IS NULL OR COALESCE(s.trading_style, up.trading_style) NOT IN ('scalper','day_trader','swing','position'))
         AND t.captured_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)"
    );
    $stmt->execute();
    $count = $stmt->rowCount();
    if ($count > 0) {
        $log[] = "Expired {$count} drafts (default, >24h)";
        $totalExpiredCount += $count;
    }

    if ($totalExpiredCount === 0) {
        // No drafts to expire, but query succeeded
        $log[] = "No drafts to expire";
    }
} catch (PDOException $e) {
    error_log("Draft expiry failed: " . $e->getMessage());
    error_log("SQL State: " . $e->getCode());
    $log[] = "ERROR: Draft expiry failed - " . $e->getMessage();
}

// ── 2. Verification Sweep ───────────────────────────────
// Users past 24h verification deadline → grace_expired (writes blocked)
try {
    // First, check if there are users to update
    $stmt = $pdo->prepare(
        "SELECT COUNT(*) FROM user_profiles 
         WHERE verification_state = 'pending' 
         AND verification_required_at <= NOW()
         AND verification_required_at IS NOT NULL"
    );
    $stmt->execute();
    $pendingCount = $stmt->fetchColumn();

    if ($pendingCount > 0) {
        $stmt = $pdo->prepare(
            "UPDATE user_profiles 
             SET verification_state = 'grace_expired', 
                 updated_at = NOW() 
             WHERE verification_state = 'pending' 
             AND verification_required_at <= NOW()
             AND verification_required_at IS NOT NULL"
        );
        $stmt->execute();
        $count = $stmt->rowCount();
        if ($count > 0) {
            $log[] = "Set {$count} users to grace_expired (verification deadline passed)";
        }
    }
} catch (PDOException $e) {
    error_log("Verification sweep failed: " . $e->getMessage());
    $log[] = "ERROR: Verification sweep failed";
}

// ── 3. Hard Delete (30-day grace) ────────────────────────
// Users soft-deleted more than 30 days ago get hard-deleted
try {
    // Get count first for logging
    $stmt = $pdo->prepare(
        "SELECT COUNT(*) FROM users 
         WHERE deleted_at IS NOT NULL 
         AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY)"
    );
    $stmt->execute();
    $deleteCount = $stmt->fetchColumn();

    if ($deleteCount > 0) {
        // Use a single DELETE with JOIN to handle related data
        $pdo->beginTransaction();
        
        try {
            // Delete related records first if not using CASCADE
            $relatedTables = [
                'trades' => 'user_id',
                'checklist_results' => 'user_id',
                'user_sessions' => 'user_id',
                'trading_accounts' => 'user_id',
                'user_profiles' => 'user_id',
            ];
            
            foreach ($relatedTables as $table => $column) {
                $stmt = $pdo->prepare(
                    "DELETE FROM {$table} 
                     WHERE {$column} IN (
                         SELECT id FROM users 
                         WHERE deleted_at IS NOT NULL 
                         AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
                     )"
                );
                $stmt->execute();
            }
            
            // Finally delete users
            $stmt = $pdo->prepare(
                "DELETE FROM users 
                 WHERE deleted_at IS NOT NULL 
                 AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY)"
            );
            $stmt->execute();
            
            $pdo->commit();
            $log[] = "Hard-deleted {$deleteCount} accounts (30-day grace expired)";
        } catch (PDOException $e) {
            $pdo->rollBack();
            throw $e;
        }
    }
} catch (PDOException $e) {
    error_log("Hard delete failed: " . $e->getMessage());
    $log[] = "ERROR: Hard delete failed";
}

// ── 4. Checklist Result Expiry ──────────────────────────
try {
    // First check if there are results to expire
    $stmt = $pdo->prepare(
        "SELECT COUNT(*) FROM checklist_results 
         WHERE status = 'pending_calculator' 
         AND expires_at <= NOW()
         AND expires_at IS NOT NULL"
    );
    $stmt->execute();
    $expiredCount = $stmt->fetchColumn();

    if ($expiredCount > 0) {
        $stmt = $pdo->prepare(
            "UPDATE checklist_results 
             SET status = 'expired'
             WHERE status = 'pending_calculator' 
             AND expires_at <= NOW()
             AND expires_at IS NOT NULL"
        );
        $stmt->execute();
        $count = $stmt->rowCount();
        if ($count > 0) {
            $log[] = "Expired {$count} checklist results";
        }
    } else {
        // No results to expire
        $log[] = "No checklist results to expire";
    }
} catch (PDOException $e) {
    error_log("Checklist expiry failed: " . $e->getMessage());
    error_log("SQL State: " . $e->getCode());
    $log[] = "ERROR: Checklist expiry failed - " . $e->getMessage();
}

// ── 5. Clean Up Old Logs (Optional) ─────────────────────
try {
    // Delete logs older than 90 days
    $stmt = $pdo->prepare(
        "DELETE FROM cron_logs 
         WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)"
    );
    $stmt->execute();
    $count = $stmt->rowCount();
    if ($count > 0) {
        $log[] = "Cleaned up {$count} old log entries";
    }
} catch (PDOException $e) {
    // Silently fail - logs table might not exist yet
}

// ── 6. Log Execution (Optional) ─────────────────────────
try {
    $executionTime = round(microtime(true) - $startTime, 2);
    $logEntry = implode('; ', $log);
    
    $stmt = $pdo->prepare(
        "INSERT INTO cron_logs (job_name, execution_time, actions_taken, status, created_at) 
         VALUES (:job, :exec_time, :actions, :status, NOW())"
    );
    $stmt->execute([
        ':job' => 'daily_maintenance',
        ':exec_time' => $executionTime,
        ':actions' => $logEntry ?: 'No actions taken',
        ':status' => empty(array_filter($log, fn($l) => str_starts_with($l, 'ERROR'))) ? 'success' : 'partial_failure'
    ]);
} catch (PDOException $e) {
    // Silently fail - logs table might not exist yet
}

// ── Output ──────────────────────────────────────────────
$executionTime = round(microtime(true) - $startTime, 2);

echo "Daily Cron — " . $now . "\n";
echo str_repeat('-', 50) . "\n";
if (empty($log)) {
    echo "No actions taken.\n";
} else {
    echo implode("\n", $log) . "\n";
}
echo str_repeat('-', 50) . "\n";
echo "Execution time: {$executionTime}s\n";
echo "Done.\n";