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

class Bansos_model extends CI_Model {

    protected $table = 'bansos';

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

        if ($tahun)  $this->db->where('b.tahun', $tahun);
        if ($status) $this->db->where('b.status', $status);
        if ($q) {
            $this->db->group_start()
                ->like('w.nama_lengkap', $q)
                ->or_like('w.nik', $q)
                ->group_end();
        }
        if ($jenis) {
            $this->db->where("JSON_CONTAINS(b.jenis_bantuan, '\"".addslashes($jenis)."\"')", null, false);
        }

        return $this->db
            ->order_by('b.tahun', 'DESC')
            ->order_by('w.nama_lengkap', 'ASC')
            ->get()
            ->result();
    }

    // ─── AMBIL PER WARGA ──────────────────────────────────────
    public function get_by_warga($warga_id, $tahun = null) {
        $this->db
            ->select('b.*, w.nama_lengkap, w.nik, kk.no_kk')
            ->from($this->table.' b')
            ->join('warga w',           'w.id = b.warga_id', 'left')
            ->join('kartu_keluarga kk', 'kk.id = w.kk_id',  'left')
            ->where('b.warga_id', (int)$warga_id);

        if ($tahun) $this->db->where('b.tahun', $tahun);

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

    // ─── AMBIL SATU (dengan data warga) ──────────────────────
    public function get_by_id_with_warga($id) {
        return $this->db
            ->select('b.*, w.nama_lengkap, w.nik, kk.no_kk')
            ->from($this->table.' b')
            ->join('warga w',           'w.id = b.warga_id', 'left')
            ->join('kartu_keluarga kk', 'kk.id = w.kk_id',  'left')
            ->where('b.id', (int)$id)
            ->get()
            ->row();
    }

    // ─── CEK DUPLIKAT (warga + tahun) ────────────────────────
    public function get_by_warga_tahun($warga_id, $tahun) {
        return $this->db
            ->where('warga_id', (int)$warga_id)
            ->where('tahun', $tahun)
            ->get($this->table)
            ->row();
    }

    // ─── 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();
    }

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

    // ─── STATISTIK ────────────────────────────────────────────
    public function count_penerima() {
        return $this->db->count_all_results($this->table);
    }

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

    public function count_penerima_unik() {
        $result = $this->db
            ->select('COUNT(DISTINCT warga_id) as total')
            ->get($this->table)
            ->row();
        return $result->total ?? 0;
    }
}