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

// Increase limits for large CSV files with Base64 images
ini_set('upload_max_filesize', '200M');
ini_set('post_max_size', '200M');
ini_set('max_execution_time', '0');
ini_set('max_input_time', '0');
ini_set('memory_limit', '512M');

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

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['picture_file'])) {
    $file = $_FILES['picture_file'];
    
    $uploadErrors = [
        UPLOAD_ERR_INI_SIZE => 'The uploaded file exceeds the upload_max_filesize directive.',
        UPLOAD_ERR_FORM_SIZE => 'The uploaded file exceeds the MAX_FILE_SIZE directive.',
        UPLOAD_ERR_PARTIAL => 'The uploaded file was only partially uploaded.',
        UPLOAD_ERR_NO_FILE => 'No file was uploaded.',
        UPLOAD_ERR_NO_TMP_DIR => 'Missing a temporary folder.',
        UPLOAD_ERR_CANT_WRITE => 'Failed to write file to disk.',
        UPLOAD_ERR_EXTENSION => 'A PHP extension stopped the file upload.'
    ];
    
    if ($file['error'] !== UPLOAD_ERR_OK) {
        $errorMsg = $uploadErrors[$file['error']] ?? "Unknown upload error (Code: {$file['error']})";
        $message = "❌ Upload failed: " . $errorMsg;
        $status = "danger";
    } else {
        $startTime = microtime(true);
        
        // Create temporary directory
        $tempDir = 'uploads/temp/';
        if (!is_dir($tempDir)) {
            mkdir($tempDir, 0777, true);
        }
        
        $tempFile = $tempDir . 'photos_upload_' . time() . '.csv';
        
        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 photo update process...<br>";
                echo $message;
                flush();
                
                // Process in batches
                $batchSize = 1000;
                $processed = 0;
                $updated = 0;
                $failed = 0;
                $skipped = 0;
                $errors = [];
                
                $db = Database::getInstance();
                $conn = $db->getConn();
                
                $handle = fopen($tempFile, "r");
                $headers = fgetcsv($handle); // Read headers
                
                // Map headers to indices
                $headerMap = array_flip($headers);
                $appNumIndex = $headerMap['Appnum'] ?? $headerMap['appnum'] ?? $headerMap['AppNum'] ?? 0;
                $photoIndex = $headerMap['StdImage'] ?? $headerMap['stdimage'] ?? $headerMap['Photo'] ?? 1;
                
                // Validate header mapping
                if ($appNumIndex === null || $photoIndex === null) {
                    throw new Exception("CSV headers not found. Expected columns: Appnum and StdImage");
                }
                
                // Prepare SQL statement
                $sql = "UPDATE students SET photo_path = ?, last_updated = CURRENT_TIMESTAMP WHERE app_num = ?";
                $stmt = $conn->prepare($sql);
                
                // Prepare a second statement for checking
                $checkSql = "SELECT id FROM students WHERE app_num = ?";
                $checkStmt = $conn->prepare($checkSql);
                
                $conn->beginTransaction();
                $batchCount = 0;
                
                while (($data = fgetcsv($handle)) !== FALSE) {
                    $processed++;
                    $batchCount++;
                    
                    try {
                        // Skip if no AppNum or Photo data
                        if (empty($data[$appNumIndex]) || empty($data[$photoIndex])) {
                            $skipped++;
                            continue;
                        }
                        
                        $appNum = trim($data[$appNumIndex]);
                        $base64Data = trim($data[$photoIndex]);
                        
                        // Check if student exists with this app_num
                        $checkStmt->execute([$appNum]);
                        $studentExists = $checkStmt->fetch();
                        
                        if (!$studentExists) {
                            $failed++;
                            $errors[] = "Record $processed: AppNum '$appNum' not found in database";
                            continue;
                        }
                        
                        // Clean and prepare Base64 data
                        if (strpos($base64Data, 'data:image') === false) {
                            // Try to determine image type from Base64 or use default
                            $base64Data = 'data:image/jpeg;base64,' . $base64Data;
                        }
                        
                        // Validate Base64 format
                        $base64Data = str_replace(' ', '+', $base64Data);
                        
                        // Execute update
                        $result = $stmt->execute([$base64Data, $appNum]);
                        
                        if ($result && $stmt->rowCount() > 0) {
                            $updated++;
                        } else {
                            $failed++;
                            $errors[] = "Record $processed: Failed to update photo for AppNum '$appNum'";
                        }
                        
                        // 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 . "%';
                                document.getElementById('stats').innerHTML = 'Updated: " . number_format($updated) . " | Failed: " . number_format($failed) . " | Skipped: " . number_format($skipped) . "';
                            </script>";
                            flush();
                        }
                        
                    } catch (Exception $e) {
                        $failed++;
                        $errors[] = "Record $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>✅ Photo Upload Complete!</h4>";
                $message .= "<strong>Processing Statistics:</strong><br>";
                $message .= "• Total records processed: " . number_format($processed) . "<br>";
                $message .= "• Photos successfully updated: " . number_format($updated) . "<br>";
                $message .= "• Records failed to update: " . number_format($failed) . "<br>";
                $message .= "• Records skipped (missing data): " . number_format($skipped) . "<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 .= "<small>";
                    $message .= implode("<br>", array_slice($errors, 0, 10));
                    if (count($errors) > 10) {
                        $message .= "<br>... and " . (count($errors) - 10) . " more errors";
                    }
                    $message .= "</small>";
                    $status = "warning";
                } else {
                    $status = "success";
                }
                
                $message .= "</div>";
                
            } 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";
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Bulk Student Photo 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, #ffecd2 0%, #fcb69f 100%); min-height: 100vh; padding: 20px; font-family: "Times New Roman", serif; }
        .upload-card { max-width: 900px; 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, #ffecd2 0%, #fcb69f 100%); color: #333; 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, #ff9a9e, #fad0c4); }
        .stats-box { background: #f8f9fa; padding: 15px; border-radius: 10px; margin: 20px 0; font-size: 14px; }
        .format-example { background: #e9ecef; padding: 10px; border-radius: 8px; font-family: monospace; font-size: 12px; margin: 10px 0; }
        .loader { display: none; border: 5px solid #f3f3f3; border-top: 5px solid #fcb69f; 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-camera me-2"></i>Bulk Student Photo Upload</h3>
                <p class="mb-0">Optimized for large CSV 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>About This Upload</h5>
                <ul class="mb-0">
                    <li>Matches photos to students using <strong>Appnum/GTS Number</strong></li>
                    <li>Processes in batches of 1,000 records</li>
                    <li>Shows real-time progress</li>
                    <li>Continues processing even if some records fail</li>
                </ul>
            </div>
            
            <div class="stats-box">
                <h6><i class="fas fa-database me-2"></i>CSV Format Requirements:</h6>
                <p>Your CSV must have these two columns (in any order):</p>
                <div class="format-example">
                    <strong>Appnum</strong> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>StdImage</strong><br>
                    GTS2210167 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGB...<br>
                    GTS2210192 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGB...
                </div>
                <small class="text-muted">StdImage column should contain Base64 encoded image data</small>
            </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="mt-2 text-center small text-muted"></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 CSV File with Photos
                    </label>
                    <input type="file" name="picture_file" id="picture_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 Bulk Photo 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="test_photo_match.php" class="btn btn-outline-info w-100">
                                <i class="fas fa-search me-2"></i>Test Matching
                            </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> 
                    <ul class="mb-0 mt-2">
                        <li>Do not close or refresh the page while uploading</li>
                        <li>Processing 15,000 records may take 5-15 minutes</li>
                        <li>Ensure CSV has correct <code>Appnum</code> values matching your database</li>
                    </ul>
                </div>
            </div>
        </div>
    </div>
    
    <div id="loader" class="loader" style="display:none;"></div>

    <script>
        function startUpload() {
            const fileInput = document.getElementById('picture_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');
            
            if (progressBar && progressText && progressPercent) {
                progressBar.style.width = percent + '%';
                progressText.textContent = message;
                progressPercent.textContent = percent.toFixed(1) + '%';
            }
        }
        
        // Update file info
        document.getElementById('picture_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) * 3); // Rough estimate
                
                document.getElementById('fileInfo').innerHTML = 
                    `Selected: ${file.name}<br>
                     Size: ${fileSizeMB} MB<br>
                     Estimated records: ${estimatedRecords.toLocaleString()}`;
            }
        });
    </script>
</body>
</html>