<?php
include "koneksi.php";
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

session_start();

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* =========================
   NORMALISASI TANGGAL
========================= */
function normalizeTanggal($tanggal){

    if(empty($tanggal)) return null;

    if(is_numeric($tanggal)){
        try{
            return Date::excelToDateTimeObject($tanggal)->format('Y-m-d');
        }catch(Exception $e){
            return null;
        }
    }

    $tanggal = trim($tanggal);

    if(preg_match('/^\d{4}-\d{2}-\d{2}$/', $tanggal)){
        return $tanggal;
    }

    if(preg_match('/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2,4})/', $tanggal, $m)){
        $day = str_pad($m[1],2,'0',STR_PAD_LEFT);
        $month = str_pad($m[2],2,'0',STR_PAD_LEFT);
        $year = strlen($m[3])==2 ? '20'.$m[3] : $m[3];
        return "$year-$month-$day";
    }

    return null;
}

/* =========================
   PREVIEW
========================= */
if(isset($_POST['preview'])){

    if($_FILES['file']['error'] != 0){
        die("Upload gagal!");
    }

    $file = $_FILES['file']['tmp_name'];
    $ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);

    if(!in_array($ext, ['xls','xlsx'])){
        die("File harus Excel (.xls / .xlsx)");
    }

    try{
        $spreadsheet = IOFactory::load($file);
    }catch(Exception $e){
        die("Gagal membaca file: ".$e->getMessage());
    }

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

    $previewData = [];

    foreach($rows as $key => $row){

        if($key < 2) continue; // skip header + baris aneh
        if(empty($row[1])) continue;

        // bersihkan newline di tanggal
        $tanggal = str_replace("\n","",$row[5] ?? '');

        $previewData[] = [
            'nomor' => trim($row[0] ?? ''),
            'nama' => trim($row[1] ?? ''),
            'jk' => strtoupper(trim($row[3] ?? '')),
            'tempat_lahir' => trim($row[4] ?? ''),
            'tanggal_lahir' => normalizeTanggal($tanggal),
            'agama' => trim($row[6] ?? ''),
            'jurusan' => trim($row[7] ?? ''),
            'sasaran' => trim($row[8] ?? ''),
            'tempat_sasaran' => trim($row[9] ?? '')
        ];
    }

    $_SESSION['preview_tracer'] = $previewData;
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Import Tracer Study</title>
    <link rel="stylesheet" href="adminlte/dist/css/adminlte.min.css">
</head>
<body class="p-4">

<div class="card">
<div class="card-header bg-primary text-white">
Import Tracer Study
</div>
<div class="card-body">

<form method="post" enctype="multipart/form-data">
    <input type="file" name="file" required class="form-control mb-2">
    <button type="submit" name="preview" class="btn btn-info">
        Preview Data
    </button>
</form>

<hr>

<?php if(!empty($_SESSION['preview_tracer'])): ?>
<form method="post">
<table class="table table-bordered table-sm">
<thead>
<tr>
<th>Nama</th>
<th>TTL</th>
<th>Jurusan</th>
<th>Sasaran</th>
</tr>
</thead>
<tbody>
<?php foreach($_SESSION['preview_tracer'] as $row): ?>
<tr>
<td><?= htmlspecialchars($row['nama']) ?></td>
<td><?= htmlspecialchars($row['tempat_lahir']) ?> - <?= $row['tanggal_lahir'] ?></td>
<td><?= htmlspecialchars($row['jurusan']) ?></td>
<td><?= htmlspecialchars($row['sasaran']) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

<button type="submit" name="simpan" class="btn btn-success">
Simpan ke Database
</button>
</form>
<?php endif; ?>

<?php
/* =========================
   INSERT
========================= */
if(isset($_POST['simpan']) && !empty($_SESSION['preview_tracer'])){

    $jumlah_insert = 0;

    foreach($_SESSION['preview_tracer'] as $data){

        if(empty($data['nama']) || empty($data['tanggal_lahir'])){
            continue;
        }

        // cek duplikat (nama + tanggal lahir)
        $cek = $koneksi->prepare("
            SELECT id FROM tracer_study 
            WHERE nama=? AND tanggal_lahir=?
        ");
        $cek->bind_param("ss", $data['nama'], $data['tanggal_lahir']);
        $cek->execute();
        $result = $cek->get_result();

        if($result->num_rows == 0){

            $insert = $koneksi->prepare("
                INSERT INTO tracer_study
                (nomor,nama,jk,tempat_lahir,tanggal_lahir,agama,jurusan,sasaran,tempat_sasaran)
                VALUES (?,?,?,?,?,?,?,?,?)
            ");

            $insert->bind_param(
                "sssssssss",
                $data['nomor'],
                $data['nama'],
                $data['jk'],
                $data['tempat_lahir'],
                $data['tanggal_lahir'],
                $data['agama'],
                $data['jurusan'],
                $data['sasaran'],
                $data['tempat_sasaran']
            );

            if(!$insert->execute()){
                echo "<pre>";
                print_r($data);
                echo "ERROR: ".$insert->error;
                exit;
            }

            $jumlah_insert++;
        }
    }

    unset($_SESSION['preview_tracer']);

    echo "<script>
    alert('Import berhasil! $jumlah_insert data masuk.');
    window.location='dashboard_adm_infokarirdanbeasiswa.php';
    </script>";
}
?>

</div>
</div>
</body>
</html>