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

class Reset_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    /**
     * Hitung user non-admin (yang akan dihapus)
     */
    public function count_user_non_admin() {
        return $this->db->where_not_in('role', ['admin', 'ketua_rt', 'sekretaris', 'bendahara'])
                        ->where('id !=', 1)
                        ->count_all_results('users');
    }

    /**
     * Get list admin IDs untuk protection
     */
    private function _get_admin_ids() {
        $this->db->select('id');
        $this->db->where_in('role', ['admin', 'ketua_rt', 'sekretaris', 'bendahara']);
        $this->db->or_where('id', 1);
        $query = $this->db->get('users');

        $ids = [];
        foreach ($query->result() as $row) {
            $ids[] = $row->id;
        }
        return $ids;
    }

    /**
     * Get warga IDs yang akan dihapus (milik non-admin)
     */
    private function _get_warga_to_delete($admin_ids) {
        $this->db->select('id');
        $this->db->where_not_in('user_id', $admin_ids);
        $this->db->or_where('user_id IS NULL', NULL, FALSE);
        $query = $this->db->get('warga');
        return array_column($query->result_array(), 'id');
    }

    /**
     * Hapus semua tabel yang bergantung pada warga_id tertentu
     * Urutan penting: child dulu, baru parent
     *
     * FK yang TIDAK CASCADE (harus dihapus manual):
     *   - pembayaran_iuran.warga_id
     *   - pengajuan_pinjaman.warga_id  → pinjaman bergantung padanya
     *   - pinjaman.warga_id            → angsuran_pinjaman, riwayat_pembayaran_pinjaman, pengajuan_bayar_angsuran bergantung
     *   - tabungan.warga_id            → transaksi_tabungan bergantung
     *   - permohonan_surat.warga_id
     *   - pengaduan.warga_id
     *   - pembayaran_pending.warga_id
     *
     * FK yang CASCADE (otomatis terhapus saat warga dihapus):
     *   - arisan_iuran.warga_id
     *   - arisan_peserta.warga_id
     *   - arisan_riwayat_pemenang.warga_id
     */
    private function _hapus_dependensi_warga($warga_ids) {
        if (empty($warga_ids)) return;

        // Ambil pinjaman_id milik warga tersebut untuk hapus anaknya dulu
        $this->db->select('id');
        $this->db->where_in('warga_id', $warga_ids);
        $pinjaman_query = $this->db->get('pinjaman');
        $pinjaman_ids = array_column($pinjaman_query->result_array(), 'id');

        // Ambil tabungan_id milik warga tersebut
        $this->db->select('id');
        $this->db->where_in('warga_id', $warga_ids);
        $tabungan_query = $this->db->get('tabungan');
        $tabungan_ids = array_column($tabungan_query->result_array(), 'id');

        // Ambil pengajuan_pinjaman_id milik warga tersebut
        $this->db->select('id');
        $this->db->where_in('warga_id', $warga_ids);
        $pengajuan_query = $this->db->get('pengajuan_pinjaman');
        $pengajuan_ids = array_column($pengajuan_query->result_array(), 'id');

        // ---------- Hapus cucu-cucu (level terdalam) dulu ----------

        // riwayat_pembayaran_pinjaman → references angsuran_pinjaman & pinjaman
        if (!empty($pinjaman_ids)) {
            $this->db->where_in('pinjaman_id', $pinjaman_ids)->delete('riwayat_pembayaran_pinjaman');
        }

        // angsuran_pinjaman → references pinjaman
        if (!empty($pinjaman_ids)) {
            $this->db->where_in('pinjaman_id', $pinjaman_ids)->delete('angsuran_pinjaman');
        }

        // pengajuan_bayar_angsuran → references pinjaman (kolom pinjaman_id)
        if (!empty($pinjaman_ids)) {
            $this->db->where_in('pinjaman_id', $pinjaman_ids)->delete('pengajuan_bayar_angsuran');
        }

        // transaksi_tabungan → references tabungan
        if (!empty($tabungan_ids)) {
            $this->db->where_in('tabungan_id', $tabungan_ids)->delete('transaksi_tabungan');
        }

        // ---------- Hapus anak-anak (level menengah) ----------

        // pinjaman → references pengajuan_pinjaman & warga
        if (!empty($pinjaman_ids)) {
            $this->db->where_in('id', $pinjaman_ids)->delete('pinjaman');
        }

        // pengajuan_pinjaman → references warga
        if (!empty($pengajuan_ids)) {
            $this->db->where_in('id', $pengajuan_ids)->delete('pengajuan_pinjaman');
        }

        // tabungan → references warga
        if (!empty($tabungan_ids)) {
            $this->db->where_in('id', $tabungan_ids)->delete('tabungan');
        }

        // pembayaran_iuran → references warga (NO CASCADE)
        $this->db->where_in('warga_id', $warga_ids)->delete('pembayaran_iuran');

        // pembayaran_pending → references warga
        $this->db->where_in('warga_id', $warga_ids)->delete('pembayaran_pending');

        // permohonan_surat → references warga (NO CASCADE)
        $this->db->where_in('warga_id', $warga_ids)->delete('permohonan_surat');

        // pengaduan → references warga
       $non_admin_user_ids = array_diff(
    array_column($this->db->select('user_id')->where_in('id', $warga_ids)->get('warga')->result_array(), 'user_id'),
    $this->_get_admin_ids()
);
if (!empty($non_admin_user_ids)) {
    $this->db->where_in('user_id', $non_admin_user_ids)->delete('pengaduan');
}

        // Catatan: arisan_peserta, arisan_iuran, arisan_riwayat_pemenang
        // sudah ON DELETE CASCADE, otomatis terhapus saat warga dihapus.
        // Tapi jika ingin eksplisit (lebih aman):
        $this->db->where_in('warga_id', $warga_ids)->delete('arisan_riwayat_pemenang');
        $this->db->where_in('warga_id', $warga_ids)->delete('arisan_iuran');
        $this->db->where_in('warga_id', $warga_ids)->delete('arisan_peserta');
    }

    // =========================================================
    // RESET WARGA DAN USER
    // =========================================================
    public function reset_warga_dan_user() {
        $this->db->trans_start();

        try {
            $admin_ids  = $this->_get_admin_ids();
            $warga_ids  = $this->_get_warga_to_delete($admin_ids);

            // Hapus semua dependensi warga dulu
            $this->_hapus_dependensi_warga($warga_ids);

            // Hapus warga
            $this->db->where_not_in('user_id', $admin_ids);
            $this->db->or_where('user_id IS NULL', NULL, FALSE);
            $this->db->delete('warga');
            $warga_deleted = $this->db->affected_rows();

            // Hapus KK yang tidak punya warga lagi
            $this->db->where("id NOT IN (SELECT DISTINCT kk_id FROM warga WHERE kk_id IS NOT NULL)", NULL, FALSE);
            $this->db->delete('kartu_keluarga');
            $kk_deleted = $this->db->affected_rows();

            // Hapus user non-admin
            $this->db->where_not_in('id', $admin_ids)->delete('users');
            $user_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'          => true,
                'warga_deleted'    => $warga_deleted,
                'kk_deleted'       => $kk_deleted,
                'user_deleted'     => $user_deleted,
                'users_preserved'  => count($admin_ids),
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET KEUANGAN (Tabungan, Pinjaman, Kas)
    // =========================================================
    public function reset_keuangan() {
        $this->db->trans_start();

        try {
            // Urutan: cucu dulu → anak → parent
            $this->db->query("DELETE FROM `riwayat_pembayaran_pinjaman`");
            $riwayat_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pengajuan_bayar_angsuran`");
            $pengajuan_bayar_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `angsuran_pinjaman`");
            $angsuran_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pinjaman`");
            $pinjaman_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pengajuan_pinjaman`");
            $pengajuan_pinjaman_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `transaksi_tabungan`");
            $transaksi_tabungan_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `tabungan`");
            $tabungan_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `kas_rt`");
            $kas_deleted = $this->db->affected_rows();

            // Reset saldo master kas ke saldo awal
            $this->db->set('saldo_saat_ini', 'saldo_awal', FALSE);
            $this->db->update('master_kas');

            $this->db->trans_complete();

            return [
                'success'                          => true,
                'tabungan_deleted'                 => $tabungan_deleted,
                'transaksi_tabungan_deleted'       => $transaksi_tabungan_deleted,
                'pinjaman_deleted'                 => $pinjaman_deleted,
                'pengajuan_pinjaman_deleted'       => $pengajuan_pinjaman_deleted,
                'angsuran_deleted'                 => $angsuran_deleted,
                'kas_deleted'                      => $kas_deleted,
                'riwayat_pembayaran_pinjaman_deleted' => $riwayat_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET IURAN DAN PEMBAYARAN
    // =========================================================
    public function reset_iuran() {
        $this->db->trans_start();

        try {
            $this->db->query("DELETE FROM `pembayaran_iuran`");
            $pembayaran_iuran_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pengajuan_bayar_angsuran`");
            $pengajuan_bayar_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pembayaran_pending`");
            $pembayaran_pending_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `riwayat_pembayaran_pinjaman`");
            $riwayat_pembayaran_pinjaman_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'                              => true,
                'pembayaran_iuran_deleted'             => $pembayaran_iuran_deleted,
                'pengajuan_bayar_deleted'              => $pengajuan_bayar_deleted,
                'pembayaran_pending_deleted'           => $pembayaran_pending_deleted,
                'riwayat_pembayaran_pinjaman_deleted'  => $riwayat_pembayaran_pinjaman_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET INVENTARIS
    // =========================================================
    public function reset_inventaris() {
        $this->db->trans_start();

        try {
            // inventaris_gallery CASCADE → aman, tapi eksplisit lebih baik
            $this->db->query("DELETE FROM `inventaris_gallery`");
            $gallery_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `inventaris`");
            $inventaris_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `rencana_pembelian`");
            $rencana_pembelian_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'                  => true,
                'inventaris_deleted'       => $inventaris_deleted,
                'gallery_deleted'          => $gallery_deleted,
                'rencana_pembelian_deleted'=> $rencana_pembelian_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET ARISAN
    // =========================================================
    public function reset_arisan() {
        $this->db->trans_start();

        try {
            // Semua child arisan sudah CASCADE, tapi hapus eksplisit lebih aman
            $this->db->query("DELETE FROM `arisan_riwayat_pemenang`");
            $riwayat_pemenang_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `arisan_iuran`");
            $iuran_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `arisan_pertemuan`");
            $pertemuan_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `arisan_peserta`");
            $peserta_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `arisan`");
            $arisan_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'               => true,
                'arisan_deleted'        => $arisan_deleted,
                'peserta_deleted'       => $peserta_deleted,
                'pertemuan_deleted'     => $pertemuan_deleted,
                'iuran_deleted'         => $iuran_deleted,
                'riwayat_pemenang_deleted' => $riwayat_pemenang_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET ADMINISTRASI
    // =========================================================
    public function reset_administrasi() {
        $this->db->trans_start();

        try {
            $this->db->query("DELETE FROM `permohonan_surat`");
            $permohonan_surat_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pengumuman`");
            $pengumuman_deleted = $this->db->affected_rows();

            $this->db->query("DELETE FROM `pengaduan`");
            $pengaduan_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'                  => true,
                'permohonan_surat_deleted' => $permohonan_surat_deleted,
                'pengumuman_deleted'       => $pengumuman_deleted,
                'pengaduan_deleted'        => $pengaduan_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET LOG AKTIVITAS
    // =========================================================
    public function reset_log() {
        $this->db->trans_start();

        try {
            $this->db->query("DELETE FROM `log_aktivitas`");
            $log_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'     => true,
                'log_deleted' => $log_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    // =========================================================
    // RESET TOTAL — SEMUA DATA KECUALI ADMIN & MASTER DATA
    // =========================================================
    public function reset_total() {
        $this->db->trans_start();

        try {
            $admin_ids = $this->_get_admin_ids();
            $warga_ids = $this->_get_warga_to_delete($admin_ids);

            // 1. Log aktivitas
            $this->db->query("DELETE FROM `log_aktivitas`");
            $log_deleted = $this->db->affected_rows();

            // 2. Arisan (CASCADE, tapi eksplisit)
            $this->db->query("DELETE FROM `arisan_riwayat_pemenang`");
            $this->db->query("DELETE FROM `arisan_iuran`");
            $this->db->query("DELETE FROM `arisan_pertemuan`");
            $this->db->query("DELETE FROM `arisan_peserta`");
            $this->db->query("DELETE FROM `arisan`");
            $arisan_deleted = $this->db->affected_rows();

            // 3. Inventaris
            $this->db->query("DELETE FROM `inventaris_gallery`");
            $this->db->query("DELETE FROM `inventaris`");
            $this->db->query("DELETE FROM `rencana_pembelian`");
            $inventaris_deleted = $this->db->affected_rows();

            // 4. Keuangan — urutan wajib: cucu → anak → parent
            $this->db->query("DELETE FROM `riwayat_pembayaran_pinjaman`");
            $this->db->query("DELETE FROM `pengajuan_bayar_angsuran`");
            $this->db->query("DELETE FROM `angsuran_pinjaman`");
            $this->db->query("DELETE FROM `pinjaman`");
            $pinjaman_deleted = $this->db->affected_rows();
            $this->db->query("DELETE FROM `pengajuan_pinjaman`");
            $this->db->query("DELETE FROM `transaksi_tabungan`");
            $this->db->query("DELETE FROM `tabungan`");
            $tabungan_deleted = $this->db->affected_rows();
            $this->db->query("DELETE FROM `kas_rt`");
            $kas_deleted = $this->db->affected_rows();

            // Reset saldo master kas
            $this->db->set('saldo_saat_ini', 'saldo_awal', FALSE)->update('master_kas');

            // 5. Iuran & pembayaran
            $this->db->query("DELETE FROM `pembayaran_iuran`");
            $this->db->query("DELETE FROM `pembayaran_pending`");

            // 6. Administrasi
            $this->db->query("DELETE FROM `permohonan_surat`");
            $this->db->query("DELETE FROM `pengumuman`");
            $pengumuman_deleted = $this->db->affected_rows();
            $this->db->query("DELETE FROM `pengaduan`");
            $pengaduan_deleted = $this->db->affected_rows();

            // 7. Warga & KK & User (paling akhir)
            if (!empty($warga_ids)) {
                $this->db->where_in('warga_id', $warga_ids)->delete('arisan_riwayat_pemenang'); // safety
                $this->db->where_in('warga_id', $warga_ids)->delete('arisan_peserta');          // safety
            }

            $this->db->where_not_in('user_id', $admin_ids);
            $this->db->or_where('user_id IS NULL', NULL, FALSE);
            $this->db->delete('warga');
            $warga_deleted = $this->db->affected_rows();

            $this->db->where("id NOT IN (SELECT DISTINCT kk_id FROM warga WHERE kk_id IS NOT NULL)", NULL, FALSE);
            $this->db->delete('kartu_keluarga');
            $kk_deleted = $this->db->affected_rows();

            $this->db->where_not_in('id', $admin_ids)->delete('users');
            $user_deleted = $this->db->affected_rows();

            $this->db->trans_complete();

            return [
                'success'          => true,
                'admin_preserved'  => count($admin_ids),
                'log_deleted'      => $log_deleted,
                'arisan_deleted'   => $arisan_deleted,
                'inventaris_deleted' => $inventaris_deleted,
                'tabungan_deleted' => $tabungan_deleted,
                'pinjaman_deleted' => $pinjaman_deleted,
                'kas_deleted'      => $kas_deleted,
                'pengumuman_deleted' => $pengumuman_deleted,
                'pengaduan_deleted'  => $pengaduan_deleted,
                'warga_deleted'    => $warga_deleted,
                'kk_deleted'       => $kk_deleted,
                'user_deleted'     => $user_deleted,
            ];

        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
}