<?php
require_once __DIR__ . '/../auth/protect_admin.php';
require_once __DIR__ . '/../config/db.php';

// Require PhpSpreadsheet
$autoload = __DIR__ . '/../vendor/autoload.php';
if (!file_exists($autoload)) {
    header("Location: /presensi_v1/pages/manage_siswa.php?err=no_phpspreadsheet");
    exit;
}
require $autoload;

use PhpOffice\PhpSpreadsheet\IOFactory;

if (empty($_FILES['xlsx']['name'])) {
    header("Location: /presensi_v1/pages/manage_siswa.php?err=no_file");
    exit;
}

$tmpPath = $_FILES['xlsx']['tmp_name'];

// try to load spreadsheet
try {
    $spreadsheet = IOFactory::load($tmpPath);
} catch (Exception $e) {
    header("Location: /presensi_v1/pages/manage_siswa.php?err=bad_file");
    exit;
}

$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true);

// Assume header in row 1 (but be flexible). We'll iterate from row 2 if row1 contains header words.
// Determine start row:
$startRow = 1;
$firstRow = array_map('strtolower', array_map('trim', array_values($rows[1] ?? [])));
$hasHeader = false;
$headerCandidates = ['nis','nama','kelas','jurusan','rombel'];
foreach ($headerCandidates as $h) {
    foreach ($firstRow as $cell) {
        if (strpos($cell, $h) !== false) { $hasHeader = true; break 2; }
    }
}
if ($hasHeader) $startRow = 2;

$added = $updated = $skipped = 0;

// Prepare statements
$selectByNis = $pdo->prepare("SELECT id FROM siswa WHERE nis = :nis LIMIT 1");
$insertStmt = $pdo->prepare("INSERT INTO siswa (nis,nama,kelas,jurusan,rombel) VALUES (:nis,:nama,:kelas,:jurusan,:rombel)");
$updateStmt = $pdo->prepare("UPDATE siswa SET nama=:nama, kelas=:kelas, jurusan=:jurusan, rombel=:rombel WHERE id=:id");

$maxRow = max(array_keys($rows));
for ($r = $startRow; $r <= $maxRow; $r++) {
    // columns A..E (1..5) but toArray returns keys 'A','B',...
    $nis = trim($rows[$r]['A'] ?? '');
    $nama = trim($rows[$r]['B'] ?? '');
    $kelas = strtoupper(trim($rows[$r]['C'] ?? ''));
    $jurusan = strtoupper(trim($rows[$r]['D'] ?? ''));
    $rombel = trim($rows[$r]['E'] ?? '');

    if ($nama === '' && $nis === '') { $skipped++; continue; }
    // Normalize kelas/jurusan values
    if (!in_array($kelas, ['X','XI','XII'])) $kelas = '';
    $jurusanList = ['TKJ','TITL','TP','TKR','TKP','DPIB'];
    if (!in_array($jurusan, $jurusanList)) $jurusan = '';

    // if nis exists -> update, else insert
    if ($nis !== '') {
        $selectByNis->execute([':nis'=>$nis]);
        $found = $selectByNis->fetchColumn();
        if ($found) {
            $updateStmt->execute([':nama'=>$nama, ':kelas'=>$kelas, ':jurusan'=>$jurusan, ':rombel'=>$rombel, ':id'=>$found]);
            $updated++;
        } else {
            $insertStmt->execute([':nis'=>$nis, ':nama'=>$nama, ':kelas'=>$kelas, ':jurusan'=>$jurusan, ':rombel'=>$rombel]);
            $added++;
        }
    } else {
        // no nis - insert as new with blank nis
        $insertStmt->execute([':nis'=>null, ':nama'=>$nama, ':kelas'=>$kelas, ':jurusan'=>$jurusan, ':rombel'=>$rombel]);
        $added++;
    }
}

// Redirect back with stats
header("Location: /presensi_v1/pages/manage_siswa.php?msg=import_ok&added={$added}&updated={$updated}&skipped={$skipped}");
exit;
