<?php
header("Content-Type: application/json");
include 'db.php';

$category_id = isset($_GET['category_id']) ? intval($_GET['category_id']) : 0;

if ($category_id <= 0) {
    echo json_encode(["error" => "Invalid category ID"]);
    exit;
}

try {
    $query = "
    SELECT 
        u.id AS user_id,
        u.name AS user_name,
        u.image AS image,  -- Add this
        COALESCE(SUM(uq.score), 0) AS total_score
    FROM users u
    LEFT JOIN user_quizzes uq ON u.id = uq.user_id
    LEFT JOIN topics t ON uq.topic_id = t.id
    WHERE t.category_id = :category_id
    GROUP BY u.id, u.name, u.image
    ORDER BY total_score DESC, u.name ASC
";
    $stmt = $pdo->prepare($query);
    $stmt->execute(['category_id' => $category_id]);
    $leaderboard = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Add rank
    $ranked_leaderboard = [];
    $rank = 1;
    $prev_score = null;
    foreach ($leaderboard as $index => $entry) {
        if ($prev_score !== null && $entry['total_score'] < $prev_score) {
            $rank = $index + 1;
        }
        $entry['rank'] = $rank;
        $ranked_leaderboard[] = $entry;
        $prev_score = $entry['total_score'];
    }
    
    echo json_encode($ranked_leaderboard);
} catch (Exception $e) {
    echo json_encode(["error" => $e->getMessage()]);
}
?>