<?php
// Enable full error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once('source/session.php');
require_once('source/error.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');

// Database connection
$host = 'localhost'; 
$dbname = 'vdacocmz_schdb_coll'; 
$user = 'vdacocmz_mendohz'; 
$pass = '@Dei22*#';

try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Process form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['upload_results'])) {
    $class_id = $_POST['class'];
    $term = $_POST['term'];
    $session_id = $_POST['session'];
    
    // Get class details
    try {
        $class_stmt = $DBH->prepare("SELECT id, class FROM tbl_class WHERE id = ?");
        $class_stmt->execute([$class_id]);
        $class_data = $class_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$class_data) {
            throw new Exception("Class not found");
        }
        $class = $class_data['class'];
    } catch (Exception $e) {
        $_SESSION['error_message'] = "Error getting class information: " . $e->getMessage();
        header("Location: ".$_SERVER['PHP_SELF']);
        exit();
    }
    
    // Get teacher for this class (using class name)
    try {
        $teacher_stmt = $DBH->prepare("SELECT hash, fullname FROM tbl_user WHERE class = ? AND teacher = 'true' LIMIT 1");
        $teacher_stmt->execute([$class]); // Using class name here
        $teacher_data = $teacher_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$teacher_data) {
            // If no teacher assigned, use the current admin user as fallback
            $teacher_no = $_SESSION['hash'];
            $teacher_name = $_SESSION['fullname'] ?? 'Admin';
        } else {
            $teacher_no = $teacher_data['hash'];
            $teacher_name = $teacher_data['fullname'];
        }
    } catch (PDOException $e) {
        $_SESSION['error_message'] = "Error getting teacher information: " . $e->getMessage();
        header("Location: ".$_SERVER['PHP_SELF']);
        exit();
    }
    
    // Get session name
    try {
        $session_stmt = $DBH->prepare("SELECT session FROM tbl_session WHERE id = ?");
        $session_stmt->execute([$session_id]);
        $session = $session_stmt->fetchColumn();
    } catch (PDOException $e) {
        $_SESSION['error_message'] = "Error getting session: " . $e->getMessage();
        header("Location: ".$_SERVER['PHP_SELF']);
        exit();
    }
    
    $success_count = 0;
    $error_count = 0;
    $error_details = [];
    $csv_numbers = [];
    
    foreach ($_POST['subjects'] as $index => $subject_data) {
        $subject_id = $subject_data['subject_id'];
        $csv_no = mt_rand(100000, 999999);
        $csv_numbers[$subject_id] = $csv_no;
        
        if (!isset($_FILES['subjects']['tmp_name'][$index]['file']) || 
            !is_uploaded_file($_FILES['subjects']['tmp_name'][$index]['file'])) {
            $error_details[] = "No file uploaded for subject row " . ($index + 1);
            $error_count++;
            continue;
        }
        
        $file = $_FILES['subjects']['tmp_name'][$index]['file'];
        $file_name = $_FILES['subjects']['name'][$index]['file'];
        
        try {
            $subject_stmt = $DBH->prepare("SELECT subject FROM tbl_subject WHERE id = ?");
            $subject_stmt->execute([$subject_id]);
            $subject = $subject_stmt->fetchColumn();
            
            if (!$subject) {
                throw new Exception("Subject not found");
            }
        } catch (PDOException $e) {
            $error_details[] = "Error getting subject name for row " . ($index + 1) . ": " . $e->getMessage();
            $error_count++;
            continue;
        }
        
        try {
            $file_ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
            
            if ($file_ext === 'csv' || $file_ext === 'tsv') {
                $delimiter = ($file_ext === 'tsv') ? "\t" : ",";
                
                if (($handle = fopen($file, "r")) !== FALSE) {
                    fgetcsv($handle, 1000, $delimiter); // Skip header
                    
                    $row_num = 1;
                    while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
                        $row_num++;
                        
                        if (count($data) < 1) {
                            $error_details[] = "Row {$row_num} in '{$file_name}' has incorrect columns. Expected 6, found " . count($data);
                            $error_count++;
                            continue;
                        }
                        
                        $name = trim($data[0]);
                        $registration_no = trim($data[1]);
                        $test1 = is_numeric($data[2]) ? floatval($data[2]) : 0;
                        $test2 = is_numeric($data[3]) ? floatval($data[3]) : 0;
                        $exam = is_numeric($data[4]) ? floatval($data[4]) : 0;
                        
                        if (empty($name) || empty($registration_no)) {
                            $error_details[] = "Row {$row_num} in '{$file_name}' has missing name or registration number";
                            $error_count++;
                            continue;
                        }
                        
                        try {
                            $check_stmt = $DBH->prepare("SELECT id, test1, test2, exam, total FROM tbl_student_results 
                                                        WHERE registration_no = ? 
                                                        AND class = ? 
                                                        AND subject = ? 
                                                        AND term = ? 
                                                        AND year = ?");
                            $check_stmt->execute([$registration_no, $class, $subject, $term, $session]);
                            $existing_record = $check_stmt->fetch(PDO::FETCH_ASSOC);
                            
                            if ($existing_record) {
                                // Update existing record, but only overwrite empty fields
                                $update_fields = [];
                                $update_values = [];
                                
                                // Always update name and teacher info
                                $update_fields[] = "name = ?";
                                $update_values[] = $name;
                                
                                $update_fields[] = "teacher_no = ?";
                                $update_values[] = $teacher_no;
                                
                                $update_fields[] = "csv_no = ?";
                                $update_values[] = $csv_no;
                                
                                $update_fields[] = "updated_at = NOW()";
                                
                                // Only update test1 if it's empty in the database or if we have a new value
                                if (empty($existing_record['test1'])) {
                                    $update_fields[] = "test1 = ?";
                                    $update_values[] = $test1;
                                } else {
                                    $test1 = $existing_record['test1']; // Use existing value for total calculation
                                }
                                
                                // Only update test2 if it's empty in the database or if we have a new value
                                if (empty($existing_record['test2'])) {
                                    $update_fields[] = "test2 = ?";
                                    $update_values[] = $test2;
                                } else {
                                    $test2 = $existing_record['test2']; // Use existing value for total calculation
                                }
                                
                                // Only update exam if it's empty in the database or if we have a new value
                                if (empty($existing_record['exam'])) {
                                    $update_fields[] = "exam = ?";
                                    $update_values[] = $exam;
                                } else {
                                    $exam = $existing_record['exam']; // Use existing value for total calculation
                                }
                                
                                // Always update total based on current values (existing or new)
                                $total = $test1 + $test2 + $exam;
                                $update_fields[] = "total = ?";
                                $update_values[] = $total;
                                
                                // Add the WHERE clause conditions
                                $update_values[] = $registration_no;
                                $update_values[] = $class;
                                $update_values[] = $subject;
                                $update_values[] = $term;
                                $update_values[] = $session;
                                
                                // Build and execute the update query
                                $update_query = "UPDATE tbl_student_results SET " . implode(", ", $update_fields) . 
                                                " WHERE registration_no = ? AND class = ? AND subject = ? AND term = ? AND year = ?";
                                
                                $stmt = $DBH->prepare($update_query);
                                $stmt->execute($update_values);
                                
                            } else {
                                // Insert new record
                                $total = $test1 + $test2 + $exam;
                                $stmt = $DBH->prepare("INSERT INTO tbl_student_results 
                                                     (name, registration_no, test1, test2, exam, total, 
                                                     class, subject, year, teacher_no, term, csv_no, created_at, updated_at)
                                                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())");
                                $stmt->execute([
                                    $name, $registration_no, $test1, $test2, $exam, $total,
                                    $class, $subject, $session, $teacher_no, $term, $csv_no
                                ]);
                            }
                            $success_count++;
                        } catch (PDOException $e) {
                            $error_details[] = "Database error on row {$row_num}: " . $e->getMessage();
                            $error_count++;
                        }
                    }
                    fclose($handle);
                }
            } else {
                $error_details[] = "Unsupported file type: {$file_ext}";
                $error_count++;
            }
        } catch (Exception $e) {
            $error_details[] = "Error processing file: " . $e->getMessage();
            $error_count++;
        }
    }
    
    if ($success_count > 0) {
        $_SESSION['success_message'] = "Successfully processed {$success_count} records for {$class} ({$term})";
        if ($error_count > 0) {
            $_SESSION['error_message'] = "{$error_count} records had errors";
            $_SESSION['error_details'] = $error_details;
        }
    } else {
        $_SESSION['error_message'] = "No records were processed. Please check your file and try again.";
        $_SESSION['error_details'] = $error_details;
    }
    
    header("Location: ".$_SERVER['PHP_SELF']);
    exit();
}

// Get classes with their assigned teachers for dropdown
try {
    $classes = $DBH->query("
        SELECT 
            c.id, 
            c.class,
            u.hash as teacher_hash,
            u.fullname as teacher_name
        FROM tbl_class c
        INNER JOIN tbl_user u ON (c.class = u.class AND u.teacher = 'true')
        ORDER BY c.class
    ")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Error fetching classes: " . $e->getMessage());
}
?>

<!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>VERBUM DEI INT'L COLLEGE - Upload Results</title>
  <link rel="icon" type="image/png" href="https://portal.vda.com.ng/coll_admin/coll_logo.jpg">
  
  <!-- 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">
  
  <!-- Custom CSS -->
  <style>
    :root {
      --primary-color: #1976d2; /* Primary blue */
      --secondary-color: #64b5f6; /* Light blue */
      --accent-color: #bbdefb; /* Very light blue */
      --dark-color: #0d47a1; /* Dark blue */
      --light-color: #e3f2fd; /* Very light blue background */
    }
    
    body {
      font-family: 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
      background-color: var(--light-color);
      padding-top: 56px;
    }
    
    /* Sidebar Styles */
    .sidebar {
      background: linear-gradient(180deg, var(--dark-color) 0%, var(--primary-color) 100%);
      position: fixed;
      top: 56px;
      left: -250px;
      width: 250px;
      height: calc(100vh - 56px);
      transition: all 0.3s;
      z-index: 1000;
      overflow-y: auto;
    }
    
    .sidebar.active {
      left: 0;
    }
    
    .sidebar .nav-link {
      color: rgba(255, 255, 255, 0.85);
      font-weight: 600;
      padding: 0.75rem 1rem;
      margin-bottom: 0.2rem;
      transition: all 0.2s;
    }
    
    .sidebar .nav-link:hover {
      color: #fff;
      background-color: rgba(255, 255, 255, 0.15);
    }
    
    .sidebar .nav-link.active {
      color: #fff;
      background-color: var(--secondary-color);
      box-shadow: 0 2px 5px rgba(0,0,0,0.1);
    }
    
    .sidebar .nav-link i {
      margin-right: 0.5rem;
      width: 20px;
      text-align: center;
    }
    
    /* Main Content Styles */
    .main-content {
      margin-left: 0;
      width: 100%;
      transition: all 0.3s;
      min-height: calc(100vh - 56px);
    }
    
    @media (min-width: 992px) {
      .sidebar {
        left: 0;
      }
      .main-content {
        margin-left: 250px;
        width: calc(100% - 250px);
      }
      body {
        padding-top: 0;
      }
    }
    
    /* Navbar Styles */
    .navbar {
      background-color: var(--primary-color);
      box-shadow: 0 0.15rem 1.75rem 0 rgba(33, 37, 41, 0.1);
      position: fixed;
      top: 0;
      width: 100%;
      z-index: 1030;
      color: white;
    }
    
    .navbar-brand {
      font-weight: 800;
      font-size: 1.2rem;
      color: white;
    }
    
    /* Card Styles */
    .card {
      border: none;
      border-radius: 0.5rem;
      box-shadow: 0 0.15rem 1.75rem 0 rgba(58, 59, 69, 0.1);
      margin-bottom: 1.5rem;
      transition: transform 0.2s, box-shadow 0.2s;
    }
    
    .card:hover {
      transform: translateY(-2px);
      box-shadow: 0 0.5rem 1.5rem rgba(0, 0, 0, 0.1);
    }
    
    .card-header {
      background-color: var(--accent-color);
      border-bottom: 1px solid var(--secondary-color);
      font-weight: 700;
      color: var(--dark-color);
    }
    
    /* Button Styles */
    .btn-primary {
      background-color: var(--primary-color);
      border-color: var(--primary-color);
    }
    
    .btn-primary:hover {
      background-color: var(--dark-color);
      border-color: var(--dark-color);
    }
    
    .btn-success {
      background-color: var(--secondary-color);
      border-color: var(--secondary-color);
      color: var(--text-dark);
    }
    
    /* Admin Info Styles */
    .admin-info {
      display: flex;
      align-items: center;
      background-color: rgba(255,255,255,0.2);
      padding: 8px 12px;
      border-radius: 0.5rem;
      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: white;
    }
    
    /* Footer styles */
    .footer {
      background-color: var(--dark-color);
      color: white;
      padding: 20px 0;
      margin-top: 30px;
      position: relative;
      z-index: 100;
      box-shadow: 0 -2px 10px rgba(0,0,0,0.1);
    }
    
    .copyright-text {
      font-weight: 600;
      font-size: 1rem;
    }
    
    .powered-by {
      display: flex;
      align-items: center;
      justify-content: flex-end;
      color: white;
    }
    
    .powered-by img {
      height: 30px;
      margin-left: 10px;
    }
    
    .powered-by-link {
      display: flex;
      align-items: center;
      text-decoration: none;
      color: inherit;
      transition: opacity 0.3s ease;
    }
    
    .powered-by-link:hover {
      opacity: 0.8;
      text-decoration: none;
    }
    
    /* Ensure footer stays visible when sidebar is active */
    .main-content.active {
      padding-bottom: 80px;
    }
  </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"></i>
      </button>
      <a class="navbar-brand d-none d-lg-block" href="#">
        VERBUM DEI INT'L COLLEGE
      </a>
      <div class="ms-auto d-flex align-items-center">
        <div class="admin-info me-3">
          <span class="admin-name"><?php echo $_SESSION['fullname']; ?></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>
        </a>
      </div>
    </div>
  </nav>

  <!-- Sidebar -->
  <nav id="sidebar" class="sidebar">
    <div class="position-sticky pt-3">
      <div class="text-center mb-4 d-none d-lg-block">
        <img src="https://portal.vda.com.ng/coll_admin/coll_logo.jpg" alt="School Logo" class="rounded-circle" width="80">
        <h5 class="mt-2 text-white">VERBUM DEI INT'L COLLEGE</h5>
        <p class="text-white-50 small">Admin Dashboard</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 Centre
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="post_session.php">
            <i class="fas fa-fw fa-calendar-alt"></i>
            School Year/Session
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="post_classes.php">
            <i class="fas fa-fw fa-building"></i>
            Create Classes
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="add_students.php">
            <i class="fas fa-fw fa-user-plus"></i>
            Register New Student
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="post_subjects.php">
            <i class="fas fa-fw fa-book"></i>
            Create Subjects
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="teachers.php">
            <i class="fas fa-fw fa-chalkboard-teacher"></i>
            Manage Teachers
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="students.php">
            <i class="fas fa-fw fa-users"></i>
            Manage Students
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="deactivate_students.php">
            <i class="fas fa-fw fa-user-lock"></i>
            Activate/Deactivate
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link active" href="upload_results.php">
            <i class="fas fa-fw fa-upload"></i>
            Upload Results
          </a>
        </li>
      </ul>
    </div>
  </nav>

<!-- Main Content -->
<main class="main-content">
    <div class="container-fluid px-4 pt-4">
        <!-- Page Heading -->
        <div class="d-sm-flex align-items-center justify-content-between mb-4">
            <h1 class="h3 mb-0 text-gray-800">Upload Student Results</h1>
            <div class="d-none d-sm-inline-block">
                <span class="badge bg-light text-dark p-2">
                    <i class="fas fa-phone-alt me-1"></i> Support: MENDOH - 08032929756
                </span>
            </div>
        </div>

        <!-- Messages -->
        <?php if (isset($_SESSION['success_message'])): ?>
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                <?php echo $_SESSION['success_message']; ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
            <?php unset($_SESSION['success_message']); ?>
        <?php endif; ?>
        
        <?php if (isset($_SESSION['error_message'])): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <?php echo $_SESSION['error_message']; ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
            <?php unset($_SESSION['error_message']); ?>
        <?php endif; ?>
        
        <?php if (isset($_SESSION['error_details']) && !empty($_SESSION['error_details'])): ?>
            <div class="alert alert-warning alert-dismissible fade show" role="alert">
                <h5 class="alert-heading">Detailed Errors:</h5>
                <ul class="mb-0">
                    <?php foreach ($_SESSION['error_details'] as $error): ?>
                        <li><?php echo htmlspecialchars($error); ?></li>
                    <?php endforeach; ?>
                </ul>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
            <?php unset($_SESSION['error_details']); ?>
        <?php endif; ?>

        <!-- Upload Form -->
        <div class="row">
            <div class="col-lg-12">
                <div class="card shadow mb-4">
                    <div class="card-header py-3">
                        <h6 class="m-0 font-weight-bold text-primary">Upload Results</h6>
                    </div>
                    <div class="card-body">
                        <form id="uploadForm" method="post" enctype="multipart/form-data">
                            <div class="row g-3">
                                <div class="col-md-4">
                                    <label for="classSelect" class="form-label">Class</label>
                                      <select name="class" class="form-select" id="classSelect" required>
                                          <option value="">Select Class</option>
                                          <?php foreach($classes as $class): ?>
                                              <option value="<?php echo $class['id']; ?>">
                                                  <?php echo $class['class']; ?>
                                                  <?php if (!empty($class['teacher_name'])): ?>
                                                      - Teacher (<?php echo $class['teacher_name']; ?>)
                                                  <?php else: ?>
                                                      - (No teacher assigned)
                                                  <?php endif; ?>
                                              </option>
                                          <?php endforeach; ?>
                                      </select>
                                    <?php if (empty($classes)): ?>
                                        <div class="text-danger small mt-1">No classes found. Please create classes first.</div>
                                    <?php endif; ?>
                                </div>
                                
                                <div class="col-md-4">
                                    <label for="termSelect" class="form-label">Term</label>
                                    <select name="term" class="form-select" id="termSelect" required>
                                        <option value="">Select Term</option>
                                        <option value="1st Term">1st Term</option>
                                        <option value="2nd Term">2nd Term</option>
                                        <option value="3rd Term">3rd Term</option>
                                    </select>
                                </div>
                                
                                <div class="col-md-4">
                                    <label for="sessionSelect" class="form-label">Session</label>
                                    <select name="session" class="form-select" id="sessionSelect" required>
                                        <option value="">Select Session</option>
                                        <?php
                                        $sessions = sql($DBH, "SELECT * FROM tbl_session ORDER BY session", [], "rows");
                                        foreach($sessions as $session) {
                                            echo "<option value='{$session['id']}'>{$session['session']}</option>";
                                        }
                                        ?>
                                    </select>
                                </div>
                            </div>
                            
                            <div id="subjectsContainer" class="mt-4">
                                <!-- Subject rows will be added here dynamically -->
                            </div>
                            
                            <div class="mt-3">
                                <button type="button" id="addSubjectBtn" class="btn btn-outline-primary">
                                    <i class="fas fa-plus me-1"></i> Add Subject
                                </button>
                            </div>
                            
                            <div class="mt-4">
                                <button type="submit" name="upload_results" class="btn btn-primary">
                                    <i class="fas fa-upload me-1"></i> Upload All Subjects
                                </button>
                                <a href="main.php" class="btn btn-outline-secondary ms-2">
                                    <i class="fas fa-arrow-left me-1"></i> Back to Dashboard
                                </a>
                            </div>
                        </form>
            </div>
          </div>
        </div>
      </div>

      <!-- Download Sample Section -->
      <div class="row">
        <div class="col-lg-12">
          <div class="card shadow mb-4">
            <div class="card-header py-3">
              <h6 class="m-0 font-weight-bold text-primary">Upload Skills, Attendance, etc. | Download Sample Files <HR>
              <a href="post_attendance.php" class="btn btn-outline-success w-33">
                    <i class="fas fa-file-csv me-1"></i> UPLOAD ATTENDANCE & TEACHER'S COMMENT
                  </a></h6><HR>
            <br>
            <a href="post_skills.php" class="btn btn-outline-success w-33">
                    <i class="fas fa-file-csv me-1"></i> UPLOAD PSYCHOMOTOR & OTHER SKILLS
                  </a></h6>
            </div>
            <div class="card-body">
              <div class="row g-3">
                <div class="col-md-4">
                  <a href="https://bkms.com.ng/portal/coll_admin/SAMPLE_CA1_CA2_EXAM.csv" class="btn btn-outline-success w-100">
                    <i class="fas fa-file-csv me-1"></i> Download CSV Sample
                  </a>
                </div>
                <div class="col-md-4">
                  <a href="https://bkms.com.ng/portal/coll_admin/SAMPLE_PSYCHOMOTOR_AND_OTHER_SKILLS.csv" class="btn btn-outline-primary w-100">
                    <i class="fas fa-file-csv me-1"></i></i> Download Sample Skills Entry Sheet
                  </a>
                </div>
                <div class="col-md-4">
                  <a href="https://bkms.com.ng/portal/coll_admin/SAMPLE_ATTENDANCE_SHEET.csv" class="btn btn-outline-danger w-100">
                    <i class="fas fa-file-csv me-1"></i> Download Sample Attendance Sheet
                  </a>
                </div>
              </div>
              <div class="mt-3">
                <p class="text-muted small">
                  <strong>Note:</strong> Your file should contain the following columns in order: 
                  <strong>Name, Admission No, Test1, Test2, Exam</strong>. The system will automatically calculate the total.
                </p>
              </div>
            </div>
          </div>
        </div>
      </div>

      <!-- Uploaded Result Section -->
      <div class="card shadow mb-4">
        <div class="card-header py-6" style="background-color: var(--accent-color);">
          <h6 class="m-0 font-weight-bold text-primary">Uploaded Results by Session</h6>
        </div>
        <div class="card-body" style="background-color: var(--light-color);">
          <div class="row">
            <?php
            $STH = $DBH->prepare("SELECT count(*) FROM tbl_student_results WHERE id >= ?");
            $result = $STH->execute(array(1));
            $count = $STH->fetchColumn();
            
            if($count >= 1) {
              $rows = sql($DBH, "SELECT MIN(id) AS id, year FROM tbl_student_results WHERE id >= ? GROUP BY year", 
              array(1), "rows");
              foreach($rows as $row) {  
                $t_year = $row['year'];
                echo '
                <div class="col-md-3 mb-3">
                  <a href="main_term.php?id='.$t_year.'" target="_blank" class="btn btn-outline-secondary w-100">
                    <i class="fas fa-calendar-alt me-1"></i> '.$t_year.'
                  </a>
                </div>';
              }
            } else {
              echo '<div class="col-12 text-center py-4">
                <i class="fas fa-info-circle fa-2x mb-3 text-muted"></i>
                <p class="text-muted">No academic records found yet</p>
              </div>';
            }
            ?>
          </div>
        </div>
      </div>

    </div>
  </main>

    <!-- Footer -->
    <footer class="footer">
        <div class="container-fluid">
            <div class="row align-items-center">
                <div class="col-md-6">
                    <span class="copyright-text">Copyright &copy; VERBUM DEI INT'L COLLEGE <?php echo date("Y"); ?></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</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>
  
  <!-- jQuery -->
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  
<!-- JavaScript -->
<script>
// Toggle sidebar on mobile
document.getElementById('sidebarToggle').addEventListener('click', function() {
    document.getElementById('sidebar').classList.toggle('active');
    document.querySelector('.main-content').classList.toggle('active');
});

// Add subject row
let subjectIndex = 0;
$('#addSubjectBtn').click(function() {
    const classSelected = $('#classSelect').val();
    const termSelected = $('#termSelect').val();
    const sessionSelected = $('#sessionSelect').val();
    
    if (!classSelected || !termSelected || !sessionSelected) {
        alert('Please select Class, Term and Session first');
        return;
    }
    
    const subjectRow = `
        <div class="subject-row" id="subjectRow${subjectIndex}">
            <div class="row g-3">
                <div class="col-md-6">
                    <label class="form-label">Subject</label>
                    <select name="subjects[${subjectIndex}][subject_id]" class="form-select" required>
                        <option value="">Select Subject</option>
                        <?php
                        $subjects = sql($DBH, "SELECT * FROM tbl_subject ORDER BY subject", [], "rows");
                        foreach($subjects as $subject) {
                            echo "<option value='{$subject['id']}'>{$subject['subject']}</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-5">
                    <label class="form-label">Result File</label>
                    <input type="file" name="subjects[${subjectIndex}][file]" class="form-control" accept=".csv,.xlsx,.xls,.tsv" required>
                </div>
                <div class="col-md-1 d-flex align-items-end">
                    <button type="button" class="btn btn-danger btn-sm" onclick="removeSubjectRow(${subjectIndex})">
                        <i class="fas fa-trash"></i>
                    </button>
                </div>
            </div>
        </div>
    `;
    
    $('#subjectsContainer').append(subjectRow);
    subjectIndex++;
});

// Remove subject row
function removeSubjectRow(index) {
    $(`#subjectRow${index}`).remove();
}

// Form validation
$('#uploadForm').submit(function() {
    if (subjectIndex === 0) {
        alert('Please add at least one subject to upload');
        return false;
    }
    return true;
});
</script>
</body>
</html>