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

class Arisan_model extends CI_Model {

    // ==================== MASTER ARISAN ====================
    public function get_all_arisan() {
        $this->db->order_by('created_at', 'DESC');
        return $this->db->get('arisan')->result();
    }

    public function get_arisan_aktif() {
        $this->db->where('status', 'aktif');
        $this->db->order_by('created_at', 'DESC');
        return $this->db->get('arisan')->result();
    }

    public function get_arisan_by_id($id) {
        return $this->db->get_where('arisan', array('id' => $id))->row();
    }

    public function insert_arisan($data) {
        $this->db->insert('arisan', $data);
        return $this->db->insert_id();
    }

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

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

    public function count_arisan_aktif() {
        return $this->db->where('status', 'aktif')->count_all_results('arisan');
    }

    // ==================== PESERTA ARISAN ====================
    public function get_peserta_by_arisan($arisan_id) {
        $this->db->select('arisan_peserta.*, users.nama_lengkap, users.no_hp, warga.nik');
        $this->db->join('warga', 'warga.id = arisan_peserta.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('arisan_peserta.arisan_id', $arisan_id);
        $this->db->where('arisan_peserta.status_peserta', 'aktif');
        $this->db->order_by('arisan_peserta.nomor_urut', 'ASC');
        return $this->db->get('arisan_peserta')->result();
    }

    public function get_peserta_by_id($id) {
        $this->db->select('arisan_peserta.*, users.nama_lengkap, warga.nik');
        $this->db->join('warga', 'warga.id = arisan_peserta.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('arisan_peserta.id', $id);
        return $this->db->get('arisan_peserta')->row();
    }

    public function insert_peserta($data) {
        $this->db->insert('arisan_peserta', $data);
        return $this->db->insert_id();
    }

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

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

    public function count_peserta_by_arisan($arisan_id) {
        $this->db->where('arisan_id', $arisan_id);
        $this->db->where('status_peserta', 'aktif');
        return $this->db->count_all_results('arisan_peserta');
    }

    public function cek_warga_sudah_terdaftar($arisan_id, $warga_id) {
        $this->db->where('arisan_id', $arisan_id);
        $this->db->where('warga_id', $warga_id);
        $this->db->where('status_peserta', 'aktif');
        return $this->db->count_all_results('arisan_peserta') > 0;
    }

    public function get_warga_belum_terdaftar($arisan_id) {
        $this->db->select('warga.id, warga.nik, users.nama_lengkap, users.no_hp, users.alamat');
        $this->db->from('warga');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('warga.id NOT IN (
            SELECT warga_id FROM arisan_peserta 
            WHERE arisan_id = ' . (int)$arisan_id . ' 
            AND status_peserta = "aktif"
        )');
        $this->db->order_by('users.nama_lengkap', 'ASC');
        return $this->db->get()->result();
    }

    // ==================== PERTEMUAN ARISAN ====================
    public function get_pertemuan_by_arisan($arisan_id) {
        $this->db->select('arisan_pertemuan.*, 
                          tuan_rumah.nama_lengkap as nama_tuan_rumah,
                          pemenang.nama_lengkap as nama_pemenang');
        $this->db->join('warga as w_tuan', 'w_tuan.id = arisan_pertemuan.warga_id_tuan_rumah', 'left');
        $this->db->join('users as tuan_rumah', 'tuan_rumah.id = w_tuan.user_id', 'left');
        $this->db->join('warga as w_pemenang', 'w_pemenang.id = arisan_pertemuan.pemenang_warga_id', 'left');
        $this->db->join('users as pemenang', 'pemenang.id = w_pemenang.user_id', 'left');
        $this->db->where('arisan_pertemuan.arisan_id', $arisan_id);
        $this->db->order_by('arisan_pertemuan.pertemuan_ke', 'DESC');
        return $this->db->get('arisan_pertemuan')->result();
    }

    public function get_pertemuan_by_id($id) {
        $this->db->select('arisan_pertemuan.*, 
                          arisan.nama_arisan, arisan.nominal_iuran,
                          tuan_rumah.nama_lengkap as nama_tuan_rumah,
                          pemenang.nama_lengkap as nama_pemenang');
        $this->db->join('arisan', 'arisan.id = arisan_pertemuan.arisan_id');
        $this->db->join('warga as w_tuan', 'w_tuan.id = arisan_pertemuan.warga_id_tuan_rumah', 'left');
        $this->db->join('users as tuan_rumah', 'tuan_rumah.id = w_tuan.user_id', 'left');
        $this->db->join('warga as w_pemenang', 'w_pemenang.id = arisan_pertemuan.pemenang_warga_id', 'left');
        $this->db->join('users as pemenang', 'pemenang.id = w_pemenang.user_id', 'left');
        $this->db->where('arisan_pertemuan.id', $id);
        return $this->db->get('arisan_pertemuan')->row();
    }

    public function insert_pertemuan($data) {
        $this->db->insert('arisan_pertemuan', $data);
        return $this->db->insert_id();
    }

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

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

    public function get_pertemuan_terakhir($arisan_id) {
        $this->db->where('arisan_id', $arisan_id);
        $this->db->order_by('pertemuan_ke', 'DESC');
        $this->db->limit(1);
        return $this->db->get('arisan_pertemuan')->row();
    }

    // ==================== IURAN ARISAN ====================
    public function get_iuran_by_pertemuan($pertemuan_id) {
        $this->db->select('arisan_iuran.*, users.nama_lengkap, arisan_peserta.nomor_urut');
        $this->db->join('warga', 'warga.id = arisan_iuran.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->join('arisan_peserta', 'arisan_peserta.id = arisan_iuran.peserta_id');
        $this->db->where('arisan_iuran.pertemuan_id', $pertemuan_id);
        $this->db->order_by('arisan_peserta.nomor_urut', 'ASC');
        return $this->db->get('arisan_iuran')->result();
    }

    public function insert_iuran($data) {
        $this->db->insert('arisan_iuran', $data);
        return $this->db->insert_id();
    }

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

    public function generate_iuran_pertemuan($pertemuan_id, $arisan_id, $nominal) {
        $peserta = $this->get_peserta_by_arisan($arisan_id);
        foreach($peserta as $p) {
            $data = array(
                'pertemuan_id' => $pertemuan_id,
                'peserta_id'   => $p->id,
                'warga_id'     => $p->warga_id,
                'nominal_bayar'=> $nominal,
                'tanggal_bayar'=> date('Y-m-d'),
                'status_bayar' => 'belum'
            );
            $this->insert_iuran($data);
        }
    }

    // ==================== RIWAYAT PEMENANG ====================
    public function get_riwayat_pemenang($arisan_id) {
        $this->db->select('arisan_riwayat_pemenang.*, users.nama_lengkap, arisan_pertemuan.pertemuan_ke');
        $this->db->join('warga', 'warga.id = arisan_riwayat_pemenang.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->join('arisan_pertemuan', 'arisan_pertemuan.id = arisan_riwayat_pemenang.pertemuan_id');
        $this->db->where('arisan_riwayat_pemenang.arisan_id', $arisan_id);
        $this->db->order_by('arisan_riwayat_pemenang.tanggal_dapat', 'DESC');
        return $this->db->get('arisan_riwayat_pemenang')->result();
    }

    public function get_riwayat_pemenang_by_id($id) {
        $this->db->select('arisan_riwayat_pemenang.*, users.nama_lengkap, warga.id as warga_id_ref');
        $this->db->join('warga', 'warga.id = arisan_riwayat_pemenang.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('arisan_riwayat_pemenang.id', $id);
        return $this->db->get('arisan_riwayat_pemenang')->row();
    }

    public function insert_riwayat_pemenang($data) {
        $this->db->insert('arisan_riwayat_pemenang', $data);
        return $this->db->insert_id();
    }

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

    public function get_peserta_belum_dapat($arisan_id) {
        $this->db->select('arisan_peserta.*, users.nama_lengkap, warga.nik');
        $this->db->join('warga', 'warga.id = arisan_peserta.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('arisan_peserta.arisan_id', $arisan_id);
        $this->db->where('arisan_peserta.status_peserta', 'aktif');
        $this->db->where('arisan_peserta.id NOT IN (
            SELECT DISTINCT arisan_peserta.id FROM arisan_peserta
            INNER JOIN arisan_riwayat_pemenang ON arisan_riwayat_pemenang.warga_id = arisan_peserta.warga_id
            WHERE arisan_riwayat_pemenang.arisan_id = ' . (int)$arisan_id . '
        )');
        $this->db->order_by('arisan_peserta.nomor_urut', 'ASC');
        return $this->db->get('arisan_peserta')->result();
    }

    // ==================== KAS ARISAN ====================

    /**
     * Catat iuran masuk ke kas_rt (pemasukan)
     */
    public function catat_iuran_ke_kas($arisan, $peserta_nama, $pertemuan_ke, $nominal, $kas_id, $user_id) {
        $data_kas = array(
            'tanggal'     => date('Y-m-d'),
            'jenis'       => 'pemasukan',
            'kategori'    => 'Arisan',
            'master_kas_id' => $kas_id,
            'nominal'     => $nominal,
            'keterangan'  => 'Iuran arisan ' . $arisan->nama_arisan . ' - ' . $peserta_nama . ' (Pertemuan Ke-' . $pertemuan_ke . ')',
            'input_oleh'  => $user_id,
            'created_at'  => date('Y-m-d H:i:s')
        );
        $this->db->insert('kas_rt', $data_kas);
        $kas_rt_id = $this->db->insert_id();

        // Update saldo_saat_ini di master_kas menggunakan model resmi
        $this->load->model('Master_kas_model');
        $this->Master_kas_model->update_saldo($kas_id);

        return $kas_rt_id;
    }

    /**
     * Catat pembayaran dana ke pemenang di kas_rt (pengeluaran)
     */
    public function catat_pemenang_ke_kas($arisan, $pemenang_nama, $pertemuan_ke, $nominal, $kas_id, $user_id) {
        $data_kas = array(
            'tanggal'     => date('Y-m-d'),
            'jenis'       => 'pengeluaran',
            'kategori'    => 'Arisan',
            'master_kas_id' => $kas_id,
            'nominal'     => $nominal,
            'keterangan'  => 'Pembayaran arisan ' . $arisan->nama_arisan . ' ke pemenang ' . $pemenang_nama . ' (Pertemuan Ke-' . $pertemuan_ke . ')',
            'input_oleh'  => $user_id,
            'created_at'  => date('Y-m-d H:i:s')
        );
        $this->db->insert('kas_rt', $data_kas);
        $kas_rt_id = $this->db->insert_id();

        // Update saldo_saat_ini di master_kas menggunakan model resmi
        $this->load->model('Master_kas_model');
        $this->Master_kas_model->update_saldo($kas_id);

        return $kas_rt_id;
    }

    /**
     * Ambil daftar master_kas yang aktif dan bukan tabungan
     * untuk dropdown pilih kas arisan
     */
    public function get_master_kas_aktif() {
        $this->db->where('status', 'aktif');
        $this->db->where('is_tabungan', 0);
        $this->db->order_by('nama', 'ASC');
        return $this->db->get('master_kas')->result();
    }

    public function get_master_kas_by_id($id) {
        return $this->db->get_where('master_kas', array('id' => $id))->row();
    }

    // ==================== STATISTIK ====================
    public function get_total_iuran_terkumpul($arisan_id) {
        $this->db->select_sum('nominal_bayar');
        $this->db->join('arisan_pertemuan', 'arisan_pertemuan.id = arisan_iuran.pertemuan_id');
        $this->db->where('arisan_pertemuan.arisan_id', $arisan_id);
        $this->db->where('arisan_iuran.status_bayar', 'lunas');
        $query = $this->db->get('arisan_iuran');
        return $query->row()->nominal_bayar ?: 0;
    }

    public function count_pertemuan_by_arisan($arisan_id) {
        $this->db->where('arisan_id', $arisan_id);
        $this->db->where('status_pertemuan', 'terlaksana');
        return $this->db->count_all_results('arisan_pertemuan');
    }
// ==================== EDIT PERTEMUAN HELPERS ====================
/**
 * Get peserta yang belum dapat arisan, tapi tetap include pemenang saat ini (untuk edit)
 */
public function get_peserta_belum_dapat_for_edit($arisan_id, $current_pemenang_id = null) {
    $this->db->select('arisan_peserta.*, users.nama_lengkap, warga.nik');
    $this->db->join('warga', 'warga.id = arisan_peserta.warga_id');
    $this->db->join('users', 'users.id = warga.user_id');
    $this->db->where('arisan_peserta.arisan_id', $arisan_id);
    $this->db->where('arisan_peserta.status_peserta', 'aktif');
    
    // Subquery untuk cari yang sudah menang (kecuali pemenang saat ini)
    $exclude_sql = 'arisan_peserta.id NOT IN (
        SELECT DISTINCT arisan_peserta.id FROM arisan_peserta
        INNER JOIN arisan_riwayat_pemenang ON arisan_riwayat_pemenang.warga_id = arisan_peserta.warga_id
        WHERE arisan_riwayat_pemenang.arisan_id = ' . (int)$arisan_id;
    
    if($current_pemenang_id) {
        $exclude_sql .= ' AND arisan_riwayat_pemenang.warga_id != ' . (int)$current_pemenang_id;
    }
    $exclude_sql .= ')';
    
    $this->db->where($exclude_sql);
    
    // Include pemenang saat ini jika ada
    if($current_pemenang_id) {
        $this->db->or_where('arisan_peserta.warga_id', $current_pemenang_id);
    }
    
    $this->db->order_by('arisan_peserta.nomor_urut', 'ASC');
    return $this->db->get('arisan_peserta')->result();
}
    
}