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

class Absensi_model extends CI_Model
{
    const TABLE = 'tb_absensi';

    private function _build_summary_query($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL, $search = '', $filter_status = '')
    {
                $tanggal_mulai = (string) $tanggal_mulai;
                $tanggal_selesai = (string) $tanggal_selesai;

                $subquery_lembur = '(SELECT l.id_pegawai, COUNT(*) AS jumlah_lembur_approved
                        FROM tb_lembur l
                        WHERE l.status = "APPROVED"
                            AND l.tanggal >= "' . $tanggal_mulai . '"
                            AND l.tanggal <= "' . $tanggal_selesai . '"
                        GROUP BY l.id_pegawai) lo';

                // Count holidays in the period from tb_hari_libur
                $subquery_holidays = '(SELECT COUNT(*) AS jumlah_libur_calendar
                        FROM tb_hari_libur
                        WHERE tanggal >= "' . $tanggal_mulai . '"
                            AND tanggal <= "' . $tanggal_selesai . '") ho';

                $total_meaningful_expr = '(
                        COUNT(CASE WHEN a.status="MASUK" THEN 1 END)
                    + COUNT(CASE WHEN a.status="ALPHA" THEN 1 END)
                    + COUNT(CASE WHEN a.status="SAKIT" THEN 1 END)
                    + COUNT(CASE WHEN a.status="CUTI" THEN 1 END)
                    + COUNT(CASE WHEN a.status="IZIN" THEN 1 END)
                    + COUNT(CASE WHEN a.status="LIBUR" THEN 1 END)
                    + MAX(IFNULL(lo.jumlah_lembur_approved, 0))
                )';

        $this->db->select('p.id_pegawai, p.nip, p.nama_lengkap, d.nama_divisi,
                           COUNT(CASE WHEN a.status="MASUK" THEN 1 END) as masuk,
                           COUNT(CASE WHEN a.status="ALPHA" THEN 1 END) as alpha,
                           COUNT(CASE WHEN a.status="SAKIT" THEN 1 END) as sakit,
                           COUNT(CASE WHEN a.status="CUTI" THEN 1 END) as cuti,
                           (COUNT(CASE WHEN a.status="LIBUR" THEN 1 END) + MAX(IFNULL(ho.jumlah_libur_calendar, 0))) as libur,
                           MAX(IFNULL(lo.jumlah_lembur_approved, 0)) as lembur,
                           COUNT(CASE WHEN a.status="IZIN" THEN 1 END) as izin,
                           SUM(CASE WHEN a.is_terlambat=1 THEN 1 ELSE 0 END) as terlambat,
                                                     ' . $total_meaningful_expr . ' as total_record')
                  ->from('tb_pegawai p')
                  ->join('tb_divisi d', 'd.id_divisi = p.id_divisi', 'left')
                  ->join(self::TABLE . ' a', 'a.id_pegawai = p.id_pegawai AND a.tanggal >= "' . $tanggal_mulai . '" AND a.tanggal <= "' . $tanggal_selesai . '"', 'left')
                  ->join($subquery_lembur, 'lo.id_pegawai = p.id_pegawai', 'left', FALSE)
                  ->join($subquery_holidays, '1=1', 'left', FALSE)
                  ->where('p.status_pegawai', 'AKTIF');

        if ($id_divisi) {
            $this->db->where('p.id_divisi', (int) $id_divisi);
        }

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

        $this->db->group_by('p.id_pegawai');

        if ($filter_status === 'SUDAH') {
            $this->db->having($total_meaningful_expr . ' > 0', NULL, FALSE);
        } elseif ($filter_status === 'BELUM') {
            $this->db->having($total_meaningful_expr . ' = 0', NULL, FALSE);
        }
    }

    public function get_all($id_pegawai = NULL, $tanggal_mulai = NULL, $tanggal_selesai = NULL)
    {
        $this->db->from(self::TABLE);
        if ($id_pegawai) {
            $this->db->where('id_pegawai', (int) $id_pegawai);
        }
        if ($tanggal_mulai && $tanggal_selesai) {
            $this->db->where('tanggal >=', (string) $tanggal_mulai);
            $this->db->where('tanggal <=', (string) $tanggal_selesai);
        }
        return $this->db->order_by('tanggal', 'DESC')
                        ->get()->result();
    }

    public function get_by_id($id)
    {
        return $this->db->where('id_absensi', (int) $id)
                        ->get(self::TABLE)->row();
    }

    public function exists_date($id_pegawai, $tanggal)
    {
        return $this->db->where('id_pegawai', (int) $id_pegawai)
                        ->where('tanggal', $tanggal)
                        ->count_all_results(self::TABLE) > 0;
    }

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

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

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

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

    public function delete_by_import($id_import)
    {
        // Get all records for this batch and delete them
        return $this->db->where('id_import', (int) $id_import)
                        ->delete(self::TABLE);
    }

    public function reset_by_range($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL)
    {
        if ($id_divisi !== NULL && (int) $id_divisi > 0) {
            $this->db->where('id_pegawai IN (SELECT id_pegawai FROM tb_pegawai WHERE id_divisi = ' . (int) $id_divisi . ')', NULL, FALSE);
        }

        $this->db->where('tanggal >=', (string) $tanggal_mulai);
        $this->db->where('tanggal <=', (string) $tanggal_selesai);
        $this->db->delete(self::TABLE);

        return (int) $this->db->affected_rows();
    }

    public function get_bulan_list($tahun)
    {
        return $this->db->select('DISTINCT MONTH(tanggal) as bulan')
                        ->where('YEAR(tanggal)', (int) $tahun)
                        ->order_by('MONTH(tanggal)', 'DESC')
                        ->get(self::TABLE)->result();
    }

    public function get_summary_periode($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL)
    {
        $this->_build_summary_query($tanggal_mulai, $tanggal_selesai, $id_divisi);
        return $this->db->order_by('d.nama_divisi, p.nama_lengkap')
                        ->get()->result();
    }

    public function datatable_total_summary($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL)
    {
        $this->_build_summary_query($tanggal_mulai, $tanggal_selesai, $id_divisi);
        $sql = $this->db->get_compiled_select();
        $row = $this->db->query('SELECT COUNT(*) AS total FROM (' . $sql . ') q')->row();
        return $row ? (int) $row->total : 0;
    }

    public function datatable_filtered_summary($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL, $search = '', $filter_status = '')
    {
        $this->_build_summary_query($tanggal_mulai, $tanggal_selesai, $id_divisi, $search, $filter_status);
        $sql = $this->db->get_compiled_select();
        $row = $this->db->query('SELECT COUNT(*) AS total FROM (' . $sql . ') q')->row();
        return $row ? (int) $row->total : 0;
    }

    public function datatable_data_summary($tanggal_mulai, $tanggal_selesai, $start, $length, $search = '', $order_col = 2, $order_dir = 'asc', $id_divisi = NULL, $filter_status = '')
    {
        $this->_build_summary_query($tanggal_mulai, $tanggal_selesai, $id_divisi, $search, $filter_status);

        $order_map = [
            1 => 'p.nip',
            2 => 'p.nama_lengkap',
            3 => 'd.nama_divisi',
            4 => 'masuk',
            5 => 'sakit',
            6 => 'cuti',
            7 => 'libur',
            8 => 'lembur',
            9 => 'terlambat',
        ];
        $order_by = isset($order_map[$order_col]) ? $order_map[$order_col] : 'p.nama_lengkap';
        $direction = strtolower($order_dir) === 'desc' ? 'DESC' : 'ASC';

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

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

    public function get_detail_rows_periode($id_pegawai, $tanggal_mulai, $tanggal_selesai, $id_divisi = 0)
    {
        $records = $this->db->from(self::TABLE)
                            ->where('id_pegawai', (int) $id_pegawai)
                            ->where('tanggal >=', (string) $tanggal_mulai)
                            ->where('tanggal <=', (string) $tanggal_selesai)
                            ->order_by('tanggal', 'ASC')
                            ->get()->result();

        $map = [];
        foreach ($records as $record) {
            $map[$record->tanggal] = $record;
        }

        // Load holidays from calendar
        $start = new DateTime((string) $tanggal_mulai);
        $end = new DateTime((string) $tanggal_selesai);
        $end->modify('+1 day');
        
        $holidays = $this->db->select('tanggal')
                             ->from('tb_hari_libur')
                             ->where('tanggal >=', $start->format('Y-m-d'))
                             ->where('tanggal <', $end->format('Y-m-d'))
                             ->get()->result();
        
        $holidays_map = [];
        foreach ($holidays as $h) {
            $holidays_map[$h->tanggal] = true;
        }

        // Load Shift_model to check schedule
        if (!isset($this->Shift_model)) {
            $this->load->model('Shift_model');
        }

        $rows = [];
        for ($date = clone $start; $date < $end; $date->modify('+1 day')) {
            $tanggal = $date->format('Y-m-d');
            $record = isset($map[$tanggal]) ? $map[$tanggal] : NULL;
            
            // Check if this date is supposed to be a working day per shift schedule
            $schedule = $this->Shift_model->get_schedule_for_pegawai((int) $id_pegawai, (int) $id_divisi, $tanggal);
            $is_expected_working_day = $schedule && (int) $schedule->is_hari_kerja === 1;
            
            // If no manual record but date is in holiday calendar, auto-populate as LIBUR
            if (!$record && isset($holidays_map[$tanggal])) {
                $rows[] = (object) [
                    'id_absensi'              => NULL,
                    'tanggal'                 => $tanggal,
                    'hari'                    => hari_indonesia(date('l', strtotime($tanggal))),
                    'jam_masuk'               => NULL,
                    'jam_keluar'              => NULL,
                    'status'                  => 'LIBUR',
                    'keterangan'              => 'Hari Libur (Otomatis dari Kalender)',
                    'is_terlambat'            => 0,
                    'menit_terlambat'         => 0,
                    'has_record'              => 0,
                    'is_system_holiday'       => 1,
                    'is_expected_working_day' => 0,
                ];
            } elseif (!$record && $is_expected_working_day) {
                // Expected working day but no record yet: auto-populate as ALPHA (editable)
                $rows[] = (object) [
                    'id_absensi'              => NULL,
                    'tanggal'                 => $tanggal,
                    'hari'                    => hari_indonesia(date('l', strtotime($tanggal))),
                    'jam_masuk'               => NULL,
                    'jam_keluar'              => NULL,
                    'status'                  => 'ALPHA',
                    'keterangan'              => '',
                    'is_terlambat'            => 0,
                    'menit_terlambat'         => 0,
                    'has_record'              => 0,
                    'is_system_holiday'       => 0,
                    'is_expected_working_day' => 1,
                ];
            } else {
                $rows[] = (object) [
                    'id_absensi'              => $record->id_absensi ?? NULL,
                    'tanggal'                 => $tanggal,
                    'hari'                    => hari_indonesia(date('l', strtotime($tanggal))),
                    'jam_masuk'               => $record->jam_masuk ?? NULL,
                    'jam_keluar'              => $record->jam_keluar ?? NULL,
                    'status'                  => $record->status ?? '',
                    'keterangan'              => $record->keterangan ?? '',
                    'is_terlambat'            => $record->is_terlambat ?? 0,
                    'menit_terlambat'         => $record->menit_terlambat ?? 0,
                    'has_record'              => $record ? 1 : 0,
                    'is_system_holiday'       => 0,
                    'is_expected_working_day' => $is_expected_working_day ? 1 : 0,
                ];
            }
        }

        return $rows;
    }

    /**
     * Pegawai aktif yang BELUM memiliki record absensi sama sekali di rentang periode.
     * Digunakan sebagai indikator di halaman terpadu.
     */
    public function get_belum_absen($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL)
    {
        $this->db->select('p.id_pegawai, p.nip, p.nama_lengkap, d.nama_divisi')
                 ->from('tb_pegawai p')
                 ->join('tb_divisi d', 'd.id_divisi = p.id_divisi', 'left')
                 ->where('p.status_pegawai', 'AKTIF')
                 ->where('NOT EXISTS (
                     SELECT 1 FROM ' . self::TABLE . ' a
                     WHERE a.id_pegawai = p.id_pegawai
                       AND a.tanggal >= ' . $this->db->escape((string) $tanggal_mulai) . '
                       AND a.tanggal <= ' . $this->db->escape((string) $tanggal_selesai) . '
                 )', NULL, FALSE);

        if ($id_divisi) {
            $this->db->where('p.id_divisi', (int) $id_divisi);
        }

        return $this->db->order_by('d.nama_divisi, p.nama_lengkap')->get()->result();
    }

    /**
     * Hitung jumlah pegawai aktif yang sudah ada minimal 1 record absensi di periode ini.
     */
    public function count_sudah_absen($tanggal_mulai, $tanggal_selesai, $id_divisi = NULL)
    {
        $this->db->select('COUNT(DISTINCT a.id_pegawai) as total', FALSE)
                 ->from(self::TABLE . ' a')
                 ->join('tb_pegawai p', 'p.id_pegawai = a.id_pegawai', 'inner')
                 ->where('p.status_pegawai', 'AKTIF')
                 ->where('a.tanggal >=', (string) $tanggal_mulai)
                 ->where('a.tanggal <=', (string) $tanggal_selesai);

        if ($id_divisi) {
            $this->db->where('p.id_divisi', (int) $id_divisi);
        }

        $row = $this->db->get()->row();
        return $row ? (int) $row->total : 0;
    }

    /**
     * Update record absensi berdasarkan id_pegawai + tanggal (upsert helper).
     * Digunakan oleh auto-sync IzinCuti.
     */
    public function update_status_by_tanggal($id_pegawai, $tanggal, $status, $keterangan = NULL)
    {
        if ($this->exists_date($id_pegawai, $tanggal)) {
            return $this->db
                ->where('id_pegawai', (int) $id_pegawai)
                ->where('tanggal', $tanggal)
                ->update(self::TABLE, [
                    'status'     => $status,
                    'keterangan' => $keterangan,
                    'updated_at' => date('Y-m-d H:i:s'),
                ]);
        } else {
            return $this->db->insert(self::TABLE, [
                'id_pegawai'  => (int) $id_pegawai,
                'tanggal'     => $tanggal,
                'status'      => $status,
                'keterangan'  => $keterangan,
                'sumber'      => 'AUTO',
                'created_at'  => date('Y-m-d H:i:s'),
                'updated_at'  => date('Y-m-d H:i:s'),
            ]);
        }
    }
}
