<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *'); // Allow CORS for frontend access (adjust for production)
require_once 'db.php'; // Include database connection

try {
    $query = "
        SELECT c.name AS category_name, u.name AS user_name
        FROM categories c
        LEFT JOIN users u ON u.category_id = c.id
        LEFT JOIN user_quizzes uq ON u.id = uq.user_id
        WHERE uq.id IS NULL AND u.id IS NOT NULL
        ORDER BY c.name, u.name
    ";

    $stmt = $pdo->prepare($query);
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Organize results by category
    $response = [];
    foreach ($results as $row) {
        $category = $row['category_name'];
        if (!isset($response[$category])) {
            $response[$category] = [];
        }
        $response[$category][] = $row['user_name'];
    }

    // Include empty categories
    $allCategoriesQuery = "SELECT name FROM categories";
    $stmt = $pdo->query($allCategoriesQuery);
    $allCategories = $stmt->fetchAll(PDO::FETCH_COLUMN);
    foreach ($allCategories as $category) {
        if (!isset($response[$category])) {
            $response[$category] = [];
        }
    }

    echo json_encode($response, JSON_PRETTY_PRINT);

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>