<?php
require 'config.php';
$action = $_GET['action'] ?? '';

// Rate limiting untuk semua request API
$ip = $_SERVER['REMOTE_ADDR'];
$endpoint = 'api.' . $action; // action bisa kosong, tetap kita beri nama
if (empty($action))
    $endpoint = 'api.unknown';

// Batas: 60 request per menit per endpoint per IP
if (!checkRateLimitApi($pdo, $ip, $endpoint, 60, 60)) {
    rateLimitExceeded('Terlalu banyak permintaan. Coba lagi setelah 1 menit.');
}
function logError($e, $customMessage = '')
{
    $logMessage = date('Y-m-d H:i:s') . " - ";
    if ($customMessage)
        $logMessage .= $customMessage . " - ";
    $logMessage .= $e->getMessage() . " in " . $e->getFile() . ":" . $e->getLine() . "\n";
    $logMessage .= $e->getTraceAsString();
    error_log($logMessage);
}

// Fungsi escapeArray (tidak digunakan lagi, namun dipertahankan untuk kompatibilitas)
function escapeArray(&$item)
{
    if (is_array($item)) {
        foreach ($item as &$value)
            escapeArray($value);
    } else {
        $item = htmlspecialchars($item, ENT_QUOTES, 'UTF-8');
    }
}

// Fungsi untuk mencatat log aktivitas admin
function logAdminAction($pdo, $action, $details = '', $targetUserId = null, $targetUsername = null)
{
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        return; // hanya admin yang bisa mencatat log (seharusnya dipanggil di aksi admin)
    }
    $adminId = $_SESSION['user_id'] ?? 0;
    $adminUsername = $_SESSION['username'] ?? 'unknown';
    $ip = $_SERVER['REMOTE_ADDR'] ?? '';

    $stmt = $pdo->prepare("INSERT INTO admin_logs (admin_id, admin_username, action, details, target_user_id, target_username, ip_address) VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->execute([$adminId, $adminUsername, $action, $details, $targetUserId, $targetUsername, $ip]);
}

// Fungsi untuk menghitung poin dari prediksi yang benar setelah pertandingan selesai
function calculatePointsForMatch($pdo, $matchId, $isKnockout, $forceRecalc = false)
{
    if ($isKnockout) {
        $table = 'knockout_predictions';
        $refTable = 'knockout_matches';

        $stmt = $pdo->prepare("SELECT team1_id, team2_id, team1_score, team2_score, team1_score_normal, team2_score_normal, winner_id, is_finished, total_goals_actual, corners_actual, yellow_cards_actual, red_cards_actual, offsides_actual FROM $refTable WHERE id = ?");
        $stmt->execute([$matchId]);
        $match = $stmt->fetch();
        if (!$match)
            return;

        if ($match['is_finished'] != 1) {
            if ($forceRecalc) {
                $pdo->prepare("UPDATE $table SET points_awarded = 0 WHERE match_id = ?")->execute([$matchId]);
            }
            return;
        }

        if ($forceRecalc) {
            $pdo->prepare("UPDATE $table SET points_awarded = 0 WHERE match_id = ?")->execute([$matchId]);
        }

        $actual_winner_id = $match['winner_id'] ?? null;
        if ($actual_winner_id === null && $match['team1_score'] != $match['team2_score']) {
            $actual_winner_id = ($match['team1_score'] > $match['team2_score']) ? $match['team1_id'] : $match['team2_id'];
        }

        $actual_score_home = $match['team1_score_normal'] ?? $match['team1_score'];
        $actual_score_away = $match['team2_score_normal'] ?? $match['team2_score'];

        $actual_total_goals = $match['total_goals_actual'] ?? null;
        $actual_corners = $match['corners_actual'] ?? null;
        $actual_yellow = $match['yellow_cards_actual'] ?? null;
        $actual_red = $match['red_cards_actual'] ?? null;
        $actual_offsides = $match['offsides_actual'] ?? null;

        $predStmt = $pdo->prepare("SELECT id, user_id, winner_id, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM $table WHERE match_id = ?");
        $predStmt->execute([$matchId]);
        $predictions = $predStmt->fetchAll();

        foreach ($predictions as $pred) {
            if ($pred['points_awarded'])
                continue;

            $points_to_add = 0;

            if ($actual_winner_id !== null && $pred['winner_id'] == $actual_winner_id) {
                $points_to_add = 2;
            }

            if (
                $pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null
                && $pred['team1_score_pred'] == $actual_score_home
                && $pred['team2_score_pred'] == $actual_score_away
            ) {
                $points_to_add = 4; // override
            }

            // Total gol
            if ($actual_total_goals !== null && $pred['total_goals_pred'] !== null && $actual_total_goals == $pred['total_goals_pred']) {
                $points_to_add += 2;
            }
            // Tendangan pojok (kembali ke 7 poin)
            if ($actual_corners !== null && $pred['corners_pred'] !== null && $actual_corners == $pred['corners_pred']) {
                $points_to_add += 7;
            }
            // Kartu kuning (kembali ke 9 poin)
            if ($actual_yellow !== null && $pred['yellow_cards_pred'] !== null && $actual_yellow == $pred['yellow_cards_pred']) {
                $points_to_add += 9;
            }
            // Kartu merah (kembali ke 9 poin)
            if ($actual_red !== null && $pred['red_cards_pred'] !== null && $actual_red == $pred['red_cards_pred']) {
                $points_to_add += 9;
            }
            // Offside (kembali ke 11 poin)
            if ($actual_offsides !== null && $pred['offsides_pred'] !== null && $actual_offsides == $pred['offsides_pred']) {
                $points_to_add += 11;
            }

            if ($points_to_add > 0) {
                $update = $pdo->prepare("UPDATE users SET points = points + ? WHERE id = ?");
                $update->execute([$points_to_add, $pred['user_id']]);
                $pdo->prepare("UPDATE $table SET points_awarded = 1 WHERE id = ?")->execute([$pred['id']]);
            }
        }
    } else {
        // === GROUP STAGE ===
        $table = 'predictions';
        $refTable = 'group_matches';

        $stmt = $pdo->prepare("SELECT team1_score, team2_score, is_finished, total_goals_actual, corners_actual, yellow_cards_actual, red_cards_actual, offsides_actual FROM $refTable WHERE id = ?");
        $stmt->execute([$matchId]);
        $match = $stmt->fetch();
        if (!$match)
            return;

        if ($match['is_finished'] != 1) {
            if ($forceRecalc) {
                $pdo->prepare("UPDATE $table SET points_awarded = 0 WHERE match_id = ?")->execute([$matchId]);
            }
            return;
        }

        if ($forceRecalc) {
            $pdo->prepare("UPDATE $table SET points_awarded = 0 WHERE match_id = ?")->execute([$matchId]);
        }

        $team1_score = $match['team1_score'];
        $team2_score = $match['team2_score'];

        if ($team1_score > $team2_score)
            $actual_result = 'home_win';
        elseif ($team1_score < $team2_score)
            $actual_result = 'away_win';
        else
            $actual_result = 'draw';

        $actual_total_goals = $match['total_goals_actual'] ?? null;
        $actual_corners = $match['corners_actual'] ?? null;
        $actual_yellow = $match['yellow_cards_actual'] ?? null;
        $actual_red = $match['red_cards_actual'] ?? null;
        $actual_offsides = $match['offsides_actual'] ?? null;

        $predStmt = $pdo->prepare("SELECT id, user_id, result_type, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM $table WHERE match_id = ?");
        $predStmt->execute([$matchId]);
        $predictions = $predStmt->fetchAll();

        foreach ($predictions as $pred) {
            if ($pred['points_awarded'])
                continue;

            $points_to_add = 0;
            if ($pred['result_type'] == $actual_result) {
                $points_to_add = 2;
            }
            if (
                $pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null
                && $pred['team1_score_pred'] == $team1_score
                && $pred['team2_score_pred'] == $team2_score
            ) {
                $points_to_add = 4; // override
            }

            if ($actual_total_goals !== null && $pred['total_goals_pred'] !== null && $actual_total_goals == $pred['total_goals_pred']) {
                $points_to_add += 2;
            }
            if ($actual_corners !== null && $pred['corners_pred'] !== null && $actual_corners == $pred['corners_pred']) {
                $points_to_add += 7;
            }
            if ($actual_yellow !== null && $pred['yellow_cards_pred'] !== null && $actual_yellow == $pred['yellow_cards_pred']) {
                $points_to_add += 9;
            }
            if ($actual_red !== null && $pred['red_cards_pred'] !== null && $actual_red == $pred['red_cards_pred']) {
                $points_to_add += 9;
            }
            if ($actual_offsides !== null && $pred['offsides_pred'] !== null && $actual_offsides == $pred['offsides_pred']) {
                $points_to_add += 11;
            }

            if ($points_to_add > 0) {
                $update = $pdo->prepare("UPDATE users SET points = points + ? WHERE id = ?");
                $update->execute([$points_to_add, $pred['user_id']]);
                $pdo->prepare("UPDATE $table SET points_awarded = 1 WHERE id = ?")->execute([$pred['id']]);
            }
        }
    }
}



// Admin actions
$adminActions = ['update_group_match', 'create_group_match', 'delete_group_match', 'update_knockout'];
if (in_array($action, $adminActions)) {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized: Admin access required']);
        exit;
    }
}

if ($action == 'get_standings') {
    $groups = $pdo->query("SELECT * FROM groups_list")->fetchAll();
    foreach ($groups as &$group) {
        $stmt = $pdo->prepare("SELECT * FROM teams WHERE group_id = ? ORDER BY points DESC, (goals_for - goals_against) DESC, goals_for DESC");
        $stmt->execute([$group['id']]);
        $group['teams'] = $stmt->fetchAll();
    }
    echo json_encode($groups);
    exit;
}

if ($action == 'get_groups') {
    $groups = $pdo->query("SELECT * FROM groups_list")->fetchAll();
    echo json_encode($groups);
    exit;
}

if ($action == 'get_user_info' && isset($_SESSION['user_id'])) {
    $stmt = $pdo->prepare("SELECT id, username, tickets, is_admin, last_daily_claim, DATE(created_at) as joined_date, points, phone_number FROM users WHERE id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $user = $stmt->fetch();
    $spentStmt = $pdo->prepare("SELECT COALESCE(SUM(points_spent), 0) FROM point_redeems WHERE user_id = ? AND status = 'approved'");
    $spentStmt->execute([$_SESSION['user_id']]);
    $spentPoints = $spentStmt->fetchColumn();
    $user['available_points'] = max(0, $user['points'] - $spentPoints);
    echo json_encode($user);
    exit;
}

if ($action == 'vote' && isset($_SESSION['user_id'])) {
    $lastVote = $_SESSION['last_vote_time'] ?? 0;
    if (time() - $lastVote < 1) {
        http_response_code(429);
        echo json_encode(['error' => 'Terlalu banyak permintaan. Harap tunggu sebentar.']);
        exit;
    }

    $teamId = $_POST['team_id'] ?? 0;
    $userId = $_SESSION['user_id'];

    try {
        $pdo->beginTransaction();

        $uStmt = $pdo->prepare("SELECT tickets FROM users WHERE id = ? FOR UPDATE");
        $uStmt->execute([$userId]);
        $user = $uStmt->fetch();
        if (!$user || $user['tickets'] <= 0) {
            throw new Exception("Tiket tidak cukup.");
        }

        $checkStmt = $pdo->prepare("SELECT team_id FROM votes WHERE user_id = ? LIMIT 1");
        $checkStmt->execute([$userId]);
        $existingVote = $checkStmt->fetch();

        if ($existingVote) {
            if ($existingVote['team_id'] != $teamId) {
                throw new Exception("Anda hanya diperbolehkan memberikan suara ke satu negara. Anda sudah memilih negara lain.");
            }
        }

        $tStmt = $pdo->prepare("SELECT id FROM teams WHERE id = ?");
        $tStmt->execute([$teamId]);
        if (!$tStmt->fetch()) {
            throw new Exception("Tim tidak ditemukan.");
        }

        $pdo->prepare("UPDATE users SET tickets = tickets - 1 WHERE id = ?")->execute([$userId]);
        $pdo->prepare("INSERT INTO votes (user_id, team_id) VALUES (?, ?)")->execute([$userId, $teamId]);

        $pdo->commit();
        $_SESSION['last_vote_time'] = time();

        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        logError($e, 'Vote action failed');
        http_response_code(400);
        echo json_encode(['error' => $e->getMessage() ?: 'Gagal memproses vote. Silakan coba lagi.']);
    }
    exit;
}

if ($action == 'predict' && isset($_SESSION['user_id'])) {
    $matchId = $_POST['match_id'] ?? 0;
    $winnerId = $_POST['winner_id'] ?? null;
    $resultType = $_POST['result_type'] ?? null;
    $team1ScorePred = isset($_POST['team1_score_pred']) ? (int) $_POST['team1_score_pred'] : null;
    $team2ScorePred = isset($_POST['team2_score_pred']) ? (int) $_POST['team2_score_pred'] : null;
    $totalGoalsPred = isset($_POST['total_goals_pred']) ? (int) $_POST['total_goals_pred'] : null;
    $cornersPred = isset($_POST['corners_pred']) ? (int) $_POST['corners_pred'] : null;
    $yellowCardsPred = isset($_POST['yellow_cards_pred']) ? (int) $_POST['yellow_cards_pred'] : null;
    $redCardsPred = isset($_POST['red_cards_pred']) ? (int) $_POST['red_cards_pred'] : null;
    $offsidesPred = isset($_POST['offsides_pred']) ? (int) $_POST['offsides_pred'] : null;

    $userId = $_SESSION['user_id'];
    $isKnockout = ($_POST['is_knockout'] ?? '0') == '1';

    if (!$isKnockout) {
        $table = 'predictions';
        $refTable = 'group_matches';
        if ($resultType) {
            $mStmt = $pdo->prepare("SELECT team1_id, team2_id, is_finished, match_date FROM $refTable WHERE id = ?");
            $mStmt->execute([$matchId]);
            $match = $mStmt->fetch();
            if (!$match)
                throw new Exception("Pertandingan tidak ditemukan.");
            if ($resultType == 'home_win')
                $winnerId = $match['team1_id'];
            elseif ($resultType == 'away_win')
                $winnerId = $match['team2_id'];
            else
                $winnerId = null;
        }
    } else {
        $table = 'knockout_predictions';
        $refTable = 'knockout_matches';
        $resultType = null;
    }

    try {
        $pdo->beginTransaction();

        $mStmt = $pdo->prepare("SELECT team1_id, team2_id, is_finished, match_date FROM $refTable WHERE id = ?");
        $mStmt->execute([$matchId]);
        $match = $mStmt->fetch();
        if (!$match)
            throw new Exception("Pertandingan tidak ditemukan.");
        if ($match['is_finished'])
            throw new Exception("Pertandingan sudah berakhir.");

        if (empty($match['match_date'])) {
            throw new Exception("Jadwal pertandingan belum ditentukan. Prediksi tidak dapat dibuat.");
        }

        // PERBAIKAN: gunakan UTC untuk validasi karena match_date disimpan dalam UTC
        $tzUTC = new DateTimeZone('UTC');
        $now = new DateTime('now', $tzUTC);
        $matchDateTime = new DateTime($match['match_date'], $tzUTC);
        $cutoff = clone $matchDateTime;
        $cutoff->modify('-30 minutes');

        if ($now >= $cutoff) {
            throw new Exception("⏰ Prediksi ditutup 30 menit sebelum pertandingan dimulai.");
        }
        if ($now > $matchDateTime) {
            throw new Exception("🚫 Pertandingan sudah dimulai. Prediksi tidak dapat dibuat.");
        }

        $hasAnyPrediction = ($resultType !== null) ||
            ($team1ScorePred !== null && $team2ScorePred !== null) ||
            ($totalGoalsPred !== null) ||
            ($cornersPred !== null) ||
            ($yellowCardsPred !== null) ||
            ($redCardsPred !== null) ||
            ($offsidesPred !== null);
        if (!$hasAnyPrediction) {
            throw new Exception("Tidak ada data prediksi yang dikirim.");
        }

        if (!$isKnockout && $resultType !== null) {
            // validasi sudah cukup
        }
        if (!$isKnockout && $winnerId && $winnerId != $match['team1_id'] && $winnerId != $match['team2_id']) {
            throw new Exception("Pemenang tidak valid.");
        }
        if ($isKnockout && $winnerId !== null) {
            if ($winnerId == 0) {
                throw new Exception("Prediksi seri tidak diperbolehkan di babak knockout.");
            }
            if ($winnerId != $match['team1_id'] && $winnerId != $match['team2_id']) {
                throw new Exception("Pemenang tidak valid.");
            }
        }
        if ($team1ScorePred !== null && ($team1ScorePred < 0 || $team1ScorePred > 30)) {
            throw new Exception("Skor tim 1 harus antara 0 dan 30.");
        }
        if ($team2ScorePred !== null && ($team2ScorePred < 0 || $team2ScorePred > 30)) {
            throw new Exception("Skor tim 2 harus antara 0 dan 30.");
        }
        if ($totalGoalsPred !== null && ($totalGoalsPred < 0 || $totalGoalsPred > 50)) {
            throw new Exception("Total gol harus antara 0 dan 50.");
        }
        if ($cornersPred !== null && ($cornersPred < 0 || $cornersPred > 50)) {
            throw new Exception("Tendangan pojok harus antara 0 dan 50.");
        }
        if ($yellowCardsPred !== null && ($yellowCardsPred < 0 || $yellowCardsPred > 40)) {
            throw new Exception("Kartu kuning harus antara 0 dan 40.");
        }
        if ($redCardsPred !== null && ($redCardsPred < 0 || $redCardsPred > 20)) {
            throw new Exception("Kartu merah harus antara 0 dan 20.");
        }
        if ($offsidesPred !== null && ($offsidesPred < 0 || $offsidesPred > 50)) {
            throw new Exception("Offside harus antara 0 dan 50.");
        }

        $pStmt = $pdo->prepare("SELECT id, result_type, team1_score_pred, team2_score_pred, winner_id, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred FROM $table WHERE user_id = ? AND match_id = ?");
        $pStmt->execute([$userId, $matchId]);
        $existing = $pStmt->fetch();

        if ($existing) {
            $needTicket = 0;
            $updateFields = [];
            $params = [];

            if (!$isKnockout && $resultType !== null) {
                if ($existing['result_type'] !== null) {
                    throw new Exception("Anda sudah memprediksi pemenang untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "result_type = ?";
                    $params[] = $resultType;
                    $updateFields[] = "winner_id = ?";
                    $params[] = $winnerId;
                }
            }

            if ($isKnockout && $winnerId !== null) {
                if ($existing['winner_id'] !== null) {
                    throw new Exception("Anda sudah memprediksi pemenang untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    if ($winnerId == 0) {
                        throw new Exception("Prediksi seri tidak diperbolehkan di babak knockout.");
                    }
                    $needTicket++;
                    $updateFields[] = "winner_id = ?";
                    $params[] = $winnerId;
                }
            }

            if ($team1ScorePred !== null && $team2ScorePred !== null) {
                if ($existing['team1_score_pred'] !== null && $existing['team2_score_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi skor untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "team1_score_pred = ?";
                    $params[] = $team1ScorePred;
                    $updateFields[] = "team2_score_pred = ?";
                    $params[] = $team2ScorePred;
                }
            }

            if ($totalGoalsPred !== null) {
                if ($existing['total_goals_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi total gol untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "total_goals_pred = ?";
                    $params[] = $totalGoalsPred;
                }
            }
            if ($cornersPred !== null) {
                if ($existing['corners_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi tendangan pojok untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "corners_pred = ?";
                    $params[] = $cornersPred;
                }
            }
            if ($yellowCardsPred !== null) {
                if ($existing['yellow_cards_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi kartu kuning untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "yellow_cards_pred = ?";
                    $params[] = $yellowCardsPred;
                }
            }
            if ($redCardsPred !== null) {
                if ($existing['red_cards_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi kartu merah untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "red_cards_pred = ?";
                    $params[] = $redCardsPred;
                }
            }
            if ($offsidesPred !== null) {
                if ($existing['offsides_pred'] !== null) {
                    throw new Exception("Anda sudah memprediksi offside untuk pertandingan ini dan tidak dapat mengubahnya.");
                } else {
                    $needTicket++;
                    $updateFields[] = "offsides_pred = ?";
                    $params[] = $offsidesPred;
                }
            }

            if (empty($updateFields)) {
                throw new Exception("Tidak ada data prediksi baru yang dikirim.");
            }

            if ($needTicket > 0) {
                $uStmt = $pdo->prepare("SELECT tickets FROM users WHERE id = ? FOR UPDATE");
                $uStmt->execute([$userId]);
                $user = $uStmt->fetch();
                if ($user['tickets'] < $needTicket) {
                    throw new Exception("Tiket tidak cukup. Butuh $needTicket tiket. ");
                }
                $pdo->prepare("UPDATE users SET tickets = tickets - ? WHERE id = ?")->execute([$needTicket, $userId]);
            }

            $params[] = $userId;
            $params[] = $matchId;
            $sql = "UPDATE $table SET " . implode(", ", $updateFields) . " WHERE user_id = ? AND match_id = ?";
            $update = $pdo->prepare($sql);
            $update->execute($params);

            $pdo->commit();
            echo json_encode(['status' => 'success']);
            exit;
        }

        $totalBets = 0;
        if (!$isKnockout && $resultType !== null)
            $totalBets++;
        if ($isKnockout && $winnerId !== null)
            $totalBets++;
        if ($team1ScorePred !== null && $team2ScorePred !== null)
            $totalBets++;
        if ($totalGoalsPred !== null)
            $totalBets++;
        if ($cornersPred !== null)
            $totalBets++;
        if ($yellowCardsPred !== null)
            $totalBets++;
        if ($redCardsPred !== null)
            $totalBets++;
        if ($offsidesPred !== null)
            $totalBets++;
        if ($totalBets == 0) {
            throw new Exception("Tidak ada data prediksi yang dikirim.");
        }

        $uStmt = $pdo->prepare("SELECT tickets FROM users WHERE id = ? FOR UPDATE");
        $uStmt->execute([$userId]);
        $user = $uStmt->fetch();
        if ($user['tickets'] < $totalBets) {
            throw new Exception("Tiket tidak cukup. Butuh $totalBets tiket. ");
        }

        $pdo->prepare("UPDATE users SET tickets = tickets - ? WHERE id = ?")->execute([$totalBets, $userId]);

        if ($isKnockout) {
            $pdo->prepare("INSERT INTO $table (user_id, match_id, winner_id, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
                ->execute([$userId, $matchId, $winnerId, $team1ScorePred, $team2ScorePred, $totalGoalsPred, $cornersPred, $yellowCardsPred, $redCardsPred, $offsidesPred]);
        } else {
            $pdo->prepare("INSERT INTO $table (user_id, match_id, winner_id, result_type, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
                ->execute([$userId, $matchId, $winnerId, $resultType, $team1ScorePred, $team2ScorePred, $totalGoalsPred, $cornersPred, $yellowCardsPred, $redCardsPred, $offsidesPred]);
        }

        $pdo->commit();
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Predict action failed');
        http_response_code(400);
        echo json_encode(['error' => $e->getMessage()]);
    }
    exit;
}

if ($action == 'get_my_predictions' && isset($_SESSION['user_id'])) {
    $groupStmt = $pdo->prepare("SELECT id, match_id, winner_id, result_type, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM predictions WHERE user_id = ?");
    $groupStmt->execute([$_SESSION['user_id']]);
    $group = $groupStmt->fetchAll();
    $koStmt = $pdo->prepare("SELECT id, match_id, winner_id, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM knockout_predictions WHERE user_id = ?");
    $koStmt->execute([$_SESSION['user_id']]);
    $ko = $koStmt->fetchAll();
    echo json_encode(['group' => $group, 'knockout' => $ko]);
    exit;
}

if ($action == 'get_admin_data') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }
    $teams = $pdo->query("SELECT * FROM teams ORDER BY name ASC")->fetchAll();
    $groups = $pdo->query("SELECT * FROM groups_list ORDER BY name ASC")->fetchAll();
    $groupMatches = $pdo->query("
        SELECT m.*, g.name as group_name, t1.name as team1_name, t2.name as team2_name
        FROM group_matches m
        JOIN groups_list g ON m.group_id = g.id
        JOIN teams t1 ON m.team1_id = t1.id
        JOIN teams t2 ON m.team2_id = t2.id
        ORDER BY m.match_date ASC
    ")->fetchAll();
    $knockoutMatches = $pdo->query("
        SELECT m.*, t1.name as team1_name, t2.name as team2_name
        FROM knockout_matches m
        LEFT JOIN teams t1 ON m.team1_id = t1.id
        LEFT JOIN teams t2 ON m.team2_id = t2.id
        ORDER BY m.id ASC
    ")->fetchAll();

    // Konversi waktu UTC ke WIB+07:00 untuk frontend admin panel
    foreach ($groupMatches as &$m) {
        if (isset($m['match_date']) && $m['match_date']) {
            $m['match_date'] = utcToWibIso($m['match_date']);
        }
    }
    foreach ($knockoutMatches as &$m) {
        if (isset($m['match_date']) && $m['match_date']) {
            $m['match_date'] = utcToWibIso($m['match_date']);
        }
    }

    echo json_encode(['teams' => $teams, 'groups' => $groups, 'group_matches' => $groupMatches, 'knockout_matches' => $knockoutMatches]);
    exit;
}

if ($action == 'get_knockout') {
    $matches = $pdo->query("
        SELECT m.*, m.match_extension,
               t1.name as team1_name, t1.flag_url as team1_flag, 
               t2.name as team2_name, t2.flag_url as team2_flag,
               m.team1_code, m.team2_code
        FROM knockout_matches m
        LEFT JOIN teams t1 ON m.team1_id = t1.id
        LEFT JOIN teams t2 ON m.team2_id = t2.id
        ORDER BY m.id ASC
    ")->fetchAll();
    foreach ($matches as &$m) {
        if (isset($m['match_date']) && $m['match_date']) {
            $m['match_date'] = utcToWibIso($m['match_date']);
        }
    }
    echo json_encode($matches);
    exit;
}

if ($action == 'get_group_matches') {
    $matches = $pdo->query("
        SELECT m.*, g.name as group_name, t1.name as team1_name, t1.flag_url as team1_flag, t2.name as team2_name, t2.flag_url as team2_flag
        FROM group_matches m
        JOIN groups_list g ON m.group_id = g.id
        JOIN teams t1 ON m.team1_id = t1.id
        JOIN teams t2 ON m.team2_id = t2.id
        ORDER BY m.match_date ASC
    ")->fetchAll();
    foreach ($matches as &$m) {
        if (isset($m['match_date']) && $m['match_date']) {
            $m['match_date'] = utcToWibIso($m['match_date']);
        }
    }
    echo json_encode($matches);
    exit;
}

if ($action == 'get_players') {
    $teamId = isset($_GET['team_id']) ? (int) $_GET['team_id'] : 0;
    if ($teamId <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Team ID required']);
        exit;
    }
    $stmt = $pdo->prepare("SELECT name, position, jersey_number FROM players WHERE team_id = ? ORDER BY FIELD(position, 'goalkeeper', 'defender', 'midfielder', 'forward'), jersey_number");
    $stmt->execute([$teamId]);
    $players = $stmt->fetchAll();

    $coachStmt = $pdo->prepare("SELECT coach FROM teams WHERE id = ?");
    $coachStmt->execute([$teamId]);
    $coach = $coachStmt->fetchColumn();

    echo json_encode([
        'players' => $players,
        'coach' => $coach ?: null
    ]);
    exit;
}

if ($action == 'update_group_match') {
    $id = $_POST['id'] ?? 0;
    $t1 = $_POST['team1_id'] ?? 0;
    $t2 = $_POST['team2_id'] ?? 0;
    $s1 = (int) $_POST['team1_score'];
    $s2 = (int) $_POST['team2_score'];
    $fin = $_POST['is_finished'] ?? 0;
    $date = $_POST['match_date'] ?? null;
    // Konversi tanggal dari WIB ke UTC
    if ($date) {
        $date = wibToUtc($date);
    }
    $total_goals_actual = isset($_POST['total_goals_actual']) ? (int) $_POST['total_goals_actual'] : null;
    $corners_actual = isset($_POST['corners_actual']) ? (int) $_POST['corners_actual'] : null;
    $yellow_cards_actual = isset($_POST['yellow_cards_actual']) ? (int) $_POST['yellow_cards_actual'] : null;
    $red_cards_actual = isset($_POST['red_cards_actual']) ? (int) $_POST['red_cards_actual'] : null;
    $offsides_actual = isset($_POST['offsides_actual']) ? (int) $_POST['offsides_actual'] : null;

    try {
        $pdo->beginTransaction();
        $oldStmt = $pdo->prepare("SELECT is_finished FROM group_matches WHERE id = ?");
        $oldStmt->execute([$id]);
        $oldFinished = $oldStmt->fetchColumn();

        $oldStmt2 = $pdo->prepare("SELECT team1_id, team2_id FROM group_matches WHERE id = ?");
        $oldStmt2->execute([$id]);
        $oldTeams = $oldStmt2->fetch();

        $stmt = $pdo->prepare("UPDATE group_matches SET team1_id=?, team2_id=?, team1_score=?, team2_score=?, is_finished=?, match_date=?, total_goals_actual=?, corners_actual=?, yellow_cards_actual=?, red_cards_actual=?, offsides_actual=? WHERE id=?");
        $stmt->execute([$t1, $t2, $s1, $s2, $fin, $date, $total_goals_actual, $corners_actual, $yellow_cards_actual, $red_cards_actual, $offsides_actual, $id]);

        $recalc = function ($teamId) use ($pdo) {
            if (!$teamId)
                return;
            $s1 = $pdo->prepare("SELECT COUNT(*) as p, SUM(CASE WHEN team1_score > team2_score THEN 1 ELSE 0 END) as w, SUM(CASE WHEN team1_score = team2_score THEN 1 ELSE 0 END) as d, SUM(CASE WHEN team1_score < team2_score THEN 1 ELSE 0 END) as l, SUM(team1_score) as gf, SUM(team2_score) as ga FROM group_matches WHERE team1_id = ? AND is_finished = 1");
            $s1->execute([$teamId]);
            $res1 = $s1->fetch();
            $s2 = $pdo->prepare("SELECT COUNT(*) as p, SUM(CASE WHEN team2_score > team1_score THEN 1 ELSE 0 END) as w, SUM(CASE WHEN team2_score = team1_score THEN 1 ELSE 0 END) as d, SUM(CASE WHEN team2_score < team1_score THEN 1 ELSE 0 END) as l, SUM(team2_score) as gf, SUM(team1_score) as ga FROM group_matches WHERE team2_id = ? AND is_finished = 1");
            $s2->execute([$teamId]);
            $res2 = $s2->fetch();
            $p = max(0, ($res1['p'] ?? 0) + ($res2['p'] ?? 0));
            $w = max(0, ($res1['w'] ?? 0) + ($res2['w'] ?? 0));
            $d = max(0, ($res1['d'] ?? 0) + ($res2['d'] ?? 0));
            $l = max(0, ($res1['l'] ?? 0) + ($res2['l'] ?? 0));
            $gf = max(0, ($res1['gf'] ?? 0) + ($res2['gf'] ?? 0));
            $ga = max(0, ($res1['ga'] ?? 0) + ($res2['ga'] ?? 0));
            $pts = ($w * 3) + ($d * 1);
            $pdo->prepare("UPDATE teams SET played=?, won=?, drawn=?, lost=?, goals_for=?, goals_against=?, points=? WHERE id=?")->execute([$p, $w, $d, $l, $gf, $ga, $pts, $teamId]);
        };
        $recalc($t1);
        $recalc($t2);
        if ($oldTeams) {
            if ($oldTeams['team1_id'] != $t1)
                $recalc($oldTeams['team1_id']);
            if ($oldTeams['team2_id'] != $t2)
                $recalc($oldTeams['team2_id']);
        }

        if ($fin == 1) {
            $predStmt = $pdo->prepare("SELECT id, user_id, result_type, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred FROM predictions WHERE match_id = ? AND points_awarded = 1");
            $predStmt->execute([$id]);
            $oldPredictions = $predStmt->fetchAll();
            foreach ($oldPredictions as $old) {
                $oldPoints = 0;
                if ($old['team1_score_pred'] !== null && $old['team2_score_pred'] !== null) {
                    $oldPoints = 4;
                } elseif ($old['result_type'] !== null) {
                    $oldPoints = 2;
                }
                if ($old['total_goals_pred'] !== null)
                    $oldPoints += 2;
                if ($old['corners_pred'] !== null)
                    $oldPoints += 7;
                if ($old['yellow_cards_pred'] !== null)
                    $oldPoints += 9;
                if ($old['red_cards_pred'] !== null)
                    $oldPoints += 9;
                if ($old['offsides_pred'] !== null)
                    $oldPoints += 11;

                if ($oldPoints > 0) {
                    $pdo->prepare("UPDATE users SET points = points - ? WHERE id = ?")->execute([$oldPoints, $old['user_id']]);
                }
            }
            calculatePointsForMatch($pdo, $id, false, true);
        } else {
            $predStmt = $pdo->prepare("SELECT id, user_id, result_type, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred FROM predictions WHERE match_id = ? AND points_awarded = 1");
            $predStmt->execute([$id]);
            $predictions = $predStmt->fetchAll();
            foreach ($predictions as $pred) {
                $points_to_subtract = 0;
                if ($pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null) {
                    $points_to_subtract = 4;
                } elseif ($pred['result_type'] !== null) {
                    $points_to_subtract = 2;
                }
                if ($pred['total_goals_pred'] !== null)
                    $points_to_subtract += 2;
                if ($pred['corners_pred'] !== null)
                    $points_to_subtract += 7;
                if ($pred['yellow_cards_pred'] !== null)
                    $points_to_subtract += 9;
                if ($pred['red_cards_pred'] !== null)
                    $points_to_subtract += 9;
                if ($pred['offsides_pred'] !== null)
                    $points_to_subtract += 11;

                if ($points_to_subtract > 0) {
                    $update = $pdo->prepare("UPDATE users SET points = points - ? WHERE id = ?");
                    $update->execute([$points_to_subtract, $pred['user_id']]);
                }
            }
            $pdo->prepare("UPDATE predictions SET points_awarded = 0 WHERE match_id = ?")->execute([$id]);
        }

        $pdo->commit();
        // Log admin
        logAdminAction($pdo, 'update_group_match', "Match ID: $id, Team1 ID: $t1, Team2 ID: $t2, Score: $s1-$s2, Finished: $fin, Date: $date");
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Update group match failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal memperbarui pertandingan grup.']);
    }
    exit;
}

if ($action == 'create_group_match') {
    $gid = $_POST['group_id'] ?? 0;
    $t1 = $_POST['team1_id'] ?? 0;
    $t2 = $_POST['team2_id'] ?? 0;
    $date = $_POST['match_date'] ?? null;
    // Konversi tanggal dari WIB ke UTC
    if ($date) {
        $date = wibToUtc($date);
    }
    try {
        $stmt = $pdo->prepare("INSERT INTO group_matches (group_id, team1_id, team2_id, match_date) VALUES (?,?,?,?)");
        $stmt->execute([$gid, $t1, $t2, $date]);
        logAdminAction($pdo, 'create_group_match', "Group ID: $gid, Team1 ID: $t1, Team2 ID: $t2, Date: $date");
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Create group match failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal membuat pertandingan grup.']);
    }
    exit;
}

if ($action == 'delete_group_match') {
    $id = $_POST['id'] ?? 0;
    try {
        $pdo->beginTransaction();
        $oldStmt = $pdo->prepare("SELECT team1_id, team2_id, is_finished FROM group_matches WHERE id = ?");
        $oldStmt->execute([$id]);
        $old = $oldStmt->fetch();
        $pdo->prepare("DELETE FROM group_matches WHERE id = ?")->execute([$id]);
        if ($old && $old['is_finished']) {
            $recalc = function ($teamId) use ($pdo) {
                if (!$teamId)
                    return;
                $s1 = $pdo->prepare("SELECT COUNT(*) as p, SUM(CASE WHEN team1_score > team2_score THEN 1 ELSE 0 END) as w, SUM(CASE WHEN team1_score = team2_score THEN 1 ELSE 0 END) as d, SUM(CASE WHEN team1_score < team2_score THEN 1 ELSE 0 END) as l, SUM(team1_score) as gf, SUM(team2_score) as ga FROM group_matches WHERE team1_id = ? AND is_finished = 1");
                $s1->execute([$teamId]);
                $res1 = $s1->fetch();
                $s2 = $pdo->prepare("SELECT COUNT(*) as p, SUM(CASE WHEN team2_score > team1_score THEN 1 ELSE 0 END) as w, SUM(CASE WHEN team2_score = team1_score THEN 1 ELSE 0 END) as d, SUM(CASE WHEN team2_score < team1_score THEN 1 ELSE 0 END) as l, SUM(team2_score) as gf, SUM(team1_score) as ga FROM group_matches WHERE team2_id = ? AND is_finished = 1");
                $s2->execute([$teamId]);
                $res2 = $s2->fetch();
                $p = max(0, ($res1['p'] ?? 0) + ($res2['p'] ?? 0));
                $w = max(0, ($res1['w'] ?? 0) + ($res2['w'] ?? 0));
                $d = max(0, ($res1['d'] ?? 0) + ($res2['d'] ?? 0));
                $l = max(0, ($res1['l'] ?? 0) + ($res2['l'] ?? 0));
                $gf = max(0, ($res1['gf'] ?? 0) + ($res2['gf'] ?? 0));
                $ga = max(0, ($res1['ga'] ?? 0) + ($res2['ga'] ?? 0));
                $pts = ($w * 3) + ($d * 1);
                $pdo->prepare("UPDATE teams SET played=?, won=?, drawn=?, lost=?, goals_for=?, goals_against=?, points=? WHERE id=?")->execute([$p, $w, $d, $l, $gf, $ga, $pts, $teamId]);
            };
            $recalc($old['team1_id']);
            $recalc($old['team2_id']);
        }
        $pdo->commit();
        logAdminAction($pdo, 'delete_group_match', "Match ID: $id");
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Delete group match failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal menghapus pertandingan grup.']);
    }
    exit;
}

if ($action == 'update_knockout') {
    $id = $_POST['id'] ?? 0;
    $t1 = $_POST['team1_id'] ?: null;
    $t2 = $_POST['team2_id'] ?: null;
    $s1 = (int) $_POST['team1_score'];
    $s2 = (int) $_POST['team2_score'];
    $fin = $_POST['is_finished'] ?? 0;
    $date = $_POST['match_date'] ?? null;
    // Konversi tanggal dari WIB ke UTC
    if ($date) {
        $date = wibToUtc($date);
    }
    $winnerId = $_POST['winner_id'] ?? null;
    if ($winnerId === '' || $winnerId === 'null')
        $winnerId = null;

    $s1_normal = isset($_POST['team1_score_normal']) ? (int) $_POST['team1_score_normal'] : null;
    $s2_normal = isset($_POST['team2_score_normal']) ? (int) $_POST['team2_score_normal'] : null;
    $matchExtension = $_POST['match_extension'] ?? 'none';
    if (!in_array($matchExtension, ['none', 'et', 'pen']))
        $matchExtension = 'none';

    $total_goals_actual = isset($_POST['total_goals_actual']) ? (int) $_POST['total_goals_actual'] : null;
    $corners_actual = isset($_POST['corners_actual']) ? (int) $_POST['corners_actual'] : null;
    $yellow_cards_actual = isset($_POST['yellow_cards_actual']) ? (int) $_POST['yellow_cards_actual'] : null;
    $red_cards_actual = isset($_POST['red_cards_actual']) ? (int) $_POST['red_cards_actual'] : null;
    $offsides_actual = isset($_POST['offsides_actual']) ? (int) $_POST['offsides_actual'] : null;

    $auto_winner_id = null;
    if ($s1_normal !== null && $s2_normal !== null) {
        if ($s1_normal > $s2_normal) {
            $auto_winner_id = $t1;
        } elseif ($s1_normal < $s2_normal) {
            $auto_winner_id = $t2;
        } else {
            if ($s1 > $s2) {
                $auto_winner_id = $t1;
            } elseif ($s1 < $s2) {
                $auto_winner_id = $t2;
            }
        }
    }
    if (empty($winnerId) && $auto_winner_id !== null) {
        $winnerId = $auto_winner_id;
    }

    if ($winnerId !== null) {
        if (empty($t1) || empty($t2)) {
            throw new Exception("Tidak dapat menentukan pemenang karena salah satu tim belum ditentukan.");
        }
        if ($winnerId != $t1 && $winnerId != $t2) {
            throw new Exception("Winner ID tidak valid: harus ID dari tim yang bertanding.");
        }
    }

    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("UPDATE knockout_matches SET 
            team1_id = ?, team2_id = ?, 
            team1_score = ?, team2_score = ?, 
            team1_score_normal = ?, team2_score_normal = ?,
            winner_id = ?,
            match_extension = ?,
            is_finished = ?, match_date = ?,
            total_goals_actual = ?, corners_actual = ?, yellow_cards_actual = ?, red_cards_actual = ?, offsides_actual = ?
            WHERE id = ?");
        $stmt->execute([
            $t1,
            $t2,
            $s1,
            $s2,
            $s1_normal,
            $s2_normal,
            $winnerId,
            $matchExtension,
            $fin,
            $date,
            $total_goals_actual,
            $corners_actual,
            $yellow_cards_actual,
            $red_cards_actual,
            $offsides_actual,
            $id
        ]);

        if ($fin == 1) {
            $predStmt = $pdo->prepare("SELECT id, user_id, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM knockout_predictions WHERE match_id = ? AND points_awarded = 1");
            $predStmt->execute([$id]);
            $oldPredictions = $predStmt->fetchAll();
            foreach ($oldPredictions as $old) {
                $oldPoints = 0;
                if ($old['team1_score_pred'] !== null && $old['team2_score_pred'] !== null) {
                    $oldPoints = 4;
                } elseif ($old['winner_id'] !== null && $old['winner_id'] != 0) {
                    $oldPoints = 2;
                }
                if ($old['total_goals_pred'] !== null)
                    $oldPoints += 2;
                if ($old['corners_pred'] !== null)
                    $oldPoints += 7;
                if ($old['yellow_cards_pred'] !== null)
                    $oldPoints += 9;
                if ($old['red_cards_pred'] !== null)
                    $oldPoints += 9;
                if ($old['offsides_pred'] !== null)
                    $oldPoints += 11;

                if ($oldPoints > 0) {
                    $pdo->prepare("UPDATE users SET points = points - ? WHERE id = ?")->execute([$oldPoints, $old['user_id']]);
                }
            }
            calculatePointsForMatch($pdo, $id, true, true);
        } else {
            $predStmt = $pdo->prepare("SELECT id, user_id, team1_score_pred, team2_score_pred, total_goals_pred, corners_pred, yellow_cards_pred, red_cards_pred, offsides_pred, points_awarded FROM knockout_predictions WHERE match_id = ? AND points_awarded = 1");
            $predStmt->execute([$id]);
            $predictions = $predStmt->fetchAll();
            foreach ($predictions as $pred) {
                $points_to_subtract = 0;
                if ($pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null) {
                    $points_to_subtract = 4;
                } elseif ($pred['winner_id'] !== null && $pred['winner_id'] != 0) {
                    $points_to_subtract = 2;
                }
                if ($pred['total_goals_pred'] !== null)
                    $points_to_subtract += 2;
                if ($pred['corners_pred'] !== null)
                    $points_to_subtract += 7;
                if ($pred['yellow_cards_pred'] !== null)
                    $points_to_subtract += 9;
                if ($pred['red_cards_pred'] !== null)
                    $points_to_subtract += 9;
                if ($pred['offsides_pred'] !== null)
                    $points_to_subtract += 11;

                if ($points_to_subtract > 0) {
                    $update = $pdo->prepare("UPDATE users SET points = points - ? WHERE id = ?");
                    $update->execute([$points_to_subtract, $pred['user_id']]);
                }
            }
            $pdo->prepare("UPDATE knockout_predictions SET points_awarded = 0 WHERE match_id = ?")->execute([$id]);
        }

        $pdo->commit();
        logAdminAction($pdo, 'update_knockout', "Match ID: $id, Team1 ID: $t1, Team2 ID: $t2, Score: $s1-$s2, Finished: $fin, Winner: $winnerId, Extension: $matchExtension");
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Update knockout match failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal memperbarui pertandingan knockout.']);
    }
    exit;
}

if ($action == 'get_teams') {
    $teams = $pdo->query("SELECT * FROM teams ORDER BY name ASC")->fetchAll();
    echo json_encode($teams);
    exit;
}

if ($action == 'get_team_vote_counts') {
    $stmt = $pdo->query("
        SELECT t.id, t.name, COUNT(v.id) as total_votes
        FROM teams t
        LEFT JOIN votes v ON t.id = v.team_id
        GROUP BY t.id
        ORDER BY total_votes DESC
    ");
    $counts = $stmt->fetchAll();
    echo json_encode($counts);
    exit;
}

if ($action == 'get_user_vote_stats' && isset($_SESSION['user_id'])) {
    $userId = $_SESSION['user_id'];
    $stmt = $pdo->prepare("
        SELECT COUNT(*) as total_votes, 
               (SELECT team_id FROM votes WHERE user_id = ? LIMIT 1) as chosen_team_id
        FROM votes WHERE user_id = ?
    ");
    $stmt->execute([$userId, $userId]);
    $stats = $stmt->fetch();

    if ($stats['chosen_team_id']) {
        $teamStmt = $pdo->prepare("SELECT name, flag_url FROM teams WHERE id = ?");
        $teamStmt->execute([$stats['chosen_team_id']]);
        $team = $teamStmt->fetch();
        $stats['chosen_team_name'] = $team['name'] ?? null;
        $stats['chosen_team_flag'] = $team['flag_url'] ?? null;
    } else {
        $stats['chosen_team_name'] = null;
        $stats['chosen_team_flag'] = null;
    }

    echo json_encode($stats);
    exit;
}

if ($action == 'get_leaderboard') {
    $today = date('Y-m-d');
    $stmt = $pdo->prepare("
        SELECT username as name, points, rank
        FROM leaderboard_snapshots
        WHERE snapshot_date = ?
        ORDER BY rank ASC
        LIMIT 10
    ");
    $stmt->execute([$today]);
    $leaderboard = $stmt->fetchAll();

    // Fallback dihapus – jika tidak ada snapshot, kembalikan array kosong
    echo json_encode($leaderboard);
    exit;
}

// === ADMIN: Get all users for ticket management ===
if ($action == 'get_all_users' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $users = $pdo->query("SELECT id, username, tickets, last_daily_claim, points, created_at FROM users ORDER BY id ASC")->fetchAll();
    echo json_encode($users);
    exit;
}

// === ADMIN: Get paginated users (baru, untuk admin panel) ===
if ($action == 'get_users_paginated' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $page = isset($_GET['page']) ? max(1, (int) $_GET['page']) : 1;
    $limit = isset($_GET['limit']) ? min(100, max(1, (int) $_GET['limit'])) : 10;
    // Maks 100 baris
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $offset = ($page - 1) * $limit;

    // Query total count
    $countSql = "SELECT COUNT(*) FROM users";
    $countParams = [];
    if ($search !== '') {
        $countSql .= " WHERE username LIKE ?";
        $countParams[] = "%$search%";
    }
    $countStmt = $pdo->prepare($countSql);
    $countStmt->execute($countParams);
    $total = $countStmt->fetchColumn();

    // Query data
    $sql = "SELECT id, username, tickets, last_daily_claim, points FROM users";
    if ($search !== '') {
        $sql .= " WHERE username LIKE ?";
    }
    $sql .= " ORDER BY id ASC LIMIT ? OFFSET ?";
    $stmt = $pdo->prepare($sql);
    $params = [];
    if ($search !== '') {
        $params[] = "%$search%";
    }
    $params[] = $limit;
    $params[] = $offset;
    $stmt->execute($params);
    $users = $stmt->fetchAll();

    echo json_encode([
        'users' => $users,
        'total' => $total,
        'page' => $page,
        'limit' => $limit,
        'total_pages' => ceil($total / $limit)
    ]);
    exit;
}

// === ADMIN: Add tickets to specific user ===
if ($action == 'add_tickets' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $userId = $_POST['user_id'] ?? 0;
    $amount = (int) ($_POST['amount'] ?? 0);
    if ($userId <= 0 || $amount <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Invalid user or amount']);
        exit;
    }
    try {
        // Ambil username target untuk log
        $stmtUser = $pdo->prepare("SELECT username FROM users WHERE id = ?");
        $stmtUser->execute([$userId]);
        $targetUser = $stmtUser->fetch();
        $targetUsername = $targetUser ? $targetUser['username'] : null;

        $stmt = $pdo->prepare("UPDATE users SET tickets = tickets + ? WHERE id = ?");
        $stmt->execute([$amount, $userId]);

        logAdminAction($pdo, 'add_tickets', "Menambah $amount tiket", $userId, $targetUsername);

        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Add tickets failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal menambah tiket.']);
    }
    exit;
}

// === ADMIN: Remove tickets from specific user ===
if ($action == 'remove_tickets' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $userId = $_POST['user_id'] ?? 0;
    $amount = (int) ($_POST['amount'] ?? 0);
    if ($userId <= 0 || $amount <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Invalid user or amount']);
        exit;
    }
    try {
        $stmtUser = $pdo->prepare("SELECT username FROM users WHERE id = ?");
        $stmtUser->execute([$userId]);
        $targetUser = $stmtUser->fetch();
        $targetUsername = $targetUser ? $targetUser['username'] : null;

        $stmt = $pdo->prepare("UPDATE users SET tickets = GREATEST(tickets - ?, 0) WHERE id = ?");
        $stmt->execute([$amount, $userId]);

        logAdminAction($pdo, 'remove_tickets', "Mengurangi $amount tiket", $userId, $targetUsername);

        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Remove tickets failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal mengurangi tiket.']);
    }
    exit;
}

// === ADMIN: Adjust user points ===
if ($action == 'adjust_points' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $userId = $_POST['user_id'] ?? 0;
    $points = (int) ($_POST['points'] ?? 0);

    if ($userId <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'User ID tidak valid']);
        exit;
    }

    try {
        $stmtUser = $pdo->prepare("SELECT username FROM users WHERE id = ?");
        $stmtUser->execute([$userId]);
        $targetUser = $stmtUser->fetch();
        $targetUsername = $targetUser ? $targetUser['username'] : null;

        $stmt = $pdo->prepare("UPDATE users SET points = points + ? WHERE id = ?");
        $stmt->execute([$points, $userId]);

        logAdminAction($pdo, 'adjust_points', "Menambah $points poin", $userId, $targetUsername);

        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Adjust points failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal mengubah poin.']);
    }
    exit;
}

// ========== BARU: ADMIN UBAH PASSWORD USER ==========
if ($action == 'admin_change_password') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized: Admin access required']);
        exit;
    }

    $userId = (int) ($_POST['user_id'] ?? 0);
    $newPassword = trim($_POST['new_password'] ?? '');

    if ($userId <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'User ID tidak valid']);
        exit;
    }
    if (strlen($newPassword) < 6) {
        http_response_code(400);
        echo json_encode(['error' => 'Password minimal 6 karakter']);
        exit;
    }

    try {
        $stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        if (!$stmt->fetch()) {
            throw new Exception('User tidak ditemukan');
        }
        $stmtUser = $pdo->prepare("SELECT username FROM users WHERE id = ?");
        $stmtUser->execute([$userId]);
        $targetUser = $stmtUser->fetch();
        $targetUsername = $targetUser ? $targetUser['username'] : null;

        $hashed = password_hash($newPassword, PASSWORD_DEFAULT);
        $update = $pdo->prepare("UPDATE users SET password_hash = ? WHERE id = ?");
        $update->execute([$hashed, $userId]);

        logAdminAction($pdo, 'change_password', "Mengubah password user", $userId, $targetUsername);

        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Admin change password failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal mengubah password: ' . $e->getMessage()]);
    }
    exit;
}

// === ADMIN: Force update leaderboard snapshot (manual) ===
if ($action == 'admin_force_leaderboard' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    try {
        $today = date('Y-m-d');
        $pdo->prepare("DELETE FROM leaderboard_snapshots WHERE snapshot_date = ?")->execute([$today]);

        $stmt = $pdo->query("
            SELECT id, username, points 
            FROM users 
            ORDER BY points DESC, username ASC
        ");
        $rankings = $stmt->fetchAll();

        $insert = $pdo->prepare("
            INSERT INTO leaderboard_snapshots (snapshot_date, rank, user_id, username, points) 
            VALUES (?, ?, ?, ?, ?)
        ");

        $rank = 1;
        foreach ($rankings as $user) {
            $insert->execute([$today, $rank, $user['id'], $user['username'], $user['points']]);
            $rank++;
        }

        logAdminAction($pdo, 'force_leaderboard_snapshot', "Manual snapshot tanggal $today");

        echo json_encode(['status' => 'success', 'message' => "Leaderboard snapshot for $today saved"]);
    } catch (Exception $e) {
        logError($e, 'Admin force leaderboard snapshot failed');
        http_response_code(500);
        echo json_encode(['error' => 'Failed to update leaderboard snapshot']);
    }
    exit;
}
// === ADMIN: Hapus semua snapshot leaderboard ===
if ($action == 'admin_delete_snapshots') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized: Admin access required']);
        exit;
    }

    try {
        $pdo->prepare("DELETE FROM leaderboard_snapshots")->execute();
        logAdminAction($pdo, 'delete_snapshots', "Menghapus semua snapshot leaderboard");
        echo json_encode(['status' => 'success', 'message' => 'All snapshots deleted']);
    } catch (Exception $e) {
        logError($e, 'Delete snapshots failed');
        http_response_code(500);
        echo json_encode(['error' => 'Failed to delete snapshots']);
    }
    exit;
}

// === DAILY LOGIN BONUS (manual claim) ===
if ($action == 'claim_daily_ticket') {
    if (!isset($_SESSION['user_id'])) {
        http_response_code(401);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }

    $userId = $_SESSION['user_id'];
    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("SELECT last_daily_claim, tickets FROM users WHERE id = ? FOR UPDATE");
        $stmt->execute([$userId]);
        $user = $stmt->fetch();
        if (!$user)
            throw new Exception("User tidak ditemukan");
        $today = date('Y-m-d');
        $claimed = false;
        $newTickets = (int) $user['tickets'];
        if ($user['last_daily_claim'] !== $today) {
            $updateStmt = $pdo->prepare("UPDATE users SET tickets = tickets + 1, last_daily_claim = ? WHERE id = ?");
            $updateStmt->execute([$today, $userId]);
            $newTickets++;
            $claimed = true;
            $pdo->prepare("INSERT INTO daily_claim_log (user_id, claim_date, ticket_added) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE claimed_at = NOW()")
                ->execute([$userId, $today]);
        }
        $pdo->commit();
        echo json_encode([
            'status' => 'success',
            'claimed' => $claimed,
            'tickets' => $newTickets,
            'message' => $claimed ? 'Selamat! Kamu mendapatkan 1 tiket harian.' : null
        ]);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Claim daily ticket failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal mengklaim tiket harian.']);
    }

    exit;
}

// === ADMIN: Generate ticket code ===
if ($action == 'generate_ticket_code' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $ticketAmount = (int) ($_POST['ticket_amount'] ?? 0);
    $maxUses = (int) ($_POST['max_uses'] ?? 1);
    $expiresAt = $_POST['expires_at'] ?? null;

    if ($ticketAmount <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Jumlah tiket harus lebih dari 0']);
        exit;
    }
    if ($maxUses <= 0)
        $maxUses = 1;

    $code = substr(bin2hex(random_bytes(5)), 0, 8);
    $stmt = $pdo->prepare("SELECT id FROM ticket_codes WHERE code = ?");
    while ($stmt->execute([$code]) && $stmt->fetch()) {
        $code = substr(bin2hex(random_bytes(5)), 0, 8);
    }

    try {
        $stmt = $pdo->prepare("INSERT INTO ticket_codes (code, ticket_amount, max_uses, expires_at, created_by) VALUES (?, ?, ?, ?, ?)");
        $stmt->execute([$code, $ticketAmount, $maxUses, $expiresAt, $_SESSION['user_id']]);

        logAdminAction($pdo, 'generate_ticket_code', "Kode: $code, Jumlah: $ticketAmount, Max uses: $maxUses, Expires: $expiresAt");

        echo json_encode(['status' => 'success', 'code' => $code]);
    } catch (Exception $e) {
        logError($e, 'Generate ticket code failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal membuat kode tiket']);
    }
    exit;
}

// === ADMIN: Get all ticket codes with pagination ===
if ($action == 'get_ticket_codes' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $page = isset($_GET['page']) ? max(1, (int) $_GET['page']) : 1;
    $limit = isset($_GET['limit']) ? min(100, max(1, (int) $_GET['limit'])) : 10;
    // Maks 100 baris
    $offset = ($page - 1) * $limit;

    $totalStmt = $pdo->query("SELECT COUNT(*) FROM ticket_codes");
    $total = $totalStmt->fetchColumn();

    $stmt = $pdo->prepare("
        SELECT tc.*, u.username as created_by_name,
               (SELECT COUNT(*) FROM ticket_code_redemptions WHERE code_id = tc.id) as redeemed_count
        FROM ticket_codes tc
        JOIN users u ON tc.created_by = u.id
        ORDER BY tc.created_at DESC
        LIMIT ? OFFSET ?
    ");
    $stmt->execute([$limit, $offset]);
    $codes = $stmt->fetchAll();

    echo json_encode([
        'codes' => $codes,
        'total' => $total,
        'page' => $page,
        'limit' => $limit,
        'total_pages' => ceil($total / $limit)
    ]);
    exit;
}

// === USER: Redeem ticket code ===
if ($action == 'redeem_ticket_code' && isset($_SESSION['user_id'])) {
    $code = trim($_POST['code'] ?? '');
    if (empty($code)) {
        http_response_code(400);
        echo json_encode(['error' => 'Kode tidak boleh kosong']);
        exit;
    }

    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT * FROM ticket_codes WHERE code = ? FOR UPDATE");
        $stmt->execute([$code]);
        $codeData = $stmt->fetch();
        if (!$codeData) {
            throw new Exception('Kode tidak valid');
        }

        if ($codeData['expires_at'] && strtotime($codeData['expires_at']) < time()) {
            throw new Exception('Kode sudah kadaluarsa');
        }

        $used = (int) $codeData['used_count'];
        $max = (int) $codeData['max_uses'];
        if ($used >= $max) {
            throw new Exception('Kode sudah tidak tersedia (kuota habis)');
        }

        $stmt = $pdo->prepare("SELECT id FROM ticket_code_redemptions WHERE code_id = ? AND user_id = ?");
        $stmt->execute([$codeData['id'], $_SESSION['user_id']]);
        if ($stmt->fetch()) {
            throw new Exception('Anda sudah pernah menggunakan kode ini');
        }

        $stmt = $pdo->prepare("UPDATE users SET tickets = tickets + ? WHERE id = ?");
        $stmt->execute([$codeData['ticket_amount'], $_SESSION['user_id']]);

        $stmt = $pdo->prepare("UPDATE ticket_codes SET used_count = used_count + 1 WHERE id = ?");
        $stmt->execute([$codeData['id']]);

        $stmt = $pdo->prepare("INSERT INTO ticket_code_redemptions (code_id, user_id) VALUES (?, ?)");
        $stmt->execute([$codeData['id'], $_SESSION['user_id']]);

        $pdo->commit();

        echo json_encode([
            'status' => 'success',
            'message' => "Berhasil mendapat {$codeData['ticket_amount']} tiket!",
            'tickets' => $codeData['ticket_amount']
        ]);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Redeem ticket code failed');
        http_response_code(400);
        echo json_encode(['error' => $e->getMessage()]);
    }
    exit;
}
// === GET AVAILABLE REWARDS ===
if ($action == 'get_rewards') {
    $stmt = $pdo->query("SELECT * FROM rewards WHERE is_active = 1 ORDER BY points_required ASC");
    $rewards = $stmt->fetchAll();
    echo json_encode($rewards);
    exit;
}
// === ADMIN: Get all rewards (including inactive) ===
if ($action == 'admin_get_rewards' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $stmt = $pdo->query("SELECT * FROM rewards ORDER BY id ASC");
    $rewards = $stmt->fetchAll();
    echo json_encode($rewards);
    exit;
}

// === ADMIN: Update reward stock (adjustment) ===
if ($action == 'admin_update_reward_stock' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $rewardId = (int) ($_POST['reward_id'] ?? 0);
    $adjustment = (int) ($_POST['adjustment'] ?? 0);
    if ($rewardId <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Reward ID tidak valid']);
        exit;
    }
    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("SELECT stock FROM rewards WHERE id = ? FOR UPDATE");
        $stmt->execute([$rewardId]);
        $currentStock = $stmt->fetchColumn();
        if ($currentStock === false) {
            throw new Exception('Reward tidak ditemukan');
        }
        $newStock = $currentStock + $adjustment;
        if ($newStock < 0) {
            throw new Exception('Stok tidak boleh negatif');
        }
        $stmt = $pdo->prepare("UPDATE rewards SET stock = ? WHERE id = ?");
        $stmt->execute([$newStock, $rewardId]);
        $pdo->commit();

        logAdminAction($pdo, 'update_reward_stock', "Reward ID: $rewardId, adjustment: $adjustment, new stock: $newStock");
        echo json_encode(['status' => 'success', 'new_stock' => $newStock]);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Update reward stock failed');
        http_response_code(500);
        echo json_encode(['error' => $e->getMessage()]);
    }
    exit;
}

// === GET USER POINTS (total & available) ===
if ($action == 'get_user_points' && isset($_SESSION['user_id'])) {
    $userId = $_SESSION['user_id'];
    $stmt = $pdo->prepare("SELECT points FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    $totalPoints = $stmt->fetchColumn();

    $stmt = $pdo->prepare("SELECT COALESCE(SUM(points_spent), 0) FROM point_redeems WHERE user_id = ? AND status = 'approved'");
    $stmt->execute([$userId]);
    $spentPoints = $stmt->fetchColumn();

    $availablePoints = $totalPoints - $spentPoints;

    echo json_encode([
        'total_points' => (int) $totalPoints,
        'available_points' => max(0, (int) $availablePoints)
    ]);
    exit;
}

// === REDEEM POINTS FOR REWARD ===
if ($action == 'redeem_points' && isset($_SESSION['user_id'])) {
    $rewardId = (int) ($_POST['reward_id'] ?? 0);
    $userId = $_SESSION['user_id'];

    if ($rewardId <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Hadiah tidak valid']);
        exit;
    }

    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("SELECT * FROM rewards WHERE id = ? AND is_active = 1 AND stock > 0 FOR UPDATE");
        $stmt->execute([$rewardId]);
        $reward = $stmt->fetch();
        if (!$reward) {
            throw new Exception('Hadiah tidak tersedia atau stok habis');
        }

        $stmt = $pdo->prepare("SELECT points FROM users WHERE id = ? FOR UPDATE");
        $stmt->execute([$userId]);
        $totalPoints = $stmt->fetchColumn();

        $stmt = $pdo->prepare("SELECT COALESCE(SUM(points_spent), 0) FROM point_redeems WHERE user_id = ? AND status = 'approved' FOR UPDATE");
        $stmt->execute([$userId]);
        $spentPoints = $stmt->fetchColumn();
        $availablePoints = $totalPoints - $spentPoints;

        if ($availablePoints < $reward['points_required']) {
            throw new Exception('Poin tidak mencukupi');
        }

        $stmt = $pdo->prepare("UPDATE rewards SET stock = stock - 1 WHERE id = ?");
        $stmt->execute([$rewardId]);

        $username = $_SESSION['username'];
        $stmt = $pdo->prepare("INSERT INTO point_redeems (user_id, username, reward_id, points_spent, status) VALUES (?, ?, ?, ?, 'approved')");
        $stmt->execute([$userId, $username, $rewardId, $reward['points_required']]);

        $pdo->commit();

        echo json_encode([
            'status' => 'success',
            'message' => "Berhasil menukar {$reward['name']} dengan {$reward['points_required']} poin"
        ]);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        http_response_code(400);
        echo json_encode(['error' => $e->getMessage()]);
    }
    exit;
}

// === ADMIN: CRUD rewards (optional) ===
if ($action == 'admin_add_reward' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $name = $_POST['name'] ?? '';
    $points = (int) ($_POST['points_required'] ?? 0);
    $stock = (int) ($_POST['stock'] ?? 0);
    $desc = $_POST['description'] ?? '';

    if (empty($name) || $points <= 0) {
        http_response_code(400);
        echo json_encode(['error' => 'Data tidak lengkap']);
        exit;
    }

    $stmt = $pdo->prepare("INSERT INTO rewards (name, description, points_required, stock) VALUES (?, ?, ?, ?)");
    $stmt->execute([$name, $desc, $points, $stock]);

    logAdminAction($pdo, 'add_reward', "Nama: $name, Poin: $points, Stok: $stock");

    echo json_encode(['status' => 'success', 'id' => $pdo->lastInsertId()]);
    exit;
}

// === ADMIN: Get point redeems log with pagination ===
if ($action == 'get_point_redeems_log' && isset($_SESSION['is_admin']) && $_SESSION['is_admin'] == 1) {
    $page = isset($_GET['page']) ? max(1, (int) $_GET['page']) : 1;
    $limit = isset($_GET['limit']) ? min(100, max(1, (int) $_GET['limit'])) : 10;
    // Maks 100 baris
    $offset = ($page - 1) * $limit;

    $totalStmt = $pdo->query("SELECT COUNT(*) FROM point_redeems");
    $total = $totalStmt->fetchColumn();

    $stmt = $pdo->prepare("
        SELECT pr.*, u.phone_number 
        FROM point_redeems pr
        LEFT JOIN users u ON pr.user_id = u.id
        ORDER BY pr.redeemed_at DESC
        LIMIT ? OFFSET ?
    ");
    $stmt->execute([$limit, $offset]);
    $logs = $stmt->fetchAll();

    echo json_encode([
        'logs' => $logs,
        'total' => $total,
        'page' => $page,
        'limit' => $limit,
        'total_pages' => ceil($total / $limit)
    ]);
    exit;
}


// ========== LAPORAN HARIAN (DAILY REPORT) ==========

if ($action == 'get_daily_report') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }
    $date = $_GET['date'] ?? date('Y-m-d');
    $stmt = $pdo->prepare("SELECT * FROM daily_report_tournament WHERE report_date = ?");
    $stmt->execute([$date]);
    $tournament = $stmt->fetch();
    $stmt = $pdo->prepare("SELECT * FROM daily_report_user WHERE report_date = ?");
    $stmt->execute([$date]);
    $user = $stmt->fetch();
    echo json_encode(['tournament' => $tournament, 'user' => $user]);
    exit;
}

if ($action == 'generate_daily_report') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }
    $date = $_POST['date'] ?? date('Y-m-d');
    $storeName = $_POST['store_name'] ?? 'FIFA World Cup 2026';

    try {
        $pdo->beginTransaction();

        // Hitung total_register (user baru pada tanggal tersebut)
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE DATE(created_at) = ?");
        $stmt->execute([$date]);
        $totalRegister = $stmt->fetchColumn();

        // Hitung total_prediksi (group + knockout)
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM predictions WHERE DATE(created_at) = ?");
        $stmt->execute([$date]);
        $totalGroupPred = $stmt->fetchColumn();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM knockout_predictions WHERE DATE(created_at) = ?");
        $stmt->execute([$date]);
        $totalKoPred = $stmt->fetchColumn();
        $totalPredictions = $totalGroupPred + $totalKoPred;

        // Hitung total_claim_daily (daily_claim_log)
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM daily_claim_log WHERE claim_date = ?");
        $stmt->execute([$date]);
        $totalClaimDaily = $stmt->fetchColumn();

        // Hitung total_claim_ticket (ticket_code_redemptions)
        $stmt = $pdo->prepare("
            SELECT COUNT(*) FROM ticket_code_redemptions tcr
            JOIN ticket_codes tc ON tcr.code_id = tc.id
            WHERE DATE(tcr.redeemed_at) = ?
        ");
        $stmt->execute([$date]);
        $totalClaimTicket = $stmt->fetchColumn();

        // Insert atau update daily_report_tournament (dengan kolom total_group_predictions dan total_knockout_predictions)
        $stmt = $pdo->prepare("
            INSERT INTO daily_report_tournament (report_date, store_name, total_register, total_predictions, total_group_predictions, total_knockout_predictions, total_claim_daily, total_claim_ticket)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                store_name = VALUES(store_name),
                total_register = VALUES(total_register),
                total_predictions = VALUES(total_predictions),
                total_group_predictions = VALUES(total_group_predictions),
                total_knockout_predictions = VALUES(total_knockout_predictions),
                total_claim_daily = VALUES(total_claim_daily),
                total_claim_ticket = VALUES(total_claim_ticket)
        ");
        $stmt->execute([$date, $storeName, $totalRegister, $totalPredictions, $totalGroupPred, $totalKoPred, $totalClaimDaily, $totalClaimTicket]);

        // ========== BAGIAN USER REPORT ==========
        $newUsersCount = $totalRegister;

        // users_claim_ticket: daftar username yang melakukan redeem kode tiket pada tanggal tersebut
        $stmt = $pdo->prepare("
            SELECT DISTINCT u.username
            FROM ticket_code_redemptions tcr
            JOIN ticket_codes tc ON tcr.code_id = tc.id
            JOIN users u ON tcr.user_id = u.id
            WHERE DATE(tcr.redeemed_at) = ?
        ");
        $stmt->execute([$date]);
        $claimUsers = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $usersClaimTicketStr = implode(', ', $claimUsers);

        // users_using_point: daftar username yang melakukan redeem poin (point_redeems) pada tanggal tersebut
        $stmt = $pdo->prepare("
            SELECT DISTINCT u.username
            FROM point_redeems pr
            JOIN users u ON pr.user_id = u.id
            WHERE DATE(pr.redeemed_at) = ?
        ");
        $stmt->execute([$date]);
        $pointUsers = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $usersUsingPointStr = implode(', ', $pointUsers);

        // prediction_history: ringkasan prediksi untuk match yang terjadi pada tanggal tersebut
        $predictionHistory = [];

        // Group matches
        $stmt = $pdo->prepare("
            SELECT m.id, g.name as group_name, t1.name as team1, t2.name as team2, m.match_date
            FROM group_matches m
            JOIN groups_list g ON m.group_id = g.id
            JOIN teams t1 ON m.team1_id = t1.id
            JOIN teams t2 ON m.team2_id = t2.id
            WHERE DATE(m.match_date) = ? AND m.is_finished = 1
        ");
        $stmt->execute([$date]);
        $finishedMatches = $stmt->fetchAll();

        foreach ($finishedMatches as $match) {
            $stmt2 = $pdo->prepare("
                SELECT u.username, p.result_type, p.team1_score_pred, p.team2_score_pred, p.total_goals_pred, p.corners_pred, p.yellow_cards_pred, p.red_cards_pred, p.offsides_pred
                FROM predictions p
                JOIN users u ON p.user_id = u.id
                WHERE p.match_id = ?
            ");
            $stmt2->execute([$match['id']]);
            $preds = $stmt2->fetchAll();
            $matchTitle = "{$match['team1']} vs {$match['team2']} (Grup {$match['group_name']})";
            $historyEntry = "Pertandingan: $matchTitle\n";
            foreach ($preds as $pred) {
                $historyEntry .= "- {$pred['username']}: ";
                if ($pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null) {
                    $historyEntry .= "Skor {$pred['team1_score_pred']}-{$pred['team2_score_pred']}, ";
                }
                if ($pred['result_type'])
                    $historyEntry .= "Hasil: {$pred['result_type']}, ";
                if ($pred['total_goals_pred'] !== null)
                    $historyEntry .= "Total Gol: {$pred['total_goals_pred']}, ";
                if ($pred['corners_pred'] !== null)
                    $historyEntry .= "Pojok: {$pred['corners_pred']}, ";
                if ($pred['yellow_cards_pred'] !== null)
                    $historyEntry .= "Kuning: {$pred['yellow_cards_pred']}, ";
                if ($pred['red_cards_pred'] !== null)
                    $historyEntry .= "Merah: {$pred['red_cards_pred']}, ";
                if ($pred['offsides_pred'] !== null)
                    $historyEntry .= "Offside: {$pred['offsides_pred']}";
                $historyEntry .= "\n";
            }
            if (empty($preds))
                $historyEntry .= "- Tidak ada prediksi.\n";
            $predictionHistory[] = $historyEntry;
        }

        // Knockout matches
        $stmt = $pdo->prepare("
            SELECT m.id, m.stage, t1.name as team1, t2.name as team2, m.match_date
            FROM knockout_matches m
            LEFT JOIN teams t1 ON m.team1_id = t1.id
            LEFT JOIN teams t2 ON m.team2_id = t2.id
            WHERE DATE(m.match_date) = ? AND m.is_finished = 1
        ");
        $stmt->execute([$date]);
        $finishedKoMatches = $stmt->fetchAll();

        foreach ($finishedKoMatches as $match) {
            $stmt2 = $pdo->prepare("
                SELECT u.username, p.winner_id, p.team1_score_pred, p.team2_score_pred, p.total_goals_pred, p.corners_pred, p.yellow_cards_pred, p.red_cards_pred, p.offsides_pred
                FROM knockout_predictions p
                JOIN users u ON p.user_id = u.id
                WHERE p.match_id = ?
            ");
            $stmt2->execute([$match['id']]);
            $preds = $stmt2->fetchAll();
            $matchTitle = "{$match['team1']} vs {$match['team2']} ({$match['stage']})";
            $historyEntry = "Pertandingan: $matchTitle\n";
            foreach ($preds as $pred) {
                $historyEntry .= "- {$pred['username']}: ";
                if ($pred['winner_id'])
                    $historyEntry .= "Pemenang: " . ($pred['winner_id'] == $match['team1'] ? $match['team1'] : $match['team2']) . ", ";
                if ($pred['team1_score_pred'] !== null && $pred['team2_score_pred'] !== null) {
                    $historyEntry .= "Skor {$pred['team1_score_pred']}-{$pred['team2_score_pred']}, ";
                }
                if ($pred['total_goals_pred'] !== null)
                    $historyEntry .= "Total Gol: {$pred['total_goals_pred']}, ";
                if ($pred['corners_pred'] !== null)
                    $historyEntry .= "Pojok: {$pred['corners_pred']}, ";
                if ($pred['yellow_cards_pred'] !== null)
                    $historyEntry .= "Kuning: {$pred['yellow_cards_pred']}, ";
                if ($pred['red_cards_pred'] !== null)
                    $historyEntry .= "Merah: {$pred['red_cards_pred']}, ";
                if ($pred['offsides_pred'] !== null)
                    $historyEntry .= "Offside: {$pred['offsides_pred']}";
                $historyEntry .= "\n";
            }
            if (empty($preds))
                $historyEntry .= "- Tidak ada prediksi.\n";
            $predictionHistory[] = $historyEntry;
        }

        $predictionHistoryStr = implode("\n\n", $predictionHistory);

        // Insert atau update daily_report_user
        $stmt = $pdo->prepare("
            INSERT INTO daily_report_user (report_date, store_name, new_users_count, users_claim_ticket, users_using_point, prediction_history)
            VALUES (?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                store_name = VALUES(store_name),
                new_users_count = VALUES(new_users_count),
                users_claim_ticket = VALUES(users_claim_ticket),
                users_using_point = VALUES(users_using_point),
                prediction_history = VALUES(prediction_history)
        ");
        $stmt->execute([$date, $storeName, $newUsersCount, $usersClaimTicketStr, $usersUsingPointStr, $predictionHistoryStr]);

        $pdo->commit();
        echo json_encode(['status' => 'success', 'message' => 'Laporan berhasil digenerate']);
    } catch (Exception $e) {
        if ($pdo->inTransaction())
            $pdo->rollBack();
        logError($e, 'Generate daily report failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal generate laporan: ' . $e->getMessage()]);
    }
    exit;
}

if ($action == 'update_report_notes') {
    if (!isset($_SESSION['is_admin']) || $_SESSION['is_admin'] != 1) {
        http_response_code(403);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }
    $date = $_POST['date'] ?? '';
    $tournamentNotes = $_POST['tournament_notes'] ?? '';
    $userNotes = $_POST['user_notes'] ?? '';
    if (empty($date)) {
        http_response_code(400);
        echo json_encode(['error' => 'Tanggal tidak valid']);
        exit;
    }
    try {
        if ($tournamentNotes !== '') {
            $stmt = $pdo->prepare("UPDATE daily_report_tournament SET notes = ? WHERE report_date = ?");
            $stmt->execute([$tournamentNotes, $date]);
        }
        if ($userNotes !== '') {
            $stmt = $pdo->prepare("UPDATE daily_report_user SET notes = ? WHERE report_date = ?");
            $stmt->execute([$userNotes, $date]);
        }
        echo json_encode(['status' => 'success']);
    } catch (Exception $e) {
        logError($e, 'Update report notes failed');
        http_response_code(500);
        echo json_encode(['error' => 'Gagal menyimpan catatan']);
    }
    exit;
}

?>