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

class PembayaranSiswa_model extends CI_Model
{
    private $table = 'pembayaransiswa';

    /** cache kolom & tipe */
    private $cols = null;
    private $statusIsNumeric = null;

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        date_default_timezone_set('Asia/Jakarta');
    }

    /* ===================== Helpers ===================== */

    /** Ambil daftar kolom (lowercase) sekali saja */
    private function _cols(): array
    {
        if ($this->cols === null) {
            $this->cols = array_map('strtolower', $this->db->list_fields($this->table));
        }
        return $this->cols;
    }

    /** Cek keberadaan kolom (case-insensitive) */
    private function has_col(string $col): bool
    {
        return in_array(strtolower($col), $this->_cols(), true);
    }

    /** Nama kolom 'jenis' yang tersedia, atau null jika tak ada */
    private function jenis_col(): ?string
    {
        if ($this->has_col('JenisPembayaran')) return 'JenisPembayaran';
        if ($this->has_col('Jenis'))           return 'Jenis';
        return null;
    }

    /** Apakah kolom StatusKonfirmasi bertipe numerik? (cache) */
    private function status_is_numeric(): bool
    {
        if ($this->statusIsNumeric !== null) return $this->statusIsNumeric;
        if (!$this->has_col('StatusKonfirmasi')) {
            return $this->statusIsNumeric = false;
        }

        $row = $this->db->query(
            "SELECT DATA_TYPE
               FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = ?
                AND COLUMN_NAME  = 'StatusKonfirmasi'
              LIMIT 1",
            [$this->table]
        )->row();

        if (!$row) return $this->statusIsNumeric = false;
        $t = strtolower((string)$row->DATA_TYPE);
        return $this->statusIsNumeric = in_array($t, [
            'int','integer','tinyint','smallint','mediumint','bigint','decimal','numeric'
        ], true);
    }

    /** Nilai 'approved' mengikuti tipe kolom */
    private function approved_value()
    {
        return $this->status_is_numeric() ? 1 : 'Approved';
    }

    /** Ambil properti dari $row dengan fallback huruf kecil/besar */
    private function rget($row, string $a, string $b = null)
    {
        // urutan prioritas: exact -> alias -> lower
        if (isset($row->$a)) return $row->$a;
        if ($b && isset($row->$b)) return $row->$b;
        $lo = strtolower($a);
        return $row->$lo ?? null;
    }

    /** Cek sudah ada baris ledger untuk PembayaranID (idempoten) */
    // HAPUS fungsi lama _existing_id_by_pembayaran()
private function _exists_by_pembayaran($pembayaran_id): bool
{
    return $this->db->select('1', false)
                    ->from($this->table)
                    ->where('PembayaranID', $pembayaran_id)
                    ->limit(1)
                    ->get()->num_rows() > 0;
}


    /* ===================== Public API ===================== */

    /**
     * Insert baris ledger saat admin approve.
     * Signature DISESUAIKAN dengan controller:
     *   insert_from_pembayaran($pembayaran_row, int $student_user_id, ?string $jenis_label = null, ?int $admin_user_id = null)
     *
     * - Idempoten: jika sudah ada row ledger untuk PembayaranID yang sama, tidak insert ulang.
     * - Auto-isi StatusKonfirmasi = Approved (1/teks) bila kolom ada.
     * - ApprovedBy/ApprovedAt diisi jika kolom ada (admin_user_id -> fallback session user_id).
     * - TanggalBayar diambil dari sumber bila ada, fallback NOW().
     */
    public function insert_from_pembayaran($pembayaran_row, int $student_user_id = 0, ?string $jenis = null, ?int $admin_user_id = null)
    {
        if (!$pembayaran_row) return false;

        $pembayaran_id = $this->rget($pembayaran_row, 'PembayaranID');
        $siswa_id      = $this->rget($pembayaran_row, 'SiswaID');
        $nominal       = (float)($this->rget($pembayaran_row, 'JumlahBayar') ?? 0);
        $bukti         = $this->rget($pembayaran_row, 'BuktiTransfer');
        $tgl_bayar_src = $this->rget($pembayaran_row, 'TanggalBayar');

        if (!$pembayaran_id) return false;

        // idempotent: jika sudah ada ledger utk PembayaranID ini, return existing
        if ($this->_exists_by_pembayaran($pembayaran_id)) {
    return true; // sudah ada ledger utk pembayaran ini
}

        // fallback student_user_id dari sumber bila kosong
        if ($student_user_id <= 0) {
            $student_user_id = (int)$this->rget($pembayaran_row, 'UserID');
        }

        // ApprovedBy fallback ke session admin
        if ($admin_user_id === null) {
            $admin_user_id = (int)($this->session->userdata('UserID') ?? 0);
        }

        $now = date('Y-m-d H:i:s');
        $tglLedger = $tgl_bayar_src ? date('Y-m-d H:i:s', strtotime($tgl_bayar_src)) : $now;

        $data = [
            'PembayaranID'  => $pembayaran_id,
            'SiswaID'       => $siswa_id,
            'Nominal'       => $nominal,
            'BuktiTransfer' => $bukti ?: null,
        ];

        // Kolom status (bila ada)
        if ($this->has_col('StatusKonfirmasi')) {
            $data['StatusKonfirmasi'] = $this->approved_value();
        }

        // Kolom UserID pada ledger (FK ke siswa.UserID) jika ada
        if ($this->has_col('UserID')) {
            $data['UserID'] = $student_user_id ?: null;
        }

        // Jenis (Pelatihan/Raftel) jika kolom tersedia
        $jenisCol = $this->jenis_col();
        if ($jenisCol !== null && $jenis !== null) {
            $data[$jenisCol] = $jenis;
        }

        // Tanggal/metadata yang tersedia
        if ($this->has_col('TanggalBayar')) $data['TanggalBayar'] = $tglLedger;
        if ($this->has_col('CreatedAt'))    $data['CreatedAt']    = $now;
        if ($this->has_col('UpdatedAt'))    $data['UpdatedAt']    = $now;
        if ($this->has_col('ApprovedAt'))   $data['ApprovedAt']   = $now;
        if ($this->has_col('ApprovedBy'))   $data['ApprovedBy']   = ($admin_user_id ?: null);

        $this->db->insert($this->table, $data);
        return $this->db->insert_id() ?: true;
    }

    /** Total nominal approved untuk satu siswa (opsional filter jenis) */
    public function sum_nominal_by_siswa($siswa_id, $jenis = null): float
    {
        $this->db->select_sum('Nominal')
                 ->from($this->table)
                 ->where('SiswaID', $siswa_id);

        if ($this->has_col('StatusKonfirmasi')) {
            $this->db->where('StatusKonfirmasi', $this->approved_value());
        }

        $jenisCol = $this->jenis_col();
        if ($jenisCol !== null && $jenis !== null) {
            $this->db->where($jenisCol, $jenis);
        }

        $row = $this->db->get()->row();
        return $row ? (float)$row->Nominal : 0.0;
    }

    /** Alias kompatibilitas lama */
    public function sum_nominal_by_siswaid($siswa_id, $jenis = null): float
    {
        return $this->sum_nominal_by_siswa($siswa_id, $jenis);
    }

    /** Ambil ledger per siswa, terbaru di atas (opsional filter jenis) */
    public function get_ledger_by_siswa($siswa_id, $jenis = null)
    {
        $this->db->from($this->table)->where('SiswaID', $siswa_id);

        $jenisCol = $this->jenis_col();
        if ($jenisCol !== null && $jenis !== null) {
            $this->db->where($jenisCol, $jenis);
        }

        if ($this->has_col('TanggalBayar')) {
            $this->db->order_by('TanggalBayar', 'DESC');
        } elseif ($this->has_col('CreatedAt')) {
            $this->db->order_by('CreatedAt', 'DESC');
        } else {
            $this->db->order_by('PembayaranID', 'DESC');
        }

        return $this->db->get()->result();
    }
}
