<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');
session_start();    

// Check if the user is logged in
if (isset($_SESSION['SESS_ID_ADMIN'])) {
    $userId = $_SESSION['SESS_ID_ADMIN'];

    // Fetch user details based on the ID
    $query = "SELECT * FROM tbl_admin WHERE id = :id";
    $stmt = $DBH->prepare($query);
    $stmt->bindParam(':id', $userId, PDO::PARAM_INT);
    $stmt->execute();

    if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $adminName = $row['username'];
    }
}

// School information
$schoolName = "Henzy Royal International School";
$schoolAddress = "No 17 Amadi Street, Amakohia, Uratta Owerri Imo State Nigeria";
$supportPhone = "+234 803 292 9756";
$supportEmail = "support@hris.com.ng";

// Fetch available classes, terms, and years for dropdowns
$classes = sql($DBH, "SELECT DISTINCT class FROM tbl_student_results ORDER BY class", [], "rows");
$terms = sql($DBH, "SELECT DISTINCT term FROM tbl_student_results ORDER BY term", [], "rows");
$years = sql($DBH, "SELECT DISTINCT year FROM tbl_student_results ORDER BY year DESC", [], "rows");

// Initialize variables
$class = $_POST['class'] ?? $_GET['class'] ?? '';
$term = $_POST['term'] ?? $_GET['term'] ?? '';
$year = $_POST['year'] ?? $_GET['year'] ?? '';
$broadsheetData = [];
$subjectList = [];
$studentList = [];
$studentAverages = [];
$subjectAverages = [];
$classAverage = 0;
$studentCount = 0;

// Process form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' || (!empty($class) && !empty($term) && !empty($year))) {
    // Validate inputs
    if (empty($class) || empty($term) || empty($year)) {
        $error = "Please select a class, term, and year.";
    } else {
        try {
            // Get all students in the class (grouped by registration number)
            $studentList = sql($DBH, 
                "SELECT registration_no, MIN(name) as name 
                 FROM tbl_student_results 
                 WHERE class = ? AND term = ? AND year = ? 
                 GROUP BY registration_no 
                 ORDER BY MIN(name)",
                [$class, $term, $year], 
                "rows"
            );
            
            if (empty($studentList)) {
                $error = "No students found for the selected criteria.";
            } else {
                $studentCount = count($studentList);
                
                // Get all subjects for this class
                $subjectList = sql($DBH, 
                    "SELECT DISTINCT subject 
                     FROM tbl_student_results 
                     WHERE class = ? AND term = ? AND year = ? 
                     ORDER BY subject",
                    [$class, $term, $year], 
                    "rows"
                );
                
                // Get all results for this class, term, and year
                $allResults = sql($DBH, 
                    "SELECT * FROM tbl_student_results 
                     WHERE class = ? AND term = ? AND year = ? 
                     ORDER BY registration_no, subject",
                    [$class, $term, $year], 
                    "rows"
                );
                
                // Organize results by student and subject
                foreach ($studentList as $student) {
                    $regNo = $student['registration_no'];
                    $studentAverages[$regNo] = 0;
                    $subjectCount = 0;
                    
                    foreach ($subjectList as $subject) {
                        $subjectName = $subject['subject'];
                        
                        // Find the result for this student and subject
                        $result = array_filter($allResults, function($row) use ($regNo, $subjectName) {
                            return $row['registration_no'] === $regNo && $row['subject'] === $subjectName;
                        });
                        
                        if (!empty($result)) {
                            $result = array_values($result)[0];
                            $total = $result['test1'] + $result['test2'] + $result['exam'];
                            $broadsheetData[$regNo][$subjectName] = [
                                'test1' => $result['test1'],
                                'test2' => $result['test2'],
                                'exam' => $result['exam'],
                                'total' => $total
                            ];
                            
                            $studentAverages[$regNo] += $total;
                            $subjectCount++;
                            
                            // Initialize subject average if not set
                            if (!isset($subjectAverages[$subjectName])) {
                                $subjectAverages[$subjectName] = 0;
                            }
                            $subjectAverages[$subjectName] += $total;
                        } else {
                            $broadsheetData[$regNo][$subjectName] = [
                                'test1' => '-',
                                'test2' => '-',
                                'exam' => '-',
                                'total' => '-'
                            ];
                        }
                    }
                    
                    // Calculate student average
                    if ($subjectCount > 0) {
                        $studentAverages[$regNo] = round($studentAverages[$regNo] / $subjectCount, 2);
                        $classAverage += $studentAverages[$regNo];
                    }
                }
                
                // Calculate subject averages
                foreach ($subjectAverages as $subject => $total) {
                    $subjectAverages[$subject] = round($total / $studentCount, 2);
                }
                
                // Calculate class average
                if ($studentCount > 0) {
                    $classAverage = round($classAverage / $studentCount, 2);
                }
                
                // Calculate positions based on averages
                $studentPositions = calculatePositions($studentAverages);
                
                // Sort student list by position
                usort($studentList, function($a, $b) use ($studentPositions) {
                    $posA = $studentPositions[$a['registration_no']] ?? PHP_INT_MAX;
                    $posB = $studentPositions[$b['registration_no']] ?? PHP_INT_MAX;
                    
                    // If positions are equal, sort by registration number
                    if ($posA == $posB) {
                        return strcmp($a['registration_no'], $b['registration_no']);
                    }
                    
                    return $posA <=> $posB;
                });
            }
        } catch (PDOException $e) {
            $error = "Database error: " . $e->getMessage();
        }
    }
}

// Function to calculate positions based on averages
function calculatePositions($averages) {
    // Filter out non-numeric averages
    $validAverages = array_filter($averages, 'is_numeric');
    
    // Sort averages in descending order
    arsort($validAverages);
    
    $positions = [];
    $position = 1;
    $prevAverage = null;
    $skipCount = 0;
    
    foreach ($validAverages as $regNo => $average) {
        if ($prevAverage !== null && $average < $prevAverage) {
            $position += 1 + $skipCount;
            $skipCount = 0;
        } elseif ($prevAverage !== null && $average == $prevAverage) {
            $skipCount++;
        }
        
        $positions[$regNo] = $position;
        $prevAverage = $average;
    }
    
    return $positions;
}

// Function to determine grade from score
function getGrade($score) {
    if (!is_numeric($score)) return '-';
    
    if ($score >= 90) return 'A+';
    if ($score >= 80) return 'A';
    if ($score >= 75) return 'B+';
    if ($score >= 70) return 'B';
    if ($score >= 65) return 'C+';
    if ($score >= 60) return 'C';
    if ($score >= 50) return 'D';
    return 'F';
}

// Function to format position
function formatPosition($position) {
    if (!is_numeric($position)) return $position;
    
    if ($position == 1) return '1st';
    if ($position == 2) return '2nd';
    if ($position == 3) return '3rd';
    return $position . 'th';
}

// Function to get grade CSS class
function getGradeClass($grade) {
    if ($grade === 'A+') return 'grade-Aplus';
    if ($grade === 'A') return 'grade-A';
    if ($grade === 'B+') return 'grade-Bplus';
    if ($grade === 'B') return 'grade-B';
    if ($grade === 'C+') return 'grade-Cplus';
    if ($grade === 'C') return 'grade-C';
    if ($grade === 'D') return 'grade-D';
    if ($grade === 'F') return 'grade-F';
    return '';
}
?>

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

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>Result Broadsheet - Henzy Royal International School</title>
  <link rel="icon" type="image/png" href="coll_logo.png">
  
  <!-- Bootstrap 5 CSS -->
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  
  <!-- Font Awesome -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
  
  <!-- Google Fonts - Poppins -->
  <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;500;600;700&display=swap" rel="stylesheet">
  
  <!-- Custom CSS -->
  <style>
    :root {
      --primary-blue: #1a4b8c;    /* Royal Blue */
      --secondary-gold: #FFD700;    /* Gold */
      --dark-blue: #0f2b4f;         /* Dark Blue */
      --light-blue: #4a7db5;         /* Medium Blue */
      --light-gold: #FFF1B8;          /* Light Gold */
      --bg-light: #f5f9ff;
    }
    
    body {
      font-family: 'Poppins', sans-serif;
      background-color: var(--bg-light);
      padding-top: 56px;
      color: #333;
      font-size: 0.9rem;
    }
    
    /* Navbar Styles */
    .navbar {
      background: linear-gradient(135deg, var(--primary-blue), var(--dark-blue));
      box-shadow: 0 0.15rem 1.75rem 0 rgba(26, 75, 140, 0.2);
      position: fixed;
      top: 0;
      width: 100%;
      z-index: 1030;
      color: white;
      border-bottom: 2px solid var(--secondary-gold);
    }
    
    .navbar-brand {
      font-weight: 800;
      font-size: 1.2rem;
      color: white;
      display: flex;
      align-items: center;
    }
    
    .navbar-brand img {
      border-radius: 4px;
      margin-right: 10px;
    }
    
    /* Sidebar Styles */
    .sidebar {
      background: linear-gradient(180deg, var(--dark-blue) 0%, var(--primary-blue) 100%);
      position: fixed;
      top: 56px;
      left: 0;
      width: 250px;
      height: calc(100vh - 56px);
      transition: all 0.3s;
      z-index: 1000;
      overflow-y: auto;
      border-right: 3px solid var(--secondary-gold);
    }
    
    .sidebar .nav-link {
      color: rgba(255, 255, 255, 0.9);
      font-weight: 500;
      padding: 0.75rem 1rem;
      margin-bottom: 0.2rem;
      transition: all 0.2s;
      border-left: 3px solid transparent;
    }
    
    .sidebar .nav-link:hover {
      color: white;
      background-color: rgba(255, 215, 0, 0.15);
      border-left-color: var(--secondary-gold);
    }
    
    .sidebar .nav-link.active {
      color: var(--dark-blue);
      background-color: var(--secondary-gold);
      border-left-color: var(--dark-blue);
      font-weight: 600;
    }
    
    .sidebar .nav-link i {
      margin-right: 0.5rem;
      width: 20px;
      text-align: center;
    }
    
    .sidebar-logo {
      text-align: center;
      margin-bottom: 1rem;
      padding: 1rem;
    }
    
    .logo-text {
      font-size: 1.5rem;
      font-weight: 900;
      color: white;
      letter-spacing: 2px;
      line-height: 1.2;
    }
    
    .logo-text span {
      color: var(--secondary-gold);
    }
    
    .logo-tagline {
      font-size: 0.65rem;
      color: var(--secondary-gold);
      letter-spacing: 1px;
    }
    
    /* Main Content Styles */
    .main-content {
      margin-left: 250px;
      width: calc(100% - 250px);
      transition: all 0.3s;
      min-height: calc(100vh - 56px);
      padding-bottom: 60px;
    }
    
    @media (max-width: 991.98px) {
      .sidebar {
        left: -250px;
      }
      .main-content {
        margin-left: 0;
        width: 100%;
      }
      body {
        padding-top: 56px;
      }
    }
    
    /* Welcome banner */
    .welcome-banner {
      background: linear-gradient(135deg, var(--primary-blue), var(--light-blue));
      color: white;
      border-radius: 0.5rem;
      padding: 0.75rem 1rem;
      margin: 1rem;
      box-shadow: 0 4px 6px rgba(0,0,0,0.1);
      border-left: 5px solid var(--secondary-gold);
    }
    
    /* Form Styles */
    .form-container {
      background-color: white;
      border-radius: 0.5rem;
      padding: 1rem;
      margin: 1rem;
      box-shadow: 0 0.15rem 1.75rem 0 rgba(26, 75, 140, 0.1);
      border: 1px solid rgba(255, 215, 0, 0.2);
    }
    
    /* Admin Info Styles */
    .admin-info {
      display: flex;
      align-items: center;
      background-color: rgba(255, 215, 0, 0.2);
      padding: 8px 12px;
      border-radius: 8px;
      color: white;
    }
    
    .admin-name {
      margin-right: 5px;
      font-weight: 600;
    }
    
    .admin-access {
      font-style: italic;
      opacity: 0.8;
      margin-right: 10px;
      font-size: 0.9rem;
    }
    
    .admin-icon {
      font-size: 1.5rem;
      color: var(--secondary-gold);
    }
    
    /* Table Styles */
    .table-container {
      background-color: white;
      border-radius: 0.5rem;
      padding: 1rem;
      margin: 1rem;
      box-shadow: 0 0.15rem 1.75rem 0 rgba(26, 75, 140, 0.1);
      overflow-x: auto;
      border: 1px solid rgba(255, 215, 0, 0.2);
    }
    
    .broadsheet-table {
      width: 100%;
      border-collapse: collapse;
      font-size: 0.8rem;
    }
    
    .broadsheet-table th,
    .broadsheet-table td {
      padding: 0.3rem;
      border: 1px solid #dee2e6;
      text-align: center;
      vertical-align: middle;
    }
    
    .broadsheet-table th {
      background: linear-gradient(135deg, var(--primary-blue), var(--dark-blue));
      color: white;
      font-weight: 600;
      position: sticky;
      top: 0;
      z-index: 10;
      font-size: 0.75rem;
    }
    
    .broadsheet-table .student-name {
      text-align: left;
      font-weight: 600;
      background-color: var(--light-gold);
      position: sticky;
      left: 0;
      z-index: 5;
      min-width: 120px;
      max-width: 120px;
      white-space: nowrap;
      overflow: hidden;
      text-overflow: ellipsis;
    }
    
    .broadsheet-table .subject-header {
      background: linear-gradient(135deg, var(--light-blue), var(--primary-blue));
      writing-mode: vertical-lr;
      transform: rotate(180deg);
      min-width: 25px;
      white-space: nowrap;
      font-size: 0.7rem;
      color: white;
      font-weight: bold;
    }
    
    .broadsheet-table .total-col {
      background-color: var(--light-gold);
      font-weight: 600;
    }
    
    .broadsheet-table .grade-col {
      background-color: #fff3e0;
    }
    
    .broadsheet-table .position-col {
      background-color: var(--bg-light);
      font-weight: 600;
    }
    
    .broadsheet-table .average-row {
      background: linear-gradient(135deg, #f5f5f5, #e8e8e8);
      font-weight: 600;
    }
    
    .broadsheet-table tr:nth-child(even) {
      background-color: #f9f9f9;
    }
    
    .broadsheet-table tr:hover {
      background-color: var(--light-gold);
    }
    
    /* Footer styles */
    .footer {
      background: linear-gradient(135deg, var(--dark-blue), var(--primary-blue));
      color: white;
      padding: 12px 0;
      position: fixed;
      bottom: 0;
      width: 100%;
      z-index: 100;
      font-size: 0.8rem;
      border-top: 2px solid var(--secondary-gold);
    }
    
    .copyright-text {
      font-weight: 600;
    }
    
    .copyright-text i {
      color: var(--secondary-gold);
    }
    
    .powered-by {
      display: flex;
      align-items: center;
      justify-content: flex-end;
      color: white;
    }
    
    .powered-by img {
      height: 18px;
      margin-left: 6px;
      filter: brightness(0) invert(1);
    }
    
    .powered-by-link {
      display: flex;
      align-items: center;
      text-decoration: none;
      color: var(--secondary-gold);
      transition: opacity 0.3s ease;
    }
    
    .powered-by-link:hover {
      opacity: 0.8;
      color: white;
    }
    
    /* Support badge */
    .support-badge {
      background: linear-gradient(135deg, var(--secondary-gold), #e6b800);
      color: var(--dark-blue);
      font-weight: 600;
      padding: 0.5rem 1rem;
      border: 1px solid var(--dark-blue);
    }
    
    /* Breadcrumbs */
    .breadcrumb-custom .breadcrumb {
      background-color: transparent;
      padding: 0;
    }
    
    .breadcrumb-custom .breadcrumb-item a {
      color: var(--primary-blue);
      text-decoration: none;
      font-weight: 500;
    }
    
    .breadcrumb-custom .breadcrumb-item a:hover {
      color: var(--secondary-gold);
    }
    
    .breadcrumb-custom .breadcrumb-item.active {
      color: var(--dark-blue);
      font-weight: 600;
    }
    
    .breadcrumb-item + .breadcrumb-item::before {
      color: var(--secondary-gold);
    }
    
    /* Action buttons */
    .btn-primary {
      background: linear-gradient(135deg, var(--primary-blue), var(--dark-blue));
      border-color: var(--secondary-gold);
      color: white;
    }
    
    .btn-primary:hover {
      background: linear-gradient(135deg, var(--secondary-gold), #e6b800);
      border-color: var(--dark-blue);
      color: var(--dark-blue);
    }
    
    .btn-print {
      background: linear-gradient(135deg, #28a745, #218838);
      color: white;
      border: none;
    }
    
    .btn-print:hover {
      background: linear-gradient(135deg, #218838, #1e7e34);
    }
    
    .btn-outline-light {
      border-color: var(--secondary-gold);
      color: var(--secondary-gold);
    }
    
    .btn-outline-light:hover {
      background-color: var(--secondary-gold);
      border-color: var(--dark-blue);
      color: var(--dark-blue);
    }
    
    /* Grade colors */
    .grade-Aplus { color: #1a4b8c; font-weight: bold; }
    .grade-A { color: #2a5ca3; font-weight: bold; }
    .grade-Bplus { color: #3a6db5; font-weight: bold; }
    .grade-B { color: #4a7db5; font-weight: bold; }
    .grade-Cplus { color: #5a8ec9; font-weight: bold; }
    .grade-C { color: #6a9fd9; font-weight: bold; }
    .grade-D { color: #7aafe9; font-weight: bold; }
    .grade-F { color: #dc3545; font-weight: bold; }
    
    /* Position badges */
    .position-badge {
      display: inline-block;
      padding: 0.2rem 0.5rem;
      border-radius: 0.25rem;
      font-weight: bold;
      font-size: 0.75rem;
    }
    
    .position-1 { background: linear-gradient(135deg, var(--secondary-gold), #e6b800); color: var(--dark-blue); }
    .position-2 { background: linear-gradient(135deg, #C0C0C0, #a0a0a0); color: #000; }
    .position-3 { background: linear-gradient(135deg, #CD7F32, #b06b2a); color: #fff; }
    .position-other { background-color: #e9ecef; color: #495057; }
    
    /* Form elements */
    .form-select, .form-label {
      font-size: 0.85rem;
    }
    
    .form-select:focus {
      border-color: var(--secondary-gold);
      box-shadow: 0 0 0 0.25rem rgba(255, 215, 0, 0.25);
    }
    
    /* Alert styles */
    .alert-danger {
      background-color: #f8d7da;
      border-color: #f5c6cb;
      color: #721c24;
    }
    
    .alert-info {
      background-color: var(--bg-light);
      border-color: var(--secondary-gold);
      color: var(--dark-blue);
    }
    
    /* Print styles */
    @media print {
      body {
        background-color: white;
        font-size: 8pt;
        padding: 0;
        margin: 0;
      }
      
      .no-print {
        display: none !important;
      }
      
      .sidebar {
        display: none !important;
      }
      
      .main-content {
        margin-left: 0;
        width: 100%;
      }
      
      .navbar {
        display: none !important;
      }
      
      .footer {
        display: none !important;
      }
      
      .table-container {
        box-shadow: none;
        padding: 0;
        margin: 0;
        border: none;
      }
      
      .broadsheet-table {
        font-size: 7pt;
      }
      
      .broadsheet-table th {
        -webkit-print-color-adjust: exact;
        print-color-adjust: exact;
      }
      
      .broadsheet-table .student-name {
        min-width: 100px;
        max-width: 100px;
      }
      
      @page {
        size: landscape;
        margin: 0.5cm;
      }
    }
  </style>
</head>

<body>
  <!-- Top Navigation -->
  <nav class="navbar navbar-expand-lg navbar-dark">
    <div class="container-fluid">
      <button id="sidebarToggle" class="btn btn-link d-lg-none me-2">
        <i class="fas fa-bars" style="color: white;"></i>
      </button>
      <a class="navbar-brand d-none d-lg-block" href="#">
        <img src="coll_logo.png" alt="Henzy Royal International School" height="35" style="border-radius: 4px;">
        HENZY ROYAL INT'L SCHOOL
      </a>
      <div class="ms-auto d-flex align-items-center">
        <div class="admin-info me-3">
          <span class="admin-name"><?php echo htmlspecialchars($adminName ?? 'Admin'); ?></span>
          <span class="admin-access">(Administrator)</span>
          <i class="fas fa-user-circle admin-icon"></i>
        </div>
        <a href="logout.php" class="btn btn-sm btn-outline-light">
          <i class="fas fa-sign-out-alt"></i> Logout
        </a>
      </div>
    </div>
  </nav>

  <!-- Sidebar -->
  <nav id="sidebar" class="sidebar">
    <div class="position-sticky pt-3">
      <div class="sidebar-logo d-none d-lg-block">
        <div class="logo-text">
          H.R.<span>I.S</span>
        </div>
        <div class="logo-tagline">
          THE PRIDE OF EXCELLENCE
        </div>
        <hr style="border-color: var(--secondary-gold); opacity: 0.3;">
        <p class="text-white small px-2">
          <i class="fas fa-map-marker-alt" style="color: var(--secondary-gold);"></i><br>
          <?php echo $schoolAddress; ?>
        </p>
      </div>
      
      <ul class="nav flex-column">
        <li class="nav-item">
          <a class="nav-link" href="main.php">
            <i class="fas fa-fw fa-tachometer-alt"></i>
            Admin Dashboard
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link active" href="download_broadsheet.php">
            <i class="fas fa-fw fa-file-pdf"></i>
            Result Broadsheet
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="uploaded_class_results.php">
            <i class="fas fa-fw fa-list"></i>
            Uploaded Results
          </a>
        </li>
      </ul>
    </div>
  </nav>

  <!-- Main Content -->
  <main class="main-content">
    <!-- Welcome Banner -->
    <div class="welcome-banner">
      <div class="d-flex justify-content-between align-items-center">
        <div>
          <h4 class="mb-1"><i class="fas fa-crown me-2" style="color: var(--secondary-gold);"></i>Result Broadsheet Generator</h4>
          <p class="mb-0 small">Henzy Royal International School - Admin Dashboard</p>
        </div>
        <div class="d-none d-sm-inline-block">
          <span class="badge support-badge p-1">
            <i class="fas fa-print me-1"></i> Printable Format
          </span>
        </div>
      </div>
    </div>

    <!-- Search Form -->
    <div class="form-container no-print">
      <form method="POST" action="">
        <div class="row align-items-end">
          <div class="col-md-3">
            <div class="form-group mb-2">
              <label for="class" class="form-label"><i class="fas fa-building me-1" style="color: var(--secondary-gold);"></i>Class:</label>
              <select id="class" name="class" class="form-select form-select-sm" required>
                <option value="">Select Class</option>
                <?php foreach ($classes as $classOption): ?>
                  <option value="<?php echo htmlspecialchars($classOption['class']); ?>" 
                    <?php echo ($classOption['class'] === $class) ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($classOption['class']); ?>
                  </option>
                <?php endforeach; ?>
              </select>
            </div>
          </div>
          
          <div class="col-md-3">
            <div class="form-group mb-2">
              <label for="term" class="form-label"><i class="fas fa-calendar-alt me-1" style="color: var(--secondary-gold);"></i>Term:</label>
              <select id="term" name="term" class="form-select form-select-sm" required>
                <option value="">Select Term</option>
                <?php foreach ($terms as $termOption): ?>
                  <option value="<?php echo htmlspecialchars($termOption['term']); ?>" 
                    <?php echo ($termOption['term'] === $term) ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($termOption['term']); ?>
                  </option>
                <?php endforeach; ?>
              </select>
            </div>
          </div>
          
          <div class="col-md-3">
            <div class="form-group mb-2">
              <label for="year" class="form-label"><i class="fas fa-calendar me-1" style="color: var(--secondary-gold);"></i>Academic Year:</label>
              <select id="year" name="year" class="form-select form-select-sm" required>
                <option value="">Select Year</option>
                <?php foreach ($years as $yearOption): ?>
                  <option value="<?php echo htmlspecialchars($yearOption['year']); ?>" 
                    <?php echo ($yearOption['year'] === $year) ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($yearOption['year']); ?>
                  </option>
                <?php endforeach; ?>
              </select>
            </div>
          </div>
          
          <div class="col-md-3">
            <button type="submit" class="btn btn-primary btn-sm w-100">
              <i class="fas fa-search me-1"></i> Generate Broadsheet
            </button>
          </div>
        </div>
      </form>
    </div>

    <?php if (isset($error)): ?>
      <div class="alert alert-danger m-3 py-2">
        <i class="fas fa-exclamation-circle me-2"></i> <?php echo $error; ?>
      </div>
    <?php endif; ?>

    <?php if (!empty($broadsheetData) && !empty($studentList)): ?>
    <div class="table-container">
      <div class="d-flex justify-content-between align-items-center mb-2 no-print">
        <h5 class="mb-0">
          <i class="fas fa-file-alt me-2" style="color: var(--secondary-gold);"></i>
          Broadsheet for <?php echo htmlspecialchars($class); ?> - 
          <?php echo htmlspecialchars($term); ?> Term, <?php echo htmlspecialchars($year); ?>
          <small class="text-muted">(<?php echo $studentCount; ?> students)</small>
        </h5>
        <button class="btn btn-print btn-sm" onclick="window.print()">
          <i class="fas fa-print me-1"></i> Print Broadsheet
        </button>
      </div>
      
      <div class="table-responsive">
        <table class="broadsheet-table">
          <thead>
            <tr>
              <th rowspan="2" class="student-name">Student Name</th>
              <th rowspan="2">Reg No</th>
              <?php foreach ($subjectList as $subject): ?>
                <th colspan="3" class="subject-header"><?php echo htmlspecialchars($subject['subject']); ?></th>
              <?php endforeach; ?>
              <th rowspan="2" class="total-col">Avg</th>
              <th rowspan="2" class="grade-col">Grade</th>
              <th rowspan="2" class="position-col">Pos</th>
            </tr>
            <tr>
              <?php foreach ($subjectList as $subject): ?>
                <th>CA1</th>
                <th>CA2</th>
                <th>Total</th>
              <?php endforeach; ?>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($studentList as $student): ?>
              <?php
              $regNo = $student['registration_no'];
              $studentTotal = 0;
              $subjectCount = 0;
              $position = $studentPositions[$regNo] ?? 'N/A';
              $average = $studentAverages[$regNo] ?? '-';
              $grade = getGrade($average);
              $gradeClass = getGradeClass($grade);
              ?>
              <tr>
                <td class="student-name" title="<?php echo htmlspecialchars($student['name']); ?>">
                  <i class="fas fa-user me-1" style="color: var(--secondary-gold);"></i><?php echo htmlspecialchars($student['name']); ?>
                </td>
                <td><i class="fas fa-id-card me-1" style="color: var(--secondary-gold);"></i><?php echo htmlspecialchars($regNo); ?></td>
                
                <?php foreach ($subjectList as $subject): ?>
                  <?php
                  $subjectName = $subject['subject'];
                  $result = $broadsheetData[$regNo][$subjectName] ?? null;
                  
                  if ($result && is_numeric($result['total'])) {
                    $studentTotal += $result['total'];
                    $subjectCount++;
                  }
                  ?>
                  
                  <td><?php echo $result ? htmlspecialchars($result['test1']) : '-'; ?></td>
                  <td><?php echo $result ? htmlspecialchars($result['test2']) : '-'; ?></td>
                  <td class="total-col">
                    <?php echo $result ? htmlspecialchars($result['total']) : '-'; ?>
                  </td>
                <?php endforeach; ?>
                
                <td class="total-col">
                  <?php echo $average; ?>
                </td>
                
                <td class="grade-col <?php echo $gradeClass; ?>">
                  <?php echo $grade; ?>
                </td>
                
                <td class="position-col">
                  <?php if (is_numeric($position)): ?>
                    <?php
                    $positionClass = 'position-other';
                    if ($position == 1) $positionClass = 'position-1';
                    elseif ($position == 2) $positionClass = 'position-2';
                    elseif ($position == 3) $positionClass = 'position-3';
                    ?>
                    <span class="position-badge <?php echo $positionClass; ?>">
                      <?php echo formatPosition($position); ?>
                    </span>
                  <?php else: ?>
                    <?php echo $position; ?>
                  <?php endif; ?>
                </td>
              </tr>
            <?php endforeach; ?>
            
            <!-- Subject Averages Row -->
            <tr class="average-row">
              <td colspan="2" class="student-name"><i class="fas fa-calculator me-1" style="color: var(--secondary-gold);"></i>Subject Averages</td>
              
              <?php foreach ($subjectList as $subject): ?>
                <?php
                $subjectName = $subject['subject'];
                $avg = $subjectAverages[$subjectName] ?? '-';
                ?>
                <td colspan="2"></td>
                <td class="total-col"><?php echo $avg; ?></td>
              <?php endforeach; ?>
              
              <td class="total-col"><?php echo $classAverage; ?></td>
              <td colspan="2"></td>
            </tr>
          </tbody>
        </table>
      </div>
      
      <div class="mt-2 no-print">
        <div class="alert alert-info py-2">
          <h6 class="mb-1"><i class="fas fa-info-circle me-1" style="color: var(--secondary-gold);"></i> Grading System</h6>
          <div class="row">
            <div class="col-md-2"><span class="grade-Aplus">A+</span>: 90-100%</div>
            <div class="col-md-2"><span class="grade-A">A</span>: 80-89%</div>
            <div class="col-md-2"><span class="grade-Bplus">B+</span>: 75-79%</div>
            <div class="col-md-2"><span class="grade-B">B</span>: 70-74%</div>
            <div class="col-md-2"><span class="grade-Cplus">C+</span>: 65-69%</div>
            <div class="col-md-2"><span class="grade-C">C</span>: 60-64%</div>
            <div class="col-md-2"><span class="grade-D">D</span>: 50-59%</div>
            <div class="col-md-2"><span class="grade-F">F</span>: 0-49%</div>
          </div>
        </div>
        
        <button class="btn btn-print btn-sm" onclick="window.print()">
          <i class="fas fa-print me-1"></i> Print Broadsheet
        </button>
      </div>
    </div>
    <?php elseif ($_SERVER['REQUEST_METHOD'] === 'POST'): ?>
      <div class="form-container text-center py-3">
        <i class="fas fa-info-circle fa-2x mb-2" style="color: var(--primary-blue);"></i>
        <h5 class="text-muted">No Results Found</h5>
        <p class="mb-0">There are no results for the selected class, term, and year combination.</p>
      </div>
    <?php else: ?>
      <div class="form-container text-center py-4">
        <i class="fas fa-file-pdf fa-3x mb-2" style="color: var(--primary-blue);"></i>
        <h5 class="text-muted">Result Broadsheet Generator</h5>
        <p class="mb-0">Select a class, term, and academic year to generate a broadsheet.</p>
      </div>
    <?php endif; ?>
  </main>

  <!-- Footer -->
  <footer class="footer no-print">
    <div class="container-fluid">
      <div class="row align-items-center">
        <div class="col-md-6">
          <span class="copyright-text">
            <i class="fas fa-copyright me-1"></i> <?php echo date("Y"); ?> Henzy Royal International School
          </span>
        </div>
        <div class="col-md-6">
          <div class="powered-by">
            <a href="https://dataswift.com.ng" target="_blank" rel="noopener noreferrer" class="powered-by-link">
              <span>Powered by DataSwift Coders</span>
              <img src="datalogo.png" alt="DataSwift Logo">
            </a>
          </div>
        </div>
      </div>
    </div>
  </footer>

  <!-- Bootstrap 5 JS Bundle with Popper --> 
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
  
  <!-- Custom Scripts -->
  <script>
    // Toggle sidebar on mobile
    document.getElementById('sidebarToggle').addEventListener('click', function() {
      document.getElementById('sidebar').classList.toggle('active');
      document.querySelector('.main-content').classList.toggle('active');
    });
    
    // Close sidebar when clicking outside on mobile
    document.addEventListener('click', function(event) {
      const sidebar = document.getElementById('sidebar');
      const sidebarToggle = document.getElementById('sidebarToggle');
      
      if (window.innerWidth < 992 && 
          !sidebar.contains(event.target) && 
          !sidebarToggle.contains(event.target) && 
          sidebar.classList.contains('active')) {
        sidebar.classList.remove('active');
        document.querySelector('.main-content').classList.remove('active');
      }
    });
    
    // Auto-hide alerts after 5 seconds
    setTimeout(function() {
        $('.alert').fadeOut('slow');
    }, 5000);
  </script>
</body>
</html>