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

class Pinjaman_model extends CI_Model {

    // ==================== PENGAJUAN ====================
    public function get_all_pengajuan() {
        $this->db->select('pengajuan_pinjaman.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, jenis_pinjaman.nama_pinjaman');
        $this->db->join('warga', 'warga.id = pengajuan_pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id', 'left');
        $this->db->join('jenis_pinjaman', 'jenis_pinjaman.id = pengajuan_pinjaman.jenis_pinjaman_id');
        $this->db->order_by('pengajuan_pinjaman.created_at', 'DESC');
        return $this->db->get('pengajuan_pinjaman')->result();
    }

    public function get_pengajuan_by_id($id) {
        $this->db->select('pengajuan_pinjaman.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, warga.nik, users.alamat');
        $this->db->join('warga', 'warga.id = pengajuan_pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id', 'left');
        return $this->db->get_where('pengajuan_pinjaman', array('pengajuan_pinjaman.id' => $id))->row();
    }

    public function count_pengajuan_pending() {
        return $this->db->where('status_pengajuan', 'pending')->count_all_results('pengajuan_pinjaman');
    }

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

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

    // ==================== PINJAMAN AKTIF ====================
    public function get_pinjaman_aktif() {
        $this->db->select('pinjaman.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, users.no_hp, pengajuan_pinjaman.keperluan');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id', 'left');
        $this->db->join('pengajuan_pinjaman', 'pengajuan_pinjaman.id = pinjaman.pengajuan_id');
        $this->db->where('pinjaman.status_pinjaman', 'aktif');
        $this->db->order_by('pinjaman.created_at', 'DESC');
        return $this->db->get('pinjaman')->result();
    }

    public function get_pinjaman_by_id($id) {
        $this->db->select('pinjaman.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, warga.nik, users.alamat, users.no_hp, 
                          pengajuan_pinjaman.keperluan, pengajuan_pinjaman.jaminan, jenis_pinjaman.nama_pinjaman, jenis_pinjaman.bunga_persen');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id', 'left');
        $this->db->join('pengajuan_pinjaman', 'pengajuan_pinjaman.id = pinjaman.pengajuan_id');
        $this->db->join('jenis_pinjaman', 'jenis_pinjaman.id = pengajuan_pinjaman.jenis_pinjaman_id');
        return $this->db->get_where('pinjaman', array('pinjaman.id' => $id))->row();
    }

    public function count_pinjaman_aktif() {
        return $this->db->where('status_pinjaman', 'aktif')->count_all_results('pinjaman');
    }

    public function get_total_piutang() {
        $this->db->select_sum('sisa_pinjaman');
        $this->db->where('status_pinjaman', 'aktif');
        $query = $this->db->get('pinjaman');
        return $query->row()->sisa_pinjaman ?: 0;
    }

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

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

    // ==================== ANGSURAN ====================
    public function insert_angsuran($data) {
        return $this->db->insert('angsuran_pinjaman', $data);
    }

    public function get_angsuran_by_pinjaman($pinjaman_id) {
        $this->db->where('pinjaman_id', $pinjaman_id);
        $this->db->order_by('angsuran_ke', 'ASC');
        return $this->db->get('angsuran_pinjaman')->result();
    }

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

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

    // TAMBAHKAN METHOD INI
public function count_sisa_angsuran($pinjaman_id) {
    $this->db->where('pinjaman_id', $pinjaman_id);
    $this->db->where('status_bayar', 'belum');
    return $this->db->count_all_results('angsuran_pinjaman');
}

    public function lunas_semua_angsuran($pinjaman_id) {
        $this->db->where('pinjaman_id', $pinjaman_id);
        $this->db->update('angsuran_pinjaman', array(
            'status_bayar' => 'dibayar',
            'tanggal_bayar' => date('Y-m-d')
        ));
    }

    public function get_angsuran_bulan_ini() {
        $this->db->select_sum('nominal_angsuran');
        $this->db->where('MONTH(tanggal_jatuh_tempo)', date('m'));
        $this->db->where('YEAR(tanggal_jatuh_tempo)', date('Y'));
        $this->db->where('status_bayar', 'belum');
        $query = $this->db->get('angsuran_pinjaman');
        return $query->row()->nominal_angsuran ?: 0;
    }

    public function get_angsuran_jatuh_tempo($tanggal) {
        $this->db->select('angsuran_pinjaman.*, pinjaman.kode_pinjaman, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, users.no_hp');
        $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id', 'left');
        $this->db->where('tanggal_jatuh_tempo', $tanggal);
        $this->db->where('status_bayar', 'belum');
        return $this->db->get('angsuran_pinjaman')->result();
    }

 

    // ==================== RIWAYAT ====================
    public function insert_riwayat_pembayaran($data) {
        return $this->db->insert('riwayat_pembayaran_pinjaman', $data);
    }

    public function get_riwayat_pembayaran($pinjaman_id) {
        $this->db->select('riwayat_pembayaran_pinjaman.*, users.nama_lengkap as petugas');
        $this->db->join('users', 'users.id = riwayat_pembayaran_pinjaman.petugas_id');
        $this->db->where('pinjaman_id', $pinjaman_id);
        $this->db->order_by('tanggal_bayar', 'DESC');
        return $this->db->get('riwayat_pembayaran_pinjaman')->result();
    }

    // ==================== LAPORAN ====================
    public function get_statistik_periode($bulan, $tahun) {
        $this->db->select('
            COUNT(DISTINCT pinjaman.id) as total_pinjaman,
            SUM(pinjaman.nominal_disetujui) as total_disetujui,
            SUM(pinjaman.total_pinjaman - pinjaman.sisa_pinjaman) as total_terbayar,
            SUM(pinjaman.sisa_pinjaman) as sisa_piutang
        ');
        $this->db->where('MONTH(pinjaman.created_at)', $bulan);
        $this->db->where('YEAR(pinjaman.created_at)', $tahun);
        return $this->db->get('pinjaman')->row();
    }

    public function get_pinjaman_lunas_periode($bulan, $tahun) {
        $this->db->select('pinjaman.*, users.nama_lengkap');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('status_pinjaman', 'lunas');
        $this->db->where('MONTH(pinjaman.updated_at)', $bulan);
        $this->db->where('YEAR(pinjaman.updated_at)', $tahun);
        return $this->db->get('pinjaman')->result();
    }

    public function get_tunggakan($bulan, $tahun) {
        $this->db->select('angsuran_pinjaman.*, pinjaman.kode_pinjaman, users.nama_lengkap, users.no_hp');
        $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('angsuran_pinjaman.status_bayar', 'belum');
        $this->db->where('angsuran_pinjaman.tanggal_jatuh_tempo <', date('Y-m-d'));
        return $this->db->get('angsuran_pinjaman')->result();
    }

    public function get_pinjaman_macet() {
        $this->db->select('pinjaman.*, users.nama_lengkap, users.no_hp');
        $this->db->join('warga', 'warga.id = pinjaman.warga_id');
        $this->db->join('users', 'users.id = warga.user_id');
        $this->db->where('pinjaman.status_pinjaman', 'aktif');
        // Macet = ada angsuran terlambat lebih dari 90 hari
        $this->db->where('EXISTS (
            SELECT 1 FROM angsuran_pinjaman 
            WHERE pinjaman_id = pinjaman.id 
            AND status_bayar = "belum" 
            AND tanggal_jatuh_tempo < DATE_SUB(NOW(), INTERVAL 90 DAY)
        )');
        return $this->db->get('pinjaman')->result();
    }
    // ==================== METHOD UNTUK WARGA ====================
public function get_pengajuan_by_warga($warga_id) {
    $this->db->select('pengajuan_pinjaman.*, users.nama_lengkap, jenis_pinjaman.nama_pinjaman');
    $this->db->join('warga', 'warga.id = pengajuan_pinjaman.warga_id');
    $this->db->join('users', 'users.id = warga.user_id');
    $this->db->join('jenis_pinjaman', 'jenis_pinjaman.id = pengajuan_pinjaman.jenis_pinjaman_id');
    $this->db->where('pengajuan_pinjaman.warga_id', $warga_id);
    $this->db->order_by('pengajuan_pinjaman.created_at', 'DESC');
    return $this->db->get('pengajuan_pinjaman')->result();
}

public function get_pinjaman_by_warga($warga_id) {
    $this->db->select('pinjaman.*, users.nama_lengkap, users.no_hp, pengajuan_pinjaman.keperluan');
    $this->db->join('warga', 'warga.id = pinjaman.warga_id');
    $this->db->join('users', 'users.id = warga.user_id');
    $this->db->join('pengajuan_pinjaman', 'pengajuan_pinjaman.id = pinjaman.pengajuan_id');
    $this->db->where('pinjaman.warga_id', $warga_id);
    $this->db->order_by('pinjaman.created_at', 'DESC');
    return $this->db->get('pinjaman')->result();
}

public function count_pinjaman_aktif_by_warga($warga_id) {
    $this->db->where('status_pinjaman', 'aktif');
    $this->db->where('warga_id', $warga_id);
    return $this->db->count_all_results('pinjaman');
}

public function count_pengajuan_pending_by_warga($warga_id) {
    $this->db->where('status_pengajuan', 'pending');
    $this->db->where('warga_id', $warga_id);
    return $this->db->count_all_results('pengajuan_pinjaman');
}

public function get_total_piutang_by_warga($warga_id) {
    $this->db->select_sum('sisa_pinjaman');
    $this->db->where('status_pinjaman', 'aktif');
    $this->db->where('warga_id', $warga_id);
    $query = $this->db->get('pinjaman');
    return $query->row()->sisa_pinjaman ?: 0;
}

public function get_angsuran_bulan_ini_by_warga($warga_id) {
    $this->db->select_sum('angsuran_pinjaman.nominal_angsuran');
    $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
    $this->db->where('pinjaman.warga_id', $warga_id);
    $this->db->where('MONTH(angsuran_pinjaman.tanggal_jatuh_tempo)', date('m'));
    $this->db->where('YEAR(angsuran_pinjaman.tanggal_jatuh_tempo)', date('Y'));
    $this->db->where('angsuran_pinjaman.status_bayar', 'belum');
    $query = $this->db->get('angsuran_pinjaman');
    return $query->row()->nominal_angsuran ?: 0;
}

public function get_pinjaman_macet_by_warga($warga_id) {
    $this->db->select('pinjaman.*, users.nama_lengkap, users.no_hp');
    $this->db->join('warga', 'warga.id = pinjaman.warga_id');
    $this->db->join('users', 'users.id = warga.user_id');
    $this->db->where('pinjaman.status_pinjaman', 'aktif');
    $this->db->where('pinjaman.warga_id', $warga_id);
    $this->db->where('EXISTS (
        SELECT 1 FROM angsuran_pinjaman 
        WHERE pinjaman_id = pinjaman.id 
        AND status_bayar = "belum" 
        AND tanggal_jatuh_tempo < DATE_SUB(NOW(), INTERVAL 90 DAY)
    )');
    return $this->db->get('pinjaman')->result();
}

public function get_angsuran_jatuh_tempo_by_warga($tanggal, $warga_id) {
    $this->db->select('angsuran_pinjaman.*, pinjaman.kode_pinjaman, users.nama_lengkap, users.no_hp');
    $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
    $this->db->join('warga', 'warga.id = pinjaman.warga_id');
    $this->db->join('users', 'users.id = warga.user_id');
    $this->db->where('angsuran_pinjaman.tanggal_jatuh_tempo', $tanggal);
    $this->db->where('angsuran_pinjaman.status_bayar', 'belum');
    $this->db->where('pinjaman.warga_id', $warga_id);
    return $this->db->get('angsuran_pinjaman')->result();
}
// Method tambahan untuk laporan warga
public function get_angsuran_bulan_ini_detail_by_warga($warga_id) {
    $this->db->select('angsuran_pinjaman.*, pinjaman.kode_pinjaman');
    $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
    $this->db->where('pinjaman.warga_id', $warga_id);
    $this->db->where('MONTH(angsuran_pinjaman.tanggal_jatuh_tempo)', date('m'));
    $this->db->where('YEAR(angsuran_pinjaman.tanggal_jatuh_tempo)', date('Y'));
    $this->db->order_by('angsuran_pinjaman.tanggal_jatuh_tempo', 'ASC');
    return $this->db->get('angsuran_pinjaman')->result();
}

public function get_tunggakan_by_warga($warga_id) {
    $this->db->select('angsuran_pinjaman.*, pinjaman.kode_pinjaman');
    $this->db->join('pinjaman', 'pinjaman.id = angsuran_pinjaman.pinjaman_id');
    $this->db->where('pinjaman.warga_id', $warga_id);
    $this->db->where('angsuran_pinjaman.status_bayar', 'belum');
    $this->db->where('angsuran_pinjaman.tanggal_jatuh_tempo <', date('Y-m-d'));
    $this->db->order_by('angsuran_pinjaman.tanggal_jatuh_tempo', 'ASC');
    return $this->db->get('angsuran_pinjaman')->result();
}

// Ganti semua method yang menggunakan 'pembayaran_pending' menjadi 'pengajuan_bayar_angsuran'
// ==================== METHOD UNTUK PEMBAYARAN PENDING ====================

public function insert_pembayaran_pending($data) {
    // Hitung denda jika ada
    $denda = $data['denda'] ?? 0;
    
    $insert_data = [
        'kode_bayar' => 'BYR-' . date('Ymd') . '-' . rand(100, 999),
        'pinjaman_id' => $data['pinjaman_id'] ?? null,
        'angsuran_id' => $data['angsuran_id'],
        'warga_id' => $data['warga_id'],
        'nominal_bayar' => $data['nominal_bayar'],
        'denda' => $denda, // Tambahkan ini
        'metode_bayar' => $data['metode'] ?? 'transfer',
        'tujuan_transfer' => $data['tujuan_transfer'] ?? null,
        'rekening_pengirim' => $data['rekening_pengirim'] ?? null,
        'catatan_warga' => $data['keterangan'] ?? null,
        'foto_bukti' => $data['bukti_transfer'] ?? null,
        'status' => 'pending',
        'created_at' => date('Y-m-d H:i:s'),
        'updated_at' => date('Y-m-d H:i:s')
    ];
    
    return $this->db->insert('pengajuan_bayar_angsuran', $insert_data);
}

public function get_pembayaran_pending_by_warga($warga_id) {
    $this->db->select('pba.*, pinjaman.kode_pinjaman, angsuran_pinjaman.angsuran_ke');
    $this->db->from('pengajuan_bayar_angsuran pba');
    $this->db->join('pinjaman', 'pinjaman.id = pba.pinjaman_id');
    $this->db->join('angsuran_pinjaman', 'angsuran_pinjaman.id = pba.angsuran_id');
    $this->db->where('pba.warga_id', $warga_id);
    $this->db->where('pba.status', 'pending');
    $this->db->order_by('pba.created_at', 'DESC');
    return $this->db->get()->result();
}

public function get_pembayaran_pending_by_id($id) {
    $this->db->select('pba.*, angsuran_pinjaman.angsuran_ke, angsuran_pinjaman.nominal_angsuran, 
                       pinjaman.kode_pinjaman, pinjaman.warga_id, pinjaman.sisa_pinjaman,
                       IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, users.no_hp');
    $this->db->from('pengajuan_bayar_angsuran pba');
    $this->db->join('angsuran_pinjaman', 'angsuran_pinjaman.id = pba.angsuran_id');
    $this->db->join('pinjaman', 'pinjaman.id = pba.pinjaman_id');
    $this->db->join('warga', 'warga.id = pba.warga_id');
    $this->db->join('users', 'users.id = warga.user_id', 'left');
    $this->db->where('pba.id', $id);
    return $this->db->get()->row();
}

public function update_pembayaran_pending($id, $data) {
    $data['updated_at'] = date('Y-m-d H:i:s');
    $this->db->where('id', $id);
    return $this->db->update('pengajuan_bayar_angsuran', $data);
}

public function get_pending_by_angsuran($angsuran_id) {
    return $this->db->get_where('pengajuan_bayar_angsuran', [
        'angsuran_id' => $angsuran_id,
        'status' => 'pending'
    ])->row();
}

public function get_all_pembayaran_pending() {
    $this->db->select('pba.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, pinjaman.kode_pinjaman, 
                       angsuran_pinjaman.angsuran_ke, angsuran_pinjaman.nominal_angsuran');
    $this->db->from('pengajuan_bayar_angsuran pba');
    $this->db->join('warga', 'warga.id = pba.warga_id');
    $this->db->join('users', 'users.id = warga.user_id', 'left');
    $this->db->join('pinjaman', 'pinjaman.id = pba.pinjaman_id');
    $this->db->join('angsuran_pinjaman', 'angsuran_pinjaman.id = pba.angsuran_id');
    $this->db->where('pba.status', 'pending');
    $this->db->order_by('pba.created_at', 'DESC');
    return $this->db->get()->result();
}

public function get_pembayaran_pending_by_pinjaman($pinjaman_id) {
    $this->db->select('pba.*, ap.angsuran_ke');
    $this->db->from('pengajuan_bayar_angsuran pba');
    $this->db->join('angsuran_pinjaman ap', 'ap.id = pba.angsuran_id');
    $this->db->where('pba.pinjaman_id', $pinjaman_id);
    $this->db->where('pba.status', 'pending');
    $this->db->order_by('pba.created_at', 'DESC');
    
    $result = $this->db->get()->result();
    
    // Tambahkan field total_bayar agar kompatibel dengan view
    foreach($result as $row) {
        $row->total_bayar = $row->nominal_bayar;
    }
    
    return $result;
}
/**
 * Hitung total pembayaran pending untuk notifikasi admin
 */
public function count_pembayaran_pending() {
    return $this->db->where('status', 'pending')
                    ->count_all_results('pengajuan_bayar_angsuran');
}

/**
 * Ambil pembayaran pending terbaru untuk dashboard admin
 */
public function get_pembayaran_pending_terbaru($limit = 5) {
    $this->db->select('pba.*, IFNULL(users.nama_lengkap, warga.nama_lengkap) as nama_lengkap, pinjaman.kode_pinjaman, angsuran_pinjaman.angsuran_ke');
    $this->db->from('pengajuan_bayar_angsuran pba');
    $this->db->join('warga', 'warga.id = pba.warga_id');
    $this->db->join('users', 'users.id = warga.user_id', 'left');
    $this->db->join('pinjaman', 'pinjaman.id = pba.pinjaman_id');
    $this->db->join('angsuran_pinjaman', 'angsuran_pinjaman.id = pba.angsuran_id');
    $this->db->where('pba.status', 'pending');
    $this->db->order_by('pba.created_at', 'DESC');
    $this->db->limit($limit);
    return $this->db->get()->result();
}
    public function count_pending_verifikasi() {
    return $this->db->where('status', 'pending')
                    ->count_all_results('pembayaran_online');
}

public function get_pending_payments_with_detail($limit = null) {
    $this->db->select('po.*, w.nama_lengkap, p.kode_pinjaman, a.angsuran_ke');
    $this->db->from('pembayaran_online po');
    $this->db->join('angsuran a', 'a.id = po.angsuran_id');
    $this->db->join('pinjaman p', 'p.id = a.pinjaman_id');
    $this->db->join('warga w', 'w.id = p.warga_id');
    $this->db->where('po.status', 'pending');
    $this->db->order_by('po.created_at', 'ASC');
    
    if($limit) {
        $this->db->limit($limit);
    }
    
    return $this->db->get()->result();
}

public function get_total_dicairkan() {
    $this->db->select_sum('nominal_disetujui');
    $this->db->where('status_pinjaman !=', 'pending');
    $query = $this->db->get('pinjaman');
    return $query->row()->nominal_disetujui ?: 0;
}

public function get_sisa_pinjaman_by_warga($warga_id) {
    $this->db->select_sum('sisa_pinjaman');
    $this->db->where('warga_id', $warga_id);
    $this->db->where('status_pinjaman', 'aktif');
    $query = $this->db->get('pinjaman');
    return $query->row()->sisa_pinjaman ?: 0;
}
public function cek_jenis_digunakan($jenis_id) {
    // Cek apakah ada pengajuan atau pinjaman yang menggunakan jenis ini
    $this->db->where('jenis_pinjaman_id', $jenis_id);
    $pengajuan = $this->db->count_all_results('pengajuan_pinjaman');
    
    if ($pengajuan > 0) return true;
    
    // Cek di tabel pinjaman juga
    $this->db->where('jenis_pinjaman_id', $jenis_id);
    $pinjaman = $this->db->count_all_results('pinjaman');
    
    return $pinjaman > 0;
}

}