<?php
require_once 'config/database.php';

// Increase limits for large files
ini_set('upload_max_filesize', '200M');
ini_set('post_max_size', '200M');
ini_set('max_execution_time', '0'); // No time limit
ini_set('max_input_time', '0');
ini_set('memory_limit', '512M');

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

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['cert_file'])) {
    $file = $_FILES['cert_file'];
    
    if ($file['error'] === UPLOAD_ERR_OK) {
        $startTime = microtime(true);
        
        // Create temp directory
        $tempDir = 'uploads/temp/';
        if (!is_dir($tempDir)) {
            mkdir($tempDir, 0777, true);
        }
        
        $tempFile = $tempDir . 'cert_upload_' . time() . '.csv';
        
        // Move uploaded file
        if (move_uploaded_file($file['tmp_name'], $tempFile)) {
            try {
                // Count total rows first
                $totalRows = 0;
                $handle = fopen($tempFile, "r");
                if ($handle) {
                    fgetcsv($handle); // Skip header
                    while (($data = fgetcsv($handle)) !== FALSE) {
                        $totalRows++;
                    }
                    fclose($handle);
                }
                
                $message = "📊 File loaded: " . number_format($totalRows) . " records found.<br>";
                $message .= "⏳ Starting batch processing...<br>";
                echo $message;
                flush();
                
                // Process in batches
                $batchSize = 1000;
                $processed = 0;
                $inserted = 0;
                $updated = 0;
                $errors = [];
                
                $db = Database::getInstance();
                $conn = $db->getConn();
                
                $handle = fopen($tempFile, "r");
                fgetcsv($handle); // Skip header
                
                // Prepare SQL statement
                $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);
                
                $conn->beginTransaction();
                $batchCount = 0;
                
                while (($data = fgetcsv($handle)) !== FALSE) {
                    $processed++;
                    $batchCount++;
                    
                    try {
                        // Skip if no Matric Number
                        if (empty($data[8])) {
                            $errors[] = "Row $processed: Missing Matric Number";
                            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));
                        
                        // Execute insert/update
                        $result = $stmt->execute([
                            trim($data[8]),                    // 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
                        ]);
                        
                        if ($result) {
                            $rowCount = $stmt->rowCount();
                            if ($rowCount === 1) {
                                $inserted++;
                            } elseif ($rowCount === 2) {
                                $updated++;
                            }
                        }
                        
                        // Commit batch every $batchSize rows
                        if ($batchCount >= $batchSize) {
                            $conn->commit();
                            $conn->beginTransaction();
                            $batchCount = 0;
                            
                            // Show progress
                            $progress = round(($processed / $totalRows) * 100, 1);
                            echo "<script>
                                document.getElementById('progress').innerHTML = 'Processed: " . number_format($processed) . " of " . number_format($totalRows) . " (" . $progress . "%)';
                                document.getElementById('progress-bar').style.width = '" . $progress . "%';
                            </script>";
                            flush();
                        }
                        
                    } catch (Exception $e) {
                        $errors[] = "Row $processed: " . $e->getMessage();
                        continue;
                    }
                }
                
                // Commit final batch
                $conn->commit();
                fclose($handle);
                
                // Delete temp file
                unlink($tempFile);
                
                $endTime = microtime(true);
                $executionTime = round($endTime - $startTime, 2);
                
                // Build final message
                $message = "<div class='alert alert-success'>";
                $message .= "<h4>✅ Processing Complete!</h4>";
                $message .= "<strong>Statistics:</strong><br>";
                $message .= "• Total records processed: " . number_format($processed) . "<br>";
                $message .= "• New records inserted: " . number_format($inserted) . "<br>";
                $message .= "• Existing records updated: " . number_format($updated) . "<br>";
                $message .= "• Execution time: " . $executionTime . " seconds<br>";
                $message .= "• Average speed: " . round($processed / $executionTime, 0) . " records/second";
                
                if (!empty($errors)) {
                    $message .= "<br><br><strong>⚠️ Errors (" . count($errors) . "):</strong><br>";
                    $message .= implode("<br>", array_slice($errors, 0, 10));
                    if (count($errors) > 10) {
                        $message .= "<br>... and " . (count($errors) - 10) . " more errors";
                    }
                }
                
                $message .= "</div>";
                $status = "success";
                
            } catch (Exception $e) {
                if (isset($conn)) $conn->rollBack();
                if (isset($handle) && is_resource($handle)) fclose($handle);
                if (file_exists($tempFile)) unlink($tempFile);
                
                $message = "<div class='alert alert-danger'>❌ Error: " . $e->getMessage() . "</div>";
                $status = "danger";
            }
        } else {
            $message = "<div class='alert alert-danger'>❌ Failed to move uploaded file.</div>";
            $status = "danger";
        }
    } else {
        $message = "<div class='alert alert-danger'>❌ Upload failed. Error code: " . $file['error'] . "</div>";
        $status = "danger";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Batch Certificate Upload (Large Files)</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: 1000px; 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; }
        .progress-container { margin: 30px 0; }
        .progress { height: 30px; border-radius: 15px; }
        .progress-bar { background: linear-gradient(90deg, #4CAF50, #8BC34A); }
        .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin: 20px 0; }
        .stat-box { background: #f8f9fa; padding: 15px; border-radius: 10px; text-align: center; }
        .stat-box .number { font-size: 24px; font-weight: bold; color: #007bff; }
        .loader { display: none; border: 5px solid #f3f3f3; border-top: 5px solid #3498db; border-radius: 50%; width: 50px; height: 50px; animation: spin 2s linear infinite; margin: 20px auto; }
        @keyframes spin { 0% { transform: rotate(0deg); } 100% { transform: rotate(360deg); } }
    </style>
</head>
<body>
    <div class="container">
        <div class="upload-card">
            <div class="card-header text-center">
                <h3><i class="fas fa-database me-2"></i>Batch Certificate Upload</h3>
                <p class="mb-0">Optimized for large files (15,000+ records)</p>
            </div>
            
            <div id="message-container">
                <?php if ($message): ?>
                    <?php echo $message; ?>
                <?php endif; ?>
            </div>
            
            <div class="alert alert-info">
                <h5><i class="fas fa-info-circle me-2"></i>For Large Files (15,000+ Records)</h5>
                <ul class="mb-0">
                    <li>Processes in batches of 1,000 records</li>
                    <li>Shows real-time progress</li>
                    <li>Handles errors gracefully</li>
                    <li>Optimized for memory usage</li>
                </ul>
            </div>
            
            <div id="progress-container" class="progress-container" style="display: none;">
                <div class="d-flex justify-content-between mb-2">
                    <span id="progress-text">Processing...</span>
                    <span id="progress-percent">0%</span>
                </div>
                <div class="progress">
                    <div id="progress-bar" class="progress-bar progress-bar-striped progress-bar-animated" role="progressbar" style="width: 0%"></div>
                </div>
                <div id="stats" class="stats-grid mt-3"></div>
            </div>
            
            <form action="" method="POST" enctype="multipart/form-data" id="upload-form" onsubmit="startUpload()">
                <div class="mb-4">
                    <label class="form-label fw-bold">
                        <i class="fas fa-file-csv text-primary me-2"></i>Select Large CSV File
                    </label>
                    <input type="file" name="cert_file" id="cert_file" class="form-control form-control-lg" accept=".csv" required>
                    <div class="form-text" id="fileInfo">Maximum file size: 200MB</div>
                </div>
                
                <div class="d-grid gap-3">
                    <button type="submit" class="btn btn-primary btn-lg" id="submit-btn">
                        <i class="fas fa-rocket me-2"></i>Start Batch Upload
                    </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-info w-100">
                                <i class="fas fa-upload me-2"></i>Regular Upload
                            </a>
                        </div>
                    </div>
                </div>
            </form>
            
            <div class="mt-4">
                <div class="alert alert-warning">
                    <i class="fas fa-exclamation-triangle me-2"></i>
                    <strong>Note:</strong> Do not close or refresh the page while uploading. The process may take several minutes for large files.
                </div>
            </div>
        </div>
    </div>
    
    <div id="loader" class="loader" style="display:none;"></div>

    <script>
        function startUpload() {
            const fileInput = document.getElementById('cert_file');
            const submitBtn = document.getElementById('submit-btn');
            const progressContainer = document.getElementById('progress-container');
            
            if (fileInput.files.length > 0) {
                const file = fileInput.files[0];
                const maxSize = 200 * 1024 * 1024; // 200MB
                
                if (file.size > maxSize) {
                    alert('File is too large! Maximum size is 200MB.');
                    return false;
                }
                
                if (!file.name.toLowerCase().endsWith('.csv')) {
                    alert('Please select a CSV file.');
                    return false;
                }
                
                // Show loading state
                submitBtn.disabled = true;
                submitBtn.innerHTML = '<i class="fas fa-spinner fa-spin me-2"></i>Processing...';
                progressContainer.style.display = 'block';
                
                // Show initial progress
                updateProgress(0, 'Starting upload...');
                
                return true;
            }
            
            return false;
        }
        
        function updateProgress(percent, message) {
            const progressBar = document.getElementById('progress-bar');
            const progressText = document.getElementById('progress-text');
            const progressPercent = document.getElementById('progress-percent');
            
            progressBar.style.width = percent + '%';
            progressText.textContent = message;
            progressPercent.textContent = percent.toFixed(1) + '%';
        }
        
        function updateStats(stats) {
            const statsContainer = document.getElementById('stats');
            if (stats) {
                statsContainer.innerHTML = `
                    <div class="stat-box">
                        <div class="number">${stats.processed || 0}</div>
                        <div class="label">Processed</div>
                    </div>
                    <div class="stat-box">
                        <div class="number">${stats.inserted || 0}</div>
                        <div class="label">Inserted</div>
                    </div>
                    <div class="stat-box">
                        <div class="number">${stats.updated || 0}</div>
                        <div class="label">Updated</div>
                    </div>
                    <div class="stat-box">
                        <div class="number">${stats.errors || 0}</div>
                        <div class="label">Errors</div>
                    </div>
                `;
            }
        }
        
        // Update file info
        document.getElementById('cert_file').addEventListener('change', function() {
            if (this.files.length > 0) {
                const file = this.files[0];
                const fileSizeMB = (file.size / (1024 * 1024)).toFixed(2);
                const estimatedRecords = Math.round((file.size / 1024) * 10); // Rough estimate: 1KB ≈ 10 records
                
                document.getElementById('fileInfo').innerHTML = 
                    `Selected: ${file.name}<br>
                     Size: ${fileSizeMB} MB<br>
                     Estimated records: ${estimatedRecords.toLocaleString()}`;
            }
        });
    </script>
</body>
</html>