<?php
ob_start();
include('koneksi.php');
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

function redirectImport($queryString) {
    while (ob_get_level() > 0) {
        ob_end_clean();
    }
    header("Location: ../dashboard.php?page=user" . $queryString);
    exit;
}

function normalizeHeader($value) {
    return strtoupper(trim((string) $value));
}

function getValueByIndexes($row, $indexes) {
    foreach ($indexes as $idx) {
        if (isset($row[$idx])) {
            $val = trim((string) $row[$idx]);
            if ($val !== '') {
                return $val;
            }
        }
    }
    return '';
}

if (!isset($_FILES['berkas_excel']) || empty($_FILES['berkas_excel']['name'])) {
    redirectImport("&pesan=ImportGagal&detail=file_kosong");
}

if ($_FILES['berkas_excel']['error'] !== UPLOAD_ERR_OK) {
    redirectImport("&pesan=ImportGagal&detail=upload_error");
}

$fileName = $_FILES['berkas_excel']['name'];
$tmpPath = $_FILES['berkas_excel']['tmp_name'];
$ext = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
$allowedExt = array('xlsx', 'xls', 'csv');

if (!in_array($ext, $allowedExt, true)) {
    redirectImport("&pesan=ImportGagal&detail=format_tidak_didukung");
}

$previousErrorHandler = set_error_handler(function ($severity, $message, $file) {
    $normalized = str_replace('\\', '/', (string) $file);
    if (
        $severity === E_WARNING
        && strpos($normalized, 'PhpSpreadsheet/Cell/DefaultValueBinder.php') !== false
    ) {
        return true;
    }
    return false;
});

try {
    $reader = IOFactory::createReaderForFile($tmpPath);
    $reader->setReadDataOnly(true);
    $spreadsheet = $reader->load($tmpPath);
} catch (Exception $e) {
    if ($previousErrorHandler !== null) {
        restore_error_handler();
    }
    redirectImport("&pesan=ImportGagal&detail=file_tidak_valid");
}

$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
if ($previousErrorHandler !== null) {
    restore_error_handler();
}

if (count($sheetData) <= 1) {
    redirectImport("&pesan=ImportGagal&detail=data_kosong");
}

$kelasMap = array();
$kelasNameToIdMap = array();
$kelasQuery = mysqli_query($koneksi, "SELECT id_kelas, nama_kelas FROM t_kelas");
while ($kelasRow = mysqli_fetch_assoc($kelasQuery)) {
    $idKelas = strtoupper(trim($kelasRow['id_kelas']));
    $namaKelas = strtoupper(trim($kelasRow['nama_kelas']));
    $kelasMap[$idKelas] = true;
    $kelasNameToIdMap[$namaKelas] = $idKelas;
}

$inserted = 0;
$updated = 0;
$skipped = 0;
$processed = 0;
$duplicateInFile = 0;
$seenNis = array();

$stmt = mysqli_prepare(
    $koneksi,
    "INSERT INTO t_user (id_user, fullname, id_kelas, jk, pemilih)
     VALUES (?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
       fullname = VALUES(fullname),
       id_kelas = VALUES(id_kelas),
       jk = VALUES(jk),
       pemilih = VALUES(pemilih)"
);

if (!$stmt) {
    redirectImport("&pesan=ImportGagal&detail=query_error");
}

mysqli_begin_transaction($koneksi);

$header = isset($sheetData[0]) ? $sheetData[0] : array();
$headerMap = array();
for ($h = 0; $h < count($header); $h++) {
    $headerMap[normalizeHeader($header[$h])] = $h;
}

$hasNamedHeader = isset($headerMap['NIS']) || isset($headerMap['NAMA']) || isset($headerMap['KELAS']);
$startRow = $hasNamedHeader ? 1 : 0;

for ($i = $startRow; $i < count($sheetData); $i++) {
    $row = $sheetData[$i];

    if ($hasNamedHeader) {
        $nis = isset($headerMap['NIS']) ? trim((string) $row[$headerMap['NIS']]) : '';
        $nama = isset($headerMap['NAMA']) ? trim((string) $row[$headerMap['NAMA']]) : '';
        $kelas = isset($headerMap['KELAS']) ? strtoupper(trim((string) $row[$headerMap['KELAS']])) : '';
        $jk = isset($headerMap['JK']) ? strtoupper(trim((string) $row[$headerMap['JK']])) : '';
        $pemilih = isset($headerMap['PEMILIH']) ? strtoupper(trim((string) $row[$headerMap['PEMILIH']])) : 'Y';
    } else {
        // Support template dengan/ tanpa kolom NO di paling depan.
        $nis = getValueByIndexes($row, array(1, 0));
        $nama = getValueByIndexes($row, array(2, 1));
        $kelas = strtoupper(getValueByIndexes($row, array(3, 2)));
        $jk = strtoupper(getValueByIndexes($row, array(4, 3)));
        $pemilih = strtoupper(getValueByIndexes($row, array(5, 4)));
        if ($pemilih === '') {
            $pemilih = 'Y';
        }
    }

    if ($nis === '' && $nama === '' && $kelas === '' && $jk === '') {
        continue;
    }

    if ($nis === '' || $nama === '' || $kelas === '' || $jk === '') {
        $skipped++;
        continue;
    }

    if (isset($seenNis[$nis])) {
        $duplicateInFile++;
        $skipped++;
        continue;
    }
    $seenNis[$nis] = true;

    if (!isset($kelasMap[$kelas])) {
        if (isset($kelasNameToIdMap[$kelas])) {
            $kelas = $kelasNameToIdMap[$kelas];
        } else {
            $skipped++;
            continue;
        }
    }

    if ($jk !== 'L' && $jk !== 'P') {
        $skipped++;
        continue;
    }

    if ($pemilih !== 'Y' && $pemilih !== 'N') {
        $pemilih = 'Y';
    }

    mysqli_stmt_bind_param($stmt, 'sssss', $nis, $nama, $kelas, $jk, $pemilih);
    $exec = mysqli_stmt_execute($stmt);

    if (!$exec) {
        $skipped++;
        continue;
    }

    $processed++;
    $affected = mysqli_stmt_affected_rows($stmt);
    if ($affected === 1) {
        $inserted++;
    } else {
        $updated++;
    }
}

mysqli_stmt_close($stmt);
mysqli_commit($koneksi);

redirectImport(
    "&pesan=ImportBerhasil"
    . "&inserted=" . $inserted
    . "&updated=" . $updated
    . "&processed=" . $processed
    . "&dupfile=" . $duplicateInFile
    . "&skipped=" . $skipped
);
?>