<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Models\KelulusanModel;

class Kelulusan extends BaseController
{
    protected $kelulusanModel;

    public function __construct()
    {
        $this->kelulusanModel = new KelulusanModel();
    }

    // ===============================
    // LIST DATA KELULUSAN (ADMIN)
    // URL: /admin/kelulusan
    // ===============================
    public function kelulusan()
    {
        $q     = trim((string)($this->request->getGet('q') ?? ''));
        $tahun = trim((string)($this->request->getGet('tahun') ?? ''));

        $builder = $this->kelulusanModel->orderBy('nama', 'ASC');

        if ($q !== '') {
            $builder->groupStart()
                ->like('nisn', $q)
                ->orLike('nama', $q)
                ->groupEnd();
        }

        if ($tahun !== '' && $this->dbHasColumn('kelulusan', 'tahun')) {
            $builder->where('tahun', $tahun);
        }

        $rows = $builder->findAll();

        foreach ($rows as &$r) {
            $nisn = preg_replace('/\D+/', '', (string)($r['nisn'] ?? ''));
            $pdfRel = 'uploads/skl/SKL_' . $nisn . '.pdf';
            $pdfAbs = ROOTPATH . 'public/' . $pdfRel;

            $r['pdf_exists'] = ($nisn !== '' && is_file($pdfAbs));
            $r['pdf_url']    = $r['pdf_exists'] ? base_url($pdfRel) : null;
        }
        unset($r);

        return view('admin/kelulusan', [
            'title'   => 'Data Kelulusan',
            'rows'    => $rows,
            'q'       => $q,
            'tahun'   => $tahun,
            'success' => session()->getFlashdata('success'),
            'errors'  => session()->getFlashdata('errors') ?? [],
        ]);
    }

    // ===============================
    // FORM TAMBAH SISWA
    // URL: /admin/kelulusan/create
    // ===============================
    public function create()
    {
        return view('admin/kelulusan/form', [
            'title'  => 'Tambah Data Kelulusan',
            'item'   => null,
            'errors' => session()->getFlashdata('errors') ?? [],
        ]);
    }

    // ===============================
    // SIMPAN DATA SISWA
    // URL: /admin/kelulusan/store
    // ===============================
    public function store()
    {
        $data = $this->request->getPost();

        if (!$this->kelulusanModel->insert($data)) {
            return redirect()->back()
                ->withInput()
                ->with('errors', $this->kelulusanModel->errors());
        }

        return redirect()->to('/admin/kelulusan')
            ->with('success', 'Data kelulusan berhasil ditambahkan');
    }

    // ===============================
    // FORM IMPORT CSV
    // URL: /admin/kelulusan/import
    // ===============================
    public function importForm()
    {
        return view('admin/kelulusan/import', [
            'title'   => 'Import Data Kelulusan (CSV)',
            'errors'  => session()->getFlashdata('errors') ?? [],
            'success' => session()->getFlashdata('success') ?? null,
        ]);
    }

    // ===============================
    // PROSES IMPORT CSV (POST)
    // URL: /admin/kelulusan/import-csv
    // input file name: csv
    // ===============================
    public function importCsv()
    {
        $file = $this->request->getFile('csv');
        if (!$file || !$file->isValid()) {
            return redirect()->back()->with('errors', ['File CSV wajib diupload.']);
        }

        $path = $file->getTempName();
        $handle = fopen($path, 'r');
        if (!$handle) {
            return redirect()->back()->with('errors', ['Gagal membaca file CSV.']);
        }

        // ambil baris pertama buat deteksi delimiter
        $firstLine = fgets($handle);
        if ($firstLine === false) {
            fclose($handle);
            return redirect()->back()->with('errors', ['CSV kosong / header tidak terbaca.']);
        }
        rewind($handle);

        // deteksi delimiter
        $delims = [',', ';', "\t", '|'];
        $bestDelim = ',';
        $bestCount = 0;
        foreach ($delims as $d) {
            $c = substr_count($firstLine, $d);
            if ($c > $bestCount) {
                $bestCount = $c;
                $bestDelim = $d;
            }
        }

        $header = fgetcsv($handle, 0, $bestDelim);
        if (!$header) {
            fclose($handle);
            return redirect()->back()->with('errors', ['Header CSV tidak terbaca.']);
        }

        // bersihin header: BOM, spasi, lowercase, spasi ganda
        $header = array_map(function ($h) {
            $h = preg_replace('/^\xEF\xBB\xBF/', '', (string)$h);
            $h = strtolower(trim($h));
            $h = preg_replace('/\s+/', ' ', $h);
            $h = str_replace(['-', '.'], ['_', ''], $h);
            return $h;
        }, $header);

        // mapping alias header fleksibel
        $aliasMap = [
            'nisn' => 'nisn',
            'nis' => 'nisn',
            'no_induk' => 'nisn',
            'no induk' => 'nisn',

            'nama' => 'nama',
            'nama_siswa' => 'nama',
            'nama siswa' => 'nama',

            'kelas' => 'kelas',
            'rombel' => 'kelas',

            'tahun' => 'tahun',
            'tahun_ajaran' => 'tahun',
            'tahun ajaran' => 'tahun',

            'tgl_lahir' => 'tgl_lahir',
            'tgl lahir' => 'tgl_lahir',
            'tanggal_lahir' => 'tgl_lahir',
            'tanggal lahir' => 'tgl_lahir',
            'ttl' => 'tgl_lahir',

            'status' => 'status',

            'keterangan' => 'keterangan',
            'catatan' => 'keterangan',
        ];

        $normalized = [];
        foreach ($header as $h) {
            $normalized[] = $aliasMap[$h] ?? $h;
        }
        $header = $normalized;

        // minimal wajib
        $minimal = ['nisn', 'nama', 'tgl_lahir'];
        foreach ($minimal as $kolom) {
            if (!in_array($kolom, $header, true)) {
                fclose($handle);
                return redirect()->back()->with('errors', [
                    "Kolom minimal wajib ada: nisn, nama, tgl_lahir. Header kamu: " . implode(',', $header)
                ]);
            }
        }

        $map = array_flip($header);

        // helper konversi tanggal (YYYY-MM-DD atau null)
        $toYmd = function ($val) {
            $v = trim((string)$val);
            if ($v === '' || strtolower($v) === 'null') return null;

            // sudah YYYY-MM-DD
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $v)) {
                return $v;
            }

            // dd/mm/yyyy atau dd-mm-yyyy
            if (preg_match('/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/', $v, $m)) {
                $d  = str_pad($m[1], 2, '0', STR_PAD_LEFT);
                $mo = str_pad($m[2], 2, '0', STR_PAD_LEFT);
                $y  = $m[3];
                return "{$y}-{$mo}-{$d}";
            }

            // excel serial number
            if (is_numeric($v)) {
                $n = (int)$v;
                if ($n > 20000 && $n < 80000) {
                    $unix = ($n - 25569) * 86400;
                    return gmdate('Y-m-d', $unix);
                }
            }

            // fallback strtotime
            $t = strtotime($v);
            if ($t !== false) return date('Y-m-d', $t);

            return null;
        };

        $count = 0;
        $skipEmpty = 0;
        $skipInvalid = 0;

        while (($row = fgetcsv($handle, 0, $bestDelim)) !== false) {
            if (count(array_filter($row, fn($v) => trim((string)$v) !== '')) === 0) {
                $skipEmpty++;
                continue;
            }

            $nisn = trim((string)($row[$map['nisn']] ?? ''));
            $nama = trim((string)($row[$map['nama']] ?? ''));
            $tgl  = $toYmd($row[$map['tgl_lahir']] ?? '');

            if ($nisn === '' || $nama === '' || $tgl === null) {
                $skipInvalid++;
                continue;
            }

            $data = [
                'tahun'      => isset($map['tahun']) ? trim((string)($row[$map['tahun']] ?? '')) : '',
                'nisn'       => $nisn,
                'nama'       => $nama,
                'kelas'      => isset($map['kelas']) ? trim((string)($row[$map['kelas']] ?? '')) : '',
                'status'     => isset($map['status']) ? trim((string)($row[$map['status']] ?? '')) : '',
                'tgl_lahir'  => $tgl,
                'keterangan' => isset($map['keterangan']) ? trim((string)($row[$map['keterangan']] ?? '')) : '',
            ];

            // upsert by nisn + tgl_lahir
            $exist = $this->kelulusanModel
                ->where('nisn', $data['nisn'])
                ->first();

            if ($exist) {
                $this->kelulusanModel->update($exist['id'], $data);
            } else {
                $this->kelulusanModel->insert($data);
            }

            $count++;
        }

        fclose($handle);

        $delimLabel = ($bestDelim === "\t") ? "TAB" : $bestDelim;

        return redirect()->to('/admin/kelulusan')
            ->with('success', "Import selesai (delimiter: {$delimLabel}). Masuk/Update: {$count} data. Skip kosong: {$skipEmpty}. Skip invalid: {$skipInvalid}.");
    }

    // ===============================
    // FORM UPLOAD ZIP SKL
    // URL: /admin/kelulusan/upload-skl
    // ===============================
    public function uploadSklForm()
    {
        return view('admin/kelulusan/upload_skl', [
            'title'   => 'Upload PDF SKL (ZIP)',
            'errors'  => session()->getFlashdata('errors') ?? [],
            'success' => session()->getFlashdata('success') ?? null,
        ]);
    }

    // ===============================
    // PROSES UPLOAD ZIP SKL (POST)
    // URL: /admin/kelulusan/upload-skl
    // ===============================
    public function uploadSklZip()
    {
        $file = $this->request->getFile('zip');
        if (!$file || !$file->isValid()) {
            return redirect()->back()->with('errors', ['File ZIP wajib diupload.']);
        }

        if (strtolower($file->getExtension()) !== 'zip') {
            return redirect()->back()->with('errors', ['File harus ZIP.']);
        }

        $tmpPath = $file->getTempName();
        $zip = new \ZipArchive();

        if ($zip->open($tmpPath) !== true) {
            return redirect()->back()->with('errors', ['ZIP tidak bisa dibuka.']);
        }

        $destDir = ROOTPATH . 'public/uploads/skl/';
        if (!is_dir($destDir)) {
            @mkdir($destDir, 0775, true);
        }

        $ok = 0; $skip = 0;

        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = $zip->getNameIndex($i);
            if (substr($name, -1) === '/') continue;

            $base = basename($name);

            if (!preg_match('/^SKL_(\d+)\.pdf$/i', $base, $m)) {
                $skip++;
                continue;
            }

            $nisn = $m[1];

            $content = $zip->getFromIndex($i);
            if ($content === false) { $skip++; continue; }

            file_put_contents($destDir . "SKL_{$nisn}.pdf", $content);
            $ok++;
        }

        $zip->close();

        return redirect()->to('/admin/kelulusan')
            ->with('success', "Upload SKL selesai. Berhasil: {$ok} file, Skip: {$skip} file.");
    }

    // ===============================
    // FORM GANTI/UPLOAD PDF 1 SISWA
    // URL: /admin/kelulusan/upload-skl/{id}
    // ===============================
    public function uploadSklSingleForm(int $id)
    {
        $row = $this->kelulusanModel->find($id);
        if (!$row) {
            return redirect()->to('/admin/kelulusan')->with('errors', ['Data tidak ditemukan.']);
        }

        return view('admin/kelulusan/upload_skl_single', [
            'title'   => 'Ganti PDF SKL',
            'row'     => $row,
            'errors'  => session()->getFlashdata('errors') ?? [],
            'success' => session()->getFlashdata('success') ?? null,
        ]);
    }

    // ===============================
    // PROSES GANTI/UPLOAD PDF 1 SISWA (POST)
    // URL: /admin/kelulusan/upload-skl/{id}
    // ===============================
    public function uploadSklSingle(int $id)
    {
        $row = $this->kelulusanModel->find($id);
        if (!$row) {
            return redirect()->to('/admin/kelulusan')->with('errors', ['Data tidak ditemukan.']);
        }

        $nisn = preg_replace('/\D+/', '', (string)($row['nisn'] ?? ''));
        if ($nisn === '') {
            return redirect()->back()->with('errors', ['NISN kosong / tidak valid.']);
        }

        $file = $this->request->getFile('pdf');
        if (!$file || !$file->isValid()) {
            return redirect()->back()->with('errors', ['File PDF wajib diupload.']);
        }

        if (strtolower($file->getExtension()) !== 'pdf') {
            return redirect()->back()->with('errors', ['File harus PDF.']);
        }

        $destDir = ROOTPATH . 'public/uploads/skl/';
        if (!is_dir($destDir)) {
            @mkdir($destDir, 0775, true);
        }

        $targetName = "SKL_{$nisn}.pdf";
        $targetPath = $destDir . $targetName;

        if (is_file($targetPath)) {
            @unlink($targetPath);
        }

        $file->move($destDir, $targetName);

        return redirect()->to('/admin/kelulusan')
            ->with('success', "PDF SKL untuk {$row['nama']} berhasil diupload/diganti.");
    }

    // ===============================
    // HAPUS PDF SKL 1 SISWA
    // URL: /admin/kelulusan/delete-pdf/{id}
    // ===============================
    public function deletePdf($id)
    {
        $row = $this->kelulusanModel->find($id);
        if (!$row) {
            return redirect()->to('/admin/kelulusan')->with('errors', ['Data tidak ditemukan.']);
        }

        $nisn = preg_replace('/\D+/', '', (string)($row['nisn'] ?? ''));
        if ($nisn === '') {
            return redirect()->to('/admin/kelulusan')->with('errors', ['NISN kosong, tidak bisa hapus PDF.']);
        }

        $pdfPath = ROOTPATH . 'public/uploads/skl/SKL_' . $nisn . '.pdf';
        if (!is_file($pdfPath)) {
            return redirect()->to('/admin/kelulusan')->with('errors', ['PDF SKL tidak ditemukan untuk NISN: ' . $nisn]);
        }

        @unlink($pdfPath);

        return redirect()->to('/admin/kelulusan')->with('success', 'PDF SKL berhasil dihapus untuk NISN: ' . $nisn);
    }

    // ===== helper: cek kolom ada atau tidak =====
    private function dbHasColumn(string $table, string $column): bool
    {
        $db = \Config\Database::connect();
        $fields = $db->getFieldNames($table);
        return in_array($column, $fields, true);
    }
    public function resetByTahun()
{
    $tahun = trim((string)$this->request->getPost('tahun'));

    if ($tahun === '') {
        return redirect()->to('/admin/kelulusan')
            ->with('errors', ['Tahun wajib diisi untuk reset data.']);
    }

    // Optional: validasi format tahun "2025/2026"
    if (!preg_match('/^\d{4}\/\d{4}$/', $tahun)) {
        return redirect()->to('/admin/kelulusan')
            ->with('errors', ['Format tahun harus seperti 2025/2026.']);
    }

    // Hapus data dari tabel kelulusan untuk tahun tsb
    $deleted = $this->kelulusanModel->where('tahun', $tahun)->delete();

    // Hapus juga file PDF SKL yang sesuai NISN dari data tahun itu (opsional)
    // Kalau mau hapus pdf juga, pakai blok ini:
    /*
    $rows = $this->kelulusanModel->where('tahun', $tahun)->findAll();
    foreach ($rows as $r) {
        $nisn = preg_replace('/\D+/', '', (string)($r['nisn'] ?? ''));
        if ($nisn === '') continue;
        $pdf = ROOTPATH . 'public/uploads/skl/SKL_' . $nisn . '.pdf';
        if (is_file($pdf)) @unlink($pdf);
    }
    $this->kelulusanModel->where('tahun', $tahun)->delete();
    */

    return redirect()->to('/admin/kelulusan')
        ->with('success', "Berhasil reset data kelulusan untuk tahun {$tahun}.");
}
}
