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

class Absensi extends Auth_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->model(['Absensi_model', 'Pegawai_model', 'Divisi_model', 'Payroll_model', 'Shift_model']);
    }

    public function index()
    {
        $tahun     = (int) ($this->input->get('tahun') ?: date('Y'));
        $bulan     = (int) ($this->input->get('bulan') ?: date('m'));
        $id_divisi = $this->input->get('divisi');
        $filter_status = strtoupper((string) $this->input->get('status'));

        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');
        if (!$resolved) {
            $this->session->set_flashdata('error', 'Periode absensi tidak valid.');
            redirect('dashboard');
            return;
        }

        $data = [
            'page_title'    => 'Manajemen Absensi',
            'page_subtitle' => 'Kelola data kehadiran pegawai',
            'f_tahun'       => $tahun,
            'f_bulan'       => str_pad((string) $bulan, 2, '0', STR_PAD_LEFT),
            'f_tanggal_mulai' => $resolved['tanggal_mulai'],
            'f_tanggal_selesai' => $resolved['tanggal_selesai'],
            'f_periode_label' => $resolved['label_rentang'],
            'f_divisi'      => $id_divisi ? (int) $id_divisi : '',
            'f_status'      => in_array($filter_status, ['SUDAH', 'BELUM'], TRUE) ? $filter_status : '',
            'divisi_list'   => $this->Divisi_model->get_all(),
        ];
        $this->render('transaksi/absensi/list', $data);
    }

    public function ajax_list()
    {
        $tahun = (int) ($this->input->get_post('tahun') ?: date('Y'));
        $bulan = (int) ($this->input->get_post('bulan') ?: date('m'));
        $id_divisi = $this->input->get_post('divisi') ? (int) $this->input->get_post('divisi') : NULL;
        $filter_status = strtoupper((string) $this->input->get_post('filter_status'));
        $draw    = (int) $this->input->get_post('draw');
        $start   = (int) $this->input->get_post('start');
        $length  = (int) $this->input->get_post('length');
        $search  = trim((string) $this->input->get_post('search[value]', TRUE));
        $order_col = (int) $this->input->get_post('order[0][column]');
        $order_dir = (string) $this->input->get_post('order[0][dir]', TRUE);

        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');
        if (!$resolved) {
            echo json_encode([
                'draw' => $draw,
                'recordsTotal' => 0,
                'recordsFiltered' => 0,
                'data' => [],
            ]);
            return;
        }

        $total = $this->Absensi_model->datatable_total_summary($resolved['tanggal_mulai'], $resolved['tanggal_selesai'], $id_divisi);
        $filtered = $this->Absensi_model->datatable_filtered_summary($resolved['tanggal_mulai'], $resolved['tanggal_selesai'], $id_divisi, $search, $filter_status);
        $rows = $this->Absensi_model->datatable_data_summary($resolved['tanggal_mulai'], $resolved['tanggal_selesai'], $start, $length, $search, $order_col, $order_dir, $id_divisi, $filter_status);

        $data = [];
        foreach ($rows as $row) {
            $totalStatus =
                (int) ($row->masuk ?? 0) +
                (int) ($row->alpha ?? 0) +
                (int) ($row->sakit ?? 0) +
                (int) ($row->cuti ?? 0) +
                (int) ($row->izin ?? 0) +
                (int) ($row->libur ?? 0) +
                (int) ($row->lembur ?? 0);

            $belumAbsen = ($totalStatus === 0);
            $data[] = [
                'row_class'     => $belumAbsen ? 'row-attendance-empty' : '',
                'status_absensi'=> $belumAbsen ? 'Belum Absen' : 'Sudah Absen',
                'id_pegawai'    => (int) $row->id_pegawai,
                'nip'           => html_escape($row->nip),
                'nama_lengkap'  => html_escape($row->nama_lengkap),
                'nama_divisi'   => html_escape($row->nama_divisi),
                'masuk'         => '<span class="badge badge-success">' . (int) ($row->masuk ?? 0) . '</span>',
                'sakit'         => '<span class="badge badge-warning">' . (int) ($row->sakit ?? 0) . '</span>',
                'cuti_izin'     => '<span class="badge badge-info">' . ((int) ($row->cuti ?? 0) + (int) ($row->izin ?? 0)) . '</span>',
                'libur'         => '<span class="badge badge-secondary">' . (int) ($row->libur ?? 0) . '</span>',
                'lembur'        => ((int) ($row->lembur ?? 0) > 0)
                    ? '<span class="badge badge-primary">' . (int) $row->lembur . 'x</span>'
                    : '<span class="text-muted">-</span>',
                'terlambat'     => ((int) ($row->terlambat ?? 0) > 0)
                    ? '<span class="badge badge-danger">' . (int) $row->terlambat . 'x</span>'
                    : '<span class="text-muted">-</span>',
                'aksi'          => '<button type="button" class="btn btn-xs btn-primary btn-detail-absensi" data-id="' . (int) $row->id_pegawai . '" data-nama="' . html_escape($row->nama_lengkap) . '"><i class="fa fa-eye"></i></button>',
            ];
        }

        echo json_encode([
            'draw'            => $draw,
            'recordsTotal'    => $total,
            'recordsFiltered' => $filtered,
            'data'            => $data,
        ]);
    }

    public function reset_periode()
    {
        if ($this->input->server('REQUEST_METHOD') !== 'POST' || !$this->input->is_ajax_request()) {
            show_404();
            return;
        }

        $tahun = (int) ($this->input->post('tahun') ?: date('Y'));
        $bulan = (int) ($this->input->post('bulan') ?: date('m'));
        $id_divisi = $this->input->post('divisi');
        $id_divisi = ($id_divisi !== null && $id_divisi !== '' && (int) $id_divisi > 0) ? (int) $id_divisi : null;

        if ($bulan < 1 || $bulan > 12 || $tahun < 2000) {
            return $this->_json_reset_response(false, 'Periode tidak valid');
        }

        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');
        if (!$resolved) {
            return $this->_json_reset_response(false, 'Periode tidak valid');
        }

        $periode_rows = $this->Payroll_model->get_periode_by_bulan_tahun($bulan, $tahun);
        foreach ($periode_rows as $periode) {
            if (in_array((string) $periode->status, ['APPROVED', 'LOCKED'], true)) {
                return $this->_json_reset_response(false, 'Reset absensi ditolak karena payroll periode terkait sudah APPROVED/LOCKED');
            }
        }

        $deleted = $this->Absensi_model->reset_by_range($resolved['tanggal_mulai'], $resolved['tanggal_selesai'], $id_divisi);

        $needs_reprocess = false;
        foreach ($periode_rows as $periode) {
            if ((string) $periode->status === 'PROCESSED' && (string) ($periode->proses_mode ?? 'absensi') === 'absensi') {
                $needs_reprocess = true;
                break;
            }
        }

        $parts = ['Reset absensi berhasil.'];
        $parts[] = $deleted . ' data dihapus.';
        if ($needs_reprocess) {
            $parts[] = 'Payroll periode terkait sudah PROCESSED dengan metode absensi. Silakan proses payroll ulang agar data terupdate.';
        }

        audit_log('RESET', 'Absensi', 'Periode: ' . sprintf('%02d/%04d', $bulan, $tahun) . ' | Divisi: ' . ($id_divisi ?: 'SEMUA') . ' | Dihapus: ' . $deleted);
        return $this->_json_reset_response(true, implode(' ', $parts), [
            'deleted' => $deleted,
            'needs_reprocess' => $needs_reprocess,
        ]);
    }

    private function _json_reset_response($success, $message, $extra = [])
    {
        $payload = array_merge([
            'success' => (bool) $success,
            'message' => (string) $message,
        ], (array) $extra);

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

    public function tambah()
    {
        $data = [
            'page_title'    => 'Tambah Absensi',
            'page_subtitle' => 'Tambah data kehadiran baru',
            'pegawai_list'  => $this->Pegawai_model->get_all(),
            'row'           => NULL,
        ];
        $this->render('transaksi/absensi/form', $data);
    }

    public function detail($id_pegawai)
    {
        $tahun = (int) ($this->input->get('tahun') ?: date('Y'));
        $bulan = (int) ($this->input->get('bulan') ?: date('m'));

        $pegawai = $this->Pegawai_model->get_by_id((int) $id_pegawai);
        if (!$pegawai) {
            $this->session->set_flashdata('error', 'Pegawai tidak ditemukan');
            redirect('transaksi/absensi');
        }

        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');
        if (!$resolved) {
            $this->session->set_flashdata('error', 'Periode absensi tidak valid');
            redirect('transaksi/absensi');
            return;
        }

        $data = [
            'page_title'    => 'Detail Absensi',
            'page_subtitle' => html_escape($pegawai->nama_lengkap),
            'f_tahun'       => $tahun,
            'f_bulan'       => str_pad((string) $bulan, 2, '0', STR_PAD_LEFT),
            'f_tanggal_mulai' => $resolved['tanggal_mulai'],
            'f_tanggal_selesai' => $resolved['tanggal_selesai'],
            'pegawai'       => $pegawai,
            'detail'        => $this->Absensi_model->get_all($id_pegawai, $resolved['tanggal_mulai'], $resolved['tanggal_selesai']),
        ];
        $this->render('transaksi/absensi/detail', $data);
    }

    public function detail_json($id_pegawai)
    {
        $tahun = (int) ($this->input->get('tahun') ?: date('Y'));
        $bulan = (int) ($this->input->get('bulan') ?: date('m'));
        $pegawai = $this->Pegawai_model->get_by_id((int) $id_pegawai);
        if (!$pegawai) {
            $this->output
                ->set_status_header(404)
                ->set_content_type('application/json')
                ->set_output(json_encode(['success' => false, 'message' => 'Pegawai tidak ditemukan']));
            return;
        }

        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');
        if (!$resolved) {
            $this->output
                ->set_status_header(422)
                ->set_content_type('application/json')
                ->set_output(json_encode(['success' => false, 'message' => 'Periode absensi tidak valid']));
            return;
        }

        $rows = $this->Absensi_model->get_detail_rows_periode((int) $id_pegawai, $resolved['tanggal_mulai'], $resolved['tanggal_selesai'], (int) $pegawai->id_divisi);
        $this->output
            ->set_content_type('application/json')
            ->set_output(json_encode([
                'success' => true,
                'pegawai' => [
                    'id_pegawai' => (int) $pegawai->id_pegawai,
                    'nip' => $pegawai->nip,
                    'nama_lengkap' => $pegawai->nama_lengkap,
                    'divisi' => $pegawai->nama_divisi ?? '',
                ],
                'periode' => [
                    'tahun' => $tahun,
                    'bulan' => str_pad((string) $bulan, 2, '0', STR_PAD_LEFT),
                    'tanggal_mulai' => $resolved['tanggal_mulai'],
                    'tanggal_selesai' => $resolved['tanggal_selesai'],
                    'label' => $resolved['label_rentang'],
                ],
                'rows' => $rows,
            ]));
    }

    public function edit($id)
    {
        $absensi = $this->Absensi_model->get_by_id((int) $id);
        if (!$absensi) {
            $this->session->set_flashdata('error', 'Data absensi tidak ditemukan');
            redirect('transaksi/absensi');
        }

        $pegawai = $this->Pegawai_model->get_by_id($absensi->id_pegawai);
        $data = [
            'page_title'    => 'Edit Absensi',
            'page_subtitle' => 'Ubah data kehadiran',
            'pegawai_list'  => $this->Pegawai_model->get_all(),
            'row'           => $absensi,
        ];
        $this->render('transaksi/absensi/form', $data);
    }

    public function simpan()
    {
        if ($this->input->server('REQUEST_METHOD') !== 'POST') {
            redirect('transaksi/absensi');
        }

        $id = $this->input->post('id_absensi');
        $tanggal_ref = $this->input->post('tanggal');
        if ($id) {
            $old = $this->Absensi_model->get_by_id((int) $id);
            $tanggal_ref = $old ? $old->tanggal : $tanggal_ref;
        }

        if ($tanggal_ref && $this->Payroll_model->has_locked_or_approved_period($tanggal_ref, $tanggal_ref)) {
            $this->session->set_flashdata('error', 'Periode payroll APPROVED/LOCKED tidak dapat diubah');
            redirect('transaksi/absensi');
        }

        $jam_masuk  = $this->input->post('jam_masuk')  ?: NULL;
        $jam_keluar = $this->input->post('jam_keluar') ?: NULL;
        $status     = $this->input->post('status');

        // Hitung keterlambatan jika status MASUK dan jam_masuk ada
        $is_terlambat    = 0;
        $menit_terlambat = 0;
        if ($status === 'MASUK' && $jam_masuk) {
            $tanggal_cek = $id ? ($this->Absensi_model->get_by_id((int) $id)->tanggal ?? date('Y-m-d')) : $this->input->post('tanggal');
            $id_pegawai_cek = $id ? ($this->Absensi_model->get_by_id((int) $id)->id_pegawai ?? 0) : (int) $this->input->post('id_pegawai');
            $pegawai_cek = $this->Pegawai_model->get_by_id($id_pegawai_cek);
            if ($pegawai_cek) {
                $shift = $this->Shift_model->get_shift_for_pegawai($pegawai_cek->id_pegawai, $pegawai_cek->id_divisi, $tanggal_cek);
                $tel   = $this->Shift_model->hitung_keterlambatan($jam_masuk, $shift);
                $is_terlambat    = $tel['is_terlambat'];
                $menit_terlambat = $tel['menit_terlambat'];
            }
        }

        $payload = [
            'status'          => $status,
            'jam_masuk'       => $jam_masuk,
            'jam_keluar'      => $jam_keluar,
            'keterangan'      => $this->input->post('keterangan'),
            'is_terlambat'    => $is_terlambat,
            'menit_terlambat' => $menit_terlambat,
        ];

        if ($id) {
            // Update
            $this->Absensi_model->update($id, $payload);
            $alasan = $this->input->post('alasan_koreksi') ?: '(tidak diisi)';
            $msg = 'Data absensi diperbarui';
            audit_log('UPDATE', 'Absensi', "ID: $id | Status: " . $payload['status'] . " | Alasan: " . $alasan);
        } else {
            // Insert
            $payload['id_pegawai'] = $this->input->post('id_pegawai');
            $payload['tanggal']    = $this->input->post('tanggal');
            $payload['sumber']     = 'MANUAL';
            $this->Absensi_model->insert($payload);
            $msg = 'Data absensi ditambahkan';
            audit_log('INSERT', 'Absensi', "Pegawai: " . $payload['id_pegawai'] . " | Tanggal: " . $payload['tanggal']);
        }

        $this->session->set_flashdata('success', $msg);
        redirect('transaksi/absensi');
    }

    public function simpan_inline()
    {
        $id = (int) $this->input->post('id_absensi');
        $id_pegawai = (int) $this->input->post('id_pegawai');
        $tanggal = (string) $this->input->post('tanggal');
        $status = strtoupper(trim((string) $this->input->post('status')));
        $jam_masuk = trim((string) $this->input->post('jam_masuk')) ?: NULL;
        $jam_keluar = trim((string) $this->input->post('jam_keluar')) ?: NULL;
        $keterangan = trim((string) $this->input->post('keterangan'));

        if (!$id_pegawai || !$tanggal) {
            return $this->_json_error('Data absensi tidak lengkap');
        }

        if ($this->Payroll_model->has_locked_or_approved_period($tanggal, $tanggal)) {
            return $this->_json_error('Periode payroll APPROVED/LOCKED tidak dapat diubah');
        }

        $pegawai = $this->Pegawai_model->get_by_id($id_pegawai);
        if (!$pegawai) {
            return $this->_json_error('Pegawai tidak ditemukan');
        }

        $existing = $id ? $this->Absensi_model->get_by_id($id) : NULL;
        if (!$existing && $this->Absensi_model->exists_date($id_pegawai, $tanggal)) {
            $existing = $this->db->where('id_pegawai', $id_pegawai)->where('tanggal', $tanggal)->get('tb_absensi')->row();
            $id = $existing ? (int) $existing->id_absensi : 0;
        }

        if ($status === '') {
            if ($id) {
                $this->Absensi_model->delete($id);
                audit_log('DELETE', 'Absensi', 'Inline delete ID: ' . $id);
            }

            return $this->_json_success('Record absensi dihapus', [
                'id_absensi' => NULL,
                'has_record' => 0,
                'status' => '',
                'jam_masuk' => NULL,
                'jam_keluar' => NULL,
                'keterangan' => '',
            ]);
        }

        $valid_status = ['MASUK', 'ALPHA', 'SAKIT', 'CUTI', 'IZIN', 'LIBUR', 'LEMBUR'];
        if (!in_array($status, $valid_status, TRUE)) {
            return $this->_json_error('Status absensi tidak valid');
        }

        $is_terlambat = 0;
        $menit_terlambat = 0;
        if ($status === 'MASUK' && $jam_masuk) {
            $shift = $this->Shift_model->get_shift_for_pegawai($pegawai->id_pegawai, $pegawai->id_divisi, $tanggal);
            $tel = $this->Shift_model->hitung_keterlambatan($jam_masuk, $shift);
            $is_terlambat = $tel['is_terlambat'];
            $menit_terlambat = $tel['menit_terlambat'];
        }

        $payload = [
            'status' => $status,
            'jam_masuk' => $jam_masuk,
            'jam_keluar' => $jam_keluar,
            'keterangan' => $keterangan,
            'is_terlambat' => $is_terlambat,
            'menit_terlambat' => $menit_terlambat,
        ];

        if ($id) {
            $this->Absensi_model->update($id, $payload);
            audit_log('UPDATE', 'Absensi', 'Inline update ID: ' . $id . ' | Status: ' . $status);
            $message = 'Absensi berhasil diperbarui';
        } else {
            $payload['id_pegawai'] = $id_pegawai;
            $payload['tanggal'] = $tanggal;
            $payload['sumber'] = 'MANUAL';
            $this->Absensi_model->insert($payload);
            $newRow = $this->db->where('id_pegawai', $id_pegawai)->where('tanggal', $tanggal)->get('tb_absensi')->row();
            $id = $newRow ? (int) $newRow->id_absensi : 0;
            audit_log('INSERT', 'Absensi', 'Inline insert pegawai: ' . $id_pegawai . ' | Tanggal: ' . $tanggal);
            $message = 'Absensi berhasil ditambahkan';
        }

        return $this->_json_success($message, [
            'id_absensi' => $id,
            'has_record' => 1,
            'status' => $status,
            'jam_masuk' => $jam_masuk,
            'jam_keluar' => $jam_keluar,
            'keterangan' => $keterangan,
            'is_terlambat' => $is_terlambat,
            'menit_terlambat' => $menit_terlambat,
        ]);
    }

    private function _json_error($message, $status = 422)
    {
        $this->output
            ->set_status_header($status)
            ->set_content_type('application/json')
            ->set_output(json_encode(['success' => false, 'message' => $message]));
    }

    private function _json_success($message, array $data = [])
    {
        $this->output
            ->set_content_type('application/json')
            ->set_output(json_encode(['success' => true, 'message' => $message, 'data' => $data]));
    }

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

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

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

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

    private function _progress_file($token)
    {
        return APPPATH . 'cache/import_absensi_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 _import_fail($is_ajax, $message)
    {
        if ($is_ajax) {
            return $this->_json_error($message);
        }

        $this->session->set_flashdata('error', $message);
        redirect('transaksi/absensi/import');
        return;
    }

    public function hapus($id)
    {
        $absensi = $this->Absensi_model->get_by_id((int) $id);
        if (!$absensi) {
            $this->session->set_flashdata('error', 'Data tidak ditemukan');
            redirect('transaksi/absensi');
        }

        if ($this->Payroll_model->has_locked_or_approved_period($absensi->tanggal, $absensi->tanggal)) {
            $this->session->set_flashdata('error', 'Data pada periode payroll APPROVED/LOCKED tidak dapat dihapus');
            redirect('transaksi/absensi');
        }

        $this->Absensi_model->delete($id);
        audit_log('DELETE', 'Absensi', "ID: $id");
        $this->session->set_flashdata('success', 'Data absensi dihapus');
        redirect('transaksi/absensi');
    }

    public function import()
    {
        // Handle POST - Generate preview first
        if ($this->input->server('REQUEST_METHOD') === 'POST') {
            return $this->_proses_import();
        }

        $tahun = (int) ($this->input->get('tahun') ?: date('Y'));
        $bulan = (int) ($this->input->get('bulan') ?: date('m'));
        $resolved = resolve_payroll_period($bulan, $tahun, 'REGULER');

        // Handle GET - Show Form
        $data = [
            'page_title'    => 'Import Data Absensi',
            'page_subtitle' => 'Unggah data kehadiran dari file Excel',
            'f_tahun'       => $tahun,
            'f_bulan'       => $bulan,
            'f_tanggal_mulai' => $resolved['tanggal_mulai'] ?? '',
            'f_tanggal_selesai' => $resolved['tanggal_selesai'] ?? '',
            'f_periode_label' => $resolved['label_rentang'] ?? '-',
            'history'       => $this->db->order_by('created_at', 'DESC')->limit(10)->get('tb_import_absensi_log')->result(),
        ];
        $this->render('transaksi/absensi/import', $data);
    }

    private function _proses_import()
    {
        $is_ajax = $this->input->is_ajax_request();

        require_once APPPATH . '../vendor/autoload.php';

        if (!isset($_FILES['file_import']) || $_FILES['file_import']['error'] !== UPLOAD_ERR_OK) {
            return $this->_import_fail($is_ajax, 'File tidak ditemukan atau gagal diupload');
        }

        $file = $_FILES['file_import'];
        $nama_file = $file['name'];
        $ukuran = $file['size'];

        if ($ukuran > 5242880) {
            return $this->_import_fail($is_ajax, 'Ukuran file melebihi 5MB');
        }

        $ext = pathinfo($nama_file, PATHINFO_EXTENSION);
        if (!in_array(strtolower($ext), ['xlsx', 'xls'])) {
            return $this->_import_fail($is_ajax, 'File harus berformat Excel (.xlsx atau .xls)');
        }

        try {
            $tahun_import = (int) ($this->input->post('tahun') ?: date('Y'));
            $bulan_import = (int) ($this->input->post('bulan') ?: date('m'));
            $resolved_import = resolve_payroll_period($bulan_import, $tahun_import, 'REGULER');
            if (!$resolved_import) {
                return $this->_import_fail($is_ajax, 'Periode import tidak valid.');
            }

            $tanggal_mulai_import = $resolved_import['tanggal_mulai'];
            $tanggal_selesai_import = $resolved_import['tanggal_selesai'];

            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            if (count($data) < 2) {
                return $this->_import_fail($is_ajax, 'File Excel tidak memiliki data');
            }

            $header = array_map('strtolower', array_map('trim', $data[0]));
            $col_nip = array_search('nip', $header);
            $col_nama = array_search('nama_lengkap', $header);
            $col_tanggal = array_search('tanggal', $header);
            $col_jam_masuk = array_search('jam_masuk', $header);
            $col_jam_keluar = array_search('jam_keluar', $header);
            $col_status = array_search('status', $header);
            $col_ket = array_search('keterangan', $header);

            if ($col_nip === FALSE || $col_nama === FALSE || $col_tanggal === FALSE || $col_status === FALSE) {
                return $this->_import_fail($is_ajax, 'Format file tidak sesuai. Pastikan ada kolom: NIP, Nama Lengkap, Tanggal, Status');
            }

            $overwrite   = (bool) $this->input->post('overwrite');
            $preview = [];

            for ($i = 1; $i < count($data); $i++) {
                $row = $data[$i];
                if (empty($row[$col_nip])) {
                    continue;
                }

                $nip = trim($row[$col_nip]);
                $tanggal = trim($row[$col_tanggal] ?? '');
                $status = strtoupper(trim($row[$col_status] ?? ''));
                $jam_masuk = empty($row[$col_jam_masuk]) ? NULL : date('H:i', strtotime($row[$col_jam_masuk]));
                $jam_keluar = empty($row[$col_jam_keluar]) ? NULL : date('H:i', strtotime($row[$col_jam_keluar]));
                $keterangan = trim($row[$col_ket] ?? '');

                $errors = [];
                $aksi = 'INSERT';

                $pegawai = $this->Pegawai_model->get_by_nip($nip);
                if (!$pegawai) {
                    $errors[] = "NIP '$nip' tidak ditemukan di database";
                } else if ($pegawai->status_pegawai !== 'AKTIF') {
                    $errors[] = "Pegawai $nip bukan AKTIF";
                }

                if (empty($errors)) {
                    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $tanggal)) {
                        $errors[] = 'Format tanggal tidak sesuai (gunakan YYYY-MM-DD)';
                    } else if (!strtotime($tanggal)) {
                        $errors[] = "Tanggal '$tanggal' tidak valid";
                    }
                }

                if (empty($errors)) {
                    $valid_status = ['MASUK', 'ALPHA', 'SAKIT', 'CUTI', 'LIBUR', 'LEMBUR'];
                    if (!in_array($status, $valid_status)) {
                        $errors[] = "Status '$status' tidak valid. Gunakan: " . implode(', ', $valid_status);
                    }
                }

                if (empty($errors) && ($tanggal < $tanggal_mulai_import || $tanggal > $tanggal_selesai_import)) {
                    $errors[] = "Tanggal $tanggal di luar periode import {$tanggal_mulai_import} s/d {$tanggal_selesai_import}";
                }

                if (empty($errors) && $this->Payroll_model->has_locked_or_approved_period($tanggal, $tanggal)) {
                    $errors[] = "Periode payroll untuk tanggal $tanggal sudah APPROVED/LOCKED";
                }

                if (empty($errors) && $pegawai) {
                    $exists = $this->Absensi_model->exists_date($pegawai->id_pegawai, $tanggal);
                    if ($exists) {
                        $aksi = 'UPDATE';
                        if (!$overwrite) {
                            $errors[] = "Data untuk tanggal $tanggal sudah ada";
                        }
                    }
                }

                $preview[] = [
                    'baris' => $i + 1,
                    'nip' => $nip,
                    'nama_lengkap' => trim((string) ($row[$col_nama] ?? '')),
                    'tanggal' => $tanggal,
                    'jam_masuk' => $jam_masuk,
                    'jam_keluar' => $jam_keluar,
                    'status' => $status,
                    'keterangan' => $keterangan,
                    'aksi' => $aksi,
                    'errors' => $errors,
                    'valid' => empty($errors),
                ];
            }

            if (empty($preview)) {
                return $this->_import_fail($is_ajax, 'Tidak ada baris data yang dapat dipreview.');
            }

            $this->session->set_userdata('absensi_import_preview', $preview);
            $this->session->set_userdata('absensi_import_filename', $nama_file);
            $this->session->set_userdata('absensi_import_overwrite', $overwrite ? 1 : 0);
            $this->session->set_userdata('absensi_import_periode', [
                'tahun' => $tahun_import,
                'bulan' => $bulan_import,
                'tanggal_mulai' => $tanggal_mulai_import,
                'tanggal_selesai' => $tanggal_selesai_import,
                'label' => $resolved_import['label_rentang'] ?? '',
            ]);

            $valid_count = count(array_filter($preview, function($r) {
                return !empty($r['valid']);
            }));
            $error_count = count($preview) - $valid_count;

            $data_view = [
                'page_title' => 'Preview Import Absensi',
                'page_subtitle' => 'Periksa data absensi sebelum disimpan',
                'preview' => $preview,
                'filename' => $nama_file,
                'overwrite_existing' => $overwrite,
                'total' => count($preview),
                'valid_count' => $valid_count,
                'error_count' => $error_count,
                'periode_label' => $resolved_import['label_rentang'] ?? '-',
            ];

            if ($is_ajax) {
                return $this->_json_success('Preview berhasil dibuat', [
                    'redirect_url' => site_url('transaksi/absensi/preview_import'),
                ]);
            }

            $this->render('transaksi/absensi/preview', $data_view);

        } catch (\Exception $e) {
            log_message('error', 'Import Absensi Error: ' . $e->getMessage());
            return $this->_import_fail($is_ajax, 'Terjadi error saat membaca file: ' . $e->getMessage());
        }
    }

    public function preview_import()
    {
        $preview = $this->session->userdata('absensi_import_preview');
        $filename = $this->session->userdata('absensi_import_filename');
        $overwrite = (bool) $this->session->userdata('absensi_import_overwrite');
        $periode = (array) $this->session->userdata('absensi_import_periode');

        if (empty($preview)) {
            $this->session->set_flashdata('error', 'Sesi preview import absensi tidak ditemukan. Silakan upload ulang.');
            redirect('transaksi/absensi/import');
            return;
        }

        $valid_count = count(array_filter($preview, function($r) {
            return !empty($r['valid']);
        }));

        $data = [
            'page_title' => 'Preview Import Absensi',
            'page_subtitle' => 'Periksa data absensi sebelum disimpan',
            'preview' => $preview,
            'filename' => $filename,
            'overwrite_existing' => $overwrite,
            'total' => count($preview),
            'valid_count' => $valid_count,
            'error_count' => count($preview) - $valid_count,
            'periode_label' => (string) ($periode['label'] ?? '-'),
        ];

        $this->render('transaksi/absensi/preview', $data);
    }

    public function simpan_import()
    {
        $is_ajax = $this->input->is_ajax_request();
        $progress_token = $this->_sanitize_progress_token($this->input->post('progress_token', TRUE));

        $preview = $this->session->userdata('absensi_import_preview');
        $filename = (string) $this->session->userdata('absensi_import_filename');
        $overwrite = (bool) $this->session->userdata('absensi_import_overwrite');
        $periode = (array) $this->session->userdata('absensi_import_periode');

        if (empty($preview)) {
            if ($is_ajax) {
                return $this->_json_error('Sesi preview import absensi tidak ditemukan. Silakan upload ulang.');
            }
            $this->session->set_flashdata('error', 'Sesi preview import absensi tidak ditemukan. Silakan upload ulang.');
            redirect('transaksi/absensi/import');
            return;
        }

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

        $berhasil = 0;
        $diperbarui = 0;
        $gagal = 0;
        $skip_range = 0;
        $processed = 0;
        $detail_log = [];
        $warning_nip_no_shift = [];

        $id_import = 0;
        $log_awal = [
            'nama_file' => $filename,
            'total_baris' => $total_rows,
            'berhasil' => 0,
            'gagal' => 0,
            'status' => 'FAILED',
            'detail_log' => NULL,
            'id_user' => $this->session->userdata('id_user'),
            'created_at' => date('Y-m-d H:i:s'),
        ];
        if ($this->db->insert('tb_import_absensi_log', $log_awal)) {
            $id_import = (int) $this->db->insert_id();
        }

        $tanggal_mulai_import = (string) ($periode['tanggal_mulai'] ?? '');
        $tanggal_selesai_import = (string) ($periode['tanggal_selesai'] ?? '');

        foreach ($preview as $row) {
            $processed++;
            $nip = trim((string) ($row['nip'] ?? ''));
            $tanggal = trim((string) ($row['tanggal'] ?? ''));
            $status = strtoupper(trim((string) ($row['status'] ?? '')));
            $jam_masuk = !empty($row['jam_masuk']) ? (string) $row['jam_masuk'] : NULL;
            $jam_keluar = !empty($row['jam_keluar']) ? (string) $row['jam_keluar'] : NULL;
            $keterangan = trim((string) ($row['keterangan'] ?? ''));

            if (empty($row['valid'])) {
                $gagal++;
                if ($tanggal_mulai_import !== '' && $tanggal_selesai_import !== '' && $tanggal !== '' && ($tanggal < $tanggal_mulai_import || $tanggal > $tanggal_selesai_import)) {
                    $skip_range++;
                }
                $detail_log[] = 'Baris ' . (int) ($row['baris'] ?? 0) . ' - ' . $nip . ': ' . implode('; ', (array) ($row['errors'] ?? ['Data tidak valid']));
            } else {
                $pegawai = $this->Pegawai_model->get_by_nip($nip);
                if (!$pegawai || $pegawai->status_pegawai !== 'AKTIF') {
                    $gagal++;
                    $detail_log[] = 'Baris ' . (int) ($row['baris'] ?? 0) . ' - ' . $nip . ': Pegawai tidak valid/aktif saat simpan';
                } else if ($this->Payroll_model->has_locked_or_approved_period($tanggal, $tanggal)) {
                    $gagal++;
                    $detail_log[] = 'Baris ' . (int) ($row['baris'] ?? 0) . ' - ' . $nip . ': Periode payroll sudah APPROVED/LOCKED';
                } else {
                    $shift = $this->Shift_model->get_shift_for_pegawai($pegawai->id_pegawai, $pegawai->id_divisi, $tanggal);
                    $pakai_default = ($shift && $shift->is_default);

                    $is_terlambat = 0;
                    $menit_terlambat = 0;
                    if ($status === 'MASUK' && $jam_masuk && $shift) {
                        $tel = $this->Shift_model->hitung_keterlambatan($jam_masuk, $shift);
                        $is_terlambat = (int) $tel['is_terlambat'];
                        $menit_terlambat = (int) $tel['menit_terlambat'];
                    }

                    $min_lembur_cfg = $this->db->where('kunci', 'minimum_lembur_menit')->get('tb_konfigurasi')->row();
                    $min_lembur = isset($min_lembur_cfg->nilai) ? (int) $min_lembur_cfg->nilai : 30;
                    $jam_lembur = 0;
                    if ($status === 'MASUK' && $jam_keluar && $shift) {
                        $jam_lembur = $this->Shift_model->hitung_lembur($jam_keluar, $shift, $min_lembur);
                    }

                    $payload = [
                        'jam_masuk' => $jam_masuk,
                        'jam_keluar' => $jam_keluar,
                        'status' => $status,
                        'keterangan' => $keterangan,
                        'is_terlambat' => $is_terlambat,
                        'menit_terlambat' => $menit_terlambat,
                        'sumber' => 'IMPORT',
                    ];

                    $existing = $this->db
                        ->where('id_pegawai', $pegawai->id_pegawai)
                        ->where('tanggal', $tanggal)
                        ->get('tb_absensi')
                        ->row();

                    if ($existing) {
                        if (!$overwrite) {
                            $gagal++;
                            $detail_log[] = 'Baris ' . (int) ($row['baris'] ?? 0) . ' - ' . $nip . ': Data untuk tanggal ' . $tanggal . ' sudah ada';
                        } else {
                            if ($id_import > 0) {
                                $payload['id_import'] = $id_import;
                            }
                            $this->Absensi_model->update((int) $existing->id_absensi, $payload);
                            $berhasil++;
                            $diperbarui++;
                        }
                    } else {
                        $payload['id_pegawai'] = (int) $pegawai->id_pegawai;
                        $payload['tanggal'] = $tanggal;
                        if ($id_import > 0) {
                            $payload['id_import'] = $id_import;
                        }
                        $payload['created_at'] = date('Y-m-d H:i:s');
                        $this->Absensi_model->insert($payload);
                        $berhasil++;
                    }

                    if ($pakai_default) {
                        $warning_nip_no_shift[] = $nip . ' - ' . $pegawai->nama_lengkap;
                    }

                    if ($jam_lembur > 0 && $shift) {
                        $exists_lembur = $this->db
                            ->where('id_pegawai', $pegawai->id_pegawai)
                            ->where('tanggal', $tanggal)
                            ->where('sumber', 'AUTO')
                            ->count_all_results('tb_lembur');

                        if (!$exists_lembur) {
                            $this->db->insert('tb_lembur', [
                                'id_pegawai' => $pegawai->id_pegawai,
                                'tanggal' => $tanggal,
                                'jam_mulai' => $shift->jam_keluar,
                                'jam_selesai' => $jam_keluar,
                                'durasi_jam' => $jam_lembur,
                                'alasan' => 'Auto-generate dari import absensi',
                                'sumber' => 'AUTO',
                                'status' => 'DRAFT',
                                'created_at' => date('Y-m-d H:i:s'),
                            ]);
                        }
                    }
                }
            }

            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 absensi ' . $processed . ' dari ' . $total_rows,
                    'processed' => $processed,
                    'total' => $total_rows,
                    'percent' => min(100, $percent),
                ]);
            }
        }

        $status_batch = $gagal === 0 ? 'SUCCESS' : ($berhasil > 0 ? 'PARTIAL' : 'FAILED');
        $log = [
            'nama_file' => $filename,
            'total_baris' => $total_rows,
            'berhasil' => $berhasil,
            'gagal' => $gagal,
            'status' => $status_batch,
            'detail_log' => empty($detail_log) ? NULL : implode("\n", $detail_log),
        ];
        if ($id_import > 0) {
            $this->db->where('id_import', $id_import)->update('tb_import_absensi_log', $log);
        }

        audit_log('IMPORT', 'Absensi', 'File: ' . $filename . ' | Berhasil: ' . $berhasil . ' | Diperbarui: ' . $diperbarui . ' | Gagal: ' . $gagal . ' | Skip: ' . $skip_range . ' | Batch ID: ' . $id_import);

        if (!empty($warning_nip_no_shift)) {
            $this->session->set_flashdata('warning_shift', implode(', ', array_unique($warning_nip_no_shift)));
        }

        $baru_count = $berhasil - $diperbarui;
        if ($status_batch === 'SUCCESS') {
            $parts = [];
            if ($baru_count > 0) $parts[] = $baru_count . ' baris baru';
            if ($diperbarui > 0) $parts[] = $diperbarui . ' diperbarui';
            if ($skip_range > 0) $parts[] = $skip_range . ' di-skip (di luar periode)';
            $msg = 'Import selesai: ' . implode(', ', $parts);
            $this->session->set_flashdata('success', $msg);
        } else {
            $parts = [];
            if ($baru_count > 0) $parts[] = $baru_count . ' baru';
            if ($diperbarui > 0) $parts[] = $diperbarui . ' diperbarui';
            if ($gagal > 0) $parts[] = $gagal . ' gagal';
            if ($skip_range > 0) $parts[] = $skip_range . ' di-skip (di luar periode)';
            $msg = 'Import selesai: ' . implode(', ', $parts);
            $this->session->set_flashdata('warning', $msg);
        }

        $this->session->unset_userdata([
            'absensi_import_preview',
            'absensi_import_filename',
            'absensi_import_overwrite',
            'absensi_import_periode',
        ]);

        if (!empty($progress_token)) {
            $this->_write_progress($progress_token, [
                'status' => 'completed',
                'message' => $msg,
                'processed' => $total_rows,
                'total' => $total_rows,
                'percent' => 100,
                'result' => [
                    'status_batch' => $status_batch,
                    'berhasil' => $berhasil,
                    'diperbarui' => $diperbarui,
                    'gagal' => $gagal,
                    'skip' => $skip_range,
                ],
            ]);
        }

        if ($is_ajax) {
            return $this->_json_success($msg, [
                'redirect_url' => site_url('transaksi/absensi/import'),
                'status_batch' => $status_batch,
            ]);
        }

        redirect('transaksi/absensi/import');
    }

    public function delete_batch($id_import)
    {
        $id_import = (int) $id_import;
        if ($id_import <= 0) {
            $this->session->set_flashdata('error', 'Batch ID tidak valid');
            redirect('transaksi/absensi/import');
        }

        // Get batch log
        $batch = $this->db->where('id_import', $id_import)->get('tb_import_absensi_log')->row();
        if (!$batch) {
            $this->session->set_flashdata('error', 'Batch tidak ditemukan');
            redirect('transaksi/absensi/import');
        }

        // Check if period is locked
        $absensi = $this->db->where('id_import', $id_import)->limit(1)->get('tb_absensi')->row();
        if ($absensi && $this->Payroll_model->has_locked_or_approved_period($absensi->tanggal, $absensi->tanggal)) {
            $this->session->set_flashdata('error', 'Batch tidak dapat dihapus karena periode payroll sudah APPROVED/LOCKED');
            redirect('transaksi/absensi/import');
        }

        // Delete batch data
        $deleted = $this->Absensi_model->delete_by_import($id_import);
        
        // Update batch log status
        $this->db->where('id_import', $id_import)->update('tb_import_absensi_log', ['status' => 'DELETED']);

        audit_log('DELETE', 'Absensi Batch', "Batch ID: $id_import | File: {$batch->nama_file} | Dihapus: $deleted baris");
        $this->session->set_flashdata('success', "Batch #{$id_import} berhasil dihapus ($deleted baris)");
        redirect('transaksi/absensi/import');
    }

    public function template()
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('absensi');

        // Header
        $headers = ['A1'=>'nip', 'B1'=>'nama_lengkap', 'C1'=>'tanggal', 'D1'=>'jam_masuk', 'E1'=>'jam_keluar', 'F1'=>'status', 'G1'=>'keterangan'];
        foreach ($headers as $cell => $val) {
            $sheet->setCellValue($cell, $val);
        }

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

        // Sample
        $sample = [
            'A2' => 'EMP001', 'B2' => 'Budi Santoso', 'C2' => '2026-01-15',
            'D2' => '08:00', 'E2' => '17:00', 'F2' => 'MASUK', 'G2' => ''
        ];
        foreach ($sample as $cell => $val) $sheet->setCellValue($cell, $val);

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

        // Info sheet
        $info = $spreadsheet->createSheet();
        $info->setTitle('Petunjuk');
        $info->setCellValue('A1', 'PETUNJUK PENGISIAN');
        $info->setCellValue('A3', 'Kolom wajib: nip, nama_lengkap, tanggal, status');
        $info->setCellValue('A4', 'Format tanggal: YYYY-MM-DD');
        $info->setCellValue('A5', 'Format jam: HH:MM');
        $info->setCellValue('A6', 'Status: MASUK, ALPHA, SAKIT, CUTI, LIBUR, LEMBUR');
        $info->getStyle('A1')->getFont()->setBold(TRUE)->setSize(14);
        $info->getColumnDimension('A')->setWidth(100);

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