<?php
session_start();
require_once 'config.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: index.php');
    exit();
}

$pdo = getDB();
$message = '';
$uploaded_count = 0;
$error_count = 0;
$error_details = [];

// Set longer timeout for large uploads
try {
    $pdo->exec("SET SESSION wait_timeout = 600");
    $pdo->exec("SET SESSION interactive_timeout = 600");
} catch (Exception $e) {
    // Ignore if can't set timeouts
}

// Function to map gender values
function mapGender($gender) {
    $gender = strtolower(trim($gender));
    
    $genderMap = [
        'male' => 'Male',
        'female' => 'Female',
        'm' => 'Male',
        'f' => 'Female',
        'other' => 'Other'
    ];
    
    if (isset($genderMap[$gender])) {
        return $genderMap[$gender];
    }
    
    if (!empty($gender)) {
        $firstChar = strtolower($gender[0]);
        if ($firstChar == 'm') return 'Male';
        if ($firstChar == 'f') return 'Female';
    }
    
    return null;
}

// ------------------------------------------------------------------
// 1) CSV data import (student records)
// ------------------------------------------------------------------
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    try {
        // Check for upload errors
        if ($_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
            throw new Exception('File upload failed with error code: ' . $_FILES['csv_file']['error']);
        }

        $file = $_FILES['csv_file']['tmp_name'];
        $original_name = $_FILES['csv_file']['name'];
        
        if (!file_exists($file) || filesize($file) == 0) {
            throw new Exception('Uploaded file is empty or invalid');
        }

        $file_ext = strtolower(pathinfo($original_name, PATHINFO_EXTENSION));
        if ($file_ext !== 'csv') {
            throw new Exception('Invalid file format. Please upload CSV files only.');
        }

        $handle = fopen($file, 'r');
        if (!$handle) {
            throw new Exception('Could not open the CSV file.');
        }

        // Read the header row
        $header = fgetcsv($handle);
        
        if ($header === false || empty($header)) {
            fclose($handle);
            throw new Exception('The CSV file appears to be empty or invalid.');
        }

        // Define the column mapping based on your CSV header
        $columnMap = [];
        foreach ($header as $index => $columnName) {
            $cleanName = strtolower(trim($columnName));
            $cleanName = preg_replace('/[^a-z0-9_]/', '', $cleanName);
            
            switch ($cleanName) {
                case 'registration_no':
                case 'registrationno':
                    $columnMap['registration_no'] = $index;
                    break;
                case 'name':
                    $columnMap['name'] = $index;
                    break;
                case 'father_name':
                case 'fathername':
                    $columnMap['father_name'] = $index;
                    break;
                case 'mother_name':
                case 'mothername':
                    $columnMap['mother_name'] = $index;
                    break;
                case 'degree':
                    $columnMap['degree'] = $index;
                    break;
                case 'branch':
                    $columnMap['branch'] = $index;
                    break;
                case 'specialization':
                    $columnMap['specialization'] = $index;
                    break;
                case 'grade_point_average':
                case 'gradepointaverage':
                    $columnMap['gpa'] = $index;
                    break;
                case 'total_grade_points':
                case 'totalgradepoints':
                    $columnMap['total_gp'] = $index;
                    break;
                case 'gender':
                    $columnMap['gender'] = $index;
                    break;
                case 'year_of_passing':
                case 'yearofpassing':
                    $columnMap['year'] = $index;
                    break;
                case 'faculty':
                    $columnMap['faculty'] = $index;
                    break;
            }
        }

        // Verify required columns exist
        $requiredColumns = ['registration_no', 'name', 'father_name', 'degree'];
        foreach ($requiredColumns as $required) {
            if (!isset($columnMap[$required])) {
                throw new Exception("Required column '$required' not found in CSV header.");
            }
        }

        // Begin transaction
        $pdo->beginTransaction();

        $checkStmt = $pdo->prepare("SELECT id FROM certificates WHERE registration_no = ?");
        $insertStmt = $pdo->prepare("INSERT INTO certificates (
            registration_no, name, father_name, mother_name, degree, branch,
            specialization, grade_point_average, total_grade_points, gender,
            photo, year_of_passing, faculty, certificate_no, generated_by
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $rowIndex = 2;
        
        while (($row = fgetcsv($handle)) !== FALSE) {
            if (empty(array_filter($row, function($val) { return !is_null($val) && trim($val) !== ''; }))) {
                $rowIndex++;
                continue;
            }

            try {
                $registration_no = isset($columnMap['registration_no']) ? 
                    sanitize(trim($row[$columnMap['registration_no']] ?? '')) : '';
                
                if (empty($registration_no)) {
                    $error_count++;
                    $error_details[] = "Row " . $rowIndex . ": Missing registration number";
                    $rowIndex++;
                    continue;
                }

                // Check for duplicate
                $checkStmt->execute([$registration_no]);
                if ($checkStmt->fetch()) {
                    $error_count++;
                    $error_details[] = "Row " . $rowIndex . ": Registration number '$registration_no' already exists";
                    $rowIndex++;
                    continue;
                }

                $name = isset($columnMap['name']) ? sanitize(trim($row[$columnMap['name']] ?? '')) : '';
                $father_name = isset($columnMap['father_name']) ? sanitize(trim($row[$columnMap['father_name']] ?? '')) : '';
                $mother_name = isset($columnMap['mother_name']) ? sanitize(trim($row[$columnMap['mother_name']] ?? '')) : '';
                $degree = isset($columnMap['degree']) ? sanitize(trim($row[$columnMap['degree']] ?? '')) : '';
                $branch = isset($columnMap['branch']) ? sanitize(trim($row[$columnMap['branch']] ?? '')) : '';
                $specialization = isset($columnMap['specialization']) ? sanitize(trim($row[$columnMap['specialization']] ?? '')) : '';
                
                $gpa = isset($columnMap['gpa']) && !empty($row[$columnMap['gpa']]) && is_numeric($row[$columnMap['gpa']]) ? 
                    floatval($row[$columnMap['gpa']]) : null;
                $total_gp = isset($columnMap['total_gp']) && !empty($row[$columnMap['total_gp']]) && is_numeric($row[$columnMap['total_gp']]) ? 
                    floatval($row[$columnMap['total_gp']]) : null;
                
                $gender_raw = isset($columnMap['gender']) ? trim($row[$columnMap['gender']] ?? '') : '';
                $gender = mapGender($gender_raw);
                
                $year = isset($columnMap['year']) && !empty($row[$columnMap['year']]) && is_numeric($row[$columnMap['year']]) ? 
                    intval($row[$columnMap['year']]) : null;
                $faculty = isset($columnMap['faculty']) ? sanitize(trim($row[$columnMap['faculty']] ?? '')) : '';
                
                $certificate_no = generateCertificateNo();

                if (empty($name) || empty($father_name) || empty($degree)) {
                    $error_count++;
                    $error_details[] = "Row " . $rowIndex . ": Missing required fields";
                    $rowIndex++;
                    continue;
                }

                $insertStmt->execute([
                    $registration_no, $name, $father_name, $mother_name, $degree,
                    $branch, $specialization, $gpa, $total_gp, $gender,
                    null, $year, $faculty, $certificate_no, $_SESSION['user_id']
                ]);

                $uploaded_count++;

            } catch(PDOException $e) {
                $error_count++;
                $error_details[] = "Row " . $rowIndex . ": " . $e->getMessage();
            }
            
            $rowIndex++;
        }

        fclose($handle);
        $pdo->commit();

        $message = "<div class='success'><i class='fas fa-circle-check'></i> Successfully uploaded <strong>$uploaded_count</strong> records. Errors: <strong>$error_count</strong></div>";
        
        if (!empty($error_details)) {
            $message .= "<div class='error' style='margin-top:10px;'><i class='fas fa-circle-exclamation'></i> Error details:<br><ul style='margin:5px 0 0 20px;'>";
            foreach (array_slice($error_details, 0, 20) as $detail) {
                $message .= "<li>" . htmlspecialchars($detail) . "</li>";
            }
            if (count($error_details) > 20) {
                $message .= "<li>... and " . (count($error_details) - 20) . " more errors</li>";
            }
            $message .= "</ul></div>";
        }

    } catch(Exception $e) {
        try { 
            if ($pdo->inTransaction()) {
                $pdo->rollBack(); 
            }
        } catch(Exception $e2) {}
        
        $message = "<div class='error'><i class='fas fa-circle-exclamation'></i> Error: " . $e->getMessage() . "</div>";
    }
}

// ------------------------------------------------------------------
// 2) Bulk photo upload - FIXED: Individual updates with retry logic
// ------------------------------------------------------------------
$photo_results = [];
$matched_count = 0;
$unmatched_count = 0;
$rejected_count = 0;
$photo_error = false;

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['photos'])) {
    if (empty($_FILES['photos']['name'][0])) {
        $message .= "<div class='warning'><i class='fas fa-exclamation-triangle'></i> No photos were selected for upload.</div>";
    } else {
        $files = $_FILES['photos'];
        $allowed_ext = ['jpg', 'jpeg', 'png', 'gif', 'webp'];
        $max_size = 5 * 1024 * 1024;

        $lookupStmt = $pdo->prepare("SELECT id, name FROM certificates WHERE registration_no = ?");
        $updateStmt = $pdo->prepare("UPDATE certificates SET photo = ? WHERE id = ?");

        $totalFiles = count($files['name']);
        
        for ($i = 0; $i < $totalFiles; $i++) {
            $original_name = $files['name'][$i];
            
            if (empty($original_name)) {
                continue;
            }
            
            $extension = strtolower(pathinfo($original_name, PATHINFO_EXTENSION));
            $key = trim(pathinfo($original_name, PATHINFO_FILENAME));
            $row = ['filename' => $original_name, 'registration_no' => $key];

            // Check upload error
            if ($files['error'][$i] != 0) {
                $row['status'] = 'error';
                $row['note'] = 'Upload error (code ' . $files['error'][$i] . ')';
                $rejected_count++;
                $photo_results[] = $row;
                continue;
            }

            // Check file extension
            if (!in_array($extension, $allowed_ext)) {
                $row['status'] = 'error';
                $row['note'] = 'Unsupported file type: ' . $extension;
                $rejected_count++;
                $photo_results[] = $row;
                continue;
            }

            // Check file size
            if ($files['size'][$i] > $max_size) {
                $row['status'] = 'error';
                $row['note'] = 'Exceeds 5MB limit (' . number_format($files['size'][$i] / 1024 / 1024, 2) . ' MB)';
                $rejected_count++;
                $photo_results[] = $row;
                continue;
            }

            $registration_no = sanitize($key);

            try {
                // Look up the student
                $lookupStmt->execute([$registration_no]);
                $student = $lookupStmt->fetch();

                if (!$student) {
                    $row['status'] = 'unmatched';
                    $row['note'] = 'No student found with registration number: ' . $registration_no;
                    $unmatched_count++;
                    $photo_results[] = $row;
                    continue;
                }

                // Read the file content
                $photo_data = file_get_contents($files['tmp_name'][$i]);
                if ($photo_data === false) {
                    $row['status'] = 'error';
                    $row['note'] = 'Could not read uploaded file';
                    $rejected_count++;
                    $photo_results[] = $row;
                    continue;
                }

                // Update the student record with the photo - with retry logic
                $maxRetries = 3;
                $retryCount = 0;
                $success = false;
                
                while ($retryCount < $maxRetries && !$success) {
                    try {
                        $updateStmt->execute([$photo_data, $student['id']]);
                        $success = true;
                    } catch (PDOException $e) {
                        $retryCount++;
                        if ($retryCount >= $maxRetries) {
                            throw $e;
                        }
                        // Wait a bit before retrying
                        usleep(500000); // 0.5 seconds
                    }
                }
                
                if ($success) {
                    // Log activity
                    if (function_exists('logActivity')) {
                        logActivity($student['id'], 'photo_mapped', $_SESSION['user_id']);
                    }

                    $row['status'] = 'matched';
                    $row['note'] = htmlspecialchars($student['name']) . ' &middot; ' . number_format(strlen($photo_data)) . ' bytes';
                    $matched_count++;
                    $photo_results[] = $row;
                }
                
                // Free memory
                unset($photo_data);

            } catch (PDOException $e) {
                $row['status'] = 'error';
                $row['note'] = 'Database error: ' . $e->getMessage();
                $rejected_count++;
                $photo_results[] = $row;
                $photo_error = true;
                error_log('Photo upload error for ' . $registration_no . ': ' . $e->getMessage());
            }
        }

        if (!empty($photo_results)) {
            $message .= "<div class='success' style='margin-top:10px;'><i class='fas fa-circle-check'></i> Photo mapping complete — <strong>$matched_count</strong> matched, <strong>$unmatched_count</strong> unmatched, <strong>$rejected_count</strong> rejected.</div>";
        }
        
        if ($photo_error) {
            $message .= "<div class='warning' style='margin-top:10px;'><i class='fas fa-exclamation-triangle'></i> Some photos had errors. Check the results table below for details.</div>";
        }
    }
}

$active = 'bulk_upload';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Bulk Upload - SKUAST-K Portal</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        /* ============ Shared portal theme ============ */
        @import url('https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700;800&family=Poppins:wght@600;700;800&display=swap');

        :root {
            --primary: #6c5ce7;
            --primary-dark: #5849c2;
            --primary-light: #a29bfe;
            --accent: #00cec9;
            --accent-2: #fd79a8;
            --success: #10b981;
            --success-bg: #d1fae5;
            --danger: #ef4444;
            --danger-bg: #fee2e2;
            --warning: #f59e0b;
            --warning-bg: #fef3c7;
            --info: #3b82f6;
            --info-bg: #dbeafe;
            --ink-900: #1a1a2e;
            --ink-700: #2d2d4a;
            --ink-500: #64648a;
            --ink-300: #a0a0c0;
            --ink-100: #e8e8f5;
            --surface: #ffffff;
            --bg: #f3f2fb;
            --bg-alt: #f8f7fd;
            --sidebar-grad: linear-gradient(160deg, #1a1a3d 0%, #2b1f5c 55%, #3a1f6b 100%);
            --brand-grad: linear-gradient(135deg, #6c5ce7 0%, #a06cf0 50%, #fd79a8 100%);
            --brand-grad-soft: linear-gradient(135deg, rgba(108,92,231,.12), rgba(253,121,168,.12));
            --radius: 16px;
            --radius-sm: 10px;
            --shadow-sm: 0 2px 8px rgba(26,26,62,.06);
            --shadow-md: 0 8px 24px rgba(26,26,62,.09);
            --shadow-lg: 0 20px 50px rgba(26,26,62,.16);
            --sidebar-w: 264px;
        }

        * { margin: 0; padding: 0; box-sizing: border-box; }

        body {
            font-family: 'Inter', 'Segoe UI', sans-serif;
            background: var(--bg);
            color: var(--ink-700);
            -webkit-font-smoothing: antialiased;
        }

        h1, h2, h3, .brand-font { font-family: 'Poppins', 'Inter', sans-serif; }
        ::selection { background: var(--primary-light); color: #fff; }
        a { color: inherit; }

        ::-webkit-scrollbar { width: 8px; height: 8px; }
        ::-webkit-scrollbar-thumb { background: var(--ink-300); border-radius: 10px; }
        ::-webkit-scrollbar-thumb:hover { background: var(--ink-500); }

        .sidebar {
            width: var(--sidebar-w);
            background: var(--sidebar-grad);
            color: #fff;
            height: 100vh;
            position: fixed;
            top: 0; left: 0;
            padding: 26px 0;
            overflow-y: auto;
            z-index: 1000;
            box-shadow: 4px 0 24px rgba(20,10,50,.25);
        }

        .sidebar .brand {
            display: flex;
            align-items: center;
            gap: 12px;
            padding: 0 22px 22px;
            margin-bottom: 18px;
            border-bottom: 1px solid rgba(255,255,255,.1);
        }
        .sidebar .brand .logo-badge {
            width: 42px; height: 42px;
            border-radius: 12px;
            background: var(--brand-grad);
            display: flex; align-items: center; justify-content: center;
            font-size: 20px;
            box-shadow: 0 6px 16px rgba(108,92,231,.45);
            flex-shrink: 0;
        }
        .sidebar .brand .brand-text { line-height: 1.25; }
        .sidebar .brand .brand-text .t1 { font-weight: 800; font-size: 15px; letter-spacing: .2px; }
        .sidebar .brand .brand-text .t2 { font-size: 10.5px; color: rgba(255,255,255,.55); letter-spacing: .4px; text-transform: uppercase; }

        .sidebar .user-info {
            margin: 0 16px 20px;
            padding: 14px 16px;
            border-radius: var(--radius-sm);
            background: rgba(255,255,255,.06);
            border: 1px solid rgba(255,255,255,.08);
            display: flex;
            align-items: center;
            gap: 10px;
        }
        .sidebar .user-info .avatar {
            width: 36px; height: 36px; border-radius: 50%;
            background: var(--brand-grad);
            display: flex; align-items: center; justify-content: center;
            font-weight: 700; font-size: 14px; flex-shrink: 0;
        }
        .sidebar .user-info .meta { min-width: 0; }
        .sidebar .user-info .name { font-weight: 600; font-size: 13.5px; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
        .sidebar .user-info .email { font-size: 11px; color: rgba(255,255,255,.5); white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
        .sidebar .user-info .role-pill {
            font-size: 9.5px; text-transform: uppercase; letter-spacing: .4px;
            background: rgba(255,255,255,.12); padding: 1px 7px; border-radius: 20px;
            display: inline-block; margin-top: 3px; color: rgba(255,255,255,.75);
        }

        .sidebar nav { padding: 0 12px; }
        .sidebar a.nav-link {
            display: flex; align-items: center; gap: 12px;
            padding: 11px 14px; margin-bottom: 3px;
            border-radius: 10px;
            color: rgba(255,255,255,.62);
            text-decoration: none;
            font-size: 13.5px; font-weight: 500;
            transition: all .2s ease;
            position: relative;
        }
        .sidebar a.nav-link i { width: 18px; text-align: center; font-size: 14px; }
        .sidebar a.nav-link:hover { background: rgba(255,255,255,.08); color: #fff; transform: translateX(2px); }
        .sidebar a.nav-link.active {
            background: var(--brand-grad);
            color: #fff;
            box-shadow: 0 6px 18px rgba(108,92,231,.4);
        }
        .sidebar .nav-logout-wrap { margin-top: 22px; padding-top: 16px; border-top: 1px solid rgba(255,255,255,.1); }

        .main-content { margin-left: var(--sidebar-w); padding: 28px 32px 50px; min-height: 100vh; }

        .page-header {
            background: var(--surface);
            padding: 22px 28px;
            border-radius: var(--radius);
            margin-bottom: 26px;
            box-shadow: var(--shadow-sm);
            display: flex; justify-content: space-between; align-items: center;
            flex-wrap: wrap; gap: 12px;
            border: 1px solid rgba(26,26,62,.04);
        }
        .page-header h1 { font-size: 22px; color: var(--ink-900); font-weight: 700; }
        .page-header .subtitle { font-size: 13px; color: var(--ink-500); margin-top: 3px; font-weight: 400; font-family: 'Inter'; }

        .btn {
            background: var(--brand-grad);
            color: #fff;
            border: none;
            padding: 11px 24px;
            border-radius: 10px;
            cursor: pointer;
            font-size: 14px;
            font-weight: 600;
            font-family: inherit;
            transition: all .2s ease;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 8px;
            box-shadow: 0 4px 14px rgba(108,92,231,.3);
        }
        .btn:hover { transform: translateY(-2px); box-shadow: 0 8px 20px rgba(108,92,231,.4); }
        .btn:active { transform: translateY(0); }
        .btn-secondary { background: var(--ink-100); color: var(--ink-700); box-shadow: none; }
        .btn-secondary:hover { background: #dcdcf0; box-shadow: none; }
        .btn-success { background: linear-gradient(135deg,#10b981,#0ea36f); box-shadow: 0 4px 14px rgba(16,185,129,.3); }
        .btn-success:hover { box-shadow: 0 8px 20px rgba(16,185,129,.4); }
        .btn-danger { background: linear-gradient(135deg,#ef4444,#dc2626); box-shadow: 0 4px 14px rgba(239,68,68,.3); }
        .btn-danger:hover { box-shadow: 0 8px 20px rgba(239,68,68,.4); }

        .form-group { margin-bottom: 16px; }
        .form-group label { display: block; margin-bottom: 6px; color: var(--ink-700); font-weight: 600; font-size: 13px; }
        .form-group input[type="file"] {
            width: 100%; padding: 10px; border-radius: var(--radius-sm);
            border: 1.5px dashed var(--ink-300); background: var(--bg-alt); font-size: 13px;
        }

        .error, .success, .message, .warning {
            padding: 13px 18px; border-radius: 12px; margin-bottom: 20px;
            font-size: 13.5px; font-weight: 500; display: flex; align-items: center; gap: 10px;
            border-left: 4px solid;
        }
        .error { background: var(--danger-bg); color: #b91c1c; border-color: var(--danger); }
        .success, .message { background: var(--success-bg); color: #047857; border-color: var(--success); }
        .warning { background: var(--warning-bg); color: #92610a; border-color: var(--warning); }

        .upload-section {
            background: var(--surface);
            padding: 28px;
            border-radius: var(--radius);
            box-shadow: var(--shadow-sm);
            margin-bottom: 20px;
            border: 1px solid rgba(26,26,62,.04);
        }
        .upload-section .sec-title {
            display: flex;
            align-items: center;
            gap: 10px;
            margin-bottom: 6px;
        }
        .upload-section .sec-title .icon {
            width: 36px; height: 36px;
            border-radius: 10px;
            background: var(--brand-grad-soft);
            color: var(--primary);
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 15px;
        }
        .upload-section h3 {
            color: var(--ink-900);
            font-size: 16px;
        }
        .upload-section .hint {
            color: var(--ink-500);
            font-size: 13px;
            margin: 6px 0 16px 46px;
        }

        .dropzone {
            border: 2px dashed var(--ink-300);
            border-radius: var(--radius-sm);
            background: var(--bg-alt);
            padding: 34px 20px;
            text-align: center;
            cursor: pointer;
            transition: all .2s;
            position: relative;
        }
        .dropzone:hover, .dropzone.dragover {
            border-color: var(--primary);
            background: var(--brand-grad-soft);
        }
        .dropzone i {
            font-size: 28px;
            color: var(--primary);
            margin-bottom: 10px;
        }
        .dropzone .dz-title {
            font-weight: 600;
            color: var(--ink-700);
            font-size: 14px;
        }
        .dropzone .dz-sub {
            font-size: 12px;
            color: var(--ink-500);
            margin-top: 4px;
        }
        .dropzone input[type="file"] {
            position: absolute;
            inset: 0;
            opacity: 0;
            cursor: pointer;
        }

        .file-chip-grid {
            display: flex;
            flex-wrap: wrap;
            gap: 10px;
            margin-top: 16px;
        }
        .file-chip {
            display: flex;
            align-items: center;
            gap: 8px;
            background: var(--bg-alt);
            border: 1px solid var(--ink-100);
            border-radius: 10px;
            padding: 6px 12px 6px 6px;
            font-size: 12.5px;
            color: var(--ink-700);
        }
        .file-chip .thumb {
            width: 26px;
            height: 26px;
            border-radius: 6px;
            object-fit: cover;
            background: var(--ink-100);
        }
        .file-chip .name {
            max-width: 140px;
            overflow: hidden;
            text-overflow: ellipsis;
            white-space: nowrap;
        }

        .results-table-wrap { margin-top: 18px; }
        .status-pill {
            padding: 3px 10px;
            border-radius: 20px;
            font-size: 11px;
            font-weight: 700;
            text-transform: uppercase;
            letter-spacing: .3px;
        }
        .status-pill.matched { background: var(--success-bg); color: #047857; }
        .status-pill.unmatched { background: var(--warning-bg); color: #92610a; }
        .status-pill.error { background: var(--danger-bg); color: #b91c1c; }

        .instructions {
            background: var(--bg-alt);
            padding: 20px 22px;
            border-radius: var(--radius-sm);
            margin-top: 20px;
            border: 1px solid var(--ink-100);
        }
        .instructions h4 { color: var(--ink-900); margin-bottom: 10px; font-size: 14px; }
        .instructions ul { list-style: none; padding: 0; }
        .instructions ul li { padding: 5px 0; color: var(--ink-500); font-size: 13px; }
        .instructions ul li:before { content: "▸ "; color: var(--primary); font-weight: bold; }
        .instructions ul li strong { color: var(--ink-700); }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 10px;
        }
        th {
            text-align: left;
            padding: 12px 14px;
            background: var(--bg-alt);
            color: var(--ink-500);
            font-weight: 700;
            font-size: 11.5px;
            text-transform: uppercase;
            letter-spacing: .4px;
        }
        td {
            padding: 12px 14px;
            border-bottom: 1px solid var(--ink-100);
            font-size: 13.5px;
            color: var(--ink-700);
        }
        tr:last-child td { border-bottom: none; }

        .csv-template-link {
            display: inline-block;
            margin-top: 10px;
            padding: 8px 16px;
            background: var(--info-bg);
            color: var(--info);
            border-radius: 8px;
            text-decoration: none;
            font-size: 13px;
            font-weight: 600;
        }
        .csv-template-link:hover {
            background: var(--info);
            color: #fff;
        }

        .summary-stats {
            display: flex;
            gap: 20px;
            margin-bottom: 15px;
            flex-wrap: wrap;
        }
        .summary-stat {
            padding: 8px 16px;
            border-radius: 8px;
            font-weight: 600;
            font-size: 14px;
        }
        .summary-stat.matched { background: var(--success-bg); color: #047857; }
        .summary-stat.unmatched { background: var(--warning-bg); color: #92610a; }
        .summary-stat.rejected { background: var(--danger-bg); color: #b91c1c; }

        @media (max-width: 900px) {
            :root { --sidebar-w: 76px; }
            .sidebar .brand .brand-text, .sidebar .user-info .meta, .sidebar a.nav-link span { display: none; }
            .sidebar .brand, .sidebar .user-info { justify-content: center; padding-left: 0; padding-right: 0; }
            .sidebar a.nav-link { justify-content: center; }
        }
        @media (max-width: 576px) {
            .main-content { padding: 18px; }
        }
    </style>
</head>
<body>
    <?php
    // Sidebar data
    $initials = '';
    if (!empty($_SESSION['user_name'])) {
        $parts = preg_split('/\s+/', trim($_SESSION['user_name']));
        $initials = strtoupper(substr($parts[0], 0, 1) . (isset($parts[1]) ? substr($parts[1], 0, 1) : ''));
    }
    ?>
    <div class="sidebar">
        <div class="brand">
            <div class="logo-badge">🎓</div>
            <div class="brand-text">
                <div class="t1">SKUAST-K</div>
                <div class="t2">Degree Portal</div>
            </div>
        </div>
        <div class="user-info">
            <div class="avatar"><?php echo htmlspecialchars($initials ?: 'U'); ?></div>
            <div class="meta">
                <div class="name"><?php echo htmlspecialchars($_SESSION['user_name'] ?? 'User'); ?></div>
                <div class="email"><?php echo htmlspecialchars($_SESSION['user_email'] ?? ''); ?></div>
                <?php if (!empty($_SESSION['user_role'])): ?>
                    <span class="role-pill"><?php echo htmlspecialchars($_SESSION['user_role']); ?></span>
                <?php endif; ?>
            </div>
        </div>
        <nav>
            <a href="dashboard.php" class="nav-link"><i class="fas fa-tachometer-alt"></i><span>Dashboard</span></a>
            <a href="add_certificate.php" class="nav-link"><i class="fas fa-plus-circle"></i><span>Add Certificate</span></a>
            <a href="bulk_upload.php" class="nav-link active"><i class="fas fa-upload"></i><span>Bulk Upload</span></a>
            <a href="map_photo.php" class="nav-link"><i class="fas fa-image"></i><span>Map Photo</span></a>
            <a href="generate_certificate.php" class="nav-link"><i class="fas fa-file-pdf"></i><span>Generate Certificate</span></a>
            <a href="view_certificates.php" class="nav-link"><i class="fas fa-list"></i><span>View All</span></a>
            <div class="nav-logout-wrap">
                <a href="logout.php" class="nav-link"><i class="fas fa-sign-out-alt"></i><span>Logout</span></a>
            </div>
        </nav>
    </div>

    <div class="main-content">
        <div class="page-header">
            <div>
                <h1>Bulk Upload</h1>
                <div class="subtitle">Import student records from CSV and map photos in one workflow</div>
            </div>
            <a href="dashboard.php" class="btn btn-secondary"><i class="fas fa-arrow-left"></i> Back</a>
        </div>

        <?php echo $message; ?>

        <!-- CSV Upload Section -->
        <div class="upload-section">
            <div class="sec-title"><div class="icon"><i class="fas fa-file-csv"></i></div><h3>Upload CSV Data</h3></div>
            <div class="hint">Upload a CSV file with student data. First row should be the header row.</div>
            <form method="POST" action="" enctype="multipart/form-data">
                <div class="form-group">
                    <label>CSV File (.csv)</label>
                    <input type="file" name="csv_file" accept=".csv" required>
                </div>
                <button type="submit" class="btn"><i class="fas fa-upload"></i> Upload CSV</button>
                <a href="#" class="csv-template-link" onclick="downloadTemplate()"><i class="fas fa-download"></i> Download CSV Template</a>
            </form>
        </div>

        <!-- Photo Upload Section -->
        <div class="upload-section">
            <div class="sec-title"><div class="icon"><i class="fas fa-images"></i></div><h3>Upload Student Photos</h3></div>
            <div class="hint">Each photo is stored directly in the database as image data (BLOB) and matched to a student by registration number.</div>
            <form method="POST" action="" enctype="multipart/form-data" id="photoBulkForm">
                <div class="dropzone" id="dropzone">
                    <input type="file" name="photos[]" id="photosInput" accept="image/*" multiple>
                    <i class="fas fa-cloud-arrow-up"></i>
                    <div class="dz-title">Drag & drop photos here, or click to browse</div>
                    <div class="dz-sub">Filename must exactly match the registration number, e.g. 2020BCT1001.jpg</div>
                </div>
                <div class="file-chip-grid" id="fileChipGrid"></div>
                <div style="margin-top:18px;">
                    <button type="submit" class="btn btn-success" id="photoSubmitBtn"><i class="fas fa-upload"></i> Map Photos</button>
                </div>
            </form>
        </div>

        <!-- Per-file results -->
        <?php if (!empty($photo_results)): ?>
        <div class="upload-section results-table-wrap">
            <div class="sec-title"><div class="icon"><i class="fas fa-list-check"></i></div><h3>Photo Mapping Results</h3></div>
            
            <!-- Summary Stats -->
            <div class="summary-stats">
                <span class="summary-stat matched">✅ Matched: <?php echo $matched_count; ?></span>
                <span class="summary-stat unmatched">⚠️ Unmatched: <?php echo $unmatched_count; ?></span>
                <span class="summary-stat rejected">❌ Rejected: <?php echo $rejected_count; ?></span>
                <span class="summary-stat" style="background: var(--info-bg); color: var(--info);">📊 Total: <?php echo count($photo_results); ?></span>
            </div>
            
            <table>
                <thead>
                    <tr>
                        <th>File</th>
                        <th>Registration No</th>
                        <th>Status</th>
                        <th>Details</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($photo_results as $r): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($r['filename']); ?></td>
                        <td><strong><?php echo htmlspecialchars($r['registration_no']); ?></strong></td>
                        <td><span class="status-pill <?php echo $r['status']; ?>"><?php echo ucfirst($r['status']); ?></span></td>
                        <td><?php echo $r['note']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <?php endif; ?>

        <div class="instructions">
            <h4>📋 Instructions</h4>
            <ul>
                <li><strong>CSV Format:</strong> id, registration_no, name, father_name, mother_name, degree, branch, specialization, grade_point_average, total_grade_points, gender, photo, certificate_no, year_of_passing, faculty, barcode</li>
                <li><strong>Required fields:</strong> registration_no, name, father_name, degree</li>
                <li><strong>Gender:</strong> Can be "Male"/"Female" or "M"/"F" - will be automatically converted</li>
                <li>Upload the CSV file first to create student records, then upload photos to map them</li>
                <li><strong>Photo filenames must exactly match the registration number</strong> (e.g., 2020BCT1001.jpg)</li>
                <li>Photos are written straight into the database as BLOBs — nothing is stored on disk</li>
                <li>Supported image formats: JPG, JPEG, PNG, GIF, WebP &middot; Maximum file size: 5MB per photo</li>
                <li><strong>Tip:</strong> Download the template above to get the correct CSV format</li>
            </ul>
        </div>
    </div>

    <script>
        // File chips for photo upload
        const dropzone = document.getElementById('dropzone');
        const photosInput = document.getElementById('photosInput');
        const fileChipGrid = document.getElementById('fileChipGrid');
        const photoSubmitBtn = document.getElementById('photoSubmitBtn');

        ['dragenter', 'dragover'].forEach(evt => {
            dropzone.addEventListener(evt, e => { e.preventDefault(); dropzone.classList.add('dragover'); });
        });
        ['dragleave', 'drop'].forEach(evt => {
            dropzone.addEventListener(evt, e => { e.preventDefault(); dropzone.classList.remove('dragover'); });
        });
        dropzone.addEventListener('drop', e => {
            if (e.dataTransfer.files.length) {
                photosInput.files = e.dataTransfer.files;
                renderChips();
            }
        });
        photosInput.addEventListener('change', renderChips);

        function renderChips() {
            fileChipGrid.innerHTML = '';
            const files = photosInput.files;
            if (files.length === 0) {
                return;
            }
            for (let i = 0; i < files.length; i++) {
                const f = files[i];
                const chip = document.createElement('div');
                chip.className = 'file-chip';
                const img = document.createElement('img');
                img.className = 'thumb';
                const reader = new FileReader();
                reader.onload = e => img.src = e.target.result;
                reader.readAsDataURL(f);
                const name = document.createElement('span');
                name.className = 'name';
                name.textContent = f.name;
                chip.appendChild(img);
                chip.appendChild(name);
                fileChipGrid.appendChild(chip);
            }
        }

        document.getElementById('photoBulkForm').addEventListener('submit', function(e) {
            const files = photosInput.files;
            if (files.length === 0) {
                e.preventDefault();
                alert('Please select at least one photo to upload.');
                return false;
            }
            photoSubmitBtn.innerHTML = '<i class="fas fa-spinner fa-spin"></i> Uploading...';
            photoSubmitBtn.disabled = true;
        });

        // Download CSV Template
        function downloadTemplate() {
            const headers = ['id', 'registration_no', 'name', 'father_name', 'mother_name', 'degree', 'branch', 'specialization', 'grade_point_average', 'total_grade_points', 'gender', 'photo', 'certificate_no', 'year_of_passing', 'faculty', 'barcode'];
            const sampleData = ['1', '2020BCT1001', 'Aarav Sharma', 'Rajesh Sharma', 'Sunita Sharma', 'B.Tech', 'Computer Science', 'Artificial Intelligence', '8.75', '87.5', 'Male', '', 'CERT/2024/0001', '2024', 'Faculty of Engineering', ''];
            
            let csvContent = headers.join(',') + '\n';
            csvContent += sampleData.join(',') + '\n';
            
            const blob = new Blob([csvContent], { type: 'text/csv' });
            const url = window.URL.createObjectURL(blob);
            const a = document.createElement('a');
            a.href = url;
            a.download = 'student_import_template.csv';
            document.body.appendChild(a);
            a.click();
            document.body.removeChild(a);
            window.URL.revokeObjectURL(url);
        }
    </script>
</body>
</html>