<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
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;
}

$userId = isset($_GET['user_id']) ? (int)$_GET['user_id'] : null;
$categoryId = isset($_GET['category_id']) ? (int)$_GET['category_id'] : null;
$setId = isset($_GET['set_id']) ? (int)$_GET['set_id'] : null;

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

// Determine set_id if not provided
if ($setId) {
    $setQuery = "SELECT id AS set_id, name AS set_name FROM sets WHERE id = :set_id LIMIT 1";
    $stmt = $pdo->prepare($setQuery);
    $stmt->execute(['set_id' => $setId]);
    $set = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$set) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid set_id']);
        exit;
    }
} else {
    $setQuery = "
        SELECT DISTINCT s.id AS set_id, s.name AS set_name
        FROM sets s
        JOIN topics t ON s.id = t.set_id
        WHERE t.category_id = :category_id
        ORDER BY s.id ASC
        LIMIT 1
    ";
    $stmt = $pdo->prepare($setQuery);
    $stmt->execute(['category_id' => $categoryId]);
    $set = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$set) {
        echo json_encode(['status' => 'error', 'message' => 'No sets found for the specified category']);
        exit;
    }
}

$setId = $set['set_id'];
$setName = $set['set_name'];

// Query to sum scores and rank users, excluding those with no quizzes
$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
    INNER JOIN user_quizzes uq ON u.id = uq.user_id
    INNER JOIN topics t ON uq.topic_id = t.id
    WHERE t.category_id = :category_id AND t.set_id = :set_id
    GROUP BY u.id, u.name, u.image
    HAVING SUM(uq.score) > 0
    ORDER BY total_score DESC
";

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

    $userRank = null;
    foreach ($leaderboard as $entry) {
        if ($entry['user_id'] == $userId) {
            $userRank = $entry;
            break;
        }
    }

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