<?php
// student_records.php
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    require_once 'config/database.php';

    $database = Database::getInstance();
    $db = $database->getConn();

    // Get filter parameters with default values
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $program = isset($_GET['program']) ? trim($_GET['program']) : '';
    $program_type = isset($_GET['program_type']) ? trim($_GET['program_type']) : '';
    $department = isset($_GET['department']) ? trim($_GET['department']) : '';
    $sort_by = isset($_GET['sort_by']) ? trim($_GET['sort_by']) : 'full_name';
    $sort_order = isset($_GET['sort_order']) ? trim($_GET['sort_order']) : 'ASC';

    // Pagination parameters
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $per_page = 10; // Number of records per page
    if ($page < 1) $page = 1;

    // Validate sort column
    $valid_sort_columns = ['full_name', 'student_id', 'program', 'department', 'grade', 'graduation_date'];
    if (!in_array($sort_by, $valid_sort_columns)) {
        $sort_by = 'full_name';
    }

    // Build base conditions
    $conditions = [];
    $params = [];
    $types = []; // For PDO parameter types

    if ($search) {
        $conditions[] = "(full_name LIKE ? OR student_id LIKE ? OR email LIKE ? OR phone LIKE ?)";
        $search_param = "%{$search}%";
        $params[] = $search_param;
        $params[] = $search_param;
        $params[] = $search_param;
        $params[] = $search_param;
        $types = array_merge($types, [PDO::PARAM_STR, PDO::PARAM_STR, PDO::PARAM_STR, PDO::PARAM_STR]);
    }

    if ($program) {
        $conditions[] = "program LIKE ?";
        $params[] = "%{$program}%";
        $types[] = PDO::PARAM_STR;
    }

    if ($program_type) {
        if ($program_type == 'HND') {
            $conditions[] = "(certificate_id LIKE ? OR program LIKE ?)";
            $params[] = '%HND%';
            $params[] = '%HND%';
            $types[] = PDO::PARAM_STR;
            $types[] = PDO::PARAM_STR;
        } elseif ($program_type == 'ND') {
            $conditions[] = "(certificate_id NOT LIKE ? AND program NOT LIKE ?)";
            $params[] = '%HND%';
            $params[] = '%HND%';
            $types[] = PDO::PARAM_STR;
            $types[] = PDO::PARAM_STR;
        }
    }

    if ($department) {
        $conditions[] = "department LIKE ?";
        $params[] = "%{$department}%";
        $types[] = PDO::PARAM_STR;
    }

    // Build WHERE clause
    $where_clause = '';
    if (!empty($conditions)) {
        $where_clause = ' WHERE ' . implode(' AND ', $conditions);
    } else {
        $where_clause = ' WHERE 1=1';
    }

    // Get total count of records
    $count_query = "SELECT COUNT(*) as total FROM students" . $where_clause;
    $count_stmt = $db->prepare($count_query);
    
    // Bind parameters for count query
    foreach ($params as $key => $value) {
        $count_stmt->bindValue($key + 1, $value, $types[$key] ?? PDO::PARAM_STR);
    }
    
    $count_stmt->execute();
    $total_records_result = $count_stmt->fetch(PDO::FETCH_ASSOC);
    $total_records = $total_records_result['total'];
    $total_pages = ceil($total_records / $per_page);

    // Ensure page is within valid range
    if ($page > $total_pages && $total_pages > 0) {
        $page = $total_pages;
    }

    // Calculate offset
    $offset = ($page - 1) * $per_page;

    // Build main query with pagination
    $query = "SELECT * FROM students" . $where_clause . " ORDER BY {$sort_by} {$sort_order} LIMIT ? OFFSET ?";
    
    // Add pagination parameters
    $params[] = $per_page;
    $params[] = $offset;
    $types[] = PDO::PARAM_INT;
    $types[] = PDO::PARAM_INT;

    // Execute the main query
    $stmt = $db->prepare($query);
    
    // Bind parameters for main query
    foreach ($params as $key => $value) {
        $stmt->bindValue($key + 1, $value, $types[$key] ?? PDO::PARAM_STR);
    }
    
    $stmt->execute();
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $current_page_students = count($students);

    // Get unique values for filters
    $programs_stmt = $db->query("SELECT DISTINCT program FROM students WHERE program IS NOT NULL AND program != '' ORDER BY program");
    $programs = $programs_stmt->fetchAll(PDO::FETCH_COLUMN);

    $departments_stmt = $db->query("SELECT DISTINCT department FROM students WHERE department IS NOT NULL AND department != '' ORDER BY department");
    $departments = $departments_stmt->fetchAll(PDO::FETCH_COLUMN);

    // Calculate statistics for current page results
    $hnd_count = 0;
    $nd_count = 0;
    $distinction_count = 0;
    $upper_credit_count = 0;
    $lower_credit_count = 0;
    $pass_count = 0;
    $current_year = date('Y');
    $recent_grads = 0;

    foreach ($students as $student) {
        // Count HND vs ND
        $cert_id = strtoupper($student['certificate_id'] ?? '');
        $program_name = strtoupper($student['program'] ?? '');
        if (strpos($cert_id, 'HND') !== false || strpos($program_name, 'HND') !== false) {
            $hnd_count++;
        } else {
            $nd_count++;
        }
        
        // Count grades
        $grade = strtoupper($student['grade'] ?? '');
        if (strpos($grade, 'DISTINCTION') !== false) {
            $distinction_count++;
        } elseif (strpos($grade, 'UPPER') !== false) {
            $upper_credit_count++;
        } elseif (strpos($grade, 'LOWER') !== false) {
            $lower_credit_count++;
        } elseif (strpos($grade, 'PASS') !== false) {
            $pass_count++;
        }
        
        // Count recent graduates
        if (!empty($student['graduation_date']) && $student['graduation_date'] != '0000-00-00') {
            $grad_year = substr($student['graduation_date'], 0, 4);
            if ($grad_year == $current_year) {
                $recent_grads++;
            }
        }
    }

} catch (Exception $e) {
    // Log error and show user-friendly message
    error_log("Error in student_records.php: " . $e->getMessage());
    die("An error occurred while loading student records. Please try again later.");
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title>Student Records - Gateway Polytechnic Saapade</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        /* ... (keep all your existing CSS styles) ... */
        body { 
            font-family: "Times New Roman", serif; 
            background: #f5f9ff; 
            margin: 0; 
            padding: 0;
        }
        .navbar {
            background: linear-gradient(135deg, #5461ebff, #6d7cf0ff);
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .navbar-brand {
            font-weight: bold;
        }
        .logo {
            height: 40px;
            margin-right: 10px;
        }
        
        .student-records-container {
            padding: 20px;
        }
        
        .filter-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        
        .stats-card {
            background: white;
            border-radius: 8px;
            padding: 12px;
            margin-bottom: 15px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            text-align: center;
            transition: transform 0.2s;
            height: 100%;
            border-left: 4px solid;
        }
        
        .stats-card:hover {
            transform: translateY(-3px);
        }
        
        .student-table-container {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            overflow-x: auto;
        }
        
        .student-avatar {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            object-fit: cover;
            border: 2px solid #1a237e;
        }
        
        .action-btn {
            padding: 5px 10px;
            margin: 2px;
            font-size: 12px;
        }
        
        .grade-badge {
            padding: 5px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: bold;
            display: inline-block;
        }
        
        .badge-distinction { background-color: #28a745; color: white; }
        .badge-upper { background-color: #007bff; color: white; }
        .badge-lower { background-color: #ffc107; color: #212529; }
        .badge-pass { background-color: #17a2b8; color: white; }
        .badge-fail { background-color: #dc3545; color: white; }
        .badge-default { background-color: #6c757d; color: white; }
        
        .pagination-container {
            margin-top: 20px;
            display: flex;
            justify-content: center;
        }
        
        .pagination .page-link {
            color: #5461eb;
            border: 1px solid #dee2e6;
        }
        
        .pagination .page-item.active .page-link {
            background-color: #5461eb;
            border-color: #5461eb;
        }
        
        .sortable {
            cursor: pointer;
            position: relative;
        }
        
        .sortable:hover {
            background-color: #f8f9fa;
        }
        
        .sort-icon {
            margin-left: 5px;
            font-size: 12px;
        }
        
        .student-link {
            color: #007bff;
            text-decoration: none;
            cursor: pointer;
            font-weight: 500;
        }
        
        .student-link:hover {
            text-decoration: underline;
            color: #0056b3;
        }
        
        .export-btn {
            margin-left: 10px;
        }
        
        .stats-icon {
            font-size: 1.5rem;
            margin-bottom: 8px;
            opacity: 0.8;
        }
        
        .stat-number {
            font-size: 1.4rem;
            font-weight: bold;
            margin: 5px 0;
        }
        
        .stat-label {
            font-size: 0.8rem;
            color: #6c757d;
            margin-bottom: 3px;
        }
        
        .results-info {
            font-size: 0.9rem;
            color: #6c757d;
        }
        
        /* Stat card colors */
        .card-hnd { border-left-color: #28a745; }
        .card-nd { border-left-color: #007bff; }
        .card-distinction { border-left-color: #ffc107; }
        .card-upper { border-left-color: #17a2b8; }
        .card-lower { border-left-color: #fd7e14; }
        .card-pass { border-left-color: #6f42c1; }
        .card-recent { border-left-color: #e83e8c; }
        .card-total { border-left-color: #6c757d; }
        
        /* Stat icon colors */
        .icon-hnd { color: #28a745; }
        .icon-nd { color: #007bff; }
        .icon-distinction { color: #ffc107; }
        .icon-upper { color: #17a2b8; }
        .icon-lower { color: #fd7e14; }
        .icon-pass { color: #6f42c1; }
        .icon-recent { color: #e83e8c; }
        .icon-total { color: #6c757d; }
        
        @media (max-width: 768px) {
            .filter-card .row > div {
                margin-bottom: 15px;
            }
            
            .action-btn {
                display: block;
                width: 100%;
                margin-bottom: 5px;
            }
            
            .stats-card {
                margin-bottom: 10px;
            }
            
            .pagination-container {
                flex-wrap: wrap;
            }
        }
        
        .row-clickable {
            cursor: pointer;
            transition: background-color 0.2s;
        }
        
        .row-clickable:hover {
            background-color: #f8f9fa;
        }
        
        .page-item.disabled .page-link {
            color: #6c757d;
            pointer-events: none;
            background-color: #fff;
            border-color: #dee2e6;
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark">
        <div class="container">
            <a class="navbar-brand" href="index.php">
                <img src="assets/NewLogo.svg" alt="Logo" class="logo" onerror="this.src='https://via.placeholder.com/40x40/ffffff/1a237e?text=GIPS'">
                Gateway ICT Polytechnic Saapade
            </a>
            <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav">
                <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarNav">
                <ul class="navbar-nav ms-auto">
                    <li class="nav-item">
                        <a class="nav-link" href="index.php">
                            <i class="fas fa-certificate me-1"></i>Certificate
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link active" href="student_records.php">
                            <i class="fas fa-users me-1"></i>Student Records
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="upload_student_data.php">
                            <i class="fas fa-file-upload me-1"></i>Upload Data
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="certificate_upload.php"><i class="fas fa-file-export me-1"></i>Upload Certificate Data</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="upload_pictures.php"><i class="fas fa-file-export me-1"></i>Upload Picture Data</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="index.php#reportsSection">
                            <i class="fas fa-chart-bar me-1"></i>Reports
                        </a>
                    </li>
                </ul>
            </div>
        </div>
    </nav>

    <div class="container-fluid student-records-container">
        <div class="row mb-4">
            <div class="col-md-8">
                <h2><i class="fas fa-users me-2"></i>Student Records Management</h2>
                <p class="text-muted">Browse, search, and manage all student records. Click on any student to view their certificate.</p>
            </div>
            <div class="col-md-4 text-end">
                <button class="btn btn-success btn-sm" onclick="exportToExcel()">
                    <i class="fas fa-file-excel me-1"></i>Export to Excel
                </button>
                <button class="btn btn-info btn-sm" onclick="refreshPage()">
                    <i class="fas fa-sync me-1"></i>Refresh
                </button>
                <a href="index.php" class="btn btn-primary btn-sm">
                    <i class="fas fa-certificate me-1"></i>Back to Certificate
                </a>
            </div>
        </div>

        <!-- Statistics Cards - Compact Version -->
        <div class="row mb-4">
            <div class="col-md-2 col-6">
                <div class="stats-card card-total">
                    <div class="stats-icon icon-total">
                        <i class="fas fa-users"></i>
                    </div>
                    <div class="stat-number"><?php echo $current_page_students; ?></div>
                    <div class="stat-label">Showing</div>
                </div>
            </div>
            <div class="col-md-2 col-6">
                <div class="stats-card card-hnd">
                    <div class="stats-icon icon-hnd">
                        <i class="fas fa-graduation-cap"></i>
                    </div>
                    <div class="stat-number"><?php echo $hnd_count; ?></div>
                    <div class="stat-label">HND</div>
                </div>
            </div>
            <div class="col-md-2 col-6">
                <div class="stats-card card-nd">
                    <div class="stats-icon icon-nd">
                        <i class="fas fa-user-graduate"></i>
                    </div>
                    <div class="stat-number"><?php echo $nd_count; ?></div>
                    <div class="stat-label">ND</div>
                </div>
            </div>
            <div class="col-md-2 col-6">
                <div class="stats-card card-distinction">
                    <div class="stats-icon icon-distinction">
                        <i class="fas fa-award"></i>
                    </div>
                    <div class="stat-number"><?php echo $distinction_count; ?></div>
                    <div class="stat-label">Distinction</div>
                </div>
            </div>
            <div class="col-md-2 col-6">
                <div class="stats-card card-upper">
                    <div class="stats-icon icon-upper">
                        <i class="fas fa-star"></i>
                    </div>
                    <div class="stat-number"><?php echo $upper_credit_count; ?></div>
                    <div class="stat-label">Upper Credit</div>
                </div>
            </div>
            <div class="col-md-2 col-6">
                <div class="stats-card card-lower">
                    <div class="stats-icon icon-lower">
                        <i class="fas fa-star-half-alt"></i>
                    </div>
                    <div class="stat-number"><?php echo $lower_credit_count; ?></div>
                    <div class="stat-label">Lower Credit</div>
                </div>
            </div>
        </div>
        
        <div class="row mb-4">
            <div class="col-md-3 col-6">
                <div class="stats-card card-pass">
                    <div class="stats-icon icon-pass">
                        <i class="fas fa-check-circle"></i>
                    </div>
                    <div class="stat-number"><?php echo $pass_count; ?></div>
                    <div class="stat-label">Pass</div>
                </div>
            </div>
            <div class="col-md-3 col-6">
                <div class="stats-card card-recent">
                    <div class="stats-icon icon-recent">
                        <i class="fas fa-calendar-alt"></i>
                    </div>
                    <div class="stat-number"><?php echo $recent_grads; ?></div>
                    <div class="stat-label">Grad <?php echo $current_year; ?></div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="alert alert-info mb-0">
                    <i class="fas fa-info-circle me-2"></i>
                    <strong>Tip:</strong> Click on any student name or row to view their certificate. Use the filters to find specific students.
                    <?php if ($total_pages > 0): ?>
                    <span class="badge bg-primary ms-2">Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Filter Card -->
        <div class="filter-card">
            <h5><i class="fas fa-filter me-2"></i>Filter & Search Students</h5>
            <form method="GET" action="">
                <!-- Hidden pagination reset -->
                <input type="hidden" name="page" value="1">
                
                <div class="row">
                    <div class="col-md-3 mb-3">
                        <label class="form-label">Search</label>
                        <input type="text" class="form-control" name="search" placeholder="Name, Matric, Email..." value="<?php echo htmlspecialchars($search); ?>">
                    </div>
                    <div class="col-md-3 mb-3">
                        <label class="form-label">Program</label>
                        <select class="form-select" name="program">
                            <option value="">All Programs</option>
                            <?php foreach ($programs as $prog): ?>
                                <option value="<?php echo htmlspecialchars($prog); ?>" <?php echo $program == $prog ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($prog); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2 mb-3">
                        <label class="form-label">Program Type</label>
                        <select class="form-select" name="program_type">
                            <option value="">All Types</option>
                            <option value="HND" <?php echo $program_type == 'HND' ? 'selected' : ''; ?>>HND Only</option>
                            <option value="ND" <?php echo $program_type == 'ND' ? 'selected' : ''; ?>>ND Only</option>
                        </select>
                    </div>
                    <div class="col-md-2 mb-3">
                        <label class="form-label">Department</label>
                        <select class="form-select" name="department">
                            <option value="">All Departments</option>
                            <?php foreach ($departments as $dept): ?>
                                <option value="<?php echo htmlspecialchars($dept); ?>" <?php echo $department == $dept ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($dept); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2 mb-3">
                        <label class="form-label">Sort By</label>
                        <select class="form-select" name="sort_by">
                            <option value="full_name" <?php echo $sort_by == 'full_name' ? 'selected' : ''; ?>>Name</option>
                            <option value="student_id" <?php echo $sort_by == 'student_id' ? 'selected' : ''; ?>>Matric No</option>
                            <option value="program" <?php echo $sort_by == 'program' ? 'selected' : ''; ?>>Program</option>
                            <option value="department" <?php echo $sort_by == 'department' ? 'selected' : ''; ?>>Department</option>
                            <option value="grade" <?php echo $sort_by == 'grade' ? 'selected' : ''; ?>>Grade</option>
                            <option value="graduation_date" <?php echo $sort_by == 'graduation_date' ? 'selected' : ''; ?>>Graduation Date</option>
                        </select>
                        <input type="hidden" name="sort_order" id="sort_order_input" value="<?php echo $sort_order; ?>">
                    </div>
                </div>
                <div class="row">
                    <div class="col-md-12 text-end">
                        <button type="submit" class="btn btn-primary btn-sm">
                            <i class="fas fa-search me-1"></i>Apply Filters
                        </button>
                        <a href="student_records.php" class="btn btn-secondary btn-sm">
                            <i class="fas fa-times me-1"></i>Clear All
                        </a>
                    </div>
                </div>
            </form>
        </div>

        <!-- Students Table -->
        <div class="student-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"></i>Students List 
                </h5>
                <div class="results-info">
                    <?php if ($total_records > 0): ?>
                        Showing <?php echo $current_page_students; ?> of <?php echo $total_records; ?> total records
                        <?php if ($search || $program || $program_type || $department): ?>
                            (filtered)
                        <?php endif; ?>
                    <?php else: ?>
                        No records found
                    <?php endif; ?>
                </div>
            </div>
            
            <?php if ($current_page_students > 0): ?>
                <div class="table-responsive">
                    <table class="table table-striped table-hover" id="studentsTable">
                        <thead>
                            <tr>
                                <th>Photo</th>
                                <th class="sortable" onclick="sortColumn('full_name')">
                                    Name <span class="sort-icon"><?php echo getSortIcon('full_name'); ?></span>
                                </th>
                                <th class="sortable" onclick="sortColumn('student_id')">
                                    Matric No <span class="sort-icon"><?php echo getSortIcon('student_id'); ?></span>
                                </th>
                                <th class="sortable" onclick="sortColumn('program')">
                                    Program <span class="sort-icon"><?php echo getSortIcon('program'); ?></span>
                                </th>
                                <th>Department</th>
                                <th class="sortable" onclick="sortColumn('grade')">
                                    Grade <span class="sort-icon"><?php echo getSortIcon('grade'); ?></span>
                                </th>
                                <th class="sortable" onclick="sortColumn('graduation_date')">
                                    Graduation <span class="sort-icon"><?php echo getSortIcon('graduation_date'); ?></span>
                                </th>
                                <th>Certificate ID</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($students as $student): ?>
                                <tr class="row-clickable" data-student-id="<?php echo $student['id']; ?>" data-student-name="<?php echo htmlspecialchars($student['full_name'], ENT_QUOTES); ?>">
                                    <td>
                                        <?php
                                        $photo_url = 'https://via.placeholder.com/40x40/1a237e/ffffff?text=' . substr($student['full_name'], 0, 1);
                                        if (!empty($student['photo_path'])) {
                                            if (strpos($student['photo_path'], 'data:image') === 0) {
                                                $photo_url = $student['photo_path'];
                                            } elseif (file_exists($student['photo_path'])) {
                                                $photo_url = $student['photo_path'];
                                            }
                                        }
                                        ?>
                                        <img src="<?php echo htmlspecialchars($photo_url); ?>" 
                                             alt="<?php echo htmlspecialchars($student['full_name']); ?>"
                                             class="student-avatar"
                                             onerror="this.src='https://via.placeholder.com/40x40/1a237e/ffffff?text=<?php echo substr($student['full_name'], 0, 1); ?>'">
                                    </td>
                                    <td>
                                        <a href="javascript:void(0);" 
                                           class="student-link view-certificate" 
                                           data-student-id="<?php echo $student['id']; ?>">
                                            <?php echo htmlspecialchars($student['full_name']); ?>
                                        </a>
                                    </td>
                                    <td><code><?php echo htmlspecialchars($student['student_id']); ?></code></td>
                                    <td><?php echo htmlspecialchars($student['program']); ?></td>
                                    <td><?php echo htmlspecialchars($student['department']); ?></td>
                                    <td>
                                        <span class="grade-badge <?php echo getGradeBadgeClass($student['grade']); ?>">
                                            <?php echo htmlspecialchars($student['grade']); ?>
                                        </span>
                                    </td>
                                    <td>
                                        <?php 
                                        if (!empty($student['graduation_date']) && $student['graduation_date'] != '0000-00-00') {
                                            echo date('M j, Y', strtotime($student['graduation_date']));
                                        } else {
                                            echo '<span class="text-muted">N/A</span>';
                                        }
                                        ?>
                                    </td>
                                    <td>
                                        <small>
                                            <?php 
                                            if (!empty($student['certificate_id']) && $student['certificate_id'] != 'null') {
                                                echo htmlspecialchars($student['certificate_id']);
                                            } else {
                                                echo '<span class="text-muted">Not assigned</span>';
                                            }
                                            ?>
                                        </small>
                                    </td>
                                    <td>
                                        <button class="btn btn-sm btn-primary action-btn view-certificate-btn" 
                                                data-student-id="<?php echo $student['id']; ?>"
                                                data-student-name="<?php echo htmlspecialchars($student['full_name'], ENT_QUOTES); ?>"
                                                title="View Certificate">
                                            <i class="fas fa-certificate"></i>
                                        </button>
                                        <button class="btn btn-sm btn-info action-btn" 
                                                onclick="viewStudentDetails(<?php echo $student['id']; ?>)"
                                                title="View Details">
                                            <i class="fas fa-eye"></i>
                                        </button>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                
                <!-- Pagination -->
                <?php if ($total_pages > 1): ?>
                <div class="pagination-container">
                    <nav aria-label="Page navigation">
                        <ul class="pagination">
                            <!-- Previous Page Link -->
                            <li class="page-item <?php echo $page <= 1 ? 'disabled' : ''; ?>">
                                <a class="page-link" href="<?php echo buildPageUrl($page - 1); ?>" aria-label="Previous">
                                    <span aria-hidden="true">&laquo;</span>
                                </a>
                            </li>
                            
                            <!-- First Page -->
                            <?php if ($page > 3): ?>
                                <li class="page-item">
                                    <a class="page-link" href="<?php echo buildPageUrl(1); ?>">1</a>
                                </li>
                                <?php if ($page > 4): ?>
                                    <li class="page-item disabled">
                                        <span class="page-link">...</span>
                                    </li>
                                <?php endif; ?>
                            <?php endif; ?>
                            
                            <!-- Page Numbers -->
                            <?php for ($i = max(1, $page - 2); $i <= min($total_pages, $page + 2); $i++): ?>
                                <li class="page-item <?php echo $i == $page ? 'active' : ''; ?>">
                                    <a class="page-link" href="<?php echo buildPageUrl($i); ?>"><?php echo $i; ?></a>
                                </li>
                            <?php endfor; ?>
                            
                            <!-- Last Page -->
                            <?php if ($page < $total_pages - 2): ?>
                                <?php if ($page < $total_pages - 3): ?>
                                    <li class="page-item disabled">
                                        <span class="page-link">...</span>
                                    </li>
                                <?php endif; ?>
                                <li class="page-item">
                                    <a class="page-link" href="<?php echo buildPageUrl($total_pages); ?>"><?php echo $total_pages; ?></a>
                                </li>
                            <?php endif; ?>
                            
                            <!-- Next Page Link -->
                            <li class="page-item <?php echo $page >= $total_pages ? 'disabled' : ''; ?>">
                                <a class="page-link" href="<?php echo buildPageUrl($page + 1); ?>" aria-label="Next">
                                    <span aria-hidden="true">&raquo;</span>
                                </a>
                            </li>
                        </ul>
                    </nav>
                </div>
                <?php endif; ?>
                
            <?php else: ?>
                <div class="alert alert-info text-center py-5">
                    <i class="fas fa-info-circle fa-3x mb-3 text-info"></i>
                    <h4>No Students Found</h4>
                    <p class="mb-4">No student records match your search criteria.</p>
                    <a href="student_records.php" class="btn btn-primary">
                        <i class="fas fa-redo me-1"></i>Show All Students
                    </a>
                </div>
            <?php endif; ?>
            
            <!-- Page Size Info -->
            <div class="mt-3 text-muted text-center">
                <small>
                    <?php if ($total_records > 0): ?>
                        Showing <?php echo $current_page_students; ?> student(s) per page 
                        <?php if ($total_pages > 0): ?>
                            (Page <?php echo $page; ?> of <?php echo $total_pages; ?>)
                        <?php endif; ?>
                    <?php endif; ?>
                </small>
            </div>
        </div>
    </div>

    <!-- JavaScript -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <script>
        // Function to redirect to index.php with student ID - SIMPLIFIED VERSION
        function viewStudentCertificate(studentId, studentName) {
            console.log('Redirecting to view certificate for student:', studentId, studentName);
            
            // Redirect to index.php with student ID as URL parameter
            window.location.href = 'index.php?student_id=' + studentId;
        }
        
        // Function to view student details
        function viewStudentDetails(studentId) {
            alert('Viewing details for student ID: ' + studentId + '\n\nThis feature can be expanded to show more detailed information in a modal.');
        }
        
        // Function to sort table
        function sortColumn(column) {
            const urlParams = new URLSearchParams(window.location.search);
            let currentSort = urlParams.get('sort_by') || 'full_name';
            let currentOrder = urlParams.get('sort_order') || 'ASC';
            
            let newOrder = 'ASC';
            if (currentSort === column && currentOrder === 'ASC') {
                newOrder = 'DESC';
            }
            
            // Update hidden input
            document.getElementById('sort_order_input').value = newOrder;
            
            // Update URL parameters
            urlParams.set('sort_by', column);
            urlParams.set('sort_order', newOrder);
            urlParams.set('page', '1'); // Reset to first page when sorting
            
            // Submit the form
            const form = document.querySelector('form');
            const sortByInput = document.querySelector('[name="sort_by"]');
            sortByInput.value = column;
            const pageInput = document.querySelector('[name="page"]');
            pageInput.value = '1';
            form.submit();
        }
        
        // Function to export to Excel (current page only for now)
        function exportToExcel() {
            try {
                const table = document.getElementById('studentsTable');
                if (!table) {
                    alert('No data to export!');
                    return;
                }
                
                // Clone table to avoid modifying the original
                const clonedTable = table.cloneNode(true);
                
                // Remove action column from export
                const actionHeader = clonedTable.querySelector('th:last-child');
                const actionCells = clonedTable.querySelectorAll('td:last-child');
                
                if (actionHeader) actionHeader.remove();
                actionCells.forEach(cell => cell.remove());
                
                // Create workbook and worksheet
                const workbook = XLSX.utils.table_to_book(clonedTable, {sheet: "Students"});
                
                // Generate filename with timestamp
                const timestamp = new Date().toISOString().slice(0, 19).replace(/[:T]/g, '-');
                const filename = "student_records_" + timestamp + ".xlsx";
                
                // Download the file
                XLSX.writeFile(workbook, filename);
                
                alert('Export completed successfully! File: ' + filename);
            } catch (error) {
                console.error('Export error:', error);
                alert('Error exporting to Excel: ' + error.message);
            }
        }
        
        // Function to refresh page
        function refreshPage() {
            window.location.reload();
        }
        
        // Add event listeners when DOM is loaded
        document.addEventListener('DOMContentLoaded', function() {
            // Add click events to student name links
            const studentLinks = document.querySelectorAll('.student-link.view-certificate');
            studentLinks.forEach(link => {
                link.addEventListener('click', function(e) {
                    e.preventDefault();
                    e.stopPropagation();
                    const studentId = this.getAttribute('data-student-id');
                    const studentName = this.textContent;
                    viewStudentCertificate(studentId, studentName);
                });
            });
            
            // Add click events to view certificate buttons
            const certificateBtns = document.querySelectorAll('.view-certificate-btn');
            certificateBtns.forEach(btn => {
                btn.addEventListener('click', function(e) {
                    e.stopPropagation();
                    const studentId = this.getAttribute('data-student-id');
                    const studentName = this.getAttribute('data-student-name');
                    viewStudentCertificate(studentId, studentName);
                });
            });
            
            // Make entire row clickable
            const tableRows = document.querySelectorAll('tbody tr.row-clickable');
            tableRows.forEach(row => {
                row.addEventListener('click', function(e) {
                    // Don't trigger if clicking on action buttons
                    if (e.target.tagName === 'BUTTON' || e.target.closest('button')) {
                        return;
                    }
                    
                    const studentId = this.getAttribute('data-student-id');
                    const studentName = this.getAttribute('data-student-name');
                    viewStudentCertificate(studentId, studentName);
                });
                
                // Add hover effect
                row.addEventListener('mouseenter', function() {
                    this.style.cursor = 'pointer';
                });
            });
            
            // Check URL parameters for any messages
            const urlParams = new URLSearchParams(window.location.search);
            if (urlParams.get('message')) {
                alert(urlParams.get('message'));
            }
            
            // Reset page to 1 when filters are applied
            const filterForm = document.querySelector('form');
            if (filterForm) {
                filterForm.addEventListener('submit', function() {
                    const pageInput = this.querySelector('[name="page"]');
                    if (pageInput) {
                        pageInput.value = '1';
                    }
                });
            }
        });
    </script>
</body>
</html>

<?php
// Helper function to get sort icon
function getSortIcon($column) {
    global $sort_by, $sort_order;
    if ($sort_by == $column) {
        return $sort_order == 'ASC' ? '↑' : '↓';
    }
    return '↕';
}

// Helper function to get grade badge class
function getGradeBadgeClass($grade) {
    if (!$grade) return 'badge-default';
    
    $grade = strtoupper($grade);
    if (strpos($grade, 'DISTINCTION') !== false) return 'badge-distinction';
    if (strpos($grade, 'UPPER') !== false) return 'badge-upper';
    if (strpos($grade, 'LOWER') !== false) return 'badge-lower';
    if (strpos($grade, 'PASS') !== false) return 'badge-pass';
    if (strpos($grade, 'FAIL') !== false) return 'badge-fail';
    return 'badge-default';
}

// Helper function to build pagination URLs
function buildPageUrl($page_num) {
    global $search, $program, $program_type, $department, $sort_by, $sort_order;
    
    $params = [];
    if ($search) $params['search'] = $search;
    if ($program) $params['program'] = $program;
    if ($program_type) $params['program_type'] = $program_type;
    if ($department) $params['department'] = $department;
    if ($sort_by != 'full_name') $params['sort_by'] = $sort_by;
    if ($sort_order != 'ASC') $params['sort_order'] = $sort_order;
    $params['page'] = $page_num;
    
    return 'student_records.php?' . http_build_query($params);
}
?>