<?php
// ============================================================
// Konfigurasi Database & Timezone - FIFA World Cup 2026
// ============================================================

// ==================== SESSION & CSRF (DIPINDAHKAN KE PALING ATAS) ====================
error_log("=== CONFIG.PHP START ===");
if (session_status() === PHP_SESSION_NONE) {
    session_start();
    error_log("Session started, ID: " . session_id());
} else {
    error_log("Session already active, ID: " . session_id());
}
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    error_log("New CSRF token generated: " . $_SESSION['csrf_token']);
} else {
    error_log("Existing CSRF token: " . $_SESSION['csrf_token']);
}

// Gunakan environment variable untuk keamanan (jangan hardcode)
$host = getenv('DB_HOST') ?: 'localhost';
$db = getenv('DB_NAME') ?: 'admiiohp_db_worldcup_sakti';
$user = getenv('DB_USER') ?: 'admiiohp_user_worldcup_sakti';
$pass = getenv('DB_PASS') ?: '@Tomswick007

';
$charset = 'utf8mb4';

// Set zona waktu untuk PHP ke WIB (Asia/Jakarta) - untuk keperluan tampilan dan logika internal
date_default_timezone_set('Asia/Jakarta');

// Konstanta zona waktu untuk kemudahan referensi
define('APP_TIMEZONE', 'Asia/Jakarta');
define('DB_TIMEZONE', '+00:00'); // MySQL menggunakan UTC (SOLUSI JANGKA PANJANG)

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '" . DB_TIMEZONE . "'"
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    error_log("Database connection successful");

    // --- TABEL UTAMA (tidak diubah, tetap sama seperti sebelumnya) ---
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        tickets INT DEFAULT 0,
        is_admin TINYINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS groups_list (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name CHAR(1) UNIQUE NOT NULL
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS teams (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        flag_url VARCHAR(255),
        group_id INT,
        played INT DEFAULT 0,
        won INT DEFAULT 0,
        drawn INT DEFAULT 0,
        lost INT DEFAULT 0,
        goals_for INT DEFAULT 0,
        goals_against INT DEFAULT 0,
        points INT DEFAULT 0,
        FOREIGN KEY (group_id) REFERENCES groups_list(id) ON DELETE SET NULL
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS group_matches (
        id INT AUTO_INCREMENT PRIMARY KEY,
        group_id INT NOT NULL,
        team1_id INT,
        team2_id INT,
        team1_score INT DEFAULT 0,
        team2_score INT DEFAULT 0,
        is_finished TINYINT DEFAULT 0,
        match_date DATETIME,
        FOREIGN KEY (group_id) REFERENCES groups_list(id),
        FOREIGN KEY (team1_id) REFERENCES teams(id),
        FOREIGN KEY (team2_id) REFERENCES teams(id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS knockout_matches (
        id INT AUTO_INCREMENT PRIMARY KEY,
        stage VARCHAR(10) NOT NULL,
        team1_id INT NULL,
        team2_id INT NULL,
        team1_score INT DEFAULT 0,
        team2_score INT DEFAULT 0,
        is_finished TINYINT DEFAULT 0,
        match_date DATETIME,
        FOREIGN KEY (team1_id) REFERENCES teams(id),
        FOREIGN KEY (team2_id) REFERENCES teams(id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS votes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        team_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_team (user_id, team_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS predictions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        match_id INT NOT NULL,
        winner_id INT NULL,
        result_type ENUM('home_win', 'draw', 'away_win') DEFAULT NULL,
        total_goals INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (match_id) REFERENCES group_matches(id) ON DELETE CASCADE,
        FOREIGN KEY (winner_id) REFERENCES teams(id),
        UNIQUE(user_id, match_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS knockout_predictions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        match_id INT NOT NULL,
        winner_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (match_id) REFERENCES knockout_matches(id) ON DELETE CASCADE,
        FOREIGN KEY (winner_id) REFERENCES teams(id),
        UNIQUE(user_id, match_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS rate_limits (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ip_address VARCHAR(45) NOT NULL,
        action VARCHAR(20) NOT NULL,
        username VARCHAR(50) NULL,
        attempts INT DEFAULT 1,
        first_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_ip_action (ip_address, action),
        INDEX idx_username_action (username, action)
    )");
    // Tabel rate limiting untuk API dan halaman web
    $pdo->exec("CREATE TABLE IF NOT EXISTS rate_limits_api (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    endpoint VARCHAR(100) NOT NULL,
    request_count INT DEFAULT 1,
    first_request TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_request TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ip_endpoint (ip_address, endpoint),
    INDEX idx_last_request (last_request)
)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS ticket_codes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(50) UNIQUE NOT NULL,
        ticket_amount INT NOT NULL,
        max_uses INT DEFAULT 1,
        used_count INT DEFAULT 0,
        expires_at DATETIME NULL,
        created_by INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (created_by) REFERENCES users(id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS ticket_code_redemptions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code_id INT NOT NULL,
        user_id INT NOT NULL,
        redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (code_id) REFERENCES ticket_codes(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY (code_id, user_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS rewards (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        points_required INT NOT NULL,
        stock INT NOT NULL DEFAULT 0,
        image_url VARCHAR(255),
        is_active TINYINT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS point_redeems (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        reward_id INT NOT NULL,
        points_spent INT NOT NULL,
        redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (reward_id) REFERENCES rewards(id) ON DELETE CASCADE
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS daily_claim_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        claim_date DATE NOT NULL,
        claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        ticket_added INT DEFAULT 1,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_date (user_id, claim_date)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leaderboard_snapshots (
        id INT AUTO_INCREMENT PRIMARY KEY,
        snapshot_date DATE NOT NULL,
        rank INT NOT NULL,
        user_id INT NOT NULL,
        username VARCHAR(50) NOT NULL,
        points INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_date (snapshot_date),
        INDEX idx_user (user_id),
        UNIQUE KEY unique_snapshot (snapshot_date, rank)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS admin_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_id INT NOT NULL,
        admin_username VARCHAR(50) NOT NULL,
        action VARCHAR(50) NOT NULL,
        details TEXT,
        target_user_id INT NULL,
        target_username VARCHAR(50) NULL,
        ip_address VARCHAR(45) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_admin (admin_id),
        INDEX idx_action (action),
        INDEX idx_created (created_at),
        FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
    )");
    // ========== TABEL LAPORAN HARIAN ==========
    $pdo->exec("CREATE TABLE IF NOT EXISTS daily_report_tournament (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_date DATE NOT NULL,
    store_name VARCHAR(100) NOT NULL,
    total_register INT DEFAULT 0,
    total_predictions INT DEFAULT 0,
    total_claim_daily INT DEFAULT 0,
    total_claim_ticket INT DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_report_date (report_date)
)");// Tambah kolom untuk detail prediksi jika belum ada
    try {
        $pdo->exec("ALTER TABLE daily_report_tournament ADD COLUMN total_group_predictions INT DEFAULT 0 AFTER total_predictions");
    } catch (Exception $e) {
    }
    try {
        $pdo->exec("ALTER TABLE daily_report_tournament ADD COLUMN total_knockout_predictions INT DEFAULT 0 AFTER total_group_predictions");
    } catch (Exception $e) {
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS daily_report_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_date DATE NOT NULL,
    store_name VARCHAR(100) NOT NULL,
    new_users_count INT DEFAULT 0,
    users_claim_ticket TEXT,
    users_using_point TEXT,
    prediction_history TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_report_date (report_date)
)");
    // ========== TABEL TRACKING VISITOR & ONLINE USER ==========
    $pdo->exec("CREATE TABLE IF NOT EXISTS user_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(128) NOT NULL,
        user_id INT NULL,
        ip_address VARCHAR(45),
        user_agent TEXT,
        first_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX(session_id),
        INDEX(last_activity)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS page_views (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(128) NOT NULL,
        user_id INT NULL,
        page_url VARCHAR(255) NOT NULL,
        referer VARCHAR(255),
        visited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX(session_id),
        INDEX(visited_at)
    )");

    // --- UPGRADE KOLOM (ALTER TABLE yang aman) ---
    $upgradeColumns = [
        "ALTER TABLE users ADD COLUMN tickets INT DEFAULT 0",
        "ALTER TABLE users ADD COLUMN last_daily_claim DATE DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN points INT DEFAULT 0",
        "ALTER TABLE users ADD COLUMN phone_number VARCHAR(20) UNIQUE DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN is_phone_verified TINYINT DEFAULT 0",
        "ALTER TABLE group_matches MODIFY match_date DATETIME",
        "ALTER TABLE knockout_matches MODIFY match_date DATETIME",
        "ALTER TABLE predictions ADD COLUMN result_type ENUM('home_win', 'draw', 'away_win') DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN total_goals INT DEFAULT NULL",
        "ALTER TABLE predictions MODIFY winner_id INT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN team1_code VARCHAR(10) DEFAULT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN team2_code VARCHAR(10) DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN points_awarded TINYINT DEFAULT 0",
        "ALTER TABLE knockout_predictions ADD COLUMN points_awarded TINYINT DEFAULT 0",
        "ALTER TABLE predictions ADD COLUMN team1_score_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN team2_score_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN total_goals_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN corners_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN yellow_cards_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN red_cards_pred INT DEFAULT NULL",
        "ALTER TABLE predictions ADD COLUMN offsides_pred INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN total_goals_pred INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN corners_pred INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN yellow_cards_pred INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN red_cards_pred INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN offsides_pred INT DEFAULT NULL",
        "ALTER TABLE point_redeems ADD COLUMN username VARCHAR(50) DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN team1_score_pred_final INT DEFAULT NULL",
        "ALTER TABLE knockout_predictions ADD COLUMN team2_score_pred_final INT DEFAULT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN winner_id INT NULL DEFAULT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN team1_score_normal INT DEFAULT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN team2_score_normal INT DEFAULT NULL",
        "ALTER TABLE knockout_matches ADD COLUMN match_extension ENUM('none','et','pen') DEFAULT 'none'",
        "ALTER TABLE knockout_predictions MODIFY winner_id INT NULL"
    ];
    foreach ($upgradeColumns as $sql) {
        try {
            $pdo->exec($sql);
        } catch (Exception $e) {
            // Kolom atau foreign key mungkin sudah ada, abaikan error
        }
    }

    // Tambahan foreign key untuk winner_id di knockout_matches (jika belum ada)
    try {
        $pdo->exec("ALTER TABLE knockout_matches ADD FOREIGN KEY (winner_id) REFERENCES teams(id) ON DELETE SET NULL");
    } catch (Exception $e) {
    }

    // ==================== FUNGSI HELPER UNTUK KONVERSI WAKTU ====================
    /**
     * Konversi waktu dari WIB ke UTC (digunakan saat menyimpan ke database)
     * @param string|null $wibDateTime Waktu dalam format 'Y-m-d H:i:s' atau 'Y-m-d\TH:i'
     * @return string|null Waktu dalam UTC format 'Y-m-d H:i:s'
     */
    function wibToUtc($wibDateTime)
    {
        if (empty($wibDateTime))
            return null;
        $datetime = new DateTime($wibDateTime, new DateTimeZone('Asia/Jakarta'));
        $datetime->setTimezone(new DateTimeZone('UTC'));
        return $datetime->format('Y-m-d H:i:s');
    }

    /**
     * Konversi waktu dari UTC ke WIB (format datetime biasa, untuk tampilan internal)
     * @param string|null $utcDateTime Waktu dalam UTC format 'Y-m-d H:i:s'
     * @return string|null Waktu dalam WIB format 'Y-m-d H:i:s'
     */
    function utcToWib($utcDateTime)
    {
        if (empty($utcDateTime))
            return null;
        $datetime = new DateTime($utcDateTime, new DateTimeZone('UTC'));
        $datetime->setTimezone(new DateTimeZone('Asia/Jakarta'));
        return $datetime->format('Y-m-d H:i:s');
    }

    /**
     * Konversi waktu dari UTC ke format ISO 8601 dengan timezone +07:00
     * Digunakan untuk dikirim ke frontend (JavaScript)
     * @param string|null $utcDateTime Waktu dalam UTC format 'Y-m-d H:i:s'
     * @return string|null Contoh: "2026-06-15T20:00:00+07:00"
     */
    function utcToWibIso($utcDateTime)
    {
        if (empty($utcDateTime))
            return null;
        $datetime = new DateTime($utcDateTime, new DateTimeZone('UTC'));
        $datetime->setTimezone(new DateTimeZone('Asia/Jakarta'));
        return $datetime->format('Y-m-d\TH:i:sP');
    }
    // ========== FUNGSI TRACKING PAGE VIEW (REALISTIS, TANPA INTERVAL, CEK LAST PAGE) ==========
    /**
     * Tracking page view yang lebih realistis:
     * - Admin (is_admin=1) tidak dicatat
     * - Tidak mencatat jika halaman yang sama dikunjungi berturut-turut (refresh / klik menu sama)
     * - Catat jika pindah ke halaman berbeda
     * - Update last_activity di user_sessions untuk online user setiap request
     */
    function trackPageView($pdo, $userId, $pageUrl, $sessionId, $ip)
    {
        // JIKA ADMIN, KELUARKAN DARI TRACKING
        if ($userId !== null) {
            $stmt = $pdo->prepare("SELECT is_admin FROM users WHERE id = ?");
            $stmt->execute([$userId]);
            $isAdmin = $stmt->fetchColumn();
            if ($isAdmin == 1) {
                return;
            }
        }

        // Normalisasi URL (hapus parameter query, biarkan path)
        $pageUrl = strtok($pageUrl, '?');

        // Cek apakah halaman ini sama dengan halaman terakhir yang dikunjungi (dalam session)
        $lastPage = $_SESSION['last_page'] ?? null;

        if ($lastPage === $pageUrl) {
            // Halaman sama dengan sebelumnya -> tidak catat page view (refresh atau klik menu sama)
            // Tetap update online user (karena user tetap aktif)
            updateOnlineUser($pdo, $userId, $sessionId, $ip);
            return;
        }

        // Halaman berbeda -> catat page view
        $insert = $pdo->prepare("
        INSERT INTO page_views (session_id, user_id, page_url, referer) 
        VALUES (?, ?, ?, ?)
    ");
        $referer = $_SERVER['HTTP_REFERER'] ?? '';
        $insert->execute([$sessionId, $userId, $pageUrl, $referer]);

        // Simpan halaman terakhir ke session
        $_SESSION['last_page'] = $pageUrl;

        // Update online user
        updateOnlineUser($pdo, $userId, $sessionId, $ip);
    }

    /**
     * Update atau insert sesi user untuk mengetahui online user (1 menit terakhir)
     * - Admin tidak dimasukkan ke tabel user_sessions
     */
    function updateOnlineUser($pdo, $userId, $sessionId, $ip)
    {
        // JIKA ADMIN, KELUARKAN
        if ($userId !== null) {
            $stmt = $pdo->prepare("SELECT is_admin FROM users WHERE id = ?");
            $stmt->execute([$userId]);
            $isAdmin = $stmt->fetchColumn();
            if ($isAdmin == 1) {
                return;
            }
        }

        $userAgent = $_SERVER['HTTP_USER_AGENT'] ?? '';
        $check = $pdo->prepare("SELECT id FROM user_sessions WHERE session_id = ?");
        $check->execute([$sessionId]);
        if ($check->fetch()) {
            $update = $pdo->prepare("
                UPDATE user_sessions 
                SET user_id = ?, ip_address = ?, user_agent = ?, last_activity = NOW()
                WHERE session_id = ?
            ");
            $update->execute([$userId, $ip, $userAgent, $sessionId]);
        } else {
            $insert = $pdo->prepare("
                INSERT INTO user_sessions (session_id, user_id, ip_address, user_agent, first_activity, last_activity)
                VALUES (?, ?, ?, ?, NOW(), NOW())
            ");
            $insert->execute([$sessionId, $userId, $ip, $userAgent]);
        }
    }

    // ==================== RATE LIMITING (tidak berubah) ====================
    function checkRateLimit($pdo, $ip, $action, $username = null)
    {
        $window = 60;
        $maxAttempts = 5;
        $stmt = $pdo->prepare("DELETE FROM rate_limits WHERE last_attempt < DATE_SUB(NOW(), INTERVAL $window SECOND)");
        $stmt->execute();
        $stmt = $pdo->prepare("SELECT attempts, UNIX_TIMESTAMP(first_attempt) as first_ts FROM rate_limits WHERE ip_address = ? AND action = ?");
        $stmt->execute([$ip, $action]);
        $row = $stmt->fetch();
        if ($row && $row['attempts'] >= $maxAttempts) {
            $remaining = $window - (time() - $row['first_ts']);
            if ($remaining > 0)
                return false;
            else
                $pdo->prepare("DELETE FROM rate_limits WHERE ip_address = ? AND action = ?")->execute([$ip, $action]);
        }
        if ($action === 'login' && $username !== null) {
            $stmt = $pdo->prepare("SELECT attempts, UNIX_TIMESTAMP(first_attempt) as first_ts FROM rate_limits WHERE username = ? AND action = 'login'");
            $stmt->execute([$username]);
            $userRow = $stmt->fetch();
            if ($userRow && $userRow['attempts'] >= 3) {
                $remaining = $window - (time() - $userRow['first_ts']);
                if ($remaining > 0)
                    return false;
                else
                    $pdo->prepare("DELETE FROM rate_limits WHERE username = ? AND action = 'login'")->execute([$username]);
            }
        }
        return true;
    }

    function recordRateLimitAttempt($pdo, $ip, $action, $username = null)
    {
        $window = 60;
        $stmt = $pdo->prepare("SELECT id, attempts FROM rate_limits WHERE ip_address = ? AND action = ? AND last_attempt > DATE_SUB(NOW(), INTERVAL $window SECOND)");
        $stmt->execute([$ip, $action]);
        $row = $stmt->fetch();
        if ($row) {
            $update = $pdo->prepare("UPDATE rate_limits SET attempts = attempts + 1, last_attempt = NOW() WHERE id = ?");
            $update->execute([$row['id']]);
        } else {
            $insert = $pdo->prepare("INSERT INTO rate_limits (ip_address, action, username, attempts) VALUES (?, ?, ?, 1)");
            $insert->execute([$ip, $action, $username]);
        }
        if ($action === 'login' && $username !== null) {
            $stmt = $pdo->prepare("SELECT id, attempts FROM rate_limits WHERE username = ? AND action = 'login' AND last_attempt > DATE_SUB(NOW(), INTERVAL $window SECOND)");
            $stmt->execute([$username]);
            $userRow = $stmt->fetch();
            if ($userRow) {
                $update = $pdo->prepare("UPDATE rate_limits SET attempts = attempts + 1, last_attempt = NOW() WHERE id = ?");
                $update->execute([$userRow['id']]);
            } else {
                $insert = $pdo->prepare("INSERT INTO rate_limits (ip_address, action, username, attempts) VALUES (?, ?, ?, 1)");
                $insert->execute([$ip, 'login', $username]);
            }
        }
    }
    /**
     * Cek rate limit untuk API atau halaman web.
     * 
     * @param PDO $pdo
     * @param string $ip Alamat IP pengunjung
     * @param string $endpoint Nama endpoint (misal 'api.get_standings' atau 'page.index')
     * @param int $maxRequests Maksimum request yang diperbolehkan dalam window
     * @param int $windowSeconds Jendela waktu dalam detik (default 60)
     * @return bool True jika masih dalam batas, False jika melebihi batas
     */
    function checkRateLimitApi($pdo, $ip, $endpoint, $maxRequests = 60, $windowSeconds = 60)
    {
        $pdo->prepare("DELETE FROM rate_limits_api WHERE last_request < DATE_SUB(NOW(), INTERVAL 2 HOUR)")->execute();

        $stmt = $pdo->prepare("SELECT COUNT(*) as total FROM rate_limits_api 
                           WHERE ip_address = ? AND endpoint = ? 
                           AND last_request > DATE_SUB(NOW(), INTERVAL ? SECOND)");
        $stmt->execute([$ip, $endpoint, $windowSeconds]);
        $total = $stmt->fetchColumn();

        if ($total >= $maxRequests) {
            return false;
        }

        // Insert request baru
        $insert = $pdo->prepare("INSERT INTO rate_limits_api (ip_address, endpoint) VALUES (?, ?)");
        $insert->execute([$ip, $endpoint]);
        return true;
    }

    /**
     * Wrapper untuk mengirim response 429 Too Many Requests
     */
    function rateLimitExceeded($message = 'Too many requests. Please slow down.')
    {
        http_response_code(429);
        header('Retry-After: 60');
        die(json_encode(['error' => $message]));
    }

    // ==================== VALIDASI CSRF TOKEN (DENGAN HASH_EQUALS) ====================
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $excludedActions = ['update_leaderboard_snapshot'];
        $currentAction = $_GET['action'] ?? '';
        if (!in_array($currentAction, $excludedActions)) {
            $providedToken = $_SERVER['HTTP_X_CSRF_TOKEN'] ?? $_POST['csrf_token'] ?? '';
            $sessionToken = $_SESSION['csrf_token'] ?? '';
            if (empty($sessionToken) || !hash_equals($sessionToken, $providedToken)) {
                error_log("CSRF validation failed - Session token: " . ($sessionToken ?: 'empty') . ", Provided token: " . ($providedToken ?: 'empty'));
                http_response_code(403);
                die(json_encode(['error' => 'Security Error: CSRF Token Invalid']));
            } else {
                error_log("CSRF validation passed for action: $currentAction");
            }
        }
    }

    error_log("=== CONFIG.PHP END (success) ===");

} catch (\PDOException $e) {
    error_log("Database connection failed: " . $e->getMessage() . " in " . $e->getFile() . ":" . $e->getLine());
    die("Maaf, terjadi kesalahan teknis. Tim kami telah diberitahu.");
}
?>