<?php
session_start();
require_once '../config/database.php';

if (!isset($_SESSION['user_id']) || $_SESSION['user_type'] != 'admin') {
    header('Location: ../index.php');
    exit();
}

$admin_id = $_SESSION['user_id'];
$conn = getDBConnection();

// Get user data including profile picture
$user_query = "SELECT profile_picture FROM users WHERE id = ?";
$stmt = $conn->prepare($user_query);
$stmt->bind_param("i", $admin_id);
$stmt->execute();
$user_result = $stmt->get_result();
$user = $user_result->fetch_assoc();
$stmt->close();

// Get selected section filter
$selected_section = isset($_GET['section']) ? $_GET['section'] : 'all';
$selected_year = isset($_GET['year']) ? $_GET['year'] : 'all';

// Get all unique sections and year levels
$sections_query = "SELECT DISTINCT section FROM users WHERE user_type = 'student' AND section IS NOT NULL ORDER BY section";
$sections_result = $conn->query($sections_query);
$all_sections = [];
while ($row = $sections_result->fetch_assoc()) {
    $all_sections[] = $row['section'];
}

$years_query = "SELECT DISTINCT year_level FROM users WHERE user_type = 'student' AND year_level IS NOT NULL ORDER BY year_level";
$years_result = $conn->query($years_query);
$all_years = [];
while ($row = $years_result->fetch_assoc()) {
    $all_years[] = $row['year_level'];
}

// Get students based on filter
$students_query = "SELECT * FROM users WHERE user_type = 'student'";
$params = [];
$types = "";

if ($selected_year != 'all') {
    $students_query .= " AND year_level = ?";
    $params[] = $selected_year;
    $types .= "s";
}

if ($selected_section != 'all') {
    $students_query .= " AND section = ?";
    $params[] = $selected_section;
    $types .= "s";
}

$students_query .= " ORDER BY year_level, section, full_name";

if (!empty($params)) {
    $stmt = $conn->prepare($students_query);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $students_result = $stmt->get_result();
} else {
    $students_result = $conn->query($students_query);
}

$students = [];
while ($row = $students_result->fetch_assoc()) {
    $students[] = $row;
}

$conn->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Students by Section - CODELAB</title>
    <link rel="stylesheet" href="../assets/css/style.css">
    <script src="../assets/js/dark-mode.js" defer></script>
</head>
<body>
    <nav class="navbar">
        <div class="nav-container">
            <div class="nav-brand">
                <?php if (isset($user['profile_picture']) && $user['profile_picture']): ?>
                    <img src="../<?php echo htmlspecialchars($user['profile_picture']); ?>" alt="Profile" class="nav-profile-pic">
                <?php else: ?>
                    <div class="nav-avatar">👨‍💼</div>
                <?php endif; ?>
                <h2>CODELAB ADMIN</h2>
            </div>
            <button class="burger-menu" onclick="toggleMenu()">
                <span></span>
                <span></span>
                <span></span>
            </button>
            <div class="nav-menu" id="nav-menu">
                <a href="dashboard.php" class="nav-link">Dashboard</a>
                <a href="users.php" class="nav-link">Manage Users</a>
                <a href="sections.php" class="nav-link active">Students by Section</a>
                <a href="subjects.php" class="nav-link">Manage Subjects</a>
                <a href="activities.php" class="nav-link">Manage Activities</a>
                <a href="profile.php" class="nav-link">Profile</a>
                <a href="../auth/logout.php" class="nav-link logout">Logout</a>
            </div>
        </div>
    </nav>

    <div class="main-content">
        <div class="dashboard-header">
            <h1>🎓 Students by Section</h1>
        </div>

        <!-- Filters -->
        <div class="filter-section">
            <div class="filter-group">
                <label>Filter by Year Level</label>
                <select onchange="updateFilter('year', this.value)">
                    <option value="all" <?php echo $selected_year == 'all' ? 'selected' : ''; ?>>All Years</option>
                    <?php foreach ($all_years as $year): ?>
                        <option value="<?php echo htmlspecialchars($year); ?>" <?php echo $selected_year == $year ? 'selected' : ''; ?>>
                            <?php echo htmlspecialchars($year); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="filter-group">
                <label>Filter by Section</label>
                <select onchange="updateFilter('section', this.value)">
                    <option value="all" <?php echo $selected_section == 'all' ? 'selected' : ''; ?>>All Sections</option>
                    <?php foreach ($all_sections as $section): ?>
                        <option value="<?php echo htmlspecialchars($section); ?>" <?php echo $selected_section == $section ? 'selected' : ''; ?>>
                            <?php echo htmlspecialchars($section); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
        </div>

        <!-- Students Table -->
        <div class="dashboard-section full-width">
            <div class="section-header">
                <h2>📋 Students List (<?php echo count($students); ?> students)</h2>
            </div>
            <?php if (!empty($students)): ?>
                <div class="data-table">
                    <table>
                        <thead>
                            <tr>
                                <th>ID Number</th>
                                <th>Full Name</th>
                                <th>Email</th>
                                <th>Year Level</th>
                                <th>Section</th>
                                <th>Username</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($students as $student): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($student['id_number'] ?? 'N/A'); ?></td>
                                    <td><?php echo htmlspecialchars($student['full_name']); ?></td>
                                    <td><?php echo htmlspecialchars($student['email']); ?></td>
                                    <td><?php echo htmlspecialchars($student['year_level'] ?? 'N/A'); ?></td>
                                    <td><?php echo htmlspecialchars($student['section'] ?? 'N/A'); ?></td>
                                    <td><?php echo htmlspecialchars($student['username']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <p class="empty-message">No students found for the selected filters.</p>
            <?php endif; ?>
        </div>
    </div>

    <script>
        function toggleMenu() {
            const navMenu = document.getElementById('nav-menu');
            navMenu.classList.toggle('active');
        }

        function updateFilter(type, value) {
            const params = new URLSearchParams(window.location.search);
            params.set(type, value);
            window.location.href = 'sections.php?' + params.toString();
        }
    </script>
</body>
</html>
