<?php
// api/core/player.php

function getAssetPath(string $ids): string
{
    return ROOT_DOMAIN . 'api/assets/player/' . $ids . '/';
}
function getRanking(array $options = []): array
{
    $gender = $_GET['gender'] ?? null;
    $page = isset($options['page']) ? max(1, (int) $options['page']) : 1;
    $limit = isset($options['limit']) ? min(50, max(1, (int) $options['limit'])) : 10;
    $lang = $options['lang'] ?? 'id';
    $search = $options['search'] ?? null;

    $sortBy = $_GET['sortBy'] ?? 'rank';
    $sortOrder = strtolower($_GET['sortOrder'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';

    $idFromUrl = isset($options['idFromUrl']) && is_numeric($options['idFromUrl']) ? (int) $options['idFromUrl'] : null;

    $offset = ($page - 1) * $limit;

    $params = [];
    $where = " WHERE 1=1 ";

    // =========================
    // FILTER BY ID FROM URL
    // =========================
    if ($idFromUrl) {
        $where .= " AND p.IDPlayer = :idFromUrl ";
        $params['idFromUrl'] = $idFromUrl;
    }


    // =========================
    // FILTER GENDER
    // =========================
    if ($gender && in_array($gender, ['male', 'female'])) {
        $where .= " AND p.gender = :gender ";
        $params['gender'] = $gender;
    }

    // =========================
    // SEARCH
    // =========================
    if (!empty($search)) {
        $where .= " AND p.full_name LIKE :search ";
        $params['search'] = "%$search%";
    }

    // =========================
    // SORT
    // =========================
    $allowedSort = [
        'rank' => 'r.ranking_position',
        'points' => 'r.total_points',
        'name' => 'p.full_name'
    ];

    $orderColumn = $allowedSort[$sortBy] ?? 'r.ranking_position';

    // =========================
    // COUNT QUERY
    // =========================
    $countSql = "
        SELECT COUNT(*) AS total
        FROM v_player_ranking r
        JOIN player p ON p.IDPlayer = r.IDPlayer
        $where
    ";

    $countRow = fetchOne($countSql, $params);
    $total = (int) ($countRow['total'] ?? 0);
    $totalPages = (int) ceil($total / $limit);

    if ($total === 0) {
        return [
            'data' => [],
            'total' => 0,
            'page' => $page,
            'limit' => $limit,
            'totalPages' => 0
        ];
    }

    // =========================
    // MAIN QUERY
    // =========================
    $sql = "
        SELECT
            p.IDPlayer,
            p.full_name,
            p.photo,
            p.gender,
            p.birth_date,
            p.birth_place,
            p.nationality,
            p.province,
            r.total_points,
            r.ranking_position
        FROM v_player_ranking r
        JOIN player p ON p.IDPlayer = r.IDPlayer
        $where
        ORDER BY $orderColumn $sortOrder
        LIMIT $limit OFFSET $offset
    ";

    $rows = fetchAll($sql, $params);

    // =========================
    // LOAD HISTORY
    // =========================
    $playerIds = array_column($rows, 'IDPlayer');

    $historyGrouped = [];

    if (!empty($playerIds)) {
        $placeholders = implode(',', array_fill(0, count($playerIds), '?'));

        $historySql = "
            SELECT *
            FROM v_player_tournament_history
            WHERE IDPlayer IN ($placeholders)
            ORDER BY tournament_date DESC
        ";

        $historyRows = fetchAll($historySql, $playerIds);

        foreach ($historyRows as $h) {
            $historyGrouped[$h['IDPlayer']][] = [
                "id" => $h['history_id'],
                "tournamentName" => $h['tournament_name'],
                "level" => $h['level_name'],
                "category" => $h['category_name'],
                "date" => $h['tournament_date'],
                "stageReached" => "-",
                "played" => $h['Played'],
                "wins" => $h['Win'],
                "losses" => $h['Lose'],
                "draws" => $h['Draw'],
                "points" => (int) $h['ranking_points']
            ];
        }
    }

    // =========================
    // MAP RESPONSE
    // =========================
    $data = [];

    foreach ($rows as $r) {
        $assetBase = getAssetPath($r['IDPlayer']);


        $data[] = [
            "id" => (string) $r['IDPlayer'],
            "rank" => (int) $r['ranking_position'],
            "name" => explode(' ', $r['full_name'])[0],
            "fullName" => $r['full_name'],
            "nationality" => $r['nationality'],
            "province" => $r['province'],
            "points" => (int) $r['total_points'],
            "gender" => $r['gender'],
            "birthDate" => $r['birth_date'],
            "birthPlace" => $r['birth_place'],
            'photo' => $r['photo']
                ? $assetBase . $r['photo']
                : null,
            "tournamentHistory" => $historyGrouped[$r['IDPlayer']] ?? []
        ];
    }

    // =========================
    // FINAL RESPONSE
    // =========================

    if ($idFromUrl > 0) {


        return $data[0] ?? null;
    }

    return [
        'data' => $data,
        'total' => $total,
        'page' => $page,
        'limit' => $limit,
        'totalPages' => $totalPages
    ];
}

function getPlayerByEmail(string $email): ?array
{
    return fetchOne("
        SELECT
            p.IDPlayer,
            p.email,
            p.full_name,
            p.photo,
            p.phone,
            p.ktp,
            p.birth_place,
            p.birth_date,
            p.gender,
            p.address,
            p.password_hash,
            p.nationality,
            p.province,
            p.profile_complete,
            p.verification_status,
            p.verification_reject_reason,
            p.CreateBy,
            p.CreateAt,
            p.LastUpdateBy,
            p.LastUpdateAt 

        FROM player p 
        WHERE p.email = :email
        LIMIT 1
    ", [
        'email' => $email
    ]);
}


function getPlayerById(int $id): ?array
{
    return fetchOne("
        SELECT
            p.*,
            m.status AS membership_status,
            m.end_date AS membership_end_date
        FROM player p
        LEFT JOIN membership m ON m.IDPlayer = p.IDPlayer
        WHERE p.IDPlayer = :id
        LIMIT 1
    ", ['id' => $id]);
}
function buildPlayerResponseOld(array $player): array
{
    $assetBase = getAssetPath($player['IDPlayer']);

    return [
        'id' => 'user-' . $player['IDPlayer'],
        'email' => $player['email'],
        'fullName' => $player['full_name'],
        'photo' => $player['photo']
            ? $assetBase . $player['photo']
            : null,
        'gender' => $player['gender'],
        'phone' => $player['phone'],
        'ktp' => $player['ktp']
            ? $assetBase . $player['ktp']
            : null,
        'birthPlace' => $player['birth_place'],
        'birthDate' => $player['birth_date'],
        'address' => $player['address'],
        'nationality' => $player['nationality'],
        'province' => $player['province'],
        'profileComplete' => (bool) $player['profile_complete'],
        'verificationStatus' => $player['verification_status'],
        'verificationRejectReason' => $player['verification_reject_reason'],
        'membership' => [
            'status' => $player['membership_status'] ?? 'inactive',
            'endDate' => $player['membership_end_date'] ?? null
        ],
        /*'membership_payment' => [
            'status' => null, //'waiting', // approve dan rejeect
            'message' => '' // jika reject ada isi
        ],*/
        'membership_payment' => [
            'status' => 'waiting', //'waiting', // approve dan rejeect
            'message' => 'ahahahah' // jika reject ada isi
        ],
        'points' => 0,
        'ranking' => null,
        'tournamentHistory' => [],
        'createdAt' => gmdate('c', strtotime($player['CreateAt']))
    ];
}

function getPlayerTournamentHistory(int $playerId): array
{
    $rows = fetchAll("
        SELECT *
        FROM v_player_tournament_history
        WHERE IDPlayer = :id
        ORDER BY tournament_date DESC
    ", ['id' => $playerId]);

    $history = [];

    foreach ($rows as $r) {
        $history[] = [
            'id' => $r['history_id'],
            'tournamentId' => (string) $r['IDTournament'],
            'tournamentName' => $r['tournament_name'],
            'date' => gmdate('c', strtotime($r['tournament_date'])),
            'level' => $r['level_name'],
            'category' => $r['category_name'],
            'play' => (int) $r['Played'],
            'win' => (int) $r['Win'],
            'lose' => (int) $r['Lose'],
            'draw' => (int) $r['Draw'],
            'points' => (int) $r['ranking_points']
        ];
    }

    return $history;
}

function getPlayerRankingData(int $playerId): array
{
    $row = fetchOne("
        SELECT 
            r.total_points,
            r.ranking_position
        FROM v_player_ranking r
        WHERE r.IDPlayer = :id
    ", ['id' => $playerId]);

    if (!$row) {
        return [
            'points' => 0,
            'ranking' => null
        ];
    }

    return [
        'points' => (int) $row['total_points'],
        'ranking' => (int) $row['ranking_position']
    ];
}

function getPlayerRank(int $playerId): ?array
{
    $row = fetchOne("
        SELECT 
            PR.IDPlayerRanking,
            PR.IDRankCategory,
            PR.LastPromotionDate,
            PR.PromotedFromCategory,
            PR.PromotionReason,
            PR.IsManualOverride,
            PR.IsActive AS RankingIsActive,
            PR.CreateBy AS RankingCreateBy,
            PR.CreateAt AS RankingCreateAt,
            PR.LastUpdateBy AS RankingLastUpdateBy,
            PR.LastUpdateAt AS RankingLastUpdateAt,
            RC.IDRankCategory AS RankCategoryID,
            RC.CategoryName,
            RC.CategoryCode,
            RC.MinPoints,
            RC.MaxPoints,
            RC.Description AS CategoryDescription,
            RC.IsExclusive,
            RC.IsActive AS CategoryIsActive,
            RC.IsDefaultRank,
            RC.SortOrder
        FROM player_ranking PR
        LEFT JOIN ranking_category RC ON RC.IDRankCategory = PR.IDRankCategory
        WHERE PR.IDPlayer = :id
        AND PR.IsActive = 1
        LIMIT 1
    ", ['id' => $playerId]);

    if (!$row) {
        return null;
    }

    return [
        'id' => (int) $row['IDPlayerRanking'],
        'rankCategoryId' => (int) $row['IDRankCategory'],
        'categoryName' => $row['CategoryName'],
        'categoryCode' => $row['CategoryCode'],
        'minPoints' => $row['MinPoints'] !== null ? (int) $row['MinPoints'] : null,
        'maxPoints' => $row['MaxPoints'] !== null ? (int) $row['MaxPoints'] : null,
        'categoryDescription' => $row['CategoryDescription'],
        'isExclusive' => (bool) $row['IsExclusive'],
        'isDefaultRank' => (bool) $row['IsDefaultRank'],
        'sortOrder' => (int) $row['SortOrder'],
        'lastPromotionDate' => $row['LastPromotionDate'] ? gmdate('c', strtotime($row['LastPromotionDate'])) : null,
        'promotedFromCategory' => $row['PromotedFromCategory'],
        'promotionReason' => $row['PromotionReason'],
        'isManualOverride' => (bool) $row['IsManualOverride'],
        'isActive' => (bool) $row['RankingIsActive'],
        'createdAt' => $row['RankingCreateAt'] ? gmdate('c', strtotime($row['RankingCreateAt'])) : null,
        'createdBy' => $row['RankingCreateBy'],
        'lastUpdateAt' => $row['RankingLastUpdateAt'] ? gmdate('c', strtotime($row['RankingLastUpdateAt'])) : null,
        'lastUpdateBy' => $row['RankingLastUpdateBy']
    ];
}

function buildPlayerResponse(array $player, bool $getFromDB = false): array
{
    // =========================
    // REFRESH FROM DB (OPTIONAL)
    // =========================
    if ($getFromDB === true) {

        $playerId = (int) $player['IDPlayer'];

        // --- PLAYER (LATEST)
        $player = fetchOne("
            SELECT *
            FROM player
            WHERE IDPlayer = :id
            LIMIT 1
        ", ['id' => $playerId]);

        if (!$player) {
            throw new Exception('Player tidak ditemukan');
        }

        // --- MEMBERSHIP (LATEST)
        $membership = fetchOne("
            SELECT 
                status, 
                end_date,
                MembershipNumber,
                cancel_reason
            FROM membership
            WHERE IDPlayer = :id
            ORDER BY CreateAt DESC
            LIMIT 1
        ", ['id' => $playerId]);

        // --- MEMBERSHIP PAYMENT (LATEST)
        $membershipPayment = fetchOne("
            SELECT status, reject_reason
            FROM membership_payment
            WHERE IDPlayer = :id
            ORDER BY CreateAt DESC
            LIMIT 1
        ", ['id' => $playerId]);

        // --- CEK APAKAH MEMBERSHIP AKAN EXPIRED (30 HARI)
        $expiringCheck = fetchOne("
            SELECT 
                COUNT(*) as total
            FROM membership
            WHERE IDPlayer = :id
            AND status = 'active'
            AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
        ", ['id' => $playerId]);

        $isAboutToExpire = ($expiringCheck['total'] ?? 0) > 0 ? 1 : 0;

        $history = getPlayerTournamentHistory($player['IDPlayer']);
        $rankingData = getPlayerRankingData($player['IDPlayer']);
        $playerRank = getPlayerRank($player['IDPlayer']);

        // inject agar kompatibel dengan mapping bawah
        $player['membership_status'] = $membership['status'] ?? 'inactive';
        $player['membership_end_date'] = $membership['end_date'] ?? null;
        $player['membership_membership_number'] = $membership['MembershipNumber'] ?? null;
        $player['membership_cancel_reason'] = $membership['cancel_reason'] ?? null;

    } else {
        // fallback kalau dipanggil dari join sebelumnya
        $membershipPayment = null;
        $rankingData = getPlayerRankingData($player['IDPlayer']);
        $playerRank = getPlayerRank($player['IDPlayer']);
        $history = getPlayerTournamentHistory($player['IDPlayer']);
    }

    $assetBase = getAssetPath($player['IDPlayer']);

    return [
        'id' => 'user-' . $player['IDPlayer'],
        'email' => $player['email'],
        'fullName' => $player['full_name'],

        'photo' => !empty($player['photo'])
            ? $assetBase . $player['photo']
            : null,

        'gender' => $player['gender'],
        'phone' => $player['phone'],

        'ktp' => !empty($player['ktp'])
            ? $assetBase . $player['ktp']
            : null,

        'birthPlace' => $player['birth_place'],
        'birthDate' => $player['birth_date'],
        'address' => $player['address'],
        'nationality' => $player['nationality'],
        'province' => $player['province'],

        'profileComplete' => (bool) $player['profile_complete'],
        'verificationStatus' => $player['verification_status'],
        'verificationRejectReason' => $player['verification_reject_reason'],

        'membership' => [
            'status' => $player['membership_status'] ?? 'inactive',
            'endDate' => $player['membership_end_date'] ?? null,
            'membershipId' => $player['membership_membership_number'] ?? null,
            'cancelReason' => $player['membership_cancel_reason'] ?? null,
            'is_about_to_expire' => $player['membership_is_about_to_expire'] ?? 0
        ],

        'membership_payment' => [
            'status' => $membershipPayment['status'] ?? null,
            'message' => $membershipPayment['reject_reason'] ?? null
        ],

        'points' => $rankingData['points'],
        'playerRank' => $playerRank,
        'ranking' => $rankingData['ranking'],
        'tournamentHistory' => $history,
        'createdAt' => gmdate('c', strtotime($player['CreateAt']))
    ];
}
