<?php
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/auth.php';
requireLogin('admin');

$ok_sert = $err_sert = '';

// Handle upload Excel/CSV
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['upload_excel'])) {
    if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] === 0) {
        $ext  = strtolower(pathinfo($_FILES['excel_file']['name'], PATHINFO_EXTENSION));
        $rows = [];
        if ($ext === 'csv') {
            if (($h = fopen($_FILES['excel_file']['tmp_name'], 'r')) !== false) {
                fgetcsv($h);
                while (($row = fgetcsv($h)) !== false) {
                    if (count($row) >= 2 && trim($row[0]) && trim($row[1]))
                        $rows[] = [trim($row[0]), trim($row[1])];
                }
                fclose($h);
            }
        } elseif (in_array($ext, ['xlsx','xls'])) {
            $zip = new ZipArchive();
            if ($zip->open($_FILES['excel_file']['tmp_name']) === true) {
                $xml    = $zip->getFromName('xl/worksheets/sheet1.xml');
                $shared = $zip->getFromName('xl/sharedStrings.xml');
                $zip->close();
                $strings = [];
                if ($shared) {
                    preg_match_all('/<t[^>]*>(.*?)<\/t>/s', $shared, $m);
                    $strings = array_map('html_entity_decode', $m[1]);
                }
                if ($xml) {
                    preg_match_all('/<row[^>]*>(.*?)<\/row>/s', $xml, $rowM);
                    $first = true;
                    foreach ($rowM[1] as $rowXml) {
                        if ($first) { $first = false; continue; }
                        preg_match_all('/<c r="([A-Z]+)\d+"[^>]*t="s"[^>]*><v>(\d+)<\/v><\/c>/', $rowXml, $cells);
                        $data = [];
                        foreach ($cells[1] as $i => $col) $data[$col] = $strings[(int)$cells[2][$i]] ?? '';
                        if (!empty($data['A']) && !empty($data['B'])) $rows[] = [$data['A'], $data['B']];
                    }
                }
            }
        }
        if (empty($rows)) {
            $err_sert = 'File kosong atau format tidak sesuai.';
        } else {
            $count = 0;
            foreach ($rows as [$nama, $link]) {
                $mhs = fetch("SELECT id FROM kkn_mahasiswa WHERE nama = ?", [trim($nama)]);
                if ($mhs) {
                    $key = 'kkn_sertifikat_mhs_' . $mhs['id'];
                    query("INSERT INTO kkn_settings (setting_key, setting_value) VALUES (?,?) ON DUPLICATE KEY UPDATE setting_value=?",
                        [$key, trim($link), trim($link)]);
                    $count++;
                }
            }
            $ok_sert = "Berhasil menyimpan $count link sertifikat individual dari " . count($rows) . " baris data.";
        }
    } else {
        $err_sert = 'File gagal diupload.';
    }
}

// Handle link global
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_global'])) {
    $link = trim($_POST['link_global'] ?? '');
    query("INSERT INTO kkn_settings (setting_key, setting_value) VALUES ('kkn_sertifikat_link_global',?) ON DUPLICATE KEY UPDATE setting_value=?", [$link, $link]);
    $ok_sert = $link ? 'Link global berhasil disimpan.' : 'Link global dihapus.';
}

// Handle hapus individual
if (isset($_GET['del'])) {
    query("DELETE FROM kkn_settings WHERE setting_key=?", ['kkn_sertifikat_mhs_' . (int)$_GET['del']]);
    $ok_sert = 'Link sertifikat mahasiswa dihapus.';
}

// Handle hapus semua
if (isset($_POST['hapus_semua'])) {
    query("DELETE FROM kkn_settings WHERE setting_key LIKE 'kkn_sertifikat_mhs_%'");
    $ok_sert = 'Semua link sertifikat individual dihapus.';
}

// Download template CSV
if (isset($_GET['download_template'])) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="template-sertifikat-kkn.csv"');
    echo "Nama Mahasiswa,Link Google Drive\n";
    $all_mhs = fetchAll("SELECT nama FROM kkn_mahasiswa ORDER BY nama");
    foreach ($all_mhs as $m)
        echo '"' . str_replace('"','""',$m['nama']) . '","https://drive.google.com/file/d/FILE_ID/view"' . "\n";
    exit;
}

// Load data
$link_global = fetch("SELECT setting_value FROM kkn_settings WHERE setting_key='kkn_sertifikat_link_global'")['setting_value'] ?? '';
$sert_ind    = fetchAll("SELECT s.setting_key, s.setting_value, m.nama, m.nim, m.prodi
                         FROM kkn_settings s
                         JOIN kkn_mahasiswa m ON m.id = CAST(REPLACE(s.setting_key,'kkn_sertifikat_mhs_','') AS UNSIGNED)
                         WHERE s.setting_key LIKE 'kkn_sertifikat_mhs_%'
                         ORDER BY m.nama");
$total_mhs   = fetch("SELECT COUNT(*) as n FROM kkn_mahasiswa")['n'] ?? 0;
?>
<!DOCTYPE html>
<html lang="id">
<head>
<meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Kelola Sertifikat KKN</title>
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700&family=Playfair+Display:wght@700&display=swap" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@tabler/icons-webfont@2.47.0/tabler-icons.min.css">
<style>
*{box-sizing:border-box;margin:0;padding:0;}
body{font-family:'Inter',sans-serif;background:#F5F7FA;padding:2rem;color:#1C2A3A;font-size:.82rem;}
.page-title{font-family:'Playfair Display',serif;font-size:1.3rem;color:#0D2E5C;margin-bottom:1.5rem;display:flex;align-items:center;gap:8px;}
.card{background:#fff;border-radius:12px;border:1px solid #E2E8F0;overflow:hidden;margin-bottom:1.25rem;}
.card-hd{padding:.875rem 1.25rem;border-bottom:1px solid #F0F4F8;background:#F8FAFF;display:flex;align-items:center;justify-content:space-between;}
.card-hd-left{display:flex;align-items:center;gap:7px;}
.card-hd h2{font-size:.88rem;font-weight:700;color:#0D2E5C;}
.card-body{padding:1.25rem 1.5rem;}
.fg{margin-bottom:.875rem;}
.fg label{display:block;font-size:.75rem;font-weight:600;color:#444;margin-bottom:.3rem;}
.fg input{width:100%;padding:8px 10px;border:1px solid #E2E8F0;border-radius:7px;font-size:.82rem;font-family:'Inter',sans-serif;outline:none;}
.fg input:focus{border-color:#1A56A4;}
.btn{display:inline-flex;align-items:center;gap:5px;padding:7px 14px;border:none;border-radius:7px;font-size:.78rem;font-weight:600;cursor:pointer;font-family:'Inter',sans-serif;transition:.15s;text-decoration:none;}
.btn-navy{background:#0D2E5C;color:#fff;}.btn-navy:hover{background:#1A56A4;}
.btn-gold{background:#C8922A;color:#fff;}.btn-gold:hover{background:#b07d22;}
.btn-ghost{background:#F5F7FA;color:#2C3545;border:1px solid #E2E8F0;}
.btn-red{background:#FFF5F5;color:#C53030;border:1px solid #FED7D7;}
.alert-ok{background:#F0FFF4;border:1px solid #C6F6D5;color:#276749;padding:10px 14px;border-radius:8px;font-size:.8rem;margin-bottom:1rem;display:flex;align-items:center;gap:6px;}
.alert-err{background:#FFF5F5;border:1px solid #FED7D7;color:#C53030;padding:10px 14px;border-radius:8px;font-size:.8rem;margin-bottom:1rem;display:flex;align-items:center;gap:6px;}
.info-box{background:#E8F0FB;border-radius:8px;padding:.875rem 1rem;font-size:.78rem;color:#0D2E5C;margin-bottom:1rem;line-height:1.6;}
table{width:100%;border-collapse:collapse;}
th{background:#F8FAFF;padding:7px 12px;text-align:left;font-size:.7rem;font-weight:700;color:#8A96A8;text-transform:uppercase;letter-spacing:.05em;}
td{padding:8px 12px;border-top:1px solid #F0F4F8;font-size:.78rem;vertical-align:middle;}
tr:hover td{background:#FAFBFF;}
.badge-count{background:#0D2E5C;color:#fff;padding:2px 8px;border-radius:20px;font-size:.68rem;font-weight:700;margin-left:6px;}
</style>
</head>
<body>
<div class="page-title"><i class="ti ti-certificate" style="color:#C8922A;font-size:1.4rem;"></i> Kelola Sertifikat KKN 2026</div>

<?php if($ok_sert): ?><div class="alert-ok"><i class="ti ti-check"></i> <?= htmlspecialchars($ok_sert) ?></div><?php endif; ?>
<?php if($err_sert): ?><div class="alert-err"><i class="ti ti-alert-circle"></i> <?= htmlspecialchars($err_sert) ?></div><?php endif; ?>

<!-- Upload Excel -->
<div class="card">
  <div class="card-hd">
    <div class="card-hd-left"><i class="ti ti-table-import" style="color:#C8922A;font-size:1rem;"></i><h2>Upload Link Individual via Excel/CSV</h2></div>
  </div>
  <div class="card-body">
    <div class="info-box">
      <strong><i class="ti ti-info-circle"></i> Format file:</strong>
      Kolom A = <strong>Nama Mahasiswa</strong> (harus sama persis dengan nama di sistem),
      Kolom B = <strong>Link Google Drive</strong> file sertifikat per mahasiswa.
      Baris pertama = header (diabaikan). Setiap mahasiswa mendapat link file sendiri.
    </div>
    <form method="POST" enctype="multipart/form-data">
      <div class="fg">
        <label>Pilih File Excel atau CSV</label>
        <input type="file" name="excel_file" accept=".xlsx,.xls,.csv" required>
      </div>
      <div style="display:flex;gap:.625rem;flex-wrap:wrap;">
        <button type="submit" name="upload_excel" class="btn btn-gold"><i class="ti ti-upload"></i> Upload & Simpan</button>
        <a href="?download_template=1" class="btn btn-ghost"><i class="ti ti-download"></i> Download Template CSV</a>
      </div>
    </form>
  </div>
</div>

<!-- Daftar link individual -->
<div class="card">
  <div class="card-hd">
    <div class="card-hd-left">
      <i class="ti ti-list" style="color:#C8922A;font-size:1rem;"></i>
      <h2>Link Individual Tersimpan <span class="badge-count"><?= count($sert_ind) ?>/<?= $total_mhs ?></span></h2>
    </div>
    <?php if($sert_ind): ?>
    <form method="POST" style="margin:0;">
      <button type="submit" name="hapus_semua" class="btn btn-red" style="font-size:.72rem;padding:5px 10px;" onclick="return confirm('Hapus semua link individual?')">
        <i class="ti ti-trash"></i> Hapus Semua
      </button>
    </form>
    <?php endif; ?>
  </div>
  <?php if($sert_ind): ?>
  <table>
    <tr><th>#</th><th>Nama Mahasiswa</th><th>NIM</th><th>Prodi</th><th>Link</th><th>Aksi</th></tr>
    <?php foreach($sert_ind as $i=>$s):
      $mhs_id = str_replace('kkn_sertifikat_mhs_','', $s['setting_key']);
    ?>
    <tr>
      <td style="color:#8A96A8;"><?= $i+1 ?></td>
      <td style="font-weight:600;color:#0D2E5C;"><?= htmlspecialchars($s['nama']) ?></td>
      <td style="font-family:monospace;"><?= $s['nim'] ?></td>
      <td><?= htmlspecialchars($s['prodi']) ?></td>
      <td><a href="<?= htmlspecialchars($s['setting_value']) ?>" target="_blank" style="color:#1A56A4;display:flex;align-items:center;gap:3px;"><i class="ti ti-brand-google-drive"></i> Buka</a></td>
      <td><a href="?del=<?= $mhs_id ?>" class="btn btn-red" style="font-size:.7rem;padding:3px 8px;" onclick="return confirm('Hapus?')"><i class="ti ti-trash"></i></a></td>
    </tr>
    <?php endforeach; ?>
  </table>
  <?php else: ?>
  <div style="padding:2rem;text-align:center;color:#8A96A8;">Belum ada link individual. Upload CSV untuk memulai.</div>
  <?php endif; ?>
</div>

<!-- Link Global fallback -->
<div class="card">
  <div class="card-hd">
    <div class="card-hd-left"><i class="ti ti-world" style="color:#C8922A;font-size:1rem;"></i><h2>Link Folder Global (Fallback)</h2></div>
  </div>
  <div class="card-body">
    <p style="font-size:.78rem;color:#64748b;margin-bottom:1rem;">Ditampilkan hanya jika link individual belum diupload untuk mahasiswa tersebut.</p>
    <form method="POST">
      <div class="fg">
        <label>Link Folder Google Drive</label>
        <input type="url" name="link_global" value="<?= htmlspecialchars($link_global) ?>" placeholder="https://drive.google.com/drive/folders/...">
        <?php if($link_global): ?>
        <div style="font-size:.72rem;color:#276749;margin-top:4px;"><i class="ti ti-check"></i> Link tersimpan. <a href="<?= htmlspecialchars($link_global) ?>" target="_blank" style="color:#1A56A4;">Buka <i class="ti ti-external-link" style="font-size:.7rem;"></i></a></div>
        <?php endif; ?>
      </div>
      <button type="submit" name="save_global" class="btn btn-navy"><i class="ti ti-device-floppy"></i> Simpan Link Global</button>
    </form>
  </div>
</div>

<a href="<?= BASE_URL ?>/kkn-dashboard/" style="font-size:.8rem;color:#1A56A4;text-decoration:none;display:inline-flex;align-items:center;gap:4px;">
  <i class="ti ti-arrow-left"></i> Kembali ke Dashboard Admin
</a>
</body>
</html>
