<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Importpegawai extends Auth_Controller
{
    // Kolom wajib template
    const REQUIRED_HEADERS = ['nip','nama_lengkap','jenis_kelamin','tanggal_masuk','kode_divisi','kode_jabatan','gaji_pokok'];

    public function __construct()
    {
        parent::__construct();
        $this->load->model(['Pegawai_model', 'Divisi_model', 'Jabatan_model']);
    }

    // Halaman utama + riwayat import
    public function index()
    {
        $data = [
            'page_title'    => 'Import Pegawai',
            'page_subtitle' => 'Upload data pegawai dari file Excel',
            'riwayat'       => $this->db->order_by('created_at','DESC')->limit(10)->get('tb_import_pegawai_log')->result(),
        ];
        $this->render('master/import_pegawai/index', $data);
    }

    // Download template Excel
    public function template()
    {
        $divisi_rows = $this->Divisi_model->get_all(TRUE);
        $jabatan_rows = $this->Jabatan_model->get_all(TRUE);
        $ptkp_options = ['TK0','TK1','TK2','TK3','K0','K1','K2','K3','K_I0','K_I1','K_I2','K_I3'];

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('pegawai');

        // Header baris 1
        $headers = [
            'A1' => 'nip',           'B1' => 'nama_lengkap',  'C1' => 'jenis_kelamin',
            'D1' => 'tempat_lahir',  'E1' => 'tanggal_lahir', 'F1' => 'alamat',
            'G1' => 'tanggal_masuk', 'H1' => 'kode_divisi',   'I1' => 'kode_jabatan',
            'J1' => 'gaji_pokok',    'K1' => 'ptkp_status',   'L1' => 'no_telepon',
            'M1' => 'email',         'N1' => 'nik',           'O1' => 'npwp',
            'P1' => 'no_bpjs_kesehatan', 'Q1' => 'no_bpjs_ketenagakerjaan',
            'R1' => 'nama_bank', 'S1' => 'no_rekening',
        ];
        foreach ($headers as $cell => $val) {
            $sheet->setCellValue($cell, $val);
        }

        // Style header
        $styleH = ['font' => ['bold' => TRUE, 'color' => ['rgb' => 'FFFFFF']],
                   'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => '800000']]];
        $sheet->getStyle('A1:S1')->applyFromArray($styleH);

        // Contoh baris 2
        $sample = [
            'A2' => 'EMP001', 'B2' => 'Budi Santoso', 'C2' => 'L',
            'D2' => 'Jakarta', 'E2' => '1990-01-15', 'F2' => 'Jl. Mawar No. 1',
            'G2' => '2023-01-01',
            'H2' => (!empty($divisi_rows) ? $divisi_rows[0]->kode_divisi : ''),
            'I2' => (!empty($jabatan_rows) ? $jabatan_rows[0]->kode_jabatan : ''),
            'J2' => '5000000', 'K2' => 'TK0', 'L2' => '0812345678', 'M2' => 'budi@email.com',
            'N2' => '1234567890123456', 'O2' => '', 'P2' => '', 'Q2' => '',
            'R2' => 'BCA', 'S2' => '1234567890',
        ];
        foreach ($sample as $cell => $val) $sheet->setCellValue($cell, $val);

        // Sheet referensi untuk dropdown (disembunyikan)
        $ref = $spreadsheet->createSheet();
        $ref->setTitle('ref_data');
        $ref->setCellValue('A1', 'kode_divisi');
        $ref->setCellValue('B1', 'kode_jabatan');
        $ref->setCellValue('C1', 'ptkp_status');

        $div_count = 0;
        foreach ($divisi_rows as $idx => $row) {
            $ref->setCellValue('A' . ($idx + 2), strtoupper((string) $row->kode_divisi));
            $div_count++;
        }

        $jab_count = 0;
        foreach ($jabatan_rows as $idx => $row) {
            $ref->setCellValue('B' . ($idx + 2), strtoupper((string) $row->kode_jabatan));
            $jab_count++;
        }

        $ptkp_count = 0;
        foreach ($ptkp_options as $idx => $val) {
            $ref->setCellValue('C' . ($idx + 2), $val);
            $ptkp_count++;
        }

        $last_div_row = max(2, $div_count + 1);
        $last_jab_row = max(2, $jab_count + 1);
        $last_ptkp_row = max(2, $ptkp_count + 1);

        // Terapkan dropdown ke baris input template
        for ($row = 2; $row <= 1000; $row++) {
            $dvDiv = $sheet->getCell('H' . $row)->getDataValidation();
            $dvDiv->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
            $dvDiv->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
            $dvDiv->setAllowBlank(TRUE);
            $dvDiv->setShowDropDown(TRUE);
            $dvDiv->setShowInputMessage(TRUE);
            $dvDiv->setPromptTitle('Pilih Kode Divisi');
            $dvDiv->setPrompt('Pilih kode divisi dari daftar.');
            $dvDiv->setShowErrorMessage(TRUE);
            $dvDiv->setErrorTitle('Kode Divisi tidak valid');
            $dvDiv->setError('Nilai harus dipilih dari daftar kode divisi.');
            $dvDiv->setFormula1('=ref_data!$A$2:$A$' . $last_div_row);

            $dvJab = $sheet->getCell('I' . $row)->getDataValidation();
            $dvJab->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
            $dvJab->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
            $dvJab->setAllowBlank(TRUE);
            $dvJab->setShowDropDown(TRUE);
            $dvJab->setShowInputMessage(TRUE);
            $dvJab->setPromptTitle('Pilih Kode Jabatan');
            $dvJab->setPrompt('Pilih kode jabatan dari daftar.');
            $dvJab->setShowErrorMessage(TRUE);
            $dvJab->setErrorTitle('Kode Jabatan tidak valid');
            $dvJab->setError('Nilai harus dipilih dari daftar kode jabatan.');
            $dvJab->setFormula1('=ref_data!$B$2:$B$' . $last_jab_row);

            $dvPtkp = $sheet->getCell('K' . $row)->getDataValidation();
            $dvPtkp->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
            $dvPtkp->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
            $dvPtkp->setAllowBlank(TRUE);
            $dvPtkp->setShowDropDown(TRUE);
            $dvPtkp->setShowInputMessage(TRUE);
            $dvPtkp->setPromptTitle('Pilih Status PTKP');
            $dvPtkp->setPrompt('Pilih status PTKP dari daftar.');
            $dvPtkp->setShowErrorMessage(TRUE);
            $dvPtkp->setErrorTitle('Status PTKP tidak valid');
            $dvPtkp->setError('Nilai harus dipilih dari daftar status PTKP.');
            $dvPtkp->setFormula1('=ref_data!$C$2:$C$' . $last_ptkp_row);
        }

        // Sembunyikan sheet referensi
        $ref->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);

        // Auto width
        foreach (range('A','S') as $col) $sheet->getColumnDimension($col)->setAutoSize(TRUE);

        // Format text untuk kolom yang rawan hilang nol di depan saat diisi di Excel
        foreach (['A', 'L', 'N', 'O', 'P', 'Q', 'S'] as $textCol) {
            $sheet->getStyle($textCol . '2:' . $textCol . '1000')
                ->getNumberFormat()
                ->setFormatCode('@');
        }

        // Sheet petunjuk
        $info = $spreadsheet->createSheet();
        $info->setTitle('Petunjuk');
        $info->setCellValue('A1', 'PETUNJUK PENGISIAN');
        $info->setCellValue('A3', 'Kolom Wajib: nip, nama_lengkap, jenis_kelamin (L/P), tanggal_masuk, kode_divisi, kode_jabatan, gaji_pokok');
        $info->setCellValue('A4', 'Format tanggal: YYYY-MM-DD (contoh: 2023-01-01)');
        $info->setCellValue('A5', 'Kolom kode_divisi, kode_jabatan, dan ptkp_status sudah dropdown sesuai master data aktif.');
        $info->setCellValue('A6', 'ptkp_status: TK0, TK1, TK2, TK3, K0, K1, K2, K3, K_I0, K_I1, K_I2, K_I3 (atau K/I/0 dst - otomatis dikonversi)');
        $info->setCellValue('A7', 'Kolom opsional (alamat, no_rekening, nama_bank, bpjs, dll) akan ikut disimpan jika diisi.');
        $info->setCellValue('A8', 'kode_divisi dan kode_jabatan harus sesuai data di sistem');
        $info->getStyle('A1')->getFont()->setBold(TRUE)->setSize(14);
        $info->getColumnDimension('A')->setWidth(100);

        // Jadikan sheet pegawai sebagai sheet aktif saat file dibuka
        $spreadsheet->setActiveSheetIndex(0);

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="template-import-pegawai.xlsx"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    // Preview dan simpan hasil upload
    public function upload()
    {
        if ($this->input->server('REQUEST_METHOD') !== 'POST') {
            redirect('master/import_pegawai');
            return;
        }

        $overwrite_existing = $this->input->post('overwrite_existing') ? TRUE : FALSE;

        $file = $_FILES['file_excel'] ?? NULL;
        if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
            $this->session->set_flashdata('error', 'File gagal diupload. Pastikan ukuran tidak melebihi batas.');
            redirect('master/import_pegawai');
            return;
        }

        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, ['xlsx','xls'])) {
            $this->session->set_flashdata('error', 'Format file harus .xlsx atau .xls');
            redirect('master/import_pegawai');
            return;
        }

        try {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file['tmp_name']);
            $reader->setReadDataOnly(TRUE);
            $spreadsheet = $reader->load($file['tmp_name']);
            $sheet = $spreadsheet->getActiveSheet();
            $rows  = $sheet->toArray(NULL, TRUE, TRUE, TRUE); // assoc by col letter
        } catch (\Exception $e) {
            $this->session->set_flashdata('error', 'File tidak dapat dibaca: ' . html_escape($e->getMessage()));
            redirect('master/import_pegawai');
            return;
        }

        // Validasi header
        $header_row = array_values($rows[1] ?? []);
        $missing = array_diff(self::REQUIRED_HEADERS, $header_row);
        if (!empty($missing)) {
            $this->session->set_flashdata('error', 'Header kolom tidak sesuai template. Kolom hilang: ' . implode(', ', $missing));
            redirect('master/import_pegawai');
            return;
        }

        // Map header ke indeks kolom
        $header_map = array_flip(array_map('trim', array_values($rows[1])));

        // Load data referensi
        $divisi_map  = [];
        foreach ($this->Divisi_model->get_all() as $d) $divisi_map[strtoupper($d->kode_divisi)] = $d->id_divisi;
        $jabatan_map = [];
        foreach ($this->Jabatan_model->get_all() as $j) $jabatan_map[strtoupper($j->kode_jabatan)] = $j->id_jabatan;

        $ptkp_valid = ['TK0','TK1','TK2','TK3','K0','K1','K2','K3','K_I0','K_I1','K_I2','K_I3'];
        // Map format K/I/x (user-friendly) ke K_Ix (DB enum)
        $ptkp_map = ['K/I/0'=>'K_I0','K/I/1'=>'K_I1','K/I/2'=>'K_I2','K/I/3'=>'K_I3'];

        $preview = [];
        $col_keys = array_keys($rows[1]); // A,B,C,...

        for ($i = 2; $i <= count($rows); $i++) {
            if (!isset($rows[$i]) || !is_array($rows[$i])) {
                continue;
            }

            $r = array_values($rows[$i]);
            $is_empty_row = TRUE;
            foreach ($r as $cell_val) {
                if (trim((string) $cell_val) !== '') {
                    $is_empty_row = FALSE;
                    break;
                }
            }
            if ($is_empty_row) {
                continue;
            }

            // Map nilai berdasarkan posisi header
            $get = function($col_name) use ($header_map, $r) {
                $idx = $header_map[$col_name] ?? -1;
                return $idx >= 0 ? trim((string) ($r[$idx] ?? '')) : '';
            };

            $errors = [];
            $nip    = $get('nip');
            $nama   = $get('nama_lengkap');
            $jk     = strtoupper($get('jenis_kelamin'));
            $tgl_m  = $this->_normalize_excel_date($get('tanggal_masuk'));
            $kdiv   = strtoupper($get('kode_divisi'));
            $kjab   = strtoupper($get('kode_jabatan'));
            $gapok  = $get('gaji_pokok');
            $raw_ptkp = strtoupper($get('ptkp_status')) ?: 'TK0';
            $ptkp     = $ptkp_map[$raw_ptkp] ?? $raw_ptkp;
            $tempat_lahir = $get('tempat_lahir');
            $tanggal_lahir = $this->_normalize_excel_date($get('tanggal_lahir'), TRUE);
            $alamat = $get('alamat');
            $no_telepon = $get('no_telepon');
            $email = $get('email');
            $nik = $get('nik');
            $npwp = $get('npwp');
            $no_bpjs_kesehatan = $get('no_bpjs_kesehatan');
            $no_bpjs_ketenagakerjaan = $get('no_bpjs_ketenagakerjaan');
            $nama_bank = $get('nama_bank');
            $no_rekening = $get('no_rekening');

            if (empty($nip))  $errors[] = 'NIP kosong';
            if (empty($nama)) $errors[] = 'Nama kosong';
            if (!in_array($jk, ['L','P'])) $errors[] = 'Jenis kelamin harus L/P';
            if (empty($tgl_m) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $tgl_m)) $errors[] = 'Format tanggal masuk salah (YYYY-MM-DD)';
            if (!isset($divisi_map[$kdiv]))  $errors[] = "Kode divisi '$kdiv' tidak ditemukan";
            if (!isset($jabatan_map[$kjab])) $errors[] = "Kode jabatan '$kjab' tidak ditemukan";
            if (!is_numeric($gapok) || $gapok < 0) $errors[] = 'Gaji pokok tidak valid';
            if (!in_array($ptkp, $ptkp_valid)) $errors[] = "PTKP '$ptkp' tidak valid";

            // Cek NIP duplikat di DB (boleh jika mode timpa aktif)
            $existing_pegawai = $nip ? $this->Pegawai_model->get_by_nip($nip) : null;
            if (empty($errors) && $nip && $existing_pegawai && !$overwrite_existing) {
                $errors[] = "NIP '$nip' sudah terdaftar";
            }

            $preview[] = [
                'baris'       => $i,
                'nip'         => $nip,
                'nama'        => $nama,
                'jk'          => $jk,
                'tgl_masuk'   => $tgl_m,
                'kode_divisi' => $kdiv,
                'kode_jabatan'=> $kjab,
                'gaji_pokok'  => $gapok,
                'ptkp'        => $ptkp,
                'tempat_lahir' => $tempat_lahir,
                'tanggal_lahir' => $tanggal_lahir,
                'alamat' => $alamat,
                'no_telepon' => $no_telepon,
                'email' => $email,
                'nik' => $nik,
                'npwp' => $npwp,
                'no_bpjs_kesehatan' => $no_bpjs_kesehatan,
                'no_bpjs_ketenagakerjaan' => $no_bpjs_ketenagakerjaan,
                'nama_bank' => $nama_bank,
                'no_rekening' => $no_rekening,
                'aksi'        => $existing_pegawai ? 'UPDATE' : 'INSERT',
                'raw'         => $r,
                'errors'      => $errors,
                'valid'        => empty($errors),
            ];
        }

        // Simpan preview ke session
        $this->session->set_userdata('import_preview', $preview);
        $this->session->set_userdata('import_filename', $file['name']);
        $this->session->set_userdata('import_overwrite', $overwrite_existing ? 1 : 0);

        $data = [
            'page_title'    => 'Preview Import Pegawai',
            'page_subtitle' => 'Periksa data sebelum disimpan',
            'preview'       => $preview,
            'filename'      => $file['name'],
            'overwrite_existing' => $overwrite_existing,
            'total'         => count($preview),
            'valid_count'   => count(array_filter($preview, fn($p) => $p['valid'])),
            'error_count'   => count(array_filter($preview, fn($p) => !$p['valid'])),
        ];
        $this->render('master/import_pegawai/preview', $data);
    }

    // Simpan baris-baris valid dari session
    public function simpan()
    {
        $is_ajax = $this->input->is_ajax_request();
        $progress_token = $this->_sanitize_progress_token($this->input->post('progress_token', TRUE));

        $preview  = $this->session->userdata('import_preview');
        $filename = $this->session->userdata('import_filename');
        $overwrite_existing = (bool) $this->session->userdata('import_overwrite');

        if (empty($preview)) {
            if ($is_ajax) {
                return $this->_json_response(FALSE, 'Sesi preview tidak ditemukan. Silakan upload ulang.');
            }

            $this->session->set_flashdata('error', 'Sesi preview tidak ditemukan. Silakan upload ulang.');
            redirect('master/import_pegawai');
            return;
        }

        $total_rows = count($preview);
        if (!empty($progress_token)) {
            $this->_write_progress($progress_token, [
                'status' => 'processing',
                'message' => 'Memulai proses import pegawai...',
                'processed' => 0,
                'total' => $total_rows,
                'percent' => 0,
            ]);
        }

        $divisi_map  = [];
        foreach ($this->Divisi_model->get_all() as $d) $divisi_map[strtoupper($d->kode_divisi)] = $d->id_divisi;
        $jabatan_map = [];
        foreach ($this->Jabatan_model->get_all() as $j) $jabatan_map[strtoupper($j->kode_jabatan)] = $j->id_jabatan;

        $berhasil = 0;
        $gagal    = 0;
        $inserted = 0;
        $updated  = 0;
        $processed = 0;

        foreach ($preview as $p) {
            if (!$p['valid']) {
                $gagal++;
                $processed++;
                if (!empty($progress_token)) {
                    $percent = $total_rows > 0 ? (int) round(($processed / $total_rows) * 100) : 100;
                    $this->_write_progress($progress_token, [
                        'status' => 'processing',
                        'message' => 'Memproses baris pegawai ' . $processed . ' dari ' . $total_rows,
                        'processed' => $processed,
                        'total' => $total_rows,
                        'percent' => min(100, $percent),
                    ]);
                }
                continue;
            }

            $payload = [
                'nip'           => $p['nip'],
                'nama_lengkap'  => $p['nama'],
                'jenis_kelamin' => $p['jk'],
                'tempat_lahir'  => $p['tempat_lahir'],
                'tanggal_lahir' => !empty($p['tanggal_lahir']) ? $p['tanggal_lahir'] : NULL,
                'alamat'        => $p['alamat'],
                'tanggal_masuk' => $p['tgl_masuk'],
                'no_telepon'    => $p['no_telepon'],
                'email'         => $p['email'],
                'nik'           => $p['nik'],
                'id_divisi'     => $divisi_map[strtoupper($p['kode_divisi'])],
                'id_jabatan'    => $jabatan_map[strtoupper($p['kode_jabatan'])],
                'gaji_pokok'    => (float) $p['gaji_pokok'],
                'npwp'          => $p['npwp'],
                'no_bpjs_kesehatan' => $p['no_bpjs_kesehatan'],
                'no_bpjs_ketenagakerjaan' => $p['no_bpjs_ketenagakerjaan'],
                'no_rekening'   => $p['no_rekening'],
                'nama_bank'     => $p['nama_bank'],
                'ptkp_status'   => $p['ptkp'],
            ];

            $existing_pegawai = $this->Pegawai_model->get_by_nip($p['nip']);
            if ($existing_pegawai) {
                if (!$overwrite_existing) {
                    $gagal++;
                    $processed++;
                    if (!empty($progress_token)) {
                        $percent = $total_rows > 0 ? (int) round(($processed / $total_rows) * 100) : 100;
                        $this->_write_progress($progress_token, [
                            'status' => 'processing',
                            'message' => 'Memproses baris pegawai ' . $processed . ' dari ' . $total_rows,
                            'processed' => $processed,
                            'total' => $total_rows,
                            'percent' => min(100, $percent),
                        ]);
                    }
                    continue;
                }
                $this->Pegawai_model->update((int) $existing_pegawai->id_pegawai, $payload);
                $berhasil++;
                $updated++;
            } else {
                $payload['status_pegawai'] = 'AKTIF';
                $this->Pegawai_model->insert($payload);
                $berhasil++;
                $inserted++;
            }

            $processed++;
            if (!empty($progress_token)) {
                $percent = $total_rows > 0 ? (int) round(($processed / $total_rows) * 100) : 100;
                $this->_write_progress($progress_token, [
                    'status' => 'processing',
                    'message' => 'Memproses baris pegawai ' . $processed . ' dari ' . $total_rows,
                    'processed' => $processed,
                    'total' => $total_rows,
                    'percent' => min(100, $percent),
                ]);
            }
        }

        if ($processed < $total_rows && !empty($progress_token)) {
            $processed = $total_rows;
            $this->_write_progress($progress_token, [
                'status' => 'processing',
                'message' => 'Menyelesaikan import pegawai...',
                'processed' => $processed,
                'total' => $total_rows,
                'percent' => 100,
            ]);
        }

        // Log import
        $status = $gagal === 0 ? 'SUCCESS' : ($berhasil === 0 ? 'FAILED' : 'PARTIAL');
        $this->db->insert('tb_import_pegawai_log', [
            'nama_file'   => $filename,
            'total_baris' => count($preview),
            'berhasil'    => $berhasil,
            'gagal'       => $gagal,
            'status'      => $status,
            'id_user'     => $this->session->userdata('id_user'),
        ]);

        $this->session->unset_userdata(['import_preview', 'import_filename', 'import_overwrite']);
        audit_log('IMPORT', 'Pegawai', "File: $filename | Overwrite: " . ($overwrite_existing ? 'YA' : 'TIDAK') . " | Insert: $inserted | Update: $updated | Gagal: $gagal");

        $success_message = "Import selesai: $inserted data baru, $updated data ditimpa, $gagal data dilewati.";

        if (!empty($progress_token)) {
            $this->_write_progress($progress_token, [
                'status' => 'completed',
                'message' => $success_message,
                'processed' => $total_rows,
                'total' => $total_rows,
                'percent' => 100,
                'result' => [
                    'inserted' => $inserted,
                    'updated' => $updated,
                    'failed' => $gagal,
                ],
            ]);
        }

        if ($is_ajax) {
            return $this->_json_response(TRUE, $success_message, [
                'redirect_url' => site_url('master/import_pegawai'),
            ]);
        }

        $this->session->set_flashdata('success', $success_message);
        redirect('master/import_pegawai');
    }

    public function progress_status()
    {
        $token = $this->_sanitize_progress_token($this->input->get('token', TRUE));
        if (empty($token)) {
            return $this->_json_response(FALSE, 'Token progress tidak valid');
        }

        $progress = $this->_read_progress($token);
        if (empty($progress)) {
            return $this->_json_response(TRUE, 'Menunggu progres import...', [
                'status' => 'pending',
                'processed' => 0,
                'total' => 0,
                'percent' => 0,
            ]);
        }

        return $this->_json_response(TRUE, 'OK', $progress);
    }

    private function _json_response($success, $message, $data = [])
    {
        $payload = [
            'success' => (bool) $success,
            'message' => (string) $message,
            'data' => (array) $data,
        ];

        $this->output
            ->set_content_type('application/json')
            ->set_output(json_encode($payload));
        return;
    }

    private function _sanitize_progress_token($token)
    {
        return preg_replace('/[^a-zA-Z0-9_-]/', '', (string) $token);
    }

    private function _progress_file($token)
    {
        return APPPATH . 'cache/import_pegawai_progress_' . $token . '.json';
    }

    private function _write_progress($token, array $payload)
    {
        if (empty($token)) {
            return;
        }

        @file_put_contents($this->_progress_file($token), json_encode($payload));
    }

    private function _read_progress($token)
    {
        $path = $this->_progress_file($token);
        if (!is_file($path)) {
            return [];
        }

        $json = @file_get_contents($path);
        $decoded = json_decode((string) $json, TRUE);
        return is_array($decoded) ? $decoded : [];
    }

    private function _normalize_excel_date($value, $allow_empty = FALSE)
    {
        $value = trim((string) $value);
        if ($value === '') {
            return $allow_empty ? '' : '';
        }

        if (is_numeric($value)) {
            try {
                $dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject((float) $value);
                return $dt->format('Y-m-d');
            } catch (\Exception $e) {
                return $value;
            }
        }

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

        $time = strtotime($value);
        if ($time !== false) {
            return date('Y-m-d', $time);
        }

        return $value;
    }
}
