<?php
session_start();
if (!isset($_SESSION['username']) || $_SESSION['role'] !== 'admin') {
    header("Location: index.php");
    exit;
}
include 'config.php';
require 'vendor/phpqrcode/qrlib.php';
require_once 'phpexcel/Classes/PHPExcel.php'; // pakai PHPExcel klasik

$msg = "";
$berhasil = 0;
$gagal = 0;
$errors = [];

// Fungsi untuk membuat template Excel/CSV
if (isset($_GET['download_template'])) {
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $sheet = $objPHPExcel->getActiveSheet();
    
    // Set header
    $sheet->setCellValue('A1', 'NIS (1-12 angka)');
    $sheet->setCellValue('B1', 'NISN (1-12 angka)');
    $sheet->setCellValue('C1', 'Nama Lengkap');
    $sheet->setCellValue('D1', 'Kelas (maks 4 karakter)');
    $sheet->setCellValue('E1', 'No. WA (opsional)');
    
    // Set contoh data
    $sheet->setCellValue('A2', '4321');
    $sheet->setCellValue('B2', '9876543210');
    $sheet->setCellValue('C2', 'Insan Kamil');
    $sheet->setCellValue('D2', '7A');
    $sheet->setCellValue('E2', '081234567890');
    
    // Set lebar kolom
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(30);
    $sheet->getColumnDimension('D')->setWidth(15);
    $sheet->getColumnDimension('E')->setWidth(20);
    
    // Format header
    $headerStyle = array(
        'font' => array('bold' => true),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'E6E6E6')
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
            )
        )
    );
    
    $sheet->getStyle('A1:E1')->applyFromArray($headerStyle);
    
    // Simpan sebagai CSV dengan pemisah titik koma
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
    $objWriter->setDelimiter(';');
    $objWriter->setEnclosure('');
    $objWriter->setLineEnding("\r\n");
    
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="template_siswa.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    $objWriter->save('php://output');
    exit;
}

if (isset($_POST['import'])) {
    if (!empty($_FILES['file_excel']['tmp_name'])) {
        $inputFileName = $_FILES['file_excel']['tmp_name'];
        $fileExtension = pathinfo($_FILES['file_excel']['name'], PATHINFO_EXTENSION);

        try {
            // Jika file CSV
            if (strtolower($fileExtension) == 'csv') {
                $file = fopen($inputFileName, 'r');
                $row = 1;
                
                while (($data = fgetcsv($file, 1000, ";")) !== FALSE) {
                    if ($row == 1) { // Skip header
                        $row++;
                        continue;
                    }
                    
                    // Bersihkan data dari BOM dan spasi
                    $nis   = trim(str_replace("\xEF\xBB\xBF", '', $data[0]));
                    $nisn  = trim(str_replace("\xEF\xBB\xBF", '', $data[1]));
                    $nama  = trim(str_replace("\xEF\xBB\xBF", '', $data[2]));
                    $kelas = trim(str_replace("\xEF\xBB\xBF", '', $data[3]));
                    $no_wa = isset($data[4]) ? trim(str_replace("\xEF\xBB\xBF", '', $data[4])) : '';

                    // Validasi: Semua field wajib kecuali no_wa (opsional)
                    if ($nis != "" && $nisn != "" && $nama != "" && $kelas != "") {
                        // Validasi NIS (minimal 1, maksimal 12 karakter, hanya angka)
                        if (strlen($nis) < 1 || strlen($nis) > 12) {
                            $gagal++;
                            $errors[] = "Baris $row: NIS harus 1-12 karakter (diisi: '$nis').";
                            $row++;
                            continue;
                        }
                        
                        // Validasi NIS hanya boleh angka
                        if (!preg_match('/^[0-9]+$/', $nis)) {
                            $gagal++;
                            $errors[] = "Baris $row: NIS harus angka saja (diisi: '$nis').";
                            $row++;
                            continue;
                        }
                        
                        // Validasi NISN (minimal 1, maksimal 12 karakter, hanya angka)
                        if (strlen($nisn) < 1 || strlen($nisn) > 12) {
                            $gagal++;
                            $errors[] = "Baris $row: NISN harus 1-12 karakter (diisi: '$nisn').";
                            $row++;
                            continue;
                        }
                        
                        // Validasi NISN hanya boleh angka
                        if (!preg_match('/^[0-9]+$/', $nisn)) {
                            $gagal++;
                            $errors[] = "Baris $row: NISN harus angka saja (diisi: '$nisn').";
                            $row++;
                            continue;
                        }
                        
                        // Validasi Kelas (maksimal 4 karakter)
                        if (strlen($kelas) > 4) {
                            $gagal++;
                            $errors[] = "Baris $row: Kelas maksimal 4 karakter (diisi: '$kelas').";
                            $row++;
                            continue;
                        }
                        
                        // Validasi No. WA (opsional, jika diisi harus angka dan minimal 10 digit)
                        if ($no_wa != "" && !preg_match('/^[0-9]{10,}$/', $no_wa)) {
                            $gagal++;
                            $errors[] = "Baris $row: No. WA harus angka minimal 10 digit (kosongkan jika tidak ada).";
                            $row++;
                            continue;
                        }
                        
                        // Jika no_wa kosong, set NULL
                        $no_wa_db = ($no_wa == "") ? "NULL" : "'$no_wa'";

                        // Simpan ke database
                        $sql = "INSERT INTO siswa (nis, nisn, nama, kelas, status, no_wa) 
                                VALUES ('$nis','$nisn','$nama','$kelas','aktif',$no_wa_db) 
                                ON DUPLICATE KEY UPDATE 
                                nama='$nama', 
                                kelas='$kelas', 
                                status='aktif', 
                                no_wa=$no_wa_db";
                        
                        if (mysqli_query($conn, $sql)) {
                            $berhasil++;

                            // Buat QR Code
                            $qr_dir = "assets/qr/";
                            if (!is_dir($qr_dir)) mkdir($qr_dir, 0777, true);
                            QRcode::png($nisn, $qr_dir . "$nisn.png", QR_ECLEVEL_L, 4);
                        } else {
                            $gagal++;
                            $errors[] = "Baris $row: Gagal disimpan ke database. Error: " . mysqli_error($conn);
                        }
                    } else {
                        $gagal++;
                        $errors[] = "Baris $row: NIS, NISN, Nama, dan Kelas wajib diisi.";
                    }
                    
                    $row++;
                }
                fclose($file);
                
            } else { // Jika file Excel (xls, xlsx)
                $excelReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
                $objPHPExcel = $excelReader->load($inputFileName);
                $sheet = $objPHPExcel->getActiveSheet();
                $highestRow = $sheet->getHighestRow();

                for ($row = 2; $row <= $highestRow; $row++) { // baris 1 header
                    $nis   = trim($sheet->getCellByColumnAndRow(0, $row)->getValue());
                    $nisn  = trim($sheet->getCellByColumnAndRow(1, $row)->getValue());
                    $nama  = trim($sheet->getCellByColumnAndRow(2, $row)->getValue());
                    $kelas = trim($sheet->getCellByColumnAndRow(3, $row)->getValue());
                    $no_wa = trim($sheet->getCellByColumnAndRow(4, $row)->getValue());

                    // Validasi: Semua field wajib kecuali no_wa (opsional)
                    if ($nis != "" && $nisn != "" && $nama != "" && $kelas != "") {
                        // Validasi NIS (minimal 1, maksimal 12 karakter, hanya angka)
                        if (strlen($nis) < 1 || strlen($nis) > 12) {
                            $gagal++;
                            $errors[] = "Baris $row: NIS harus 1-12 karakter (diisi: '$nis').";
                            continue;
                        }
                        
                        // Validasi NIS hanya boleh angka
                        if (!preg_match('/^[0-9]+$/', $nis)) {
                            $gagal++;
                            $errors[] = "Baris $row: NIS harus angka saja (diisi: '$nis').";
                            continue;
                        }
                        
                        // Validasi NISN (minimal 1, maksimal 12 karakter, hanya angka)
                        if (strlen($nisn) < 1 || strlen($nisn) > 12) {
                            $gagal++;
                            $errors[] = "Baris $row: NISN harus 1-12 karakter (diisi: '$nisn').";
                            continue;
                        }
                        
                        // Validasi NISN hanya boleh angka
                        if (!preg_match('/^[0-9]+$/', $nisn)) {
                            $gagal++;
                            $errors[] = "Baris $row: NISN harus angka saja (diisi: '$nisn').";
                            continue;
                        }
                        
                        // Validasi Kelas (maksimal 4 karakter)
                        if (strlen($kelas) > 4) {
                            $gagal++;
                            $errors[] = "Baris $row: Kelas maksimal 4 karakter (diisi: '$kelas').";
                            continue;
                        }
                        
                        // Validasi No. WA (opsional, jika diisi harus angka dan minimal 10 digit)
                        if ($no_wa != "" && !preg_match('/^[0-9]{10,}$/', $no_wa)) {
                            $gagal++;
                            $errors[] = "Baris $row: No. WA harus angka minimal 10 digit (kosongkan jika tidak ada).";
                            continue;
                        }
                        
                        // Jika no_wa kosong, set NULL
                        $no_wa_db = ($no_wa == "") ? "NULL" : "'$no_wa'";

                        // Simpan ke database
                        $sql = "INSERT INTO siswa (nis, nisn, nama, kelas, status, no_wa) 
                                VALUES ('$nis','$nisn','$nama','$kelas','aktif',$no_wa_db) 
                                ON DUPLICATE KEY UPDATE 
                                nama='$nama', 
                                kelas='$kelas', 
                                status='aktif', 
                                no_wa=$no_wa_db";
                        
                        if (mysqli_query($conn, $sql)) {
                            $berhasil++;

                            // Buat QR Code
                            $qr_dir = "assets/qr/";
                            if (!is_dir($qr_dir)) mkdir($qr_dir, 0777, true);
                            QRcode::png($nisn, $qr_dir . "$nisn.png", QR_ECLEVEL_L, 4);
                        } else {
                            $gagal++;
                            $errors[] = "Baris $row: Gagal disimpan ke database. Error: " . mysqli_error($conn);
                        }
                    } else {
                        $gagal++;
                        $errors[] = "Baris $row: NIS, NISN, Nama, dan Kelas wajib diisi.";
                    }
                }
            }

            $msg = "✅ Import selesai. <br> 
                    Berhasil: <b>$berhasil</b> <br> 
                    Gagal: <b>$gagal</b>";
        } catch (Exception $e) {
            $msg = "❌ Gagal membaca file: " . $e->getMessage();
        }
    } else {
        $msg = "❌ Harap pilih file CSV/Excel terlebih dahulu.";
    }
}
?>
<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <title>Import Data Siswa</title>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    .format-table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 10px;
    }
    .format-table th, .format-table td {
        border: 1px solid #ddd;
        padding: 8px;
        text-align: left;
    }
    .format-table th {
        background-color: #f8f9fa;
        font-weight: bold;
    }
    .format-table tr:nth-child(even) {
        background-color: #f2f2f2;
    }
    .badge-info {
        background-color: #17a2b8;
        color: white;
        padding: 3px 8px;
        border-radius: 4px;
        font-size: 0.85em;
    }
    .preview-csv {
        background-color: #f8f9fa;
        padding: 15px;
        border-radius: 5px;
        border: 1px solid #ddd;
        font-family: 'Courier New', monospace;
        font-size: 14px;
        line-height: 1.5;
        white-space: pre;
        overflow-x: auto;
    }
  </style>
</head>
<body class="container py-4">

  <h2 class="mb-4 text-center">📥 Import Data Siswa</h2>

  <?php if (!empty($msg)) echo "<div class='alert alert-info'>$msg</div>"; ?>

  <a href="siswa.php" class="btn btn-secondary mb-3">← Kembali</a>

  <div class="card p-3 mb-4">
    <form method="post" enctype="multipart/form-data">
      <div class="mb-3">
        <label class="form-label">Pilih File CSV/Excel</label>
        <input type="file" name="file_excel" accept=".csv,.xls,.xlsx" class="form-control" required>
        <div class="form-text">
          <span class="badge-info">Format CSV: Gunakan pemisah titik koma (;)</span><br>
          <span class="badge-info">Format Excel: .xls atau .xlsx</span>
        </div>
      </div>
      <button type="submit" name="import" class="btn btn-success">Import</button>
      <a href="?download_template" class="btn btn-primary">📥 Download Template CSV</a>
    </form>
  </div>

  <div class="card p-3">
    <h5>📋 Format File Import</h5>
    
    <div class="alert alert-info mb-3">
      <strong>Format CSV:</strong> Gunakan titik koma (;) sebagai pemisah kolom
    </div>
    
    <table class="format-table mb-4">
      <thead>
        <tr>
          <th width="20%">Kolom</th>
          <th width="30%">Deskripsi</th>
          <th width="20%">Contoh Data</th>
          <th width="30%">Validasi</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td><b>Kolom 1: NIS</b></td>
          <td>Nomor Induk Siswa</td>
          <td>4321</td>
          <td>
            <span class="badge bg-primary">Wajib</span> 
            <span class="badge bg-info">1-12 digit</span>
            <span class="badge bg-info">Angka saja</span>
          </td>
        </tr>
        <tr>
          <td><b>Kolom 2: NISN</b></td>
          <td>Nomor Induk Siswa Nasional</td>
          <td>9876543210</td>
          <td>
            <span class="badge bg-primary">Wajib</span> 
            <span class="badge bg-info">1-12 digit</span>
            <span class="badge bg-info">Angka saja</span>
          </td>
        </tr>
        <tr>
          <td><b>Kolom 3: Nama</b></td>
          <td>Nama Lengkap Siswa</td>
          <td>Insan Kamil</td>
          <td><span class="badge bg-primary">Wajib</span> Nama lengkap</td>
        </tr>
        <tr>
          <td><b>Kolom 4: Kelas</b></td>
          <td>Kelas Siswa</td>
          <td>7A</td>
          <td><span class="badge bg-primary">Wajib</span> Maksimal 4 karakter</td>
        </tr>
        <tr>
          <td><b>Kolom 5: No. WA</b></td>
          <td>Nomor WhatsApp Orang Tua</td>
          <td>081234567890</td>
          <td>
            <span class="badge bg-secondary">Opsional</span> 
            <span class="badge bg-info">Minimal 10 digit</span>
            <span class="badge bg-info">Angka saja</span>
          </td>
        </tr>
      </tbody>
    </table>
    
    <h6 class="mt-4">📝 Contoh Isi File CSV:</h6>
    <div class="preview-csv">
NIS (1-12 angka);NISN (1-12 angka);Nama Lengkap;Kelas (maks 4 karakter);No. WA (opsional)
4321;9876543210;Insan Kamil;7A;081234567890
1234;123456789012;Ahmad Budi;8B;
5678;987654321;Siti Aminah;9C;082112345678
9999;5555555555;Budi Santoso;10A;
    </div>
    
    <div class="alert alert-warning mt-4">
      <strong>Perhatian:</strong>
      <ul class="mb-0 mt-2">
        <li>Baris pertama adalah header (akan diabaikan saat import)</li>
        <li>Data harus dimulai dari baris kedua</li>
        <li>Untuk format CSV, gunakan <strong>pemisah titik koma (;)</strong></li>
        <li>NIS dan NISN harus <strong>1-12 digit angka</strong></li>
        <li>Jika ada data yang sama (NISN), data akan diperbarui (update)</li>
        <li>QR Code akan dibuat otomatis berdasarkan NISN</li>
        <li>Nomor WA opsional, jika diisi harus minimal 10 digit angka</li>
      </ul>
    </div>
  </div>

  <?php if (!empty($errors)) { ?>
    <div class="card mt-3 border-danger">
      <div class="card-header bg-danger text-white d-flex justify-content-between align-items-center">
        <span>❌ Detail Error (Total: <?php echo count($errors); ?> error)</span>
        <span class="badge bg-light text-danger"><?php echo $gagal; ?> data gagal</span>
      </div>
      <div class="card-body">
        <div style="max-height: 300px; overflow-y: auto;">
          <ul class="mb-0">
            <?php foreach ($errors as $err) { echo "<li>$err</li>"; } ?>
          </ul>
        </div>
      </div>
    </div>
  <?php } ?>

  <div class="mt-4 text-center text-muted">
    <small>© <?php echo date('Y'); ?> Sistem Absensi Sekolah</small>
  </div>

</body>
</html>