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

class Dashboard_model extends CI_Model
{
    public function get_periode_aktif()
    {
        return $this->db
            ->select('id_periode, bulan, tahun, status, tanggal_mulai, tanggal_selesai')
            ->from('tb_payroll_periode')
            ->where('status !=', 'LOCKED')
            ->order_by('tahun DESC, bulan DESC')
            ->limit(1)
            ->get()->row();
    }

    public function get_stat_cards($periode_aktif)
    {
        // Total pegawai aktif
        $total_pegawai = $this->db->where('status_pegawai', 'AKTIF')->count_all_results('tb_pegawai');

        // Payroll periode aktif
        $payroll_periode = ['total' => 0, 'total_neto' => 0, 'status_periode' => '-'];
        if ($periode_aktif) {
            $row = $this->db
                ->select('COUNT(*) as total, COALESCE(SUM(p.gaji_neto), 0) as total_neto')
                ->from('tb_payroll p')
                ->where('p.id_periode', $periode_aktif->id_periode)
                ->get()->row();
            $payroll_periode = [
                'total'          => (int) $row->total,
                'total_neto'     => (float) $row->total_neto,
                'status_periode' => $periode_aktif->status,
                'bulan'          => $periode_aktif->bulan,
                'tahun'          => $periode_aktif->tahun,
            ];
        }

        // Batch absensi yang butuh perhatian (PARTIAL atau FAILED)
        $batch_pending = $this->db->where_in('status', ['PARTIAL', 'FAILED'])->count_all_results('tb_import_absensi_log');

        // Total komponen gaji aktif
        $total_komponen = $this->db->where('is_active', 1)->count_all_results('tb_komponen_gaji');

        // Pegawai belum diproses payroll periode aktif
        $belum_proses = 0;
        if ($periode_aktif) {
            $sudah = $this->db
                ->select('id_pegawai')->from('tb_payroll')
                ->where('id_periode', $periode_aktif->id_periode)
                ->get()->result();
            $ids_sudah = array_column($sudah, 'id_pegawai');
            $q = $this->db->where('status_pegawai', 'AKTIF');
            if (!empty($ids_sudah)) {
                $q = $this->db->where_not_in('id_pegawai', $ids_sudah);
            }
            $belum_proses = $this->db->count_all_results('tb_pegawai');
        }

        return [
            'total_pegawai'  => $total_pegawai,
            'payroll'        => $payroll_periode,
            'batch_pending'  => $batch_pending,
            'total_komponen' => $total_komponen,
            'belum_proses'   => $belum_proses,
        ];
    }

    public function get_pegawai_per_divisi()
    {
        $rows = $this->db
            ->select('d.nama_divisi, COUNT(p.id_pegawai) as jumlah')
            ->from('tb_divisi d')
            ->join('tb_pegawai p', 'p.id_divisi = d.id_divisi AND p.status_pegawai = \'AKTIF\'', 'left')
            ->where('d.is_active', 1)
            ->group_by('d.id_divisi')
            ->order_by('jumlah DESC')
            ->get()->result();

        $labels = [];
        $values = [];
        foreach ($rows as $r) {
            $labels[] = $r->nama_divisi;
            $values[] = (int) $r->jumlah;
        }
        return ['labels' => $labels, 'values' => $values];
    }

    public function get_payroll_trend($bulan_ke_belakang = 6)
    {
        $rows = $this->db
            ->select('pp.bulan, pp.tahun, COALESCE(SUM(p.gaji_neto), 0) as total_neto, COUNT(p.id_payroll) as jml_pegawai')
            ->from('tb_payroll_periode pp')
            ->join('tb_payroll p', 'p.id_periode = pp.id_periode', 'left')
            ->group_by('pp.id_periode')
            ->order_by('pp.tahun ASC, pp.bulan ASC')
            ->limit($bulan_ke_belakang)
            ->get()->result();

        $bulan_id = ['', 'Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun', 'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des'];
        $labels   = [];
        $neto     = [];
        $pegawai  = [];
        foreach ($rows as $r) {
            $labels[]  = $bulan_id[(int) $r->bulan] . ' ' . $r->tahun;
            $neto[]    = (float) $r->total_neto;
            $pegawai[] = (int) $r->jml_pegawai;
        }
        return ['labels' => $labels, 'neto' => $neto, 'pegawai' => $pegawai];
    }

    public function get_batch_terbaru($limit = 5)
    {
        return $this->db
            ->select('id_import, nama_file, total_baris, berhasil, gagal, status, created_at')
            ->from('tb_import_absensi_log')
            ->order_by('created_at DESC')
            ->limit($limit)
            ->get()->result();
    }

    public function get_aktivitas_terbaru($limit = 8)
    {
        // Gabung payroll + batch import sebagai log aktivitas
        $sql = "
            SELECT 'PAYROLL' as tipe,
                   CONCAT('Payroll diproses - ', p.nama_lengkap) as keterangan,
                   py.updated_at as waktu,
                   py.status as status
            FROM tb_payroll py
            JOIN tb_pegawai p ON p.id_pegawai = py.id_pegawai
            UNION ALL
            SELECT 'IMPORT' as tipe,
                   CONCAT('Import absensi: ', nama_file) as keterangan,
                   created_at as waktu,
                   status as status
            FROM tb_import_absensi_log
            ORDER BY waktu DESC
            LIMIT " . (int) $limit;

        return $this->db->query($sql)->result();
    }
}
