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

class Lembur_model extends CI_Model
{
    const TABLE = 'tb_lembur';

    private function _build_datatable_query($tahun, $bulan, $id_divisi = NULL, $status = '', $search = '')
    {
        $this->db->select('l.*, p.nip, p.nama_lengkap, d.nama_divisi')
                 ->from(self::TABLE . ' l')
                 ->join('tb_pegawai p', 'p.id_pegawai = l.id_pegawai', 'inner')
                 ->join('tb_divisi d', 'd.id_divisi = p.id_divisi', 'left')
                 ->where('YEAR(l.tanggal)', (int) $tahun)
                 ->where('MONTH(l.tanggal)', (int) $bulan);

        if (!empty($id_divisi)) {
            $this->db->where('p.id_divisi', (int) $id_divisi);
        }
        if (!empty($status)) {
            $this->db->where('l.status', $status);
        }
        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('l.alasan', $search)
                     ->group_end();
        }
    }

    public function get_all($id_pegawai = NULL, $tahun = NULL, $status = NULL)
    {
        $this->db->from(self::TABLE);
        if ($id_pegawai) {
            $this->db->where('id_pegawai', (int) $id_pegawai);
        }
        if ($tahun) {
            $this->db->where('YEAR(tanggal)', (int) $tahun);
        }
        if ($status) {
            $this->db->where('status', $status);
        }
        return $this->db->order_by('tanggal', 'DESC')
                        ->get()->result();
    }

    public function get_by_id($id)
    {
        return $this->db->select('l.*, p.nip, p.nama_lengkap, p.id_divisi, d.nama_divisi')
                        ->from(self::TABLE . ' l')
                        ->join('tb_pegawai p', 'p.id_pegawai = l.id_pegawai', 'left')
                        ->join('tb_divisi d', 'd.id_divisi = p.id_divisi', 'left')
                        ->where('l.id_lembur', (int) $id)
                        ->get()->row();
    }

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

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

    public function delete($id)
    {
        return $this->db->where('id_lembur', (int) $id)
                        ->delete(self::TABLE);
    }

    public function approve($id, $id_user)
    {
        return $this->db->where('id_lembur', (int) $id)
                        ->update(self::TABLE, [
                            'status'      => 'APPROVED',
                            'approved_by' => $id_user,
                            'approved_at' => date('Y-m-d H:i:s'),
                        ]);
    }

    /**
     * Bulk approve: terima array of id_lembur, approve semuanya sekaligus.
     * Hanya yang masih DRAFT yang akan diupdate.
     */
    public function bulk_approve(array $ids, $id_user)
    {
        if (empty($ids)) return 0;
        $ids = array_map('intval', $ids);
        $this->db->where_in('id_lembur', $ids)
                 ->where('status', 'DRAFT')
                 ->update(self::TABLE, [
                     'status'      => 'APPROVED',
                     'approved_by' => $id_user,
                     'approved_at' => date('Y-m-d H:i:s'),
                 ]);
        return $this->db->affected_rows();
    }

    /**
     * Bulk reject: terima array of id_lembur, reject semuanya sekaligus.
     * Hanya yang masih DRAFT yang akan diupdate.
     */
    public function bulk_reject(array $ids, $catatan = '')
    {
        if (empty($ids)) return 0;
        $ids = array_map('intval', $ids);
        $this->db->where_in('id_lembur', $ids)
                 ->where('status', 'DRAFT')
                 ->update(self::TABLE, [
                     'status'  => 'REJECTED',
                     'catatan' => $catatan ?: 'Bulk reject',
                 ]);
        return $this->db->affected_rows();
    }

    public function reject($id, $catatan = '')
    {
        return $this->db->where('id_lembur', (int) $id)
                        ->update(self::TABLE, [
                            'status'  => 'REJECTED',
                            'catatan' => $catatan,
                        ]);
    }

    public function get_pending()
    {
        return $this->db->where('status', 'DRAFT')
                        ->order_by('tanggal', 'ASC')
                        ->limit(20)
                        ->get(self::TABLE)->result();
    }

    public function get_summary_periode($tahun, $bulan, $id_divisi = NULL)
    {
        $this->db->select('p.id_pegawai, p.nip, p.nama_lengkap, d.nama_divisi, 
                           SUM(l.durasi_jam) as total_jam_lembur,
                           COUNT(l.id_lembur) as jumlah_lembur')
                 ->from('tb_pegawai p')
                 ->join('tb_divisi d', 'd.id_divisi = p.id_divisi', 'left')
                 ->join(self::TABLE . ' l', 'l.id_pegawai = p.id_pegawai AND YEAR(l.tanggal)=' . (int) $tahun . ' AND MONTH(l.tanggal)=' . (int) $bulan . ' AND l.status="APPROVED"', 'left');
        
        if ($id_divisi) {
            $this->db->where('p.id_divisi', (int) $id_divisi);
        }
        
        return $this->db->where('p.status_pegawai', 'AKTIF')
                        ->group_by('p.id_pegawai')
                        ->order_by('d.nama_divisi, p.nama_lengkap')
                        ->get()->result();
    }

    public function datatable_total($tahun, $bulan, $id_divisi = NULL, $status = '')
    {
        $this->_build_datatable_query($tahun, $bulan, $id_divisi, $status, '');
        return $this->db->count_all_results();
    }

    public function datatable_filtered($tahun, $bulan, $id_divisi = NULL, $status = '', $search = '')
    {
        $this->_build_datatable_query($tahun, $bulan, $id_divisi, $status, $search);
        return $this->db->count_all_results();
    }

    public function datatable_data($tahun, $bulan, $start, $length, $order_col = 3, $order_dir = 'desc', $id_divisi = NULL, $status = '', $search = '')
    {
        $this->_build_datatable_query($tahun, $bulan, $id_divisi, $status, $search);

        $order_map = [
            1 => 'p.nip',
            2 => 'p.nama_lengkap',
            3 => 'l.tanggal',
            4 => 'l.jam_mulai',
            5 => 'l.jam_selesai',
            6 => 'l.durasi_jam',
            8 => 'l.status',
        ];
        $order_by = isset($order_map[$order_col]) ? $order_map[$order_col] : 'l.tanggal';
        $direction = strtolower($order_dir) === 'asc' ? 'ASC' : 'DESC';

        $this->db->order_by($order_by, $direction)
                 ->order_by('l.id_lembur', 'DESC');

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

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