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

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

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['student_file'])) {
    $file = $_FILES['student_file'];
    
    if ($file['error'] === UPLOAD_ERR_OK) {
        $fileType = pathinfo($file['name'], PATHINFO_EXTENSION);
        
        set_time_limit(0); 
        ini_set('memory_limit', '512M');

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

            if (strtolower($fileType) === 'csv') {
                // Process CSV file
                $handle = fopen($file['tmp_name'], "r");
                $headers = fgetcsv($handle); // Read headers
                
                // Map headers to indices
                $headerMap = array_flip($headers);
                
                // Define column indices based on your Excel structure
                $indices = [
                    'student_id' => $headerMap['Matricnum'] ?? $headerMap['student_id'] ?? 1,
                    'app_num' => $headerMap['Appnum'] ?? $headerMap['app_num'] ?? 2,
                    'surname' => $headerMap['Surname'] ?? $headerMap['surname'] ?? 3,
                    'firstname' => $headerMap['Firstname'] ?? $headerMap['firstname'] ?? 4,
                    'othername' => $headerMap['Othername'] ?? $headerMap['othername'] ?? 5,
                    'program' => $headerMap['Programme'] ?? $headerMap['program'] ?? 6,
                    'level_name' => $headerMap['Level'] ?? $headerMap['level'] ?? 7,
                    'amount_paid' => $headerMap['Amount'] ?? $headerMap['amount_paid'] ?? 8,
                    'receipt_no' => $headerMap['Receipt No.'] ?? $headerMap['receipt_no'] ?? 9,
                    'phone' => $headerMap['Phone'] ?? $headerMap['phone'] ?? 10,
                    'payment_date' => $headerMap['Date'] ?? $headerMap['payment_date'] ?? 11
                ];
                
                // SQL for Student Data
                $sql = "INSERT INTO students 
                        (student_id, app_num, surname, firstname, othername, full_name, 
                         program, level_name, amount_paid, receipt_no, phone, payment_date,
                         department, grade, data_source) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'batch_upload')
                        ON DUPLICATE KEY UPDATE 
                        app_num = VALUES(app_num),
                        surname = VALUES(surname),
                        firstname = VALUES(firstname),
                        othername = VALUES(othername),
                        full_name = VALUES(full_name),
                        program = VALUES(program),
                        level_name = VALUES(level_name),
                        amount_paid = VALUES(amount_paid),
                        receipt_no = VALUES(receipt_no),
                        phone = VALUES(phone),
                        payment_date = VALUES(payment_date),
                        last_updated = CURRENT_TIMESTAMP";
                
                $stmt = $conn->prepare($sql);
                $count = 0;
                $inserted = 0;
                $updated = 0;
                
                while (($data = fgetcsv($handle)) !== FALSE) {
                    if (empty($data[$indices['student_id']])) continue;
                    
                    $studentId = trim($data[$indices['student_id']]);
                    
                    // Create full name
                    $surname = trim($data[$indices['surname']] ?? '');
                    $firstname = trim($data[$indices['firstname']] ?? '');
                    $othername = trim($data[$indices['othername']] ?? '');
                    $fullName = trim("$surname $firstname $othername");
                    
                    // Format payment date (dd/mm/yy to YYYY-MM-DD)
                    $paymentDateStr = trim($data[$indices['payment_date']] ?? '');
                    $paymentDate = date('Y-m-d', strtotime(str_replace('/', '-', $paymentDateStr)));
                    
                    // Check if student exists
                    $checkSql = "SELECT id FROM students WHERE student_id = ?";
                    $checkStmt = $conn->prepare($checkSql);
                    $checkStmt->execute([$studentId]);
                    $exists = $checkStmt->fetch();
                    
                    // Extract program name (remove "ND " prefix if present)
                    $program = trim($data[$indices['program']] ?? '');
                    $program = preg_replace('/^ND\s+/i', '', $program);
                    
                    // Execute insert/update
                    $stmt->execute([
                        $studentId,
                        trim($data[$indices['app_num']] ?? ''),
                        $surname,
                        $firstname,
                        $othername,
                        $fullName,
                        $program,
                        trim($data[$indices['level_name']] ?? ''),
                        (float) preg_replace('/[^0-9.]/', '', $data[$indices['amount_paid']] ?? 0),
                        trim($data[$indices['receipt_no']] ?? ''),
                        trim($data[$indices['phone']] ?? ''),
                        $paymentDate,
                        'Financial Studies', // Default department
                        'Pass'              // Default grade
                    ]);
                    
                    $count++;
                    if ($exists) {
                        $updated++;
                    } else {
                        $inserted++;
                    }
                }
                
                fclose($handle);
                
            } elseif (in_array(strtolower($fileType), ['xls', 'xlsx'])) {
                // Process Excel file
                require_once 'vendor/autoload.php'; // For PhpSpreadsheet
                
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
                $worksheet = $spreadsheet->getActiveSheet();
                $rows = $worksheet->toArray();
                
                $headers = array_shift($rows);
                $headerMap = array_flip($headers);
                
                // Define column indices for Excel
                $indices = [
                    'student_id' => $headerMap['Matricnum'] ?? $headerMap['Student ID'] ?? 1,
                    'app_num' => $headerMap['Appnum'] ?? $headerMap['Application No'] ?? 2,
                    'surname' => $headerMap['Surname'] ?? 3,
                    'firstname' => $headerMap['Firstname'] ?? 4,
                    'othername' => $headerMap['Othername'] ?? 5,
                    'program' => $headerMap['Programme'] ?? $headerMap['Program'] ?? 6,
                    'level_name' => $headerMap['Level'] ?? 7,
                    'amount_paid' => $headerMap['Amount'] ?? 8,
                    'receipt_no' => $headerMap['Receipt No.'] ?? $headerMap['Receipt No'] ?? 9,
                    'phone' => $headerMap['Phone'] ?? 10,
                    'payment_date' => $headerMap['Date'] ?? 11
                ];
                
                // SQL for Student Data
                $sql = "INSERT INTO students 
                        (student_id, app_num, surname, firstname, othername, full_name, 
                         program, level_name, amount_paid, receipt_no, phone, payment_date,
                         department, grade, data_source) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'batch_upload')
                        ON DUPLICATE KEY UPDATE 
                        app_num = VALUES(app_num),
                        surname = VALUES(surname),
                        firstname = VALUES(firstname),
                        othername = VALUES(othername),
                        full_name = VALUES(full_name),
                        program = VALUES(program),
                        level_name = VALUES(level_name),
                        amount_paid = VALUES(amount_paid),
                        receipt_no = VALUES(receipt_no),
                        phone = VALUES(phone),
                        payment_date = VALUES(payment_date),
                        last_updated = CURRENT_TIMESTAMP";
                
                $stmt = $conn->prepare($sql);
                $count = 0;
                $inserted = 0;
                $updated = 0;
                
                foreach ($rows as $data) {
                    if (empty($data[$indices['student_id']])) continue;
                    
                    $studentId = trim($data[$indices['student_id']]);
                    
                    // Create full name
                    $surname = trim($data[$indices['surname']] ?? '');
                    $firstname = trim($data[$indices['firstname']] ?? '');
                    $othername = trim($data[$indices['othername']] ?? '');
                    $fullName = trim("$surname $firstname $othername");
                    
                    // Format payment date (Excel serial date or string)
                    $paymentDateCell = $data[$indices['payment_date']] ?? '';
                    if (is_numeric($paymentDateCell)) {
                        // Excel serial date
                        $paymentDate = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($paymentDateCell)->format('Y-m-d');
                    } else {
                        // String date
                        $paymentDate = date('Y-m-d', strtotime(str_replace('/', '-', $paymentDateCell)));
                    }
                    
                    // Check if student exists
                    $checkSql = "SELECT id FROM students WHERE student_id = ?";
                    $checkStmt = $conn->prepare($checkSql);
                    $checkStmt->execute([$studentId]);
                    $exists = $checkStmt->fetch();
                    
                    // Extract program name
                    $program = trim($data[$indices['program']] ?? '');
                    $program = preg_replace('/^ND\s+/i', '', $program);
                    
                    // Execute insert/update
                    $stmt->execute([
                        $studentId,
                        trim($data[$indices['app_num']] ?? ''),
                        $surname,
                        $firstname,
                        $othername,
                        $fullName,
                        $program,
                        trim($data[$indices['level_name']] ?? ''),
                        (float) preg_replace('/[^0-9.]/', '', $data[$indices['amount_paid']] ?? 0),
                        trim($data[$indices['receipt_no']] ?? ''),
                        trim($data[$indices['phone']] ?? ''),
                        $paymentDate,
                        'Financial Studies', // Default department
                        'Pass'              // Default grade
                    ]);
                    
                    $count++;
                    if ($exists) {
                        $updated++;
                    } else {
                        $inserted++;
                    }
                }
            } else {
                throw new Exception("Unsupported file type. Please upload CSV or Excel file.");
            }

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

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Student Data 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, #f093fb 0%, #f5576c 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, #f093fb 0%, #f5576c 100%); color: white; border-radius: 15px 15px 0 0; padding: 25px; margin: -40px -40px 30px -40px; }
        .format-info { background: #f8f9fa; padding: 15px; border-radius: 10px; margin: 20px 0; }
        .file-types { display: flex; gap: 10px; margin-top: 10px; }
        .file-type { flex: 1; text-align: center; padding: 10px; border-radius: 8px; background: #e9ecef; }
        .file-type i { font-size: 24px; margin-bottom: 5px; }
    </style>
</head>
<body>
    <div class="container">
        <div class="upload-card">
            <div class="card-header text-center">
                <h3><i class="fas fa-users me-2"></i>Student Data Upload</h3>
                <p class="mb-0">Upload student payment and registration data</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="format-info">
                <strong>📋 Expected Format:</strong><br>
                <small>S/N, Matricnum, Appnum, Surname, Firstname, Othername, Programme, Level, Amount, Receipt No., Phone, Date</small>
                
                <div class="file-types">
                    <div class="file-type">
                        <i class="fas fa-file-excel text-success"></i><br>
                        <small>Excel (.xlsx)</small>
                    </div>
                    <div class="file-type">
                        <i class="fas fa-file-csv text-primary"></i><br>
                        <small>CSV (.csv)</small>
                    </div>
                </div>
            </div>

            <form action="" method="POST" enctype="multipart/form-data">
                <div class="mb-4">
                    <label class="form-label fw-bold">
                        <i class="fas fa-file-upload text-primary me-2"></i>Select File
                    </label>
                    <input type="file" name="student_file" class="form-control form-control-lg" accept=".csv,.xlsx,.xls" required>
                    <div class="form-text">Upload CSV or Excel file containing student registration data</div>
                </div>

                <div class="d-grid gap-3">
                    <button type="submit" class="btn btn-success btn-lg">
                        <i class="fas fa-upload me-2"></i>Upload Student Data
                    </button>
                    <div class="row">
                        <div class="col">
                            <a href="index.php" class="btn btn-outline-secondary w-100">
                                <i class="fas fa-arrow-left me-2"></i>Dashboard
                            </a>
                        </div>
                        <div class="col">
                            <a href="certificate_upload.php" class="btn btn-outline-primary w-100">
                                <i class="fas fa-graduation-cap me-2"></i>Upload Certificates
                            </a>
                        </div>
                    </div>
                </div>
            </form>
            
            <div class="mt-4">
                <div class="alert alert-warning">
                    <i class="fas fa-exclamation-triangle me-2"></i>
                    <strong>Important:</strong> This upload handles student payment data. For graduation certificates, use the other upload form.
                </div>
            </div>
        </div>
    </div>
</body>
</html>