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

class Warga_kurang_mampu_model extends CI_Model {

    protected $table = 'warga_kurang_mampu';

    // ─── GET ALL ──────────────────────────────────────────────
    public function get_all($status = null, $q = null, $tahun = null) {
        $this->db
            ->select('wkm.*, w.nama_lengkap, w.nik, kk.no_kk')
            ->from($this->table . ' wkm')
            ->join('warga w',           'w.id = wkm.warga_id', 'left')
            ->join('kartu_keluarga kk', 'kk.id = w.kk_id',    'left');

        if ($status) $this->db->where('wkm.status_pengajuan', $status);
        if ($tahun)  $this->db->where('YEAR(wkm.tgl_pengajuan)', $tahun);
        if ($q) {
            $this->db->group_start()
                ->like('w.nama_lengkap', $q)
                ->or_like('w.nik', $q)
                ->or_like('kk.no_kk', $q)
            ->group_end();
        }

        return $this->db
            ->order_by('wkm.tgl_pengajuan', 'DESC')
            ->get()->result();
    }

    // ─── GET BY ID ────────────────────────────────────────────
    public function get_by_id($id) {
        return $this->db
            ->select('wkm.*, w.nama_lengkap, w.nik, kk.no_kk')
            ->from($this->table . ' wkm')
            ->join('warga w',           'w.id = wkm.warga_id', 'left')
            ->join('kartu_keluarga kk', 'kk.id = w.kk_id',    'left')
            ->where('wkm.id', (int)$id)
            ->get()->row();
    }

    // ─── GET BY WARGA ─────────────────────────────────────────
    public function get_by_warga($warga_id) {
        return $this->db
            ->where('warga_id', (int)$warga_id)
            ->order_by('tgl_pengajuan', 'DESC')
            ->get($this->table)->result();
    }

    // ─── CEK DUPLIKAT ─────────────────────────────────────────
    public function cek_duplikat($warga_id, $exclude_id = null) {
        $this->db->where('warga_id', (int)$warga_id)
                 ->where_in('status_pengajuan', ['menunggu', 'diverifikasi', 'disetujui']);
        if ($exclude_id) $this->db->where('id !=', (int)$exclude_id);
        return $this->db->count_all_results($this->table) > 0;
    }

    // ─── INSERT ───────────────────────────────────────────────
    public function insert($data) {
        $this->db->insert($this->table, $data);
        return $this->db->insert_id();
    }

    // ─── UPDATE ───────────────────────────────────────────────
    public function update($id, $data) {
        $this->db->where('id', (int)$id)->update($this->table, $data);
        return $this->db->affected_rows();
    }

    // ─── DELETE ───────────────────────────────────────────────
    public function delete($id) {
        $this->db->where('id', (int)$id)->delete($this->table);
        return $this->db->affected_rows();
    }

    // ─── STATISTIK ────────────────────────────────────────────
    public function count_by_status($status) {
        return $this->db->where('status_pengajuan', $status)->count_all_results($this->table);
    }

    public function count_all() {
        return $this->db->count_all($this->table);
    }
}