<?php
require_once 'config.php';
require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

requireLogin();
if ($_SESSION['role'] !== 'admin') {
    header('Location: dashboard.php?error=access_denied');
    exit();
}

$message = '';
$error = '';

// Handle file upload and import
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $type = sanitizeInput($_POST['type'] ?? '');
    
    if (empty($type)) {
        $error = 'Pilih jenis data yang akan diimport';
    } elseif ($_FILES['excel_file']['error'] !== 0) {
        $error = 'Error uploading file';
    } else {
        try {
            $spreadsheet = IOFactory::load($_FILES['excel_file']['tmp_name']);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();
            
            // Skip header row
            array_shift($rows);
            
            $success_count = 0;
            $error_count = 0;
            $errors = [];
            
            foreach ($rows as $index => $row) {
                // Skip empty rows
                if (empty(array_filter($row))) continue;
                
                $row_number = $index + 2; // +2 because we skipped header and array is 0-indexed
                
                try {
                    if ($type === 'students') {
                        // Expected columns: Name, Username, Password, Class, NIS
                        if (count($row) < 5) {
                            $errors[] = "Baris $row_number: Data tidak lengkap";
                            $error_count++;
                            continue;
                        }
                        
                        $name = sanitizeInput($row[0]);
                        $username = sanitizeInput($row[1]);
                        $password = $row[2];
                        $class = sanitizeInput($row[3]);
                        $nis = sanitizeInput($row[4]);
                        
                        if (empty($name) || empty($username) || empty($password)) {
                            $errors[] = "Baris $row_number: Nama, username, dan password harus diisi";
                            $error_count++;
                            continue;
                        }
                        
                        // Check if username already exists
                        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
                        $checkStmt->execute([$username]);
                        if ($checkStmt->fetchColumn() > 0) {
                            $errors[] = "Baris $row_number: Username '$username' sudah ada";
                            $error_count++;
                            continue;
                        }
                        
                        $stmt = $pdo->prepare("
                            INSERT INTO users (name, username, password, role, class, nis, status) 
                            VALUES (?, ?, MD5(?), 'siswa', ?, ?, 'aktif')
                        ");
                        
                        if ($stmt->execute([$name, $username, $password, $class, $nis])) {
                            $success_count++;
                        } else {
                            $errors[] = "Baris $row_number: Gagal menyimpan data";
                            $error_count++;
                        }
                        
                    } elseif ($type === 'teachers') {
                        // Expected columns: Name, Username, Password, Subject, NIP
                        if (count($row) < 5) {
                            $errors[] = "Baris $row_number: Data tidak lengkap";
                            $error_count++;
                            continue;
                        }
                        
                        $name = sanitizeInput($row[0]);
                        $username = sanitizeInput($row[1]);
                        $password = $row[2];
                        $subject = sanitizeInput($row[3]);
                        $nip = sanitizeInput($row[4]);
                        
                        if (empty($name) || empty($username) || empty($password) || empty($subject)) {
                            $errors[] = "Baris $row_number: Nama, username, password, dan mata pelajaran harus diisi";
                            $error_count++;
                            continue;
                        }
                        
                        // Check if username already exists
                        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
                        $checkStmt->execute([$username]);
                        if ($checkStmt->fetchColumn() > 0) {
                            $errors[] = "Baris $row_number: Username '$username' sudah ada";
                            $error_count++;
                            continue;
                        }
                        
                        $stmt = $pdo->prepare("
                            INSERT INTO users (name, username, password, role, subject, nip, status) 
                            VALUES (?, ?, MD5(?), 'guru', ?, ?, 'aktif')
                        ");
                        
                        if ($stmt->execute([$name, $username, $password, $subject, $nip])) {
                            $success_count++;
                        } else {
                            $errors[] = "Baris $row_number: Gagal menyimpan data";
                            $error_count++;
                        }
                        
                    } elseif ($type === 'classes') {
                        // Expected columns: Class Name, Homeroom Teacher, Student Count
                        if (count($row) < 3) {
                            $errors[] = "Baris $row_number: Data tidak lengkap";
                            $error_count++;
                            continue;
                        }
                        
                        $class_name = sanitizeInput($row[0]);
                        $homeroom_teacher = sanitizeInput($row[1]);
                        $student_count = intval($row[2]);
                        
                        if (empty($class_name)) {
                            $errors[] = "Baris $row_number: Nama kelas harus diisi";
                            $error_count++;
                            continue;
                        }
                        
                        // Check if class already exists
                        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM classes WHERE name = ?");
                        $checkStmt->execute([$class_name]);
                        if ($checkStmt->fetchColumn() > 0) {
                            $errors[] = "Baris $row_number: Kelas '$class_name' sudah ada";
                            $error_count++;
                            continue;
                        }
                        
                        $stmt = $pdo->prepare("
                            INSERT INTO classes (name, homeroom_teacher, student_count, status) 
                            VALUES (?, ?, ?, 'aktif')
                        ");
                        
                        if ($stmt->execute([$class_name, $homeroom_teacher, $student_count])) {
                            $success_count++;
                        } else {
                            $errors[] = "Baris $row_number: Gagal menyimpan data";
                            $error_count++;
                        }
                    }
                } catch(PDOException $e) {
                    $errors[] = "Baris $row_number: Database error - " . $e->getMessage();
                    $error_count++;
                }
            }
            
            $message = "Import selesai: $success_count berhasil, $error_count gagal";
            if (!empty($errors)) {
                $message .= "\n\nDetail error:\n" . implode("\n", array_slice($errors, 0, 10));
                if (count($errors) > 10) {
                    $message .= "\n... dan " . (count($errors) - 10) . " error lainnya";
                }
            }
            
        } catch (Exception $e) {
            $error = 'Error reading Excel file: ' . $e->getMessage();
        }
    }
}

// Generate template Excel file
if (isset($_GET['download_template'])) {
    $type = $_GET['download_template'];
    
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    if ($type === 'students') {
        $sheet->setCellValue('A1', 'Nama');
        $sheet->setCellValue('B1', 'Username');
        $sheet->setCellValue('C1', 'Password');
        $sheet->setCellValue('D1', 'Kelas');
        $sheet->setCellValue('E1', 'NIS');
        
        // Sample data
        $sheet->setCellValue('A2', 'Ahmad Rizki');
        $sheet->setCellValue('B2', 'ahmad123');
        $sheet->setCellValue('C2', 'password');
        $sheet->setCellValue('D2', 'X-A');
        $sheet->setCellValue('E2', '2024001');
        
        $filename = 'template_siswa.xlsx';
    } elseif ($type === 'teachers') {
        $sheet->setCellValue('A1', 'Nama');
        $sheet->setCellValue('B1', 'Username');
        $sheet->setCellValue('C1', 'Password');
        $sheet->setCellValue('D1', 'Mata Pelajaran');
        $sheet->setCellValue('E1', 'NIP');
        
        // Sample data
        $sheet->setCellValue('A2', 'Dr. Indira Sari');
        $sheet->setCellValue('B2', 'indira');
        $sheet->setCellValue('C2', 'password');
        $sheet->setCellValue('D2', 'Matematika');
        $sheet->setCellValue('E2', '198501012010012001');
        
        $filename = 'template_guru.xlsx';
    } else {
        $sheet->setCellValue('A1', 'Nama Kelas');
        $sheet->setCellValue('B1', 'Wali Kelas');
        $sheet->setCellValue('C1', 'Jumlah Siswa');
        
        // Sample data
        $sheet->setCellValue('A2', 'X-A');
        $sheet->setCellValue('B2', 'Dr. Indira Sari');
        $sheet->setCellValue('C2', '30');
        
        $filename = 'template_kelas.xlsx';
    }
    
    // Style the header
    $sheet->getStyle('A1:E1')->getFont()->setBold(true);
    $sheet->getStyle('A1:E1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
    $sheet->getStyle('A1:E1')->getFill()->getStartColor()->setRGB('4472C4');
    $sheet->getStyle('A1:E1')->getFont()->getColor()->setRGB('FFFFFF');
    
    // Auto-size columns
    foreach (range('A', 'E') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit();
}
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Excel - Sistem Pengumpulan Tugas</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
</head>
<body class="bg-gray-50 min-h-screen">
    <!-- Navigation -->
    <nav class="bg-white shadow-lg">
        <div class="max-w-7xl mx-auto px-4">
            <div class="flex justify-between items-center py-4">
                <div class="flex items-center">
                    <a href="dashboard.php" class="text-blue-600 hover:text-blue-800 mr-4">
                        <i class="fas fa-arrow-left text-xl"></i>
                    </a>
                    <i class="fas fa-file-excel text-2xl text-green-600 mr-3"></i>
                    <h1 class="text-xl font-bold text-gray-800">Import Data Excel</h1>
                </div>
                <div class="flex items-center space-x-4">
                    <span class="text-gray-600">Admin: <span class="font-medium"><?php echo htmlspecialchars($_SESSION['name']); ?></span></span>
                    <a href="logout.php" class="bg-red-500 text-white px-4 py-2 rounded-lg hover:bg-red-600 transition">
                        <i class="fas fa-sign-out-alt mr-2"></i>Logout
                    </a>
                </div>
            </div>
        </div>
    </nav>

    <div class="max-w-4xl mx-auto p-6">
        <!-- Messages -->
        <?php if ($message): ?>
            <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded mb-6">
                <i class="fas fa-check-circle mr-2"></i>
                <pre class="whitespace-pre-wrap"><?php echo htmlspecialchars($message); ?></pre>
            </div>
        <?php endif; ?>
        
        <?php if ($error): ?>
            <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded mb-6">
                <i class="fas fa-exclamation-circle mr-2"></i><?php echo htmlspecialchars($error); ?>
            </div>
        <?php endif; ?>

        <!-- Import Form -->
        <div class="bg-white rounded-xl shadow-lg p-8">
            <h2 class="text-2xl font-bold text-gray-800 mb-6">Import Data dari Excel</h2>
            
            <form method="POST" enctype="multipart/form-data" class="space-y-6">
                <div>
                    <label class="block text-sm font-medium text-gray-700 mb-2">Jenis Data</label>
                    <select name="type" id="dataType" class="w-full px-4 py-3 border border-gray-300 rounded-lg focus:ring-2 focus:ring-blue-500" required>
                        <option value="">Pilih jenis data</option>
                        <option value="students">Data Siswa</option>
                        <option value="teachers">Data Guru</option>
                        <option value="classes">Data Kelas</option>
                    </select>
                </div>
                
                <div>
                    <label class="block text-sm font-medium text-gray-700 mb-2">File Excel</label>
                    <input type="file" name="excel_file" accept=".xlsx,.xls" class="w-full px-4 py-3 border border-gray-300 rounded-lg focus:ring-2 focus:ring-blue-500" required>
                    <p class="text-sm text-gray-500 mt-1">Format: .xlsx atau .xls (Max: 5MB)</p>
                </div>
                
                <div class="flex space-x-4">
                    <button type="submit" class="bg-blue-600 text-white px-6 py-3 rounded-lg hover:bg-blue-700 transition font-medium">
                        <i class="fas fa-upload mr-2"></i>Import Data
                    </button>
                    
                    <button type="button" id="downloadTemplate" class="bg-green-600 text-white px-6 py-3 rounded-lg hover:bg-green-700 transition font-medium">
                        <i class="fas fa-download mr-2"></i>Download Template
                    </button>
                </div>
            </form>
        </div>

        <!-- Instructions -->
        <div class="bg-white rounded-xl shadow-lg p-8 mt-8">
            <h3 class="text-xl font-bold text-gray-800 mb-4">Petunjuk Import</h3>
            
            <div class="grid grid-cols-1 md:grid-cols-3 gap-6">
                <div class="border-l-4 border-blue-500 pl-4">
                    <h4 class="font-medium text-blue-700 mb-2">Data Siswa</h4>
                    <ul class="text-sm text-gray-600 space-y-1">
                        <li>• Kolom 1: Nama</li>
                        <li>• Kolom 2: Username</li>
                        <li>• Kolom 3: Password</li>
                        <li>• Kolom 4: Kelas</li>
                        <li>• Kolom 5: NIS</li>
                    </ul>
                </div>
                
                <div class="border-l-4 border-green-500 pl-4">
                    <h4 class="font-medium text-green-700 mb-2">Data Guru</h4>
                    <ul class="text-sm text-gray-600 space-y-1">
                        <li>• Kolom 1: Nama</li>
                        <li>• Kolom 2: Username</li>
                        <li>• Kolom 3: Password</li>
                        <li>• Kolom 4: Mata Pelajaran</li>
                        <li>• Kolom 5: NIP</li>
                    </ul>
                </div>
                
                <div class="border-l-4 border-purple-500 pl-4">
                    <h4 class="font-medium text-purple-700 mb-2">Data Kelas</h4>
                    <ul class="text-sm text-gray-600 space-y-1">
                        <li>• Kolom 1: Nama Kelas</li>
                        <li>• Kolom 2: Wali Kelas</li>
                        <li>• Kolom 3: Jumlah Siswa</li>
                    </ul>
                </div>
            </div>
            
            <div class="mt-6 p-4 bg-yellow-50 border border-yellow-200 rounded-lg">
                <h4 class="font-medium text-yellow-800 mb-2">
                    <i class="fas fa-exclamation-triangle mr-2"></i>Catatan Penting:
                </h4>
                <ul class="text-sm text-yellow-700 space-y-1">
                    <li>• Baris pertama harus berisi header kolom</li>
                    <li>• Username harus unik (tidak boleh sama)</li>
                    <li>• Pastikan format data sesuai dengan template</li>
                    <li>• Download template terlebih dahulu untuk panduan</li>
                </ul>
            </div>
        </div>
    </div>

    <script>
        document.getElementById('downloadTemplate').addEventListener('click', function() {
            const dataType = document.getElementById('dataType').value;
            if (!dataType) {
                alert('Pilih jenis data terlebih dahulu');
                return;
            }
            window.location.href = '?download_template=' + dataType;
        });
    </script>
</body>
</html>