<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Scores and Responses</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-5">
        <h1 class="mb-4">User Scores by Category and Topic</h1>
        
        <!-- Category and Topic Selection -->
        <form id="filterForm" class="form-inline mb-4">
            <label for="category" class="mr-2">Category:</label>
            <select id="category" name="category" class="form-control mr-3" required>
                <option value="">Select a Category</option>
                <?php
                // Database connection
                include 'db.php'; 
                // Fetch categories
                $categoriesResult = $conn->query("SELECT id, name FROM categories");
                while ($category = $categoriesResult->fetch_assoc()) {
                    echo "<option value='{$category['id']}'>{$category['name']}</option>";
                }
                ?>
            </select>

            <label for="topic" class="mr-2">Topic:</label>
            <select id="topic" name="topic" class="form-control mr-3" required>
                <option value="">Select a Topic</option>
                <!-- Topics will be populated by JavaScript -->
            </select>

            <button type="button" class="btn btn-primary" onclick="fetchScores()">Show Scores</button>
        </form>

        <!-- User Scores Table -->
        <table class="table table-striped" id="userScoresTable">
            <thead>
                <tr>
                    <th>User</th>
                    <th>Score</th>
                </tr>
            </thead>
            <tbody>
                <!-- Scores will be populated by JavaScript -->
            </tbody>
        </table>

        <!-- User Responses Table -->
        <h2 class="mt-5" id="responsesTitle" style="display: none;">User Responses</h2>
        <table class="table table-bordered" id="userResponsesTable" style="display: none;">
            <thead>
                <tr>
                    <th>Question</th>
                    <th>Option</th>
                    <th>Selected</th>
                </tr>
            </thead>
            <tbody>
                <!-- User responses will be populated by JavaScript -->
            </tbody>
        </table>
    </div>

    <script>
        // Fetch topics based on selected category
        document.getElementById('category').addEventListener('change', async function() {
            const categoryId = this.value;
            const topicSelect = document.getElementById('topic');
            topicSelect.innerHTML = '<option value="">Select a Topic</option>';
            
            if (categoryId) {
                const response = await fetch(`?action=getTopics&category_id=${categoryId}`);
                const topics = await response.json();
                topics.forEach(topic => {
                    topicSelect.innerHTML += `<option value="${topic.id}">${topic.name}</option>`;
                });
            }
        });

        // Fetch and display user scores
        async function fetchScores() {
            const category = document.getElementById('category').value;
            const topic = document.getElementById('topic').value;

            const response = await fetch(`?action=getScores&category_id=${category}&topic_id=${topic}`);
            const scores = await response.json();
            const tableBody = document.getElementById('userScoresTable').querySelector('tbody');
            
            tableBody.innerHTML = scores.map(score => `
                <tr onclick="fetchUserResponses(${score.user_id}, ${topic})">
                    <td>${score.user_name}</td>
                    <td>${score.score}</td>
                </tr>
            `).join('');
        }

        // Fetch and display user responses
        async function fetchUserResponses(userId, topicId) {
            const response = await fetch(`?action=getUserResponses&user_id=${userId}&topic_id=${topicId}`);
            const responses = await response.json();
            const tableBody = document.getElementById('userResponsesTable').querySelector('tbody');
            
            document.getElementById('responsesTitle').style.display = 'block';
            document.getElementById('userResponsesTable').style.display = 'table';

            tableBody.innerHTML = responses.map(response => `
                <tr>
                    <td>${response.question_text}</td>
                    <td>${response.option_text}</td>
                    <td>${response.selected_option_id == response.option_id ? 'Yes' : 'No'}</td>
                </tr>
            `).join('');
        }
    </script>

    <?php
    // Handle AJAX requests for different actions
    if (isset($_GET['action'])) {
        switch ($_GET['action']) {
            case 'getTopics':
                $category_id = intval($_GET['category_id']);
                $result = $conn->query("SELECT id, name FROM topics WHERE category_id = $category_id");
                $topics = $result->fetch_all(MYSQLI_ASSOC);
                echo json_encode($topics);
                break;

            case 'getScores':
                $category_id = intval($_GET['category_id']);
                $topic_id = intval($_GET['topic_id']);
                $result = $conn->query("SELECT u.user_id, u.email as user_name, uq.score
                                        FROM users u
                                        JOIN user_quizzes uq ON u.user_id = uq.user_id
                                        WHERE u.category_id = $category_id AND uq.topic_id = $topic_id");
                $scores = $result->fetch_all(MYSQLI_ASSOC);
                echo json_encode($scores);
                break;

            case 'getUserResponses':
                $user_id = intval($_GET['user_id']);
                $topic_id = intval($_GET['topic_id']);
                $result = $conn->query("SELECT q.question_text, o.option_text, uqo.selected_option_id, o.option_id
                                        FROM user_quiz_options uqo
                                        JOIN questions q ON uqo.question_id = q.id
                                        JOIN options o ON uqo.option_id = o.id
                                        WHERE uqo.user_id = $user_id AND uqo.topic_id = $topic_id");
                $responses = $result->fetch_all(MYSQLI_ASSOC);
                echo json_encode($responses);
                break;
        }
        exit;
    }
    $conn->close();
    ?>
</body>
</html>
