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

class Iuran_model extends CI_Model {

    private $table = 'pembayaran_iuran';


    // Semua cicilan yang menunggu verifikasi (untuk admin dashboard)
public function get_all_cicilan_menunggu_verifikasi() {
    return $this->db
        ->select('ci.*, u.nama_lengkap, ji.nama_iuran, pi.periode_bulan, pi.periode_tahun, pi.nominal as nominal_tagihan')
        ->from('cicilan_iuran ci')
        ->join('pembayaran_iuran pi', 'pi.id = ci.pembayaran_iuran_id')
        ->join('warga w',            'w.id  = pi.warga_id')
        ->join('users u',            'u.id  = w.user_id')
        ->join('jenis_iuran ji',     'ji.id = pi.jenis_iuran_id')
        ->where('ci.status', 'menunggu_verifikasi')
        ->order_by('ci.created_at', 'DESC')
        ->get()->result();
}

// Iuran milik warga yang sudah dicicil sebagian (untuk warga dashboard)
public function get_iuran_bercicilan_by_user($user_id) {
    return $this->db
        ->select('pi.id, pi.nominal, pi.total_terbayar, pi.status, pi.periode_bulan, pi.periode_tahun, ji.nama_iuran')
        ->from('pembayaran_iuran pi')
        ->join('warga w',        'w.id  = pi.warga_id')
        ->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id')
        ->where('w.user_id', $user_id)
        ->where('pi.status !=', 'lunas')
        ->where('pi.total_terbayar >', 0)
        ->order_by('pi.created_at', 'DESC')
        ->get()->result();
}

    // ═══════════════════════════════════════════════════════
    // ─── CICILAN ───────────────────────────────────────────
    // ═══════════════════════════════════════════════════════

    public function get_cicilan_by_iuran($pembayaran_iuran_id) {
        $this->db->select('ci.*, u.nama_lengkap as input_nama, mk.nama as kas_nama');
        $this->db->from('cicilan_iuran ci');
        $this->db->join('users u',       'u.id  = ci.input_oleh',    'left');
        $this->db->join('master_kas mk', 'mk.id = ci.master_kas_id', 'left');
        $this->db->where('ci.pembayaran_iuran_id', (int)$pembayaran_iuran_id);
        $this->db->order_by('ci.ke', 'ASC');
        return $this->db->get()->result();
    }

    public function insert_cicilan($data) {
        return $this->db->insert('cicilan_iuran', $data);
    }

    public function get_cicilan_by_id($id) {
        $this->db->select('ci.*, pi.nominal, pi.periode_bulan, pi.periode_tahun,
            pi.total_terbayar, pi.sisa_tagihan, pi.jumlah_cicilan,
            u.nama_lengkap, w.nik, ji.nama_iuran, mk.nama as kas_nama');
        $this->db->from('cicilan_iuran ci');
        $this->db->join('pembayaran_iuran pi', 'pi.id = ci.pembayaran_iuran_id');
        $this->db->join('warga w',             'w.id  = pi.warga_id');
        $this->db->join('users u',             'u.id  = w.user_id');
        $this->db->join('jenis_iuran ji',      'ji.id = pi.jenis_iuran_id');
        $this->db->join('master_kas mk',       'mk.id = ci.master_kas_id', 'left');
        $this->db->where('ci.id', (int)$id);
        return $this->db->get()->row();
    }

    public function recalc_iuran($pembayaran_iuran_id) {
        $iuran = $this->get_by_id($pembayaran_iuran_id);
        if (!$iuran) return false;

        // Hitung hanya dari cicilan yang sudah lunas
        $row = $this->db->select('SUM(nominal_cicilan) as total, COUNT(id) as jml')
            ->where('pembayaran_iuran_id', (int)$pembayaran_iuran_id)
            ->where('status', 'lunas')
            ->get('cicilan_iuran')->row();

        $total_terbayar = (float)($row->total ?: 0);
        $jumlah_cicilan = (int)  ($row->jml   ?: 0);
        $sisa           = $iuran->nominal - $total_terbayar;

        // Status: lunas jika sisa <= 0, cicil jika ada cicilan tapi belum lunas, pending jika belum ada cicilan sama sekali
        if ($sisa <= 0) {
            $status = 'lunas';
        } elseif ($jumlah_cicilan > 0) {
            $status = 'cicil';
        } else {
            $status = 'pending';
        }

        $tipe_bayar = ($jumlah_cicilan > 0) ? 'cicil' : ($iuran->tipe_bayar ?? 'lunas');

        $this->db->where('id', (int)$pembayaran_iuran_id);
        return $this->db->update('pembayaran_iuran', [
            'total_terbayar'     => $total_terbayar,
            'sisa_tagihan'       => max(0, $sisa),
            'jumlah_cicilan'     => $jumlah_cicilan,
            'tipe_bayar'         => $tipe_bayar,
            'status'             => $status,
            'tanggal_verifikasi' => ($sisa <= 0) ? date('Y-m-d H:i:s') : null,
        ]);
    }

    public function hapus_cicilan($cicilan_id) {
        $this->db->where('id', (int)$cicilan_id);
        return $this->db->delete('cicilan_iuran');
    }


    public function get_cicilan_menunggu_by_iuran($iuran_id) {
    return $this->db
        ->where('pembayaran_iuran_id', (int)$iuran_id)
        ->where('status', 'menunggu_verifikasi')
        ->get('cicilan_iuran')
        ->row();
}

    // ═══════════════════════════════════════════════════════
    // ─── GET ALL — include field cicilan ───────────────────
    // ═══════════════════════════════════════════════════════

    // public function get_all() {
    //     $this->db->select('
    //         pi.*,
    //         pi.total_terbayar,
    //         pi.sisa_tagihan,
    //         pi.jumlah_cicilan,
    //         pi.tipe_bayar,
    //         w.nik,
    //         w.id as warga_id,
    //         u.nama_lengkap,
    //         u.id as user_id,
    //         ji.nama_iuran
    //     ');
    //     $this->db->from($this->table . ' pi');
    //     $this->db->join('warga w',        'w.id  = pi.warga_id');
    //     $this->db->join('users u',        'u.id  = w.user_id');
    //     $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
    //     $this->db->order_by('pi.periode_tahun', 'DESC');
    //     $this->db->order_by('pi.periode_bulan', 'DESC');
    //     return $this->db->get()->result();
    // }

    public function get_all($filter = []) {
    $this->db->select('
        pi.*,
        pi.total_terbayar,
        pi.sisa_tagihan,
        pi.jumlah_cicilan,
        pi.tipe_bayar,
        w.nik,
        w.id as warga_id,
        u.nama_lengkap,
        u.id as user_id,
        ji.nama_iuran
    ');
    $this->db->from($this->table . ' pi');
    $this->db->join('warga w',        'w.id  = pi.warga_id');
    $this->db->join('users u',        'u.id  = w.user_id');
    $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');

    // ── FILTER ──
    if (!empty($filter['status'])) {
        if ($filter['status'] === 'cicil') {
            // cicil bisa dari DB langsung atau fallback
            $this->db->group_start();
            $this->db->where('pi.status', 'cicil');
            $this->db->or_group_start();
                $this->db->where('pi.status', 'pending');
                $this->db->where('pi.total_terbayar >', 0);
                $this->db->where('pi.sisa_tagihan >', 0);
            $this->db->group_end();
            $this->db->group_end();
        } else {
            $this->db->where('pi.status', $filter['status']);
        }
    }
    if (!empty($filter['bulan']))          $this->db->where('pi.periode_bulan', $filter['bulan']);
    if (!empty($filter['tahun']))          $this->db->where('pi.periode_tahun', $filter['tahun']);
    if (!empty($filter['jenis_iuran_id'])) $this->db->where('pi.jenis_iuran_id', $filter['jenis_iuran_id']);
    if (!empty($filter['nama']))           $this->db->like('u.nama_lengkap', $filter['nama']);

    $this->db->order_by('pi.periode_tahun', 'DESC');
    $this->db->order_by('pi.periode_bulan', 'DESC');
    return $this->db->get()->result();
}

    // ═══════════════════════════════════════════════════════
    // ─── GET BY ID — include field cicilan ─────────────────
    // ═══════════════════════════════════════════════════════

    public function get_by_id($id) {
        $this->db->select('
            pi.*,
            pi.total_terbayar,
            pi.sisa_tagihan,
            pi.jumlah_cicilan,
            pi.tipe_bayar,
            w.nik,
            w.id as warga_id,
            w.user_id,
            u.nama_lengkap,
            u.alamat,
            ji.nama_iuran
        ');
        $this->db->from($this->table . ' pi');
        $this->db->join('warga w',        'w.id  = pi.warga_id');
        $this->db->join('users u',        'u.id  = w.user_id');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.id', $id);
        return $this->db->get()->row();
    }

    // ═══════════════════════════════════════════════════════
    // ─── GET BY USER — include field cicilan ───────────────
    // ═══════════════════════════════════════════════════════

    // public function get_by_user($user_id) {
    //     $warga = $this->db->get_where('warga', array('user_id' => $user_id))->row();
    //     if (!$warga) return array();

    //     $this->db->select('
    //         pi.*,
    //         pi.total_terbayar,
    //         pi.sisa_tagihan,
    //         pi.jumlah_cicilan,
    //         pi.tipe_bayar,
    //         ji.nama_iuran,
    //         pi.periode_bulan as bulan,
    //         pi.periode_tahun as tahun,
    //         pi.status as status_bayar
    //     ');
    //     $this->db->from($this->table . ' pi');
    //     $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
    //     $this->db->where('pi.warga_id', $warga->id);
    //     $this->db->order_by('pi.periode_tahun', 'DESC');
    //     $this->db->order_by('pi.periode_bulan', 'DESC');
    //     return $this->db->get()->result();
    // }

    public function get_by_user($user_id, $filter = []) {
    $warga = $this->db->get_where('warga', ['user_id' => $user_id])->row();
    if (!$warga) return [];

    $this->db->select('pi.*, pi.total_terbayar, pi.sisa_tagihan, pi.jumlah_cicilan,
        pi.tipe_bayar, ji.nama_iuran,
        pi.periode_bulan as bulan, pi.periode_tahun as tahun, pi.status as status_bayar');
    $this->db->from($this->table . ' pi');
    $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
    $this->db->where('pi.warga_id', $warga->id);

    if (!empty($filter['status'])) {
        if ($filter['status'] === 'cicil') {
            $this->db->group_start();
            $this->db->where('pi.status', 'cicil');
            $this->db->or_group_start();
                $this->db->where('pi.status', 'pending');
                $this->db->where('pi.total_terbayar >', 0);
            $this->db->group_end();
            $this->db->group_end();
        } else {
            $this->db->where('pi.status', $filter['status']);
        }
    }
    if (!empty($filter['bulan']))          $this->db->where('pi.periode_bulan', $filter['bulan']);
    if (!empty($filter['tahun']))          $this->db->where('pi.periode_tahun', $filter['tahun']);
    if (!empty($filter['jenis_iuran_id'])) $this->db->where('pi.jenis_iuran_id', $filter['jenis_iuran_id']);

    $this->db->order_by('pi.periode_tahun', 'DESC');
    $this->db->order_by('pi.periode_bulan', 'DESC');
    return $this->db->get()->result();
}

    // ═══════════════════════════════════════════════════════
    // ─── GET BY WARGA — include field cicilan ──────────────
    // ═══════════════════════════════════════════════════════

    public function get_by_warga($warga_id) {
        $this->db->select('
            pi.*,
            pi.total_terbayar,
            pi.sisa_tagihan,
            pi.jumlah_cicilan,
            pi.tipe_bayar,
            ji.nama_iuran,
            pi.periode_bulan as bulan,
            pi.periode_tahun as tahun
        ');
        $this->db->from($this->table . ' pi');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.warga_id', $warga_id);
        $this->db->order_by('pi.periode_tahun', 'DESC');
        $this->db->order_by('pi.periode_bulan', 'DESC');
        return $this->db->get()->result();
    }

    // ═══════════════════════════════════════════════════════
    // ─── GET BY STATUS — include field cicilan ─────────────
    // ═══════════════════════════════════════════════════════

    public function get_by_status($status) {
        $this->db->select('
            pi.*,
            pi.total_terbayar,
            pi.sisa_tagihan,
            pi.jumlah_cicilan,
            pi.tipe_bayar,
            w.nik,
            u.nama_lengkap,
            ji.nama_iuran
        ');
        $this->db->from($this->table . ' pi');
        $this->db->join('warga w',        'w.id  = pi.warga_id');
        $this->db->join('users u',        'u.id  = w.user_id');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.status', $status);
        $this->db->order_by('pi.periode_tahun', 'DESC');
        $this->db->order_by('pi.periode_bulan', 'DESC');
        return $this->db->get()->result();
    }

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

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

    public function delete($id) {
        $this->db->where('id', $id);
        return $this->db->delete($this->table);
    }

    public function get_jenis_iuran() {
        $this->db->where('status', 'aktif');
        return $this->db->get('jenis_iuran')->result();
    }

    public function generate_tagihan_massal($bulan, $tahun, $jenis_iuran_id) {
        $this->db->select('w.*, u.nama_lengkap');
        $this->db->from('warga w');
        $this->db->join('users u', 'u.id = w.user_id');
        $this->db->where('u.status', 'aktif');
        $warga = $this->db->get()->result();

        $jenis = $this->db->get_where('jenis_iuran', array('id' => $jenis_iuran_id))->row();
        if (!$jenis) return false;

        $success = true;
        foreach ($warga as $w) {
            $this->db->where('warga_id', $w->id);
            $this->db->where('jenis_iuran_id', $jenis_iuran_id);
            $this->db->where('periode_bulan', $bulan);
            $this->db->where('periode_tahun', $tahun);
            $exists = $this->db->get($this->table)->row();

            if (!$exists) {
                $data = array(
                    'warga_id'       => $w->id,
                    'jenis_iuran_id' => $jenis_iuran_id,
                    'periode_bulan'  => $bulan,
                    'periode_tahun'  => $tahun,
                    'nominal'        => $jenis->nominal,
                    'status'         => 'pending',
                    'created_at'     => date('Y-m-d H:i:s'),
                );
                if (!$this->db->insert($this->table, $data)) {
                    $success = false;
                }
            }
        }
        return $success;
    }

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

    public function get_total_iuran($status = null) {
        $this->db->select('SUM(nominal) as total');
        if ($status) $this->db->where('status', $status);
        $result = $this->db->get($this->table)->row();
        return $result->total ? $result->total : 0;
    }

    public function get_total_bulan_ini() {
        $this->db->select('SUM(nominal) as total');
        // Gunakan tanggal_verifikasi untuk mendeteksi uang yang masuk di bulan ini
        $this->db->where('MONTH(tanggal_verifikasi)', date('n'));
        $this->db->where('YEAR(tanggal_verifikasi)', date('Y'));
        $this->db->where('status', 'lunas');
        $result = $this->db->get($this->table)->row();
        return $result->total ? $result->total : 0;
    }

    public function get_total_tahun_ini() {
        $this->db->select('SUM(nominal) as total');
        $this->db->where('periode_tahun', date('Y'));
        $this->db->where('status', 'lunas');
        $result = $this->db->get($this->table)->row();
        return $result->total ? $result->total : 0;
    }

    // ═══════════════════════════════════════════════════════
    // ─── TOTAL TUNGGAKAN (admin) ────────────────────────────
    // pending  → belum ada cicilan sama sekali → pakai nominal penuh
    // cicil    → sudah dicicil sebagian        → pakai sisa_tagihan
    // ═══════════════════════════════════════════════════════
public function get_total_tunggakan() {
    $sql = "
        SELECT SUM(
            pi.nominal - IFNULL(
                (SELECT SUM(ci.nominal_cicilan) 
                 FROM cicilan_iuran ci 
                 WHERE ci.pembayaran_iuran_id = pi.id 
                 AND ci.status = 'lunas'),
            0)
        ) as total
        FROM pembayaran_iuran pi
        WHERE pi.status IN ('pending', 'cicil')
    ";
    $result = $this->db->query($sql)->row();
    return $result->total ? $result->total : 0;
}
    public function get_warga_belum_bayar_bulan_ini() {
        $this->db->where('periode_bulan', date('n'));
        $this->db->where('periode_tahun', date('Y'));
        $this->db->where('status', 'pending');
        $this->db->group_by('warga_id');
        return $this->db->count_all_results($this->table);
    }

    public function get_pembayaran_terbaru($limit = 5) {
        $this->db->select('pi.*, w.nik, u.nama_lengkap, ji.nama_iuran');
        $this->db->from($this->table . ' pi');
        $this->db->join('warga w',        'w.id  = pi.warga_id');
        $this->db->join('users u',        'u.id  = w.user_id');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.status', 'lunas');
        $this->db->order_by('pi.tanggal_verifikasi', 'DESC');
        $this->db->limit($limit);
        return $this->db->get()->result();
    }

    public function get_statistik_per_bulan($tahun = null) {
        if (!$tahun) $tahun = date('Y');
        $this->db->select('periode_bulan as bulan, SUM(nominal) as total');
        $this->db->where('periode_tahun', $tahun);
        $this->db->where('status', 'lunas');
        $this->db->group_by('periode_bulan');
        $this->db->order_by('periode_bulan', 'ASC');
        return $this->db->get($this->table)->result();
    }

    public function cek_tagihan_exists($warga_id, $jenis_iuran_id, $bulan, $tahun) {
        $this->db->where('warga_id', $warga_id);
        $this->db->where('jenis_iuran_id', $jenis_iuran_id);
        $this->db->where('periode_bulan', $bulan);
        $this->db->where('periode_tahun', $tahun);
        return $this->db->get($this->table)->row();
    }

    public function get_tagihan_pending_by_user($user_id) {
        $warga = $this->db->get_where('warga', array('user_id' => $user_id))->row();
        if (!$warga) return array();

        $this->db->select('pi.*, ji.nama_iuran');
        $this->db->from($this->table . ' pi');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.warga_id', $warga->id);
        $this->db->where('pi.status', 'pending');
        $this->db->order_by('pi.periode_tahun', 'DESC');
        $this->db->order_by('pi.periode_bulan', 'DESC');
        return $this->db->get()->result();
    }

    public function get_tagihan_menunggu_verifikasi_by_user($user_id) {
        $warga = $this->db->get_where('warga', array('user_id' => $user_id))->row();
        if (!$warga) return array();

        $this->db->select('pi.*, ji.nama_iuran');
        $this->db->from($this->table . ' pi');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.warga_id', $warga->id);
        $this->db->where('pi.status', 'menunggu_verifikasi');
        $this->db->order_by('pi.tanggal_bayar', 'DESC');
        return $this->db->get()->result();
    }

    public function get_all_menunggu_verifikasi() {
        $this->db->select('pembayaran_iuran.*, users.nama_lengkap, jenis_iuran.nama_iuran');
        $this->db->from('pembayaran_iuran');
        $this->db->join('warga',       'warga.id       = pembayaran_iuran.warga_id');
        $this->db->join('users',       'users.id       = warga.user_id');
        $this->db->join('jenis_iuran', 'jenis_iuran.id = pembayaran_iuran.jenis_iuran_id');
        $this->db->where('pembayaran_iuran.status', 'menunggu_verifikasi');
        $this->db->order_by('pembayaran_iuran.tanggal_bayar', 'DESC');
        return $this->db->get()->result();
    }

    public function get_all_pending_by_user($user_id) {
        $warga = $this->db->get_where('warga', array('user_id' => $user_id))->row();
        if (!$warga) return array();

        $this->db->select('pi.*, ji.nama_iuran');
        $this->db->from($this->table . ' pi');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');
        $this->db->where('pi.warga_id', $warga->id);
        $this->db->where('pi.status', 'pending');
        $this->db->order_by('pi.periode_tahun', 'ASC');
        $this->db->order_by('pi.periode_bulan', 'ASC');
        return $this->db->get()->result();
    }

    // ─── Iuran Arisan: semua warga, belum bayar ──────────────────────────────
    public function get_arisan_iuran_pending() {
        $this->db->select('
            ai.id,
            ai.warga_id,
            ai.nominal_bayar      AS nominal,
            u.nama_lengkap,
            w.nik,
            w.user_id,
            a.nama_arisan         AS nama_iuran,
            a.id                  AS arisan_id,
            ap.pertemuan_ke,
            ap.tanggal_pertemuan,
            ap.id                 AS pertemuan_id
        ');
        $this->db->from('arisan_iuran ai');
        $this->db->join('warga w',             'w.id  = ai.warga_id');
        $this->db->join('users u',             'u.id  = w.user_id');
        $this->db->join('arisan_pertemuan ap', 'ap.id = ai.pertemuan_id');
        $this->db->join('arisan a',            'a.id  = ap.arisan_id');
        $this->db->where('ai.status_bayar', 'belum');
        $this->db->where('u.status',        'aktif');
        $this->db->order_by('u.nama_lengkap',       'ASC');
        $this->db->order_by('ap.tanggal_pertemuan', 'ASC');
        return $this->db->get()->result();
    }

    // ─── Iuran Arisan: 1 warga tertentu, belum bayar ─────────────────────────
    public function get_arisan_iuran_pending_by_warga($warga_id) {
        $this->db->select('
            ai.id,
            ai.warga_id,
            ai.nominal_bayar      AS nominal,
            u.nama_lengkap,
            w.nik,
            a.nama_arisan         AS nama_iuran,
            a.id                  AS arisan_id,
            ap.pertemuan_ke,
            ap.tanggal_pertemuan,
            ap.id                 AS pertemuan_id
        ');
        $this->db->from('arisan_iuran ai');
        $this->db->join('warga w',             'w.id  = ai.warga_id');
        $this->db->join('users u',             'u.id  = w.user_id');
        $this->db->join('arisan_pertemuan ap', 'ap.id = ai.pertemuan_id');
        $this->db->join('arisan a',            'a.id  = ap.arisan_id');
        $this->db->where('ai.status_bayar', 'belum');
        $this->db->where('ai.warga_id',     (int)$warga_id);
        $this->db->order_by('ap.tanggal_pertemuan', 'ASC');
        return $this->db->get()->result();
    }

    // ═══════════════════════════════════════════════════════
    // ─── SERVER-SIDE DATATABLES ────────────────────────────
    // ═══════════════════════════════════════════════════════

    private function _dt_base_query($filter = [], $user_id = null) {
        // ── Lookup warga_id SEBELUM build query utama ──
        // Penting: get_where() mereset CI3 query builder, jadi harus selesai dulu
        $warga_id_filter = null;
        if ($user_id) {
            $warga = $this->db->get_where('warga', ['user_id' => $user_id])->row();
            $warga_id_filter = $warga ? (int)$warga->id : 0;
        }

        // ── Mulai build query utama ──
        $this->db->select('
            pi.id, pi.nominal, pi.total_terbayar, pi.sisa_tagihan,
            pi.jumlah_cicilan, pi.tipe_bayar, pi.status,
            pi.periode_bulan, pi.periode_tahun,
            pi.tanggal_bayar, pi.warga_id,
            w.nik,
            u.nama_lengkap, u.no_hp, u.id as user_id,
            ji.nama_iuran
        ');
        $this->db->from($this->table . ' pi');
        $this->db->join('warga w',        'w.id  = pi.warga_id');
        $this->db->join('users u',        'u.id  = w.user_id');
        $this->db->join('jenis_iuran ji', 'ji.id = pi.jenis_iuran_id');

        // Filter by warga (mode warga)
        if ($user_id !== null) {
            if ($warga_id_filter > 0) {
                $this->db->where('pi.warga_id', $warga_id_filter);
            } else {
                $this->db->where('pi.id', 0); // tidak ada data
            }
        }

        // Filter status
        if (!empty($filter['status'])) {
            if ($filter['status'] === 'cicil') {
                $this->db->group_start();
                $this->db->where('pi.status', 'cicil');
                $this->db->or_group_start();
                    $this->db->where('pi.status', 'pending');
                    $this->db->where('pi.total_terbayar >', 0);
                    $this->db->where('pi.sisa_tagihan >', 0);
                $this->db->group_end();
                $this->db->group_end();
            } else {
                $this->db->where('pi.status', $filter['status']);
            }
        }
        if (!empty($filter['bulan']))          $this->db->where('pi.periode_bulan', $filter['bulan']);
        if (!empty($filter['tahun']))          $this->db->where('pi.periode_tahun', $filter['tahun']);
        if (!empty($filter['jenis_iuran_id'])) $this->db->where('pi.jenis_iuran_id', $filter['jenis_iuran_id']);

        // DataTables global search
        if (!empty($filter['search'])) {
            $s = $filter['search'];
            $this->db->group_start();
            $this->db->like('u.nama_lengkap', $s);
            $this->db->or_like('ji.nama_iuran', $s);
            $this->db->or_like('w.nik', $s);
            $this->db->group_end();
        }
    }

    public function count_datatables($filter = [], $user_id = null) {
        $this->_dt_base_query($filter, $user_id);
        return $this->db->count_all_results();
    }

    public function get_datatables($filter = [], $limit = 10, $offset = 0, $order_col = 'pi.periode_tahun', $order_dir = 'DESC', $user_id = null) {
        $this->_dt_base_query($filter, $user_id);
        $this->db->order_by($order_col, $order_dir);
        $this->db->limit($limit, $offset);
        return $this->db->get()->result();
    }

}
