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

class Surat_model extends CI_Model {

    private $table = 'permohonan_surat';

    public function get_all() {
        $this->db->select('ps.id as id, ps.*, IFNULL(u.nama_lengkap, w.nama_lengkap) as nama_lengkap, w.nik, js.nama_surat as jenis_surat');
        $this->db->from($this->table . ' ps');
        $this->db->join('warga w', 'w.id = ps.warga_id', 'left');
        $this->db->join('users u', 'u.id = w.user_id', 'left');
        $this->db->join('jenis_surat js', 'js.id = ps.jenis_surat_id', 'left');
        $this->db->order_by('ps.created_at', 'DESC');
        return $this->db->get()->result();
    }

    public function get_by_id($id) {
        // FIX: ps.id as id harus PERTAMA agar tidak di-override oleh kolom id dari tabel JOIN (warga)
        // Semua kolom warga di-select eksplisit (tidak pakai w.*) untuk menghindari ambiguitas
        $this->db->select('ps.id as id, ps.*');
        $this->db->select('w.id as warga_id_ref, w.nik, w.nama_lengkap as nama_warga, w.tempat_lahir, w.tanggal_lahir,
                           w.jenis_kelamin, w.agama, w.pekerjaan, w.status_perkawinan,
                           w.kewarganegaraan, w.status_domisili, w.foto_ktp');
        $this->db->select('IFNULL(u.nama_lengkap, w.nama_lengkap) as nama_lengkap, u.alamat, u.no_hp');
        $this->db->select('js.nama_surat as jenis_surat, js.kode_surat');
        $this->db->from($this->table . ' ps');
        $this->db->join('warga w', 'w.id = ps.warga_id', 'left');
        $this->db->join('users u', 'u.id = w.user_id', 'left');
        $this->db->join('jenis_surat js', 'js.id = ps.jenis_surat_id', 'left');
        $this->db->where('ps.id', $id);
        return $this->db->get()->row();
    }

    public function get_by_user($user_id) {
        $this->db->select('id');
        $warga = $this->db->get_where('warga', array('user_id' => $user_id))->row();

        if(!$warga) {
            return array();
        }

        // FIX: ps.id as id duluan agar tidak di-override
        $this->db->select('ps.id as id, ps.*');
        $this->db->select('IFNULL(u.nama_lengkap, w.nama_lengkap) as nama_lengkap');
        $this->db->select('js.nama_surat as jenis_surat');
        $this->db->select('ps.keperluan, ps.tanggal_permohonan as tanggal_pengajuan');
        $this->db->select('ps.status, ps.keterangan_penolakan as catatan_admin, ps.nomor_surat');
        $this->db->from($this->table . ' ps');
        $this->db->join('warga w', 'w.id = ps.warga_id', 'left');
        $this->db->join('users u', 'u.id = w.user_id', 'left');
        $this->db->join('jenis_surat js', 'js.id = ps.jenis_surat_id', 'left');
        $this->db->where('ps.warga_id', $warga->id);
        $this->db->order_by('ps.created_at', 'DESC');
        return $this->db->get()->result();
    }

    public function get_by_status($status) {
        $this->db->select('ps.id as id, ps.*, IFNULL(u.nama_lengkap, w.nama_lengkap) as nama_lengkap, w.nik, js.nama_surat as jenis_surat');
        $this->db->from($this->table . ' ps');
        $this->db->join('warga w', 'w.id = ps.warga_id', 'left');
        $this->db->join('users u', 'u.id = w.user_id', 'left');
        $this->db->join('jenis_surat js', 'js.id = ps.jenis_surat_id', 'left');
        $this->db->where('ps.status', $status);
        $this->db->order_by('ps.created_at', '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 count_by_status($status) {
        $this->db->where('status', $status);
        return $this->db->count_all_results($this->table);
    }

    public function generate_nomor_surat($jenis_surat_id) {
        $jenis = $this->db->get_where('jenis_surat', array('id' => $jenis_surat_id))->row();
        if(!$jenis) {
            return false;
        }

        $kode         = $jenis->kode_surat;
        $bulan_romawi = $this->get_bulan_romawi(date('n'));
        $tahun        = date('Y');

        $this->db->select('nomor_surat');
        $this->db->like('nomor_surat', "/{$kode}/RT");
        $this->db->like('nomor_surat', "/{$bulan_romawi}/{$tahun}");
        $this->db->order_by('id', 'DESC');
        $last = $this->db->get($this->table)->row();

        if($last && $last->nomor_surat) {
            $parts      = explode('/', $last->nomor_surat);
            $nomor_urut = intval($parts[0]) + 1;
        } else {
            $nomor_urut = 1;
        }

        $nomor_urut_formatted = str_pad($nomor_urut, 3, '0', STR_PAD_LEFT);

        return "{$nomor_urut_formatted}/{$kode}/RT.001/{$bulan_romawi}/{$tahun}";
    }

    private function get_bulan_romawi($bulan) {
        $romawi = array(
            1 => 'I',   2 => 'II',   3 => 'III', 4 => 'IV',
            5 => 'V',   6 => 'VI',   7 => 'VII', 8 => 'VIII',
            9 => 'IX', 10 => 'X',   11 => 'XI', 12 => 'XII'
        );
        return isset($romawi[$bulan]) ? $romawi[$bulan] : 'I';
    }

    public function get_pending_count() {
        return $this->count_by_status('pending');
    }

    public function get_approved_count() {
        return $this->count_by_status('disetujui');
    }

    public function get_rejected_count() {
        return $this->count_by_status('ditolak');
    }

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