<?php
require_once 'db.php'; // Include the database connection

// Handle CSV export
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    try {
        // Fetch data for CSV
        $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
        $categories = [];
        foreach ($results as $row) {
            $category = $row['category_name'];
            if (!isset($categories[$category])) {
                $categories[$category] = [];
            }
            $categories[$category][] = $row['user_name'];
        }

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

        // Set headers for CSV download
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="users_no_quizzes.csv"');

        // Create a file pointer connected to the output stream
        $output = fopen('php://output', 'w');

        // Write CSV headers
        fputcsv($output, ['Category', 'User']);

        // Write data to CSV
        foreach ($categories as $category => $users) {
            if (count($users) > 0) {
                foreach ($users as $user) {
                    fputcsv($output, [$category, $user]);
                }
            } else {
                fputcsv($output, [$category, 'No users found']);
            }
        }

        // Close the file pointer and exit
        fclose($output);
        exit;

    } catch (PDOException $e) {
        // Handle errors by redirecting with an error message
        header('Location: users_no_quizzes.php?error=' . urlencode('Database error: ' . $e->getMessage()));
        exit;
    }
}

// Fetch data for display
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
    $categories = [];
    foreach ($results as $row) {
        $category = $row['category_name'];
        if (!isset($categories[$category])) {
            $categories[$category] = [];
        }
        $categories[$category][] = $row['user_name'];
    }

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

} catch (PDOException $e) {
    $error = "Database error: " . $e->getMessage();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Users with No Quiz Attempts</title>
    <!-- Bootstrap 5 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
    <style>
        body { padding: 20px; }
        .accordion-button { font-weight: bold; }
        .no-users { color: #6c757d; font-style: italic; }
        .container { max-width: 800px; }
    </style>
</head>
<body>
    <div class="container">
        <h1 class="my-4 text-center">Users Who Haven't Attempted Quizzes</h1>

        <!-- Export Button -->
        <div class="mb-4 text-center">
            <a href="users_no_quizzes.php?export=csv" class="btn btn-primary">Export to CSV</a>
        </div>

        <!-- Error Message -->
        <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger" role="alert">
                <?php echo htmlspecialchars($_GET['error']); ?>
            </div>
        <?php elseif (isset($error)): ?>
            <div class="alert alert-danger" role="alert">
                <?php echo htmlspecialchars($error); ?>
            </div>
        <?php else: ?>
            <!-- Accordion Display -->
            <div class="accordion" id="categoriesAccordion">
                <?php foreach ($categories as $category => $users): ?>
                    <?php
                    $accordionId = 'collapse-' . preg_replace('/\s+/', '-', htmlspecialchars($category));
                    ?>
                    <div class="accordion-item">
                        <h2 class="accordion-header">
                            <button class="accordion-button" type="button" data-bs-toggle="collapse" data-bs-target="#<?php echo $accordionId; ?>" aria-expanded="false" aria-controls="<?php echo $accordionId; ?>">
                                <?php echo htmlspecialchars($category); ?> (<?php echo count($users); ?> users)
                            </button>
                        </h2>
                        <div id="<?php echo $accordionId; ?>" class="accordion-collapse collapse" data-bs-parent="#categoriesAccordion">
                            <div class="accordion-body">
                                <?php if (count($users) > 0): ?>
                                    <ul class="list-group">
                                        <?php foreach ($users as $user): ?>
                                            <li class="list-group-item"><?php echo htmlspecialchars($user); ?></li>
                                        <?php endforeach; ?>
                                    </ul>
                                <?php else: ?>
                                    <p class="no-users">No users found who haven’t attempted quizzes in this category.</p>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>
                <?php if (empty($categories)): ?>
                    <p class="text-center no-users">No categories or users found.</p>
                <?php endif; ?>
            </div>
        <?php endif; ?>
    </div>

    <!-- Bootstrap 5 JS and Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
</body>
</html>