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

class Siswa_model extends CI_Model
{
    // Kandidat nama tabel (di-resolve terhadap DB)
    private $T_SISWA  = null;
    private $T_USERS  = null;
    private $T_STATUS = null;
    private $T_LPK    = null;
    private $T_DOK    = null;

    // Konvensi (samakan dengan DB)
    // StatusSiswa: 1=Calon, 2=Menunggu Pembayaran, 3=Aktif, 4=Lulus, 5=Ditolak
    const STATUS_CALON      = 1;
    const STATUS_MENUNGGU   = 2;
    const STATUS_AKTIF      = 3;
    const STATUS_LULUS      = 4;
    const STATUS_DITOLAK    = 5;

    const HSI_LPK_ID   = 1;

    // cache kolom per-tabel biar hemat
    private $cols_cache = [];

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->model('DokumenSiswa_model', 'dokumenm');

        // Resolve nama tabel berdasar yang benar-benar ada
        $this->T_SISWA  = $this->resolve_table(['Siswa','siswa']);
        $this->T_USERS  = $this->resolve_table(['Users','users']);
        $this->T_STATUS = $this->resolve_table(['StatusSiswa','statussiswa']);
        $this->T_LPK    = $this->resolve_table(['LPK','lpk']);
        $this->T_DOK    = $this->resolve_table(['DokumenSiswa','dokumensiswa']);
    }

    /** Pilih nama tabel yang eksis di DB dari daftar kandidat */
    private function resolve_table(array $candidates): string
    {
        foreach ($candidates as $t) {
            if ($this->db->table_exists($t)) return $t;
        }
        return $candidates[0]; // fallback
    }

    /** ambil kolom (lowercase) & cache */
    private function cols(string $table): array
    {
        if (!isset($this->cols_cache[$table])) {
            $this->cols_cache[$table] = array_map('strtolower', $this->db->list_fields($table));
        }
        return $this->cols_cache[$table];
    }

    private function has_col(string $table, string $col): bool
    {
        return in_array(strtolower($col), $this->cols($table), true);
    }

    private function field_exists_cached(string $table, string $col): bool
    {
        return $this->has_col($table, $col);
    }

    /** Tambah kondisi "belum dihapus" jika kolom DeletedAt ada */
    private function scope_not_deleted(string $alias = 's'): void
    {
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedAt')) {
            $this->db->where("$alias.DeletedAt IS NULL", null, false);
        }
    }

    /* ====================== COUNT ====================== */

    public function count_all(): int
    {
        $this->db->from($this->T_SISWA.' s');
        $this->scope_not_deleted('s');
        return (int) $this->db->count_all_results();
    }

    /* ====================== LISTING ====================== */

    /** Calon siswa HSI (StatusID=1 atau NamaStatus='Calon') */
    public function get_calon_siswa()
    {
        $this->db->select('u.UserID, s.SiswaID, s.NamaLengkap, u.Email, u.Phone, s.CreatedAt, ss.NamaStatus')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->group_start()
                    ->where('ss.NamaStatus', 'Calon')
                    ->or_where('s.StatusID', self::STATUS_CALON)
                 ->group_end()
                 ->group_start()
                    ->where('s.LPKID', self::HSI_LPK_ID)
                    ->or_where('s.LPKID IS NULL', null, false)
                 ->group_end();
        $this->scope_not_deleted('s');
        return $this->db->order_by('s.CreatedAt', 'DESC')->get()->result();
    }

    /** List semua siswa + filter keyword (SiswaID, Nama, Email, Phone) */
    public function get_all($keyword = null)
    {
        $this->db->select('s.SiswaID, s.UserID, s.NamaLengkap, u.Email, u.Phone, s.CreatedAt, ss.NamaStatus')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left');
        $this->scope_not_deleted('s');

        $keyword = trim((string)$keyword);
        if ($keyword !== '') {
            $this->db->group_start()
                     ->like('s.SiswaID', $keyword)
                     ->or_like('s.NamaLengkap', $keyword)
                     ->or_like('u.Email', $keyword)
                     ->or_like('u.Phone', $keyword)
                     ->group_end();
        }

        return $this->db->order_by('s.CreatedAt', 'DESC')->get()->result();
    }

    /** Semua siswa non-calon (StatusID != 1) */
    public function get_all_non_calon()
    {
        $this->db->select('s.SiswaID, s.UserID, s.NamaLengkap, u.Email, u.Phone, s.CreatedAt, ss.NamaStatus')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->where('s.StatusID !=', self::STATUS_CALON);
        $this->scope_not_deleted('s');

        return $this->db->order_by('s.SiswaID', 'DESC')->get()->result();
    }

    /** Semua siswa HSI (termasuk calon) */
    public function get_all_hsi_with_calon()
    {
        $this->db->select('s.SiswaID, s.UserID, s.NamaLengkap, ss.NamaStatus, u.Email, u.Phone, s.CreatedAt')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->group_start()
                    ->where('s.LPKID', self::HSI_LPK_ID)
                    ->or_where('s.LPKID IS NULL', null, false)
                 ->group_end();
        $this->scope_not_deleted('s');

        return $this->db->order_by('s.CreatedAt', 'DESC')->get()->result();
    }

    /** Semua siswa HSI (tanpa calon) */
    public function get_all_hsi()
    {
        $this->db->select('s.*, u.Email, u.Phone, ss.NamaStatus')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->where('s.LPKID', self::HSI_LPK_ID)
                 ->where('s.StatusID !=', self::STATUS_CALON);
        $this->scope_not_deleted('s');

        return $this->db->order_by('s.CreatedAt', 'DESC')->get()->result();
    }

    /** Semua siswa non-HSI (LPK lain) */
    public function get_all_non_hsi()
    {
        $this->db->select('s.*, u.Email, u.Phone, ss.NamaStatus, l.NamaLPK')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->join($this->T_LPK.' l',     'l.LPKID = s.LPKID', 'left')
                 ->where('s.LPKID !=', self::HSI_LPK_ID);
        $this->scope_not_deleted('s');

        return $this->db->order_by('s.CreatedAt', 'DESC')->get()->result();
    }

    /* ====================== DETAIL ====================== */

    /** Detail siswa (by SiswaID atau UserID) + dokumen */
    public function get_by_id($siswa_id = null, $user_id = null)
    {
        $this->db->select('s.*, u.Email, u.Phone, ss.NamaStatus')
                 ->from($this->T_SISWA.' s')
                 ->join($this->T_USERS.' u',   's.UserID = u.UserID', 'left')
                 ->join($this->T_STATUS.' ss', 's.StatusID = ss.StatusID', 'left')
                 ->limit(1);
        $this->scope_not_deleted('s');

        if (!empty($siswa_id)) {
            $this->db->where('s.SiswaID', $siswa_id);
        } elseif (!empty($user_id)) {
            $this->db->where('s.UserID', (int)$user_id);
        } else {
            return null;
        }

        $row = $this->db->get()->row();

        if ($row) {
            $sid = $row->SiswaID ?? $siswa_id;
            $uid = $row->UserID  ?? $user_id;
            $row->dokumen = $this->dokumenm->get_by_siswa($sid, $uid);
        }

        return $row;
    }

    /* ====================== MUTASI ====================== */

    /** Update status siswa (isi UpdatedAt kalau kolomnya ada) */
    public function update_status($siswa_id, $status_id)
    {
        $data = ['StatusID' => (int)$status_id];
        if ($this->field_exists_cached($this->T_SISWA, 'UpdatedAt')) {
            $data['UpdatedAt'] = date('Y-m-d H:i:s');
        }

        return $this->db->where('SiswaID', $siswa_id)
                        ->update($this->T_SISWA, $data);
    }

    /** Wrapper dokumen */
    public function get_dokumen($siswa_id = null, $user_id = null)
    {
        return $this->dokumenm->get_by_siswa($siswa_id, $user_id);
    }

    /** Arsip (soft delete). Bila tak ada kolom arsip → set Ditolak. */
    public function soft_delete(string $siswa_id, ?int $admin_user_id = null): bool
    {
        // ambil status sekarang (buat disimpan sebagai prev)
        $row = $this->db->select('StatusID')
                        ->from($this->T_SISWA)
                        ->where('SiswaID', $siswa_id)
                        ->limit(1)->get()->row();

        $data = [];

        // simpan status lama bila kolomnya ada
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedPrevStatusID') && $row) {
            $data['DeletedPrevStatusID'] = (int)$row->StatusID;
        }

        if ($this->field_exists_cached($this->T_SISWA, 'DeletedAt')) {
            $data['DeletedAt'] = date('Y-m-d H:i:s');
        }
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedBy') && $admin_user_id) {
            $data['DeletedBy'] = $admin_user_id;
        }

        // fallback kalau tidak ada kolom arsip sama sekali → tandai Ditolak
        if (empty($data)) {
            $data['StatusID'] = self::STATUS_DITOLAK;
            if ($this->field_exists_cached($this->T_SISWA, 'UpdatedAt')) {
                $data['UpdatedAt'] = date('Y-m-d H:i:s');
            }
        }

        return $this->db->where('SiswaID', $siswa_id)
                        ->update($this->T_SISWA, $data);
    }

    /** Hapus permanen data berdasarkan SiswaID (ikut hapus users jika tak dipakai siswa lain) */
    public function hard_delete(string $siswa_id): bool
    {
        $this->db->trans_begin();

        // Ambil user_id pemilik siswa ini
        $urow = $this->db->select('UserID')
            ->from($this->T_SISWA)
            ->where('SiswaID', $siswa_id)
            ->limit(1)->get()->row();
        $user_id = $urow ? (int)$urow->UserID : null;

        // Dokumen
        if ($this->T_DOK && $this->db->table_exists($this->T_DOK)) {
            $this->db->delete($this->T_DOK, ['SiswaID' => $siswa_id]);
            if ($user_id && $this->field_exists_cached($this->T_DOK, 'UserID')) {
                $this->db->delete($this->T_DOK, ['UserID' => $user_id]);
            }
        }

        // Ledger & sumber pembayaran
        if ($this->db->table_exists('pembayaransiswa')) {
            if ($this->db->field_exists('UserID', 'pembayaransiswa') && $user_id) {
                $this->db->delete('pembayaransiswa', ['UserID' => $user_id]);
            }
            $this->db->delete('pembayaransiswa', ['SiswaID' => $siswa_id]);
        }
        if ($this->db->table_exists('pembayaran')) {
            if ($user_id && $this->db->field_exists('UserID', 'pembayaran')) {
                $this->db->delete('pembayaran', ['UserID' => $user_id]);
            }
            $this->db->delete('pembayaran', ['SiswaID' => $siswa_id]);
        }

        // (opsional) TagihanHSI kalau ada
        if ($this->db->table_exists('tagihanhsi')) {
            if ($user_id && $this->db->field_exists('UserID', 'tagihanhsi')) {
                $this->db->delete('tagihanhsi', ['UserID' => $user_id]);
            }
            if ($this->db->field_exists('SiswaID', 'tagihanhsi')) {
                $this->db->delete('tagihanhsi', ['SiswaID' => $siswa_id]);
            }
        }

        // Hapus siswa
        $this->db->delete($this->T_SISWA, ['SiswaID' => $siswa_id]);

        // Jika user tidak lagi dipakai siswa manapun → hapus users juga
        if ($user_id) {
            $sisa = $this->db->where('UserID', $user_id)->count_all_results($this->T_SISWA);
            if ($sisa === 0) {
                $this->db->delete($this->T_USERS, ['UserID' => $user_id]);
            }
        }

        if (!$this->db->trans_status()) {
            $this->db->trans_rollback();
            return false;
        }
        $this->db->trans_commit();
        return true;
    }

    /** Pulihkan dari arsip (jika pakai DeletedAt/DeletedBy) */
    public function restore(string $siswa_id): bool
    {
        // baca prev status jika ada kolomnya
        $cur = $this->db->select('DeletedPrevStatusID, StatusID, SiswaID')
                        ->from($this->T_SISWA)
                        ->where('SiswaID', $siswa_id)
                        ->limit(1)->get()->row();
        if (!$cur) return false;

        // target status:
        // - DeletedPrevStatusID ada → pakai itu
        // - else: punya SiswaID ⇒ Aktif, tidak punya ⇒ Calon
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedPrevStatusID') &&
            isset($cur->DeletedPrevStatusID) && $cur->DeletedPrevStatusID !== null) {

            $targetStatus = (int)$cur->DeletedPrevStatusID;

        } else {
            $targetStatus = !empty($cur->SiswaID)
                ? self::STATUS_AKTIF    // sudah punya SiswaID, minimal aktif
                : self::STATUS_CALON;   // belum punya SiswaID, kembali jadi calon
        }

        $data = ['StatusID' => $targetStatus];

        // hapus flag arsip jika ada
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedAt'))           $data['DeletedAt'] = null;
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedBy'))           $data['DeletedBy'] = null;
        if ($this->field_exists_cached($this->T_SISWA, 'DeletedPrevStatusID')) $data['DeletedPrevStatusID'] = null;
        if ($this->field_exists_cached($this->T_SISWA, 'UpdatedAt'))           $data['UpdatedAt'] = date('Y-m-d H:i:s');

        return $this->db->where('SiswaID', $siswa_id)->update($this->T_SISWA, $data);
    }

    /* ====================== UTIL STATUS/CEK ====================== */

    /** Apakah siswa dalam status Aktif (3) */
    public function is_aktif($siswa_id): bool
    {
        $row = $this->db->select('StatusID')
                        ->from($this->T_SISWA)
                        ->where('SiswaID', $siswa_id)
                        ->limit(1)->get()->row();
        return (bool) ($row && (int)$row->StatusID === self::STATUS_AKTIF);
    }

    /** Ambil StatusID siswa */
    public function get_status_id($siswa_id): ?int
    {
        $row = $this->db->select('StatusID')
                        ->from($this->T_SISWA)
                        ->where('SiswaID', $siswa_id)
                        ->limit(1)->get()->row();
        return $row ? (int)$row->StatusID : null;
    }

    /** Ambil master status untuk dropdown (urut by StatusID) */
    public function get_status_list(): array
    {
        return $this->db->order_by('StatusID','ASC')->get($this->T_STATUS)->result();
    }

    /**
     * Cek apakah punya transaksi approved (sumber/ledger).
     * Dipakai controller untuk memutuskan soft/hard delete.
     */
    public function has_approved_payments(string $siswa_id): bool
    {
        $approved = false;

        if ($this->db->table_exists('pembayaran')) {
            $cnt = $this->db->where('SiswaID', $siswa_id)
                            ->where_in('StatusBayar', ['Approved','approved','APPROVED'])
                            ->count_all_results('pembayaran');
            $approved = $approved || ($cnt > 0);
        }

        if ($this->db->table_exists('pembayaransiswa')) {
            $this->db->from('pembayaransiswa')
                     ->where('SiswaID', $siswa_id)
                     ->group_start()
                        ->where('StatusKonfirmasi', 1)
                        ->or_where_in('StatusKonfirmasi', ['Approved','approved','APPROVED'])
                     ->group_end();
            $cnt = $this->db->count_all_results();
            $approved = $approved || ($cnt > 0);
        }

        return $approved;
    }

    /* ====================== HELPER TAMBAHAN (opsional dipakai controller) ====================== */

    public function get_user_id_by_siswaid(string $siswa_id): ?int
    {
        $row = $this->db->select('UserID')
                        ->from($this->T_SISWA)
                        ->where('SiswaID', $siswa_id)
                        ->limit(1)->get()->row();
        return $row ? (int)$row->UserID : null;
    }

    /** Hapus permanen SEMUA data milik UserID (termasuk users). */
    public function hard_delete_by_user(int $user_id): bool
    {
        $this->db->trans_begin();

        // kumpulkan semua SiswaID milik user
        $siswaIDs = array_map(function($r){ return $r->SiswaID; },
            $this->db->select('SiswaID')->from($this->T_SISWA)->where('UserID', $user_id)->get()->result()
        );

        // DokumenSiswa
        if ($this->T_DOK && $this->db->table_exists($this->T_DOK)) {
            if ($this->field_exists_cached($this->T_DOK, 'UserID')) {
                $this->db->where('UserID', $user_id)->delete($this->T_DOK);
            }
            if (!empty($siswaIDs)) $this->db->where_in('SiswaID', $siswaIDs)->delete($this->T_DOK);
        }

        // Ledger
        if ($this->db->table_exists('pembayaransiswa')) {
            if ($this->db->field_exists('UserID', 'pembayaransiswa')) {
                $this->db->where('UserID', $user_id)->delete('pembayaransiswa');
            }
            if (!empty($siswaIDs)) $this->db->where_in('SiswaID', $siswaIDs)->delete('pembayaransiswa');
        }

        // Sumber pembayaran
        if ($this->db->table_exists('pembayaran')) {
            if ($this->db->field_exists('UserID', 'pembayaran')) {
                $this->db->where('UserID', $user_id)->delete('pembayaran');
            }
            if (!empty($siswaIDs)) $this->db->where_in('SiswaID', $siswaIDs)->delete('pembayaran');
        }

        // TagihanHSI (jika ada)
        if ($this->db->table_exists('tagihanhsi')) {
            if ($this->db->field_exists('UserID', 'tagihanhsi')) {
                $this->db->where('UserID', $user_id)->delete('tagihanhsi');
            }
            if (!empty($siswaIDs) && $this->db->field_exists('SiswaID', 'tagihanhsi')) {
                $this->db->where_in('SiswaID', $siswaIDs)->delete('tagihanhsi');
            }
        }

        // Hapus siswa milik user
        $this->db->where('UserID', $user_id)->delete($this->T_SISWA);

        // Terakhir: hapus akun users
        $this->db->delete($this->T_USERS, ['UserID' => $user_id]);

        if (!$this->db->trans_status()) {
            $this->db->trans_rollback();
            return false;
        }
        $this->db->trans_commit();
        return true;
    }

}
