<?php
require_once __DIR__ . '/../init.php';
require_once PROJECT_ROOT . '/backend/controllers/common_function.php';
require_once PROJECT_ROOT . '/backend/controllers/db_select_helper.php';
require_once PROJECT_ROOT . '/../api/core/mailer.php';

// include other controller
require_once PROJECT_ROOT . '/backend/controllers/ActivityController.php';
require_once PROJECT_ROOT . '/backend/controllers/UserAksesController.php';
require_once PROJECT_ROOT . '/backend/controllers/AttachmentController.php';
require_once PROJECT_ROOT . '/backend/controllers/TeamController.php';
require PROJECT_ROOT . '../../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class TournamentController
{
    private $conn;
    private $table = "tournament T";
    private $pk = "IDTournament";
    private $sv = "Tournament";

    protected $displayedColumn = [
        'T.IDTournament',
        'T.NamaTournament',
        'T.NamaTournamentEN',
        'T.Description',
        'T.Location',
        'T.GoogleMapsUrl',
        'T.RegistrationStart',
        'T.RegistrationEnd',
        'T.TournamentStart',
        'T.TournamentEnd',
        'T.RegistrationFee',
        'T.Bank',
        'T.NomorRekening',
        'T.NamaRekening',
        'T.TotalPrize',
        'T.MaxParticipant',
        'T.RuleDocument',
        'T.RuleDocumentName',
        'T.RuleDocumentUploadedBy',
        'T.RuleDocumentUploadedAt',
        'T.Gambar',
        'T.StatusTournament',
        'T.TournamentFormat',
        'T.PointWin',
        'T.PointDraw',
        'T.PointLose',
        'T.ReportGeneratedName',
        'T.ReportGeneratedBy',
        'T.ReportGeneratedAt',
        'T.CreateBy',
        'T.CreateAt',
        'T.LastUpdateBy',
        'T.LastUpdateAt',
        '(SELECT PointChampion1 FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointChampion1',
        '(SELECT PointChampion2 FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointChampion2',
        '(SELECT PointChampion3 FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointChampion3',
        '(SELECT PointChampion4 FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointChampion4',
        '(SELECT PointMinusFirstRound FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointMinusFirstRound',
        '(SELECT PointMinusSecondRound FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointMinusSecondRound',
        '(SELECT PointMinusQuarterFinal FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointMinusQuarterFinal',
        '(SELECT PointParticipate FROM tournament_point_setup WHERE IDTournament = T.IDTournament LIMIT 1) AS PointParticipate'
    ];


    public function __construct($conn)
    {
        $this->conn = $conn;
        $this->activityController = new ActivityController($this->conn);
        $this->attachmentController = new AttachmentController($this->conn);
        $this->userAksesController = new UserAksesController($this->conn);
        $this->teamController = new TeamController($this->conn);
    }

    public function select($post)
    {
        $listExclude = [];

        $listSearch = [
            'T.NamaTournament',
            'T.NamaTournamentEN',
            'T.Description',
            'T.Location'
        ];


        return runSelect(
            $this->conn,
            $this->table,
            $this->pk,
            $this->displayedColumn,
            $post,
            $listExclude,
            $listSearch
        );
    }
    public function update($post, $files)
    {
        try {

            if (empty($post['NamaTournament'])) {
                throw new Exception("Nama Tournament tidak boleh kosong");
            }


            return runWithTransaction($this->conn, function ($conn) use ($post, $files) {

                $NamaTournament = trim($post['NamaTournament']);
                $NamaTournamentEN = $post['NamaTournamentEN'] ?? '';
                $Description = $post['Description'] ?? '';
                $Location = $post['Location'] ?? '';
                $GoogleMapsUrl = $post['GoogleMapsUrl'] ?? '';
                $RegistrationStart = $post['RegistrationStart'] ?? null;
                $RegistrationEnd = $post['RegistrationEnd'] ?? null;
                $TournamentStart = $post['TournamentStart'] ?? null;
                $TournamentEnd = $post['TournamentEnd'] ?? null;
                $RegistrationFee = $post['RegistrationFee'] ?? 0;
                $Bank = trim($post['Bank']) ?? '';
                $NomorRekening = trim($post['NomorRekening']) ?? '';
                $NamaRekening = trim($post['NamaRekening']) ?? '';
                $TotalPrize = $post['TotalPrize'] ?? 0;
                $MaxParticipant = $post['MaxParticipant'] ?? 0;
                $StatusTournament = $post['StatusTournament'] ?? '';
                $TournamentFormat = $post['TournamentFormat'] ?? '';
                $CategoryLevelData = $post['CategoryLevelData'] ?? '';
                $PointChampion1 = isset($post['PointChampion1']) ? intval($post['PointChampion1']) : 100;
                $PointChampion2 = isset($post['PointChampion2']) ? intval($post['PointChampion2']) : 50;
                $PointChampion3 = isset($post['PointChampion3']) ? intval($post['PointChampion3']) : 25;
                $PointChampion4 = isset($post['PointChampion4']) ? intval($post['PointChampion4']) : 0;
                $PointMinusFirstRound = isset($post['PointMinusFirstRound']) ? intval($post['PointMinusFirstRound']) : 0;
                $PointMinusSecondRound = isset($post['PointMinusSecondRound']) ? intval($post['PointMinusSecondRound']) : 0;
                $PointMinusQuarterFinal = isset($post['PointMinusQuarterFinal']) ? intval($post['PointMinusQuarterFinal']) : 0;
                $PointParticipate = isset($post['PointParticipate']) ? intval($post['PointParticipate']) : 0;
                $isUpdate = !empty($post['IDTournament']) && intval($post['IDTournament']) > 0;

                if (!in_array($StatusTournament, ['draft', 'open_registration', 'closed_registration', 'ongoing', 'finished', 'cancelled'])) {
                    throw new Exception("Status tournament tidak valid");
                }
                // Convert datetime format if needed
                $RegistrationStart = !empty($RegistrationStart) ? str_replace('T', ' ', substr($RegistrationStart, 0, 16) . ':00') : null;
                $RegistrationEnd = !empty($RegistrationEnd) ? str_replace('T', ' ', substr($RegistrationEnd, 0, 16) . ':00') : null;
                $TournamentStart = !empty($TournamentStart) ? str_replace('T', ' ', substr($TournamentStart, 0, 16) . ':00') : null;
                $TournamentEnd = !empty($TournamentEnd) ? str_replace('T', ' ', substr($TournamentEnd, 0, 16) . ':00') : null;

                // --------------------------------------
                // MULAI USER AKSES
                // --------------------------------------
                $UserLogin = "";
                $AksesNeeded = $isUpdate ? $this->sv . ".Modify" : $this->sv . ".Insert";

                $userAksesFilterParam = [
                    ["IDUser", $post['IDUserLogin'], 1]
                ];

                $userAksesselectParam = [
                    "filters" => $userAksesFilterParam,
                    "rows_per_page" => 1,
                    "page_number" => 1,
                    "no_count" => true
                ];

                $userAksesresult = $this->userAksesController->select($userAksesselectParam);
                $listAkses = array_column($userAksesresult['list_data'], 'AksesName');

                if ($userAksesresult['Status'] == 1 && !empty($listAkses)) {
                    if (!in_array($AksesNeeded, $listAkses)) {
                        $msg = "User tidak memiliki otoritas akses - [" . $AksesNeeded . "]";
                        throw new Exception($msg);
                    }

                    $UserLogin = $userAksesresult['Username'];
                } else {
                    $msg = $userAksesresult['Keterangan'];
                    throw new Exception($msg);
                }
                // --------------------------------------
                // END USER AKSES
                // --------------------------------------

                // =========================
                // START - cek duplikat
                // ========================= 
                $duplikatDataFilter = [];
                $duplikatDataFilter[] = ["NamaTournament", $post['NamaTournament'], 1];

                if ($isUpdate) {
                    $duplikatDataFilter[] = ["IDTournament", $post['IDTournament'], 6];
                }

                $duplikatDataSelectParam = [
                    "filters" => $duplikatDataFilter,
                    "rows_per_page" => 1,
                    "page_number" => 1,
                    "no_count" => true
                ];
                $duplikatDataResult = $this->select($duplikatDataSelectParam);

                if ($duplikatDataResult['Status'] == 1 && count($duplikatDataResult['list_data']) > 0) {
                    $msg = "Tournament sudah terpakai, mohon diganti";
                    throw new Exception($msg);
                }
                // =========================
                // END - cek duplikat
                // =========================


                // =========================
                // START - ambil data lama
                // =========================
                $oldData = [];
                if ($isUpdate && !empty($post['IDTournament'])) {

                    $oldDataFilter = [
                        ["IDTournament", $post['IDTournament'], 1]
                    ];

                    $oldDataSelectParam = [
                        "filters" => $oldDataFilter,
                        "rows_per_page" => 1,
                        "page_number" => 1,
                        "no_count" => true
                    ];

                    $oldDataResult = $this->select($oldDataSelectParam);

                    if ($oldDataResult['Status'] == 1) {
                        $oldData = $oldDataResult['list_data'][0];
                    } else {
                        $msg = $oldDataResult['Keterangan'];
                        throw new Exception($msg);
                    }
                }
                // =========================
                // END - ambil data lama
                // =========================



                // =========================
                // QUERY DATABASE
                // =========================
                $NewID = 0;

                if ($isUpdate) {
                    // Update tournament
                    $sql = "UPDATE tournament 
                        SET NamaTournament = ?, 
                            NamaTournamentEN = ?, 
                            Description = ?,
                            Location = ?,
                            GoogleMapsUrl = ?,
                            RegistrationStart = ?,
                            RegistrationEnd = ?,
                            TournamentStart = ?,
                            TournamentEnd = ?,
                            RegistrationFee = ?,
                            Bank = ?,
                            NomorRekening = ?,
                            NamaRekening = ?,
                            TotalPrize = ?,
                            MaxParticipant = ?,
                            StatusTournament = ?,
                            TournamentFormat = ?,
                            LastUpdateBy = ?, 
                            LastUpdateAt = NOW()
                        WHERE IDTournament = ?";

                    $stmt = $conn->prepare($sql);
                    if (!$stmt) {
                        throw new Exception("Prepare statement gagal: " . $conn->error);
                    }

                    $stmt->bind_param(
                        "ssssssssssssssssssi",
                        $NamaTournament,
                        $NamaTournamentEN,
                        $Description,
                        $Location,
                        $GoogleMapsUrl,
                        $RegistrationStart,
                        $RegistrationEnd,
                        $TournamentStart,
                        $TournamentEnd,
                        $RegistrationFee,
                        $Bank,
                        $NomorRekening,
                        $NamaRekening,
                        $TotalPrize,
                        $MaxParticipant,
                        $StatusTournament,
                        $TournamentFormat,
                        $UserLogin,
                        $post['IDTournament']
                    );

                    $stmt->execute();

                    if ($stmt->affected_rows < 0) {
                        throw new Exception("Gagal update tournament");
                    }

                    $stmt->close();
                    $NewID = $post['IDTournament'];
                } else {

                    // Insert tournament baru
                    $sql = "INSERT INTO tournament 
                        (NamaTournament, NamaTournamentEN, Description, Location, GoogleMapsUrl,
                         RegistrationStart, RegistrationEnd, TournamentStart, TournamentEnd,
                         RegistrationFee, Bank, NomorRekening, NamaRekening,
                         TotalPrize, MaxParticipant, StatusTournament, TournamentFormat,
                         CreateBy, CreateAt) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";

                    $stmt = $conn->prepare($sql);
                    if (!$stmt) {
                        throw new Exception("Prepare statement gagal: " . $conn->error);
                    }

                    $stmt->bind_param(
                        "ssssssssssssssssss",
                        $NamaTournament,
                        $NamaTournamentEN,
                        $Description,
                        $Location,
                        $GoogleMapsUrl,
                        $RegistrationStart,
                        $RegistrationEnd,
                        $TournamentStart,
                        $TournamentEnd,
                        $RegistrationFee,
                        $Bank,
                        $NomorRekening,
                        $NamaRekening,
                        $TotalPrize,
                        $MaxParticipant,
                        $StatusTournament,
                        $TournamentFormat,
                        $UserLogin
                    );


                    $stmt->execute();

                    if ($stmt->affected_rows <= 0) {
                        throw new Exception("Gagal insert tournament baru");
                    }

                    $NewID = $stmt->insert_id;
                    $stmt->close();
                }

                $this->saveTournamentPointSetup(
                    $conn,
                    $NewID,
                    [
                        'PointChampion1' => $PointChampion1,
                        'PointChampion2' => $PointChampion2,
                        'PointChampion3' => $PointChampion3,
                        'PointChampion4' => $PointChampion4,
                        'PointMinusFirstRound' => $PointMinusFirstRound,
                        'PointMinusSecondRound' => $PointMinusSecondRound,
                        'PointMinusQuarterFinal' => $PointMinusQuarterFinal,
                        'PointParticipate' => $PointParticipate
                    ],
                    $UserLogin
                );

                // If tournament status set to finished, populate tournament_standing
                try {
                    if ($StatusTournament === 'finished') {
                        // delete existing standings for this tournament
                        $stmtDelStand = $conn->prepare("DELETE FROM tournament_standing WHERE IDTournament = ?");
                        if ($stmtDelStand) {
                            $stmtDelStand->bind_param("i", $NewID);
                            $stmtDelStand->execute();
                            $stmtDelStand->close();
                        }

                        // insert aggregated standings using match results and sets
                        // treat WinnerTeam = 0 as Draw (same as NULL), so SQL counts accordingly
                        $sqlInsertStand = "
                        INSERT INTO tournament_standing (
                            IDTournament, IDGroup, IDTeam, Played, Win, Lose, Draw, Points, ScoreFor, ScoreAgainst
                        )
                        SELECT
                            m.IDTournament,
                            m.IDGroup,
                            t.IDTeam,
                            COUNT(m.IDMatch) AS Played,
                            SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam = t.IDTeam THEN 1 ELSE 0 END) AS Win,
                            SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam != t.IDTeam THEN 1 ELSE 0 END) AS Lose,
                            SUM(CASE WHEN (m.WinnerTeam IS NULL OR m.WinnerTeam = 0) AND m.Status = 'finished' THEN 1 ELSE 0 END) AS Draw,
                            SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam = t.IDTeam THEN COALESCE(tcl.PointWin, tour.PointWin)
                                     WHEN (m.WinnerTeam IS NULL OR m.WinnerTeam = 0) THEN COALESCE(tcl.PointDraw, tour.PointDraw)
                                     ELSE COALESCE(tcl.PointLose, tour.PointLose) END) AS Points,
                            SUM(CASE WHEN t.IDTeam = m.TeamA THEN ms.ScoreTotalA ELSE ms.ScoreTotalB END) AS ScoreFor,
                            SUM(CASE WHEN t.IDTeam = m.TeamA THEN ms.ScoreTotalB ELSE ms.ScoreTotalA END) AS ScoreAgainst
                        FROM tournament_match m
                        LEFT JOIN (
                            SELECT IDMatch, SUM(ScoreTeamA) AS ScoreTotalA, SUM(ScoreTeamB) AS ScoreTotalB
                            FROM tournament_match_set
                            GROUP BY IDMatch
                        ) ms ON ms.IDMatch = m.IDMatch
                        LEFT JOIN tournament_category_level tcl ON tcl.IDTCL = m.IDTCL
                        LEFT JOIN tournament tour ON tour.IDTournament = m.IDTournament
                        JOIN (
                            SELECT IDMatch, TeamA AS IDTeam FROM tournament_match
                            UNION ALL
                            SELECT IDMatch, TeamB AS IDTeam FROM tournament_match
                        ) t ON t.IDMatch = m.IDMatch
                        WHERE m.IDTournament = ? AND m.Status = 'finished'
                        GROUP BY m.IDTournament, m.IDGroup, t.IDTeam
                        ";

                        $stmtIns = $conn->prepare($sqlInsertStand);
                        if ($stmtIns) {
                            $stmtIns->bind_param("i", $NewID);
                            $stmtIns->execute();
                            $stmtIns->close();
                        }
                    }
                } catch (Exception $e) {
                    // log but do not interrupt main flow
                    error_log('Populate standings failed: ' . $e->getMessage());
                }

                // =========================
                // START - ambil data baru
                // =========================
                $newData = [];
                if ($NewID > 0) {

                    $newDataFilter = [
                        ["IDTournament", $NewID, 1]
                    ];

                    $newDataSelectParam = [
                        "filters" => $newDataFilter,
                        "rows_per_page" => 1,
                        "page_number" => 1,
                        "no_count" => true
                    ];

                    $newDataResult = $this->select($newDataSelectParam);

                    if ($newDataResult['Status'] == 1) {
                        $newData = $newDataResult['list_data'][0];
                    } else {
                        $msg = $newDataResult['Keterangan'];

                        throw new Exception($msg);
                    }


                }
                // =========================
                // END - ambil data baru
                // =========================

                // =========================
                // START - Handle Category & Level
                // =========================

                if (!empty($CategoryLevelData)) {
                    try {
                        $categoryLevelArray = json_decode($CategoryLevelData, true);

                        if (is_array($categoryLevelArray) && count($categoryLevelArray) > 0) {
                            // Jika update, hapus data lama terlebih dahulu
                            if ($isUpdate) {
                                $sqlDeleteOld = "DELETE FROM tournament_category_level WHERE IDTournament = ?";
                                $stmtDeleteOld = $conn->prepare($sqlDeleteOld);
                                if ($stmtDeleteOld) {
                                    $stmtDeleteOld->bind_param("i", $NewID);
                                    $stmtDeleteOld->execute();
                                    $stmtDeleteOld->close();
                                }
                            }

                            // Insert data baru
                            $sqlInsertCatLevel = "INSERT INTO tournament_category_level (IDTournament, IDKategori, IDLevel,CreateBy, CreateAt) VALUES (?, ?, ?, ?, NOW())";
                            $stmtInsertCatLevel = $conn->prepare($sqlInsertCatLevel);

                            if (!$stmtInsertCatLevel) {
                                throw new Exception("Prepare statement gagal untuk kategori-level: " . $conn->error);
                            }

                            foreach ($categoryLevelArray as $item) {
                                $idKategori = intval($item['IDKategori'] ?? 0);
                                $idLevel = intval($item['IDLevel'] ?? 0);

                                if ($idKategori > 0 && $idLevel > 0) {
                                    $stmtInsertCatLevel->bind_param("iiis", $NewID, $idKategori, $idLevel, $UserLogin);
                                    $stmtInsertCatLevel->execute();
                                }
                            }
                            $stmtInsertCatLevel->close();
                        }
                    } catch (Exception $e) {
                        // Log error tapi jangan hentikan proses
                        error_log("Error handling category-level: " . $e->getMessage());
                    }
                }
                // =========================
                // END - Handle Category & Level
                // =========================

                // =========================
                // START - Handle File Upload (RuleDocument)
                // =========================
                if (!empty($files) && isset($files['RuleDocument'])) {
                    $ruleFile = $files['RuleDocument'];

                    if ($ruleFile['error'] === 0) {
                        try {
                            // Get tournament ID dari newData
                            $tournamentId = $newData['IDTournament'] ?? $NewID;

                            // Jika update, hapus file lama terlebih dahulu
                            if ($isUpdate && !empty($oldData['RuleDocument'])) {
                                $oldFilePath = PROJECT_ROOT . '/attachments/tournament/' . $tournamentId . '/' . $oldData['RuleDocument'];
                                if (is_file($oldFilePath)) {
                                    @unlink($oldFilePath);
                                }
                            }

                            // Get file extension
                            $fileExt = pathinfo($ruleFile['name'], PATHINFO_EXTENSION);
                            $fileExt = strtolower($fileExt);

                            // Validasi extension
                            $allowedExt = ['pdf', 'doc', 'docx'];
                            if (!in_array($fileExt, $allowedExt)) {
                                throw new Exception("Format file tidak dibolehkan. Hanya PDF, DOC, DOCX yang diperbolehkan");
                            }

                            // Generate nama file dengan microtime
                            $microtime = str_replace('.', '', microtime(true));
                            $newFileName = $tournamentId . '_rule_' . $microtime . '.' . $fileExt;

                            // Buat directory jika belum ada
                            $attachmentDir = PROJECT_ROOT . '/attachments/tournament/' . $tournamentId;
                            if (!is_dir($attachmentDir)) {
                                @mkdir($attachmentDir, 0755, true);
                            }

                            // Upload file
                            $uploadPath = $attachmentDir . '/' . $newFileName;
                            if (move_uploaded_file($ruleFile['tmp_name'], $uploadPath)) {
                                // Update database dengan nama file baru
                                $sqlUpdateFile = "UPDATE tournament SET 
                                RuleDocument = ?,
                                RuleDocumentName = ?,
                                RuleDocumentUploadedBy = ?,
                                RuleDocumentUploadedAt = NOW()
                                WHERE IDTournament = ?";
                                $stmtUpdateFile = $conn->prepare($sqlUpdateFile);
                                if ($stmtUpdateFile) {
                                    $stmtUpdateFile->bind_param("sssi", $newFileName, $ruleFile['name'], $UserLogin, $tournamentId);
                                    $stmtUpdateFile->execute();
                                    $stmtUpdateFile->close();
                                }
                            } else {
                                throw new Exception("Gagal mengupload file dokumen peraturan");
                            }
                        } catch (Exception $e) {
                            // Log error tapi jangan hentikan proses keseluruhan
                            error_log("Error uploading rule document: " . $e->getMessage());
                        }
                    }
                }
                // =========================
                // END - Handle File Upload (RuleDocument)
                // =========================

                // =========================
                // START - Handle File Upload (Gambar)
                // =========================
                if (!empty($files) && isset($files['Gambar'])) {
                    $gambarFile = $files['Gambar'];

                    if ($gambarFile['error'] === 0) {
                        try {
                            // Get tournament ID dari newData
                            $tournamentId = $newData['IDTournament'] ?? $NewID;

                            // Jika update, hapus file lama terlebih dahulu
                            if ($isUpdate && !empty($oldData['Gambar'])) {
                                $oldFilePath = PROJECT_ROOT . '/attachments/tournament/' . $tournamentId . '/' . $oldData['Gambar'];
                                if (is_file($oldFilePath)) {
                                    @unlink($oldFilePath);
                                }
                            }

                            // Get file extension
                            $fileExt = pathinfo($gambarFile['name'], PATHINFO_EXTENSION);
                            $fileExt = strtolower($fileExt);

                            // Validasi extension gambar
                            $allowedExt = ['jpg', 'jpeg', 'png', 'gif', 'webp'];
                            $fileExt = strtolower($fileExt); // pastikan lowercase

                            if (!in_array($fileExt, $allowedExt)) {
                                throw new Exception("Format file tidak dibolehkan. Hanya JPG, JPEG, PNG, GIF, WEBP yang diperbolehkan");
                            }

                            // Generate nama file dengan microtime
                            $microtime = str_replace('.', '', microtime(true));
                            $newFileName = $tournamentId . '_gambar_' . $microtime . '.' . $fileExt;

                            // Buat directory jika belum ada
                            $attachmentDir = PROJECT_ROOT . '/attachments/tournament/' . $tournamentId;
                            if (!is_dir($attachmentDir)) {
                                @mkdir($attachmentDir, 0755, true);
                            }

                            // Upload file
                            $uploadPath = $attachmentDir . '/' . $newFileName;
                            if (move_uploaded_file($gambarFile['tmp_name'], $uploadPath)) {
                                // Update database dengan nama file baru
                                $sqlUpdateFile = "UPDATE tournament SET Gambar = ? WHERE IDTournament = ?";
                                $stmtUpdateFile = $conn->prepare($sqlUpdateFile);
                                if ($stmtUpdateFile) {
                                    $stmtUpdateFile->bind_param("si", $newFileName, $tournamentId);
                                    $stmtUpdateFile->execute();
                                    $stmtUpdateFile->close();
                                }
                            } else {
                                throw new Exception("Gagal mengupload file dokumen peraturan");
                            }
                        } catch (Exception $e) {
                            // Log error tapi jangan hentikan proses keseluruhan
                            error_log("Error uploading gambar document: " . $e->getMessage());
                        }
                    }
                }
                // =========================
                // END - Handle File Upload (Gambar)
                // =========================


                // =========================
                // START HANDLE Activity
                // =========================
                $di = json_decode($post['DeviceInfo'], true);
                $Platform = $di['platform'] ?? '';
                $DeviceName = $di['device_name'] ?? '';
                $FullDevice = $di['FullDevice'] ?? $Platform . " - " . $DeviceName;

                $IP = "";
                if (isset($post['IP'])) {
                    $IP = $post['IP'];
                } else {
                    if (isset($_SERVER['REMOTE_ADDR'])) {
                        $IP = $_SERVER['REMOTE_ADDR'];
                    }
                }

                $Category = "tournament";
                $KodeUnik = $NewID;
                $TipeActivity = $isUpdate ? "Modify" : "Insert";
                $queryAcitivy = g_generateActivityQuery(
                    $Category,
                    $TipeActivity,
                    $newData,
                    $oldData,
                    []
                );
                $activityResult = $this->activityController->generateActivity(
                    $post['IDUserLogin'],
                    $UserLogin,
                    $queryAcitivy,
                    $FullDevice,
                    $IP,
                    $Category,
                    $KodeUnik
                );
                // =========================
                // END HANDLE Activity
                // =========================

                return [
                    "Status" => 1,
                    "Keterangan" => $isUpdate ? "Data berhasil diupdate" : "Data berhasil ditambahkan",
                    "ID" => $NewID
                ];
            });
        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    public function delete($post)
    {
        try {
            $MainID = $post[$this->pk];
            if (empty($MainID)) {
                throw new Exception("Parameter tidak lengkap");
            }

            return runWithTransaction($this->conn, function ($conn) use ($MainID, $post) {

                // --------------------------------------
                // VALIDASI AKSES USER
                // --------------------------------------
                $UserLogin = "";
                $AksesNeeded = $this->sv . ".Delete"; // Tournament.Delete
                $userAksesFilterParam = [
                    ["IDUser", $post['IDUserLogin'], 1]
                ];

                $userAksesselectParam = [
                    "filters" => $userAksesFilterParam,
                    "rows_per_page" => 1,
                    "page_number" => 1,
                    "no_count" => true
                ];

                $userAksesresult = $this->userAksesController->select($userAksesselectParam);
                $listAkses = array_column($userAksesresult['list_data'], 'AksesName');

                if ($userAksesresult['Status'] == 1 && !empty($listAkses)) {
                    if (!in_array($AksesNeeded, $listAkses)) {
                        throw new Exception("User tidak memiliki otoritas akses - [" . $AksesNeeded . "]");
                    }
                    $UserLogin = $userAksesresult['Username'];
                } else {
                    throw new Exception($userAksesresult['Keterangan'] ?? "Akses pengguna tidak valid");
                }

                // --------------------------------------
                // AMBIL DATA LAMA 
                // --------------------------------------
                $oldData = [];
                $idArray = is_array($MainID)
                    ? $MainID
                    : array_filter(array_unique(array_map('trim', explode(',', $MainID))));

                if (empty($idArray)) {
                    throw new Exception("Tidak ada ID valid untuk dihapus.");
                }

                if (count($idArray) > 1000) {
                    throw new Exception("Maksimal 1000 data yang dapat dihapus sekaligus.");
                }

                $oldDataFilter = [
                    ["IDTournament", $MainID, 7]
                ];

                $oldDataSelectParam = [
                    "filters" => $oldDataFilter,
                    "rows_per_page" => 0,
                    "page_number" => 1,
                    "no_count" => true
                ];

                $oldDataResult = $this->select($oldDataSelectParam);

                if ($oldDataResult['Status'] == 1) {
                    $oldData = $oldDataResult['list_data'];
                } else {
                    $msg = $oldDataResult['Keterangan'];
                    throw new Exception($msg);
                }


                // --------------------------------------
                // VALIDASI: ADA BARANG?
                // --------------------------------------
                $errorList = [];
                $deleteID = [];

                foreach ($oldData as $row) {
                    $canDelete = true;
                    $IDTournament = $row['IDTournament'];
                    $NamaTournament = $row['NamaTournament'];
                    $StatusTournament = $row['StatusTournament'];

                    // ==========================================
                    // VALIDASI 1: STATUS HARUS DRAFT
                    // ==========================================
                    if ($StatusTournament !== 'draft') {
                        $errorList[] = [
                            "IDTournament" => $IDTournament,
                            "NamaTournament" => $NamaTournament,
                            "Keterangan" => "Tournament tidak dapat dihapus karena status '$StatusTournament'. Hanya status 'draft' yang bisa dihapus."
                        ];
                        $canDelete = false;
                    }

                    // ==========================================
                    // VALIDASI 2: CEK TOURNAMENT_CATEGORY_LEVEL
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM tournament_category_level WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data kategori-level (tournament_category_level)."
                            ];
                            $canDelete = false;
                        }
                    }

                    // ==========================================
                    // VALIDASI 3: CEK TOURNAMENT_GROUP
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM tournament_group WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data group (tournament_group)."
                            ];
                            $canDelete = false;
                        }
                    }

                    // ==========================================
                    // VALIDASI 4: CEK TOURNAMENT_MATCH
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM tournament_match WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data match (tournament_match)."
                            ];
                            $canDelete = false;
                        }
                    }

                    // ==========================================
                    // VALIDASI 5: CEK TOURNAMENT_STANDING
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM tournament_standing WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data standing (tournament_standing)."
                            ];
                            $canDelete = false;
                        }
                    }

                    // ==========================================
                    // VALIDASI 6: CEK TOURNAMENT_REGISTRATION
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM tournament_registration WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data registrasi (tournament_registration)."
                            ];
                            $canDelete = false;
                        }
                    }

                    // ==========================================
                    // VALIDASI 7: CEK TEAM (indirect via tournament)
                    // ==========================================
                    if ($canDelete) {
                        $sqlCheck = "SELECT COUNT(*) as total FROM team WHERE IDTournament = ?";
                        $stmtCheck = $conn->prepare($sqlCheck);
                        $stmtCheck->bind_param("i", $IDTournament);
                        $stmtCheck->execute();
                        $resultCheck = $stmtCheck->get_result();
                        $rowCheck = $resultCheck->fetch_assoc();
                        $stmtCheck->close();

                        if ($rowCheck['total'] > 0) {
                            $errorList[] = [
                                "IDTournament" => $IDTournament,
                                "NamaTournament" => $NamaTournament,
                                "Keterangan" => "Tournament tidak dapat dihapus karena sudah memiliki " . $rowCheck['total'] . " data team."
                            ];
                            $canDelete = false;
                        }
                    }

                    if ($canDelete) {
                        $deleteID[] = $IDTournament;
                    }
                }

                // --------------------------------------
                // PROSES DELETE
                // --------------------------------------
                $statusDelete = 2;

                if (!empty($deleteID)) {
                    $statusDelete = 1;
                    try {
                        $conn->begin_transaction();

                        $placeholders = implode(',', array_fill(0, count($deleteID), '?'));
                        $types = str_repeat('i', count($deleteID));

                        $sqlDelTournament = "DELETE FROM tournament WHERE IDTournament IN ($placeholders)";
                        $stmtDel = $conn->prepare($sqlDelTournament);
                        if (!$stmtDel) {
                            throw new Exception("Gagal prepare delete tournament: " . $conn->error);
                        }
                        $stmtDel->bind_param($types, ...$deleteID);
                        $stmtDel->execute();

                        if ($stmtDel->affected_rows <= 0) {
                            $statusDelete = 2;
                            $errorList[] = [
                                "IDTournament" => implode(',', $deleteID),
                                "NamaTournament" => "-",
                                "Keterangan" => "Tidak ada data yang dihapus dari tabel tournament."
                            ];
                        }

                        $stmtDel->close();
                        $conn->commit();
                    } catch (Exception $e) {
                        $conn->rollback();
                        $statusDelete = 2;
                        $errorList[] = [
                            "IDTournament" => implode(',', $deleteID),
                            "NamaTournament" => "-",
                            "Keterangan" => "Terjadi kesalahan saat menghapus data: " . $e->getMessage()
                        ];
                    }
                }



                // --------------------------------------
                // LOG AKTIVITAS
                // --------------------------------------
                $di = json_decode($post['DeviceInfo'], true);
                $Platform = $di['platform'] ?? '';
                $DeviceName = $di['device_name'] ?? '';
                $FullDevice = $di['FullDevice'] ?? $Platform . " - " . $DeviceName;
                $IP = $post['IP'] ?? ($_SERVER['REMOTE_ADDR'] ?? '');
                $Category = "tournament";

                if (!empty($deleteID)) {
                    foreach ($oldData as $row) {
                        if (in_array($row['IDTournament'], $deleteID)) {
                            $KodeUnik = $row['IDTournament'];
                            $blacklist = [];
                            $whitelist = ["NamaTournament", "NamaTournamentEN"];
                            $queryAcitivy = g_generateActivityQuery("tournament", "Delete", $row, null, $blacklist, $whitelist);
                            $this->activityController->generateActivity(
                                $post['IDUserLogin'],
                                $UserLogin,
                                $queryAcitivy,
                                $FullDevice,
                                $IP,
                                $Category,
                                $KodeUnik
                            );
                        }
                    }
                }

                // --------------------------------------
                // FINAL RETURN
                // --------------------------------------
                if ($statusDelete === 1 && empty($errorList)) {
                    return [
                        "Status" => 1,
                        "Keterangan" => "Semua data berhasil dihapus",
                        "listErrorMessage" => []
                    ];
                } else {
                    return [
                        "Status" => 0,
                        "Keterangan" => "",
                        "listErrorMessage" => $errorList
                    ];
                }
            });
        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage(),
                "listErrorMessage" => []
            ];
        }
    }

    // ========================
    // STATUS MANAGEMENT
    // ========================
    public function updateStatus($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $StatusTournament = $post['StatusTournament'] ?? '';
            $IDUserLogin = $post['IDUserLogin'] ?? 0;
            $clearStanding = isset($post['clearStanding']) ? intval($post['clearStanding']) : 0;

            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak valid");
            }

            if (empty($StatusTournament)) {
                throw new Exception("Status tidak boleh kosong");
            }

            // Status yang valid
            $validStatuses = ['draft', 'open_registration', 'closed_registration', 'ongoing', 'finished', 'cancelled'];
            if (!in_array($StatusTournament, $validStatuses)) {
                throw new Exception("Status tidak valid");
            }

            // Check user access
            $AksesNeeded = $this->sv . ".Modify";
            $userAksesFilterParam = [
                ["IDUser", $IDUserLogin, 1]
            ];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $userAksesController = new UserAksesController($this->conn);
            $AksesResult = $userAksesController->select($userAksesselectParam);

            if (
                $AksesResult['Status'] != 1 ||
                !is_array($AksesResult['list_data']) ||
                count($AksesResult['list_data']) <= 0
            ) {
                throw new Exception("Akses tidak ditemukan");
            }

            // Get current status
            $sql = "SELECT StatusTournament, NamaTournament FROM tournament WHERE IDTournament = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();
            $result = $stmt->get_result();

            if ($result->num_rows === 0) {
                throw new Exception("Tournament tidak ditemukan");
            }

            $tournamentData = $result->fetch_assoc();
            $currentStatus = $tournamentData['StatusTournament'];

            // Validate status transition
            $this->validateStatusTransition($currentStatus, $StatusTournament);

            // Update status
            $sql = "UPDATE tournament SET StatusTournament = ?, LastUpdateAt = NOW(), LastUpdateBy = ? WHERE IDTournament = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("sii", $StatusTournament, $IDUserLogin, $IDTournament);
            $stmt->execute();

            // ==========================================
            // HANDLE STANDING & RANKING
            // ==========================================

            // If tournament finished, generate standing & ranking
            if ($StatusTournament === 'finished') {
                try {
                    // Generate standing
                    $this->generateStandings(['IDTournament' => $IDTournament]);

                    // Generate ranking dari tournament ini
                    $this->generateTournamentRanking(['IDTournament' => $IDTournament]);

                } catch (Exception $e) {
                    // Log error but don't break status update
                    error_log('Generate standing/ranking failed: ' . $e->getMessage());
                }
            }

            // If moving from finished to other status, rollback ranking
            if ($clearStanding === 1 && $currentStatus === 'finished' && $StatusTournament !== 'finished') {
                try {


                    // Rollback ranking (hapus history & recalculate)
                    $this->rollbackTournamentRanking(['IDTournament' => $IDTournament]);

                    // Hapus standing
                    $sqlDelStand = "DELETE FROM tournament_standing WHERE IDTournament = ?";
                    $stmtDelStand = $this->conn->prepare($sqlDelStand);
                    if ($stmtDelStand) {
                        $stmtDelStand->bind_param("i", $IDTournament);
                        $stmtDelStand->execute();
                        $stmtDelStand->close();
                    }

                } catch (Exception $e) {
                    // Log error but don't break status update
                    error_log('Rollback standing/ranking failed: ' . $e->getMessage());
                }
            }

            return [
                "Status" => 1,
                "Keterangan" => "Status tournament berhasil diubah",
                "CurrentStatus" => $StatusTournament
            ];

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    private function validateStatusTransition($currentStatus, $newStatus)
    {
        $validTransitions = [
            'draft' => ['open_registration', 'cancelled'],
            'open_registration' => ['closed_registration', 'draft', 'cancelled'],
            'closed_registration' => ['ongoing', 'open_registration', 'cancelled'],
            'ongoing' => ['finished', 'closed_registration', 'cancelled'],
            'finished' => ['ongoing'],
            'cancelled' => []
        ];

        if (!isset($validTransitions[$currentStatus])) {
            throw new Exception("Status saat ini '{$currentStatus}' tidak valid");
        }

        if (!in_array($newStatus, $validTransitions[$currentStatus])) {
            throw new Exception("Tidak dapat mengubah status dari '{$currentStatus}' ke '{$newStatus}'");
        }
    }

    // ========================
    // PARTICIPANT MANAGEMENT
    // ========================
    public function addParticipant($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $IDTCL = isset($post['IDTCL']) ? intval($post['IDTCL']) : 0;
            $TeamName = trim($post['TeamName'] ?? '');
            $IDPlayer1 = isset($post['IDPlayer1']) ? intval($post['IDPlayer1']) : 0;
            $IDPlayer2 = isset($post['IDPlayer2']) ? intval($post['IDPlayer2']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;


            if ($IDTournament <= 0 || $IDTCL <= 0) {
                throw new Exception("IDTournament atau IDTCL tidak valid");
            }

            if ($IDPlayer1 <= 0) {
                throw new Exception("Player 1 harus dipilih");
            }

            if (empty($TeamName)) {
                throw new Exception("Nama team tidak boleh kosong");
            }

            // Check user access
            $UserLogin = "";
            $AksesNeeded = $this->sv . ".AddParticipant";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $userAksesController = new UserAksesController($this->conn);
            $userAksesresult = $userAksesController->select($userAksesselectParam);
            $listAkses = array_column($userAksesresult['list_data'], 'AksesName');

            if ($userAksesresult['Status'] == 1 && !empty($listAkses)) {
                if (!in_array($AksesNeeded, $listAkses)) {
                    $msg = "User tidak memiliki otoritas akses - [" . $AksesNeeded . "]";
                    throw new Exception($msg);
                }

                $UserLogin = $userAksesresult['Username'];
            } else {
                $msg = $userAksesresult['Keterangan'];
                throw new Exception($msg);
            }

            return runWithTransaction($this->conn, callback: function ($conn) use ($IDTournament, $IDTCL, $TeamName, $IDPlayer1, $IDPlayer2, $UserLogin) {

                // check player duplikate
                $sql = "SELECT tm.IDTeam, t.NamaTournament, tm.IDPlayer , p.full_name
                        FROM team_member tm
                        JOIN team te ON tm.IDTeam = te.IDTeam
                        JOIN tournament_registration tr ON te.IDTeam = tr.IDTeam
                        JOIN tournament t ON tr.IDTournament = t.IDTournament
                        JOIN player p ON tm.IDPlayer = p.IDPlayer
                        WHERE tm.IDPlayer IN (?, ?) AND t.IDTournament = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("iii", $IDPlayer1, $IDPlayer2, $IDTournament);
                $stmt->execute();
                $result = $stmt->get_result();
                $duplikatPlayers = [];
                while ($row = $result->fetch_assoc()) {
                    $duplikatPlayers[] = [
                        "IDTeam" => $row['IDTeam'],
                        "NamaTournament" => $row['NamaTournament'],
                        "IDPlayer" => $row['IDPlayer'],
                        "full_name" => $row['full_name']
                    ];
                }

                if (!empty($duplikatPlayers)) {
                    throw new Exception("Player " . implode(", ", array_column($duplikatPlayers, 'full_name')) . " sudah terdaftar di tournament lain");
                }

                // Insert team
                $sql = "INSERT INTO team (IDTournament, IDTCL, TeamName, CreateAt) VALUES (?, ?, ?, NOW())";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("iis", $IDTournament, $IDTCL, $TeamName);
                $stmt->execute();
                $IDTeam = $stmt->insert_id;

                // Insert team members
                $sql = "INSERT INTO team_member (IDTeam, IDPlayer) VALUES (?, ?)";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("ii", $IDTeam, $IDPlayer1);
                $stmt->execute();

                if ($IDPlayer2 > 0) {
                    $stmt->bind_param("ii", $IDTeam, $IDPlayer2);
                    $stmt->execute();
                }

                // Insert tournament registration
                $sql = "INSERT INTO tournament_registration (IDTournament, IDTCL, IDTeam, RegistrationDate, `Status`, CreateBy, CreateAt,LastUpdateBy, LastUpdateAt) 
                        VALUES (?, ?, ?, NOW(), 'active', ?, NOW(), ?, NOW())";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("iiiss", $IDTournament, $IDTCL, $IDTeam, $UserLogin, $UserLogin);
                $stmt->execute();

                return [
                    "Status" => 1,
                    "Keterangan" => "Peserta berhasil ditambahkan",
                    "IDTeam" => $IDTeam
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    public function deleteParticipant($post)
    {
        try {
            $IDTeam = isset($post['IDTeam']) ? intval($post['IDTeam']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDTeam <= 0) {
                throw new Exception("IDTeam tidak valid");
            }

            // Check user access
            $UserLogin = "";
            $AksesNeeded = $this->sv . ".Delete";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $userAksesController = new UserAksesController($this->conn);
            $userAksesresult = $userAksesController->select($userAksesselectParam);
            if ($userAksesresult['Status'] == 1 && !empty($listAkses)) {
                if (!in_array($AksesNeeded, $listAkses)) {
                    $msg = "User tidak memiliki otoritas akses - [" . $AksesNeeded . "]";
                    throw new Exception($msg);
                }

                $UserLogin = $userAksesresult['Username'];
            } else {
                $msg = $userAksesresult['Keterangan'];
                throw new Exception($msg);
            }

            return runWithTransaction($this->conn, function ($conn) use ($IDTeam, $UserLogin) {
                // Delete tournament registration
                $sql = "DELETE FROM tournament_registration WHERE IDTeam = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $IDTeam);
                $stmt->execute();

                // Delete team members
                $sql = "DELETE FROM team_member WHERE IDTeam = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $IDTeam);
                $stmt->execute();

                // Delete team
                $sql = "DELETE FROM team WHERE IDTeam = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $IDTeam);
                $stmt->execute();

                return [
                    "Status" => 1,
                    "Keterangan" => "Peserta berhasil dihapus"
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    // ========================
    // SCOREBOARD MANAGEMENT
    // ========================
    public function addMatch($post)
    {
        try {
            $IDTCL = isset($post['IDTCL']) ? intval($post['IDTCL']) : 0;
            $IDStage = isset($post['IDStage']) ? intval($post['IDStage']) : 0;
            $IDGroup = isset($post['IDGroup']) ? intval($post['IDGroup']) : 0;
            $TeamA = isset($post['TeamA']) ? intval($post['TeamA']) : 0;
            $TeamB = isset($post['TeamB']) ? intval($post['TeamB']) : 0;
            $MatchDate = $post['MatchDate'] ?? null;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            // accept either IDStage or IDGroup (prefer IDGroup if provided)
            $refId = $IDGroup > 0 ? $IDGroup : $IDStage;
            if ($IDTCL <= 0 || $refId <= 0) {
                throw new Exception("IDTCL atau IDGroup/IDStage tidak valid");
            }

            if ($TeamA <= 0 || $TeamB <= 0) {
                throw new Exception("Tim A dan Tim B harus dipilih");
            }

            if ($TeamA === $TeamB) {
                throw new Exception("Tim A dan Tim B tidak boleh sama");
            }

            // Check user access
            $UserLogin = "";
            $AksesNeeded = $this->sv . ".AddMatch";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $userAksesController = new UserAksesController($this->conn);
            $userAksesresult = $userAksesController->select($userAksesselectParam);
            if ($userAksesresult['Status'] == 1 && !empty($listAkses)) {
                if (!in_array($AksesNeeded, $listAkses)) {
                    $msg = "User tidak memiliki otoritas akses - [" . $AksesNeeded . "]";
                    throw new Exception($msg);
                }

                $UserLogin = $userAksesresult['Username'];
            } else {
                $msg = $userAksesresult['Keterangan'];
                throw new Exception($msg);
            }

            return runWithTransaction($this->conn, function ($conn) use ($IDTCL, $refId, $IDStage, $IDGroup, $TeamA, $TeamB, $MatchDate, $UserLogin) {
                // Get next match number (use IDGroup if provided, else IDStage)
                $col = $IDGroup > 0 ? 'IDGroup' : 'IDStage';
                $sql = "SELECT MAX(MatchNumber) as maxMatch FROM tournament_match WHERE " . $col . " = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("i", $refId);
                $stmt->execute();
                $result = $stmt->get_result();
                $row = $result->fetch_assoc();
                $MatchNumber = ($row['maxMatch'] ?? 0) + 1;

                // Insert match
                if ($IDGroup > 0) {
                    $sql = "INSERT INTO tournament_match (IDTCL, IDGroup, RoundNumber, MatchNumber, TeamA, TeamB, MatchDate, Status, CreateBy, CreateAt, LastUpdateBy, LastUpdateAt) 
                        VALUES (?, ?, 1, ?, ?, ?, ?, 'pending', ?, NOW(), ?, NOW())";
                    $stmt = $conn->prepare($sql);
                    $stmt->bind_param("iiiiisss", $IDTCL, $IDGroup, $MatchNumber, $TeamA, $TeamB, $MatchDate, $UserLogin, $UserLogin);
                } else {
                    $sql = "INSERT INTO tournament_match (IDTCL, IDStage, RoundNumber, MatchNumber, TeamA, TeamB, MatchDate, Status, CreateBy, CreateAt, LastUpdateBy, LastUpdateAt) 
                        VALUES (?, ?, 1, ?, ?, ?, ?, 'pending', ?, NOW(), ?, NOW())";
                    $stmt = $conn->prepare($sql);
                    $stmt->bind_param("iiiiisss", $IDTCL, $IDStage, $MatchNumber, $TeamA, $TeamB, $MatchDate, $UserLogin, $UserLogin);
                }
                $stmt->execute();
                $IDMatch = $stmt->insert_id;

                return [
                    "Status" => 1,
                    "Keterangan" => "Pertandingan berhasil ditambahkan",
                    "IDMatch" => $IDMatch
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    public function updateMatchScore($post)
    {
        try {
            $IDMatch = isset($post['IDMatch']) ? intval($post['IDMatch']) : 0;
            $WinnerTeam = isset($post['WinnerTeam']) ? intval($post['WinnerTeam']) : null;
            $ScoresRaw = $post['Scores'] ?? [];
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDMatch <= 0) {
                throw new Exception("IDMatch tidak valid");
            }

            // Ensure the match belongs to a tournament whose status is 'ongoing'
            $stmtChk = $this->conn->prepare("SELECT IDGroup,  IDTCL FROM tournament_match WHERE IDMatch = ?");
            $stmtChk->bind_param("i", $IDMatch);
            $stmtChk->execute();
            $resChk = $stmtChk->get_result();
            if ($resChk->num_rows === 0) {
                throw new Exception("Match tidak ditemukan");
            }
            $mrow = $resChk->fetch_assoc();
            $IDGroupFromMatch = intval($mrow['IDGroup'] ?? 0);
            $IDTCLFromMatch = intval($mrow['IDTCL'] ?? 0);

            $IDTournament = 0;
            if ($IDGroupFromMatch > 0) {
                $s1 = $this->conn->prepare("SELECT IDTournament FROM tournament_group WHERE IDGroup = ? LIMIT 1");
                $s1->bind_param("i", $IDGroupFromMatch);
                $s1->execute();
                $r1 = $s1->get_result();
                if ($r1 && $r1->num_rows)
                    $IDTournament = intval($r1->fetch_assoc()['IDTournament']);
            }
            if ($IDTournament === 0 && $IDTCLFromMatch > 0) {
                $s3 = $this->conn->prepare("SELECT IDTournament FROM tournament_category_level WHERE IDTCL = ? LIMIT 1");
                $s3->bind_param("i", $IDTCLFromMatch);
                $s3->execute();
                $r3 = $s3->get_result();
                if ($r3 && $r3->num_rows)
                    $IDTournament = intval($r3->fetch_assoc()['IDTournament']);
            }

            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak ditemukan untuk match ini");
            }

            $stmtT = $this->conn->prepare("SELECT StatusTournament FROM tournament WHERE IDTournament = ? LIMIT 1");
            $stmtT->bind_param("i", $IDTournament);
            $stmtT->execute();
            $rt = $stmtT->get_result();
            if ($rt->num_rows === 0)
                throw new Exception("Tournament tidak ditemukan");
            $statusRow = $rt->fetch_assoc();
            if (($statusRow['StatusTournament'] ?? '') !== 'ongoing') {
                throw new Exception("Operasi hanya diizinkan saat tournament dalam status 'ongoing'");
            }

            // ==========================================
            // VALIDASI SINGLE ELIMINATION LOCK
            // ==========================================
            $stmtFormat = $this->conn->prepare("
                SELECT TournamentFormat 
                FROM tournament 
                WHERE IDTournament = ?
                LIMIT 1
            ");
            $stmtFormat->bind_param("i", $IDTournament);
            $stmtFormat->execute();
            $formatRow = $stmtFormat->get_result()->fetch_assoc();

            if (($formatRow['TournamentFormat'] ?? '') === 'single_elimination') {

                // Ambil round match ini
                $stmtRound = $this->conn->prepare("
                    SELECT RoundNumber, IDGroup
                    FROM tournament_match
                    WHERE IDMatch = ?
                    LIMIT 1
                ");
                $stmtRound->bind_param("i", $IDMatch);
                $stmtRound->execute();
                $roundRow = $stmtRound->get_result()->fetch_assoc();

                $currentRound = intval($roundRow['RoundNumber'] ?? 0);
                $currentGroup = intval($roundRow['IDGroup'] ?? 0);

                // Cek apakah sudah ada ronde lebih tinggi di group yg sama
                $stmtNext = $this->conn->prepare("
                        SELECT 1
                        FROM tournament_match
                        WHERE IDTournament = ?
                        AND IDGroup = ?
                        AND RoundNumber > ?
                        LIMIT 1
                    ");
                $stmtNext->bind_param("iii", $IDTournament, $currentGroup, $currentRound);
                $stmtNext->execute();

                if ($stmtNext->get_result()->num_rows > 0) {
                    throw new Exception("Tidak bisa update score. Ronde selanjutnya sudah dibuat.");
                }
            }

            /*
            ==========================================
            DECODE JSON SCORES
            ==========================================
            */
            $Scores = [];

            if (is_string($ScoresRaw)) {
                $decoded = json_decode($ScoresRaw, true);
                if (json_last_error() !== JSON_ERROR_NONE) {
                    throw new Exception("Format Scores tidak valid (JSON error)");
                }
                $Scores = $decoded;
            } elseif (is_array($ScoresRaw)) {
                $Scores = $ScoresRaw;
            }

            if (empty($Scores) || !is_array($Scores)) {
                throw new Exception("Scores tidak boleh kosong");
            }

            /*
            ==========================================
            CEK AKSES USER
            ==========================================
            */
            $AksesNeeded = $this->sv . ".AddMatch";

            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $userAksesresult = $this->userAksesController->select($userAksesselectParam);

            if ($userAksesresult['Status'] != 1 || empty($userAksesresult['list_data'])) {
                throw new Exception("Akses tidak ditemukan");
            }

            $listAkses = array_column($userAksesresult['list_data'], 'AksesName');
            if (!in_array($AksesNeeded, $listAkses)) {
                throw new Exception("User tidak memiliki otoritas akses - [$AksesNeeded]");
            }

            $UserLogin = $userAksesresult['Username'] ?? '';

            /*
            ==========================================
            TRANSACTION
            ==========================================
            */
            return runWithTransaction($this->conn, function ($conn) use ($IDMatch, $WinnerTeam, $Scores, $UserLogin) {

                /*
                ------------------------------------------
                DELETE OLD SETS
                ------------------------------------------
                */
                $stmt = $conn->prepare("DELETE FROM tournament_match_set WHERE IDMatch = ?");
                $stmt->bind_param("i", $IDMatch);

                if (!$stmt->execute()) {
                    throw new Exception("Gagal hapus set lama: " . $stmt->error);
                }

                /*
                ------------------------------------------
                INSERT NEW SETS
                ------------------------------------------
                */
                $stmt = $conn->prepare("
                INSERT INTO tournament_match_set 
                (IDMatch, SetNumber, ScoreTeamA, ScoreTeamB) 
                VALUES (?, ?, ?, ?)
            ");

                $insertCount = 0;

                foreach ($Scores as $score) {

                    $SetNumber = intval($score['SetNumber'] ?? 0);
                    $ScoreTeamA = intval($score['ScoreTeamA'] ?? 0);
                    $ScoreTeamB = intval($score['ScoreTeamB'] ?? 0);

                    if ($SetNumber <= 0) {
                        continue;
                    }

                    $stmt->bind_param("iiii", $IDMatch, $SetNumber, $ScoreTeamA, $ScoreTeamB);

                    if (!$stmt->execute()) {
                        throw new Exception("Gagal insert set: " . $stmt->error);
                    }

                    $insertCount++;
                }

                if ($insertCount <= 0) {
                    throw new Exception("Tidak ada set yang berhasil di-insert");
                }

                /*
                ------------------------------------------
                UPDATE MATCH STATUS & WINNER
                ------------------------------------------
                */
                $sql = "UPDATE tournament_match SET Status = 'finished'";

                if (!is_null($WinnerTeam)) {
                    $sql .= ", WinnerTeam = ?";
                }

                $sql .= " WHERE IDMatch = ?";

                $stmt = $conn->prepare($sql);

                if (!is_null($WinnerTeam)) {
                    $stmt->bind_param("ii", $WinnerTeam, $IDMatch);
                } else {
                    $stmt->bind_param("i", $IDMatch);
                }

                if (!$stmt->execute()) {
                    throw new Exception("Gagal update match: " . $stmt->error);
                }

                return [
                    "Status" => 1,
                    "Keterangan" => "Score pertandingan berhasil diperbarui"
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    public function updateMatch($post)
    {
        try {

            $IDMatch = isset($post['IDMatch']) ? intval($post['IDMatch']) : 0;
            $MatchDate = $post['MatchDate'] ?? null;
            $Status = $post['Status'] ?? null;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            // ⚡ FIXED: jangan konversi 0 jadi null
            $WinnerTeam = null;
            if (array_key_exists('WinnerTeam', $post)) {
                if ($post['WinnerTeam'] === '' || $post['WinnerTeam'] === null) {
                    $WinnerTeam = null;
                } else {
                    $WinnerTeam = intval($post['WinnerTeam']); // bisa 0 atau >0
                }
            }

            if ($IDMatch <= 0)
                throw new Exception("IDMatch tidak valid");

            // Ensure the match belongs to a tournament whose status is 'ongoing'
            $stmtChk = $this->conn->prepare("SELECT IDGroup,  IDTCL FROM tournament_match WHERE IDMatch = ?");
            $stmtChk->bind_param("i", $IDMatch);
            $stmtChk->execute();
            $resChk = $stmtChk->get_result();
            if ($resChk->num_rows === 0) {
                throw new Exception("Match tidak ditemukan");
            }
            $mrow = $resChk->fetch_assoc();
            $IDGroupFromMatch = intval($mrow['IDGroup'] ?? 0);
            $IDTCLFromMatch = intval($mrow['IDTCL'] ?? 0);

            $IDTournament = 0;
            if ($IDGroupFromMatch > 0) {
                $s1 = $this->conn->prepare("SELECT IDTournament FROM tournament_group WHERE IDGroup = ? LIMIT 1");
                $s1->bind_param("i", $IDGroupFromMatch);
                $s1->execute();
                $r1 = $s1->get_result();
                if ($r1 && $r1->num_rows)
                    $IDTournament = intval($r1->fetch_assoc()['IDTournament']);
            }

            if ($IDTournament === 0 && $IDTCLFromMatch > 0) {
                $s3 = $this->conn->prepare("SELECT IDTournament FROM tournament_category_level WHERE IDTCL = ? LIMIT 1");
                $s3->bind_param("i", $IDTCLFromMatch);
                $s3->execute();
                $r3 = $s3->get_result();
                if ($r3 && $r3->num_rows)
                    $IDTournament = intval($r3->fetch_assoc()['IDTournament']);
            }

            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak ditemukan untuk match ini");
            }

            $stmtT = $this->conn->prepare("SELECT StatusTournament FROM tournament WHERE IDTournament = ? LIMIT 1");
            $stmtT->bind_param("i", $IDTournament);
            $stmtT->execute();
            $rt = $stmtT->get_result();
            if ($rt->num_rows === 0)
                throw new Exception("Tournament tidak ditemukan");
            $statusRow = $rt->fetch_assoc();
            if (($statusRow['StatusTournament'] ?? '') !== 'ongoing') {
                throw new Exception("Operasi hanya diizinkan saat tournament dalam status 'ongoing'");
            }

            // ==========================================
            // VALIDASI SINGLE ELIMINATION LOCK
            // ==========================================
            $stmtFormat = $this->conn->prepare("
                SELECT TournamentFormat 
                FROM tournament 
                WHERE IDTournament = ?
                LIMIT 1
            ");
            $stmtFormat->bind_param("i", $IDTournament);
            $stmtFormat->execute();
            $formatRow = $stmtFormat->get_result()->fetch_assoc();

            if (($formatRow['TournamentFormat'] ?? '') === 'single_elimination') {
                if (array_key_exists('WinnerTeam', $post) && !is_null($WinnerTeam) && intval($WinnerTeam) === 0) {
                    throw new Exception("Single elimination tidak boleh berakhir draw");
                }
                // Ambil round & group
                $stmtRound = $this->conn->prepare("
                    SELECT RoundNumber, IDGroup
                    FROM tournament_match
                    WHERE IDMatch = ?
                    LIMIT 1
                ");
                $stmtRound->bind_param("i", $IDMatch);
                $stmtRound->execute();
                $roundRow = $stmtRound->get_result()->fetch_assoc();

                $currentRound = intval($roundRow['RoundNumber'] ?? 0);
                $currentGroup = intval($roundRow['IDGroup'] ?? 0);

                // Cek ronde lebih tinggi di group yg sama
                $stmtNext = $this->conn->prepare("
                    SELECT 1
                    FROM tournament_match
                    WHERE IDTournament = ?
                    AND IDGroup = ?
                    AND RoundNumber > ?
                    LIMIT 1
                ");
                $stmtNext->bind_param("iii", $IDTournament, $currentGroup, $currentRound);
                $stmtNext->execute();

                if ($stmtNext->get_result()->num_rows > 0) {
                    throw new Exception("Tidak bisa modify match. Ronde selanjutnya sudah dibuat.");
                }
            }

            // ===============================
            // VALIDASI AKSES
            // ===============================
            $AksesNeeded = $this->sv . ".Modify";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = [
                "filters" => $userAksesFilterParam,
                "rows_per_page" => 1,
                "page_number" => 1,
                "no_count" => true
            ];

            $AksesResult = $this->userAksesController->select($userAksesselectParam);

            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");

            $UserLogin = $AksesResult['Username'];

            // ===============================
            // AUTO STATUS LOGIC
            // ===============================
            if (array_key_exists('WinnerTeam', $post)) {

                if (is_null($WinnerTeam)) {
                    $Status = 'pending';
                } else {
                    $Status = 'finished';
                }
            }

            // ===============================
            // BUILD QUERY DINAMIS
            // ===============================
            $sql = "UPDATE tournament_match SET ";
            $parts = [];
            $params = [];
            $types = '';

            if (!is_null($MatchDate)) {
                $parts[] = 'MatchDate = ?';
                $params[] = $MatchDate;
                $types .= 's';
            }

            if (!is_null($Status)) {
                $parts[] = 'Status = ?';
                $params[] = $Status;
                $types .= 's';
            }

            // ⚡ HANDLE NULL SECARA BENAR
            if (array_key_exists('WinnerTeam', $post)) {

                if (is_null($WinnerTeam)) {
                    $parts[] = 'WinnerTeam = NULL';
                } else {
                    $parts[] = 'WinnerTeam = ?';
                    $params[] = $WinnerTeam; // bisa 0 atau >0
                    $types .= 'i';
                }
            }

            // audit trail
            $parts[] = 'LastUpdateBy = ?';
            $params[] = $UserLogin;
            $types .= 's';

            $parts[] = 'LastUpdateAt = NOW()';

            if (empty($parts))
                throw new Exception("Tidak ada data untuk di-update");

            $sql .= implode(', ', $parts) . ' WHERE IDMatch = ?';
            $params[] = $IDMatch;
            $types .= 'i';

            $stmt = $this->conn->prepare($sql);

            if (!empty($params)) {
                $stmt->bind_param($types, ...$params);
            }

            $stmt->execute();

            return [
                "Status" => 1,
                "Keterangan" => "Match berhasil diupdate"
            ];

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    public function deleteMatch($post)
    {
        try {
            $IDMatch = isset($post['IDMatch']) ? intval($post['IDMatch']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;
            if ($IDMatch <= 0)
                throw new Exception("IDMatch tidak valid");

            // access
            $AksesNeeded = $this->sv . ".Modify";

            // Ensure the match belongs to a tournament whose status is 'ongoing'
            $stmtChk = $this->conn->prepare("SELECT IDGroup,  IDTCL FROM tournament_match WHERE IDMatch = ?");
            $stmtChk->bind_param("i", $IDMatch);
            $stmtChk->execute();
            $resChk = $stmtChk->get_result();
            if ($resChk->num_rows === 0) {
                throw new Exception("Match tidak ditemukan");
            }
            $mrow = $resChk->fetch_assoc();
            $IDGroupFromMatch = intval($mrow['IDGroup'] ?? 0);
            $IDTCLFromMatch = intval($mrow['IDTCL'] ?? 0);

            $IDTournament = 0;
            if ($IDGroupFromMatch > 0) {
                $s1 = $this->conn->prepare("SELECT IDTournament FROM tournament_group WHERE IDGroup = ? LIMIT 1");
                $s1->bind_param("i", $IDGroupFromMatch);
                $s1->execute();
                $r1 = $s1->get_result();
                if ($r1 && $r1->num_rows)
                    $IDTournament = intval($r1->fetch_assoc()['IDTournament']);
            }
            if ($IDTournament === 0 && $IDTCLFromMatch > 0) {
                $s3 = $this->conn->prepare("SELECT IDTournament FROM tournament_category_level WHERE IDTCL = ? LIMIT 1");
                $s3->bind_param("i", $IDTCLFromMatch);
                $s3->execute();
                $r3 = $s3->get_result();
                if ($r3 && $r3->num_rows)
                    $IDTournament = intval($r3->fetch_assoc()['IDTournament']);
            }

            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak ditemukan untuk match ini");
            }

            $stmtT = $this->conn->prepare("SELECT StatusTournament FROM tournament WHERE IDTournament = ? LIMIT 1");
            $stmtT->bind_param("i", $IDTournament);
            $stmtT->execute();
            $rt = $stmtT->get_result();
            if ($rt->num_rows === 0)
                throw new Exception("Tournament tidak ditemukan");
            $statusRow = $rt->fetch_assoc();
            if (($statusRow['StatusTournament'] ?? '') !== 'ongoing') {
                throw new Exception("Operasi hanya diizinkan saat tournament dalam status 'ongoing'");
            }
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = ["filters" => $userAksesFilterParam, "rows_per_page" => 1, "page_number" => 1, "no_count" => true];
            $AksesResult = $this->userAksesController->select($userAksesselectParam);
            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");

            $this->conn->begin_transaction();
            try {
                $delSets = $this->conn->prepare("DELETE FROM tournament_match_set WHERE IDMatch = ?");
                $delSets->bind_param("i", $IDMatch);
                $delSets->execute();

                $del = $this->conn->prepare("DELETE FROM tournament_match WHERE IDMatch = ?");
                $del->bind_param("i", $IDMatch);
                $del->execute();

                $this->conn->commit();
            } catch (Exception $e) {
                $this->conn->rollback();
                throw $e;
            }

            return ["Status" => 1, "Keterangan" => "Match berhasil dihapus"];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }

    public function getParticipants($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $IDTCL = isset($post['IDTCL']) ? intval($post['IDTCL']) : 0;

            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak valid");
            }

            $sql = "SELECT 
                        t.IDTeam,
                        t.TeamName,
                        tr.IDRegistration,
                        tr.PaymentStatus,
                        tr.Status,
                        GROUP_CONCAT(p.full_name SEPARATOR ', ') as Players
                    FROM team t
                    LEFT JOIN tournament_registration tr ON t.IDTeam = tr.IDTeam
                    LEFT JOIN team_member tm ON t.IDTeam = tm.IDTeam
                    LEFT JOIN player p ON tm.IDPlayer = p.IDPlayer
                    WHERE t.IDTournament = ?";

            $params = [$IDTournament];

            if ($IDTCL > 0) {
                $sql .= " AND t.IDTCL = ?";
                $params[] = $IDTCL;
            }

            $sql .= " GROUP BY t.IDTeam ORDER BY t.CreateAt DESC";

            $stmt = $this->conn->prepare($sql);
            if (!empty($params)) {
                $types = str_repeat('i', count($params));
                $stmt->bind_param($types, ...$params);
            }
            $stmt->execute();
            $result = $stmt->get_result();

            $participants = [];
            while ($row = $result->fetch_assoc()) {
                $participants[] = $row;
            }

            return [
                "Status" => 1,
                "Keterangan" => "Data peserta berhasil diambil",
                "list_data" => $participants
            ];

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage(),
                "list_data" => []
            ];
        }
    }

    public function getMatches($post)
    {
        try {
            $IDTCL = isset($post['IDTCL']) ? intval($post['IDTCL']) : 0;
            $IDStage = isset($post['IDStage']) ? intval($post['IDStage']) : 0;

            if ($IDTCL <= 0) {
                throw new Exception("IDTCL tidak valid");
            }

            $sql = "SELECT 
                        m.IDMatch,
                        m.MatchNumber,
                        m.MatchDate,
                        m.Status,
                        tA.TeamName as TeamAName,
                        tB.TeamName as TeamBName,
                        tW.TeamName as WinnerTeamName
                    FROM tournament_match m
                    LEFT JOIN team tA ON m.TeamA = tA.IDTeam
                    LEFT JOIN team tB ON m.TeamB = tB.IDTeam
                    LEFT JOIN team tW ON m.WinnerTeam = tW.IDTeam
                    WHERE m.IDTCL = ?";

            $params = [$IDTCL];

            if ($IDStage > 0) {
                $sql .= " AND m.IDStage = ?";
                $params[] = $IDStage;
            }

            $sql .= " ORDER BY m.MatchNumber ASC";

            $stmt = $this->conn->prepare($sql);
            if (!empty($params)) {
                $types = str_repeat('i', count($params));
                $stmt->bind_param($types, ...$params);
            }
            $stmt->execute();
            $result = $stmt->get_result();

            $matches = [];
            while ($row = $result->fetch_assoc()) {
                $row['Scores'] = [];

                // Get match scores (use tournament_match_set)
                $scoreSql = "SELECT SetNumber, ScoreTeamA, ScoreTeamB FROM tournament_match_set WHERE IDMatch = ? ORDER BY SetNumber";
                $scoreStmt = $this->conn->prepare($scoreSql);
                $scoreStmt->bind_param("i", $row['IDMatch']);
                $scoreStmt->execute();
                $scoreResult = $scoreStmt->get_result();

                while ($scoreRow = $scoreResult->fetch_assoc()) {
                    $row['Scores'][] = $scoreRow;
                }

                $matches[] = $row;
            }

            return [
                "Status" => 1,
                "Keterangan" => "Data pertandingan berhasil diambil",
                "list_data" => $matches
            ];

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage(),
                "list_data" => []
            ];
        }
    }

    // ========================
    // REGISTRATION DETAIL & ACTIONS
    // ========================
    public function getRegistrationDetail($post)
    {
        $IDRegistration = isset($post['IDRegistration']) ? intval($post['IDRegistration']) : 0;
        if ($IDRegistration <= 0) {
            return ['Status' => 0, 'Keterangan' => 'Invalid IDRegistration'];
        }

        $baseUrl = ROOT_DOMAIN . "api/assets/tournament_payment/";

        try {
            $stmt = $this->conn->prepare("SELECT 
                r.*, 
                t.NamaTournament, t.Location AS Lokasi, t.Gambar, t.TournamentStart, t.TournamentEnd, t.IDTournament,
                tcl.IDTCL, tcl.RegistrationFee AS CategoryRegistrationFee, k.NamaKategori, l.NamaLevel, team.TeamName
                FROM tournament_registration r
                LEFT JOIN tournament t ON r.IDTournament = t.IDTournament
                LEFT JOIN tournament_category_level tcl ON r.IDTCL = tcl.IDTCL
                LEFT JOIN kategori k ON tcl.IDKategori = k.IDKategori
                LEFT JOIN `level` l ON tcl.IDLevel = l.IDLevel
                LEFT JOIN team ON r.IDTeam = team.IDTeam
                WHERE r.IDRegistration = ? LIMIT 1");

            $stmt->bind_param("i", $IDRegistration);
            $stmt->execute();
            $res = $stmt->get_result();
            $row = $res->fetch_assoc();
            $stmt->close();

            if (!$row)
                return ['Status' => 0, 'Keterangan' => 'Registration not found'];

            if (!empty($row['PaymentProof'])) {
                $row['PaymentProof'] = $baseUrl . $row['IDTournament'] . "/" . $row['PaymentProof'];
            } else {
                $row['PaymentProof'] = "https://placehold.co/600x400?text=Belum+Data+Diupload";
            }

            $teamMembers = [];
            if (!empty($row['IDTeam'])) {
                $teamId = intval($row['IDTeam']);
                $stmt2 = $this->conn->prepare("SELECT tm.IDTeamMember, tm.IDPlayer, p.email, p.full_name, p.phone, p.verification_status,
                    (SELECT m.MembershipNumber FROM membership m WHERE m.IDPlayer = p.IDPlayer ORDER BY m.end_date DESC LIMIT 1) AS MembershipNumber,
                    (SELECT m.status FROM membership m WHERE m.IDPlayer = p.IDPlayer ORDER BY m.end_date DESC LIMIT 1) AS MembershipStatus,
                    (SELECT m.end_date FROM membership m WHERE m.IDPlayer = p.IDPlayer ORDER BY m.end_date DESC LIMIT 1) AS MembershipEndDate
                    FROM team_member tm
                    LEFT JOIN player p ON tm.IDPlayer = p.IDPlayer
                    WHERE tm.IDTeam = ?");

                $stmt2->bind_param("i", $teamId);
                $stmt2->execute();
                $res2 = $stmt2->get_result();
                while ($r2 = $res2->fetch_assoc())
                    $teamMembers[] = $r2;
                $stmt2->close();
            }

            return ['Status' => 1, 'Keterangan' => 'OK', 'data' => ['registration' => $row, 'team_members' => $teamMembers]];
        } catch (Exception $e) {
            return ['Status' => 0, 'Keterangan' => $e->getMessage()];
        }
    }

    public function setRegistrationPaymentApproval($post)
    {
        $IDRegistration = isset($post['IDRegistration']) ? intval($post['IDRegistration']) : 0;
        $paymentStatus = isset($post['PaymentStatus']) ? $post['PaymentStatus'] : null;
        $paymentRemarks = isset($post['PaymentRemarks']) ? $post['PaymentRemarks'] : null;

        if ($IDRegistration <= 0 || !$paymentStatus)
            return ['Status' => 0, 'Keterangan' => 'Invalid parameters'];

        try {
            $stmt = $this->conn->prepare("UPDATE tournament_registration SET PaymentStatus = ?, PaymentRemarks = ?, LastUpdateAt = NOW() WHERE IDRegistration = ?");
            $stmt->bind_param("ssi", $paymentStatus, $paymentRemarks, $IDRegistration);
            $ok = $stmt->execute();
            $stmt->close();

            $emailDebugInfo = []; // Untuk debugging email

            if ($ok && in_array($paymentStatus, ['rejected'], true)) {
                try {
                    $registrationDetail = $this->getRegistrationDetail(['IDRegistration' => $IDRegistration]);
                    if ($registrationDetail['Status'] === 1) {
                        $teamMembers = $registrationDetail['data']['team_members'] ?? [];
                        $registration = $registrationDetail['data']['registration'] ?? [];

                        $validRecipients = [];
                        $invalidRecipients = []; // Untuk tracking email yang gagal

                        foreach ($teamMembers as $member) {
                            if (isPlayerEmailVerified($member)) {
                                $validRecipients[] = $member;
                            } else {
                                $invalidRecipients[] = [
                                    'email' => $member['email'] ?? 'unknown',
                                    'name' => $member['full_name'] ?? 'unknown',
                                    'reason' => 'Email not verified'
                                ];
                            }
                        }

                        $emailDebugInfo['total_members'] = count($teamMembers);
                        $emailDebugInfo['valid_recipients'] = count($validRecipients);
                        $emailDebugInfo['invalid_recipients'] = $invalidRecipients;
                        $emailDebugInfo['recipients_detail'] = array_map(function ($m) {
                            return [
                                'email' => $m['email'] ?? 'unknown',
                                'name' => $m['full_name'] ?? 'unknown'
                            ];
                        }, $validRecipients);

                        if (!empty($validRecipients)) {
                            $template = $paymentStatus === 'paid'
                                ? 'registration_payment_approved.html'
                                : 'registration_payment_rejected.html';
                            $subject = $paymentStatus === 'paid'
                                ? 'Pembayaran Registrasi Turnamen Disetujui'
                                : 'Pembayaran Registrasi Turnamen Ditolak';

                            $sentEmails = [];
                            $failedEmails = [];

                            foreach ($validRecipients as $recipient) {
                                $emailParams = $this->buildRegistrationPaymentEmailParams(
                                    $registration,
                                    $teamMembers,
                                    $recipient,
                                    $paymentStatus,
                                    $paymentRemarks
                                );

                                try {
                                    $sendResult = sendMail(
                                        $recipient['email'],
                                        $subject,
                                        [
                                            'type' => 'html',
                                            'template' => $template,
                                            'params' => $emailParams
                                        ],
                                        [],
                                        'id'
                                    );

                                    if ($sendResult) {
                                        $sentEmails[] = $recipient['email'];
                                    } else {
                                        $failedEmails[] = $recipient['email'];
                                    }
                                } catch (Exception $emailEx) {
                                    $failedEmails[] = [
                                        'email' => $recipient['email'],
                                        'error' => $emailEx->getMessage()
                                    ];
                                }
                            }

                            $emailDebugInfo['sent_emails'] = $sentEmails;
                            $emailDebugInfo['failed_emails'] = $failedEmails;
                            $emailDebugInfo['email_status'] = count($failedEmails) === 0 ? 'all_sent' : 'partial_failed';
                        } else {
                            $emailDebugInfo['email_status'] = 'no_valid_recipients';
                            $emailDebugInfo['message'] = 'Tidak ada recipient email yang valid';
                        }
                    } else {
                        $emailDebugInfo['error'] = 'Failed to get registration detail';
                        $emailDebugInfo['detail_error'] = $registrationDetail['Keterangan'] ?? 'Unknown error';
                    }
                } catch (Throwable $e) {
                    $emailDebugInfo['error'] = 'Email sending error: ' . $e->getMessage();
                    $emailDebugInfo['trace'] = $e->getTraceAsString();
                    // Skip email errors so approval flow still succeeds.
                }
            }

            $response = [
                'Status' => $ok ? 1 : 0,
                'Keterangan' => $ok ? 'OK' : 'Failed to update'
            ];

            // Tambahkan debug info ke response jika ada error email atau untuk testing
            if (!empty($emailDebugInfo)) {
                $response['debug'] = $emailDebugInfo;

                // Jika ada email yang gagal, tambahkan warning
                if (isset($emailDebugInfo['failed_emails']) && count($emailDebugInfo['failed_emails']) > 0) {
                    $response['Keterangan'] .= ' - Email notification partial failed';
                    $response['email_warning'] = 'Beberapa email gagal terkirim. Cek log untuk detail.';
                }
            }

            return $response;
        } catch (Exception $e) {
            return [
                'Status' => 0,
                'Keterangan' => $e->getMessage(),
                'debug' => [
                    'exception' => $e->getMessage(),
                    'trace' => $e->getTraceAsString()
                ]
            ];
        }
    }

    private function buildRegistrationPaymentEmailParams(array $registration, array $teamMembers, array $recipient, string $paymentStatus, ?string $paymentRemarks = null): array
    {
        $partner1 = $teamMembers[0] ?? null;
        $partner2 = $teamMembers[1] ?? null;

        $tournamentImage = '';
        if (!empty($registration['Gambar'])) {
            $baseUrl = ROOT_DOMAIN . "admin/attachments/tournament/";
            $tournamentImage = $baseUrl . $registration['IDTournament'] . '/' . $registration['Gambar'];
        }

        $formattedDates = $this->formatTournamentDates($registration['TournamentStart'] ?? '', $registration['TournamentEnd'] ?? '');

        return [
            'NamaLengkap' => $recipient['full_name'] ?? $recipient['email'] ?? 'Peserta',
            'Email' => $recipient['email'] ?? '',
            'NoTelp' => $recipient['phone'] ?? '',
            'Tournament.NamaTournament' => $registration['NamaTournament'] ?? '',
            'Tournament.Lokasi' => $registration['Lokasi'] ?? '',
            'Tournament.Gambar' => $tournamentImage,
            'Tournament.Tanggal' => $formattedDates,
            'Tournament.URLLanding' => ROOT_DOMAIN . 'event/' . ($registration['IDTournament'] ?? ''),
            'TeamData.NamaTim' => $registration['TeamName'] ?? '',
            'TeamData.Kategori' => $registration['NamaKategori'] ?? '',
            'TeamData.Level' => $registration['NamaLevel'] ?? '',
            'TeamData.Partner1.Nama' => $partner1['full_name'] ?? '',
            'TeamData.Partner1.Email' => $partner1['email'] ?? '',
            'TeamData.Partner2.Nama' => $partner2['full_name'] ?? '',
            'TeamData.Partner2.Email' => $partner2['email'] ?? '',
            'AlasanDitolak' => $paymentStatus === 'rejected' ? ($paymentRemarks ?? '') : ''
        ];
    }

    private function formatTournamentDates(string $start, string $end): string
    {
        if (empty($start) && empty($end)) {
            return '';
        }

        try {
            $startDate = !empty($start) ? date('d F Y', strtotime($start)) : '';
            $endDate = !empty($end) ? date('d F Y', strtotime($end)) : '';
        } catch (Exception $e) {
            return trim(($start ?: '') . ' ' . ($end ?: ''));
        }

        if ($startDate && $endDate) {
            return $startDate === $endDate ? $startDate : $startDate . ' - ' . $endDate;
        }

        return $startDate ?: $endDate;
    }

    private function buildParticipationEmailParams(array $registration, array $teamMembers, array $recipient, string $status, ?string $remarks = null): array
    {
        $partner1 = $teamMembers[0] ?? null;
        $partner2 = $teamMembers[1] ?? null;

        $tournamentImage = '';
        if (!empty($registration['Gambar'])) {
            $tournamentImage = ROOT_DOMAIN . 'admin/attachments/tournament/' . ($registration['IDTournament'] ?? '') . '/' . $registration['Gambar'];
        }

        $formattedDates = $this->formatTournamentDates($registration['TournamentStart'] ?? '', $registration['TournamentEnd'] ?? '');

        $statusMappingId = [
            'active' => 'Diterima',
            'cancelled' => 'Dibatalkan',
            'disqualified' => 'Didiskualifikasi'
        ];

        $statusTextId = [
            'active' => 'diterima',
            'cancelled' => 'dibatalkan',
            'disqualified' => 'didiskualifikasi'
        ];

        $statusLabel = $statusMappingId[$status] ?? $status;
        $statusText = $statusTextId[$status] ?? $status;

        return [
            'NamaLengkap' => $recipient['full_name'] ?? $recipient['email'] ?? 'Peserta',
            'Email' => $recipient['email'] ?? '',
            'NoTelp' => $recipient['phone'] ?? '',
            'Tournament.NamaTournament' => $registration['NamaTournament'] ?? '',
            'Tournament.Lokasi' => $registration['Lokasi'] ?? '',
            'Tournament.Gambar' => $tournamentImage,
            'Tournament.Tanggal' => $formattedDates,
            'Tournament.URLLanding' => ROOT_DOMAIN . 'event/' . ($registration['IDTournament'] ?? ''),
            'TeamData.NamaTim' => $registration['TeamName'] ?? '',
            'TeamData.Kategori' => $registration['NamaKategori'] ?? '',
            'TeamData.Level' => $registration['NamaLevel'] ?? '',
            'TeamData.Partner1.Nama' => $partner1['full_name'] ?? '',
            'TeamData.Partner1.Email' => $partner1['email'] ?? '',
            'TeamData.Partner2.Nama' => $partner2['full_name'] ?? '',
            'TeamData.Partner2.Email' => $partner2['email'] ?? '',
            'StatusPartisipasi' => $statusLabel,
            'StatusPartisipasiText' => $statusText,
            'AlasanDitolak' => ($status === 'cancelled' || $status === 'disqualified') ? ($remarks ?? '') : ''
        ];
    }

    public function setRegistartionTournamentStatus($post)
    {
        $IDRegistration = isset($post['IDRegistration']) ? intval($post['IDRegistration']) : 0;
        $status = isset($post['Status']) ? $post['Status'] : null;
        $statusRemarks = isset($post['StatusRemarks']) ? $post['StatusRemarks'] : null;

        if ($IDRegistration <= 0 || !$status)
            return ['Status' => 0, 'Keterangan' => 'Invalid parameters'];

        try {
            $stmt = $this->conn->prepare("UPDATE tournament_registration SET `Status` = ?, StatusRemarks = ?, LastUpdateAt = NOW() WHERE IDRegistration = ?");
            $stmt->bind_param("ssi", $status, $statusRemarks, $IDRegistration);
            $ok = $stmt->execute();
            $stmt->close();

            $emailDebugInfo = [];

            // Send participation status emails when changed to active/cancelled/disqualified
            if ($ok && in_array($status, ['active', 'cancelled', 'disqualified'], true)) {
                try {
                    $registrationDetail = $this->getRegistrationDetail(['IDRegistration' => $IDRegistration]);
                    if ($registrationDetail['Status'] === 1) {
                        $registration = $registrationDetail['data']['registration'] ?? [];
                        $teamMembers = $registrationDetail['data']['team_members'] ?? [];

                        $validRecipients = [];
                        $invalidRecipients = [];

                        foreach ($teamMembers as $member) {
                            if (isPlayerEmailVerified($member)) {
                                $validRecipients[] = $member;
                            } else {
                                $invalidRecipients[] = [
                                    'email' => $member['email'] ?? 'unknown',
                                    'name' => $member['full_name'] ?? 'unknown',
                                    'reason' => 'Email not verified or invalid'
                                ];
                            }
                        }

                        $emailDebugInfo['total_members'] = count($teamMembers);
                        $emailDebugInfo['valid_recipients_count'] = count($validRecipients);
                        $emailDebugInfo['invalid_recipients'] = $invalidRecipients;

                        if (!empty($validRecipients)) {
                            $template = $status === 'active'
                                ? 'participation_status_accepted.html'
                                : 'participation_status_rejected.html';



                            $subject = $status === 'active'
                                ? 'Status Partisipasi Turnamen Diterima'
                                : 'Status Partisipasi Turnamen Dibatalkan';

                            $sentEmails = [];
                            $failedEmails = [];

                            foreach ($validRecipients as $recipient) {
                                $emailParams = $this->buildParticipationEmailParams(
                                    $registration,
                                    $teamMembers,
                                    $recipient,
                                    $status,
                                    $statusRemarks
                                );

                                try {
                                    $sendResult = sendMail(
                                        $recipient['email'],
                                        $subject,
                                        [
                                            'type' => 'html',
                                            'template' => $template,
                                            'params' => $emailParams
                                        ],
                                        [],
                                        'id'
                                    );

                                    if (!empty($sendResult) && ($sendResult['success'] ?? false)) {
                                        $sentEmails[] = $recipient['email'];
                                    } else {
                                        $failedEmails[] = $recipient['email'];
                                    }
                                } catch (Exception $ex) {
                                    $failedEmails[] = ['email' => $recipient['email'] ?? 'unknown', 'error' => $ex->getMessage()];
                                }
                            }

                            $emailDebugInfo['sent_emails'] = $sentEmails;
                            $emailDebugInfo['failed_emails'] = $failedEmails;
                            $emailDebugInfo['email_status'] = empty($failedEmails) ? 'all_sent' : 'partial_failed';
                        } else {
                            $emailDebugInfo['email_status'] = 'no_valid_recipients';
                        }
                    } else {
                        $emailDebugInfo['error'] = 'Failed to fetch registration detail';
                    }
                } catch (Throwable $t) {
                    $emailDebugInfo['error'] = 'Email process error: ' . $t->getMessage();
                }
            }

            $response = ['Status' => $ok ? 1 : 0, 'Keterangan' => $ok ? 'OK' : 'Failed to update'];

            if (!empty($emailDebugInfo)) {
                $response['debug'] = $emailDebugInfo;
                if (isset($emailDebugInfo['failed_emails']) && count($emailDebugInfo['failed_emails']) > 0) {
                    $response['Keterangan'] .= ' - Email notification partial failed';
                    $response['email_warning'] = 'Beberapa email gagal terkirim. Cek log untuk detail.';
                }
            }

            return $response;
        } catch (Exception $e) {
            return ['Status' => 0, 'Keterangan' => $e->getMessage()];
        }
    }

    public function setRegistrationTCL($post)
    {
        $IDRegistration = isset($post['IDRegistration']) ? intval($post['IDRegistration']) : 0;
        $IDTCL = isset($post['IDTCL']) ? intval($post['IDTCL']) : 0;

        if ($IDRegistration <= 0 || $IDTCL <= 0) {
            return ['Status' => 0, 'Keterangan' => 'Invalid parameters'];
        }

        try {
            $stmt = $this->conn->prepare("SELECT r.IDTournament, r.IDTeam, t.StatusTournament FROM tournament_registration r JOIN tournament t ON r.IDTournament = t.IDTournament WHERE r.IDRegistration = ? LIMIT 1");
            $stmt->bind_param("i", $IDRegistration);
            $stmt->execute();
            $result = $stmt->get_result();
            $registration = $result->fetch_assoc();
            $stmt->close();

            if (!$registration) {
                return ['Status' => 0, 'Keterangan' => 'Registration not found'];
            }

            $allowedStatuses = ['draft', 'open_registration', 'closed_registration'];
            if (!in_array($registration['StatusTournament'], $allowedStatuses, true)) {
                return ['Status' => 0, 'Keterangan' => 'Kategori/Level hanya dapat diubah saat tournament status draft, open_registration, atau closed_registration'];
            }

            $stmt2 = $this->conn->prepare("SELECT IDTournament FROM tournament_category_level WHERE IDTCL = ? LIMIT 1");
            $stmt2->bind_param("i", $IDTCL);
            $stmt2->execute();
            $result2 = $stmt2->get_result();
            $tcl = $result2->fetch_assoc();
            $stmt2->close();

            if (!$tcl) {
                return ['Status' => 0, 'Keterangan' => 'Kategori / Level tidak valid'];
            }

            if (intval($tcl['IDTournament']) !== intval($registration['IDTournament'])) {
                return ['Status' => 0, 'Keterangan' => 'Kategori / Level tidak sesuai dengan tournament'];
            }

            return runWithTransaction($this->conn, function ($conn) use ($IDRegistration, $IDTCL, $registration) {
                $stmt3 = $conn->prepare("UPDATE tournament_registration SET IDTCL = ?, LastUpdateAt = NOW() WHERE IDRegistration = ?");
                $stmt3->bind_param("ii", $IDTCL, $IDRegistration);
                $stmt3->execute();
                $stmt3->close();

                if (!empty($registration['IDTeam'])) {
                    $stmt4 = $conn->prepare("UPDATE team SET IDTCL = ? WHERE IDTeam = ?");
                    $stmt4->bind_param("ii", $IDTCL, $registration['IDTeam']);
                    $stmt4->execute();
                    $stmt4->close();
                }

                return ['Status' => 1, 'Keterangan' => 'Kategori dan Level berhasil diassign'];
            });
        } catch (Exception $e) {
            return ['Status' => 0, 'Keterangan' => $e->getMessage()];
        }
    }

    // ========================
    // GROUP MANAGEMENT
    // ========================
    public function listGroups($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            if ($IDTournament <= 0)
                throw new Exception("IDTournament tidak valid");

            $sql = "SELECT g.*, t.NamaTournament FROM tournament_group g LEFT JOIN tournament t ON g.IDTournament = t.IDTournament WHERE g.IDTournament = ? ORDER BY g.IDGroup ASC";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();
            $res = $stmt->get_result();
            $list = [];
            while ($r = $res->fetch_assoc())
                $list[] = $r;

            return ["Status" => 1, "Keterangan" => "OK", "list_data" => $list];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage(), "list_data" => []];
        }
    }





    // ========================
    // MATCH GENERATION
    // ========================
    public function autoGenerateMatches($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDTournament <= 0)
                throw new Exception("IDTournament tidak valid");

            // access
            $AksesNeeded = $this->sv . ".AddMatch";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = ["filters" => $userAksesFilterParam, "rows_per_page" => 1, "page_number" => 1, "no_count" => true];
            $AksesResult = $this->userAksesController->select($userAksesselectParam);
            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");

            // check tournament status
            $stmt = $this->conn->prepare("SELECT StatusTournament, TournamentFormat FROM tournament WHERE IDTournament = ?");
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();
            $t = $stmt->get_result()->fetch_assoc();
            if ($t['StatusTournament'] !== 'ongoing')
                throw new Exception("Auto-generate matches hanya dapat dilakukan saat status = ongoing");
            $format = $t['TournamentFormat'];

            // get groups
            $gstmt = $this->conn->prepare("SELECT IDGroup, IDTCL FROM tournament_group WHERE IDTournament = ? ORDER BY IDGroup ASC");
            $gstmt->bind_param("i", $IDTournament);
            $gstmt->execute();
            $gres = $gstmt->get_result();
            $groups = [];
            while ($r = $gres->fetch_assoc())
                $groups[] = $r;
            if (empty($groups))
                throw new Exception("Belum ada group untuk tournament ini");

            $this->conn->begin_transaction();
            try {
                foreach ($groups as $g) {
                    $groupId = intval($g['IDGroup']);
                    $idTcl = intval($g['IDTCL']);

                    // get teams in group
                    $ts = $this->conn->prepare("SELECT IDTeam FROM tournament_group_team WHERE IDGroup = ? ORDER BY SeedNumber IS NULL, SeedNumber ASC");
                    $ts->bind_param("i", $groupId);
                    $ts->execute();
                    $tres = $ts->get_result();
                    $teamIds = [];
                    while ($tr = $tres->fetch_assoc())
                        $teamIds[] = intval($tr['IDTeam']);

                    $n = count($teamIds);
                    if ($n < 2)
                        continue;

                    if ($format === 'round_robin') {
                        // create all pair combinations
                        for ($i = 0; $i < $n; $i++) {
                            for ($j = $i + 1; $j < $n; $j++) {
                                $teamA = $teamIds[$i];
                                $teamB = $teamIds[$j];
                                $stmtMax = $this->conn->prepare("SELECT COALESCE(MAX(MatchNumber),0) as maxM FROM tournament_match WHERE IDGroup = ?");
                                $stmtMax->bind_param("i", $groupId);
                                $stmtMax->execute();
                                $maxR = $stmtMax->get_result()->fetch_assoc();
                                $matchNumber = intval($maxR['maxM']) + 1;

                                $ins = $this->conn->prepare("INSERT INTO tournament_match (IDTournament, IDTCL, IDGroup, RoundNumber, MatchNumber, TeamA, TeamB, Status, CreateAt) VALUES (?, ?, ?, 1, ?, ?, ?, 'pending', NOW())");
                                $ins->bind_param("iiiiii", $IDTournament, $idTcl, $groupId, $matchNumber, $teamA, $teamB);
                                $ins->execute();
                            }
                        }
                    } else {
                        // single elimination - pair sequentially
                        for ($i = 0; $i < $n; $i += 2) {
                            if (!isset($teamIds[$i + 1]))
                                break; // odd => skip last
                            $teamA = $teamIds[$i];
                            $teamB = $teamIds[$i + 1];
                            $stmtMax = $this->conn->prepare("SELECT COALESCE(MAX(MatchNumber),0) as maxM FROM tournament_match WHERE IDGroup = ?");
                            $stmtMax->bind_param("i", $groupId);
                            $stmtMax->execute();
                            $maxR = $stmtMax->get_result()->fetch_assoc();
                            $matchNumber = intval($maxR['maxM']) + 1;

                            $ins = $this->conn->prepare("INSERT INTO tournament_match (IDTournament, IDTCL, IDGroup, RoundNumber, MatchNumber, TeamA, TeamB, Status, CreateAt) VALUES (?, ?, ?, 1, ?, ?, ?, 'pending', NOW())");
                            $ins->bind_param("iiiiii", $IDTournament, $idTcl, $groupId, $matchNumber, $teamA, $teamB);
                            $ins->execute();
                        }
                    }
                }

                $this->conn->commit();
            } catch (Exception $e) {
                $this->conn->rollback();
                throw $e;
            }

            return ["Status" => 1, "Keterangan" => "Matches berhasil dibuat"];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }

    // ========================
    // GROUP - TEAM ASSIGNMENT
    // ========================
    public function assignTeamToGroup($post)
    {
        try {
            $IDGroup = isset($post['IDGroup']) ? intval($post['IDGroup']) : 0;
            $IDTeam = isset($post['IDTeam']) ? intval($post['IDTeam']) : 0;
            $SeedNumber = isset($post['SeedNumber']) ? intval($post['SeedNumber']) : null;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDGroup <= 0 || $IDTeam <= 0)
                throw new Exception("Parameter tidak valid");

            // check group exists
            $stmt = $this->conn->prepare("SELECT IDTournament FROM tournament_group WHERE IDGroup = ?");
            $stmt->bind_param("i", $IDGroup);
            $stmt->execute();
            $res = $stmt->get_result();
            if ($res->num_rows === 0)
                throw new Exception("Group tidak ditemukan");
            $g = $res->fetch_assoc();

            // check tournament status
            $stmt2 = $this->conn->prepare("SELECT StatusTournament FROM tournament WHERE IDTournament = ?");
            $stmt2->bind_param("i", $g['IDTournament']);
            $stmt2->execute();
            $r2 = $stmt2->get_result()->fetch_assoc();
            if ($r2['StatusTournament'] !== 'closed_registration')
                throw new Exception("Group-team assignment hanya dapat dilakukan saat status = closed_registration");

            // duplicate team in group
            $dup = $this->conn->prepare("SELECT 1 FROM tournament_group_team WHERE IDGroup = ? AND IDTeam = ? LIMIT 1");
            $dup->bind_param("ii", $IDGroup, $IDTeam);
            $dup->execute();
            if ($dup->get_result()->num_rows > 0)
                throw new Exception("Team sudah berada di group ini");

            $ins = $this->conn->prepare("INSERT INTO tournament_group_team (IDGroup, IDTeam, SeedNumber) VALUES (?, ?, ?)");
            $ins->bind_param("iii", $IDGroup, $IDTeam, $SeedNumber);
            $ins->execute();

            return ["Status" => 1, "Keterangan" => "Team berhasil ditambahkan ke group", "IDGroupTeam" => $ins->insert_id];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }

    public function autoAssignTeamsToGroups($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDTournament <= 0)
                throw new Exception("IDTournament tidak valid");

            // access & status
            $AksesNeeded = $this->sv . ".Modify";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = ["filters" => $userAksesFilterParam, "rows_per_page" => 1, "page_number" => 1, "no_count" => true];
            $AksesResult = $this->userAksesController->select($userAksesselectParam);
            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");
            $listAkses = array_column($AksesResult['list_data'], 'AksesName');
            if (!in_array($AksesNeeded, $listAkses))
                throw new Exception("User tidak memiliki otoritas akses - [" . $AksesNeeded . "]");

            $stmt = $this->conn->prepare("SELECT StatusTournament, TournamentFormat FROM tournament WHERE IDTournament = ?");
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();
            $t = $stmt->get_result()->fetch_assoc();
            if ($t['StatusTournament'] !== 'closed_registration')
                throw new Exception("Auto-assign hanya dapat dilakukan saat status = closed_registration");
            $format = $t['TournamentFormat'];

            // get groups
            $gstmt = $this->conn->prepare("SELECT IDGroup FROM tournament_group WHERE IDTournament = ? ORDER BY IDGroup ASC");
            $gstmt->bind_param("i", $IDTournament);
            $gstmt->execute();
            $gres = $gstmt->get_result();
            $groups = [];
            while ($r = $gres->fetch_assoc())
                $groups[] = intval($r['IDGroup']);
            if (empty($groups))
                throw new Exception("Belum ada group untuk tournament ini");

            // get teams
            $sqlTeams = "SELECT t.IDTeam FROM team t JOIN tournament_registration tr ON t.IDTeam = tr.IDTeam WHERE t.IDTournament = ? ORDER BY RAND()";
            $stmtTeams = $this->conn->prepare($sqlTeams);
            $stmtTeams->bind_param("i", $IDTournament);
            $stmtTeams->execute();
            $resTeams = $stmtTeams->get_result();
            $teams = [];
            while ($r = $resTeams->fetch_assoc())
                $teams[] = intval($r['IDTeam']);
            if (empty($teams))
                throw new Exception("Belum ada team terdaftar");

            // distribute teams round-robin into groups
            $this->conn->begin_transaction();
            try {
                // clear existing assignments for these groups
                $placeholders = implode(',', array_fill(0, count($groups), '?'));
                // delete per-group
                $delSql = "DELETE FROM tournament_group_team WHERE IDGroup IN (" . implode(',', $groups) . ")";
                $this->conn->query($delSql);

                $gcount = count($groups);
                foreach ($teams as $idx => $teamId) {
                    $gidx = $idx % $gcount;
                    $gid = $groups[$gidx];
                    $ins = $this->conn->prepare("INSERT INTO tournament_group_team (IDGroup, IDTeam, SeedNumber) VALUES (?, ?, NULL)");
                    $ins->bind_param("ii", $gid, $teamId);
                    $ins->execute();
                }

                $this->conn->commit();
            } catch (Exception $e) {
                $this->conn->rollback();
                throw $e;
            }

            return ["Status" => 1, "Keterangan" => "Teams berhasil di-assign ke groups"];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }

    // ========================
    // MATCH SETS
    // ========================
    public function addMatchSet($post)
    {
        try {
            $IDMatch = isset($post['IDMatch']) ? intval($post['IDMatch']) : 0;
            $SetNumber = isset($post['SetNumber']) ? intval($post['SetNumber']) : 0;
            $ScoreTeamA = isset($post['ScoreTeamA']) ? intval($post['ScoreTeamA']) : 0;
            $ScoreTeamB = isset($post['ScoreTeamB']) ? intval($post['ScoreTeamB']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDMatch <= 0 || $SetNumber <= 0)
                throw new Exception("Parameter tidak valid");

            // access
            $AksesNeeded = $this->sv . ".AddMatch";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = ["filters" => $userAksesFilterParam, "rows_per_page" => 1, "page_number" => 1, "no_count" => true];
            $AksesResult = $this->userAksesController->select($userAksesselectParam);
            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");

            // insert or update set
            $dup = $this->conn->prepare("SELECT IDSet FROM tournament_match_set WHERE IDMatch = ? AND SetNumber = ? LIMIT 1");
            $dup->bind_param("ii", $IDMatch, $SetNumber);
            $dup->execute();
            $dr = $dup->get_result();
            if ($dr->num_rows > 0) {
                $row = $dr->fetch_assoc();
                $stmt = $this->conn->prepare("UPDATE tournament_match_set SET ScoreTeamA = ?, ScoreTeamB = ? WHERE IDSet = ?");
                $stmt->bind_param("iii", $ScoreTeamA, $ScoreTeamB, $row['IDSet']);
                $stmt->execute();
            } else {
                $stmt = $this->conn->prepare("INSERT INTO tournament_match_set (IDMatch, SetNumber, ScoreTeamA, ScoreTeamB) VALUES (?, ?, ?, ?)");
                $stmt->bind_param("iiii", $IDMatch, $SetNumber, $ScoreTeamA, $ScoreTeamB);
                $stmt->execute();
            }

            return ["Status" => 1, "Keterangan" => "Set score disimpan"];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }

    // ========================
    // STANDINGS
    // ========================
    public function generateStandings($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            if ($IDTournament <= 0)
                throw new Exception("IDTournament tidak valid");

            // use the SQL logic provided by user to populate tournament_standing
            // delete existing standings
            $del = $this->conn->prepare("DELETE FROM tournament_standing WHERE IDTournament = ?");
            $del->bind_param("i", $IDTournament);
            $del->execute();

            // insert aggregate standings from finished matches
            // treat WinnerTeam = 0 as Draw
            $sql = "INSERT INTO tournament_standing (IDTournament, IDGroup, IDTeam, Played, Win, Lose, Draw, Points, ScoreFor, ScoreAgainst)
            SELECT m.IDTournament, m.IDGroup, tt.IDTeam,
                COUNT(m.IDMatch) AS Played,
                SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam = tt.IDTeam THEN 1 ELSE 0 END) AS Win,
                SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam != tt.IDTeam THEN 1 ELSE 0 END) AS Lose,
                SUM(CASE WHEN (m.WinnerTeam IS NULL OR m.WinnerTeam = 0) AND m.Status = 'finished' THEN 1 ELSE 0 END) AS Draw,
                SUM(CASE WHEN m.WinnerTeam != 0 AND m.WinnerTeam = tt.IDTeam THEN IFNULL(tr.PointWin,0) WHEN (m.WinnerTeam IS NULL OR m.WinnerTeam = 0) THEN IFNULL(tr.PointDraw,0) ELSE IFNULL(tr.PointLose,0) END) AS Points,
                SUM(CASE WHEN tt.IDTeam = m.TeamA THEN IFNULL(ms.ScoreTotalA,0) ELSE IFNULL(ms.ScoreTotalB,0) END) AS ScoreFor,
                SUM(CASE WHEN tt.IDTeam = m.TeamA THEN IFNULL(ms.ScoreTotalB,0) ELSE IFNULL(ms.ScoreTotalA,0) END) AS ScoreAgainst
            FROM tournament_match m
            JOIN (
                SELECT IDMatch, TeamA AS IDTeam FROM tournament_match
                UNION ALL
                SELECT IDMatch, TeamB AS IDTeam FROM tournament_match
            ) tt ON tt.IDMatch = m.IDMatch
            LEFT JOIN (
                SELECT IDMatch, SUM(ScoreTeamA) AS ScoreTotalA, SUM(ScoreTeamB) AS ScoreTotalB
                FROM tournament_match_set
                GROUP BY IDMatch
            ) ms ON ms.IDMatch = m.IDMatch
            JOIN tournament tr ON tr.IDTournament = m.IDTournament
            WHERE m.IDTournament = ? AND m.Status = 'finished'
            GROUP BY m.IDTournament, m.IDGroup, tt.IDTeam";

            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();

            return ["Status" => 1, "Keterangan" => "Standings berhasil di-generate"];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }




    /**
     * Ensure player ranking exists, insert with default if not
     */
    private function ensurePlayerRankingExists($IDPlayer)
    {
        $sql = "SELECT IDPlayerRanking FROM player_ranking WHERE IDPlayer = ?";
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) {
            error_log("Prepare failed (check exists): " . $this->conn->error);
            throw new Exception("Prepare failed: " . $this->conn->error);
        }

        $stmt->bind_param("i", $IDPlayer);
        $stmt->execute();
        $result = $stmt->get_result();
        $exists = $result->num_rows > 0;
        $stmt->close();

        if (!$exists) {
            error_log("Player $IDPlayer not found in player_ranking, inserting default...");

            // Get default category (Lower Bronze)
            $sqlCat = "SELECT IDRankCategory FROM ranking_category WHERE CategoryCode = 'LB' LIMIT 1";
            $catResult = $this->conn->query($sqlCat);
            if (!$catResult) {
                error_log("Failed to get default category: " . $this->conn->error);
                throw new Exception("Failed to get default category");
            }

            $catRow = $catResult->fetch_assoc();
            $defaultCategory = $catRow['IDRankCategory'] ?? null;

            if (!$defaultCategory) {
                error_log("Default category not found");
                throw new Exception("Default category not found");
            }

            // Insert default ranking
            $sqlInsert = "INSERT INTO player_ranking (
            IDPlayer, 
            IDRankCategory, 
            IsActive, 
            CreateAt, 
            LastUpdateAt
        ) VALUES (?, ?, 1, NOW(), NOW())";

            $stmtInsert = $this->conn->prepare($sqlInsert);
            if (!$stmtInsert) {
                error_log("Prepare failed (insert): " . $this->conn->error);
                throw new Exception("Prepare failed: " . $this->conn->error);
            }

            $stmtInsert->bind_param("ii", $IDPlayer, $defaultCategory);
            if (!$stmtInsert->execute()) {
                error_log("Execute failed (insert): " . $stmtInsert->error);
                throw new Exception("Execute failed: " . $stmtInsert->error);
            }


            $affectedRows = $stmtInsert->affected_rows;
            error_log("Inserted default ranking for player $IDPlayer, rows affected: $affectedRows");
            $stmtInsert->close();

            return true;
        }

        return false;
    }

    /**
     * Generate Excel report (summary, matches per round, leaderboard) per TCL
     * Saves file to assets/attachment/{IDTournament}/report.xlsx
     */
    public function generateReport($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDTournament <= 0)
                throw new Exception("IDTournament tidak valid");

            // Check access
            $AksesNeeded = $this->sv . ".GenerateReport";
            $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
            $userAksesselectParam = ["filters" => $userAksesFilterParam, "rows_per_page" => 1, "page_number" => 1, "no_count" => true];
            $userAksesController = new UserAksesController($this->conn);
            $AksesResult = $userAksesController->select($userAksesselectParam);
            if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data']))
                throw new Exception("Akses tidak ditemukan");
            $listAkses = array_column($AksesResult['list_data'], 'AksesName');
            if (!in_array($AksesNeeded, $listAkses))
                throw new Exception("User tidak memiliki otoritas akses - [" . $AksesNeeded . "]");
            $UserLogin = $AksesResult['Username'] ?? '';

            // Load tournament info
            $stmt = $this->conn->prepare("SELECT * FROM tournament WHERE IDTournament = ? LIMIT 1");
            $stmt->bind_param("i", $IDTournament);
            $stmt->execute();
            $res = $stmt->get_result();
            if ($res->num_rows === 0)
                throw new Exception("Tournament tidak ditemukan");
            $tour = $res->fetch_assoc();

            // Require finished status
            if (($tour['StatusTournament'] ?? '') !== 'finished') {
                throw new Exception("Laporan hanya dapat dihasilkan ketika tournament berstatus 'finished'");
            }

            // Load TCL list for this tournament (include category & level names)
            $stmtTcl = $this->conn->prepare(
                "SELECT tcl.*, k.NamaKategori, l.NamaLevel
                 FROM tournament_category_level tcl
                 LEFT JOIN kategori k ON tcl.IDKategori = k.IDKategori
                 LEFT JOIN `level` l ON tcl.IDLevel = l.IDLevel
                 WHERE tcl.IDTournament = ?
                 ORDER BY k.NamaKategori, l.NamaLevel"
            );
            $stmtTcl->bind_param("i", $IDTournament);
            $stmtTcl->execute();
            $tclRes = $stmtTcl->get_result();
            $tcls = [];
            while ($r = $tclRes->fetch_assoc())
                $tcls[] = $r;

            // Rely on project-level autoload and imports
            if (!class_exists(Spreadsheet::class))
                throw new Exception('PhpSpreadsheet not installed');

            $spreadsheet = new Spreadsheet();
            $sheetIndex = 0;

            // Function to make safe sheet title
            $makeTitle = function ($s) {
                $s = $s ?? '';
                $s = (string) $s;
                $s = str_replace(array('\\', '/', '?', '*', '[', ']', ':'), '-', $s);
                $s = trim($s);
                return mb_substr($s, 0, 31);
            };

            // Helper to set cell using column index + row
            $setCell = function ($sheet, $col, $row, $value) {
                $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                $sheet->setCellValue($colLetter . $row, $value);
            };

            // If no TCLs found, fallback to single sheet
            if (empty($tcls))
                $tcls = [['IDTCL' => 0, 'NamaKategori' => 'All', 'NamaLevel' => 'All']];

            // Define common styles
            $borderStyle = ['borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]];
            $headerFill = ['font' => ['bold' => true], 'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFCCFFFF']]];
            $sectionTitleStyle = ['font' => ['bold' => true]];
            $grayFill = ['fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFD3D3D3']]];

            // ==========================================
            // PROCESS EACH TCL
            // ==========================================
            foreach ($tcls as $tcl) {
                // Create sheet for this TCL
                if ($sheetIndex === 0)
                    $sheet = $spreadsheet->getActiveSheet();
                else
                    $sheet = $spreadsheet->createSheet();

                $title = $makeTitle(($tcl['NamaKategori'] ?? '') . ' - ' . ($tcl['NamaLevel'] ?? ''));
                $sheet->setTitle($title ?: 'TCL' . ($sheetIndex + 1));

                $row = 1;

                // === HEADER ===
                $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(16);
                $setCell($sheet, 1, $row, 'Tournament Report');
                $row++;

                $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(12);
                $setCell($sheet, 1, $row, 'Tournament: ' . ($tour['NamaTournament'] ?? ''));
                $row++;

                $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(12);
                $setCell($sheet, 1, $row, 'Category-Level: ' . ($tcl['NamaKategori'] ?? '') . ' - ' . ($tcl['NamaLevel'] ?? ''));
                $row++;
                $row++;

                // Load groups for this TCL
                $stmtG = $this->conn->prepare("SELECT IDGroup, GroupName FROM tournament_group WHERE IDTournament = ? AND IDTCL = ? ORDER BY GroupName");
                $stmtG->bind_param("ii", $IDTournament, $tcl['IDTCL']);
                $stmtG->execute();
                $gres = $stmtG->get_result();
                $groups = [];
                while ($g = $gres->fetch_assoc())
                    $groups[] = $g;

                if (empty($groups)) {
                    $setCell($sheet, 1, $row, 'No groups for this category level');
                    $row += 2;
                } else {
                    // ==========================================
                    // PROCESS EACH GROUP UNDER THIS TCL
                    // ==========================================
                    foreach ($groups as $g) {
                        $setCell($sheet, 1, $row, 'Group: ' . ($g['GroupName'] ?? ''));
                        $sheet->getStyle('A' . $row)->applyFromArray($sectionTitleStyle);
                        $row++;

                        // --- SUMMARY MATRIX ---
                        $teamSql = "SELECT t.IDTeam, t.TeamName FROM tournament_group_team tgt LEFT JOIN tournament_registration tr ON tgt.IDRegistration = tr.IDRegistration LEFT JOIN team t ON tr.IDTeam = t.IDTeam WHERE tgt.IDGroup = ? ORDER BY tgt.SeedNumber IS NULL, tgt.SeedNumber ASC";
                        $tstmt = $this->conn->prepare($teamSql);
                        $tstmt->bind_param("i", $g['IDGroup']);
                        $tstmt->execute();
                        $tres = $tstmt->get_result();
                        $teams = [];
                        while ($tr = $tres->fetch_assoc()) {
                            $teams[] = ['IDTeam' => intval($tr['IDTeam'] ?? 0), 'TeamName' => $tr['TeamName'] ?? 'Unknown'];
                        }

                        if (!empty($teams)) {
                            // Header row for summary matrix
                            $col = 1;
                            $setCell($sheet, $col++, $row, '#');
                            $setCell($sheet, $col++, $row, 'Team');
                            foreach ($teams as $t) {
                                $setCell($sheet, $col++, $row, $t['TeamName']);
                            }
                            $setCell($sheet, $col++, $row, 'Play');
                            $setCell($sheet, $col++, $row, 'Win');
                            $setCell($sheet, $col++, $row, 'Draw');
                            $setCell($sheet, $col++, $row, 'Lose');
                            $setCell($sheet, $col++, $row, 'Pts');
                            $headerRow = $row;
                            $row++;

                            // Load standings for quick lookup
                            $stmtStand = $this->conn->prepare("SELECT IDTeam, Played, Win, Lose, Draw, Points FROM tournament_standing WHERE IDTournament = ? AND IDGroup = ?");
                            $stmtStand->bind_param("ii", $IDTournament, $g['IDGroup']);
                            $stmtStand->execute();
                            $sr = $stmtStand->get_result();
                            $standingsMap = [];
                            while ($rS = $sr->fetch_assoc()) {
                                $standingsMap[intval($rS['IDTeam'])] = $rS;
                            }

                            // Fill matrix rows
                            foreach ($teams as $idx => $tA) {
                                $col = 1;
                                $setCell($sheet, $col++, $row, $idx + 1);
                                $setCell($sheet, $col++, $row, $tA['TeamName']);

                                // Cells for matchups against each team
                                foreach ($teams as $tB) {
                                    if ($tA['IDTeam'] === $tB['IDTeam']) {
                                        $setCell($sheet, $col++, $row, '');
                                        // Apply gray fill to diagonal cell
                                        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1);
                                        $sheet->getStyle($colLetter . $row)->applyFromArray($grayFill);
                                        continue;
                                    }

                                    // Find finished match between A and B
                                    $mstmt = $this->conn->prepare("SELECT WinnerTeam FROM tournament_match WHERE IDGroup = ? AND ((TeamA = ? AND TeamB = ?) OR (TeamA = ? AND TeamB = ?)) AND Status = 'finished' LIMIT 1");
                                    $mstmt->bind_param("iiiii", $g['IDGroup'], $tA['IDTeam'], $tB['IDTeam'], $tB['IDTeam'], $tA['IDTeam']);
                                    $mstmt->execute();
                                    $mres2 = $mstmt->get_result();
                                    $mark = '';
                                    if ($mres2 && $mres2->num_rows) {
                                        $mr = $mres2->fetch_assoc();
                                        $w = $mr['WinnerTeam'];
                                        if ($w === null)
                                            $mark = '';
                                        else if (intval($w) === 0)
                                            $mark = 'D';
                                        else if (intval($w) === intval($tA['IDTeam']))
                                            $mark = 'O';
                                        else
                                            $mark = 'X';
                                    }
                                    $setCell($sheet, $col++, $row, $mark);
                                }

                                // Append stats from standings
                                $st = $standingsMap[intval($tA['IDTeam'])] ?? null;
                                $setCell($sheet, $col++, $row, $st['Played'] ?? 0);
                                $setCell($sheet, $col++, $row, $st['Win'] ?? 0);
                                $setCell($sheet, $col++, $row, $st['Draw'] ?? 0);
                                $setCell($sheet, $col++, $row, $st['Lose'] ?? 0);
                                $setCell($sheet, $col++, $row, $st['Points'] ?? 0);
                                $row++;
                            }

                            // Apply styles to summary block
                            $lastCol = $col - 1;
                            $range = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $headerRow . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($lastCol) . ($row - 1);
                            $sheet->getStyle($range)->applyFromArray($borderStyle);
                            $sheet->getStyle(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $headerRow . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($lastCol) . $headerRow)->applyFromArray($headerFill);
                            $row += 2;  // Spacing after summary
                        }

                        // --- MATCH DETAILS PER ROUND ---
                        $stmtM = $this->conn->prepare("SELECT m.IDMatch, m.RoundNumber, m.MatchNumber, m.TeamA, m.TeamB, tA.TeamName AS TeamAName, tB.TeamName AS TeamBName, tW.TeamName AS WinnerTeamName, m.Status, m.WinnerTeam FROM tournament_match m LEFT JOIN team tA ON m.TeamA = tA.IDTeam LEFT JOIN team tB ON m.TeamB = tB.IDTeam LEFT JOIN team tW ON m.WinnerTeam = tW.IDTeam WHERE m.IDGroup = ? ORDER BY m.RoundNumber, m.MatchNumber");
                        $stmtM->bind_param("i", $g['IDGroup']);
                        $stmtM->execute();
                        $mres = $stmtM->get_result();
                        $matchesByRound = [];
                        while ($m = $mres->fetch_assoc()) {
                            $matchesByRound[intval($m['RoundNumber'])][] = $m;
                        }

                        foreach ($matchesByRound as $roundNum => $matches) {
                            // Section title for round
                            $setCell($sheet, 1, $row, 'Round ' . $roundNum);
                            $sheet->getStyle('A' . $row)->applyFromArray($sectionTitleStyle);
                            $row++;

                            // Table header for this round
                            $setCell($sheet, 1, $row, 'Round');
                            $setCell($sheet, 2, $row, 'Match #');
                            $setCell($sheet, 3, $row, 'Team A');
                            $setCell($sheet, 4, $row, 'Team B');
                            $setCell($sheet, 5, $row, 'Sets');
                            $setCell($sheet, 6, $row, 'Winner');
                            $setCell($sheet, 7, $row, 'Status');
                            $sheet->getStyle(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $row . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(7) . $row)->applyFromArray($headerFill);
                            $roundHeaderRow = $row;
                            $row++;

                            foreach ($matches as $m) {
                                // Load sets
                                $sstmt = $this->conn->prepare("SELECT SetNumber, ScoreTeamA, ScoreTeamB FROM tournament_match_set WHERE IDMatch = ? ORDER BY SetNumber");
                                $sstmt->bind_param("i", $m['IDMatch']);
                                $sstmt->execute();
                                $sres = $sstmt->get_result();
                                $setText = [];
                                while ($sr = $sres->fetch_assoc()) {
                                    $setText[] = ($sr['ScoreTeamA'] ?? 0) . '-' . ($sr['ScoreTeamB'] ?? 0);
                                }
                                $setsStr = implode('; ', $setText);

                                $winnerLabel = '';
                                if (isset($m['WinnerTeam']) && ($m['WinnerTeam'] === '0' || $m['WinnerTeam'] === 0)) {
                                    $winnerLabel = 'Draw';
                                } else if (!empty($m['WinnerTeam'])) {
                                    $winnerLabel = ($m['WinnerTeamName'] ?? '');
                                }

                                $setCell($sheet, 1, $row, $m['RoundNumber']);
                                $setCell($sheet, 2, $row, $m['MatchNumber']);
                                $setCell($sheet, 3, $row, $m['TeamAName'] ?? $m['TeamA']);
                                $setCell($sheet, 4, $row, $m['TeamBName'] ?? $m['TeamB']);
                                $setCell($sheet, 5, $row, $setsStr);
                                $setCell($sheet, 6, $row, $winnerLabel);
                                $setCell($sheet, 7, $row, $m['Status']);
                                $row++;
                            }

                            // Apply borders to round table
                            $roundEndRow = $row - 1;
                            $sheet->getStyle(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $roundHeaderRow . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(7) . $roundEndRow)->applyFromArray($borderStyle);
                            $row += 2;  // Spacing after round
                        }

                        // --- LEADERBOARD ---
                        $setCell($sheet, 1, $row, 'Leaderboard');
                        $sheet->getStyle('A' . $row)->applyFromArray($sectionTitleStyle);
                        $row++;

                        $setCell($sheet, 1, $row, 'Rank');
                        $setCell($sheet, 2, $row, 'Team');
                        $setCell($sheet, 3, $row, 'Win');
                        $setCell($sheet, 4, $row, 'Lose');
                        $setCell($sheet, 5, $row, 'Draw');
                        $setCell($sheet, 6, $row, 'Points');
                        $setCell($sheet, 7, $row, 'ScoreFor');
                        $setCell($sheet, 8, $row, 'ScoreAgainst');
                        $sheet->getStyle(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $row . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(8) . $row)->applyFromArray($headerFill);
                        $lbHeaderRow = $row;
                        $row++;

                        // Load standings
                        $stmtS = $this->conn->prepare("SELECT IDTeam, Win, Lose, Draw, Points, ScoreFor, ScoreAgainst FROM tournament_standing WHERE IDTournament = ? AND IDGroup = ? ORDER BY Points DESC, Win DESC");
                        $stmtS->bind_param("ii", $IDTournament, $g['IDGroup']);
                        $stmtS->execute();
                        $sres2 = $stmtS->get_result();
                        $rank = 1;
                        $lbStartRow = $row;

                        while ($sr = $sres2->fetch_assoc()) {
                            $teamId = intval($sr['IDTeam'] ?? 0);
                            $tname = '';
                            if ($teamId > 0) {
                                $tstmt = $this->conn->prepare("SELECT TeamName FROM team WHERE IDTeam = ? LIMIT 1");
                                $tstmt->bind_param("i", $teamId);
                                $tstmt->execute();
                                $tres2 = $tstmt->get_result();
                                if ($tres2 && $tres2->num_rows)
                                    $tname = $tres2->fetch_assoc()['TeamName'];
                            }

                            $setCell($sheet, 1, $row, $rank);
                            $setCell($sheet, 2, $row, $tname ?: $teamId);
                            $setCell($sheet, 3, $row, $sr['Win'] ?? 0);
                            $setCell($sheet, 4, $row, $sr['Lose'] ?? 0);
                            $setCell($sheet, 5, $row, $sr['Draw'] ?? 0);
                            $setCell($sheet, 6, $row, $sr['Points'] ?? 0);
                            $setCell($sheet, 7, $row, $sr['ScoreFor'] ?? 0);
                            $setCell($sheet, 8, $row, $sr['ScoreAgainst'] ?? 0);
                            $row++;
                            $rank++;
                        }

                        // Apply styles to leaderboard
                        if ($row > $lbStartRow) {
                            $lbEndRow = $row - 1;
                            $sheet->getStyle(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $lbHeaderRow . ':' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(8) . $lbEndRow)->applyFromArray($borderStyle);
                        }
                        $row += 2;  // Spacing after group
                    }
                }

                $sheetIndex++;
            }

            // Autosize columns for all sheets
            foreach ($spreadsheet->getAllSheets() as $s) {
                foreach (range('A', 'H') as $col) {
                    $s->getColumnDimension($col)->setAutoSize(true);
                }
            }

            // Ensure directory exists and save file
            $relDir = 'attachments/tournament/' . $IDTournament;
            $fsDir = PROJECT_ROOT . '/' . $relDir;

            if (!is_dir($fsDir)) {
                mkdir($fsDir, 0755, true);
            }

            // Generate dynamic filename with microtime
            $microtime = str_replace('.', '', microtime(true));
            $reportFileName = $IDTournament . '_report_' . $microtime . '.xlsx';
            $filePath = $fsDir . '/' . $reportFileName;

            // Save new file first
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);


            // =========================================
// AMBIL FILE LAMA SEBELUM UPDATE
// =========================================
            $oldFileName = null;

            $stmtSelect = $this->conn->prepare("
    SELECT ReportGeneratedName
    FROM tournament
    WHERE IDTournament = ?
    LIMIT 1
");
            $stmtSelect->bind_param("i", $IDTournament);
            $stmtSelect->execute();
            $res = $stmtSelect->get_result()->fetch_assoc();

            if ($res && !empty($res['ReportGeneratedName'])) {
                $oldFileName = basename($res['ReportGeneratedName']);
            }
            $stmtSelect->close();


            // =========================================
            // HAPUS FILE LAMA JIKA ADA
            // =========================================
            if (!empty($oldFileName)) {

                $oldFilePath = $fsDir . '/' . $oldFileName;

                // extra safety: pastikan file ada & memang file
                if (file_exists($oldFilePath) && is_file($oldFilePath)) {
                    unlink($oldFilePath);
                }
            }


            // =========================================
            // UPDATE METADATA KE DATABASE
            // =========================================
            try {

                $sqlUpdateReport = "
                    UPDATE tournament 
                    SET 
                        ReportGeneratedName = ?, 
                        ReportGeneratedBy = ?, 
                        ReportGeneratedAt = NOW() 
                    WHERE IDTournament = ?
                ";

                $stmtUpdateReport = $this->conn->prepare($sqlUpdateReport);

                if (!$stmtUpdateReport) {
                    throw new Exception("Prepare update report gagal.");
                }

                $stmtUpdateReport->bind_param("ssi", $reportFileName, $UserLogin, $IDTournament);
                $stmtUpdateReport->execute();
                $stmtUpdateReport->close();

            } catch (Exception $e) {
                error_log('Update report metadata failed: ' . $e->getMessage());
                throw $e;
            }

            return [
                "Status" => 1,
                "Keterangan" => "Report generated",
                "FilePath" => $relDir . '/' . $reportFileName,
                "ReportFileName" => $reportFileName
            ];
        } catch (Exception $e) {
            return ["Status" => 0, "Keterangan" => $e->getMessage()];
        }
    }


    public function select_miniDashboard()
    {
        try {

            // ==============================
            // 1️⃣ SUMMARY TOURNAMENT
            // ==============================
            $sqlSummary = "
            SELECT
                COUNT(CASE WHEN StatusTournament = 'draft' THEN 1 END) AS total_draft,
                COUNT(CASE WHEN StatusTournament = 'open_registration' THEN 1 END) AS total_open_registration,
                COUNT(CASE WHEN StatusTournament = 'ongoing' THEN 1 END) AS total_ongoing,
                COUNT(CASE WHEN StatusTournament = 'finished' THEN 1 END) AS total_finished
            FROM tournament
        ";

            $stmtSummary = $this->conn->prepare($sqlSummary);
            $stmtSummary->execute();
            $summary = $stmtSummary->get_result()->fetch_assoc();
            $stmtSummary->close();


            // ==============================
            // 2️⃣ SUMMARY MATCH PENDING
            // ==============================
            $sqlMatchPending = "
            SELECT COUNT(*) AS total_match_pending
            FROM tournament_match
            WHERE Status = 'pending'
        ";

            $stmtMatchPending = $this->conn->prepare($sqlMatchPending);
            $stmtMatchPending->execute();
            $matchSummary = $stmtMatchPending->get_result()->fetch_assoc();
            $stmtMatchPending->close();


            // ==============================
            // 3️⃣ LIST TOURNAMENT (ACTIVE FLOW)
            // ==============================
            $sqlTournamentList = "
                SELECT
                    T.IDTournament,
                    T.NamaTournament,
                    T.StatusTournament,
                    T.TournamentStart,
                    T.TournamentEnd,

                    GROUP_CONCAT(
                        DISTINCT CONCAT(K.NamaKategori, ' - ', L.NamaLevel)
                        ORDER BY K.NamaKategori, L.NamaLevel
                        SEPARATOR ', '
                    ) AS TCLName

                FROM tournament T

                LEFT JOIN tournament_category_level TCL 
                    ON TCL.IDTournament = T.IDTournament
                LEFT JOIN kategori K 
                    ON K.IDKategori = TCL.IDKategori
                LEFT JOIN level L 
                    ON L.IDLevel = TCL.IDLevel

                WHERE T.StatusTournament IN 
                    ('draft','open_registration','closed_registration','ongoing')

                GROUP BY 
                    T.IDTournament,
                    T.NamaTournament,
                    T.StatusTournament,
                    T.TournamentStart,
                    T.TournamentEnd

                ORDER BY T.TournamentStart DESC
            ";

            $stmtTournament = $this->conn->prepare($sqlTournamentList);
            $stmtTournament->execute();
            $resultTournament = $stmtTournament->get_result();

            $list_tournament = [];
            while ($row = $resultTournament->fetch_assoc()) {
                $list_tournament[] = $row;
            }
            $stmtTournament->close();


            // ==============================
            // 4️⃣ LIST MATCH PENDING DETAIL
            // ==============================
            $sqlMatchList = "
            SELECT
                TM.IDMatch,
                TM.MatchDate,
                TM.RoundNumber,
                TM.MatchNumber,
                TM.Status,

                T.IDTournament,
                T.NamaTournament,

                K.NamaKategori,
                L.NamaLevel

            FROM tournament_match TM
            INNER JOIN tournament T 
                ON T.IDTournament = TM.IDTournament

            LEFT JOIN tournament_category_level TCL 
                ON TCL.IDTCL = TM.IDTCL
            LEFT JOIN kategori K 
                ON K.IDKategori = TCL.IDKategori
            LEFT JOIN level L 
                ON L.IDLevel = TCL.IDLevel

            WHERE TM.Status = 'pending'
            ORDER BY TM.MatchDate ASC
        ";

            $stmtMatch = $this->conn->prepare($sqlMatchList);
            $stmtMatch->execute();
            $resultMatch = $stmtMatch->get_result();

            $list_match_pending = [];
            while ($row = $resultMatch->fetch_assoc()) {
                $list_match_pending[] = $row;
            }
            $stmtMatch->close();


            return [
                "Status" => 1,
                "Keterangan" => "",
                "list_data" => [
                    "summary" => [
                        "total_draft" => (int) $summary['total_draft'],
                        "total_open_registration" => (int) $summary['total_open_registration'],
                        "total_ongoing" => (int) $summary['total_ongoing'],
                        "total_finished" => (int) $summary['total_finished'],
                        "total_match_pending" => (int) $matchSummary['total_match_pending']
                    ],
                    "tournament_list" => $list_tournament,
                    "match_pending_list" => $list_match_pending
                ]
            ];

        } catch (Exception $e) {

            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage(),
                "list_data" => []
            ];
        }
    }


    /*========================== 2027-07-01 ========================== */

    // ========================
// RANKING MANAGEMENT
// ========================

    /**
     * Generate ranking dari tournament yang sudah selesai
     * Dipanggil saat tournament status berubah ke 'finished'
     */
    public function generateTournamentRanking($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak valid");
            }

            // Cek apakah ada point setup
            $pointSetup = $this->getPointSetup($IDTournament);
            if (!$pointSetup) {
                // Jika tidak ada setup, gunakan default
                $pointSetup = $this->getDefaultPointSetup();
            }



            return runWithTransaction($this->conn, function ($conn) use ($IDTournament, $pointSetup) {

                // 1. Ambil semua standing dari tournament ini
                $standings = $this->getTournamentStandings($IDTournament);

                if (empty($standings)) {
                    throw new Exception("Tidak ada standing untuk tournament ini");
                }

                // 2. Group standings per group untuk menentukan posisi
                $groupedStandings = $this->groupStandingsByGroup($standings);

                // 3. Hitung poin per team berdasarkan posisi di group
                $teamPoints = [];
                foreach ($groupedStandings as $groupId => $groupStandings) {
                    // Urutkan berdasarkan points tertinggi
                    usort($groupStandings, function ($a, $b) {
                        return $b['Points'] - $a['Points'];
                    });

                    $position = 1;
                    foreach ($groupStandings as $standing) {
                        $pointsEarned = $this->calculatePositionPoints($position, $pointSetup);
                        $teamPoints[$standing['IDTeam']] = [
                            'points' => $pointsEarned,
                            'position' => $position,
                            'isWinner' => ($position == 1) ? 1 : 0,
                            'isRunnerUp' => ($position == 2) ? 1 : 0
                        ];
                        $position++;
                    }
                }

                // 4. Update ranking untuk setiap player dalam team
                foreach ($teamPoints as $IDTeam => $pointData) {
                    $players = $this->getTeamPlayers($IDTeam);
                    foreach ($players as $player) {
                        //   var_dump("Updating ranking for player ID: " . $player['IDPlayer'] . " from team ID: " . $IDTeam . " with points: " . $pointData['points']);

                        $this->updatePlayerRankingFromTournament(
                            $player['IDPlayer'],
                            $IDTournament,
                            $pointData
                        );
                    }
                }

                // 5. Cek promosi otomatis untuk semua player yang terupdate
                // $this->checkAllPromotions();

                return [
                    "Status" => 1,
                    "Keterangan" => "Ranking berhasil di-generate"
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    /**
     * Update player ranking from tournament
     */
    private function updatePlayerRankingFromTournament($IDPlayer, $IDTournament, $pointData)
    {
        try {
            // Ensure player ranking exists
            $this->ensurePlayerRankingExists($IDPlayer);

            $pointsToAdd = $pointData['points'];
            $newTotalPoints = $pointsToAdd;

            $IsChampion = $pointData['isWinner'] == 1 ? 1 : 0;
            $IsRunnerUp = $pointData['isRunnerUp'] == 1 ? 1 : 0;


            // Insert history dengan total point BARU
            $sql = "INSERT INTO player_ranking_history (
            IDPlayer, 
            TotalPoints,  
            ChangeType, 
            ChangeReason, 
            IDTournament, 
            IsChampionship,
            IsRunnerUp,
            ChangeDate
        ) VALUES (?, ?, 'tournament_points', ?, ?, ?, ?, NOW())";

            $stmt = $this->conn->prepare($sql);
            if (!$stmt) {
                error_log("Prepare failed (history): " . $this->conn->error);
                throw new Exception("Prepare failed: " . $this->conn->error);
            }


            $reason = "Points from tournament (Position: {$pointData['position']}, Points: {$pointsToAdd})";

            $stmt->bind_param(
                "iisiii",
                $IDPlayer,
                $newTotalPoints,
                $reason,
                $IDTournament,
                $IsChampion,
                $IsRunnerUp
            );

            if (!$stmt->execute()) {
                error_log("Execute failed (history): " . $stmt->error);
                throw new Exception("Execute failed: " . $stmt->error);
            }

            $affectedRows = $stmt->affected_rows;
            error_log("History inserted for player $IDPlayer, rows affected: $affectedRows");
            $stmt->close();



        } catch (Exception $e) {
            error_log("ERROR in updatePlayerRankingFromTournament: " . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Rollback ranking dari tournament (saat status berubah dari finished)
     */
    public function rollbackTournamentRanking($post)
    {
        try {
            $IDTournament = isset($post['IDTournament']) ? intval($post['IDTournament']) : 0;
            if ($IDTournament <= 0) {
                throw new Exception("IDTournament tidak valid");
            }

            return runWithTransaction($this->conn, function ($conn) use ($IDTournament) {

                // 1. Ambil semua history ranking untuk tournament ini
                $histories = $this->getTournamentRankingHistory($IDTournament);

                if (empty($histories)) {
                    // Tidak ada history, skip
                    return [
                        "Status" => 1,
                        "Keterangan" => "Tidak ada ranking yang perlu di-rollback"
                    ];
                }

                // 2. Hapus semua history ranking untuk tournament ini
                //    (cukup hapus saja, karena TotalPoints di history adalah cumulative)
                //    Total point player akan otomatis kembali ke nilai sebelumnya
                //    karena data history yang dihapus adalah data terakhir (cumulative)
                $this->deleteTournamentRankingHistory($IDTournament);


                return [
                    "Status" => 1,
                    "Keterangan" => "Ranking berhasil di-rollback"
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    // ========================
    // HELPER FUNCTIONS
    // ========================

    /**
     * Get point setup untuk tournament
     */
    private function getPointSetup($IDTournament)
    {
        $sql = "SELECT * FROM tournament_point_setup WHERE IDTournament = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDTournament);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }

    /**
     * Get default point setup jika tidak ada
     */
    private function getDefaultPointSetup()
    {
        return [
            'PointChampion1' => 100,
            'PointChampion2' => 50,
            'PointChampion3' => 25,
            'PointChampion4' => 0,
            'PointParticipate' => 0,
            'PointMinusFirstRound' => 0,
            'PointMinusSecondRound' => 0,
            'PointMinusQuarterFinal' => 0
        ];
    }

    /**
     * Save or update tournament point setup row
     */
    private function saveTournamentPointSetup($conn, $IDTournament, $pointSetup, $username)
    {
        $existing = $this->getPointSetup($IDTournament);

        if ($existing) {
            $sql = "UPDATE tournament_point_setup SET
                PointChampion1 = ?,
                PointChampion2 = ?,
                PointChampion3 = ?,
                PointChampion4 = ?,
                PointMinusFirstRound = ?,
                PointMinusSecondRound = ?,
                PointMinusQuarterFinal = ?,
                PointParticipate = ?,
                LastUpdateBy = ?,
                LastUpdateAt = NOW()
                WHERE IDTournament = ?";

            $stmt = $conn->prepare($sql);
            if (!$stmt) {
                throw new Exception("Prepare statement gagal: " . $conn->error);
            }

            $stmt->bind_param(
                "iiiiiiiisi",
                $pointSetup['PointChampion1'],
                $pointSetup['PointChampion2'],
                $pointSetup['PointChampion3'],
                $pointSetup['PointChampion4'],
                $pointSetup['PointMinusFirstRound'],
                $pointSetup['PointMinusSecondRound'],
                $pointSetup['PointMinusQuarterFinal'],
                $pointSetup['PointParticipate'],
                $username,
                $IDTournament
            );

            $stmt->execute();
            if ($stmt->affected_rows < 0) {
                throw new Exception("Gagal update tournament point setup");
            }
            $stmt->close();
            return;
        }

        $sql = "INSERT INTO tournament_point_setup (
                IDTournament,
                PointChampion1,
                PointChampion2,
                PointChampion3,
                PointChampion4,
                PointMinusFirstRound,
                PointMinusSecondRound,
                PointMinusQuarterFinal,
                PointParticipate,
                CreateBy,
                CreateAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement gagal: " . $conn->error);
        }

        $stmt->bind_param(
            "iiiiiiiiss",
            $IDTournament,
            $pointSetup['PointChampion1'],
            $pointSetup['PointChampion2'],
            $pointSetup['PointChampion3'],
            $pointSetup['PointChampion4'],
            $pointSetup['PointMinusFirstRound'],
            $pointSetup['PointMinusSecondRound'],
            $pointSetup['PointMinusQuarterFinal'],
            $pointSetup['PointParticipate'],
            $username
        );

        $stmt->execute();
        if ($stmt->affected_rows <= 0) {
            throw new Exception("Gagal insert tournament point setup");
        }
        $stmt->close();
    }

    /**
     * Get tournament standings
     */
    private function getTournamentStandings($IDTournament)
    {
        $sql = "SELECT * FROM tournament_standing WHERE IDTournament = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDTournament);
        $stmt->execute();
        $result = $stmt->get_result();
        $standings = [];
        while ($row = $result->fetch_assoc()) {
            $standings[] = $row;
        }
        return $standings;
    }

    /**
     * Group standings by group
     */
    private function groupStandingsByGroup($standings)
    {
        $grouped = [];
        foreach ($standings as $standing) {
            $groupId = $standing['IDGroup'];
            if (!isset($grouped[$groupId])) {
                $grouped[$groupId] = [];
            }
            $grouped[$groupId][] = $standing;
        }
        return $grouped;
    }

    /**
     * Calculate points based on position
     */
    private function calculatePositionPoints($position, $pointSetup)
    {
        $points = $pointSetup['PointParticipate'] ?? 5;

        switch ($position) {
            case 1:
                $points += $pointSetup['PointChampion1'] ?? 100;
                break;
            case 2:
                $points += $pointSetup['PointChampion2'] ?? 50;
                break;
            case 3:
                $points += $pointSetup['PointChampion3'] ?? 25;
                break;
            case 4:
                $points += $pointSetup['PointChampion4'] ?? 10;
                break;
            default:
                // Untuk posisi 5+, hanya dapat poin partisipasi
                $points = $pointSetup['PointParticipate'] ?? 5;
                break;
        }

        return $points;
    }

    /**
     * Get players in a team
     */
    private function getTeamPlayers($IDTeam)
    {
        $sql = "SELECT tm.IDPlayer, p.full_name 
            FROM team_member tm 
            JOIN player p ON tm.IDPlayer = p.IDPlayer 
            WHERE tm.IDTeam = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDTeam);
        $stmt->execute();
        $result = $stmt->get_result();
        $players = [];
        while ($row = $result->fetch_assoc()) {
            $players[] = $row;
        }
        return $players;
    }



    /**
     * Get player ranking
     */
    private function getPlayerRanking($IDPlayer)
    {
        $sql = "SELECT * FROM player_ranking WHERE IDPlayer = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDPlayer);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }

    /**
     * Get player total points from history
     */
    private function getPlayerTotalPoints($IDPlayer)
    {
        $sql = "SELECT SUM(TotalPoints) as total_points 
            FROM player_ranking_history 
            WHERE IDPlayer = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDPlayer);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['total_points'] ?? 0;
    }


    /**
     * Get tournament ranking history
     */
    private function getTournamentRankingHistory($IDTournament)
    {
        $sql = "SELECT * FROM player_ranking_history WHERE IDTournament = ? AND ChangeType = 'tournament_points'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDTournament);
        $stmt->execute();
        $result = $stmt->get_result();
        $histories = [];
        while ($row = $result->fetch_assoc()) {
            $histories[] = $row;
        }
        $stmt->close();
        return $histories;
    }

    /**
     * Recalculate player points (exclude tournament)
     */
    private function recalculatePlayerPoints($IDPlayer, $IDTournament)
    {
        // Dapatkan total point terakhir SEBELUM tournament ini
        $sql = "SELECT SUM(TotalPoints) as total_points 
        FROM player_ranking_history 
        WHERE IDPlayer = ? 
        AND IDTournament != ? 
        AND ChangeType = 'tournament_points'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ii", $IDPlayer, $IDTournament);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['total_points'] ?? 0;
    }


    /**
     * Delete tournament ranking history
     */
    private function deleteTournamentRankingHistory($IDTournament)
    {
        $sql = "DELETE FROM player_ranking_history WHERE IDTournament = ? AND ChangeType = 'tournament_points'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDTournament);
        $stmt->execute();
        $stmt->close();
    }

    /**
     * Check all promotions
     */
    private function checkAllPromotions()
    {
        // Ambil semua player yang aktif
        $sql = "SELECT IDPlayer FROM player_ranking WHERE IsActive = 1";
        $result = $this->conn->query($sql);
        while ($row = $result->fetch_assoc()) {
            $this->checkAndPromotePlayer($row['IDPlayer']);
        }
    }

    /**
     * Check and promote player
     */
    public function checkAndPromotePlayer($IDPlayer)
    {
        $player = $this->getPlayerRanking($IDPlayer);
        if (!$player) {
            return;
        }

        $currentCategory = $this->getPlayerCategory($IDPlayer);
        $totalPoints = $player['TotalPoints'] ?? 0;
        $runnerUpCount = $player['RunnerUpCount'] ?? 0;
        $championshipCount = $player['ChampionshipCount'] ?? 0;

        // Ambil aturan promosi dari kategori saat ini
        $rules = $this->getPromotionRules($currentCategory);

        foreach ($rules as $rule) {
            $eligible = false;
            $reason = '';

            switch ($rule['RuleType']) {
                case 'points':
                    $eligible = ($totalPoints >= $rule['RuleValue']);
                    $reason = "Mencapai {$rule['RuleValue']} poin";
                    break;

                case 'runner_up_count':
                    $eligible = ($runnerUpCount >= $rule['RuleValue']);
                    $reason = "Runner up {$rule['RuleValue']} kali";
                    break;

                case 'champion':
                    $eligible = ($championshipCount >= $rule['RuleValue']);
                    $reason = "Juara {$rule['RuleValue']} kali";
                    break;

                case 'top_ten_ranking':
                    // Check top 10 di akhir tahun
                    $eligible = $this->isTopTenRanking($IDPlayer);
                    $reason = "Top 10 ranking akhir tahun";
                    break;
            }

            if ($eligible) {
                $this->promotePlayer($IDPlayer, $rule, $reason);
                break; // Hanya 1 promosi per pengecekan
            }
        }
    }

    /**
     * Get player category
     */
    private function getPlayerCategory($IDPlayer)
    {
        $sql = "SELECT rc.CategoryCode 
            FROM player_ranking pr 
            JOIN ranking_category rc ON pr.IDRankCategory = rc.IDRankCategory 
            WHERE pr.IDPlayer = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDPlayer);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        return $row['CategoryCode'] ?? 'LB';
    }

    /**
     * Get promotion rules
     */
    private function getPromotionRules($fromCategory)
    {
        $sql = "SELECT * FROM ranking_promotion_rule 
            WHERE FromCategoryCode = ? AND IsActive = 1 
            ORDER BY IDRule ASC";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $fromCategory);
        $stmt->execute();
        $result = $stmt->get_result();
        $rules = [];
        while ($row = $result->fetch_assoc()) {
            $rules[] = $row;
        }
        return $rules;
    }

    /**
     * Promote player
     */
    private function promotePlayer($IDPlayer, $rule, $reason)
    {
        $oldCategory = $this->getPlayerCategory($IDPlayer);
        $newCategory = $rule['ToCategoryCode'];

        // Get IDRankCategory for new category
        $sql = "SELECT IDRankCategory FROM ranking_category WHERE CategoryCode = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $newCategory);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $IDRankCategory = $row['IDRankCategory'];

        // Update player ranking
        $sql = "UPDATE player_ranking 
            SET IDRankCategory = ?,
                LastPromotionDate = NOW(),
                PromotedFromCategory = ?,
                PromotionReason = ?,
                LastUpdateAt = NOW()
            WHERE IDPlayer = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("issi", $IDRankCategory, $oldCategory, $reason, $IDPlayer);
        $stmt->execute();

        // Insert history
        $sql = "INSERT INTO player_ranking_history 
            (IDPlayer, IDRankCategory, TotalPoints, ChangeType, ChangeReason, 
             OldCategory, NewCategory, ChangeDate)
            VALUES (?, ?, (SELECT TotalPoints FROM player_ranking WHERE IDPlayer = ?), 
                    'auto_promotion', ?, ?, ?, NOW())";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("iiisss", $IDPlayer, $IDRankCategory, $IDPlayer, $reason, $oldCategory, $newCategory);
        $stmt->execute();
    }

    /**
     * Check if player is top 10 ranking
     */
    private function isTopTenRanking($IDPlayer)
    {
        // Check if player is in top 10 at end of year
        $sql = "SELECT COUNT(*) as rank_position 
            FROM (
                SELECT IDPlayer, TotalPoints,
                       RANK() OVER (ORDER BY TotalPoints DESC) as rank_num
                FROM player_ranking
                WHERE IsActive = 1
            ) ranked
            WHERE IDPlayer = ? AND rank_num <= 10";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $IDPlayer);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        return ($row['rank_position'] > 0);
    }

    /**
     * Manual upgrade to Gold
     */
    public function manualUpgradeToGold($post)
    {
        try {
            $IDPlayer = isset($post['IDPlayer']) ? intval($post['IDPlayer']) : 0;
            $IDUserLogin = $post['IDUserLogin'] ?? 0;

            if ($IDPlayer <= 0) {
                throw new Exception("IDPlayer tidak valid");
            }

            // Validasi admin access
            $this->validateAdminAccess($IDUserLogin);

            // Cek apakah player sudah Gold
            $currentCategory = $this->getPlayerCategory($IDPlayer);
            if ($currentCategory == 'GD') {
                throw new Exception("Player sudah berada di kategori Gold");
            }

            return runWithTransaction($this->conn, function ($conn) use ($IDPlayer, $currentCategory, $IDUserLogin) {

                // Get IDRankCategory for Gold
                $sql = "SELECT IDRankCategory FROM ranking_category WHERE CategoryCode = 'GD'";
                $result = $conn->query($sql);
                $row = $result->fetch_assoc();
                $IDRankCategory = $row['IDRankCategory'];

                // Update player ranking
                $sql = "UPDATE player_ranking 
                    SET IDRankCategory = ?,
                        IsManualOverride = 1,
                        LastPromotionDate = NOW(),
                        PromotedFromCategory = ?,
                        PromotionReason = 'Manual upgrade to Gold by admin',
                        LastUpdateAt = NOW()
                    WHERE IDPlayer = ?";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("isi", $IDRankCategory, $currentCategory, $IDPlayer);
                $stmt->execute();

                // Insert history
                $sql = "INSERT INTO player_ranking_history 
                    (IDPlayer, IDRankCategory, TotalPoints, ChangeType, ChangeReason, 
                     OldCategory, NewCategory, ChangeDate)
                    VALUES (?, ?, (SELECT TotalPoints FROM player_ranking WHERE IDPlayer = ?), 
                            'manual_upgrade', 'Manual upgrade to Gold by admin (User ID: ?)', 
                            ?, 'GD', NOW())";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("iiisi", $IDPlayer, $IDRankCategory, $IDPlayer, $IDUserLogin, $currentCategory);
                $stmt->execute();

                return [
                    "Status" => 1,
                    "Keterangan" => "Player berhasil di-upgrade ke Gold"
                ];
            });

        } catch (Exception $e) {
            return [
                "Status" => 0,
                "Keterangan" => $e->getMessage()
            ];
        }
    }

    /**
     * Validate admin access
     */
    private function validateAdminAccess($IDUserLogin)
    {
        // Implementasi validasi admin
        // Sesuai dengan sistem akses yang sudah ada
        $AksesNeeded = "Ranking.Modify";
        $userAksesFilterParam = [["IDUser", $IDUserLogin, 1]];
        $userAksesselectParam = [
            "filters" => $userAksesFilterParam,
            "rows_per_page" => 1,
            "page_number" => 1,
            "no_count" => true
        ];

        $AksesResult = $this->userAksesController->select($userAksesselectParam);
        if ($AksesResult['Status'] != 1 || empty($AksesResult['list_data'])) {
            throw new Exception("Akses tidak ditemukan");
        }

        $listAkses = array_column($AksesResult['list_data'], 'AksesName');
        if (!in_array($AksesNeeded, $listAkses)) {
            throw new Exception("User tidak memiliki otoritas akses - [$AksesNeeded]");
        }
    }


}
?>