<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *'); // Allow CORS for Flutter app

// Database connection
include 'db.php';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo json_encode(['status' => 'error', 'message' => 'Database connection failed: ' . $e->getMessage()]);
    exit;
}

// Get user_id and category_id from GET parameters
$userId = isset($_GET['user_id']) ? (int)$_GET['user_id'] : null;
$categoryId = isset($_GET['category_id']) ? (int)$_GET['category_id'] : null;

if (!$userId || !$categoryId) {
    echo json_encode(['status' => 'error', 'message' => 'Missing user_id or category_id']);
    exit;
}

// Query to sum scores and rank users within the specified category, including image
$query = "
    SELECT 
        u.id AS user_id,
        u.name AS user_name,
        u.image AS image,
        SUM(uq.score) AS total_score,
        RANK() OVER (ORDER BY SUM(uq.score) DESC) AS ranking
    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
";

try {
    $stmt = $pdo->prepare($query);
    $stmt->execute(['category_id' => $categoryId]);
    $leaderboard = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Find the current user's rank
    $userRank = null;
    foreach ($leaderboard as $entry) {
        if ($entry['user_id'] == $userId) {
            $userRank = $entry;
            break;
        }
    }

    echo json_encode([
        'status' => 'success',
        'leaderboard' => $leaderboard,
        'user_rank' => $userRank
    ]);
} catch (PDOException $e) {
    echo json_encode(['status' => 'error', 'message' => 'Query failed: ' . $e->getMessage()]);
}
?>