<?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";

// Get distinct years from tbl_pin for dropdown - multiple sources
$years = [];

// First, check if there's an academic_year column in tbl_pin
try {
    $stmt = $DBH->query("SHOW COLUMNS FROM tbl_pin LIKE 'academic_year'");
    $hasAcademicYear = $stmt->rowCount() > 0;
    
    if ($hasAcademicYear) {
        // Get years from academic_year column
        $academicYears = sql($DBH, "SELECT DISTINCT academic_year as year FROM tbl_pin WHERE academic_year IS NOT NULL AND academic_year != '' ORDER BY academic_year DESC", [], "rows");
        foreach ($academicYears as $year) {
            if ($year['year']) {
                $years[] = $year['year'];
            }
        }
    }
} catch (Exception $e) {
    error_log("Error checking academic_year column: " . $e->getMessage());
}

// Always get years from date_time column as fallback
try {
    $dateYears = sql($DBH, "SELECT DISTINCT YEAR(date_time) as year FROM tbl_pin WHERE date_time IS NOT NULL ORDER BY year DESC", [], "rows");
    foreach ($dateYears as $year) {
        if ($year['year']) {
            $yearStr = (string)$year['year'];
            if (!in_array($yearStr, $years)) {
                $years[] = $yearStr;
            }
        }
    }
} catch (Exception $e) {
    error_log("Error getting years from date_time: " . $e->getMessage());
}

// Also check for any other year-related columns
$otherYearColumns = ['year', 'session_year', 'session'];
foreach ($otherYearColumns as $column) {
    try {
        $stmt = $DBH->query("SHOW COLUMNS FROM tbl_pin LIKE '$column'");
        if ($stmt->rowCount() > 0) {
            $colYears = sql($DBH, "SELECT DISTINCT $column as year FROM tbl_pin WHERE $column IS NOT NULL AND $column != '' ORDER BY $column DESC", [], "rows");
            foreach ($colYears as $year) {
                if ($year['year']) {
                    $yearStr = (string)$year['year'];
                    if (!in_array($yearStr, $years)) {
                        $years[] = $yearStr;
                    }
                }
            }
        }
    } catch (Exception $e) {
        // Skip if column doesn't exist or error
        continue;
    }
}

// Sort years in descending order and ensure uniqueness
usort($years, function($a, $b) {
    return $b - $a;
});
$years = array_unique($years);

// Initialize variables
$reg_no = $_POST['reg_no'] ?? '';
$year = $_POST['year'] ?? '';
$searchResults = [];
$noResults = false;

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['search'])) {
    $reg_no = trim($_POST['reg_no']);
    $year = trim($_POST['year']);
    
    if (empty($reg_no)) {
        $_SESSION['error'] = "Please enter an admission number";
        header("Location: find_student_pin.php");
        exit();
    }
    
    // Build the query based on inputs
    $queryParams = [];
    $query = "SELECT p.*, u.name, u.class 
              FROM tbl_pin p 
              LEFT JOIN tbl_user u ON p.pin_user = u.reg_no 
              WHERE p.pin_user = ?";
    $queryParams[] = $reg_no;
    
    if (!empty($year)) {
        // Try to match year from multiple possible columns
        $yearQuery = " AND (";
        $yearConditions = [];
        
        // Check date_time year
        $yearConditions[] = "YEAR(p.date_time) = ?";
        $yearParams[] = $year;
        
        // Check if academic_year column exists
        try {
            $stmt = $DBH->query("SHOW COLUMNS FROM tbl_pin LIKE 'academic_year'");
            if ($stmt->rowCount() > 0) {
                $yearConditions[] = "p.academic_year = ?";
                $yearParams[] = $year;
            }
        } catch (Exception $e) {
            // Column doesn't exist or error
        }
        
        // Check other possible year columns
        $otherYearColumns = ['year', 'session_year', 'session'];
        foreach ($otherYearColumns as $column) {
            try {
                $stmt = $DBH->query("SHOW COLUMNS FROM tbl_pin LIKE '$column'");
                if ($stmt->rowCount() > 0) {
                    $yearConditions[] = "p.$column = ?";
                    $yearParams[] = $year;
                }
            } catch (Exception $e) {
                continue;
            }
        }
        
        if (!empty($yearConditions)) {
            $yearQuery .= implode(" OR ", $yearConditions) . ")";
            $query .= $yearQuery;
            // Add all year parameters
            foreach ($yearParams as $yearParam) {
                $queryParams[] = $yearParam;
            }
        }
    }
    
    $query .= " ORDER BY p.date_time DESC";
    
    $searchResults = sql($DBH, $query, $queryParams, "rows");
    
    if (empty($searchResults)) {
        $noResults = true;
        $_SESSION['warning'] = "No PIN found for admission number '$reg_no'" . (!empty($year) ? " in academic year $year" : "");
    }
}

// Handle messages from session
$error_message = $_SESSION['error'] ?? '';
$success_message = $_SESSION['success'] ?? '';
$warning_message = $_SESSION['warning'] ?? '';

// Clear messages from session
unset($_SESSION['error'], $_SESSION['success'], $_SESSION['warning']);
?>

<!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>Find Student PIN - 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);
    }
    
    /* 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);
    }
    
    .results-table {
      width: 100%;
      border-collapse: collapse;
      font-size: 0.85rem;
    }
    
    .results-table th,
    .results-table td {
      padding: 0.75rem;
      border: 1px solid #dee2e6;
      text-align: left;
      vertical-align: middle;
    }
    
    .results-table th {
      background: linear-gradient(135deg, var(--primary-blue), var(--dark-blue));
      color: white;
      font-weight: 600;
      position: sticky;
      top: 0;
      z-index: 10;
    }
    
    .results-table th i {
      color: var(--secondary-gold);
    }
    
    .results-table tr:nth-child(even) {
      background-color: #f9f9f9;
    }
    
    .results-table tr:hover {
      background-color: var(--light-gold);
    }
    
    /* PIN display */
    .pin-display {
      font-family: 'Courier New', monospace;
      font-size: 1.1rem;
      font-weight: 700;
      letter-spacing: 2px;
      color: var(--dark-blue);
      background: linear-gradient(135deg, #f5f5f5, #ffffff);
      padding: 0.5rem 0.75rem;
      border-radius: 0.375rem;
      border: 1px solid var(--secondary-gold);
      display: inline-block;
      margin: 0.25rem 0;
      box-shadow: 0 2px 4px rgba(0,0,0,0.1);
    }
    
    /* Status badges */
    .badge-status {
      padding: 0.35rem 0.75rem;
      border-radius: 2rem;
      font-size: 0.75rem;
      font-weight: 600;
      text-transform: uppercase;
      letter-spacing: 0.5px;
      display: inline-block;
    }
    
    .badge-used {
      background: linear-gradient(135deg, #28a745, #218838);
      color: white;
    }
    
    .badge-unused {
      background: linear-gradient(135deg, #6c757d, #5a6268);
      color: white;
    }
    
    /* 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);
    }
    
    /* 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);
    }
    
    @media (min-width: 992px) {
      .footer {
        margin-left: 250px;
        width: calc(100% - 250px);
      }
    }
    
    @media (max-width: 991.98px) {
      .footer {
        margin-left: 0;
        width: 100%;
      }
    }
    
    .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;
      margin: 1rem 1rem 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-action {
      padding: 0.5rem 1rem;
      border-radius: 0.25rem;
      font-weight: 600;
      transition: all 0.3s;
      font-size: 0.85rem;
    }
    
    .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-success {
      background: linear-gradient(135deg, #28a745, #218838);
      border: none;
      color: white;
    }
    
    .btn-success:hover {
      background: linear-gradient(135deg, #218838, #1e7e34);
    }
    
    .btn-warning {
      background: linear-gradient(135deg, #ffc107, #e0a800);
      border: none;
      color: var(--dark-blue);
    }
    
    .btn-warning:hover {
      background: linear-gradient(135deg, #e0a800, #c69500);
      color: var(--dark-blue);
    }
    
    .btn-info {
      background: linear-gradient(135deg, #17a2b8, #138496);
      border: none;
      color: white;
    }
    
    .btn-info:hover {
      background: linear-gradient(135deg, #138496, #117a8b);
    }
    
    .btn-outline-primary {
      border-color: var(--secondary-gold);
      color: var(--dark-blue);
    }
    
    .btn-outline-primary:hover {
      background-color: var(--secondary-gold);
      border-color: var(--dark-blue);
      color: var(--dark-blue);
    }
    
    .btn-outline-success {
      border-color: #28a745;
      color: #28a745;
    }
    
    .btn-outline-success:hover {
      background: linear-gradient(135deg, #28a745, #218838);
      color: white;
    }
    
    .btn-group-sm .btn {
      padding: 0.25rem 0.5rem;
      font-size: 0.8rem;
    }
    
    /* Alert styles */
    .alert {
      padding: 0.75rem 1rem;
      border-radius: 0.25rem;
      margin: 1rem;
    }
    
    .alert-success {
      background-color: #d4edda;
      border-color: #c3e6cb;
      color: #155724;
    }
    
    .alert-danger {
      background-color: #f8d7da;
      border-color: #f5c6cb;
      color: #721c24;
    }
    
    .alert-warning {
      background-color: #fff3cd;
      border-color: #ffeaa7;
      color: #856404;
    }
    
    .alert-info {
      background-color: #d1ecf1;
      border-color: #bee5eb;
      color: #0c5460;
    }
    
    /* Info cards */
    .info-card {
      background: linear-gradient(135deg, var(--light-gold) 0%, #ffffff 100%);
      border-left: 4px solid var(--secondary-gold);
      padding: 0.75rem;
      margin-bottom: 0.5rem;
      border-radius: 0 0.25rem 0.25rem 0;
      color: var(--dark-blue);
    }
    
    .info-card i {
      color: var(--primary-blue);
    }
    
    /* Student info box */
    .student-info-box {
      background: linear-gradient(135deg, var(--light-gold) 0%, #ffffff 100%);
      border-left: 4px solid var(--secondary-gold);
      padding: 1rem;
      border-radius: 0 0.5rem 0.5rem 0;
      margin-bottom: 1rem;
      border: 1px solid rgba(255, 215, 0, 0.3);
    }
    
    /* Year badge */
    .year-badge {
      background: linear-gradient(135deg, var(--light-blue), var(--primary-blue));
      color: white;
      padding: 0.25rem 0.75rem;
      border-radius: 2rem;
      font-size: 0.75rem;
      font-weight: 600;
      display: inline-block;
    }
    
    /* Form elements */
    .form-select, .form-label, .form-control {
      font-size: 0.85rem;
    }
    
    .form-control:focus, .form-select:focus {
      border-color: var(--secondary-gold);
      box-shadow: 0 0 0 0.25rem rgba(255, 215, 0, 0.25);
    }
    
    .form-label i {
      color: var(--secondary-gold);
    }
    
    /* Empty state */
    .empty-state {
      text-align: center;
      padding: 3rem;
      color: #6c757d;
    }
    
    .empty-state i {
      font-size: 3rem;
      margin-bottom: 1rem;
      color: var(--primary-blue);
    }
    
    .empty-state h5 {
      color: var(--dark-blue);
    }
    
    /* Code style */
    code {
      background-color: var(--light-gold);
      color: var(--dark-blue);
      padding: 0.2rem 0.4rem;
      border-radius: 0.25rem;
      font-weight: 600;
    }
    
    /* Responsive */
    @media (max-width: 768px) {
      .row {
        flex-direction: column;
      }
      
      .btn-primary {
        width: 100%;
        margin-top: 0.5rem;
      }
      
      .badge-status {
        font-size: 0.7rem;
        padding: 0.25rem 0.5rem;
      }
      
      .pin-display {
        font-size: 1rem;
        padding: 0.35rem 0.5rem;
      }
    }
  </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" href="edit_scores_subject.php">
            <i class="fas fa-fw fa-edit"></i>
            Edit Subject Scores
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" 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="list_uploaded_results.php">
            <i class="fas fa-fw fa-list"></i>
            Uploaded Results
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="edit_comments_attendance.php">
            <i class="fas fa-fw fa-comments"></i>
            Attendance & Comments
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="edit_skills.php">
            <i class="fas fa-fw fa-chart-bar"></i>
            Psychomotor Skills
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link active" href="find_student_pin.php">
            <i class="fas fa-fw fa-search"></i>
            Find Student's PIN
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="pin_user.php">
            <i class="fas fa-fw fa-check-circle"></i>
            Confirm PIN Usage
          </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="edit_pin.php">
            <i class="fas fa-fw fa-edit"></i>
            Edit PIN
          </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>Find Student PIN</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-key me-1"></i> PIN Management
          </span>
        </div>
      </div>
    </div>

    <!-- Breadcrumbs -->
    <div class="breadcrumb-custom">
      <nav aria-label="breadcrumb">
        <ol class="breadcrumb">
          <li class="breadcrumb-item"><a href="main.php">Admin Dashboard</a></li>
          <li class="breadcrumb-item active">Find Student PIN</li>
        </ol>
      </nav>
    </div>

    <?php if ($error_message): ?>
      <div class="alert alert-danger">
        <i class="fas fa-exclamation-circle me-2"></i> <?php echo htmlspecialchars($error_message); ?>
      </div>
    <?php endif; ?>
    
    <?php if ($success_message): ?>
      <div class="alert alert-success">
        <i class="fas fa-check-circle me-2"></i> <?php echo htmlspecialchars($success_message); ?>
      </div>
    <?php endif; ?>
    
    <?php if ($warning_message): ?>
      <div class="alert alert-warning">
        <i class="fas fa-exclamation-triangle me-2"></i> <?php echo htmlspecialchars($warning_message); ?>
      </div>
    <?php endif; ?>

    <!-- Search Form -->
    <div class="form-container">
      <form method="POST" action="">
        <div class="row align-items-end">
          <div class="col-md-5">
            <div class="form-group mb-2">
              <label for="reg_no" class="form-label"><i class="fas fa-id-card me-1"></i>Admission Number:</label>
              <input type="text" class="form-control form-control-sm" id="reg_no" name="reg_no" 
                     value="<?php echo htmlspecialchars($reg_no); ?>" 
                     placeholder="Enter student admission number" required>
            </div>
          </div>
          
          <div class="col-md-4">
            <div class="form-group mb-2">
              <label for="year" class="form-label"><i class="fas fa-calendar me-1"></i>Academic Year (Optional):</label>
              <select id="year" name="year" class="form-select form-select-sm">
                <option value="">All Academic Years</option>
                <?php if (!empty($years)): ?>
                  <?php foreach ($years as $yearOption): ?>
                    <option value="<?php echo htmlspecialchars($yearOption); ?>" 
                      <?php echo ($yearOption == $year) ? 'selected' : ''; ?>>
                      <?php echo htmlspecialchars($yearOption); ?>
                    </option>
                  <?php endforeach; ?>
                <?php else: ?>
                  <option value="">No years found in database</option>
                <?php endif; ?>
              </select>
            </div>
          </div>
          
          <div class="col-md-3">
            <button type="submit" name="search" class="btn btn-primary btn-sm w-100">
              <i class="fas fa-search me-1"></i> Search PIN
            </button>
          </div>
        </div>
        
        <?php if (!empty($years)): ?>
          <div class="mt-3">
            <div class="alert alert-info py-2">
              <h6 class="mb-1"><i class="fas fa-calendar-alt me-1" style="color: var(--secondary-gold);"></i> Available Academic Years</h6>
              <div class="d-flex flex-wrap gap-1 mt-1">
                <?php foreach ($years as $yearOption): ?>
                  <span class="year-badge"><?php echo htmlspecialchars($yearOption); ?></span>
                <?php endforeach; ?>
              </div>
              <p class="mb-0 small mt-2">Total: <?php echo count($years); ?> academic years found in PIN database</p>
            </div>
          </div>
        <?php endif; ?>
        
        <div class="mt-2">
          <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> Search Instructions</h6>
            <div class="row small">
              <div class="col-md-6">
                <i class="fas fa-check-circle text-success me-1"></i> Enter the student's admission number<br>
                <i class="fas fa-check-circle text-success me-1"></i> Optionally select an academic year to filter
              </div>
              <div class="col-md-6">
                <i class="fas fa-check-circle text-success me-1"></i> Click "Search PIN" to find all associated PINs<br>
                <i class="fas fa-check-circle text-success me-1"></i> Results show PIN details and usage status
              </div>
            </div>
          </div>
        </div>
      </form>
    </div>

    <?php if (!empty($searchResults)): ?>
      <!-- Student Information -->
      <?php 
      // Get student info from first result
      $firstResult = $searchResults[0];
      $studentName = $firstResult['name'] ?? 'Unknown';
      $studentClass = $firstResult['class'] ?? 'Not Assigned';
      $totalPins = count($searchResults);
      
      // Determine academic year for each result
      foreach ($searchResults as &$result) {
        $academicYear = '';
        
        // Check academic_year column first
        if (!empty($result['academic_year'])) {
          $academicYear = $result['academic_year'];
        }
        // Check other year columns
        elseif (!empty($result['year'])) {
          $academicYear = $result['year'];
        }
        elseif (!empty($result['session_year'])) {
          $academicYear = $result['session_year'];
        }
        elseif (!empty($result['session'])) {
          $academicYear = $result['session'];
        }
        // Fallback to date_time year
        elseif (!empty($result['date_time'])) {
          $academicYear = date('Y', strtotime($result['date_time']));
        }
        
        $result['academic_year_display'] = $academicYear;
      }
      unset($result); // Break the reference
      ?>
      <div class="form-container">
        <div class="student-info-box">
          <div class="row">
            <div class="col-md-6">
              <h6 class="mb-1">
                <i class="fas fa-user-graduate me-2" style="color: var(--secondary-gold);"></i>Student Information
              </h6>
              <p class="mb-1"><strong>Name:</strong> <?php echo htmlspecialchars($studentName); ?></p>
              <p class="mb-1"><strong>Admission No:</strong> <?php echo htmlspecialchars($reg_no); ?></p>
              <p class="mb-0"><strong>Class:</strong> <?php echo htmlspecialchars($studentClass); ?></p>
            </div>
            <div class="col-md-6">
              <h6 class="mb-1">
                <i class="fas fa-key me-2" style="color: var(--secondary-gold);"></i>PIN Summary
              </h6>
              <p class="mb-1"><strong>Total PINs Found:</strong> <?php echo $totalPins; ?></p>
              <p class="mb-1"><strong>Academic Year:</strong> <?php echo !empty($year) ? $year : 'All Years'; ?></p>
              <p class="mb-0">
                <strong>Last PIN Used:</strong> 
                <?php 
                $lastUsed = sql($DBH, 
                  "SELECT date_time FROM tbl_pin WHERE pin_user = ? ORDER BY date_time DESC LIMIT 1", 
                  [$reg_no], 
                  "fetchColumn"
                );
                echo $lastUsed ? date('M j, Y h:i A', strtotime($lastUsed)) : 'Never';
                ?>
              </p>
            </div>
          </div>
        </div>
      </div>

      <!-- Results Table -->
      <div class="table-container">
        <div class="d-flex justify-content-between align-items-center mb-3">
          <h5 class="mb-0">
            <i class="fas fa-list me-2" style="color: var(--secondary-gold);"></i>Student PIN Records
            <small class="text-muted">(<?php echo $totalPins; ?> records found)</small>
          </h5>
          <div class="btn-group" role="group">
            <button type="button" class="btn btn-outline-primary btn-sm" onclick="window.print()">
              <i class="fas fa-print me-1"></i> Print
            </button>
            <button type="button" class="btn btn-outline-success btn-sm" onclick="exportToCSV()">
              <i class="fas fa-download me-1"></i> Export
            </button>
          </div>
        </div>
        
        <div class="table-responsive">
          <table class="results-table">
            <thead>
              <tr>
                <th width="5%"><i class="fas fa-hashtag me-1"></i>S/N</th>
                <th><i class="fas fa-key me-1"></i>PIN Code</th>
                <th><i class="fas fa-barcode me-1"></i>Serial No</th>
                <th><i class="fas fa-layer-group me-1"></i>Batch ID</th>
                <th><i class="fas fa-calendar me-1"></i>Academic Year</th>
                <th><i class="fas fa-circle me-1"></i>Status</th>
                <th><i class="fas fa-clock me-1"></i>Used Date</th>
                <th width="12%"><i class="fas fa-cog me-1"></i>Actions</th>
              </tr>
            </thead>
            <tbody>
              <?php foreach ($searchResults as $index => $result): ?>
                <?php
                $pinUsed = !empty($result['pin_user']);
                $statusClass = $pinUsed ? 'badge-used' : 'badge-unused';
                $statusText = $pinUsed ? 'Used' : 'Unused';
                $usedDate = $result['date_time'] ? date('M j, Y h:i A', strtotime($result['date_time'])) : 'Not Used';
                $academicYear = $result['academic_year_display'] ?? '-';
                ?>
                <tr>
                  <td><?php echo $index + 1; ?></td>
                  <td>
                    <span class="pin-display"><?php echo htmlspecialchars($result['pin']); ?></span>
                  </td>
                  <td><?php echo isset($result['Serial']) ? str_pad($result['Serial'], 5, "0", STR_PAD_LEFT) : '-'; ?></td>
                  <td><?php echo htmlspecialchars($result['batch_id'] ?? 'No Batch'); ?></td>
                  <td>
                    <?php if ($academicYear): ?>
                      <span class="year-badge"><?php echo htmlspecialchars($academicYear); ?></span>
                    <?php else: ?>
                      -
                    <?php endif; ?>
                  </td>
                  <td>
                    <span class="badge-status <?php echo $statusClass; ?>">
                      <?php echo $statusText; ?>
                    </span>
                  </td>
                  <td><?php echo $usedDate; ?></td>
                  <td>
                    <div class="btn-group btn-group-sm" role="group">
                      <?php if ($pinUsed): ?>
                        <button type="button" class="btn btn-info" 
                                onclick="showPinDetails('<?php echo addslashes($result['pin']); ?>', '<?php echo addslashes($studentName); ?>', '<?php echo addslashes($academicYear); ?>', '<?php echo addslashes($usedDate); ?>')"
                                title="View Details">
                          <i class="fas fa-eye"></i>
                        </button>
                      <?php endif; ?>
                      <a href="edit_pin.php?pin=<?php echo urlencode($result['pin']); ?>" 
                         class="btn btn-warning" title="Edit PIN">
                        <i class="fas fa-edit"></i>
                      </a>
                    </div>
                  </td>
                </tr>
              <?php endforeach; ?>
            </tbody>
          </table>
        </div>
        
        <div class="mt-3">
          <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> PIN Status Information</h6>
            <div class="row small">
              <div class="col-md-3">
                <span class="badge-used badge-status me-1"></span> Used: Student has activated this PIN
              </div>
              <div class="col-md-3">
                <span class="badge-unused badge-status me-1"></span> Unused: PIN has not been activated
              </div>
              <div class="col-md-3">
                <span class="year-badge me-1"></span> Academic Year: Session when PIN was issued
              </div>
              <div class="col-md-3">
                <i class="fas fa-edit text-warning me-1"></i> Edit: Modify PIN details if needed
              </div>
            </div>
          </div>
        </div>
      </div>
      
    <?php elseif ($noResults): ?>
      <div class="form-container empty-state">
        <i class="fas fa-search"></i>
        <h5 class="text-muted">No PINs Found</h5>
        <p class="mb-0">No PIN records found for admission number "<?php echo htmlspecialchars($reg_no); ?>"<?php echo !empty($year) ? " in academic year $year" : ""; ?>.</p>
        <div class="mt-3">
          <button class="btn btn-primary btn-sm" onclick="history.back()">
            <i class="fas fa-arrow-left me-1"></i> Try Another Search
          </button>
        </div>
      </div>
    <?php elseif ($_SERVER['REQUEST_METHOD'] == 'POST'): ?>
      <div class="form-container empty-state">
        <i class="fas fa-search"></i>
        <h5 class="text-muted">Search for Student PIN</h5>
        <p class="mb-0">Enter an admission number above to search for student PIN records.</p>
      </div>
    <?php else: ?>
      <div class="form-container empty-state">
        <i class="fas fa-key"></i>
        <h5 class="text-muted">Find Student PIN</h5>
        <p class="mb-0">Enter a student's admission number to search for their PIN records.</p>
      </div>
    <?php endif; ?>
  </main>

  <!-- Footer -->
  <footer class="footer">
    <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');
      document.querySelector('.footer').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');
        document.querySelector('.footer').classList.remove('active');
      }
    });
    
    // Show PIN details modal
    function showPinDetails(pin, studentName, academicYear, usedDate) {
      const modalHtml = `
        <div class="modal fade" id="pinDetailsModal" tabindex="-1">
          <div class="modal-dialog">
            <div class="modal-content">
              <div class="modal-header" style="background: linear-gradient(135deg, var(--primary-blue), var(--dark-blue)); color: white;">
                <h5 class="modal-title"><i class="fas fa-key me-2" style="color: var(--secondary-gold);"></i>PIN Details</h5>
                <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
              </div>
              <div class="modal-body">
                <div class="mb-3">
                  <label class="form-label fw-bold">PIN Code:</label>
                  <div class="pin-display w-100 text-center" style="font-size: 1.5rem;">${pin}</div>
                </div>
                <div class="mb-3">
                  <label class="form-label fw-bold">Student:</label>
                  <input type="text" class="form-control" value="${studentName}" readonly>
                </div>
                <div class="mb-3">
                  <label class="form-label fw-bold">Academic Year:</label>
                  <input type="text" class="form-control" value="${academicYear}" readonly>
                </div>
                <div class="mb-3">
                  <label class="form-label fw-bold">Used Date:</label>
                  <input type="text" class="form-control" value="${usedDate}" readonly>
                </div>
              </div>
              <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
              </div>
            </div>
          </div>
        </div>
      `;
      
      // Remove existing modal if any
      const existingModal = document.getElementById('pinDetailsModal');
      if (existingModal) {
        existingModal.remove();
      }
      
      // Add modal to body and show it
      document.body.insertAdjacentHTML('beforeend', modalHtml);
      const modal = new bootstrap.Modal(document.getElementById('pinDetailsModal'));
      modal.show();
    }
    
    // Export to CSV function
    function exportToCSV() {
      const table = document.querySelector('.results-table');
      let csv = [];
      
      // Get headers
      const headers = [];
      table.querySelectorAll('thead th').forEach(header => {
        if (header.textContent !== 'Actions') {
          headers.push(header.textContent.replace(/[^\w\s]/gi, '').trim());
        }
      });
      csv.push(headers.join(','));
      
      // Get data rows (skip action column)
      table.querySelectorAll('tbody tr').forEach(row => {
        const rowData = [];
        row.querySelectorAll('td').forEach((cell, index) => {
          // Skip the last column (Actions)
          if (index < row.cells.length - 1) {
            let text = cell.textContent.trim();
            // Handle PIN code
            const pinDisplay = cell.querySelector('.pin-display');
            if (pinDisplay) {
              text = pinDisplay.textContent.trim();
            }
            // Handle badge status
            const badge = cell.querySelector('.badge-status');
            if (badge) {
              text = badge.textContent.trim();
            }
            // Handle year badge
            const yearBadge = cell.querySelector('.year-badge');
            if (yearBadge) {
              text = yearBadge.textContent.trim();
            }
            // Handle CSV formatting
            if (text.includes(',') || text.includes('"') || text.includes('\n')) {
              text = '"' + text.replace(/"/g, '""') + '"';
            }
            rowData.push(text);
          }
        });
        csv.push(rowData.join(','));
      });
      
      // Create and download CSV file
      const csvContent = csv.join('\n');
      const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });
      const link = document.createElement('a');
      const url = URL.createObjectURL(blob);
      
      link.setAttribute('href', url);
      link.setAttribute('download', `student_pin_${new Date().toISOString().slice(0,10)}.csv`);
      link.style.visibility = 'hidden';
      
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }
    
    // Auto-hide alerts after 5 seconds
    setTimeout(() => {
      const alerts = document.querySelectorAll('.alert');
      alerts.forEach(alert => {
        alert.style.transition = 'opacity 0.5s';
        alert.style.opacity = '0';
        setTimeout(() => {
          alert.remove();
        }, 500);
      });
    }, 5000);
    
    // Auto-focus on search field
    document.addEventListener('DOMContentLoaded', function() {
      const searchField = document.getElementById('reg_no');
      if (searchField) {
        searchField.focus();
      }
    });
  </script>
</body>
</html>