<?php
include 'db.php'; // Include database connection

// Fetch categories for the dropdown
$categories = $pdo->query("SELECT * FROM categories")->fetchAll();
$topics = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $category_id = $_POST['category_id'];
    $topic_id = $_POST['topic_id'];

    // Handle score update
   // Handle score update
if (isset($_POST['update_score'])) {
    $user_quiz_id = $_POST['user_quiz_id'];
    $new_score = $_POST['score'];
    $total_questions = $_POST['total_questions'];

    // Validate score
    if ($new_score >= 0 && $new_score <= $total_questions) {
        $stmt = $pdo->prepare("UPDATE user_quizzes SET score = ? WHERE id = ?");
        $stmt->execute([$new_score, $user_quiz_id]);
        
        // Store success message in session
        session_start();
        $_SESSION['alert'] = '<div class="alert alert-success" role="alert">Score updated successfully!</div>';
        
       $redirect_url = sprintf("%s?category_id=%s&topic_id=%s", htmlspecialchars($_SERVER['PHP_SELF']), urlencode($category_id), urlencode($topic_id));
header("Location: $redirect_url");
        exit;
    } else {
        $alert = '<div class="alert alert-danger" role="alert">Invalid score. Must be between 0 and ' . $total_questions . '.</div>';
    }
}

    // Handle CSV export
    if (isset($_POST['export_csv'])) {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=quiz_scores.csv');

        $output = fopen('php://output', 'w');
        fputcsv($output, ['User Name', 'Email', 'Topic', 'Score', 'Total Questions']);

        // Re-run query to get results for export
        $query = "
            SELECT user_quizzes.id AS user_quiz_id, users.name AS user_name, users.email, topics.name AS topic_name, 
                   user_quizzes.score, user_quizzes.total_questions 
            FROM user_quizzes
            INNER JOIN users ON users.id = user_quizzes.user_id
            INNER JOIN topics ON topics.id = user_quizzes.topic_id
            WHERE users.category_id = ? AND user_quizzes.topic_id = ?
        ";
        $stmt = $pdo->prepare($query);
        $stmt->execute([$category_id, $topic_id]);
        $results = $stmt->fetchAll();

        foreach ($results as $result) {
            fputcsv($output, [
                $result['user_name'],
                $result['email'],
                $result['topic_name'],
                $result['score'],
                $result['total_questions']
            ]);
        }
        fclose($output);
        exit;
    }

    // Fetch topics based on selected category
    $topics = $pdo->prepare("SELECT * FROM topics WHERE category_id = ?");
    $topics->execute([$category_id]);
    $topics = $topics->fetchAll();

    // Fetch users and their scores based on selected category and topic
    $query = "
        SELECT user_quizzes.id AS user_quiz_id, users.name AS user_name, users.email, topics.name AS topic_name, 
               user_quizzes.score, user_quizzes.total_questions 
        FROM user_quizzes
        INNER JOIN users ON users.id = user_quizzes.user_id
        INNER JOIN topics ON topics.id = user_quizzes.topic_id
        WHERE users.category_id = ? AND user_quizzes.topic_id = ?
    ";
    
    $stmt = $pdo->prepare($query);
    $stmt->execute([$category_id, $topic_id]);
    $results = $stmt->fetchAll();
}
?>

<!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 & 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 href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
    <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 rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <title>kijani quizes</title>
    <link href="css/app.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;600&display=swap" rel="stylesheet">
    <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.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="categoriesandtopic.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">add category and topic</span>
                        </a>
                    </li>
                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="add_question.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="viewusersscores.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="all_questions.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">View Questions</span>
                        </a>
                    </li>
                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="add_user.php">
                            <i class="align-middle" data-feather="user"></i>
                            <span class="align-middle">Add Users</span>
                        </a>
                    </li>
                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="viewusers.php">
                            <i class="align-middle" data-feather="user"></i>
                            <span class="align-middle">view Users</span>
                        </a>
                    </li>
                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="quiz_management.php">
                            <i class="align-middle" data-feather="user"></i>
                            <span class="align-middle">Quiz management</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="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 class="col-10">
                                                <div class="text-dark">Lorem ipsum</div>
                                                <div class="text-muted small mt-1">Aliquam ex eros, imperdiet vulputate hendrerit et.</div>
                                                <div class="text-muted small mt-1">2h ago</div>
                                            </div> -->c
                                        </div>
                                    </a>
                                    <a href="#" class="list-group-item">
                                        <div class="row g-0 align-items-cetnter">
                                            <div class="col-2">
                                                <i class="text-primary" data-feather="home"></i>
                                            </div>
                                            <!-- <div class="col-10">
                                                <div class="text-dark">Login from 192.186.1.1</div>
                                                <div class="text-muted small mt-1">12h 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-success" data-feather="user-plus"></i>
                                            </div>
                                            <!-- <div class="col-10">
                                                <div class="text-dark">New connection</div>
                                                <div class="text-muted small mt-1">Christina accepted your request.</div>
                                                <div class="text-muted small mt-1">14h ago</div>
                                            </div> -->
                                        </div>
                                    </a>
                                </div>
                            </div>
                        </li>
                        <li class="nav-item dropdown">
                            <a class="nav-icon dropdown-toggle d-inline-block d-sm-none" href="#" data-bs-toggle="dropdown">
                                <i class="align-middle" data-feather="settings"></i>
                            </a>
                            <a class="nav-link dropdown-toggle d-none d-sm-inline-block" href="#" data-bs-toggle="dropdown">
                                <img src="img/avatars/kijanilogo.png" class="avatar img-fluid rounded me-1" alt=" " />
                                <span class="text-dark">kijani admin</span>
                            </a>
                            <div class="dropdown-menu dropdown-menu-end">
                                <a class="dropdown-item" href="profile.php">
                                    <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="settings.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 class="row">
                        <div class="col-xl-12">
                            <div class="card">
                                <div class="card-body">
                                    <form method="POST" class="mt-4 mb-4">
                                        <!-- Category Selection -->
                                        <div class="form-group">
                                            <label for="category">Select Category</label>
                                            <select name="category_id" id="category" class="form-control" onchange="this.form.submit()">
                                                <option value="">Select Category</option>
                                                <?php foreach ($categories as $category): ?>
                                                    <option value="<?= $category['id'] ?>" <?php if (isset($category_id) && $category_id == $category['id']) echo 'selected'; ?>>
                                                        <?= htmlspecialchars($category['name']) ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>

                                        <!-- Topic Selection (Auto-refresh based on category) -->
                                        <div class="form-group">
                                            <label for="topic">Select Topic</label>
                                            <select name="topic_id" id="topic" class="form-control">
                                                <option value="">Select Topic</option>
                                                <?php foreach ($topics as $topic): ?>
                                                    <option value="<?= $topic['id'] ?>" <?php if (isset($topic_id) && $topic_id == $topic['id']) echo 'selected'; ?>>
                                                        <?= htmlspecialchars($topic['name']) ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>

                                        <!-- Submit Button -->
                                        <button type="submit" class="btn btn-primary mt-2">Search</button>
                                    </form>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-12">
                            <div class="card">
                                <div class="card-body">
                                    <?php
                                    // Display alert if set
                                    session_start();
                                    if (isset($_SESSION['alert'])) {
                                        echo $_SESSION['alert'];
                                        unset($_SESSION['alert']);
                                    }
                                    if (isset($alert)) {
                                        echo $alert;
                                    }
                                    ?>

                                    <?php if (isset($results) && !empty($results)): ?>
                                        <!-- Export CSV Button -->
                                        <form method="POST" class="mb-3">
                                            <input type="hidden" name="category_id" value="<?= htmlspecialchars($category_id) ?>">
                                            <input type="hidden" name="topic_id" value="<?= htmlspecialchars($topic_id) ?>">
                                            <button type="submit" name="export_csv" class="btn btn-success">Export to CSV</button>
                                        </form>

                                        <table class="table table-hover table-bordered">
                                            <thead>
                                                <tr>
                                                    <th>User Name</th>
                                                    <th>Email</th>
                                                    <th>Topic</th>
                                                    <th>Score</th>
                                                    <th>Total Questions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($results as $result): ?>
                                                    <tr>
                                                        <td><a href="quiz_details.php?user_quiz_id=<?= htmlspecialchars($result['user_quiz_id']) ?>"><?= htmlspecialchars($result['user_name']) ?></a></td>
                                                        <td><?= htmlspecialchars($result['email']) ?></td>
                                                        <td><?= htmlspecialchars($result['topic_name']) ?></td>
                                                        <td>
                                                            <form method="POST" class="form-inline">
                                                                <input type="hidden" name="user_quiz_id" value="<?= htmlspecialchars($result['user_quiz_id']) ?>">
                                                                <input type="hidden" name="category_id" value="<?= htmlspecialchars($category_id) ?>">
                                                                <input type="hidden" name="topic_id" value="<?= htmlspecialchars($topic_id) ?>">
                                                                <input type="hidden" name="total_questions" value="<?= htmlspecialchars($result['total_questions']) ?>">
                                                                <input type="number" name="score" value="<?= htmlspecialchars($result['score']) ?>" class="form-control form-control-sm d-inline-block" style="width: 80px;" min="0" max="<?= htmlspecialchars($result['total_questions']) ?>">
                                                                <button type="submit" name="update_score" class="btn btn-primary btn-sm ml-2">Update</button>
                                                            </form>
                                                        </td>
                                                        <td><?= htmlspecialchars($result['total_questions']) ?></td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    <?php elseif ($_SERVER['REQUEST_METHOD'] === 'POST'): ?>
                                        <p class="text-danger">No results found for the selected category and topic.</p>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </main>
        </div>
    </div>

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