<?php
// Rychlý cleanup script - určený pro časté spouštění (každou hodinu)
// Povolit dlouhé běhy (pro web i CLI)
set_time_limit(0);
ignore_user_abort(true);

require_once __DIR__ . '/db.php';

echo "[" . date('Y-m-d H:i:s') . "] Starting cleanup...\n";

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (Exception $e) {
    error_log('Cleanup failed to connect to DB: ' . $e->getMessage());
    die("Database connection failed\n");
}


// ============================================
// 1. Odstranění starých watchdog_heartbeat (>7 dní)
// ============================================
// POZNÁMKA: Tabulka watchdog_heartbeat má nyní jeden záznam per account_number
// (PRIMARY KEY na account_number). Záznamy se updatují, nemažou se podle stáří.
// Tento cleanup je ponechán pro případy kdy účet přestane pingovat.
try {
    echo "Cleaning old watchdog records (>7 days)...\n";
    $stmt = $pdo->prepare("
        DELETE FROM watchdog_heartbeat
        WHERE time_ping < DATE_SUB(NOW(), INTERVAL 7 DAY)
    ");
    $stmt->execute();
    $deleted = $stmt->rowCount();
    echo "Deleted $deleted old watchdog records.\n";
} catch (Exception $e) {
    error_log('Cleanup watchdog failed: ' . $e->getMessage());
    echo "Error: " . $e->getMessage() . "\n";
}


// ============================================
// 2. Odstranění starých strategy_logs - PŘESKOČENO
// ============================================
// Mazání podle času odstraněno - ponecháváme pouze limit 5000 logů per bot (krok 5)
echo "Skipping time-based strategy_logs cleanup (using size limit only in step 5).\n";


// ============================================
// 3. Agregace positions - PŘESKOČENO
// ============================================
// Tabulka positions má nyní UNIQUE constraint na position_id.
// Jeden záznam per position_id, updatuje se přes ON DUPLICATE KEY UPDATE.
// Deduplikace už není potřeba.
echo "\n=== Positions aggregation ===\n";
echo "SKIPPED: positions table has UNIQUE constraint on position_id (one record per position).\n";


// ============================================
// 4. Odstranění starých souborů (>30 dní)
// ============================================
function removeOldFiles($dir, $days = 30) {
    if (!is_dir($dir)) {
        echo "Directory $dir does not exist, skipping.\n";
        return 0;
    }
    $now = time();
    $maxAge = 60 * 60 * 24 * $days;
    $deleted = 0;
    foreach (glob($dir . '/*') as $file) {
        if (is_file($file) && ($now - filemtime($file)) > $maxAge) {
            unlink($file);
            $deleted++;
        }
    }
    return $deleted;
}

echo "Cleaning old backtest files (>30 days)...\n";
$deleted1 = removeOldFiles(__DIR__ . '/backtests_trades', 30);
$deleted2 = removeOldFiles(__DIR__ . '/backtests_equity', 30);
echo "Deleted $deleted1 trade files and $deleted2 equity files.\n";


// ============================================
// 5. Čištění strategy_logs - ponechat 5000 posledních na bota
// ============================================
// Bot = kombinace account_number + label + log_type
// Ponecháme posledních 5000 záznamů pro každého bota

try {
    echo "\n=== Cleaning strategy_logs ===\n";
    echo "Keeping last 5000 logs per bot (account_number + label + log_type)...\n";

    $globalStart = time();
    $totalDeleted = 0;

    // Získat unikátní boty z indexu - rychlejší než DISTINCT na celé tabulce
    // Použijeme GROUP BY s indexem idx_account_label
    $stmt = $pdo->query("
        SELECT account_number, label, log_type
        FROM strategy_logs
        GROUP BY account_number, label, log_type
    ");
    $bots = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Found " . count($bots) . " bots to process.\n";

    foreach ($bots as $i => $bot) {
        $accNum = $bot['account_number'];
        $label = $bot['label'];
        $logType = $bot['log_type'];

        // Najdi ID 5000. nejnovějšího záznamu pro tohoto bota
        $stmt = $pdo->prepare("
            SELECT id FROM strategy_logs
            WHERE account_number = ? AND label = ? AND log_type = ?
            ORDER BY id DESC
            LIMIT 1 OFFSET 4999
        ");
        $stmt->execute([$accNum, $label, $logType]);
        $cutoffId = $stmt->fetchColumn();

        if ($cutoffId) {
            // Smaž po dávkách pro velké objemy
            $botDeleted = 0;
            do {
                $stmt = $pdo->prepare("
                    DELETE FROM strategy_logs
                    WHERE account_number = ? AND label = ? AND log_type = ?
                    AND id < ?
                    LIMIT 100000
                ");
                $stmt->execute([$accNum, $label, $logType, $cutoffId]);
                $deleted = $stmt->rowCount();
                $botDeleted += $deleted;

                if ($deleted > 0) {
                    usleep(50000); // 50ms pauza
                }
            } while ($deleted > 0);

            if ($botDeleted > 0) {
                $totalDeleted += $botDeleted;
                echo "  [$i/" . count($bots) . "] $accNum/$label/$logType: deleted $botDeleted logs\n";
            }
        }

        // Bezpečnostní break
        if (time() - $globalStart > 600) {
            echo "  Warning: cleanup running >10 minutes, stopping at bot $i.\n";
            break;
        }
    }

    $elapsed = time() - $globalStart;
    echo "Strategy logs cleanup finished in {$elapsed}s: deleted $totalDeleted logs.\n";

} catch (Exception $e) {
    error_log('Cleanup strategy_logs failed: ' . $e->getMessage());
    echo "Error cleaning strategy_logs: " . $e->getMessage() . "\n";
}


// ============================================
// 6. Agregace wallet_history
// ============================================
// Strategie:
// - 0-24 hodin: ponechat všechny záznamy
// - 1-7 dní: ponechat 1 záznam/hodinu/wallet (poslední v hodině)
// - 7+ dní: ponechat 1 záznam/den/wallet (poslední v den)
//
// Přístup: zpracování po jednotlivých walletech pro lepší výkon

try {
    echo "\n=== Aggregating wallet_history ===\n";
    $globalStart = time();

    // Získat seznam všech walletů
    $stmt = $pdo->query("SELECT DISTINCT account_number FROM wallet_history");
    $wallets = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    echo "Found " . count($wallets) . " wallets to process.\n";

    $totalHourlyDeleted = 0;
    $totalDailyDeleted = 0;

    foreach ($wallets as $wallet) {
        echo "\nProcessing wallet: $wallet\n";

        // 5a. Agregace 1-7 dní pro tento wallet
        $stmt = $pdo->prepare("
            SELECT MAX(id) as keep_id
            FROM wallet_history
            WHERE account_number = ?
              AND time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
              AND time < DATE_SUB(NOW(), INTERVAL 24 HOUR)
            GROUP BY DATE(time), HOUR(time)
        ");
        $stmt->execute([$wallet]);
        $keepIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

        if (count($keepIds) > 0) {
            $placeholders = implode(',', $keepIds);
            $stmt = $pdo->prepare("
                DELETE FROM wallet_history
                WHERE account_number = ?
                  AND time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                  AND time < DATE_SUB(NOW(), INTERVAL 24 HOUR)
                  AND id NOT IN ($placeholders)
            ");
            $stmt->execute([$wallet]);
            $deleted = $stmt->rowCount();
            $totalHourlyDeleted += $deleted;
            if ($deleted > 0) {
                echo "  1-7d: deleted $deleted (kept " . count($keepIds) . ")\n";
            }
        }

        // 5b. Agregace 7+ dní pro tento wallet
        $stmt = $pdo->prepare("
            SELECT MAX(id) as keep_id
            FROM wallet_history
            WHERE account_number = ?
              AND time < DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY DATE(time)
        ");
        $stmt->execute([$wallet]);
        $keepIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

        if (count($keepIds) > 0) {
            $placeholders = implode(',', $keepIds);
            $stmt = $pdo->prepare("
                DELETE FROM wallet_history
                WHERE account_number = ?
                  AND time < DATE_SUB(NOW(), INTERVAL 7 DAY)
                  AND id NOT IN ($placeholders)
            ");
            $stmt->execute([$wallet]);
            $deleted = $stmt->rowCount();
            $totalDailyDeleted += $deleted;
            if ($deleted > 0) {
                echo "  7d+: deleted $deleted (kept " . count($keepIds) . ")\n";
            }
        }

        // Bezpečnostní break
        if (time() - $globalStart > 600) {
            echo "\nWarning: aggregation running >10 minutes, stopping.\n";
            break;
        }
    }

    $elapsed = time() - $globalStart;
    echo "\nAggregation finished in {$elapsed}s: deleted " . ($totalHourlyDeleted + $totalDailyDeleted) . " records ";
    echo "(hourly: $totalHourlyDeleted, daily: $totalDailyDeleted)\n";

} catch (Exception $e) {
    error_log('Cleanup wallet_history aggregation failed: ' . $e->getMessage());
    echo "Error: " . $e->getMessage() . "\n";
}


// ============================================
// 7. Čištění reporter_log - ponechat posledních 1000 záznamů
// ============================================
try {
    echo "\n=== Cleaning reporter_log ===\n";
    echo "Keeping last 1000 logs...\n";

    // Najdi ID 1000. nejnovějšího záznamu
    $stmt = $pdo->query("
        SELECT id FROM reporter_log
        ORDER BY id DESC
        LIMIT 1 OFFSET 999
    ");
    $cutoffId = $stmt->fetchColumn();

    if ($cutoffId) {
        // Smaž záznamy starší než cutoff
        $stmt = $pdo->prepare("
            DELETE FROM reporter_log
            WHERE id < ?
        ");
        $stmt->execute([$cutoffId]);
        $deleted = $stmt->rowCount();
        echo "Deleted $deleted old reporter_log records.\n";
    } else {
        echo "Less than 1000 records, nothing to delete.\n";
    }

} catch (Exception $e) {
    error_log('Cleanup reporter_log failed: ' . $e->getMessage());
    echo "Error cleaning reporter_log: " . $e->getMessage() . "\n";
}

// ============================================
// 8. Čištění mrdca_api_log - ponechat posledních 5000 záznamů
// ============================================
try {
    echo "\n=== Cleaning mrdca_api_log ===\n";
    $cutoffId = $pdo->query("SELECT id FROM mrdca_api_log ORDER BY id DESC LIMIT 1 OFFSET 4999")->fetchColumn();
    if ($cutoffId) {
        $stmt = $pdo->prepare("DELETE FROM mrdca_api_log WHERE id < ?");
        $stmt->execute([$cutoffId]);
        echo "Deleted " . $stmt->rowCount() . " old mrdca_api_log records.\n";
    } else {
        echo "Less than 5000 records, nothing to delete.\n";
    }
} catch (Exception $e) {
    error_log('Cleanup mrdca_api_log failed: ' . $e->getMessage());
    echo "Error cleaning mrdca_api_log: " . $e->getMessage() . "\n";
}


echo "\n[" . date('Y-m-d H:i:s') . "] Cleanup finished.\n";