<?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';

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

// Cek apakah kolom whatsapp ada di tabel guru
$check_column = mysqli_query($conn, "SHOW COLUMNS FROM guru LIKE 'whatsapp'");
$whatsapp_column_exists = (mysqli_num_rows($check_column) > 0);

if (isset($_POST['import'])) {
    if (!empty($_FILES['file_excel']['tmp_name'])) {
        $inputFileName = $_FILES['file_excel']['tmp_name'];

        try {
            $excelReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
            $objPHPExcel = $excelReader->load($inputFileName);
            $sheet = $objPHPExcel->getActiveSheet();
            $highestRow = $sheet->getHighestRow();

            for ($row = 2; $row <= $highestRow; $row++) {
                $nip    = trim($sheet->getCellByColumnAndRow(0, $row)->getValue());
                $nama   = trim($sheet->getCellByColumnAndRow(1, $row)->getValue());
                $mapel  = trim($sheet->getCellByColumnAndRow(2, $row)->getValue());
                $whatsapp = trim($sheet->getCellByColumnAndRow(3, $row)->getValue());
                $status = trim($sheet->getCellByColumnAndRow(4, $row)->getValue());

                if ($nip != "" && $nama != "" && $mapel != "") {
                    // Validasi
                    if (!preg_match('/^[0-9]{8,18}$/', $nip)) {
                        $gagal++;
                        $errors[] = "Baris $row: NIP harus 8-18 digit angka.";
                        continue;
                    }
                    
                    if (strlen($nama) < 3 || strlen($nama) > 50) {
                        $gagal++;
                        $errors[] = "Baris $row: Nama harus 3-50 karakter.";
                        continue;
                    }
                    
                    if (strlen($mapel) > 30) {
                        $gagal++;
                        $errors[] = "Baris $row: Mata pelajaran maksimal 30 karakter.";
                        continue;
                    }
                    
                    // Validasi nomor WhatsApp
                    $whatsapp_value = '';
                    if (!empty($whatsapp)) {
                        $whatsapp_clean = preg_replace('/[^0-9]/', '', $whatsapp);
                        if (substr($whatsapp_clean, 0, 1) === '0') {
                            $whatsapp_value = '62' . substr($whatsapp_clean, 1);
                        } else {
                            $whatsapp_value = $whatsapp_clean;
                        }
                        
                        if (strlen($whatsapp_value) < 10 || strlen($whatsapp_value) > 15) {
                            $gagal++;
                            $errors[] = "Baris $row: Nomor WhatsApp harus 10-15 digit.";
                            continue;
                        }
                    }
                    
                    // Set status default jika kosong
                    if (empty($status)) {
                        $status = 'aktif';
                    } else {
                        $status = strtolower($status);
                        if (!in_array($status, ['aktif', 'nonaktif'])) {
                            $status = 'aktif';
                        }
                    }

                    // Siapkan query berdasarkan apakah kolom whatsapp ada
                    if ($whatsapp_column_exists) {
                        $sql = "INSERT INTO guru (nip, nama, mapel, whatsapp, status) 
                                VALUES ('$nip','$nama','$mapel','$whatsapp_value','$status') 
                                ON DUPLICATE KEY UPDATE 
                                nama='$nama', mapel='$mapel', whatsapp='$whatsapp_value', status='$status'";
                    } else {
                        $sql = "INSERT INTO guru (nip, nama, mapel, status) 
                                VALUES ('$nip','$nama','$mapel','$status') 
                                ON DUPLICATE KEY UPDATE 
                                nama='$nama', mapel='$mapel', status='$status'";
                    }
                    
                    if (mysqli_query($conn, $sql)) {
                        $berhasil++;

                        // Buat QR Code
                        $qr_dir = "assets/qr_guru/";
                        if (!is_dir($qr_dir)) mkdir($qr_dir, 0777, true);
                        QRcode::png($nip, $qr_dir . "$nip.png", QR_ECLEVEL_L, 4);
                    } else {
                        $gagal++;
                        $errors[] = "Baris $row: Gagal disimpan ke database. Error: " . mysqli_error($conn);
                    }
                }
            }

            $msg = "✅ Import selesai. <br> 
                    Berhasil: <b>$berhasil</b> <br> 
                    Gagal: <b>$gagal</b>";
                    
            if (!$whatsapp_column_exists) {
                $msg .= "<br><div class='alert alert-warning mt-2'>⚠️ Kolom WhatsApp tidak ditemukan. Data WhatsApp tidak disimpan. 
                         <a href='javascript:void(0)' onclick='showSQL()'>Klik di sini</a> untuk menambahkan kolom WhatsApp.</div>";
            }
        } catch (Exception $e) {
            $msg = "❌ Gagal membaca file: " . $e->getMessage();
        }
    } else {
        $msg = "❌ Harap pilih file Excel terlebih dahulu.";
    }
}

// Fungsi untuk membuat template Excel
if (isset($_GET['download_template'])) {
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $sheet = $objPHPExcel->getActiveSheet();
    
    // Set header
    $sheet->setCellValue('A1', 'NIP');
    $sheet->setCellValue('B1', 'Nama');
    $sheet->setCellValue('C1', 'Mata Pelajaran');
    
    // Tampilkan kolom WhatsApp hanya jika kolomnya ada di database
    if ($whatsapp_column_exists) {
        $sheet->setCellValue('D1', 'No. WhatsApp');
        $sheet->setCellValue('E1', 'Status');
    } else {
        $sheet->setCellValue('D1', 'Status');
    }
    
    // Set contoh data
    $sheet->setCellValue('A2', '198012342023012001');
    $sheet->setCellValue('B2', 'Dr. Ahmad Sutisna, M.Pd');
    $sheet->setCellValue('C2', 'Matematika');
    
    if ($whatsapp_column_exists) {
        $sheet->setCellValue('D2', '081234567890');
        $sheet->setCellValue('E2', 'Aktif');
        
        $sheet->setCellValue('A3', '197807172022031002');
        $sheet->setCellValue('B3', 'Drs. Budi Santoso');
        $sheet->setCellValue('C3', 'Bahasa Indonesia');
        $sheet->setCellValue('D3', '6281298765432');
        $sheet->setCellValue('E3', 'Aktif');
    } else {
        $sheet->setCellValue('D2', 'Aktif');
        
        $sheet->setCellValue('A3', '197807172022031002');
        $sheet->setCellValue('B3', 'Drs. Budi Santoso');
        $sheet->setCellValue('C3', 'Bahasa Indonesia');
        $sheet->setCellValue('D3', 'Aktif');
    }
    
    // Style header
    $headerStyle = array(
        'font' => array('bold' => true),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'E0E0E0')
        )
    );
    
    if ($whatsapp_column_exists) {
        $sheet->getStyle('A1:E1')->applyFromArray($headerStyle);
        foreach(range('A','E') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    } else {
        $sheet->getStyle('A1:D1')->applyFromArray($headerStyle);
        foreach(range('A','D') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    }
    
    // Download file
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="template_import_guru.xlsx"');
    header('Cache-Control: max-age=0');
    
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;
}
?>
<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <title>Import Data Guru</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">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
  <style>
    body { background-color: #f8f9fa; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; }
    .card { box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.15); border-radius: 1rem; border: none; }
    .btn-download { background-color: #6c757d; color: white; border: none; }
    .btn-download:hover { background-color: #5a6268; color: white; }
    .alert-info { border-radius: 0.75rem; }
    .table th { background-color: #f1f5f9; }
    .header-title { color: #2c3e50; font-weight: 700; margin-bottom: 0.5rem; }
    .step-card { background: linear-gradient(135deg, #4361ee 0%, #3a0ca3 100%); color: white; border-radius: 1rem; }
    .step-number { background-color: white; color: #4361ee; width: 40px; height: 40px; border-radius: 50%; display: flex; align-items: center; justify-content: center; font-weight: bold; margin-right: 15px; }
    .warning-badge { background-color: #ffc107; color: #000; padding: 5px 10px; border-radius: 20px; font-size: 0.8rem; }
    .sql-code { background-color: #f8f9fa; padding: 15px; border-radius: 5px; border-left: 4px solid #0d6efd; font-family: monospace; }
  </style>
</head>
<body class="container py-4">

  <div class="row justify-content-center">
    <div class="col-md-10">
      <h2 class="mb-4 text-center header-title"><i class="fas fa-chalkboard-teacher me-2"></i>Import Data Guru dari Excel</h2>

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

      <?php if (!$whatsapp_column_exists): ?>
      <div class="alert alert-warning">
        <div class="d-flex">
          <i class="fas fa-exclamation-triangle me-3 fs-4"></i>
          <div>
            <h5 class="alert-heading">Peringatan: Kolom WhatsApp tidak ditemukan dalam database!</h5>
            <p class="mb-2">Data WhatsApp tidak akan disimpan sampai kolom whatsapp ditambahkan ke tabel guru.</p>
            <button class="btn btn-sm btn-outline-dark" onclick="showSQL()">
              <i class="fas fa-code me-1"></i>Lihat Query SQL untuk Memperbaiki
            </button>
          </div>
        </div>
      </div>
      <?php endif; ?>

      <a href="guru.php" class="btn btn-secondary mb-3"><i class="fas fa-arrow-left me-1"></i>Kembali ke Data Guru</a>

      <div class="row mb-4">
        <div class="col-md-4 mb-3">
          <div class="step-card p-3 h-100">
            <div class="d-flex align-items-center">
              <div class="step-number">1</div>
              <div>Download template Excel</div>
            </div>
          </div>
        </div>
        <div class="col-md-4 mb-3">
          <div class="step-card p-3 h-100">
            <div class="d-flex align-items-center">
              <div class="step-number">2</div>
              <div>Isi data guru sesuai format</div>
            </div>
          </div>
        </div>
        <div class="col-md-4 mb-3">
          <div class="step-card p-3 h-100">
            <div class="d-flex align-items-center">
              <div class="step-number">3</div>
              <div>Upload file Excel</div>
            </div>
          </div>
        </div>
      </div>

      <div class="card p-4 mb-4">
        <form method="post" enctype="multipart/form-data">
          <div class="mb-3">
            <label class="form-label fw-bold">Pilih File Excel (.xls / .xlsx)</label>
            <input type="file" name="file_excel" accept=".xls,.xlsx" class="form-control" required>
            <div class="form-text">Pastikan file Excel sesuai dengan format yang ditentukan</div>
          </div>
          <div class="d-flex gap-2 flex-wrap">
            <button type="submit" name="import" class="btn btn-success">
              <i class="fas fa-upload me-1"></i> Import Data
            </button>
            <a href="?download_template=1" class="btn btn-download">
              <i class="fas fa-download me-1"></i> Unduh Template Excel
            </a>
          </div>
        </form>
      </div>

      <div class="card p-4">
        <h5 class="card-title"><i class="fas fa-info-circle me-2"></i>Format Kolom Excel</h5>
        <p class="text-muted">Baris pertama harus header dengan format berikut:</p>
        
        <div class="table-responsive">
          <table class="table table-bordered">
            <thead class="table-light">
              <tr>
                <th>Kolom A</th>
                <th>Kolom B</th>
                <th>Kolom C</th>
                <?php if ($whatsapp_column_exists): ?>
                <th>Kolom D</th>
                <th>Kolom E</th>
                <?php else: ?>
                <th>Kolom D</th>
                <?php endif; ?>
              </tr>
            </thead>
            <tbody>
              <tr>
                <td><strong>NIP</strong><br><span class="text-muted">Contoh: 198012342023012001</span></td>
                <td><strong>Nama</strong><br><span class="text-muted">Contoh: Dr. Ahmad Sutisna, M.Pd</span></td>
                <td><strong>Mata Pelajaran</strong><br><span class="text-muted">Contoh: Matematika</span></td>
                <?php if ($whatsapp_column_exists): ?>
                <td><strong>No. WhatsApp</strong><br><span class="text-muted">Contoh: 081234567890</span></td>
                <td><strong>Status</strong><br><span class="text-muted">Contoh: Aktif</span></td>
                <?php else: ?>
                <td><strong>Status</strong><br><span class="text-muted">Contoh: Aktif</span></td>
                <?php endif; ?>
              </tr>
            </tbody>
          </table>
        </div>
        
        <div class="alert alert-warning mt-3">
          <strong><i class="fas fa-exclamation-triangle me-1"></i>Catatan:</strong>
          <ul class="mb-0 mt-2">
            <li>Kolom <strong>NIP</strong> harus unik dan terdiri dari 8-18 digit angka</li>
            <?php if ($whatsapp_column_exists): ?>
            <li>Kolom <strong>No. WhatsApp</strong> bersifat opsional (format: 08xxx atau 628xxx)</li>
            <?php else: ?>
            <li><span class="warning-badge"><i class="fas fa-exclamation-circle me-1"></i>Kolom WhatsApp tidak tersedia</span></li>
            <?php endif; ?>
            <li>Kolom <strong>Status</strong> bersifat opsional (default: Aktif, pilihan: Aktif/Nonaktif)</li>
            <li>File Excel harus dalam format .xls atau .xlsx</li>
            <li>Data dengan NIP yang sudah ada akan diperbarui</li>
          </ul>
        </div>
      </div>

      <?php if (!empty($errors)) { ?>
        <div class="card mt-4">
          <div class="card-header bg-danger text-white">
            <i class="fas fa-exclamation-circle me-1"></i> Detail Error
          </div>
          <div class="card-body">
            <ul class="mb-0">
              <?php foreach ($errors as $err) { echo "<li>$err</li>"; } ?>
            </ul>
          </div>
        </div>
      <?php } ?>
    </div>
  </div>

  <!-- Modal untuk menampilkan SQL -->
  <div class="modal fade" id="sqlModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-lg">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title"><i class="fas fa-database me-2"></i>Perbaikan Database</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
        </div>
        <div class="modal-body">
          <div class="alert alert-info">
            <i class="fas fa-info-circle me-2"></i>Jalankan query SQL berikut di phpMyAdmin atau tool database management Anda untuk menambahkan kolom whatsapp:
          </div>
          
          <pre class="sql-code">ALTER TABLE guru ADD whatsapp VARCHAR(20) NULL AFTER mapel;</pre>
          
          <div class="alert alert-secondary mt-3">
            <h6><i class="fas fa-lightbulb me-2"></i>Cara menjalankan query:</h6>
            <ol class="mb-0">
              <li>Buka phpMyAdmin</li>
              <li>Pilih database yang digunakan</li>
              <li>Klik tab "SQL"</li>
              <li>Salin dan tempel query di atas</li>
              <li>Klik "Go" atau "Jalankan"</li>
              <li>Refresh halaman ini setelah query berhasil dijalankan</li>
            </ol>
          </div>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Tutup</button>
          <a href="guru.php" class="btn btn-primary">Refresh Halaman</a>
        </div>
      </div>
    </div>
  </div>

  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
  <script>
    function showSQL() {
      var sqlModal = new bootstrap.Modal(document.getElementById('sqlModal'));
      sqlModal.show();
    }
  </script>
</body>
</html>