<?php
$host = 'localhost';
$dbname = 'kijani_quiz';
$username = 'root';
$password = '';

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch categories
$categories_query = "SELECT category_id, category_name FROM categories";
$categories_result = $conn->query($categories_query);
$categories = [];
while ($row = $categories_result->fetch_assoc()) {
    $categories[] = $row;
}

// Fetch topics
$topics_query = "SELECT topic_id, topic_name, category_id FROM topics";
$topics_result = $conn->query($topics_query);
$topics = [];
while ($row = $topics_result->fetch_assoc()) {
    $topics[] = $row;
}

// Fetch results
$results_query = "
SELECT 
    u.email, 
    c.category_name, 
    t.topic_name, 
    q.question_text, 
    o.option_text AS user_answer,
    o2.option_text AS correct_answer,
    (CASE WHEN o.option_id = o2.option_id THEN 1 ELSE 0 END) AS is_correct
FROM 
    responses r
JOIN 
    users u ON r.user_id = u.user_id
JOIN 
    questions q ON r.question_id = q.question_id
JOIN 
    options o ON r.option_id = o.option_id
JOIN 
    options o2 ON q.question_id = o2.question_id AND o2.is_correct = 1
JOIN 
    topics t ON q.topic_id = t.topic_id
JOIN 
    categories c ON t.category_id = c.category_id";
$results_result = $conn->query($results_query);
$results = [];
while ($row = $results_result->fetch_assoc()) {
    $results[] = $row;
}

$conn->close();
?>


<!-- <script>
    var totalQuizzes = <?php echo json_encode($totalQuizzes); ?>;
    var departmentsData = <?php echo json_encode($departmentsData); ?>;
    var departmentsTopicsData = <?php echo json_encode($departmentsTopicsData); ?>;
</script> -->

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="Responsive Admin &amp; Dashboard Template based on Bootstrap 5">
    <meta name="author" content="AdminKit">
    <meta name="keywords" content="adminkit, bootstrap, bootstrap 5, admin, dashboard, template, responsive, css, sass, html, theme, front-end, ui kit, web">

    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link rel="shortcut icon" href="img/icons/icon-48x48.png" />

    <link rel="canonical" href="https://demo-basic.adminkit.io/" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    

    <link href="css/app.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;600&display=swap" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <meta charset="UTF-8">
    <title>Survey Results</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.min.css">
    <!-- <style>
        .chart-container {
            max-width: 600px;
            margin: auto;
            margin-bottom: 50px;
            height: 400px;
        }
    </style> -->
</head>

<body>
    <div class="wrapper">
        <nav id="sidebar" class="sidebar js-sidebar">
            <div class="sidebar-content js-simplebar">
                <a class="sidebar-brand" href="index.html">
                    <span class="align-middle">Kijani Admin</span>
                </a>
                <ul class="sidebar-nav">
                    <li class="sidebar-header">Pages</li>
                    <li class="sidebar-item active">
                        <a class="sidebar-link" href="index.html">
                            <i class="align-middle" data-feather="sliders"></i>
                            <span class="align-middle">Dashboard</span>
                        </a>
                    </li>
                    <li class="sidebar-item">
                        <a class="sidebar-link" href="create_questions.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">create questions</span>
                        </a>
                    </li>
                </ul>
            </div>
        </nav>

        <div class="main">
            <nav class="navbar navbar-expand navbar-light navbar-bg">
                <a class="sidebar-toggle js-sidebar-toggle">
                    <i class="hamburger align-self-center"></i>
                </a>
                <div class="navbar-collapse collapse">
                    <ul class="navbar-nav navbar-align">
                        <li class="nav-item dropdown">
                            <a class="nav-icon dropdown-toggle" href="#" id="alertsDropdown" data-bs-toggle="dropdown">
                                <div class="position-relative">
                                    <i class="align-middle" data-feather="bell"></i>
                                    <span class="indicator">4</span>
                                </div>
                            </a>
                            <div class="dropdown-menu dropdown-menu-lg dropdown-menu-end py-0" aria-labelledby="alertsDropdown">
                                <div class="dropdown-menu-header">
                                    4 New Notifications
                                </div>
                                <div class="list-group">
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <i class="text-danger" data-feather="alert-circle"></i>
                                            </div>
                                            <div class="col-10">
                                                <div class="text-dark">Update completed</div>
                                                <div class="text-muted small mt-1">Restart server 12 to complete the update.</div>
                                                <div class="text-muted small mt-1">30m ago</div>
                                            </div>
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <i class="text-warning" data-feather="bell"></i>
                                            </div>
                                            
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <i class="text-primary" data-feather="home"></i>
                                            </div>
                                            
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <i class="text-success" data-feather="user-plus"></i>
                                            </div>
                                            
                                            
                                        </div>
                                    </a>
                                </div>
                               
                            </div>
                        </li>
                        <li class="nav-item dropdown">
                            <a class="nav-icon dropdown-toggle" href="#" id="messagesDropdown" data-bs-toggle="dropdown">
                                <div class="position-relative">
                                    <i class="align-middle" data-feather="message-square"></i>
                                </div>
                            </a>
                            
                        </li>
                        <li class="nav-item dropdown">
                            <a class="nav-link dropdown-toggle d-none d-sm-inline-block" href="#" data-bs-toggle="dropdown">
                                <img src="img/avatars/avatar.jpg" class="avatar img-fluid rounded me-1" alt="Charles Hall" /> <span class="text-dark">Charles Hall</span>
                            </a>
                            <div class="dropdown-menu dropdown-menu-end">
                                <a class="dropdown-item" href="pages-profile.html"><i class="align-middle me-1" data-feather="user"></i> Profile</a>
                                <a class="dropdown-item" href="#"><i class="align-middle me-1" data-feather="pie-chart"></i> Analytics</a>
                                <div class="dropdown-divider"></div>
                                <a class="dropdown-item" href="index.php"><i class="align-middle me-1" data-feather="settings"></i> Settings & Privacy</a>
                                <a class="dropdown-item" href="#"><i class="align-middle me-1" data-feather="help-circle"></i> Help Center</a>
                                <div class="dropdown-divider"></div>
                                <a class="dropdown-item" href="#">Log out</a>
                            </div>
                        </li>
                    </ul>
                </div>
            </nav>

            <main class="content">
                <div class="container-fluid p-0">

                    <h1 class="h3 mb-3">Dashboard</h1>
                    <div>

                    <form method="GET" id="filter-form" class="mb-4">
            <div class="form-group">
                <label for="category">Category</label>
                <select id="category" name="category" class="form-control">
                    <option value="">Select Category</option>
                    <?php foreach ($categories as $category): ?>
                        <option value="<?= $category['category_id']; ?>">
                            <?= htmlspecialchars($category['category_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group">
                <label for="topic">Topic</label>
                <select id="topic" name="topic" class="form-control">
                    <option value="">Select Topic</option>
                    <?php foreach ($topics as $topic): ?>
                        <option value="<?= $topic['topic_id']; ?>" data-category="<?= $topic['category_id']; ?>">
                            <?= htmlspecialchars($topic['topic_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <button type="submit" class="btn btn-primary">
    <i class="fas fa-filter"></i> Filter
</button>
        </form>

        <!-- Results Table -->
        <table class="table table-bordered">
            <thead>
                <tr>
                <th><i class="fas fa-envelope"  style="color:green"></i> Email</th>
            <th><i class="fas fa-tag" style="color:green"></i> Category</th>
            <th><i class="fas fa-bookmark"  style="color:green"></i> Topic</th>
            <th><i class="fas fa-question-circle"  style="color:green"></i> Question</th>
            <th><i class="fas fa-user"  style="color:green"></i>user answer</th>
            <th><i class="fas fa-check-double"  style="color:green"></i> Correct Answer</th>
            <th><i class="fas fa-check"></i> Correct</th>
                </tr>
            </thead>
            <tbody id="results-table">
                <?php foreach ($results as $result): ?>
                    <tr>
                        <td><?= htmlspecialchars($result['email']); ?></td>
                        <td><?= htmlspecialchars($result['category_name']); ?></td>
                        <td><?= htmlspecialchars($result['topic_name']); ?></td>
                        <td><?= htmlspecialchars($result['question_text']); ?></td>
                        <td><?= htmlspecialchars($result['user_answer']); ?></td>
                        <td><?= htmlspecialchars($result['correct_answer']); ?></td>
                        <td><?= $result['is_correct'] ? '<i class="fa fa-check text-success"></i>' : '<i class="fa fa-times text-danger"></i>'; ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <script>
        document.addEventListener('DOMContentLoaded', function () {
            const categorySelect = document.getElementById('category');
            const topicSelect = document.getElementById('topic');
            const filterForm = document.getElementById('filter-form');

            categorySelect.addEventListener('change', function () {
                const categoryId = this.value;
                for (const option of topicSelect.options) {
                    option.style.display = option.dataset.category == categoryId || categoryId == '' ? 'block' : 'none';
                }
            });

            filterForm.addEventListener('submit', function (event) {
                event.preventDefault();
                const categoryId = categorySelect.value;
                const topicId = topicSelect.value;
                const rows = document.querySelectorAll('#results-table tr');
                for (const row of rows) {
                    const rowCategory = row.querySelector('td:nth-child(2)').textContent;
                    const rowTopic = row.querySelector('td:nth-child(3)').textContent;
                    const show = (categoryId == '' || rowCategory == categorySelect.options[categorySelect.selectedIndex].text) &&
                                 (topicId == '' || rowTopic == topicSelect.options[topicSelect.selectedIndex].text);
                    row.style.display = show ? '' : 'none';
                }
            });
        });
    </script>

                </div>
            </main>

            <footer class="footer">
                <div class="container-fluid">
                    <div class="row text-muted">
                        <div class="col-6 text-start">
                            <p class="mb-0">
                                <a class="text-muted" href="https://adminkit.io/" target="_blank"><strong>AdminKit</strong></a> &copy;
                            </p>
                        </div>
                        <div class="col-6 text-end">
                            <ul class="list-inline">
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Support</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Help Center</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Privacy</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Terms</a>
                                </li>
                            </ul>
                        </div>
                    </div>
                </div>
            </footer>
        </div>
    </div>

    <script src="js/app.js"></script>
    
</body>

</html>
