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

class Payroll_model extends CI_Model
{
    private $table_periode = 'tb_payroll_periode';
    private $table_payroll = 'tb_payroll';
    private $table_detail = 'tb_payroll_komponen';
    private $table_adjustment = 'tb_payroll_komponen_adjustment';
    private $table_adjustment_audit = 'tb_payroll_adjustment_audit';
    private $adjustment_feature_ready = null;
    private $has_proses_mode_column = null;

    public function get_periode_list($status_filter = null)
    {
        $this->db
            ->select('pp.*, COUNT(p.id_payroll) as total_pegawai, COALESCE(SUM(p.gaji_neto),0) as total_neto', false)
            ->from($this->table_periode . ' pp')
            ->join($this->table_payroll . ' p', 'p.id_periode = pp.id_periode', 'left');

        if (!empty($status_filter)) {
            if (is_array($status_filter)) {
                $this->db->where_in('pp.status', $status_filter);
            } else {
                $this->db->where('pp.status', $status_filter);
            }
        }

        $rows = $this->db
            ->group_by('pp.id_periode')
            ->order_by('pp.tahun', 'DESC')
            ->order_by('pp.bulan', 'DESC')
            ->get()->result();

        foreach ($rows as $row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
        }

        return $rows;
    }

    public function get_periode_by_id($id)
    {
        $row = $this->db->where('id_periode', (int) $id)->get($this->table_periode)->row();
        if ($row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
        }
        return $row;
    }

    public function get_periode_by_bulan_tahun($bulan, $tahun)
    {
        $rows = $this->db
            ->from($this->table_periode)
            ->where('bulan', (int) $bulan)
            ->where('tahun', (int) $tahun)
            ->get()->result();

        foreach ($rows as $row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
        }

        return $rows;
    }

    public function get_periode_target($bulan, $tahun, $tipe_periode = 'REGULER', $status_filter = null)
    {
        $this->db
            ->from($this->table_periode)
            ->where('bulan', (int) $bulan)
            ->where('tahun', (int) $tahun)
            ->where('tipe_periode', in_array((string) $tipe_periode, ['REGULER', 'THR'], true) ? (string) $tipe_periode : 'REGULER');

        if (!empty($status_filter)) {
            if (is_array($status_filter)) {
                $this->db->where_in('status', $status_filter);
            } else {
                $this->db->where('status', (string) $status_filter);
            }
        }

        $row = $this->db
            ->order_by('id_periode', 'DESC')
            ->limit(1)
            ->get()
            ->row();

        if ($row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
        }

        return $row;
    }
    public function get_payroll_detail($id_periode)
    {
        $select_proses_mode = $this->_has_proses_mode_column()
            ? 'pp.proses_mode'
            : 'NULL as proses_mode';

        $rows = $this->db
            ->select('p.*, pg.nip, pg.nama_lengkap, d.nama_divisi,
                      pp.tanggal_mulai, pp.tanggal_selesai, pp.catatan, ' . $select_proses_mode . ',
                      COALESCE(absensi.hari_hadir, 0) as hari_hadir,
                      COALESCE(absensi.hari_absen, 0) as hari_absen,
                      COALESCE(lembur.total_jam_lembur, 0) as total_jam_lembur', false)
            ->from($this->table_payroll . ' p')
            ->join($this->table_periode . ' pp', 'pp.id_periode = p.id_periode', 'left')
            ->join('tb_pegawai pg', 'pg.id_pegawai = p.id_pegawai', 'left')
            ->join('tb_divisi d', 'd.id_divisi = pg.id_divisi', 'left')
            ->join(
                                '(SELECT p2.id_payroll,
                                                 SUM(CASE WHEN a.status = "MASUK" THEN 1 ELSE 0 END) as hari_hadir,
                                                 SUM(CASE WHEN a.status IN ("ALPHA", "IZIN", "SAKIT", "CUTI") THEN 1 ELSE 0 END) as hari_absen
                                    FROM tb_payroll p2
                                    JOIN tb_payroll_periode pp2 ON pp2.id_periode = p2.id_periode
                                    LEFT JOIN tb_absensi a ON a.id_pegawai = p2.id_pegawai
                                        AND a.tanggal >= pp2.tanggal_mulai
                                        AND a.tanggal <= pp2.tanggal_selesai
                                    GROUP BY p2.id_payroll) absensi',
                                'absensi.id_payroll = p.id_payroll',
                'left'
            )
            ->join(
                                '(SELECT p2.id_payroll,
                                                 SUM(CASE WHEN l.status = "APPROVED" THEN l.durasi_jam ELSE 0 END) as total_jam_lembur
                                    FROM tb_payroll p2
                                    JOIN tb_payroll_periode pp2 ON pp2.id_periode = p2.id_periode
                                    LEFT JOIN tb_lembur l ON l.id_pegawai = p2.id_pegawai
                                        AND l.tanggal >= pp2.tanggal_mulai
                                        AND l.tanggal <= pp2.tanggal_selesai
                                        AND l.status = "APPROVED"
                                    GROUP BY p2.id_payroll) lembur',
                                'lembur.id_payroll = p.id_payroll',
                'left'
            )
            ->where('p.id_periode', (int) $id_periode)
            ->order_by('d.nama_divisi', 'ASC')
            ->order_by('pg.nama_lengkap', 'ASC')
            ->get()->result();

        foreach ($rows as $row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
            $this->_apply_tanpa_absensi_attendance($row);
        }

        return $rows;
    }

    public function datatable_detail_total($id_periode)
    {
        return (int) $this->db
            ->from($this->table_payroll . ' p')
            ->where('p.id_periode', (int) $id_periode)
            ->count_all_results();
    }

    public function datatable_detail_filtered($id_periode, $search = '')
    {
        $this->_build_payroll_detail_datatable_query($id_periode, $search);
        return (int) $this->db->count_all_results();
    }

    public function datatable_detail_data($id_periode, $start, $length, $search = '', $order_col = 2, $order_dir = 'asc')
    {
        $columns = [
            1 => 'pg.nip',
            2 => 'pg.nama_lengkap',
            3 => 'd.nama_divisi',
            4 => 'p.gaji_pokok',
            5 => 'p.total_tunjangan',
            6 => 'p.total_lembur',
            7 => 'p.total_potongan',
            8 => 'p.gaji_neto',
        ];

        $this->_build_payroll_detail_datatable_query($id_periode, $search);

        $sort_field = isset($columns[(int) $order_col]) ? $columns[(int) $order_col] : 'pg.nama_lengkap';
        $sort_dir = strtolower((string) $order_dir) === 'desc' ? 'DESC' : 'ASC';
        $this->db->order_by($sort_field, $sort_dir);
        $this->db->order_by('pg.nama_lengkap', 'ASC');

        $length = (int) $length;
        $start = max(0, (int) $start);
        if ($length > 0) {
            $this->db->limit($length, $start);
        }

        return $this->db
            ->select('p.id_payroll, p.gaji_pokok, p.total_tunjangan, p.total_lembur, p.total_potongan, p.gaji_neto, pg.nip, pg.nama_lengkap, d.nama_divisi', false)
            ->get()->result();
    }

    private function _build_payroll_detail_datatable_query($id_periode, $search = '')
    {
        $this->db
            ->from($this->table_payroll . ' p')
            ->join('tb_pegawai pg', 'pg.id_pegawai = p.id_pegawai', 'left')
            ->join('tb_divisi d', 'd.id_divisi = pg.id_divisi', 'left')
            ->where('p.id_periode', (int) $id_periode);

        $search = trim((string) $search);
        if ($search !== '') {
            $this->db->group_start()
                ->like('pg.nip', $search)
                ->or_like('pg.nama_lengkap', $search)
                ->or_like('d.nama_divisi', $search)
                ->group_end();
        }
    }

    public function periode_exists($bulan, $tahun, $tipe_periode = 'REGULER', $exclude_id = null)
    {
        $this->db->where('bulan', (int) $bulan)->where('tahun', (int) $tahun)->where('tipe_periode', $tipe_periode);
        if ($exclude_id) {
            $this->db->where('id_periode !=', (int) $exclude_id);
        }
        return $this->db->count_all_results($this->table_periode) > 0;
    }

    public function periode_overlap_exists($tanggal_mulai, $tanggal_selesai, $tipe_periode = 'REGULER', $exclude_id = null)
    {
        $this->db
            ->from($this->table_periode)
            ->where('tipe_periode', in_array((string) $tipe_periode, ['REGULER', 'THR'], true) ? (string) $tipe_periode : 'REGULER')
            ->where('tanggal_mulai <=', (string) $tanggal_selesai)
            ->where('tanggal_selesai >=', (string) $tanggal_mulai);

        if ($exclude_id) {
            $this->db->where('id_periode !=', (int) $exclude_id);
        }

        return $this->db->count_all_results() > 0;
    }

    public function insert_periode($data)
    {
        $this->db->insert($this->table_periode, $data);
        return $this->db->insert_id();
    }

    public function update_periode_status($id_periode, $status, $id_user)
    {
        $payload = ['status' => $status];
        $now = date('Y-m-d H:i:s');

        if ($status === 'PROCESSED') {
            $payload['processed_by'] = (int) $id_user;
            $payload['processed_at'] = $now;
        } elseif ($status === 'APPROVED') {
            $payload['approved_by'] = (int) $id_user;
            $payload['approved_at'] = $now;
        } elseif ($status === 'LOCKED') {
            $payload['locked_by'] = (int) $id_user;
            $payload['locked_at'] = $now;
        }

        $this->db->where('id_periode', (int) $id_periode)->update($this->table_periode, $payload);
        return $this->db->affected_rows();
    }

    public function delete_draft_periode($id_periode, $force_delete = false)
    {
        $periode = $this->get_periode_by_id($id_periode);
        if (!$periode || (!$force_delete && $periode->status !== 'DRAFT')) {
            return false;
        }

        $this->db->trans_begin();
        $payroll_rows = $this->db->select('id_payroll')->where('id_periode', (int) $id_periode)->get($this->table_payroll)->result();
        if (!empty($payroll_rows)) {
            $payroll_ids = array_map(function ($row) {
                return (int) $row->id_payroll;
            }, $payroll_rows);
            $this->db->where_in('id_payroll', $payroll_ids)->delete($this->table_detail);
        }
        $this->db->where('id_periode', (int) $id_periode)->delete($this->table_payroll);
        $this->db->where('id_periode', (int) $id_periode)->delete($this->table_periode);

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return false;
        }

        $this->db->trans_commit();
        return true;
    }

    public function process_payroll($id_periode, $id_user)
    {
        $periode = $this->get_periode_by_id($id_periode);
        if (!$periode) {
            return ['success' => false, 'message' => 'Periode tidak ditemukan'];
        }

        if ($periode->status === 'LOCKED') {
            return ['success' => false, 'message' => 'Periode LOCKED tidak dapat diproses ulang'];
        }

        $pegawai = $this->db->where('status_pegawai', 'AKTIF')->get('tb_pegawai')->result();
        if (empty($pegawai)) {
            return ['success' => false, 'message' => 'Tidak ada pegawai aktif'];
        }

        $tarif_cfg    = $this->db->where('kunci', 'tarif_lembur_per_jam')->get('tb_konfigurasi')->row();
        $tarif_lembur = $tarif_cfg ? (float) $tarif_cfg->nilai : 0;

        // Load semua komponen aktif (termasuk cara_hitung), kecuali GAPOK & LEMBUR
        // Filter berdasarkan tipe_periode: tampilkan 'SEMUA' dan komponen yang cocok dengan tipe periode ini
        $tipe_periode = isset($periode->tipe_periode) ? $periode->tipe_periode : 'REGULER';
        $komponen_master = $this->db
            ->select('id_komponen, kode_komponen, nama_komponen, jenis, tipe_nilai, cara_hitung, nilai_default, formula_expression, is_wajib')
            ->where('is_active', 1)
            ->where_not_in('kode_komponen', ['GAPOK', 'LEMBUR'])
            ->where_in('tipe_periode', ['SEMUA', $tipe_periode])
            ->order_by('urutan', 'ASC')
            ->get('tb_komponen_gaji')->result();

        // Load rules kehadiran semua komponen (1 query)
        $rules_map = $this->_get_komponen_rules_map();

        // Deteksi ALPHA: sisipkan record AUTO ke tb_absensi untuk hari kerja tanpa absensi
        $this->_inject_alpha_records($periode->tanggal_mulai, $periode->tanggal_selesai, $pegawai);

        // Load ringkasan absensi semua pegawai dalam periode (1 query besar, efisien)
        $absensi_map = $this->_get_absensi_summary($periode->tanggal_mulai, $periode->tanggal_selesai);

        $this->db->trans_begin();

        // Reset hasil lama jika reprocess.
        $old_payroll = $this->db->select('id_payroll')->where('id_periode', (int) $id_periode)->get($this->table_payroll)->result();
        if (!empty($old_payroll)) {
            $ids = array_map(function ($r) { return (int) $r->id_payroll; }, $old_payroll);
            $this->db->where_in('id_payroll', $ids)->delete($this->table_detail);
        }
        $this->db->where('id_periode', (int) $id_periode)->delete($this->table_payroll);

        $processed = 0;
        try {
            foreach ($pegawai as $pg) {
                $gaji_pokok   = (float) $pg->gaji_pokok;
                $map_komponen = $this->_get_pegawai_komponen_map($pg->id_pegawai);
                $pg_absensi   = isset($absensi_map[(int) $pg->id_pegawai]) ? $absensi_map[(int) $pg->id_pegawai] : [];

                // Hitung jam lembur APPROVED dalam periode
                $durasi_lembur = $this->db
                    ->select('COALESCE(SUM(durasi_jam),0) as total_jam', false)
                    ->where('id_pegawai', (int) $pg->id_pegawai)
                    ->where('status', 'APPROVED')
                    ->where('tanggal >=', $periode->tanggal_mulai)
                    ->where('tanggal <=', $periode->tanggal_selesai)
                    ->get('tb_lembur')->row();
                $jam_lembur = $durasi_lembur ? (float) $durasi_lembur->total_jam : 0;

                $rate_lembur    = $tarif_lembur > 0 ? $tarif_lembur : ($gaji_pokok / 173);
                $nominal_lembur = round($jam_lembur * $rate_lembur, 2);

                $total_tunjangan = 0;
                $total_potongan  = 0;
                $formula_context = $this->_build_formula_context($pg, $pg_absensi, $gaji_pokok, $jam_lembur, $nominal_lembur, $periode);

                // Hitung nilai tiap komponen (tahap 1: akumulasi total)
                $detail_items = []; // simpan sementara untuk insert detail
                foreach ($komponen_master as $k) {
                $mapped = isset($map_komponen[(int) $k->id_komponen]) ? $map_komponen[(int) $k->id_komponen] : null;

                $is_selected = ((int) $k->is_wajib === 1);
                if ($mapped) {
                    $is_selected = ((int) $mapped->is_active === 1);
                }
                if (!$is_selected) continue;
                if (!$this->_is_komponen_applicable_for_pegawai($k, $pg)) continue;

                $nilai_dasar = (float) $k->nilai_default;
                $jumlah_hari = null;
                $sumber      = 'SYSTEM_DEFAULT';

                // --- Cara hitung PER_HARI ---
                if (($k->cara_hitung ?? 'FLAT') === 'PER_HARI') {
                    $rules = isset($rules_map[(int) $k->id_komponen]) ? $rules_map[(int) $k->id_komponen] : [];
                    $jumlah_hari = 0;
                    foreach ($rules as $st) {
                        $jumlah_hari += isset($pg_absensi[$st]) ? (int) $pg_absensi[$st] : 0;
                    }
                    $nilai  = round($nilai_dasar * $jumlah_hari, 2);
                    $sumber = 'PER_HARI';
                } elseif (($k->cara_hitung ?? 'FLAT') === 'FORMULA') {
                    $formula_context['SUBTOTAL_TUNJANGAN'] = (float) $total_tunjangan;
                    $formula_context['SUBTOTAL_POTONGAN']  = (float) $total_potongan;
                    $formula_context['BRUTO_SEMENTARA']    = (float) ($gaji_pokok + $total_tunjangan + $nominal_lembur);
                    $nilai  = $this->_evaluate_formula_expression((string) $k->formula_expression, $formula_context);
                    $nilai  = round($nilai, 2);
                    $sumber = 'FORMULA';
                } else {
                    // FLAT
                    if ($mapped && $mapped->nilai_override !== null && $mapped->nilai_override !== '') {
                        $nilai_dasar = (float) $mapped->nilai_override;
                        $sumber = 'MAPPING_OVERRIDE';
                    }
                    $nilai = $nilai_dasar;
                    if ($k->tipe_nilai === 'PERSEN') {
                        $nilai = round(($nilai / 100) * $gaji_pokok, 2);
                    } else {
                        $nilai = round($nilai, 2);
                    }
                }

                if ($k->jenis === 'TUNJANGAN') { $total_tunjangan += $nilai; }
                else                           { $total_potongan  += $nilai; }

                $formula_context[strtoupper((string) $k->kode_komponen)] = (float) $nilai;
                $formula_context['SUBTOTAL_TUNJANGAN'] = (float) $total_tunjangan;
                $formula_context['SUBTOTAL_POTONGAN'] = (float) $total_potongan;
                $formula_context['BRUTO_SEMENTARA'] = (float) ($gaji_pokok + $total_tunjangan + $nominal_lembur);

                    $detail_items[] = [
                        'id_komponen'   => (int) $k->id_komponen,
                        'nama_komponen' => $k->nama_komponen,
                        'jenis'         => $k->jenis,
                        'tipe_nilai'    => $k->tipe_nilai,
                        'nilai'         => $nilai,
                        'jumlah_hari'   => $jumlah_hari,
                        'sumber'        => $sumber,
                    ];
                }

                $gaji_bruto = $gaji_pokok + $total_tunjangan + $nominal_lembur;
                $gaji_neto  = $gaji_bruto - $total_potongan;

                $this->db->insert($this->table_payroll, [
                    'id_periode'      => (int) $id_periode,
                    'id_pegawai'      => (int) $pg->id_pegawai,
                    'gaji_pokok'      => $gaji_pokok,
                    'total_tunjangan' => $total_tunjangan,
                    'total_potongan'  => $total_potongan,
                    'total_lembur'    => $nominal_lembur,
                    'gaji_bruto'      => $gaji_bruto,
                    'gaji_neto'       => $gaji_neto,
                    'status'          => 'PROCESSED',
                ]);
                $id_payroll = $this->db->insert_id();

                // Insert detail komponen
                foreach ($detail_items as $item) {
                    $this->db->insert($this->table_detail, [
                        'id_payroll'    => (int) $id_payroll,
                        'id_komponen'   => $item['id_komponen'],
                        'nama_komponen' => $item['nama_komponen'],
                        'jenis'         => $item['jenis'],
                        'tipe_nilai'    => $item['tipe_nilai'],
                        'nilai'         => $item['nilai'],
                        'jumlah_hari'   => $item['jumlah_hari'],
                        'sumber'        => $item['sumber'],
                    ]);
                }

                // Insert lembur jika ada
                if ($nominal_lembur > 0) {
                    $this->db->insert($this->table_detail, [
                        'id_payroll'    => (int) $id_payroll,
                        'id_komponen'   => null,
                        'nama_komponen' => 'Upah Lembur',
                        'jenis'         => 'TUNJANGAN',
                        'tipe_nilai'    => 'NOMINAL',
                        'nilai'         => $nominal_lembur,
                        'jumlah_hari'   => null,
                        'sumber'        => 'LEMBUR_APPROVED',
                    ]);
                }

                $processed++;
            }
        } catch (RuntimeException $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal memproses payroll: ' . $e->getMessage()];
        }

        $this->update_periode_status($id_periode, 'PROCESSED', $id_user);
        $this->update_periode_process_mode($id_periode, 'absensi');

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal memproses payroll'];
        }

        $this->db->trans_commit();
        return ['success' => true, 'message' => 'Payroll berhasil diproses', 'processed' => $processed];
    }

    /**
     * Process payroll with options:
     * - $mode: 'absensi' (use attendance) or 'tanpa_absensi' (treat all present)
     * - $employee_ids: array of id_pegawai to process (empty = all active)
     */
    public function process_payroll_with_options($id_periode, $id_user, $mode = 'absensi', array $employee_ids = [])
    {
        $mode = strtolower((string) $mode) === 'tanpa_absensi' ? 'tanpa_absensi' : 'absensi';
        $periode = $this->get_periode_by_id($id_periode);
        if (!$periode) {
            return ['success' => false, 'message' => 'Periode tidak ditemukan'];
        }

        if ($periode->status === 'LOCKED') {
            return ['success' => false, 'message' => 'Periode LOCKED tidak dapat diproses ulang'];
        }

        // Load pegawai (filter jika diberikan)
        $this->db->from('tb_pegawai')->where('status_pegawai', 'AKTIF');
        if (!empty($employee_ids)) {
            $this->db->where_in('id_pegawai', array_map('intval', $employee_ids));
        }
        $pegawai = $this->db->get()->result();
        if (empty($pegawai)) {
            return ['success' => false, 'message' => 'Tidak ada pegawai aktif terpilih'];
        }

        $tarif_cfg    = $this->db->where('kunci', 'tarif_lembur_per_jam')->get('tb_konfigurasi')->row();
        $tarif_lembur = $tarif_cfg ? (float) $tarif_cfg->nilai : 0;

        $tipe_periode = isset($periode->tipe_periode) ? $periode->tipe_periode : 'REGULER';
        $komponen_master = $this->db
            ->select('id_komponen, kode_komponen, nama_komponen, jenis, tipe_nilai, cara_hitung, nilai_default, formula_expression, is_wajib')
            ->where('is_active', 1)
            ->where_not_in('kode_komponen', ['GAPOK', 'LEMBUR'])
            ->where_in('tipe_periode', ['SEMUA', $tipe_periode])
            ->order_by('urutan', 'ASC')
            ->get('tb_komponen_gaji')->result();

        $rules_map = $this->_get_komponen_rules_map();

        // Prepare absensi map depending on mode
        if ($mode === 'tanpa_absensi') {
            // Treat each pegawai as hadir penuh sesuai jadwal kerja efektifnya.
            $holiday_rows = $this->db->select('tanggal')->from('tb_hari_libur')
                ->where('tanggal >=', $periode->tanggal_mulai)
                ->where('tanggal <=', $periode->tanggal_selesai)
                ->get()->result();
            $holidays = [];
            foreach ($holiday_rows as $h) { $holidays[$h->tanggal] = true; }

            $absensi_map = [];
            foreach ($pegawai as $pg) {
                $working_days = $this->_count_working_days_by_schedule(
                    (int) $pg->id_pegawai,
                    (int) ($pg->id_divisi ?? 0),
                    $periode->tanggal_mulai,
                    $periode->tanggal_selesai,
                    $holidays
                );

                $absensi_map[(int) $pg->id_pegawai] = [
                    'MASUK' => $working_days,
                    'ALPHA' => 0,
                    'SAKIT' => 0,
                    'CUTI' => 0,
                    'IZIN' => 0,
                ];
            }
        } else {
            // Deteksi ALPHA: sisipkan record AUTO ke tb_absensi untuk hari kerja tanpa absensi
            $this->_inject_alpha_records($periode->tanggal_mulai, $periode->tanggal_selesai, $pegawai);
            $absensi_map = $this->_get_absensi_summary($periode->tanggal_mulai, $periode->tanggal_selesai);
        }

        $this->db->trans_begin();

        // Reset hasil lama jika reprocess.
        $old_payroll = $this->db->select('id_payroll')->where('id_periode', (int) $id_periode)->get($this->table_payroll)->result();
        if (!empty($old_payroll)) {
            $ids = array_map(function ($r) { return (int) $r->id_payroll; }, $old_payroll);
            $this->db->where_in('id_payroll', $ids)->delete($this->table_detail);
        }
        $this->db->where('id_periode', (int) $id_periode)->delete($this->table_payroll);

        $processed = 0;
        try {
            foreach ($pegawai as $pg) {
                $gaji_pokok   = (float) $pg->gaji_pokok;
                $map_komponen = $this->_get_pegawai_komponen_map($pg->id_pegawai);
                $pg_absensi   = isset($absensi_map[(int) $pg->id_pegawai]) ? $absensi_map[(int) $pg->id_pegawai] : [];

                $durasi_lembur = $this->db
                    ->select('COALESCE(SUM(durasi_jam),0) as total_jam', false)
                    ->where('id_pegawai', (int) $pg->id_pegawai)
                    ->where('status', 'APPROVED')
                    ->where('tanggal >=', $periode->tanggal_mulai)
                    ->where('tanggal <=', $periode->tanggal_selesai)
                    ->get('tb_lembur')->row();
                $jam_lembur = $durasi_lembur ? (float) $durasi_lembur->total_jam : 0;

                $rate_lembur    = $tarif_lembur > 0 ? $tarif_lembur : ($gaji_pokok / 173);
                $nominal_lembur = round($jam_lembur * $rate_lembur, 2);

                $total_tunjangan = 0;
                $total_potongan  = 0;
                $formula_context = $this->_build_formula_context($pg, $pg_absensi, $gaji_pokok, $jam_lembur, $nominal_lembur, $periode);

                $detail_items = [];
                foreach ($komponen_master as $k) {
                    $mapped = isset($map_komponen[(int) $k->id_komponen]) ? $map_komponen[(int) $k->id_komponen] : null;

                    $is_selected = ((int) $k->is_wajib === 1);
                    if ($mapped) {
                        $is_selected = ((int) $mapped->is_active === 1);
                    }
                    if (!$is_selected) continue;
                    if (!$this->_is_komponen_applicable_for_pegawai($k, $pg)) continue;

                    $nilai_dasar = (float) $k->nilai_default;
                    $jumlah_hari = null;
                    $sumber      = 'SYSTEM_DEFAULT';

                    if (($k->cara_hitung ?? 'FLAT') === 'PER_HARI') {
                        $rules = isset($rules_map[(int) $k->id_komponen]) ? $rules_map[(int) $k->id_komponen] : [];
                        $jumlah_hari = 0;
                        foreach ($rules as $st) {
                            $jumlah_hari += isset($pg_absensi[$st]) ? (int) $pg_absensi[$st] : 0;
                        }
                        $nilai  = round($nilai_dasar * $jumlah_hari, 2);
                        $sumber = 'PER_HARI';
                    } elseif (($k->cara_hitung ?? 'FLAT') === 'FORMULA') {
                        $formula_context['SUBTOTAL_TUNJANGAN'] = (float) $total_tunjangan;
                        $formula_context['SUBTOTAL_POTONGAN']  = (float) $total_potongan;
                        $formula_context['BRUTO_SEMENTARA']    = (float) ($gaji_pokok + $total_tunjangan + $nominal_lembur);
                        $nilai  = $this->_evaluate_formula_expression((string) $k->formula_expression, $formula_context);
                        $nilai  = round($nilai, 2);
                        $sumber = 'FORMULA';
                    } else {
                        if ($mapped && $mapped->nilai_override !== null && $mapped->nilai_override !== '') {
                            $nilai_dasar = (float) $mapped->nilai_override;
                            $sumber = 'MAPPING_OVERRIDE';
                        }
                        $nilai = $nilai_dasar;
                        if ($k->tipe_nilai === 'PERSEN') {
                            $nilai = round(($nilai / 100) * $gaji_pokok, 2);
                        } else {
                            $nilai = round($nilai, 2);
                        }
                    }

                    if ($k->jenis === 'TUNJANGAN') { $total_tunjangan += $nilai; }
                    else                           { $total_potongan  += $nilai; }

                    $formula_context[strtoupper((string) $k->kode_komponen)] = (float) $nilai;
                    $formula_context['SUBTOTAL_TUNJANGAN'] = (float) $total_tunjangan;
                    $formula_context['SUBTOTAL_POTONGAN'] = (float) $total_potongan;
                    $formula_context['BRUTO_SEMENTARA'] = (float) ($gaji_pokok + $total_tunjangan + $nominal_lembur);

                    $detail_items[] = [
                        'id_komponen'   => (int) $k->id_komponen,
                        'nama_komponen' => $k->nama_komponen,
                        'jenis'         => $k->jenis,
                        'tipe_nilai'    => $k->tipe_nilai,
                        'nilai'         => $nilai,
                        'jumlah_hari'   => $jumlah_hari,
                        'sumber'        => $sumber,
                    ];
                }

                $gaji_bruto = $gaji_pokok + $total_tunjangan + $nominal_lembur;
                $gaji_neto  = $gaji_bruto - $total_potongan;

                $this->db->insert($this->table_payroll, [
                    'id_periode'      => (int) $id_periode,
                    'id_pegawai'      => (int) $pg->id_pegawai,
                    'gaji_pokok'      => $gaji_pokok,
                    'total_tunjangan' => $total_tunjangan,
                    'total_potongan'  => $total_potongan,
                    'total_lembur'    => $nominal_lembur,
                    'gaji_bruto'      => $gaji_bruto,
                    'gaji_neto'       => $gaji_neto,
                    'status'          => 'PROCESSED',
                ]);
                $id_payroll = $this->db->insert_id();

                foreach ($detail_items as $item) {
                    $this->db->insert($this->table_detail, [
                        'id_payroll'    => (int) $id_payroll,
                        'id_komponen'   => $item['id_komponen'],
                        'nama_komponen' => $item['nama_komponen'],
                        'jenis'         => $item['jenis'],
                        'tipe_nilai'    => $item['tipe_nilai'],
                        'nilai'         => $item['nilai'],
                        'jumlah_hari'   => $item['jumlah_hari'],
                        'sumber'        => $item['sumber'],
                    ]);
                }

                if ($nominal_lembur > 0) {
                    $this->db->insert($this->table_detail, [
                        'id_payroll'    => (int) $id_payroll,
                        'id_komponen'   => null,
                        'nama_komponen' => 'Upah Lembur',
                        'jenis'         => 'TUNJANGAN',
                        'tipe_nilai'    => 'NOMINAL',
                        'nilai'         => $nominal_lembur,
                        'jumlah_hari'   => null,
                        'sumber'        => 'LEMBUR_APPROVED',
                    ]);
                }

                $processed++;
            }
        } catch (RuntimeException $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal memproses payroll: ' . $e->getMessage()];
        }

        $this->update_periode_status($id_periode, 'PROCESSED', $id_user);
        $this->update_periode_process_mode($id_periode, $mode);

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal memproses payroll'];
        }

        $this->db->trans_commit();
        return ['success' => true, 'message' => 'Payroll berhasil diproses', 'processed' => $processed];
    }

    /**
     * Deteksi ALPHA: untuk setiap pegawai, iterasi tiap hari dalam periode.
    * Jika hari adalah hari kerja (per jadwal efektif) dan belum ada record absensi apapun,
     * sisipkan record ALPHA dengan sumber='AUTO' ke tb_absensi.
     *
    * Hari kerja ditentukan dari jadwal efektif pegawai/divisi pada tanggal tersebut,
    * lalu tetap menghormati hari libur nasional (tb_hari_libur).
     * Record ALPHA AUTO yang sudah ada tidak akan di-duplicate.
     */
    private function _inject_alpha_records($tanggal_mulai, $tanggal_selesai, array $pegawai)
    {
        if (empty($pegawai)) return;

        // Load hari libur nasional dalam rentang
        $holidays_rows = $this->db
            ->where('tanggal >=', $tanggal_mulai)
            ->where('tanggal <=', $tanggal_selesai)
            ->get('tb_hari_libur')->result();
        $holidays = [];
        foreach ($holidays_rows as $h) {
            $holidays[$h->tanggal] = true;
        }

        // Load Shift_model jika belum
        if (!isset($this->Shift_model)) {
            $this->load->model('Shift_model');
        }

        // Ambil semua record absensi dalam periode sekaligus (efisien)
        $existing_rows = $this->db
            ->select('id_pegawai, tanggal')
            ->from('tb_absensi')
            ->where('tanggal >=', $tanggal_mulai)
            ->where('tanggal <=', $tanggal_selesai)
            ->get()->result();

        // Build lookup: "id_pegawai_tanggal" => true
        $existing_map = [];
        foreach ($existing_rows as $r) {
            $existing_map[(int) $r->id_pegawai . '_' . $r->tanggal] = true;
        }

        // Iterate setiap hari dalam periode
        $start = new DateTime($tanggal_mulai);
        $end   = new DateTime($tanggal_selesai);
        $end->modify('+1 day');
        $interval = new DateInterval('P1D');
        $period   = new DatePeriod($start, $interval, $end);

        $batch_insert = [];

        foreach ($pegawai as $pg) {
            $id_pegawai = (int) $pg->id_pegawai;
            $id_divisi  = isset($pg->id_divisi) ? (int) $pg->id_divisi : 0;

            foreach ($period as $dt) {
                $tanggal = $dt->format('Y-m-d');
                // Skip hari libur nasional
                if (isset($holidays[$tanggal])) continue;

                // Lookup jadwal efektif pegawai pada tanggal ini
                $schedule = $this->Shift_model->get_schedule_for_pegawai($id_pegawai, $id_divisi, $tanggal);
                if (!$schedule || (int) $schedule->is_hari_kerja !== 1) continue;

                // Jika sudah ada record absensi apapun, skip
                $key = $id_pegawai . '_' . $tanggal;
                if (isset($existing_map[$key])) continue;

                // Belum ada absensi => ALPHA
                $batch_insert[] = [
                    'id_pegawai'  => $id_pegawai,
                    'tanggal'     => $tanggal,
                    'status'      => 'ALPHA',
                    'keterangan'  => 'Auto-deteksi saat run payroll',
                    'sumber'      => 'AUTO',
                    'is_terlambat' => 0,
                    'menit_terlambat' => 0,
                    'created_at'  => date('Y-m-d H:i:s'),
                ];
                // Tandai supaya tidak di-insert duplikat dalam loop ini
                $existing_map[$key] = true;
            }
        }

        // Batch insert semua ALPHA sekaligus
        if (!empty($batch_insert)) {
            $this->db->insert_batch('tb_absensi', $batch_insert);
        }
    }

    /**
     * Ringkasan absensi seluruh pegawai dalam rentang tanggal.
     * Returns: [id_pegawai => ['MASUK' => 22, 'ALPHA' => 1, ...]]
     */
    private function _get_absensi_summary($tanggal_mulai, $tanggal_selesai)
    {
        $rows = $this->db
            ->select('id_pegawai, status, COUNT(*) as jumlah', false)
            ->from('tb_absensi')
            ->where('tanggal >=', $tanggal_mulai)
            ->where('tanggal <=', $tanggal_selesai)
            ->group_by('id_pegawai, status')
            ->get()->result();

        $map = [];
        foreach ($rows as $r) {
            $map[(int) $r->id_pegawai][$r->status] = (int) $r->jumlah;
        }
        return $map;
    }

    /**
     * Count holidays (hari libur) in the given period from tb_hari_libur.
     */
    private function _count_holidays_in_period($tanggal_mulai, $tanggal_selesai)
    {
        $row = $this->db->select('COUNT(*) as jumlah', false)
                        ->from('tb_hari_libur')
                        ->where('tanggal >=', $tanggal_mulai)
                        ->where('tanggal <=', $tanggal_selesai)
                        ->get()->row();
        return $row ? (int) $row->jumlah : 0;
    }

    /**
     * Load semua rules kehadiran komponen.
     * Returns: [id_komponen => ['MASUK', 'TERLAMBAT', ...]]
     */
    private function _get_komponen_rules_map()
    {
        $rows = $this->db->get('tb_komponen_kehadiran_rule')->result();
        $map = [];
        foreach ($rows as $r) {
            $map[(int) $r->id_komponen][] = $r->status_absensi;
        }
        return $map;
    }

    private function _build_formula_context($pegawai, array $pg_absensi, $gaji_pokok, $jam_lembur, $nominal_lembur, $periode)
    {
        $masa_kerja_bulan = 0;
        if (!empty($pegawai->tanggal_masuk) && !empty($periode->tanggal_selesai)) {
            try {
                $start = new DateTime($pegawai->tanggal_masuk);
                $end = new DateTime($periode->tanggal_selesai);
                $diff = $start->diff($end);
                $masa_kerja_bulan = max(0, ($diff->y * 12) + $diff->m + (($diff->d > 0 || $diff->invert === 0) ? 0 : 0));
            } catch (Exception $e) {
                $masa_kerja_bulan = 0;
            }
        }

        // Get actual holiday count from tb_hari_libur instead of relying on manual LIBUR records
        $hari_libur_calendar = $this->_count_holidays_in_period($periode->tanggal_mulai, $periode->tanggal_selesai);

        return [
            'GAPOK'              => (float) $gaji_pokok,
            'GAJI_POKOK'         => (float) $gaji_pokok,
            'LEMBUR'             => (float) $nominal_lembur,
            'NILAI_LEMBUR'       => (float) $nominal_lembur,
            'JAM_LEMBUR'         => (float) $jam_lembur,
            'MASA_KERJA_BULAN'   => (float) $masa_kerja_bulan,
            'HARI_MASUK'         => (float) ($pg_absensi['MASUK'] ?? 0),
            'HARI_TERLAMBAT'     => (float) ($pg_absensi['TERLAMBAT'] ?? 0),
            'HARI_SAKIT'         => (float) ($pg_absensi['SAKIT'] ?? 0),
            'HARI_CUTI'          => (float) ($pg_absensi['CUTI'] ?? 0),
            'HARI_IZIN'          => (float) ($pg_absensi['IZIN'] ?? 0),
            'HARI_ALPHA'         => (float) ($pg_absensi['ALPHA'] ?? 0),
            'HARI_LIBUR'         => (float) $hari_libur_calendar,
            'SUBTOTAL_TUNJANGAN' => 0.0,
            'SUBTOTAL_POTONGAN'  => 0.0,
            'BRUTO_SEMENTARA'    => (float) ($gaji_pokok + $nominal_lembur),
        ];
    }

    public function preview_formula_expression($expression, array $context = [])
    {
        $defaults = [
            'GAPOK'              => 0.0,
            'GAJI_POKOK'         => 0.0,
            'LEMBUR'             => 0.0,
            'NILAI_LEMBUR'       => 0.0,
            'JAM_LEMBUR'         => 0.0,
            'MASA_KERJA_BULAN'   => 0.0,
            'HARI_MASUK'         => 0.0,
            'HARI_TERLAMBAT'     => 0.0,
            'HARI_SAKIT'         => 0.0,
            'HARI_CUTI'          => 0.0,
            'HARI_IZIN'          => 0.0,
            'HARI_ALPHA'         => 0.0,
            'HARI_LIBUR'         => 0.0,
            'SUBTOTAL_TUNJANGAN' => 0.0,
            'SUBTOTAL_POTONGAN'  => 0.0,
            'BRUTO_SEMENTARA'    => 0.0,
            'TJ_POSISI'          => 0.0,
            'TJ_MAKAN'           => 0.0,
            'TJ_TRANS'           => 0.0,
        ];

        foreach ($context as $key => $value) {
            $defaults[strtoupper((string) $key)] = (float) $value;
        }

        return $this->_evaluate_formula_expression($expression, $defaults);
    }

    private function _evaluate_formula_expression($expression, array $context)
    {
        $expression = strtoupper(trim((string) $expression));
        if ($expression === '') {
            return 0;
        }

        if (preg_match('/[^A-Z0-9_\s\+\-\*\/\(\)\.,<>=!]/', $expression)) {
            throw new RuntimeException('Rumus komponen mengandung karakter yang tidak diizinkan.');
        }

        $tokens = $this->_tokenize_formula($expression);
        $index = 0;
        $value = $this->_parse_formula_comparison($tokens, $index, $context);

        if ($index !== count($tokens)) {
            throw new RuntimeException('Rumus komponen tidak valid di dekat token: ' . $tokens[$index]['value']);
        }

        return (float) $value;
    }

    private function _tokenize_formula($expression)
    {
        $pattern = '/\s*(>=|<=|==|!=|>|<|\+|\-|\*|\/|\(|\)|,|[A-Z_][A-Z0-9_]*|\d+(?:\.\d+)?)/';
        preg_match_all($pattern, $expression, $matches);
        $rawTokens = $matches[1];
        $joined = preg_replace('/\s+/', '', $expression);
        if (implode('', $rawTokens) !== $joined) {
            throw new RuntimeException('Rumus komponen tidak dapat diparse.');
        }

        $tokens = [];
        foreach ($rawTokens as $raw) {
            $type = 'operator';
            if (preg_match('/^\d+(?:\.\d+)?$/', $raw)) {
                $type = 'number';
            } elseif (preg_match('/^[A-Z_][A-Z0-9_]*$/', $raw)) {
                $type = 'identifier';
            }
            $tokens[] = ['type' => $type, 'value' => $raw];
        }
        return $tokens;
    }

    private function _parse_formula_comparison(array $tokens, &$index, array $context)
    {
        $left = $this->_parse_formula_additive($tokens, $index, $context);
        while ($index < count($tokens) && in_array($tokens[$index]['value'], ['>', '<', '>=', '<=', '==', '!='], true)) {
            $op = $tokens[$index]['value'];
            $index++;
            $right = $this->_parse_formula_additive($tokens, $index, $context);
            switch ($op) {
                case '>':  $left = $left > $right ? 1 : 0; break;
                case '<':  $left = $left < $right ? 1 : 0; break;
                case '>=': $left = $left >= $right ? 1 : 0; break;
                case '<=': $left = $left <= $right ? 1 : 0; break;
                case '==': $left = abs($left - $right) < 0.0000001 ? 1 : 0; break;
                case '!=': $left = abs($left - $right) >= 0.0000001 ? 1 : 0; break;
            }
        }
        return $left;
    }

    private function _parse_formula_additive(array $tokens, &$index, array $context)
    {
        $left = $this->_parse_formula_multiplicative($tokens, $index, $context);
        while ($index < count($tokens) && in_array($tokens[$index]['value'], ['+', '-'], true)) {
            $op = $tokens[$index]['value'];
            $index++;
            $right = $this->_parse_formula_multiplicative($tokens, $index, $context);
            $left = $op === '+' ? $left + $right : $left - $right;
        }
        return $left;
    }

    private function _parse_formula_multiplicative(array $tokens, &$index, array $context)
    {
        $left = $this->_parse_formula_unary($tokens, $index, $context);
        while ($index < count($tokens) && in_array($tokens[$index]['value'], ['*', '/'], true)) {
            $op = $tokens[$index]['value'];
            $index++;
            $right = $this->_parse_formula_unary($tokens, $index, $context);
            $left = $op === '*' ? $left * $right : ($right == 0 ? 0 : $left / $right);
        }
        return $left;
    }

    private function _parse_formula_unary(array $tokens, &$index, array $context)
    {
        if ($index < count($tokens) && in_array($tokens[$index]['value'], ['+', '-'], true)) {
            $op = $tokens[$index]['value'];
            $index++;
            $value = $this->_parse_formula_unary($tokens, $index, $context);
            return $op === '-' ? -$value : $value;
        }
        return $this->_parse_formula_primary($tokens, $index, $context);
    }

    private function _parse_formula_primary(array $tokens, &$index, array $context)
    {
        if ($index >= count($tokens)) {
            throw new RuntimeException('Rumus komponen tidak lengkap.');
        }

        $token = $tokens[$index];
        if ($token['type'] === 'number') {
            $index++;
            return (float) $token['value'];
        }

        if ($token['value'] === '(') {
            $index++;
            $value = $this->_parse_formula_comparison($tokens, $index, $context);
            if ($index >= count($tokens) || $tokens[$index]['value'] !== ')') {
                throw new RuntimeException('Tanda kurung penutup rumus tidak ditemukan.');
            }
            $index++;
            return $value;
        }

        if ($token['type'] === 'identifier') {
            $name = $token['value'];
            $index++;
            if ($index < count($tokens) && $tokens[$index]['value'] === '(') {
                $index++;
                $args = [];
                if ($index < count($tokens) && $tokens[$index]['value'] !== ')') {
                    while (true) {
                        $args[] = $this->_parse_formula_comparison($tokens, $index, $context);
                        if ($index < count($tokens) && $tokens[$index]['value'] === ',') {
                            $index++;
                            continue;
                        }
                        break;
                    }
                }
                if ($index >= count($tokens) || $tokens[$index]['value'] !== ')') {
                    throw new RuntimeException('Penutup fungsi rumus tidak ditemukan.');
                }
                $index++;
                return $this->_call_formula_function($name, $args);
            }
            if (!array_key_exists($name, $context)) {
                throw new RuntimeException('Variabel rumus tidak dikenal: ' . $name);
            }
            return (float) $context[$name];
        }

        throw new RuntimeException('Token rumus tidak dikenali: ' . $token['value']);
    }

    private function _call_formula_function($name, array $args)
    {
        switch ($name) {
            case 'MIN':
                return empty($args) ? 0 : min($args);
            case 'MAX':
                return empty($args) ? 0 : max($args);
            case 'ROUND':
                return round((float) ($args[0] ?? 0), (int) ($args[1] ?? 0));
            case 'ABS':
                return abs((float) ($args[0] ?? 0));
            case 'IF':
                return !empty($args[0]) ? (float) ($args[1] ?? 0) : (float) ($args[2] ?? 0);
            default:
                throw new RuntimeException('Fungsi rumus tidak dikenali: ' . $name);
        }
    }

    public function has_locked_or_approved_period($start_date, $end_date)
    {
        return $this->db
            ->from($this->table_periode)
            ->where_in('status', ['APPROVED', 'LOCKED'])
            ->where('tanggal_mulai <=', $end_date)
            ->where('tanggal_selesai >=', $start_date)
            ->count_all_results() > 0;
    }

    public function get_payroll_single($id_payroll)
    {
        $select_proses_mode = $this->_has_proses_mode_column()
            ? 'pp.proses_mode'
            : 'NULL as proses_mode';

        $row = $this->db
            ->select('p.*, pg.nip, pg.nama_lengkap, pg.nama_bank, pg.no_rekening, pg.npwp, d.nama_divisi, j.nama_jabatan, pp.bulan, pp.tahun, pp.tanggal_mulai, pp.tanggal_selesai, pp.catatan, ' . $select_proses_mode . ',
                                            COALESCE(absensi.hari_hadir, 0) as hari_hadir,
                                            COALESCE(absensi.hari_absen, 0) as hari_absen,
                                            COALESCE(lembur.total_jam_lembur, 0) as total_jam_lembur', false)
            ->from($this->table_payroll . ' p')
            ->join('tb_pegawai pg', 'pg.id_pegawai = p.id_pegawai', 'left')
            ->join('tb_divisi d', 'd.id_divisi = pg.id_divisi', 'left')
            ->join('tb_jabatan j', 'j.id_jabatan = pg.id_jabatan', 'left')
            ->join($this->table_periode . ' pp', 'pp.id_periode = p.id_periode', 'left')
                        ->join(
                                '(SELECT p2.id_payroll,
                                                 SUM(CASE WHEN a.status = "MASUK" THEN 1 ELSE 0 END) as hari_hadir,
                                                 SUM(CASE WHEN a.status IN ("ALPHA", "IZIN", "SAKIT", "CUTI") THEN 1 ELSE 0 END) as hari_absen
                                    FROM tb_payroll p2
                                    JOIN tb_payroll_periode pp2 ON pp2.id_periode = p2.id_periode
                                    LEFT JOIN tb_absensi a ON a.id_pegawai = p2.id_pegawai
                                        AND a.tanggal >= pp2.tanggal_mulai
                                        AND a.tanggal <= pp2.tanggal_selesai
                                    GROUP BY p2.id_payroll) absensi',
                                'absensi.id_payroll = p.id_payroll',
                                'left'
                        )
                        ->join(
                                '(SELECT p2.id_payroll,
                                                 SUM(CASE WHEN l.status = "APPROVED" THEN l.durasi_jam ELSE 0 END) as total_jam_lembur
                                    FROM tb_payroll p2
                                    JOIN tb_payroll_periode pp2 ON pp2.id_periode = p2.id_periode
                                    LEFT JOIN tb_lembur l ON l.id_pegawai = p2.id_pegawai
                                        AND l.tanggal >= pp2.tanggal_mulai
                                        AND l.tanggal <= pp2.tanggal_selesai
                                        AND l.status = "APPROVED"
                                    GROUP BY p2.id_payroll) lembur',
                                'lembur.id_payroll = p.id_payroll',
                                'left'
                        )
            ->where('p.id_payroll', (int) $id_payroll)
            ->get()->row();

        if ($row) {
            $row->proses_mode = $this->_resolve_periode_process_mode($row);
            $this->_apply_tanpa_absensi_attendance($row);
        }

        return $row;
    }

    public function update_periode_process_mode($id_periode, $mode)
    {
        $normalized = strtolower((string) $mode) === 'tanpa_absensi' ? 'tanpa_absensi' : 'absensi';

        if ($this->_has_proses_mode_column()) {
            return $this->db
                ->where('id_periode', (int) $id_periode)
                ->update($this->table_periode, ['proses_mode' => $normalized]);
        }

        $periode = $this->db->where('id_periode', (int) $id_periode)->get($this->table_periode)->row();
        if (!$periode) {
            return false;
        }

        $catatan = (string) ($periode->catatan ?? '');
        $catatan = preg_replace('/\[MODE:(ABSENSI|TANPA_ABSENSI)\]\s*/i', '', $catatan);
        $token = '[MODE:' . strtoupper($normalized) . ']';
        $new_catatan = trim($token . ' ' . trim($catatan));

        return $this->db
            ->where('id_periode', (int) $id_periode)
            ->update($this->table_periode, ['catatan' => $new_catatan]);
    }

    private function _has_proses_mode_column()
    {
        if ($this->has_proses_mode_column !== null) {
            return (bool) $this->has_proses_mode_column;
        }

        $this->has_proses_mode_column = $this->db->field_exists('proses_mode', $this->table_periode);
        return (bool) $this->has_proses_mode_column;
    }

    private function _resolve_periode_process_mode($periode_row)
    {
        if ($this->_has_proses_mode_column() && !empty($periode_row->proses_mode)) {
            $mode = strtolower((string) $periode_row->proses_mode);
            return $mode === 'tanpa_absensi' ? 'tanpa_absensi' : 'absensi';
        }

        $catatan = (string) ($periode_row->catatan ?? '');
        if (preg_match('/\[MODE:(ABSENSI|TANPA_ABSENSI)\]/i', $catatan, $matches)) {
            $mode = strtolower((string) $matches[1]);
            return $mode === 'tanpa_absensi' ? 'tanpa_absensi' : 'absensi';
        }

        if (isset($periode_row->status) && in_array((string) $periode_row->status, ['PROCESSED', 'APPROVED', 'LOCKED'], true)) {
            return 'absensi';
        }

        return '';
    }

    private function _apply_tanpa_absensi_attendance($row)
    {
        if (!$row) {
            return;
        }

        $mode = strtolower((string) ($row->proses_mode ?? ''));
        if ($mode !== 'tanpa_absensi') {
            return;
        }

        if (empty($row->tanggal_mulai) || empty($row->tanggal_selesai)) {
            return;
        }

        $holiday_map = $this->_get_holiday_map($row->tanggal_mulai, $row->tanggal_selesai);
        $hari_hadir = $this->_count_working_days_by_schedule(
            (int) ($row->id_pegawai ?? 0),
            (int) ($row->id_divisi ?? 0),
            (string) $row->tanggal_mulai,
            (string) $row->tanggal_selesai,
            $holiday_map
        );

        $row->hari_hadir = $hari_hadir;
        $row->hari_absen = 0;
    }

    private function _get_holiday_map($tanggal_mulai, $tanggal_selesai)
    {
        $rows = $this->db
            ->select('tanggal')
            ->from('tb_hari_libur')
            ->where('tanggal >=', $tanggal_mulai)
            ->where('tanggal <=', $tanggal_selesai)
            ->get()->result();

        $map = [];
        foreach ($rows as $row) {
            $map[$row->tanggal] = true;
        }

        return $map;
    }

    private function _count_working_days_by_schedule($id_pegawai, $id_divisi, $tanggal_mulai, $tanggal_selesai, array $holiday_map = [])
    {
        if ($id_pegawai <= 0 || empty($tanggal_mulai) || empty($tanggal_selesai)) {
            return 0;
        }

        if (!isset($this->Shift_model)) {
            $this->load->model('Shift_model');
        }

        $start = new DateTime($tanggal_mulai);
        $end = new DateTime($tanggal_selesai);
        $end->modify('+1 day');
        $interval = new DateInterval('P1D');
        $period = new DatePeriod($start, $interval, $end);

        $count = 0;
        foreach ($period as $dt) {
            $tanggal = $dt->format('Y-m-d');
            if (isset($holiday_map[$tanggal])) {
                continue;
            }

            $schedule = $this->Shift_model->get_schedule_for_pegawai((int) $id_pegawai, (int) $id_divisi, $tanggal);
            if ($schedule && (int) $schedule->is_hari_kerja === 1) {
                $count++;
            }
        }

        return $count;
    }

    public function get_komponen_by_payroll($id_payroll)
    {
        return $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->order_by('jenis', 'ASC')
            ->order_by('nama_komponen', 'ASC')
            ->get($this->table_detail)->result();
    }

    public function is_adjustment_feature_ready()
    {
        if ($this->adjustment_feature_ready !== null) {
            return (bool) $this->adjustment_feature_ready;
        }

        $has_adjustment = $this->db->table_exists($this->table_adjustment);
        $has_audit = $this->db->table_exists($this->table_adjustment_audit);
        $this->adjustment_feature_ready = ($has_adjustment && $has_audit);

        return (bool) $this->adjustment_feature_ready;
    }

    public function get_adjustment_by_id($id_adjustment)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return null;
        }

        return $this->db
            ->where('id_adjustment', (int) $id_adjustment)
            ->where('is_active', 1)
            ->get($this->table_adjustment)
            ->row();
    }

    public function get_active_adjustments($id_periode, $id_payroll)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return [];
        }

        return $this->db
            ->where('id_periode', (int) $id_periode)
            ->where('id_payroll', (int) $id_payroll)
            ->where('is_active', 1)
            ->order_by('jenis', 'ASC')
            ->order_by('nama_komponen', 'ASC')
            ->get($this->table_adjustment)
            ->result();
    }

    public function get_komponen_modal_by_payroll($id_periode, $id_payroll)
    {
        $system_rows = $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->where('sumber !=', 'CUSTOM_MANUAL')
            ->order_by('jenis', 'ASC')
            ->order_by('nama_komponen', 'ASC')
            ->get($this->table_detail)
            ->result();

        $custom_rows = $this->is_adjustment_feature_ready()
            ? $this->get_active_adjustments($id_periode, $id_payroll)
            : [];

        $result = [];
        foreach ($system_rows as $row) {
            $result[] = [
                'id_adjustment' => null,
                'jenis' => (string) $row->jenis,
                'nama_komponen' => (string) $row->nama_komponen,
                'tipe_nilai' => (string) $row->tipe_nilai,
                'jumlah_hari' => $row->jumlah_hari !== null ? (int) $row->jumlah_hari : null,
                'sumber' => (string) $row->sumber,
                'nilai' => (float) $row->nilai,
                'catatan' => null,
                'is_custom' => false,
                'is_editable' => false,
            ];
        }

        foreach ($custom_rows as $row) {
            $result[] = [
                'id_adjustment' => (int) $row->id_adjustment,
                'jenis' => (string) $row->jenis,
                'nama_komponen' => (string) $row->nama_komponen,
                'tipe_nilai' => 'NOMINAL',
                'jumlah_hari' => null,
                'sumber' => 'CUSTOM_MANUAL',
                'nilai' => (float) $row->nilai,
                'catatan' => (string) ($row->catatan ?? ''),
                'is_custom' => true,
                'is_editable' => true,
            ];
        }

        usort($result, function ($a, $b) {
            $aJenis = strtoupper((string) ($a['jenis'] ?? ''));
            $bJenis = strtoupper((string) ($b['jenis'] ?? ''));
            if ($aJenis === $bJenis) {
                return strcmp((string) $a['nama_komponen'], (string) $b['nama_komponen']);
            }
            return strcmp($aJenis, $bJenis);
        });

        return $result;
    }

    public function save_custom_adjustment(array $payload, $id_user)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return ['success' => false, 'message' => 'Fitur penyesuaian belum siap. Jalankan migration payroll adjustment terlebih dahulu.'];
        }

        $id_adjustment = isset($payload['id_adjustment']) ? (int) $payload['id_adjustment'] : 0;
        $id_periode = (int) ($payload['id_periode'] ?? 0);
        $id_payroll = (int) ($payload['id_payroll'] ?? 0);
        $id_pegawai = (int) ($payload['id_pegawai'] ?? 0);
        $nama_komponen = trim((string) ($payload['nama_komponen'] ?? ''));
        $jenis = strtoupper(trim((string) ($payload['jenis'] ?? '')));
        $nilai = round((float) ($payload['nilai'] ?? 0), 2);
        $catatan = trim((string) ($payload['catatan'] ?? ''));

        if ($nama_komponen === '' || strlen($nama_komponen) < 3) {
            return ['success' => false, 'message' => 'Nama komponen minimal 3 karakter'];
        }
        if (!in_array($jenis, ['TUNJANGAN', 'POTONGAN'], true)) {
            return ['success' => false, 'message' => 'Jenis komponen tidak valid'];
        }
        if ($nilai < 0) {
            return ['success' => false, 'message' => 'Nilai komponen tidak boleh negatif'];
        }

        $this->db->trans_begin();

        $before_row = null;
        if ($id_adjustment > 0) {
            $before_row = $this->get_adjustment_by_id($id_adjustment);
            if (!$before_row) {
                $this->db->trans_rollback();
                return ['success' => false, 'message' => 'Data penyesuaian tidak ditemukan'];
            }
            if ((int) $before_row->id_periode !== $id_periode || (int) $before_row->id_payroll !== $id_payroll || (int) $before_row->id_pegawai !== $id_pegawai) {
                $this->db->trans_rollback();
                return ['success' => false, 'message' => 'Data penyesuaian tidak valid'];
            }
        }

        $dup = $this->db
            ->from($this->table_adjustment)
            ->where('id_periode', $id_periode)
            ->where('id_payroll', $id_payroll)
            ->where('is_active', 1)
            ->where('jenis', $jenis)
            ->where('id_adjustment !=', $id_adjustment)
            ->where('LOWER(nama_komponen) = ' . $this->db->escape(strtolower($nama_komponen)), null, false)
            ->count_all_results();
        if ($dup > 0) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Komponen custom dengan nama yang sama sudah ada'];
        }

        $now = date('Y-m-d H:i:s');
        if ($id_adjustment > 0) {
            $this->db
                ->where('id_adjustment', $id_adjustment)
                ->update($this->table_adjustment, [
                    'nama_komponen' => $nama_komponen,
                    'jenis' => $jenis,
                    'nilai' => $nilai,
                    'catatan' => $catatan,
                    'updated_by' => (int) $id_user,
                    'updated_at' => $now,
                ]);
        } else {
            $this->db->insert($this->table_adjustment, [
                'id_periode' => $id_periode,
                'id_payroll' => $id_payroll,
                'id_pegawai' => $id_pegawai,
                'nama_komponen' => $nama_komponen,
                'jenis' => $jenis,
                'tipe_nilai' => 'NOMINAL',
                'nilai' => $nilai,
                'catatan' => $catatan,
                'is_active' => 1,
                'created_by' => (int) $id_user,
                'created_at' => $now,
                'updated_by' => (int) $id_user,
                'updated_at' => $now,
            ]);
            $id_adjustment = (int) $this->db->insert_id();
        }

        $after_row = $this->get_adjustment_by_id($id_adjustment);
        $this->_insert_adjustment_audit([
            'id_adjustment' => $id_adjustment,
            'id_periode' => $id_periode,
            'id_payroll' => $id_payroll,
            'id_pegawai' => $id_pegawai,
            'aksi' => $before_row ? 'UPDATE' : 'CREATE',
            'before_payload' => $before_row ? json_encode($before_row) : null,
            'after_payload' => $after_row ? json_encode($after_row) : null,
            'performed_by' => (int) $id_user,
        ]);

        $recalc = $this->recalculate_payroll_with_adjustment((int) $id_periode, (int) $id_payroll, (int) $id_user);
        if (!$recalc['success']) {
            $this->db->trans_rollback();
            return $recalc;
        }

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal menyimpan penyesuaian'];
        }

        $this->db->trans_commit();
        return [
            'success' => true,
            'message' => 'Penyesuaian komponen berhasil disimpan',
            'summary' => $recalc['summary'],
            'komponen' => $recalc['komponen'],
        ];
    }

    public function delete_custom_adjustment($id_adjustment, $id_periode, $id_payroll, $id_pegawai, $id_user)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return ['success' => false, 'message' => 'Fitur penyesuaian belum siap. Jalankan migration payroll adjustment terlebih dahulu.'];
        }

        $row = $this->get_adjustment_by_id((int) $id_adjustment);
        if (!$row) {
            return ['success' => false, 'message' => 'Data penyesuaian tidak ditemukan'];
        }

        if ((int) $row->id_periode !== (int) $id_periode || (int) $row->id_payroll !== (int) $id_payroll || (int) $row->id_pegawai !== (int) $id_pegawai) {
            return ['success' => false, 'message' => 'Data penyesuaian tidak valid'];
        }

        $this->db->trans_begin();

        $this->db
            ->where('id_adjustment', (int) $id_adjustment)
            ->update($this->table_adjustment, [
                'is_active' => 0,
                'updated_by' => (int) $id_user,
                'updated_at' => date('Y-m-d H:i:s'),
            ]);

        $this->_insert_adjustment_audit([
            'id_adjustment' => (int) $id_adjustment,
            'id_periode' => (int) $id_periode,
            'id_payroll' => (int) $id_payroll,
            'id_pegawai' => (int) $id_pegawai,
            'aksi' => 'DELETE',
            'before_payload' => json_encode($row),
            'after_payload' => null,
            'performed_by' => (int) $id_user,
        ]);

        $recalc = $this->recalculate_payroll_with_adjustment((int) $id_periode, (int) $id_payroll, (int) $id_user);
        if (!$recalc['success']) {
            $this->db->trans_rollback();
            return $recalc;
        }

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => 'Gagal menghapus penyesuaian'];
        }

        $this->db->trans_commit();
        return [
            'success' => true,
            'message' => 'Penyesuaian komponen berhasil dihapus',
            'summary' => $recalc['summary'],
            'komponen' => $recalc['komponen'],
        ];
    }

    public function recalculate_payroll_with_adjustment($id_periode, $id_payroll, $id_user)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return ['success' => false, 'message' => 'Fitur penyesuaian belum siap. Jalankan migration payroll adjustment terlebih dahulu.'];
        }

        $payroll = $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->where('id_periode', (int) $id_periode)
            ->get($this->table_payroll)
            ->row();
        if (!$payroll) {
            return ['success' => false, 'message' => 'Data payroll tidak ditemukan'];
        }

        $base_components = $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->where('sumber !=', 'CUSTOM_MANUAL')
            ->get($this->table_detail)
            ->result();

        $custom_components = $this->get_active_adjustments((int) $id_periode, (int) $id_payroll);

        $base_tunjangan = 0.0;
        $base_potongan = 0.0;
        foreach ($base_components as $item) {
            $jenis = strtoupper((string) ($item->jenis ?? ''));
            $sumber = strtoupper((string) ($item->sumber ?? ''));
            $nilai = (float) ($item->nilai ?? 0);
            if ($jenis === 'TUNJANGAN') {
                if ($sumber !== 'LEMBUR_APPROVED') {
                    $base_tunjangan += $nilai;
                }
            } elseif ($jenis === 'POTONGAN') {
                $base_potongan += $nilai;
            }
        }

        $custom_tunjangan = 0.0;
        $custom_potongan = 0.0;
        foreach ($custom_components as $item) {
            $jenis = strtoupper((string) ($item->jenis ?? ''));
            $nilai = (float) ($item->nilai ?? 0);
            if ($jenis === 'TUNJANGAN') {
                $custom_tunjangan += $nilai;
            } elseif ($jenis === 'POTONGAN') {
                $custom_potongan += $nilai;
            }
        }

        $new_total_tunjangan = round($base_tunjangan + $custom_tunjangan, 2);
        $new_total_potongan = round($base_potongan + $custom_potongan, 2);
        $new_total_lembur = round((float) $payroll->total_lembur, 2);
        $new_gaji_bruto = round((float) $payroll->gaji_pokok + $new_total_tunjangan + $new_total_lembur, 2);
        $new_gaji_neto = round($new_gaji_bruto - $new_total_potongan, 2);

        $before_payload = [
            'total_tunjangan' => (float) $payroll->total_tunjangan,
            'total_potongan' => (float) $payroll->total_potongan,
            'gaji_bruto' => (float) $payroll->gaji_bruto,
            'gaji_neto' => (float) $payroll->gaji_neto,
        ];

        $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->update($this->table_payroll, [
                'total_tunjangan' => $new_total_tunjangan,
                'total_potongan' => $new_total_potongan,
                'gaji_bruto' => $new_gaji_bruto,
                'gaji_neto' => $new_gaji_neto,
            ]);

        $this->db
            ->where('id_payroll', (int) $id_payroll)
            ->where('sumber', 'CUSTOM_MANUAL')
            ->delete($this->table_detail);

        foreach ($custom_components as $item) {
            $this->db->insert($this->table_detail, [
                'id_payroll' => (int) $id_payroll,
                'id_komponen' => null,
                'nama_komponen' => (string) $item->nama_komponen,
                'jenis' => (string) $item->jenis,
                'tipe_nilai' => 'NOMINAL',
                'nilai' => (float) $item->nilai,
                'jumlah_hari' => null,
                'sumber' => 'CUSTOM_MANUAL',
            ]);
        }

        $after_payload = [
            'total_tunjangan' => $new_total_tunjangan,
            'total_potongan' => $new_total_potongan,
            'gaji_bruto' => $new_gaji_bruto,
            'gaji_neto' => $new_gaji_neto,
        ];

        $this->_insert_adjustment_audit([
            'id_adjustment' => null,
            'id_periode' => (int) $id_periode,
            'id_payroll' => (int) $id_payroll,
            'id_pegawai' => (int) $payroll->id_pegawai,
            'aksi' => 'RECALCULATE',
            'before_payload' => json_encode($before_payload),
            'after_payload' => json_encode($after_payload),
            'performed_by' => (int) $id_user,
        ]);

        $payroll_latest = $this->get_payroll_single((int) $id_payroll);
        if (!$payroll_latest) {
            return ['success' => false, 'message' => 'Gagal membaca payroll terbaru'];
        }

        return [
            'success' => true,
            'summary' => [
                'gaji_pokok' => (float) $payroll_latest->gaji_pokok,
                'total_tunjangan' => (float) $payroll_latest->total_tunjangan,
                'total_lembur' => (float) $payroll_latest->total_lembur,
                'total_potongan' => (float) $payroll_latest->total_potongan,
                'gaji_neto' => (float) $payroll_latest->gaji_neto,
            ],
            'komponen' => $this->get_komponen_modal_by_payroll((int) $id_periode, (int) $id_payroll),
        ];
    }

    private function _insert_adjustment_audit(array $payload)
    {
        if (!$this->is_adjustment_feature_ready()) {
            return false;
        }

        $this->db->insert($this->table_adjustment_audit, [
            'id_adjustment' => isset($payload['id_adjustment']) && $payload['id_adjustment'] !== null ? (int) $payload['id_adjustment'] : null,
            'id_periode' => (int) $payload['id_periode'],
            'id_payroll' => (int) $payload['id_payroll'],
            'id_pegawai' => (int) $payload['id_pegawai'],
            'aksi' => (string) $payload['aksi'],
            'before_payload' => $payload['before_payload'] ?? null,
            'after_payload' => $payload['after_payload'] ?? null,
            'performed_by' => isset($payload['performed_by']) ? (int) $payload['performed_by'] : null,
            'performed_at' => date('Y-m-d H:i:s'),
        ]);

        return true;
    }

    public function get_payroll_arsip($id_periode = null, $id_pegawai = null, $tahun = null, $bulan = null, $tipe_periode = null)
    {
        $this->db
            ->select('p.id_payroll, p.gaji_bruto, p.total_potongan, p.gaji_neto, p.id_periode, p.id_pegawai, pg.nip, pg.nama_lengkap, d.nama_divisi, pp.bulan, pp.tahun, pp.tanggal_mulai, pp.tanggal_selesai, pp.tipe_periode, pp.status', false)
            ->from($this->table_payroll . ' p')
            ->join('tb_pegawai pg', 'pg.id_pegawai = p.id_pegawai', 'left')
            ->join('tb_divisi d', 'd.id_divisi = pg.id_divisi', 'left')
            ->join($this->table_periode . ' pp', 'pp.id_periode = p.id_periode', 'left')
            ->where_in('pp.status', ['APPROVED', 'LOCKED']);

        if ($id_periode) {
            $this->db->where('p.id_periode', (int) $id_periode);
        }
        if ($id_pegawai) {
            $this->db->where('p.id_pegawai', (int) $id_pegawai);
        }
        if ($tahun) {
            $this->db->where('pp.tahun', (int) $tahun);
        }
        if ($bulan) {
            $this->db->where('pp.bulan', (int) $bulan);
        }
        if ($tipe_periode) {
            $this->db->where('pp.tipe_periode', $tipe_periode);
        }

        return $this->db
            ->order_by('pp.tahun', 'DESC')
            ->order_by('pp.bulan', 'DESC')
            ->order_by('pp.tipe_periode', 'ASC')
            ->order_by('pg.nama_lengkap', 'ASC')
            ->get()->result();
    }

    private function _is_komponen_applicable_for_pegawai($komponen, $pegawai)
    {
        // Berlaku untuk semua komponen yang dipilih di mapping.
        // Validasi kelengkapan data (mis. no BPJS) ditangani di layer input/proses bisnis terpisah.
        return true;
    }

    private function _get_pegawai_komponen_map($id_pegawai)
    {
        $rows = $this->db
            ->select('id_komponen, is_active, nilai_override')
            ->where('id_pegawai', (int) $id_pegawai)
            ->get('tb_pegawai_komponen')
            ->result();

        $map = [];
        foreach ($rows as $r) {
            $map[(int) $r->id_komponen] = $r;
        }
        return $map;
    }
}
