<?php
require_once 'config/database.php';
require_once 'vendor/autoload.php'; // For PhpSpreadsheet


$message = "";
$status = "";

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['cert_file'])) {
    $file = $_FILES['cert_file'];
    
    if ($file['error'] === UPLOAD_ERR_OK) {
        $handle = fopen($file['tmp_name'], "r");
        
        // Read header to verify format
        $headers = fgetcsv($handle);
        
        // Optimization for large files
        set_time_limit(0); 
        ini_set('memory_limit', '512M');

        try {
            $db = Database::getInstance();
            $conn = $db->getConn();
            $conn->beginTransaction();

            // SQL for Graduation Certificate Data
            $sql = "INSERT INTO students 
                    (student_id, session_name, school_name, program_type, program, 
                     cgpa, grade, certificate_id, full_name, surname, firstname, othername, 
                     prog_grade, graduation_date, day, month, year, data_source,
                     department) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'batch_upload', ?)
                    ON DUPLICATE KEY UPDATE 
                    session_name = VALUES(session_name),
                    school_name = VALUES(school_name),
                    program_type = VALUES(program_type),
                    program = VALUES(program),
                    cgpa = VALUES(cgpa),
                    grade = VALUES(grade),
                    certificate_id = VALUES(certificate_id),
                    full_name = VALUES(full_name),
                    surname = VALUES(surname),
                    firstname = VALUES(firstname),
                    othername = VALUES(othername),
                    prog_grade = VALUES(prog_grade),
                    graduation_date = VALUES(graduation_date),
                    day = VALUES(day),
                    month = VALUES(month),
                    year = VALUES(year),
                    department = VALUES(department),
                    last_updated = CURRENT_TIMESTAMP";
            
            $stmt = $conn->prepare($sql);
            $count = 0;
            $inserted = 0;
            $updated = 0;

            while (($data = fgetcsv($handle)) !== FALSE) {
                // Skip if no Matric Number (column 8)
                if (empty($data[8])) continue;

                // Format Grade
                $rawGrade = strtolower(trim($data[6]));
                $formattedGrade = 'Pass';
                if (strpos($rawGrade, 'distinction') !== false) {
                    $formattedGrade = 'Distinction';
                } elseif (strpos($rawGrade, 'upper') !== false) {
                    $formattedGrade = 'Upper Credit';
                } elseif (strpos($rawGrade, 'lower') !== false) {
                    $formattedGrade = 'Lower Credit';
                }

                // Convert Date
                $rawDate = trim($data[14]);
                $gradDate = date('Y-m-d', strtotime($rawDate));

                // Check if student exists
                $studentId = trim($data[8]);
                $checkSql = "SELECT id FROM students WHERE student_id = ?";
                $checkStmt = $conn->prepare($checkSql);
                $checkStmt->execute([$studentId]);
                $exists = $checkStmt->fetch();

                // Execute insert/update
                $stmt->execute([
                    $studentId,                    // student_id
                    trim($data[1]),                // session_name
                    trim($data[2]),                // school_name
                    trim($data[3]),                // program_type
                    trim($data[4]),                // program
                    (float) trim($data[5]),        // cgpa
                    $formattedGrade,               // grade
                    trim($data[7]),                // certificate_id
                    trim($data[9]),                // full_name
                    trim($data[11]),               // surname
                    trim($data[12]),               // firstname
                    trim($data[13]),               // othername
                    trim($data[10]),               // prog_grade
                    $gradDate,                     // graduation_date
                    (int) trim($data[15]),         // day
                    trim($data[16]),               // month
                    (int) trim($data[17]),         // year
                    trim($data[2])                 // department (same as school_name)
                ]);

                $count++;
                if ($exists) {
                    $updated++;
                } else {
                    $inserted++;
                }
            }

            $conn->commit();
            $message = "✅ Successfully processed " . number_format($count) . " graduation records!<br>";
            $message .= "• New records: " . number_format($inserted) . "<br>";
            $message .= "• Updated records: " . number_format($updated);
            $status = "success";
            
        } catch (Exception $e) {
            if (isset($conn)) $conn->rollBack();
            $message = "❌ Error: " . $e->getMessage();
            $status = "danger";
        }
        fclose($handle);
    } else {
        $message = "❌ Upload failed. Error code: " . $file['error'];
        $status = "danger";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Graduation Certificate Upload</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>
        body { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); min-height: 100vh; padding: 20px; font-family: "Times New Roman", serif; }
        .upload-card { max-width: 800px; margin: 40px auto; background: white; padding: 40px; border-radius: 20px; box-shadow: 0 20px 60px rgba(0,0,0,0.3); }
        .card-header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border-radius: 15px 15px 0 0; padding: 25px; margin: -40px -40px 30px -40px; }
        .csv-format { background: #f8f9fa; padding: 15px; border-radius: 10px; margin: 20px 0; font-family: monospace; font-size: 12px; }
    </style>
</head>
<body>
    <div class="container">
        <div class="upload-card">
            <div class="card-header text-center">
                <h3><i class="fas fa-graduation-cap me-2"></i>Graduation Certificate Upload</h3>
                <p class="mb-0">Upload "Graduated Student Cert. List.csv" file</p>
            </div>
            
            <?php if ($message): ?>
                <div class="alert alert-<?php echo $status; ?> alert-dismissible fade show">
                    <?php echo $message; ?>
                    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                </div>
            <?php endif; ?>

            <div class="csv-format">
                <strong>📋 Expected CSV Format:</strong><br>
                <small>S/N, SessionName, SchoolName, Prog. Type, Prog. Name, CGpa, Grade, CertificateNumber, Matric Number, Name, ProgGrade, Surname, Firstname, Othername, DateGraduated, Day, Month, Year</small>
            </div>

            <form action="" method="POST" enctype="multipart/form-data">
                <div class="mb-4">
                    <label class="form-label fw-bold">
                        <i class="fas fa-file-csv text-primary me-2"></i>Select CSV File
                    </label>
                    <input type="file" name="cert_file" class="form-control form-control-lg" accept=".csv" required>
                    <div class="form-text">Only CSV files from the graduation certificate list</div>
                </div>

                <div class="d-grid gap-3">
                    <button type="submit" class="btn btn-primary btn-lg">
                        <i class="fas fa-file-import me-2"></i>Upload & Process Graduation Data
                    </button>
                    <a href="index.php" class="btn btn-outline-secondary">
                        <i class="fas fa-arrow-left me-2"></i>Back to Dashboard
                    </a>
                </div>
            </form>
            
            <div class="mt-4 text-center">
                <div class="alert alert-info">
                    <i class="fas fa-info-circle me-2"></i>
                    <strong>Note:</strong> This upload will add/update graduation details including CGPA, certificate number, and graduation date.
                </div>
            </div>
        </div>
    </div>
</body>
</html>