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

class Pegawai_model extends CI_Model
{
    const TABLE = 'tb_pegawai';

    // -------------------------------------------------------
    // Queries
    // -------------------------------------------------------

    public function get_all_with_join()
    {
        return $this->db
            ->select('p.*, d.nama_divisi, j.nama_jabatan, j.level_jabatan')
            ->from(self::TABLE . ' p')
            ->join('tb_divisi d',  'd.id_divisi  = p.id_divisi',  'left')
            ->join('tb_jabatan j', 'j.id_jabatan = p.id_jabatan', 'left')
            ->order_by('p.nama_lengkap', 'ASC')
            ->get()->result();
    }

    public function get_all($aktif_only = TRUE)
    {
        $this->db->select('id_pegawai, nip, nama_lengkap, id_divisi, id_jabatan, status_pegawai')
                 ->from(self::TABLE);
        if ($aktif_only) {
            $this->db->where('status_pegawai', 'AKTIF');
        }
        return $this->db->order_by('nama_lengkap', 'ASC')->get()->result();
    }

    public function get_by_id($id)
    {
        return $this->db
            ->select('p.*, d.nama_divisi, j.nama_jabatan')
            ->from(self::TABLE . ' p')
            ->join('tb_divisi d',  'd.id_divisi  = p.id_divisi',  'left')
            ->join('tb_jabatan j', 'j.id_jabatan = p.id_jabatan', 'left')
            ->where('p.id_pegawai', (int) $id)
            ->get()->row();
    }
    public function get_by_nip($nip)
    {
        return $this->db
            ->select('p.*, d.nama_divisi, j.nama_jabatan, j.level_jabatan')
            ->from(self::TABLE . ' p')
            ->join('tb_divisi d',  'd.id_divisi  = p.id_divisi',  'left')
            ->join('tb_jabatan j', 'j.id_jabatan = p.id_jabatan', 'left')
            ->where('p.nip', $nip)
            ->get()->row();
    }
    public function nip_exists($nip, $exclude_id = NULL)
    {
        $this->db->where('nip', $nip);
        if ($exclude_id) {
            $this->db->where('id_pegawai !=', (int) $exclude_id);
        }
        return $this->db->count_all_results(self::TABLE) > 0;
    }

    public function count_aktif()
    {
        return $this->db->where('status_pegawai', 'AKTIF')
                        ->count_all_results(self::TABLE);
    }

    // -------------------------------------------------------
    // DataTables server-side
    // -------------------------------------------------------

    public function datatable_total($filter = [])
    {
        $this->_apply_filter($filter);
        return $this->db->count_all_results();
    }

    public function datatable_data($start, $length, $search, $order_col, $order_dir, $filter = [])
    {
        $cols = ['p.nip', 'p.nama_lengkap', 'd.nama_divisi', 'j.nama_jabatan', 'p.status_pegawai', 'p.tanggal_masuk'];

        $this->db->select('p.id_pegawai, p.nip, p.nama_lengkap, p.status_pegawai, p.tanggal_masuk, p.no_telepon, d.nama_divisi, j.nama_jabatan')
                 ->from(self::TABLE . ' p')
                 ->join('tb_divisi d',  'd.id_divisi  = p.id_divisi',  'left')
                 ->join('tb_jabatan j', 'j.id_jabatan = p.id_jabatan', 'left');

        if (!empty($search)) {
            $this->db->group_start()
                     ->like('p.nip', $search)
                     ->or_like('p.nama_lengkap', $search)
                     ->or_like('d.nama_divisi', $search)
                     ->or_like('j.nama_jabatan', $search)
                     ->group_end();
        }

        $this->_apply_filter($filter, FALSE);

        $order_field = isset($cols[$order_col]) ? $cols[$order_col] : 'p.nama_lengkap';
        $this->db->order_by($order_field, $order_dir === 'desc' ? 'DESC' : 'ASC');
        $this->db->limit((int) $length, (int) $start);

        return $this->db->get()->result();
    }

    private function _apply_filter($filter, $reset_select = TRUE)
    {
        if ($reset_select) {
            $this->db->from(self::TABLE . ' p')
                     ->join('tb_divisi d',  'd.id_divisi  = p.id_divisi',  'left')
                     ->join('tb_jabatan j', 'j.id_jabatan = p.id_jabatan', 'left');
        }
        if (!empty($filter['id_divisi']))      $this->db->where('p.id_divisi', (int) $filter['id_divisi']);
        if (!empty($filter['status_pegawai'])) $this->db->where('p.status_pegawai', $filter['status_pegawai']);
    }

    public function get_komponen_mapping($id_pegawai)
    {
        return $this->db
            ->select('k.id_komponen, k.kode_komponen, k.nama_komponen, k.jenis, k.tipe_nilai, k.cara_hitung, k.nilai_default, k.is_wajib, k.urutan,
                      COALESCE(pk.is_active, IF(k.is_wajib = 1, 1, 0)) as map_active,
                      pk.nilai_override,
                      pk.catatan', false)
            ->from('tb_komponen_gaji k')
            ->join(
                'tb_pegawai_komponen pk',
                'pk.id_komponen = k.id_komponen AND pk.id_pegawai = ' . (int) $id_pegawai,
                'left'
            )
            ->where('k.is_active', 1)
            ->where_not_in('k.kode_komponen', ['GAPOK', 'LEMBUR'])
            ->order_by('k.urutan', 'ASC')
            ->order_by('k.nama_komponen', 'ASC')
            ->get()->result();
    }

    /**
     * Sama dengan get_komponen_mapping() tapi MENYERTAKAN GAPOK.
     * Digunakan oleh halaman Mapping Komponen mandiri.
     */
    public function get_komponen_mapping_full($id_pegawai)
    {
        return $this->db
            ->select('k.id_komponen, k.kode_komponen, k.nama_komponen, k.jenis, k.tipe_nilai, k.cara_hitung, k.nilai_default, k.is_wajib, k.urutan,
                      COALESCE(pk.is_active, IF(k.is_wajib = 1, 1, 0)) as map_active,
                      pk.nilai_override,
                      pk.catatan', false)
            ->from('tb_komponen_gaji k')
            ->join(
                'tb_pegawai_komponen pk',
                'pk.id_komponen = k.id_komponen AND pk.id_pegawai = ' . (int) $id_pegawai,
                'left'
            )
            ->where('k.is_active', 1)
            ->where('k.kode_komponen !=', 'LEMBUR')
            ->order_by('k.urutan', 'ASC')
            ->order_by('k.nama_komponen', 'ASC')
            ->get()->result();
    }

    public function save_komponen_mapping($id_pegawai, $items, $id_user = null)
    {
        $this->db->trans_begin();

        $this->db->where('id_pegawai', (int) $id_pegawai)->delete('tb_pegawai_komponen');

        $now = date('Y-m-d H:i:s');
        foreach ($items as $it) {
            $this->db->insert('tb_pegawai_komponen', [
                'id_pegawai'  => (int) $id_pegawai,
                'id_komponen' => (int) $it['id_komponen'],
                'is_active'   => !empty($it['is_active']) ? 1 : 0,
                'nilai_override' => isset($it['nilai_override']) && $it['nilai_override'] !== null
                    ? (float) $it['nilai_override']
                    : null,
                'catatan'     => !empty($it['catatan']) ? $it['catatan'] : null,
                'updated_at'  => $now,
                'updated_by'  => $id_user ? (int) $id_user : null,
            ]);
        }

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

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

    // -------------------------------------------------------
    // Write
    // -------------------------------------------------------

    public function insert($data)
    {
        $this->db->insert(self::TABLE, $data);
        return $this->db->insert_id();
    }

    public function update($id, $data)
    {
        $this->db->where('id_pegawai', (int) $id)->update(self::TABLE, $data);
        return $this->db->affected_rows();
    }

    public function set_status($id, $status)
    {
        $allowed = ['AKTIF', 'NONAKTIF', 'CUTI_PANJANG'];
        if (!in_array($status, $allowed)) return FALSE;
        $this->db->where('id_pegawai', (int) $id)
                 ->update(self::TABLE, ['status_pegawai' => $status]);
        return $this->db->affected_rows();
    }

    // -------------------------------------------------------
    // Histori perubahan
    // -------------------------------------------------------

    public function log_histori($id_pegawai, $jenis, $lama, $baru, $keterangan = '', $id_user = NULL)
    {
        $this->db->insert('tb_pegawai_histori', [
            'id_pegawai'      => (int) $id_pegawai,
            'jenis_perubahan' => $jenis,
            'nilai_lama'      => is_array($lama) ? json_encode($lama) : $lama,
            'nilai_baru'      => is_array($baru)  ? json_encode($baru)  : $baru,
            'keterangan'      => $keterangan,
            'id_user'         => $id_user,
            'created_at'      => date('Y-m-d H:i:s'),
        ]);
    }

    public function get_histori($id_pegawai = null)
    {
        $this->db->select('h.*, p.nip, p.nama_lengkap, u.username as dibuat_oleh')
                 ->from('tb_pegawai_histori h')
                 ->join('tb_pegawai p', 'p.id_pegawai = h.id_pegawai', 'left')
                 ->join('tb_users u', 'u.id_user = h.id_user', 'left')
                 ->order_by('h.created_at', 'DESC');

        if (!empty($id_pegawai)) {
            $this->db->where('h.id_pegawai', (int) $id_pegawai);
        }

        return $this->db->get()->result();
    }
}
