<?php

namespace App\Models;

use Core\Database;
use PDO;

class DataPMB
{
    private $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }

    // Ambil total data (untuk pagination)
    public function countData($search = '')
    {
        $sql = "SELECT COUNT(*) FROM tb_pmb WHERE 1";

        if (!empty($search)) {
            $sql .= " AND (nama_camaba LIKE :search OR nisn LIKE :search OR npsn LIKE :search)";
        }

        $stmt = $this->db->prepare($sql);
        if (!empty($search)) {
            $stmt->bindValue(':search', "%$search%", PDO::PARAM_STR);
        }
        $stmt->execute();

        return (int)$stmt->fetchColumn();
    }

    // Ambil data PMB
    public function getData($search = '', $limit = 25, $offset = 0)
    {
        $sql = "SELECT id_pmb, nama_camaba, nisn, npsn, wa FROM tb_pmb WHERE 1";

        if (!empty($search)) {
            $sql .= " AND (nama_camaba LIKE :search OR nisn LIKE :search OR npsn LIKE :search)";
        }

        $sql .= " ORDER BY nama_camaba ASC LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        if (!empty($search)) {
            $stmt->bindValue(':search', "%$search%", PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);

        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getDistinctTahunMasuk(): array
    {
        $sql = "SELECT DISTINCT tahun_masuk FROM tb_pmb ORDER BY tahun_masuk DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    // Hapus data PMB
    public function deleteData(int $id): bool
    {
        $stmt = $this->db->prepare("DELETE FROM tb_pmb WHERE id_pmb = :id");
        return $stmt->execute([':id' => $id]);
    }

    // Ambil data berdasarkan id
    public function getDataById(int $id)
    {
        $stmt = $this->db->prepare("SELECT id_pmb, nama_camaba, nisn, npsn, wa FROM tb_pmb WHERE id_pmb = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Update data PMB
    public function updateData(int $id, string $nama, string $nisn, string $npsn, string $wa): bool
    {
        $stmt = $this->db->prepare("
            UPDATE tb_pmb SET
                nama_camaba = :nama,
                nisn = :nisn,
                npsn = :npsn,
                wa = :wa
            WHERE id_pmb = :id
        ");
        return $stmt->execute([
            ':nama' => $nama,
            ':nisn' => $nisn,
            ':npsn' => $npsn,
            ':wa' => $wa,
            ':id' => $id
        ]);
    }  

    public function isNisnExist(string $nisn): bool
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM tb_pmb WHERE nisn = :nisn");
        $stmt->execute([':nisn' => $nisn]);
        return $stmt->fetchColumn() > 0;
    }

    public function insertImport(array $data): bool
    {
        $tahun = date('Y');
    
        // 🔹 Ambil nomor urut TERBESAR berdasarkan no_kartu
        $stmt = $this->db->prepare("
            SELECT MAX(CAST(SUBSTRING(no_kartu, -4) AS UNSIGNED)) 
            FROM tb_pmb 
            WHERE no_kartu LIKE :prefix
        ");
        $stmt->execute([
            ':prefix' => "PMB-{$tahun}-%"
        ]);
    
        $lastNumber = (int)$stmt->fetchColumn();
        $nextNumber = $lastNumber + 1;
    
        $no_kartu = sprintf(
            "PMB-%s-%04d",
            $tahun,
            $nextNumber
        );
    
        $sql = "INSERT INTO tb_pmb SET
            nama_camaba   = :nama,
            nisn          = :nisn,
            asal_sekolah  = :asal_sekolah,
            npsn          = :npsn,
            tahun_lulus   = :tahun_lulus,
            wa            = :wa,
            id_prodi      = :id_prodi,
            tgl_daftar    = NOW(),
            tahun_masuk   = :tahun_masuk,
            no_kartu      = :no_kartu,
            gelombang     = :gelombang,
            tanggal       = :tanggal,
            status        = 'Paid',
            status_wa     = 'Sudah'
        ";
    
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            ':nama'         => $data['nama_camaba'],
            ':nisn'         => $data['nisn'],
            ':asal_sekolah' => $data['asal_sekolah'],
            ':npsn'         => $data['npsn'],
            ':tahun_lulus'  => $data['tahun_lulus'],
            ':wa'           => $data['wa'],
            ':id_prodi'     => $data['id_prodi'],
            ':tahun_masuk'  => $tahun,
            ':no_kartu'     => $no_kartu,
            ':gelombang'    => $data['gelombang'],
            ':tanggal'      => $data['tanggal'],
        ]);
    }

}
