<?php
header("Content-Type: application/json; charset=UTF-8");

require_once "config.php";

$data = [];

$sql = "
SELECT 
    c.id,
    c.title,
    c.image_link,
    CASE 
        WHEN LOWER(TRIM(c.title)) = 'free match' THEN 'ZabbarLudoActivity.kt'
        ELSE COALESCE(
            (
                SELECT f.activity_name
                FROM freefire f
                WHERE f.match_type = c.title
                  AND f.match_result = 'No'
                ORDER BY f.id DESC
                LIMIT 1
            ),
            ''
        )
    END AS activity_name,
    (
        SELECT COUNT(*)
        FROM freefire f
        WHERE f.match_type = c.title
          AND f.match_result = 'No'
    ) + (
        SELECT COUNT(*)
        FROM match_schedule ms
        WHERE ms.match_type = c.title
          AND ms.match_result = 'No'
          AND STR_TO_DATE(REPLACE(ms.start_time, 'T', ' '), '%Y-%m-%d %H:%i:%s') <= NOW()
    ) AS match_found
FROM category c
ORDER BY c.id DESC
";

$result = $conn->query($sql);

if (!$result) {
    echo json_encode([
        "error" => $conn->error
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

while ($row = $result->fetch_assoc()) {
    $data[] = [
        "id" => (int)$row["id"],
        "title" => $row["title"],
        "image_link" => $row["image_link"],
        "activity_name" => $row["activity_name"],
        "match_found" => (int)$row["match_found"]
    ];
}

echo json_encode($data, JSON_UNESCAPED_UNICODE);

$conn->close();
?>