<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title>Certificate System - 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>
        @page { size: A4; margin: 0; }
        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);
            color:white;
        }
        .navbar-brand {
            font-weight: bold;
        }
        .logo {
            height: 40px;
            margin-right: 10px;
        }
        .mlogo {
            height: 100px;
            margin-right: 10px;
        }
        
        /* Certificate Container for A4 printing */
        .certificate-a4 {
            border: 2px solid #ffffffff; 
            background: white; 
            width: 210mm; 
            height: 297mm; 
            padding: 20mm;
            box-sizing: border-box; 
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.2); 
            text-align: center; 
            margin: 20px auto;
            position: relative;
            overflow: hidden;
        }
        
        .certificate-a4 h1 { 
            font-size: 28px; 
            margin: 0 0 8px 0; /* Reduced margin */
            font-weight: bold;
            line-height: 1.2;
        }
        .pos { 
            position: relative; 
            width: 100%; 
            height: 140px; /* Reduced height */
            margin-bottom: 12px; /* Reduced margin */
        }
        .qr { 
            position: absolute; 
            top: 40px; /* SHIFTED DOWN from 8px to 20px */
            left: 0; 
            width: 100px;
            height: 100px;
        }
        .qr img {
            width: 100%;
            height: 100%;
            object-fit: cover;
        }
        .sap { 
            position: absolute; 
            top: 12px; /* Keep as is */
            left: 48%; 
            transform: translateX(-50%); 
            width: 120px;
            height: 120px; 
        }
        .passport { 
            position: absolute; 
            top: 40px; /* SHIFTED DOWN from 8px to 20px */
            right: 0; 
            width: 100px;
            height: 150px; /* INCREASED from 130px to 150px */
            display: flex;
            flex-direction: column;
            align-items: center;
        }
        .passport img {
            width: 100%;
            height: 120px; /* INCREASED from 100px to 120px */
            object-fit: cover;
            border: 1px solid #ccc;
        }
        .certificate-number-under {
            font-weight: bold;
            font-size: 10px;
            color: #000000ff;
            text-align: center;
            margin-top: 4px; /* Reduced margin */
            width: 100%;
            min-height: 20px;
        }
        .certificate-a4 h2 { 
            font-style: italic; 
            margin: 12px 0; /* Reduced margin */
            font-size: 20px;
        }
        .content { 
            margin: 16px 0; /* Reduced margin */
            font-size: 16px; 
            line-height: 1.5; /* Reduced line height */
            text-align: justify; 
            min-height: 180px; /* Reduced height */
        }
        .student-name-bold {
            font-weight: bold;
            font-size: 22px;
            text-align: center;
            display: block;
            margin: 6px 0; /* Reduced margin */
            padding: 6px; /* Reduced padding */
            /*border-bottom: 1px solid #000;*/
            width: 80%;
            margin-left: auto;
            margin-right: auto;
        }
        .program-bold {
            font-weight: bold;
            font-size: 18px;
            text-align: center;
            display: block;
            margin: 50px 0; /* Reduced margin */
            padding: 6px; /* Reduced padding */
            /*border-bottom: 1px solid #000;*/
            width: 70%;
            
            margin-left: 9%;
            margin-right: 30%; /* SHIFTED LEFT from auto to 15% */
        }
        .certificate-type-display {
            font-weight: bold;
            font-size: 24px;
            text-align: center;
            display: block;
            margin: 6mm 0 3px 0; /* Reduced top margin, kept bottom */
        }
        .in-text {
            font-style: italic;
            text-align: center;
            display: block;
            margin: 3px 0 12mm 0; /* Reduced bottom margin */
            font-size: 16px;
        }
         .fields { 
            position: relative; 
            width: 100%; 
            height: 55px; /* Increased height to accommodate the shift */
            font-size: 14px; 
            font-style: italic; 
            margin: 10px 0 8px 0;  /*Increased top margin to push it down */
        }
        .matric { 
            position: absolute; 
            top: -3px; /* Added 20px to push it down */
            left: 0; 
            font-size: 16px;
            width: 245px;
            height: 20px; 
            margin:10px;
            margin-top:1px;
        }

        .inner { 
            margin-left: 0;
        }
        .grade { 
            position: absolute; 
            top: -10px; /* Added 20px to push it down */
            font-size: 17px;
            right: 0; 
            width: 200px;
            height: 20px; 
            margin-right:50px;
        }
        .space { 
            padding-left: 150px;
        }
        .seal { 
            margin: 25px auto; /* Reduced margin */
            width: 100px;
            height: 100px; 
            border-radius: 50%;
            display: flex; 
            align-items: center; 
            justify-content: center; 
            font-size: 10px;
            text-align: center; 
            line-height: 1.2; 
            overflow: hidden;
        }
        .seal img {
            width: 100%;
            height: 100%;
            object-fit: cover;
        }
        
        /* --- UPDATED CERTIFICATE FOOTER AND SIGNATURE CSS --- */
        .certificate-footer {
            position: relative;
            margin-top: 20px; /* Reduced margin */
            width: 100%;
            page-break-inside: avoid !important;
        }

        .signatures { 
            display: flex; 
            justify-content: space-between; 
            align-items: flex-start;
            margin-bottom: 50px; /* Reduced margin */
            position: relative;
            width: 100%;
        }

        /* Generic signature box styling */
        .signature-box {
            text-align: center;
            position: relative;
            min-height: 70px; /* Reduced height */
        }

        /* Date/Signature/Text content on or above the line */
        .signature-box .line-content {
            display: block;
            font-weight: bold; 
            margin-bottom: 0.5px; /* Reduced margin */
        }

        /* The actual line placeholder */
        .signature-box .signature-line-placeholder {
            display: block;
            width: 180px; 
            height: 1px;
            background-color: #000;
            margin: 4px auto; /* Reduced margin */
        }
        
        /* Label (Date, Registrar, Rector) positioning */
        .signature-box > span:last-child {
            display: block;
            margin-top: 2px; /* Reduced margin */
        }

        /* Signature Box Dimensions */
        .sign-box-date { 
            width: 32%; 
            top:-30px;
        }

        .sign-box-registrar { 
            width: 45%; 
        }

        .sign-box-rector { 
            position: absolute;
            bottom: -75px; /* Moved up (reduced from -90px) */
            left: 50%;
            transform: translateX(-50%);
            width: 30%; 
        }
        
        /* NO-PRINT PLACEHOLDERS */
        .no-print-placeholder {
            color: #999;
            font-style: italic;
        }
        
        .logo-placeholder {
            border: 2px dashed #999;
            background-color: #f0f0f0;
            width: 100px;
            height: 100px;
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 10px;
            color: #666;
            margin: 0 auto;
        }
        
        /* Image Not Found Placeholder */
        .image-not-found {
            width: 100%;
            height: 120px;
            display: flex;
            align-items: center;
            justify-content: center;
            background: #f8f9fa;
            border: 1px solid #dee2e6;
            color: #6c757d;
            font-size: 11px;
            text-align: center;
            font-style: italic;
        }
        
        /* END UPDATED SIGNATURE CSS */
        
        .student-list {
            background: white;
            border-radius: 10px;
            padding: 20px;
            margin-top: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        .student-card {
            border: 1px solid #ddd;
            border-radius: 8px;
            padding: 15px;
            margin-bottom: 10px;
            cursor: pointer;
            transition: all 0.3s;
        }
        .student-card:hover {
            background-color: #f8f9fa;
            border-color: #007bff;
        }
        .student-avatar {
            width: 50px;
            height: 50px;
            border-radius: 50%;
            object-fit: cover;
            border: 2px solid #1a237e;
        }
        .settings-panel {
            background: white;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        .loading-spinner {
            display: none;
            position: fixed;
            top: 50%;
            left: 50%;
            transform: translate(-50%, -50%);
            z-index: 1000;
        }
        .stats-panel {
            background: white;
            border-radius: 10px;
            padding: 20px;
            margin: 20px 0;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        .stat-card {
            text-align: center;
            padding: 20px;
            border-radius: 8px;
            margin: 10px 0;
        }
        .stat-printed {
            background: #e8f5e8;
            border-left: 4px solid #28a745;
        }
        .stat-pending {
            background: #fff3cd;
            border-left: 4px solid #ffc107;
        }
        .stat-error {
            background: #f8d7da;
            border-left: 4px solid #dc3545;
        }

        /* Upload page styles */
        .upload-card {
            border: none;
            border-radius: 15px;
            overflow: hidden;
            transition: transform 0.3s;
        }
        .upload-card:hover {
            transform: translateY(-5px);
        }
        .upload-icon {
            font-size: 3rem;
            margin-bottom: 1rem;
        }
        .upload-step {
            margin-bottom: 30px;
        }
        .upload-step-number {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            width: 40px;
            height: 40px;
            background-color: #5461eb;
            color: white;
            border-radius: 50%;
            font-weight: bold;
            margin-right: 10px;
        }
        .file-upload-area {
            border: 3px dashed #ddd;
            border-radius: 15px;
            padding: 50px 20px;
            text-align: center;
            background-color: #f8f9ff;
            cursor: pointer;
            transition: all 0.3s;
        }
        .file-upload-area:hover {
            border-color: #5461eb;
            background-color: #eef1ff;
        }
        .file-upload-area.dragover {
            border-color: #28a745;
            background-color: #e8f5e8;
        }
        .file-info {
            background-color: #f8f9fa;
            border-radius: 10px;
            padding: 15px;
            margin-top: 20px;
        }
        .template-preview {
            border: 1px solid #dee2e6;
            border-radius: 10px;
            overflow: hidden;
            margin-top: 10px;
        }
        .template-preview img {
            width: 100%;
            height: auto;
        }

        @media print {
            body { 
                background: none; 
                box-shadow: none; 
                padding: 0;
                margin: 0;
                -webkit-print-color-adjust: exact;
                color-adjust: exact;
            }
            .certificate-a4 { 
                box-shadow: none; 
                margin: 0;
                border: 2px solid #000;
                padding: 20mm;
                page-break-inside: avoid !important;
                width: 210mm;
                height: 297mm;
            }
            .no-print {
                display: none !important;
            }
            .certificate-footer {
                position: relative;
                bottom: auto;
                left: auto;
                right: auto;
                margin-top: 35px; /* Reduced margin */
                page-break-inside: avoid !important;
                width: 100%;
            }
            .signatures {
                page-break-inside: avoid !important;
                width: 100%;
            }
            .sign-box-rector {
                page-break-inside: avoid !important;
            }
            
            .certificate-footer {
                margin-top: 30px; /* Reduced margin */
            }
            
            .signatures {
                margin-bottom: 45px; /* Reduced margin */
            }
            
            /* MAKE PLACEHOLDER TEXT INVISIBLE BUT STILL OCCUPY SPACE WHEN PRINTING */
            .no-print-placeholder {
                color: transparent !important;
                visibility: hidden !important;
            }
            
            /* MAKE LOGO PLACEHOLDER INVISIBLE BUT STILL OCCUPY SPACE WHEN PRINTING */
            .logo-placeholder {
                border-color: transparent !important;
                background-color: transparent !important;
                color: transparent !important;
                visibility: hidden !important;
            }
            
            /* HIDE STATIC TEXT PLACEHOLDERS BUT KEEP THEIR SPACE WHEN PRINTING */
            .hide-on-print {
                color: transparent !important;
                visibility: hidden !important;
            }
            
            /* Image not found placeholder should be visible in print */
            .image-not-found {
                border: 1px solid #000 !important;
                color: #000 !important;
            }
        }

        @media (max-width: 768px) {
            .certificate-a4 {
                padding: 10mm;
                width: 100%;
                height: auto;
                min-height: 297mm;
            }
            .signatures {
                flex-direction: column;
                gap: 15px; /* Reduced gap */
            }
            .sign-box-date, .sign-box-registrar, .sign-box-rector {
                width: 100%;
                margin: 0px 0; /* Reduced margin */
                text-align: center;
            }
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark no-print">
        <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 active" href="index.php">
                            <i class="fas fa-certificate me-1"></i>Certificate
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" 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="#" onclick="showSection('reportsSection')">
                            <i class="fas fa-chart-bar me-1"></i>Reports
                        </a>
                    </li>
                </ul>
            </div>
        </div>
    </nav>

    <div class="loading-spinner" id="loadingSpinner">
        <div class="spinner-border text-primary" style="width: 3rem; height: 3rem;"></div>
        <div class="mt-2">Loading...</div>
    </div>

    <div class="container-fluid">
        <!-- Certificate Section -->
        <div id="certificateSection">
            <div class="row">
                <div class="col-md-12">
                    <!-- Certificate Container -->
                    <div class="certificate-a4">
                        <!-- Institution Name -->
                        <div class="institution-header">
                            <h1 class="no-print-placeholder hide-on-print">The Gateway (ICT) Polytechnic Saapade<br />Ogun State, Nigeria</h1>
                        </div>
                        
                        <div class="pos">
                            <div class="qr" id="qrCodeContainer">
                                <div style="width:100%;height:100%;display:flex;align-items:center;justify-content:center;background:#f0f0f0;font-size:12px;">QR Code</div>
                            </div>
                            <div class="sap">
                                <!-- Logo Placeholder -->
                                <div class="logo-placeholder hide-on-print" style="width: 100px; height: 100px;">
                                    LOGO<br>PLACEHOLDER
                                </div>
                            </div>
                            <div class="passport" id="studentPhotoContainer">
                                <div class="image-not-found">Image not found</div>
                                <div class="certificate-number-under" id="certificateNumber">Cert. no not found</div>
                            </div>
                        </div>
                        
                        <!-- Certification Statement -->
                        <div class="certify-statement">
                            <h2 class="no-print-placeholder hide-on-print">This is to Certify that</h2>
                        </div>
                        
                        <div class="content"><br />
                            <span class="student-name-bold" id="studentName">[Student Name]</span>
                            
                            <!-- Body text -->
                            <div class="body-text-placeholder no-print-placeholder hide-on-print">
                                having completed an approved course of study, passed the examinations
                                and fulfilled all the requirements prescribed by the Polytechnic, as at
                                this day, under the authority of the Academic Board, been awarded the
                            </div>
                            <br />
                            
                            <span class="certificate-type-display" id="certificateTypeDisplay">National Diploma</span>
                            
                            <!-- "in" text -->
                            <span class="in-text no-print-placeholder hide-on-print">in</span>
                            
                            <span class="program-bold" id="programName">[Program Name]</span>
                        </div>
                        
                        <div class="fields">
                            <div class="matric">
                                <span class="inner">
                                    <!-- "Matric No:" text -->
                                    <span class="no-print-placeholder hide-on-print">Matric No:</span> 
                                    <strong id="studentMatric">[student_id]</strong>
                                </span>
                            </div>
                            <div class="grade">
                                <!-- "With" text -->
                                <span class="no-print-placeholder hide-on-print">With</span> 
                                <strong id="studentFinalGrade">[Grade]</strong> 
                                <span class="space"></span>
                            </div>
                        </div>
                        
                        <div class="seal" id="officialSealContainer">
                            <img src="assets/seal.jpg" alt="Official Seal" onerror="this.style.display='none'">
                        </div>
                        
                        <div class="certificate-footer">
                            <div class="signatures">
                                <div class="sign-box-date signature-box">
                                    <span class="line-content" id="issueDateDisplay"><?php echo date('F j, Y'); ?></span>
                                    <!-- "Date" text -->
                                    <span class="no-print-placeholder hide-on-print">Date</span>
                                </div>
                                <div class="sign-box-registrar signature-box">
                                    <span class="line-content"></span><br />
                                    <!-- "Registrar" text -->
                                    <span class="no-print-placeholder hide-on-print">Registrar</span>
                                </div>
                            </div>
                            <div class="sign-box-rector signature-box">
                                <span class="line-content"></span>
                                <!-- "Rector" text -->
                                <span class="rec no-print-placeholder hide-on-print">Rector</span>
                            </div>
                        </div>
                    </div>
                    
                    <div class="action-buttons no-print text-center mb-4">
                        <button class="btn btn-primary btn-lg" onclick="printCurrentCertificate()">
                            <i class="fas fa-print me-2"></i>Print Current Certificate
                        </button>
                        <a href="student_records.php" class="btn btn-info btn-lg">
                            <i class="fas fa-users me-2"></i>View All Students
                        </a>
                    </div>
                </div>
                
                <div class="col-md-12 no-print">
                    <div class="student-list">
                        <h3 class="mb-4"><i class="fas fa-users me-2"></i>Student Records</h3>
                        <p>kindly search for student here as well </p>
                        
                        <div class="input-group mb-3">
                            <input type="text" class="form-control" placeholder="Search students by name, matric, program, grade..." id="searchInput">
                            <button class="btn btn-outline-primary" type="button" onclick="searchStudents()">
                                <i class="fas fa-search"></i>
                            </button>
                        </div>
                        
                        <div class="mb-2">
                            <small class="text-muted">Showing <span id="studentCount">0</span> students</small>
                        </div>
                        
                        <div id="studentsContainer" style="max-height: 600px; overflow-y: auto;">
                            <div class="text-center">
                                <div class="spinner-border text-primary" role="status">
                                    <span class="visually-hidden">Loading...</span>
                                </div>
                                <p>Loading students...</p>
                            </div>
                        </div>
                        
                        <div class="text-center mt-3">
                            <small class="text-muted">Search shows all matching records from database</small>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Upload Data Section -->
        <div id="uploadDataSection" style="display: none;">
            <div class="row">
                <div class="col-12">
                    <div class="settings-panel">
                        <h4><i class="fas fa-file-upload me-2"></i>Upload Student & Certificate Data</h4>
                        <p class="text-muted">Upload Excel files containing student data and certificate records</p>
                        
                        <!-- Quick Start Guide -->
                        <div class="alert alert-info mb-4">
                            <h5><i class="fas fa-info-circle me-2"></i>Quick Start Guide</h5>
                            <ol class="mb-0">
                                <li>Prepare your Excel files in the correct format (see below)</li>
                                <li>Upload Student Data first (contains basic student information)</li>
                                <li>Upload Certificate Records (contains graduation details and grades)</li>
                                <li>Review and verify the imported data</li>
                                <li>Proceed to print certificates</li>
                            </ol>
                        </div>
                        
                        <!-- Two Column Layout for Uploads -->
                        <div class="row">
                            <!-- Student Data Upload -->
                            <div class="col-md-6 mb-4">
                                <div class="card upload-card h-100">
                                    <div class="card-body text-center">
                                        <div class="upload-icon text-primary">
                                            <i class="fas fa-user-graduate"></i>
                                        </div>
                                        <h4 class="card-title">Student Data Upload</h4>
                                        <p class="card-text text-muted mb-4">
                                            Upload student information including names, matric numbers, and program details.
                                        </p>
                                        
                                        <div class="upload-step">
                                            <h5><span class="upload-step-number">1</span> Format Requirements</h5>
                                            <div class="file-info">
                                                <small><strong>Required Columns:</strong> S/N, Matricnum, Appnum, Surname, Firstname, Othername, Programme, Level, Amount, Receipt No., Phone, Date</small>
                                            </div>
                                            <div class="template-preview">
                                                <img src="https://via.placeholder.com/400x200/4a6cf7/ffffff?text=Student+Data+Template" alt="Student Data Template" class="img-fluid">
                                            </div>
                                        </div>
                                        
                                        <div class="file-upload-area mb-3" id="studentDataUploadArea" onclick="document.getElementById('studentDataFile').click()">
                                            <i class="fas fa-cloud-upload-alt fa-3x text-primary mb-3"></i>
                                            <h5>Drop Student Data Excel File Here</h5>
                                            <p class="text-muted">or click to browse</p>
                                            <small class="text-muted">Supports .xlsx and .xls files (Max 10MB)</small>
                                        </div>
                                        <input type="file" id="studentDataFile" accept=".xlsx,.xls" style="display: none;">
                                        
                                        <div class="mb-3">
                                            <label class="form-label">Upload Options</label>
                                            <div class="form-check">
                                                <input class="form-check-input" type="checkbox" id="updateExistingStudents" checked>
                                                <label class="form-check-label" for="updateExistingStudents">
                                                    Update existing records
                                                </label>
                                            </div>
                                            <div class="form-check">
                                                <input class="form-check-input" type="checkbox" id="skipDuplicates" checked>
                                                <label class="form-check-label" for="skipDuplicates">
                                                    Skip duplicate matric numbers
                                                </label>
                                            </div>
                                        </div>
                                        
                                        <div class="progress mb-3" style="height: 25px; display: none;" id="studentUploadProgressContainer">
                                            <div id="studentUploadProgressBar" class="progress-bar progress-bar-striped progress-bar-animated" 
                                                 role="progressbar" style="width: 0%">0%</div>
                                        </div>
                                        
                                        <div id="studentUploadStatus" class="alert" style="display: none;"></div>
                                        
                                        <button class="btn btn-primary btn-lg w-100" onclick="uploadStudentData()" id="uploadStudentDataBtn">
                                            <i class="fas fa-upload me-2"></i>Upload Student Data
                                        </button>
                                        <button class="btn btn-outline-secondary btn-sm mt-2" onclick="downloadSample('student')">
                                            <i class="fas fa-download me-1"></i>Download Sample
                                        </button>
                                    </div>
                                </div>
                            </div>
                            
                            <!-- Certificate Records Upload -->
                            <div class="col-md-6 mb-4">
                                <div class="card upload-card h-100">
                                    <div class="card-body text-center">
                                        <div class="upload-icon text-success">
                                            <i class="fas fa-file-certificate"></i>
                                        </div>
                                        <h4 class="card-title">Certificate Records Upload</h4>
                                        <p class="card-text text-muted mb-4">
                                            Upload graduation details including grades, CGPA, and graduation dates.
                                        </p>
                                        
                                        <div class="upload-step">
                                            <h5><span class="upload-step-number">2</span> Format Requirements</h5>
                                            <div class="file-info">
                                                <small><strong>Required Columns:</strong> S/N, SessionName, MatricNum, Surname, Firstname, Othername, Programme, Cgpa, Grade, LevelName, Semester, DateGraduated, Phone, Email</small>
                                            </div>
                                            <div class="template-preview">
                                                <img src="https://via.placeholder.com/400x200/28a745/ffffff?text=Certificate+Records+Template" alt="Certificate Records Template" class="img-fluid">
                                            </div>
                                        </div>
                                        
                                        <div class="file-upload-area mb-3" id="certificateRecordsUploadArea" onclick="document.getElementById('certificateRecordsFile').click()">
                                            <i class="fas fa-cloud-upload-alt fa-3x text-success mb-3"></i>
                                            <h5>Drop Certificate Records Excel File Here</h5>
                                            <p class="text-muted">or click to browse</p>
                                            <small class="text-muted">Supports .xlsx and .xls files (Max 10MB)</small>
                                        </div>
                                        <input type="file" id="certificateRecordsFile" accept=".xlsx,.xls" style="display: none;">
                                        
                                        <div class="mb-3">
                                            <label class="form-label">Upload Options</label>
                                            <div class="form-check">
                                                <input class="form-check-input" type="checkbox" id="updateCertificateGrades" checked>
                                                <label class="form-check-label" for="updateCertificateGrades">
                                                    Update grades & graduation dates
                                                </label>
                                            </div>
                                            <div class="form-check">
                                                <input class="form-check-input" type="checkbox" id="generateCertificateIds" checked>
                                                <label class="form-check-label" for="generateCertificateIds">
                                                    Generate certificate IDs
                                                </label>
                                            </div>
                                        </div>
                                        
                                        <div class="progress mb-3" style="height: 25px; display: none;" id="certificateUploadProgressContainer">
                                            <div id="certificateUploadProgressBar" class="progress-bar progress-bar-striped progress-bar-animated" 
                                                 role="progressbar" style="width: 0%">0%</div>
                                        </div>
                                        
                                        <div id="certificateUploadStatus" class="alert" style="display: none;"></div>
                                        
                                        <button class="btn btn-success btn-lg w-100" onclick="uploadCertificateRecords()" id="uploadCertificateRecordsBtn">
                                            <i class="fas fa-upload me-2"></i>Upload Certificate Records
                                        </button>
                                        <button class="btn btn-outline-secondary btn-sm mt-2" onclick="downloadSample('certificate')">
                                            <i class="fas fa-download me-1"></i>Download Sample
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Upload History -->
                        <div class="card">
                            <div class="card-header">
                                <h5 class="mb-0"><i class="fas fa-history me-2"></i>Upload History</h5>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-striped table-hover">
                                        <thead>
                                            <tr>
                                                <th>Date & Time</th>
                                                <th>File Type</th>
                                                <th>Filename</th>
                                                <th>Records</th>
                                                <th>Status</th>
                                                <th>Actions</th>
                                            </tr>
                                        </thead>
                                        <tbody id="uploadHistory">
                                            <tr id="noUploadsRow">
                                                <td colspan="6" class="text-center text-muted py-4">
                                                    <i class="fas fa-inbox fa-2x mb-2"></i>
                                                    <p>No uploads yet</p>
                                                </td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Reports Section -->
        <div id="reportsSection" style="display: none;">
            <div class="row">
                <div class="col-12">
                    <div class="settings-panel">
                        <h4><i class="fas fa-chart-bar me-2"></i>Reports & Statistics</h4>
                        
                        <div class="alert alert-info mb-4">
                            <i class="fas fa-info-circle me-2"></i>
                            View comprehensive statistics and reports in the <a href="student_records.php" class="alert-link">Student Records</a> page.
                        </div>

                        <div class="row">
                            <div class="col-md-4">
                                <div class="stat-card stat-printed">
                                    <h3 id="printedCount">0</h3>
                                    <p>Certificates Printed</p>
                                    <i class="fas fa-check-circle fa-2x text-success"></i>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="stat-card stat-pending">
                                    <h3 id="pendingCount">0</h3>
                                    <p>Total Students</p>
                                    <i class="fas fa-users fa-2x text-warning"></i>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <div class="stat-card stat-error">
                                    <h3 id="errorCount">0</h3>
                                    <p>Upload Errors</p>
                                    <i class="fas fa-exclamation-triangle fa-2x text-danger"></i>
                                </div>
                            </div>
                        </div>

                        <div class="mt-4">
                            <h5>Recent Activities</h5>
                            <div class="alert alert-light" id="recentActivities" style="max-height: 200px; overflow-y: auto;">
                                <div class="text-center text-muted py-3">
                                    <i class="fas fa-history fa-2x mb-2"></i>
                                    <p>No recent activities</p>
                                </div>
                            </div>
                        </div>

                        <div class="mt-4">
                            <h5>Quick Actions</h5>
                            <div class="d-grid gap-2 d-md-block">
                                <a href="student_records.php" class="btn btn-primary me-2">
                                    <i class="fas fa-users me-2"></i>View All Students
                                </a>
                                <button class="btn btn-success" onclick="showSection('uploadDataSection')">
                                    <i class="fas fa-upload me-2"></i>Upload Data
                                </button>
                                <button class="btn btn-info" onclick="fetchStudents()">
                                    <i class="fas fa-sync me-2"></i>Refresh Students
                                </button>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Global variables
        let currentStudent = null;
        let allStudents = [];
        let displayedStudents = [];
        let studentLoadInProgress = false;
        let selectedStudentIdFromURL = null;
        let uploadHistory = JSON.parse(localStorage.getItem('uploadHistory') || '[]');

        // Check URL parameters on page load
        window.addEventListener('DOMContentLoaded', function() {
            const urlParams = new URLSearchParams(window.location.search);
            const studentId = urlParams.get('student_id');
            
            if (studentId) {
                console.log('Found student ID in URL:', studentId);
                selectedStudentIdFromURL = studentId;
                
                // Clean up URL
                const newUrl = window.location.pathname;
                window.history.replaceState({}, document.title, newUrl);
            }
            
            // Initialize the page
            initializePage();
        });

        // Initialize page - load students and check if we need to load a specific student
        async function initializePage() {
            // First load all students from database
            await fetchStudents();
            
            // After students are loaded, check if we need to load a specific student
            if (selectedStudentIdFromURL) {
                console.log('Loading student from URL:', selectedStudentIdFromURL);
                // Wait a bit for the student list to render
                setTimeout(() => {
                    loadSpecificStudent(selectedStudentIdFromURL);
                }, 500);
            }
        }

        // Load a specific student by ID
        async function loadSpecificStudent(studentId) {
            if (studentLoadInProgress) {
                console.log('Student load already in progress, waiting...');
                setTimeout(() => loadSpecificStudent(studentId), 100);
                return;
            }
            
            studentLoadInProgress = true;
            try {
                console.log('Loading student with ID:', studentId);
                
                // First, check if student exists in our loaded list
                const studentExists = allStudents.some(s => s.id == studentId);
                
                if (!studentExists) {
                    console.log('Student not in initial list, fetching directly from database...');
                    // Fetch student directly from database API
                    const response = await fetch(`api/get_student.php?id=${studentId}`);
                    if (response.ok) {
                        const student = await response.json();
                        if (!student.error) {
                            currentStudent = student;
                            updateCertificateDisplay(student);
                            console.log('Direct student load successful:', student.full_name);
                            return;
                        }
                    }
                }
                
                // Use the viewStudent function which handles the display
                await viewStudent(studentId);
                console.log('Student loaded via viewStudent function');
                
            } catch (error) {
                console.error('Error loading specific student:', error);
            } finally {
                studentLoadInProgress = false;
            }
        }

        // Helper function to determine certificate type
        function getCertificateType(student) {
            if (!student.certificate_id) return 'National Diploma';
            
            const certId = student.certificate_id.toLowerCase();
            if (certId.includes('hnd') || certId.includes('higher')) {
                return 'Higher National Diploma';
            } else if (certId.includes('nd') || certId.includes('national')) {
                return 'National Diploma';
            }
            return 'National Diploma';
        }

        // Helper function to safely encode data for URL
        function safeUrlEncode(data) {
            // Convert to JSON string
            const jsonString = JSON.stringify(data);
            // Base64 encode
            const base64String = btoa(jsonString);
            // URL encode to handle special characters
            return encodeURIComponent(base64String);
        }

        // Helper function to check if a string is base64
        function isBase64(str) {
            if (!str || str === '' || str.trim() === '') return false;
            
            // Check if it's a data URL
            if (str.startsWith('data:image')) {
                return true;
            }
            
            // Check if it's a valid base64 string
            try {
                return btoa(atob(str)) === str;
            } catch (err) {
                return false;
            }
        }

        // Helper function to convert base64 to data URL if needed
        function toDataURL(base64String, mimeType = 'image/jpeg') {
            if (!base64String) return null;
            
            if (base64String.startsWith('data:')) {
                return base64String;
            }
            
            return `data:${mimeType};base64,${base64String}`;
        }

        // Helper function to handle image URLs (base64 or regular) with proper error handling
        function getImageUrl(imagePath, placeholder = null, studentName = '') {
            if (!imagePath || imagePath.trim() === '' || imagePath === 'null' || imagePath === 'undefined') {
                return placeholder || 'data:image/svg+xml;utf8,<svg xmlns="http://www.w3.org/2000/svg" width="100" height="120" viewBox="0 0 100 120"><rect width="100" height="120" fill="%23f8f9fa"/><text x="50" y="60" font-family="Arial" font-size="10" text-anchor="middle" fill="%236c757d">Image not found</text></svg>';
            }
            
            if (imagePath.startsWith('data:image')) {
                // It's already a data URL
                return imagePath;
            } else if (imagePath.startsWith('http')) {
                // It's a regular URL
                return imagePath;
            } else if (isBase64(imagePath)) {
                // It's a base64 string without data URL prefix
                return toDataURL(imagePath);
            } else if (imagePath.includes('uploads/') || imagePath.includes('assets/')) {
                // It's a local file path
                return imagePath;
            }
            
            return placeholder || 'data:image/svg+xml;utf8,<svg xmlns="http://www.w3.org/2000/svg" width="100" height="120" viewBox="0 0 100 120"><rect width="100" height="120" fill="%23f8f9fa"/><text x="50" y="60" font-family="Arial" font-size="10" text-anchor="middle" fill="%236c757d">Image not found</text></svg>';
        }

        // Helper function to clean program name
        function cleanProgramName(programName) {
            if (!programName) return '[Program Name]';
            
            // Remove FULL TIME, PART TIME, PART TIME (WEEKEND), PART TIME (EVENING) and their variations
            let cleanedName = programName.replace(/\s*FULL\s*TIME\s*/gi, ' ');
            cleanedName = cleanedName.replace(/\s*PART\s*TIME\s*(\(WEEKEND\))?\s*/gi, ' ');
            cleanedName = cleanedName.replace(/\s*PART\s*TIME\s*(\(EVENING\))?\s*/gi, ' ');
            cleanedName = cleanedName.replace(/\s*\(WEEKEND\)\s*/gi, ' ');
            cleanedName = cleanedName.replace(/\s*\(EVENING\)\s*/gi, ' ');
            
            // Remove extra spaces and trim
            cleanedName = cleanedName.replace(/\s+/g, ' ').trim();
            
            return cleanedName;
        }

        // Navigation functions
        function showSection(sectionId) {
            document.getElementById('certificateSection').style.display = 'none';
            document.getElementById('uploadDataSection').style.display = 'none';
            document.getElementById('reportsSection').style.display = 'none';
            
            document.getElementById(sectionId).style.display = 'block';
            
            if (sectionId === 'reportsSection') {
                refreshReports();
            }
            
            if (sectionId === 'uploadDataSection') {
                loadUploadHistory();
            }
        }

        // Show loading spinner
        function showLoading(message = 'Loading...') {
            const spinner = document.getElementById('loadingSpinner');
            spinner.style.display = 'block';
            spinner.querySelector('div:last-child').textContent = message;
        }

        // Hide loading spinner
        function hideLoading() {
            document.getElementById('loadingSpinner').style.display = 'none';
        }

        // Fetch all students from database
        async function fetchStudents() {
            try {
                showLoading('Loading students from database...');
                
                // Fetch from your database API
                const response = await fetch('api/get_students.php');
                
                if (!response.ok) {
                    throw new Error(`HTTP error! status: ${response.status}`);
                }
                
                const data = await response.json();
                console.log('Database API Response:', data);
                
                if (data.error) {
                    throw new Error(data.error);
                }
                
                if (data.success && data.students && data.students.length > 0) {
                    // Store all students from database
                    allStudents = data.students;
                    
                    // Display only first 5 students initially
                    displayedStudents = allStudents.slice(0, 5);
                    displayStudents(displayedStudents);
                    
                    // Only auto-select first student if we're not loading a specific student
                    if (!selectedStudentIdFromURL && displayedStudents.length > 0) {
                        await viewStudent(displayedStudents[0].id);
                    }
                    
                    updateReportStats();
                    updateStudentCount();
                } else {
                    showNoStudentsMessage();
                }
            } catch (error) {
                console.error('Error fetching students from database:', error);
                showError('studentsContainer', 'Error loading students from database: ' + error.message);
                
                // Try alternative API endpoint
                await fetchAlternativeStudents();
            } finally {
                hideLoading();
            }
        }

        // Try alternative API endpoint
        async function fetchAlternativeStudents() {
            try {
                console.log('Trying alternative API endpoint...');
                const response = await fetch('api/direct_students.php');
                
                if (response.ok) {
                    const students = await response.json();
                    console.log('Alternative API response:', students);
                    
                    if (Array.isArray(students) && students.length > 0) {
                        allStudents = students;
                        displayedStudents = allStudents.slice(0, 20);
                        displayStudents(displayedStudents);
                        
                        if (!selectedStudentIdFromURL && displayedStudents.length > 0) {
                            await viewStudent(displayedStudents[0].id);
                        }
                        
                        updateReportStats();
                        updateStudentCount();
                    } else {
                        createSampleStudents();
                    }
                } else {
                    createSampleStudents();
                }
            } catch (error) {
                console.error('Alternative API also failed:', error);
                createSampleStudents();
            }
        }

        // Create sample students when database is not available
        function createSampleStudents() {
            console.log('Creating sample students...');
            allStudents = [
                {
                    id: 1,
                    student_id: "252302182",
                    full_name: "Seun Adebayo",
                    program: "Accountancy",
                    department: "School of Management",
                    grade: "Upper Credit",
                    graduation_date: "2023-05-15",
                    photo_path: "",
                    certificate_id: "GICT-CS-2023-001",
                    created_at: "2025-09-30 11:17:40"
                },
                {
                    id: 2,
                    student_id: "252305145",
                    full_name: "Fatima Bello",
                    program: "Electrical Engineering",
                    department: "School of Engineering",
                    grade: "Lower Credit",
                    graduation_date: "2023-05-16",
                    photo_path: "",
                    certificate_id: "GICT-EE-2023-002",
                    created_at: "2025-09-30 11:17:40"
                },
                {
                    id: 3,
                    student_id: "252602062",
                    full_name: "ISRAEL PITAN",
                    program: "Computer Science",
                    department: "School of Science and Technology",
                    grade: "Upper Credit",
                    graduation_date: "2023-05-17",
                    photo_path: "",
                    certificate_id: "GICT-BA-2023-003",
                    created_at: "2025-09-30 11:17:40"
                }
            ];
            
            displayedStudents = allStudents.slice(0, 20);
            displayStudents(displayedStudents);
            
            if (!selectedStudentIdFromURL && displayedStudents.length > 0) {
                viewStudent(displayedStudents[0].id);
            }
            
            updateReportStats();
            updateStudentCount();
        }

        // Display students in the list
        function displayStudents(students) {
            const container = document.getElementById('studentsContainer');
            
            if (students.length === 0) {
                container.innerHTML = `
                    <div class="alert alert-info">
                        <h5>No Students Found</h5>
                        <p>No student records match your search.</p>
                    </div>`;
                return;
            }
            
            container.innerHTML = students.map(student => {
                // Handle photo URL - check if it's base64
                const photoUrl = getImageUrl(student.photo_path, 'https://via.placeholder.com/50x50/1a237e/ffffff?text=Photo', student.full_name);
                
                // Check if this is the currently selected student
                const isSelected = currentStudent && currentStudent.id == student.id;
                const borderStyle = isSelected ? '4px solid #007bff' : '1px solid #ddd';
                
                return `
                    <div class="student-card" onclick="selectAndViewStudent(${student.id})" 
                         data-student-id="${student.id}"
                         style="border-left: ${borderStyle};">
                        <div class="d-flex align-items-center">
                            <img src="${photoUrl}" 
                                 alt="${student.full_name}" 
                                 class="student-avatar me-3"
                                 onerror="this.onerror=null; this.src='https://via.placeholder.com/50x50/1a237e/ffffff?text=Photo'">
                            <div class="flex-grow-1">
                                <h6 class="mb-1">${student.full_name}</h6>
                                <small class="text-muted">${student.program}</small><br>
                                <small class="text-primary">Matric: ${student.student_id}</small><br>
                                <small class="text-success">Grade: ${student.grade}</small>
                            </div>
                            <span class="badge ${getGradeBadgeClass(student.grade)}">${student.grade}</span>
                        </div>
                    </div>
                `;
            }).join('');
            
            updateStudentCount();
        }

        function updateStudentCount() {
            document.getElementById('studentCount').textContent = displayedStudents.length;
        }

        function getGradeBadgeClass(grade) {
            switch(grade) {
                case 'Distinction': return 'bg-success';
                case 'Upper Credit': return 'bg-primary';
                case 'Lower Credit': return 'bg-warning';
                case 'Pass': return 'bg-info';
                default: return 'bg-secondary';
            }
        }

        function showNoStudentsMessage() {
            document.getElementById('studentsContainer').innerHTML = `
                <div class="alert alert-info">
                    <h5>No Students Found</h5>
                    <p>No student records were found in the database.</p>
                    <button class="btn btn-sm btn-primary" onclick="createSampleStudents()">
                        Load Sample Data
                    </button>
                </div>`;
            updateStudentCount();
        }

        function showError(containerId, message) {
            document.getElementById(containerId).innerHTML = `
                <div class="alert alert-danger">
                    <h5>Error</h5>
                    <p>${message}</p>
                    <small>Check browser console for details</small>
                </div>`;
            updateStudentCount();
        }

        // Select and view student (wrapper function to prevent race conditions)
        async function selectAndViewStudent(studentId) {
            if (studentLoadInProgress) {
                console.log('Another student load in progress, please wait...');
                return;
            }
            
            await viewStudent(studentId);
        }

        // View student certificate - Fetches from database
        async function viewStudent(studentId) {
            if (studentLoadInProgress) {
                console.log('Student load already in progress for ID:', studentId);
                return;
            }
            
            studentLoadInProgress = true;
            
            try {
                showLoading('Loading student data from database...');
                
                // Fetch student data from database API
                const response = await fetch(`api/get_student.php?id=${studentId}`);
                
                if (!response.ok) {
                    throw new Error(`HTTP error! status: ${response.status}`);
                }
                
                const student = await response.json();
                console.log('Student data from database:', student);
                
                if (student.error) {
                    throw new Error(student.error);
                }
                
                // Set as current student
                currentStudent = student;
                
                // Update certificate display
                updateCertificateDisplay(student);
                
                // Update student list highlighting
                updateStudentListHighlight(studentId);
                
                console.log('Successfully loaded student from database:', student.full_name);
                
            } catch (error) {
                console.error('Error fetching student from database:', error);
                
                // Fallback to local student data if database fetch fails
                const localStudent = allStudents.find(s => s.id == studentId);
                if (localStudent) {
                    currentStudent = localStudent;
                    updateCertificateDisplay(localStudent);
                    updateStudentListHighlight(studentId);
                    console.log('Using cached student data:', localStudent);
                } else {
                    alert('Error loading student data from database: ' + error.message);
                }
            } finally {
                hideLoading();
                studentLoadInProgress = false;
            }
        }

        // Update student list highlighting
        function updateStudentListHighlight(studentId) {
            // Remove all highlights
            document.querySelectorAll('.student-card').forEach(card => {
                card.style.borderLeft = '1px solid #ddd';
            });
            
            // Add highlight to selected student
            const selectedCard = document.querySelector(`[data-student-id="${studentId}"]`);
            if (selectedCard) {
                selectedCard.style.borderLeft = '4px solid #007bff';
            }
        }

        // Update certificate display with student data
        function updateCertificateDisplay(student) {
            try {
                console.log('Updating certificate display for:', student.full_name);
                
                // Get full name from full_name column and convert to capital letters
                const studentName = student.full_name ? student.full_name.toUpperCase() : '[Student Name]';
                document.getElementById('studentName').textContent = studentName;
                
                // Get matric number from student_id column
                document.getElementById('studentMatric').textContent = student.student_id || '[Matric Number]';
                
                // Get grade from grade column
                document.getElementById('studentFinalGrade').textContent = student.grade || '[Grade]';
                
                // Get program name from program column, clean it, and convert to capital letters
                const programName = student.program ? cleanProgramName(student.program).toUpperCase() : '[Program Name]';
                document.getElementById('programName').textContent = programName;
                
                // Get certificate type from certificate_id column
                const certificateType = getCertificateType(student);
                document.getElementById('certificateTypeDisplay').textContent = certificateType;
                
                // Use certificate_id column for certificate number
                const certificateNumber = student.certificate_id || 'Cert. no not found';
                document.getElementById('certificateNumber').textContent = certificateNumber;
                
                // Get date from graduation_date column
                let graduationDate = student.graduation_date;
                if (!graduationDate) {
                    // Fallback to current date if no graduation date
                    graduationDate = new Date().toISOString().split('T')[0];
                }
                
                const formattedDate = new Date(graduationDate).toLocaleDateString('en-US', { 
                    year: 'numeric', 
                    month: 'long', 
                    day: 'numeric' 
                });
                document.getElementById('issueDateDisplay').textContent = formattedDate;
                
                // Update student photo - handle base64 images with error handling
                const photoContainer = document.getElementById('studentPhotoContainer');
                const photoUrl = getImageUrl(student.photo_path, null, student.full_name);
                
                // Create image element with error handling
                const imgHtml = `
                    <div class="image-container" style="position: relative;">
                        <img src="${photoUrl}" alt="${student.full_name}" 
                             style="width: 100px; height: 120px; object-fit: cover; border: 1px solid #ccc; display: none;"
                             onload="this.style.display='block'; this.parentElement.querySelector('.image-not-found').style.display='none';"
                             onerror="this.style.display='none'; this.parentElement.querySelector('.image-not-found').style.display='flex';">
                        <div class="image-not-found" style="display: flex;">Image not found</div>
                    </div>
                    <div class="certificate-number-under">${certificateNumber}</div>
                `;
                
                photoContainer.innerHTML = imgHtml;
                
                // Generate QR code
                updateQRCode(student, certificateNumber);
                
            } catch (error) {
                console.error('Error updating certificate display:', error);
                // Show error message in photo container
                const photoContainer = document.getElementById('studentPhotoContainer');
                photoContainer.innerHTML = `
                    <div class="image-not-found">Error loading image</div>
                    <div class="certificate-number-under">Cert. no not found</div>
                `;
            }
        }

        // Update QR code - Production version
        async function updateQRCode(student, certificateNumber) {
            const qrContainer = document.getElementById('qrCodeContainer');
            
            try {
                // Prepare student data for QR code
                const studentData = {
                    full_name: student.full_name || '',
                    matric_no: student.student_id || '',
                    program: student.program || '',
                    certificate_id: certificateNumber || '',
                    grade: student.grade || '',
                    certificate_type: getCertificateType(student),
                    graduation_date: student.graduation_date || '',
                    student_id: student.id || ''
                };
                
                // Convert to JSON string
                const jsonString = JSON.stringify(studentData);
                console.log('QR Code JSON Data:', jsonString);
                
                // Get the current domain
                const protocol = window.location.protocol;
                const host = window.location.host;
                const baseUrl = `${protocol}//${host}`;
                
                // Create simple URL parameters
                const params = new URLSearchParams();
                params.append('full_name', studentData.full_name);
                params.append('matric_no', studentData.matric_no);
                params.append('program', studentData.program);
                params.append('certificate_id', studentData.certificate_id);
                params.append('grade', studentData.grade);
                params.append('certificate_type', studentData.certificate_type);
                params.append('student_id', studentData.student_id);
                
                const queryString = params.toString();
                
                // Generate QR code with simple URL
                const qrContent = `${baseUrl}/verify_certificate.php?${queryString}`;
                console.log('QR Code URL:', qrContent);
                
                // Use QR code generator API
                const qrUrl = `https://api.qrserver.com/v1/create-qr-code/?size=100x100&data=${encodeURIComponent(qrContent)}`;
                
                qrContainer.innerHTML = `<img src="${qrUrl}" alt="Verification QR Code" style="width: 100px; height: 100px; object-fit: contain;">`;
                
            } catch (error) {
                console.error('Error generating QR code:', error);
                generateFallbackQRCode(student, qrContainer, certificateNumber);
            }
        }

        // Generate fallback QR code
        function generateFallbackQRCode(student, qrContainer, certificateNumber) {
            // Get the current domain
            const protocol = window.location.protocol;
            const host = window.location.host;
            const baseUrl = `${protocol}//${host}`;
            
            // Create simple URL with student data
            const params = new URLSearchParams();
            params.append('full_name', student.full_name || '');
            params.append('matric_no', student.student_id || '');
            params.append('program', student.program || '');
            params.append('certificate_id', certificateNumber || '');
            params.append('grade', student.grade || '');
            params.append('certificate_type', getCertificateType(student));
            params.append('student_id', student.id || '');
            
            const queryString = params.toString();
            const qrContent = `${baseUrl}/verify_certificate1.php?${queryString}`;
            
            const qrUrl = `https://api.qrserver.com/v1/create-qr-code/?size=100x100&data=${encodeURIComponent(qrContent)}`;
            qrContainer.innerHTML = `<img src="${qrUrl}" alt="Verification Link QR Code" style="width: 100px; height: 100px; object-fit: contain;">`;
        }

        // Search students across all fields - searches entire database
        async function searchStudents() {
            const searchTerm = document.getElementById('searchInput').value.trim();
            
            if (searchTerm === '') {
                // Show first 20 students when search is cleared
                displayedStudents = allStudents.slice(0, 20);
                displayStudents(displayedStudents);
                return;
            }
            
            try {
                showLoading('Searching database...');
                
                // Use the database search API
                const response = await fetch(`api/get_students.php?search=${encodeURIComponent(searchTerm)}`);
                
                if (!response.ok) {
                    throw new Error(`HTTP error! status: ${response.status}`);
                }
                
                const data = await response.json();
                console.log('Search results from database:', data);
                
                if (data.success && data.students) {
                    // These are the search results from the entire database
                    const searchResults = data.students;
                    
                    // Update displayedStudents with search results
                    displayedStudents = searchResults;
                    
                    if (searchResults.length > 0) {
                        // Display all search results
                        displayStudents(searchResults);
                        
                        // Show the first result
                        await viewStudent(searchResults[0].id);
                    } else {
                        // Show message if no results
                        document.getElementById('studentsContainer').innerHTML = `
                            <div class="alert alert-info">
                                <h5>No Results Found</h5>
                                <p>No students match your search for "${searchTerm}"</p>
                            </div>`;
                        updateStudentCount();
                    }
                } else {
                    // Fallback to local search if API returns error
                    fallbackLocalSearch(searchTerm);
                }
            } catch (error) {
                console.error('Search API error:', error);
                // Fallback to local search if API fails
                fallbackLocalSearch(searchTerm);
            } finally {
                hideLoading();
            }
        }

        // Fallback function for local search when API fails
        function fallbackLocalSearch(searchTerm) {
            console.log('Using fallback local search');
            
            const searchLower = searchTerm.toLowerCase();
            const searchResults = allStudents.filter(student => {
                return (
                    (student.full_name && student.full_name.toLowerCase().includes(searchLower)) ||
                    (student.student_id && student.student_id.toLowerCase().includes(searchLower)) ||
                    (student.program && student.program.toLowerCase().includes(searchLower)) ||
                    (student.grade && student.grade.toLowerCase().includes(searchLower)) ||
                    (student.certificate_id && student.certificate_id.toLowerCase().includes(searchLower)) ||
                    (student.department && student.department.toLowerCase().includes(searchLower)) ||
                    (student.email && student.email.toLowerCase().includes(searchLower)) ||
                    (student.phone && student.phone.includes(searchTerm))
                );
            });
            
            displayedStudents = searchResults;
            displayStudents(searchResults);
            
            if (searchResults.length > 0) {
                viewStudent(searchResults[0].id);
            } else {
                document.getElementById('studentsContainer').innerHTML = `
                    <div class="alert alert-info">
                        <h5>No Results Found</h5>
                        <p>No students match your search for "${searchTerm}"</p>
                    </div>`;
                updateStudentCount();
            }
        }

        // Print current certificate
        function printCurrentCertificate() {
            if (!currentStudent) {
                alert('Please select a student first');
                return;
            }
            
            // Hide non-printable elements
            const nonPrintable = document.querySelectorAll('.no-print');
            nonPrintable.forEach(el => el.style.display = 'none');
            
            // Print only the certificate
            window.print();
            
            // Restore non-printable elements
            setTimeout(() => {
                nonPrintable.forEach(el => el.style.display = '');
            }, 100);
        }

        // Upload Student Data
        async function uploadStudentData() {
            const fileInput = document.getElementById('studentDataFile');
            const file = fileInput.files[0];
            
            if (!file) {
                alert('Please select an Excel file for Student Data');
                return;
            }
            
            if (!file.name.match(/\.(xlsx|xls)$/)) {
                alert('Please select an Excel file (.xlsx or .xls)');
                return;
            }
            
            const formData = new FormData();
            formData.append('studentDataFile', file);
            formData.append('updateExisting', document.getElementById('updateExistingStudents').checked ? '1' : '0');
            formData.append('skipDuplicates', document.getElementById('skipDuplicates').checked ? '1' : '0');
            
            const uploadBtn = document.getElementById('uploadStudentDataBtn');
            const progressContainer = document.getElementById('studentUploadProgressContainer');
            const progressBar = document.getElementById('studentUploadProgressBar');
            const statusDiv = document.getElementById('studentUploadStatus');
            
            uploadBtn.disabled = true;
            progressContainer.style.display = 'block';
            progressBar.style.width = '0%';
            progressBar.textContent = '0%';
            
            try {
                const response = await fetch('api/upload_student_data.php', {
                    method: 'POST',
                    body: formData
                });
                
                if (!response.ok) {
                    throw new Error(`HTTP error! status: ${response.status}`);
                }
                
                const result = await response.json();
                
                progressBar.style.width = '100%';
                progressBar.textContent = '100%';
                
                statusDiv.style.display = 'block';
                
                if (result.success) {
                    statusDiv.className = 'alert alert-success';
                    statusDiv.innerHTML = `
                        <h5><i class="fas fa-check-circle me-2"></i>Upload Successful!</h5>
                        <p>${result.message}</p>
                        <p><strong>Total Records:</strong> ${result.total_records || 0}</p>
                        <p><strong>Imported:</strong> ${result.imported || 0}</p>
                        <p><strong>Updated:</strong> ${result.updated || 0}</p>
                        <p><strong>Skipped:</strong> ${result.skipped || 0}</p>
                        ${result.errors && result.errors.length > 0 ? 
                            `<p><strong>Errors:</strong> ${result.errors.length}</p>
                             <small class="text-muted">Check console for details</small>` : ''}
                    `;
                    
                    // Add to upload history
                    addToUploadHistory({
                        type: 'Student Data',
                        filename: file.name,
                        records: result.total_records || 0,
                        status: 'Success',
                        timestamp: new Date().toISOString(),
                        details: result
                    });
                    
                    // Refresh student list from database
                    await fetchStudents();
                } else {
                    statusDiv.className = 'alert alert-danger';
                    statusDiv.innerHTML = `
                        <h5><i class="fas fa-exclamation-circle me-2"></i>Upload Failed</h5>
                        <p>${result.message || 'Unknown error occurred'}</p>
                        ${result.errors ? `<small>${result.errors.join('<br>')}</small>` : ''}
                    `;
                    
                    // Add to upload history with error
                    addToUploadHistory({
                        type: 'Student Data',
                        filename: file.name,
                        records: 0,
                        status: 'Failed',
                        timestamp: new Date().toISOString(),
                        details: result
                    });
                }
            } catch (error) {
                console.error('Upload error:', error);
                statusDiv.style.display = 'block';
                statusDiv.className = 'alert alert-danger';
                statusDiv.innerHTML = `
                    <h5><i class="fas fa-exclamation-circle me-2"></i>Upload Error</h5>
                    <p>${error.message}</p>
                `;
                
                // Add to upload history with error
                addToUploadHistory({
                    type: 'Student Data',
                    filename: file.name,
                    records: 0,
                    status: 'Error',
                    timestamp: new Date().toISOString(),
                    details: { error: error.message }
                });
            } finally {
                uploadBtn.disabled = false;
                setTimeout(() => {
                    progressContainer.style.display = 'none';
                }, 2000);
            }
        }

        // Upload Certificate Records
        async function uploadCertificateRecords() {
            const fileInput = document.getElementById('certificateRecordsFile');
            const file = fileInput.files[0];
            
            if (!file) {
                alert('Please select an Excel file for Certificate Records');
                return;
            }
            
            if (!file.name.match(/\.(xlsx|xls)$/)) {
                alert('Please select an Excel file (.xlsx or .xls)');
                return;
            }
            
            const formData = new FormData();
            formData.append('certificateRecordsFile', file);
            formData.append('updateGrades', document.getElementById('updateCertificateGrades').checked ? '1' : '0');
            formData.append('generateIds', document.getElementById('generateCertificateIds').checked ? '1' : '0');
            
            const uploadBtn = document.getElementById('uploadCertificateRecordsBtn');
            const progressContainer = document.getElementById('certificateUploadProgressContainer');
            const progressBar = document.getElementById('certificateUploadProgressBar');
            const statusDiv = document.getElementById('certificateUploadStatus');
            
            uploadBtn.disabled = true;
            progressContainer.style.display = 'block';
            progressBar.style.width = '0%';
            progressBar.textContent = '0%';
            
            try {
                const response = await fetch('api/upload_certificate_records.php', {
                    method: 'POST',
                    body: formData
                });
                
                if (!response.ok) {
                    throw new Error(`HTTP error! status: ${response.status}`);
                }
                
                const result = await response.json();
                
                progressBar.style.width = '100%';
                progressBar.textContent = '100%';
                
                statusDiv.style.display = 'block';
                
                if (result.success) {
                    statusDiv.className = 'alert alert-success';
                    statusDiv.innerHTML = `
                        <h5><i class="fas fa-check-circle me-2"></i>Upload Successful!</h5>
                        <p>${result.message}</p>
                        <p><strong>Total Records:</strong> ${result.total_records || 0}</p>
                        <p><strong>Processed:</strong> ${result.processed || 0}</p>
                        <p><strong>Updated:</strong> ${result.updated || 0}</p>
                        <p><strong>Created:</strong> ${result.created || 0}</p>
                        ${result.errors && result.errors.length > 0 ? 
                            `<p><strong>Errors:</strong> ${result.errors.length}</p>
                             <small class="text-muted">Check console for details</small>` : ''}
                    `;
                    
                    // Add to upload history
                    addToUploadHistory({
                        type: 'Certificate Records',
                        filename: file.name,
                        records: result.total_records || 0,
                        status: 'Success',
                        timestamp: new Date().toISOString(),
                        details: result
                    });
                    
                    // Refresh student list from database
                    await fetchStudents();
                } else {
                    statusDiv.className = 'alert alert-danger';
                    statusDiv.innerHTML = `
                        <h5><i class="fas fa-exclamation-circle me-2"></i>Upload Failed</h5>
                        <p>${result.message || 'Unknown error occurred'}</p>
                        ${result.errors ? `<small>${result.errors.join('<br>')}</small>` : ''}
                    `;
                    
                    // Add to upload history with error
                    addToUploadHistory({
                        type: 'Certificate Records',
                        filename: file.name,
                        records: 0,
                        status: 'Failed',
                        timestamp: new Date().toISOString(),
                        details: result
                    });
                }
            } catch (error) {
                console.error('Upload error:', error);
                statusDiv.style.display = 'block';
                statusDiv.className = 'alert alert-danger';
                statusDiv.innerHTML = `
                    <h5><i class="fas fa-exclamation-circle me-2"></i>Upload Error</h5>
                    <p>${error.message}</p>
                `;
                
                // Add to upload history with error
                addToUploadHistory({
                    type: 'Certificate Records',
                    filename: file.name,
                    records: 0,
                    status: 'Error',
                    timestamp: new Date().toISOString(),
                    details: { error: error.message }
                });
            } finally {
                uploadBtn.disabled = false;
                setTimeout(() => {
                    progressContainer.style.display = 'none';
                }, 2000);
            }
        }

        // Download sample file
        function downloadSample(type) {
            if (type === 'student') {
                window.open('api/download_sample.php?type=student', '_blank');
            } else if (type === 'certificate') {
                window.open('api/download_sample.php?type=certificate', '_blank');
            }
        }

        // Add to upload history
        function addToUploadHistory(record) {
            uploadHistory.unshift(record);
            
            // Keep only last 20 records
            if (uploadHistory.length > 20) {
                uploadHistory = uploadHistory.slice(0, 20);
            }
            
            // Save to localStorage
            localStorage.setItem('uploadHistory', JSON.stringify(uploadHistory));
            
            // Update display
            loadUploadHistory();
        }

        // Load upload history
        function loadUploadHistory() {
            const tbody = document.getElementById('uploadHistory');
            
            if (uploadHistory.length === 0) {
                tbody.innerHTML = `
                    <tr id="noUploadsRow">
                        <td colspan="6" class="text-center text-muted py-4">
                            <i class="fas fa-inbox fa-2x mb-2"></i>
                            <p>No uploads yet</p>
                        </td>
                    </tr>
                `;
                return;
            }
            
            tbody.innerHTML = uploadHistory.map((record, index) => `
                <tr>
                    <td>${new Date(record.timestamp).toLocaleString()}</td>
                    <td><span class="badge ${record.type === 'Student Data' ? 'bg-primary' : 'bg-success'}">${record.type}</span></td>
                    <td><small>${record.filename}</small></td>
                    <td>${record.records}</td>
                    <td>
                        <span class="badge ${record.status === 'Success' ? 'bg-success' : record.status === 'Failed' ? 'bg-danger' : 'bg-warning'}">
                            ${record.status}
                        </span>
                    </td>
                    <td>
                        <button class="btn btn-sm btn-outline-info" onclick="viewUploadDetails(${index})" title="View Details">
                            <i class="fas fa-eye"></i>
                        </button>
                        ${record.status === 'Success' ? `
                        <button class="btn btn-sm btn-outline-success" onclick="refreshAfterUpload(${index})" title="Refresh Data">
                            <i class="fas fa-sync"></i>
                        </button>
                        ` : ''}
                    </td>
                </tr>
            `).join('');
        }

        // View upload details
        function viewUploadDetails(index) {
            const record = uploadHistory[index];
            const details = JSON.stringify(record.details, null, 2);
            
            alert(`Upload Details:\n\nType: ${record.type}\nFilename: ${record.filename}\nStatus: ${record.status}\n\nDetails:\n${details}`);
        }

        // Refresh after upload
        function refreshAfterUpload(index) {
            const record = uploadHistory[index];
            
            if (record.status === 'Success') {
                fetchStudents();
                alert('Refreshing student data from database...');
            }
        }

        // Setup drag and drop
        function setupDragAndDrop() {
            const studentUploadArea = document.getElementById('studentDataUploadArea');
            const certificateUploadArea = document.getElementById('certificateRecordsUploadArea');
            
            ['dragenter', 'dragover', 'dragleave', 'drop'].forEach(eventName => {
                [studentUploadArea, certificateUploadArea].forEach(area => {
                    area.addEventListener(eventName, preventDefaults, false);
                });
            });
            
            function preventDefaults(e) {
                e.preventDefault();
                e.stopPropagation();
            }
            
            ['dragenter', 'dragover'].forEach(eventName => {
                studentUploadArea.addEventListener(eventName, highlightStudent, false);
                certificateUploadArea.addEventListener(eventName, highlightCertificate, false);
            });
            
            ['dragleave', 'drop'].forEach(eventName => {
                studentUploadArea.addEventListener(eventName, unhighlightStudent, false);
                certificateUploadArea.addEventListener(eventName, unhighlightCertificate, false);
            });
            
            studentUploadArea.addEventListener('drop', handleStudentDrop, false);
            certificateUploadArea.addEventListener('drop', handleCertificateDrop, false);
            
            function highlightStudent() {
                studentUploadArea.classList.add('dragover');
            }
            
            function unhighlightStudent() {
                studentUploadArea.classList.remove('dragover');
            }
            
            function highlightCertificate() {
                certificateUploadArea.classList.add('dragover');
            }
            
            function unhighlightCertificate() {
                certificateUploadArea.classList.remove('dragover');
            }
            
            function handleStudentDrop(e) {
                const dt = e.dataTransfer;
                const files = dt.files;
                
                if (files.length > 0 && files[0].name.match(/\.(xlsx|xls)$/)) {
                    document.getElementById('studentDataFile').files = files;
                    studentUploadArea.innerHTML = `
                        <i class="fas fa-check-circle fa-3x text-success mb-3"></i>
                        <h5>File Ready: ${files[0].name}</h5>
                        <p class="text-muted">Click "Upload Student Data" to proceed</p>
                        <small class="text-muted">Size: ${(files[0].size / 1024 / 1024).toFixed(2)} MB</small>
                    `;
                } else {
                    alert('Please drop an Excel file (.xlsx or .xls)');
                }
            }
            
            function handleCertificateDrop(e) {
                const dt = e.dataTransfer;
                const files = dt.files;
                
                if (files.length > 0 && files[0].name.match(/\.(xlsx|xls)$/)) {
                    document.getElementById('certificateRecordsFile').files = files;
                    certificateUploadArea.innerHTML = `
                        <i class="fas fa-check-circle fa-3x text-success mb-3"></i>
                        <h5>File Ready: ${files[0].name}</h5>
                        <p class="text-muted">Click "Upload Certificate Records" to proceed</p>
                        <small class="text-muted">Size: ${(files[0].size / 1024 / 1024).toFixed(2)} MB</small>
                    `;
                } else {
                    alert('Please drop an Excel file (.xlsx or .xls)');
                }
            }
        }

        // Reports functions
        function updateReportStats() {
            const totalStudents = allStudents.length;
            const printedCount = allStudents.filter(s => s.printed).length;
            const uploadErrors = uploadHistory.filter(record => record.status === 'Error' || record.status === 'Failed').length;
            
            document.getElementById('printedCount').textContent = printedCount;
            document.getElementById('pendingCount').textContent = totalStudents;
            document.getElementById('errorCount').textContent = uploadErrors;
        }

        function refreshReports() {
            updateReportStats();
            
            // Update recent activities
            const recentActivities = document.getElementById('recentActivities');
            if (uploadHistory.length > 0) {
                let activitiesHtml = '';
                const recentUploads = uploadHistory.slice(0, 5);
                
                recentUploads.forEach((record, index) => {
                    const timeAgo = getTimeAgo(new Date(record.timestamp));
                    activitiesHtml += `
                        <div class="mb-2 ${index < recentUploads.length - 1 ? 'border-bottom pb-2' : ''}">
                            <div class="d-flex justify-content-between">
                                <span class="fw-bold">${record.type}</span>
                                <span class="badge ${record.status === 'Success' ? 'bg-success' : record.status === 'Failed' ? 'bg-danger' : 'bg-warning'}">
                                    ${record.status}
                                </span>
                            </div>
                            <small class="text-muted">${record.filename}</small><br>
                            <small class="text-muted">${timeAgo} • ${record.records} records</small>
                        </div>
                    `;
                });
                
                recentActivities.innerHTML = activitiesHtml;
            } else {
                recentActivities.innerHTML = `
                    <div class="text-center text-muted py-3">
                        <i class="fas fa-history fa-2x mb-2"></i>
                        <p>No recent activities</p>
                    </div>
                `;
            }
        }

        function getTimeAgo(date) {
            const now = new Date();
            const diffMs = now - date;
            const diffSec = Math.round(diffMs / 1000);
            const diffMin = Math.round(diffSec / 60);
            const diffHour = Math.round(diffMin / 60);
            const diffDay = Math.round(diffHour / 24);
            
            if (diffSec < 60) return 'just now';
            if (diffMin < 60) return `${diffMin} minute${diffMin > 1 ? 's' : ''} ago`;
            if (diffHour < 24) return `${diffHour} hour${diffHour > 1 ? 's' : ''} ago`;
            if (diffDay < 30) return `${diffDay} day${diffDay > 1 ? 's' : ''} ago`;
            
            return date.toLocaleDateString();
        }

        // Initialize when page loads
        document.addEventListener('DOMContentLoaded', function() {
            setupDragAndDrop();
            
            document.getElementById('searchInput').addEventListener('keypress', function(e) {
                if (e.key === 'Enter') {
                    searchStudents();
                }
            });
            
            // Setup file input change handlers
            document.getElementById('studentDataFile').addEventListener('change', function(e) {
                if (e.target.files.length > 0) {
                    const file = e.target.files[0];
                    const uploadArea = document.getElementById('studentDataUploadArea');
                    uploadArea.innerHTML = `
                        <i class="fas fa-check-circle fa-3x text-success mb-3"></i>
                        <h5>File Selected: ${file.name}</h5>
                        <p class="text-muted">Click "Upload Student Data" to proceed</p>
                        <small class="text-muted">Size: ${(file.size / 1024 / 1024).toFixed(2)} MB</small>
                    `;
                }
            });
            
            document.getElementById('certificateRecordsFile').addEventListener('change', function(e) {
                if (e.target.files.length > 0) {
                    const file = e.target.files[0];
                    const uploadArea = document.getElementById('certificateRecordsUploadArea');
                    uploadArea.innerHTML = `
                        <i class="fas fa-check-circle fa-3x text-success mb-3"></i>
                        <h5>File Selected: ${file.name}</h5>
                        <p class="text-muted">Click "Upload Certificate Records" to proceed</p>
                        <small class="text-muted">Size: ${(file.size / 1024 / 1024).toFixed(2)} MB</small>
                    `;
                }
            });
        });
    </script>
</body>
</html>