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

class Warga_model extends CI_Model {

    // public function get_all() {
    //     $this->db->select('warga.*, kk.no_kk as no_kk, kk.id as kk_id_rel, u.username, u.password, u.status as status_akun');
    //     $this->db->from('warga');
    //     $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
    //     $this->db->join('users u', 'u.warga_id = warga.id', 'left');
    //     $this->db->order_by('warga.status_meninggal', 'ASC'); // aktif dulu
    //     $this->db->order_by('warga.nama_lengkap', 'ASC');
    //     return $this->db->get()->result();
    // }

     public function get_all() {
        $this->db->select('warga.*, kk.no_kk as no_kk, kk.id as kk_id_rel,
                           u.username, u.password,
                           u.status as status_akun,
                           u.role   as role_akun,
                           u.id     as user_id_akun');
        $this->db->from('warga');
        $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
        $this->db->join('users u', 'u.warga_id = warga.id', 'left');
        $this->db->order_by('warga.status_meninggal', 'ASC');
        $this->db->order_by('warga.nama_lengkap', 'ASC');
        return $this->db->get()->result();
    }

    public function get_by_id($id) {
        $this->db->select('warga.*, kk.no_kk as no_kk_kk, kk.id as kk_id_rel, u.username, u.password, u.status as status_akun');
        $this->db->from('warga');
        $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
        $this->db->join('users u', 'u.warga_id = warga.id', 'left');
        $this->db->where('warga.id', $id);
        return $this->db->get()->row();
    }

    public function get_by_nik($nik) {
        $this->db->where('nik', $nik);
        return $this->db->get('warga')->row();
    }

    public function get_by_user_id($user_id) {
        $this->db->select('warga.*, kk.no_kk as no_kk_kk, u.alamat, u.no_hp, u.email');
        $this->db->from('warga');
        $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
        $this->db->join('users u', 'u.id = warga.user_id', 'left');
        $this->db->where('warga.user_id', $user_id);
        return $this->db->get()->row();
    }

    /**
     * Ambil semua warga yang meninggal beserta info KK & nama kepala KK
     * Digunakan untuk banner/pill di dashboard warga
     */
    public function get_warga_meninggal() {
        $this->db->select('
            warga.id,
            warga.nama_lengkap,
            warga.tanggal_meninggal,
            warga.keterangan_meninggal,
            warga.kk_id,
            kk.no_kk,
            kepala.nama_lengkap as nama_kepala_kk
        ');
        $this->db->from('warga');
        $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
        $this->db->join('warga kepala', 'kepala.id = kk.kepala_keluarga_id', 'left');
        $this->db->where('warga.status_meninggal', 1);
        $this->db->order_by('warga.tanggal_meninggal', 'DESC');
        return $this->db->get()->result();
    }

    /**
     * Hitung total warga meninggal
     */
    public function count_meninggal() {
        $this->db->where('status_meninggal', 1);
        return $this->db->count_all_results('warga');
    }

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

    public function insert($data) {
        $warga_data = [
            'user_id'             => $data['user_id']             ?? null,
            'kk_id'               => $data['kk_id']               ?? null,
            'nik'                 => $data['nik']                  ?? null,
            'no_kk'               => $data['no_kk']               ?? null,
            'nama_lengkap'        => $data['nama_lengkap']         ?? null,
            'jenis_kelamin'       => $data['jenis_kelamin']        ?? null,
            'tempat_lahir'        => $data['tempat_lahir']         ?? null,
            'tanggal_lahir'       => !empty($data['tanggal_lahir']) ? $data['tanggal_lahir'] : null,
            'agama'               => $data['agama']                ?? null,
            'status_perkawinan'   => $data['status_perkawinan']    ?? null,
            'pekerjaan'           => $data['pekerjaan']            ?? null,
            'no_hp'               => $data['no_hp']                ?? null,
            'status_hubungan'     => $data['status_hubungan']      ?? 'lainnya',
            'status_domisili'     => $data['status_domisili']      ?? 'tetap',
            'keterangan_domisili' => $data['keterangan_domisili']  ?? null,
            'status_meninggal'    => $data['status_meninggal']     ?? 0,
        ];
        $this->db->insert('warga', $warga_data);
        return $this->db->insert_id();
    }

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

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

    public function search_warga_tanpa_kk($keyword) {
        $this->db->select('id, nik, nama_lengkap, jenis_kelamin');
        $this->db->from('warga');
        $this->db->where('(kk_id IS NULL OR kk_id = 0)', null, false);
        $this->db->where('nama_lengkap IS NOT NULL', null, false);
        $this->db->group_start();
            $this->db->like('nama_lengkap', $keyword);
            $this->db->or_like('nik', $keyword);
        $this->db->group_end();
        $this->db->limit(10);
        return $this->db->get()->result();
    }

    public function get_statistik_dashboard() {
        $statistik = [];

        $statistik['total_jiwa'] = $this->db->count_all('warga');
        $statistik['total_kk']   = $this->db->count_all('kartu_keluarga');

        $this->db->where('jenis_kelamin', 'L');
        $statistik['total_pria'] = $this->db->count_all_results('warga');

        $this->db->where('jenis_kelamin', 'P');
        $statistik['total_wanita'] = $this->db->count_all_results('warga');

        $this->db->where('jenis_kelamin IS NULL OR jenis_kelamin = ""', null, false);
        $statistik['total_tidak_diketahui'] = $this->db->count_all_results('warga');

        $this->db->select('status_domisili, COUNT(*) as jumlah');
        $this->db->from('warga');
        $this->db->group_by('status_domisili');
        $statistik['domisili'] = [];
        foreach ($this->db->get()->result() as $row) {
            $statistik['domisili'][$row->status_domisili] = $row->jumlah;
        }

        $this->db->select('status_hubungan, COUNT(*) as jumlah');
        $this->db->from('warga');
        $this->db->group_by('status_hubungan');
        $statistik['hubungan'] = [];
        foreach ($this->db->get()->result() as $row) {
            $statistik['hubungan'][$row->status_hubungan] = $row->jumlah;
        }

        $this->db->select('agama, COUNT(*) as jumlah');
        $this->db->from('warga');
        $this->db->where('agama IS NOT NULL AND agama != ""', null, false);
        $this->db->group_by('agama');
        $statistik['agama'] = [];
        foreach ($this->db->get()->result() as $row) {
            $statistik['agama'][$row->agama] = $row->jumlah;
        }

        $this->db->where('status_hubungan', 'kepala_keluarga');
        $statistik['total_kepala_keluarga'] = $this->db->count_all_results('warga');

        return $statistik;
    }

    public function get_status_hubungan_options() {
        return [
            'kepala_keluarga' => 'Kepala Keluarga',
            'istri'           => 'Istri',
            'anak'            => 'Anak',
            'cucu'            => 'Cucu',
            'orang_tua'       => 'Orang Tua',
            'mertua'          => 'Mertua',
            'famili_lain'     => 'Famili Lain',
            'pembantu'        => 'Pembantu',
            'lainnya'         => 'Lainnya',
        ];
    }

    public function get_status_domisili_options() {
        return [
            'tetap'     => 'Tetap',
            'kost'      => 'Kost',
            'kontrak'   => 'Kontrak',
            'pendatang' => 'Pendatang',
            'sementara' => 'Sementara',
        ];
    }

    public function get_status_warga_options() {
        return $this->get_status_domisili_options();
    }

    /**
     * Ambil semua warga yang berhak memilih dalam Pemilu:
     * - Usia >= 17 tahun ATAU sudah/pernah menikah
     * - Tidak meninggal
     */
    public function get_pemilih_pemilu() {
        $tanggal_batas = date('Y-m-d', strtotime('-17 years'));

        $this->db->select('
            warga.id,
            warga.nik,
            warga.nama_lengkap,
            warga.jenis_kelamin,
            warga.tempat_lahir,
            warga.tanggal_lahir,
            warga.status_perkawinan,
            warga.status_hubungan,
            warga.status_domisili,
            warga.keterangan_domisili,
            warga.kk_id,
            kk.no_kk,
            kepala.nama_lengkap as nama_kepala_kk
        ');
        $this->db->from('warga');
        $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
        $this->db->join('warga kepala', 'kepala.id = kk.kepala_keluarga_id', 'left');
        $this->db->where('(warga.status_meninggal = 0 OR warga.status_meninggal IS NULL)', null, false);
        $this->db->group_start();
            $this->db->where('warga.tanggal_lahir <=', $tanggal_batas);
            $this->db->or_where_in('warga.status_perkawinan', ['kawin', 'cerai_hidup', 'cerai_mati']);
        $this->db->group_end();
        $this->db->group_start();
            $this->db->where('warga.tanggal_lahir IS NOT NULL', null, false);
            $this->db->or_where_in('warga.status_perkawinan', ['kawin', 'cerai_hidup', 'cerai_mati']);
        $this->db->group_end();
        $this->db->order_by('warga.nama_lengkap', 'ASC');
        return $this->db->get()->result();
    }

    /**
     * Statistik ringkasan pemilih untuk stat cards
     */
    public function get_statistik_pemilih() {
        $pemilih = $this->get_pemilih_pemilu();
        $today   = new DateTime();

        $stat = [
            'total'       => count($pemilih),
            'total_warga' => $this->db->count_all('warga'),
            'pria'        => 0,
            'wanita'      => 0,
            'pemula'      => 0,
            'muda'        => 0,
            'dewasa'      => 0,
            'lansia'      => 0,
        ];

        foreach ($pemilih as $p) {
            if (($p->jenis_kelamin ?? '') === 'L') $stat['pria']++;
            else $stat['wanita']++;

            if (!empty($p->tanggal_lahir)) {
                $lahir = new DateTime($p->tanggal_lahir);
                $umur  = $today->diff($lahir)->y;
                if ($umur >= 17 && $umur <= 21)     $stat['pemula']++;
                elseif ($umur >= 22 && $umur <= 35) $stat['muda']++;
                elseif ($umur >= 36 && $umur <= 55) $stat['dewasa']++;
                elseif ($umur >= 56)                $stat['lansia']++;
            }
        }

        return $stat;
    }

    public function get_all_filter($filter) {
    $this->db->select('warga.*, kk.no_kk as no_kk, kk.id as kk_id_rel,
                       u.username, u.password,
                       u.status as status_akun,
                       u.role   as role_akun,
                       u.id     as user_id_akun,
                       u.no_hp  as no_hp');
    $this->db->from('warga');
    $this->db->join('kartu_keluarga kk', 'kk.id = warga.kk_id', 'left');
    $this->db->join('users u', 'u.warga_id = warga.id', 'left');

    switch ($filter) {
        case 'balita':
            $batas = date('Y-m-d', strtotime('-5 years'));
            $this->db->where('warga.tanggal_lahir >', $batas);
            $this->db->where('(warga.status_meninggal = 0 OR warga.status_meninggal IS NULL)', null, false);
            break;
        case 'lansia':
            $batas = date('Y-m-d', strtotime('-60 years'));
            $this->db->where('warga.tanggal_lahir <=', $batas);
            $this->db->where('(warga.status_meninggal = 0 OR warga.status_meninggal IS NULL)', null, false);
            break;
        case 'janda':
            $this->db->group_start();
                $this->db->like('warga.status_perkawinan', 'cerai');
                $this->db->or_where('warga.status_perkawinan', 'janda');
            $this->db->group_end();
            $this->db->where_in('warga.jenis_kelamin', ['P', 'p', 'Perempuan', 'perempuan']);
            $this->db->where('(warga.status_meninggal = 0 OR warga.status_meninggal IS NULL)', null, false);
            break;
        case 'duda':
            $this->db->group_start();
                $this->db->like('warga.status_perkawinan', 'cerai');
                $this->db->or_where('warga.status_perkawinan', 'duda');
            $this->db->group_end();
            $this->db->where_in('warga.jenis_kelamin', ['L', 'l', 'Laki-laki', 'laki-laki']);
            $this->db->where('(warga.status_meninggal = 0 OR warga.status_meninggal IS NULL)', null, false);
            break;
        case 'laki':
            $this->db->where_in('warga.jenis_kelamin', ['L', 'l', 'Laki-laki', 'laki-laki']);
            break;
        case 'perempuan':
            $this->db->where_in('warga.jenis_kelamin', ['P', 'p', 'Perempuan', 'perempuan']);
            break;
        case 'meninggal':
            $this->db->where('warga.status_meninggal', 1);
            break;
    }

    $this->db->order_by('warga.status_meninggal', 'ASC');
    $this->db->order_by('warga.nama_lengkap', 'ASC');
    return $this->db->get()->result();
}

    public function get_ulang_tahun_bulan_ini() {
        $bulan_ini = date('m');
        $this->db->select('id, nama_lengkap, tanggal_lahir, no_hp');
        $this->db->from('warga');
        $this->db->where('MONTH(tanggal_lahir)', $bulan_ini);
        $this->db->where('(status_meninggal = 0 OR status_meninggal IS NULL)', null, false);
        $this->db->order_by('DAY(tanggal_lahir)', 'ASC');
        return $this->db->get()->result();
    }
}