<?php
include 'config.php';

// 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 filtered results
$category_filter = isset($_GET['category']) ? $_GET['category'] : '';
$topic_filter = isset($_GET['topic']) ? $_GET['topic'] : '';

$filter_sql = '';
if (!empty($category_filter)) {
    $filter_sql .= " AND t.category_id = " . intval($category_filter);
}
if (!empty($topic_filter)) {
    $filter_sql .= " AND t.topic_id = " . intval($topic_filter);
}

$results_query = "
SELECT 
    u.user_id,
    u.email, 
    c.category_name, 
    t.topic_id,
    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,
    COUNT(q.question_id) OVER (PARTITION BY u.user_id, t.topic_id) AS total_questions,
    SUM(CASE WHEN o.option_id = o2.option_id THEN 1 ELSE 0 END) OVER (PARTITION BY u.user_id, t.topic_id) AS correct_answers
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
WHERE 1=1 " . $filter_sql;

$results_result = $conn->query($results_query);
$results = [];
while ($row = $results_result->fetch_assoc()) {
    $results[] = $row;
}

$conn->close();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <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">
    <link href="css/app.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">

    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

    <style>
    .badge-circle {
        display: inline-block;
        width: 2rem;
        height: 2rem;
        border-radius: 50%;
        line-height: 2rem;
        text-align: center;
        padding: 0;
    }
</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.php">
                    <span class="align-middle">Kijani Admin</span>
                </a>
                <ul class="sidebar-nav">
                    <li class="sidebar-header">Pages</li>
                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="index.php">
                            <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>
                    <li class="sidebar-item active">
                        <a class="sidebar-link" href="view_results.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">view quiz results</span>
                        </a>
                    </li>
                    <li class="sidebar-item">
                        <a class="sidebar-link" href="edit_questions.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">Edit Quiz</span>
                        </a>
                    </li>
                    <li class="sidebar-item">
                        <a class="sidebar-link" href="users.php">
                            <i class="align-middle" data-feather="user"></i>
                            <span class="align-middle">Users</span>
                        </a>
                    </li>
                    <li class="sidebar-item  ">
                        <a class="sidebar-link" href="bulk_upload.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">Bulk upload</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>
                            <div class="dropdown-menu dropdown-menu-lg dropdown-menu-end py-0" aria-labelledby="alertsDropdown">
                                 
                                <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>
                                    </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">                    
                            <div class="dropdown-menu dropdown-menu-lg dropdown-menu-end py-0" aria-labelledby="messagesDropdown">
                                <div class="dropdown-menu-header">
                                     
                                </div>
                                <div class="list-group">
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <img src="img/avatars/avatar-5.jpg" class="avatar img-fluid rounded-circle" alt="Vanessa Tucker">
                                            </div>                                         
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <img src="img/avatars/avatar-2.jpg" class="avatar img-fluid rounded-circle" alt="William Harris">
                                            </div>            
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <img src="img/avatars/avatar-4.jpg" class="avatar img-fluid rounded-circle" alt="Christina Mason">
                                            </div>
                                             
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-center">
                                            <div class="col-2">
                                                <img src="img/avatars/avatar-3.jpg" class="avatar img-fluid rounded-circle" alt="Sharon Lessman">
                                            </div>
                                             
                                        </div>
                                    </a>
                                </div>
                                 
                            </div>
                        </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" /> 
                            </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">Survey Results</h1>

                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="card-title">Filter</h5>
                                </div>
                                <div class="card-body">
    <form method="GET" id="filter-form">
        <div class="form-row">
            <div class="form-group col-md-4">
                <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']; ?>" <?= $category_filter == $category['category_id'] ? 'selected' : ''; ?>>
                                                            <?= htmlspecialchars($category['category_name']); ?>
                                                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group col-md-4">
                <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']; ?>" <?= $topic_filter == $topic['topic_id'] ? 'selected' : ''; ?>>
                                                            <?= htmlspecialchars($topic['topic_name']); ?>
                                                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group col-md-4 d-flex align-items-end">
                <button type="submit" class="btn btn-info">
                    <i class="fas fa-search"></i> ....Filter
                </button>
            </div>
        </div>
    </form>
</div>
                            </div>
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="card-title">Results</h5>
                                </div>
                                <div class="card-body">
                                    <table class="table table-hover table-striped">
                                        <thead>
                                            <tr>
                                        <thead>
                                            <tr>
                                            <th>Email</th>
                                                <th>Category</th>
                                                <th>Topic</th>
                                                <th>Score</th>
                                            </tr>
                                        </thead>
                                        <tbody id="results-table">
                                        <?php
                                            $current_user = null;
                                            $current_topic = null;
                                            $user_score = null;
                                            foreach ($results as $result):
                                                $user_id = $result['user_id'];
                                                $topic_id = $result['topic_id'];
                                                $topic_name = $result['topic_name'];
                                                if ($current_user !== $user_id || $current_topic !== $topic_name) {
                                                    if ($current_user !== null) {
                                                        // Print the previous user's score
                                                        echo '<tr class="clickable-row" data-user-id="' . $current_user . '" data-topic-id="' . $current_topic_id . '">';
                                                        echo '<td>' . htmlspecialchars($user_email) . '</td>';
                                                        echo '<td><span class="badge badge-success">' . htmlspecialchars($category_name) . '</span></td>';

                                                        echo '<td>' . htmlspecialchars($current_topic) . '</td>';
                                                        echo '<td><span class="">' . round(($user_correct / $user_total) * 100, 2) . '%</span></td>';
                                                        echo '</tr>';
                                                    }
                                                    // Initialize new user and topic
                                                    $current_user = $user_id;
                                                    $current_topic = $topic_name;
                                                    $current_topic_id = $topic_id;
                                                    $user_email = $result['email'];
                                                    $category_name = $result['category_name'];
                                                    $user_correct = 0;
                                                    $user_total = 0;
                                                }
                                                $user_total = $result['total_questions'];
                                                $user_correct = $result['correct_answers'];
                                            endforeach;
                                            if ($current_user !== null) {
                                                // Print the last user's score
                                                echo '<tr class="clickable-row" data-user-id="' . $current_user . '" data-topic-id="' . $current_topic_id . '">';
                                                echo '<td>' . htmlspecialchars($user_email) . '</td>';
                                                echo '<td><span class="badge badge-success">' . htmlspecialchars($category_name) . '</span></td>';
                                                echo '<td>' . htmlspecialchars($current_topic) . '</td>';
                                                echo '<td>' . round(($user_correct / $user_total) * 100, 2) . '%</td>';
                                                echo '</tr>';
                                            }
                                            ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </main>

            <footer class="footer">
                <!-- Footer content here -->
            </footer>
        </div>
    </div>

    <script src="js/app.js"></script>
    <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();
                this.submit();
            });

            document.querySelectorAll('.clickable-row').forEach(function(row) {
                row.addEventListener('click', function() {
                    const userId = this.dataset.userId;
                    const topicId = this.dataset.topicId;
                    window.location.href = `user_details.php?user_id=${userId}&topic_id=${topicId}`;
                });
            });
        });
    </script>
</body>

</html>
