<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DataMabaPegController
{
    private $db;

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

    public function index()
    {
        $username = $_SESSION['user']['username'] ?? null;
        if (!$username) {
            header("Location: /login");
            exit();
        }

        // ambil parameter GET
        $search       = $_GET['search'] ?? '';
        $tahun_masuk  = $_GET['tahun_masuk'] ?? '';
        $kelas        = $_GET['kelas'] ?? '';
        $pusat_pokjar = $_GET['pusat_pokjar'] ?? '';
        $page         = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $limit        = 10;
        $offset       = ($page - 1) * $limit;

        // cek apakah ada kolom tahun_masuk di tb_mahasiswa
        $hasTahun = false;
        $checkColumn = $this->db->query("SHOW COLUMNS FROM tb_mahasiswa LIKE 'tahun_masuk'");
        if ($checkColumn->rowCount() > 0) {
            $hasTahun = true;
        }

        // daftar tahun masuk (kalau ada kolom tahun_masuk)
        $daftar_tahun = [];
        if ($hasTahun) {
            $stmtTahun = $this->db->query("SELECT DISTINCT tahun_masuk FROM tb_mahasiswa ORDER BY tahun_masuk DESC");
            $daftar_tahun = $stmtTahun->fetchAll(PDO::FETCH_COLUMN);
        }

        // daftar kelas
        $stmtKelas = $this->db->query("SELECT DISTINCT kelas FROM tb_mahasiswa ORDER BY kelas ASC");
        $daftar_kelas = $stmtKelas->fetchAll(PDO::FETCH_COLUMN);

        // daftar pusat/pokjar
        $stmtPokjar = $this->db->query("SELECT DISTINCT pusat_pokjar FROM tb_mahasiswa ORDER BY pusat_pokjar ASC");
        $daftar_pokjar = $stmtPokjar->fetchAll(PDO::FETCH_COLUMN);

        // base query join (nisn harus match)
        $sql = "SELECT m.status_check, m.nim, m.nama, m.nisn, m.kelas, m.pusat_pokjar,
                       p.asal_sekolah, p.npsn"
                . ($hasTahun ? ", m.tahun_masuk" : "") . "
                FROM tb_mahasiswa m
                INNER JOIN tb_pmb p ON m.nisn = p.nisn
                WHERE 1=1";

        $params = [];

        // filter tahun masuk
        if ($hasTahun && !empty($tahun_masuk)) {
            $sql .= " AND m.tahun_masuk = :tahun_masuk";
            $params[':tahun_masuk'] = $tahun_masuk;
        }

        // filter kelas
        if (!empty($kelas)) {
            $sql .= " AND m.kelas = :kelas";
            $params[':kelas'] = $kelas;
        }

        // filter pusat/pokjar
        if (!empty($pusat_pokjar)) {
            $sql .= " AND m.pusat_pokjar = :pusat_pokjar";
            $params[':pusat_pokjar'] = $pusat_pokjar;
        }

        // filter pencarian
        if (!empty($search)) {
            $sql .= " AND (m.nama LIKE :search OR m.nisn LIKE :search)";
            $params[':search'] = "%$search%";
        }

        // hitung total
        $countSql = "SELECT COUNT(*) FROM ($sql) as sub";
        $stmtCount = $this->db->prepare($countSql);
        $stmtCount->execute($params);
        $totalRows = $stmtCount->fetchColumn();
        $totalPages = ceil($totalRows / $limit);

        // data dengan limit & offset + urutan sama seperti download
        $sql .= "
            ORDER BY 
                CASE 
                    WHEN m.pusat_pokjar LIKE '%Pusat%' OR m.pusat_pokjar LIKE '%Menes%' THEN 0 
                    ELSE 1 
                END,
                CASE 
                    WHEN m.kelas LIKE '%Reguler%' THEN 0 
                    WHEN m.kelas LIKE '%Karyawan%' THEN 1 
                    ELSE 2 
                END,
                m.nama ASC
            LIMIT :limit OFFSET :offset
        ";

        $stmt = $this->db->prepare($sql);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // kirim ke view
        require __DIR__ . '/../Views/pegawai/data_peg_maba.php';
    }

    public function download()
    {
        $username = $_SESSION['user']['username'] ?? null;
        if (!$username) {
            header("Location: /login");
            exit();
        }
    
        $tahun_masuk  = $_GET['tahun_masuk'] ?? '';
        $kelas        = $_GET['kelas'] ?? '';
        $pusat_pokjar = $_GET['pusat_pokjar'] ?? '';
    
        // Query data
        $query = "
            SELECT m.nama, m.nisn, m.kelas, m.pusat_pokjar
            FROM tb_mahasiswa m
            INNER JOIN tb_pmb p ON m.nisn = p.nisn
            WHERE 1=1
        ";
    
        $params = [];
        if (!empty($tahun_masuk)) {
            $query .= " AND m.tahun_masuk = :tahun_masuk";
            $params[':tahun_masuk'] = $tahun_masuk;
        }
        if (!empty($kelas)) {
            $query .= " AND m.kelas = :kelas";
            $params[':kelas'] = $kelas;
        }
        if (!empty($pusat_pokjar)) {
            $query .= " AND m.pusat_pokjar = :pusat_pokjar";
            $params[':pusat_pokjar'] = $pusat_pokjar;
        }
    
        // urutkan sama
        $query .= "
            ORDER BY 
                CASE 
                    WHEN m.pusat_pokjar LIKE '%Pusat%' OR m.pusat_pokjar LIKE '%Menes%' THEN 0 
                    ELSE 1 
                END,
                CASE 
                    WHEN m.kelas LIKE '%Reguler%' THEN 0 
                    WHEN m.kelas LIKE '%Karyawan%' THEN 1 
                    ELSE 2 
                END,
                m.nama ASC
        ";
    
        $stmt = $this->db->prepare($query);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
    
        $filename = $tahun_masuk ? "data_mahasiswa_angkatan_$tahun_masuk.xls" : "data_mahasiswa.xls";
    
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=\"$filename\"");
    
        $output = fopen("php://output", "w");
        fputcsv($output, ['No', 'Nama', 'NISN', 'Kelas', 'Pusat/Pokjar'], "\t");
    
        $no = 1;
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $nisn = "'" . $row['nisn'];
            fputcsv(
                $output,
                [$no++, $row['nama'], $nisn, $row['kelas'], $row['pusat_pokjar']],
                "\t"
            );
        }
    
        fclose($output);
        exit();
    }

    public function detail()
    {
        $nim = $_GET['nim'] ?? '';
        $stmt = $this->db->prepare("SELECT * FROM tb_mahasiswa WHERE nim = :nim");
        $stmt->execute([':nim' => $nim]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
    
        header('Content-Type: application/json');
        echo json_encode($data);
        exit();
    }
    
public function check()
{
    $nim = $_POST['nim'] ?? '';
    if (!$nim) {
        echo json_encode(['success' => false, 'message' => 'NIM tidak ditemukan']);
        return;
    }

    $stmt = $this->db->prepare("SELECT * FROM tb_mahasiswa WHERE nim = :nim");
    $stmt->execute([':nim' => $nim]);
    $mhs = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$mhs) {
        echo json_encode(['success' => false, 'message' => 'Mahasiswa tidak ditemukan']);
        return;
    }

    $newNisn = $_POST['nisn'] ?? $mhs['nisn'];

    // Ambil semua field dari form
    $fields = $_POST;
    unset($fields['nim']); // jangan update nim langsung

    // --- ubah nama-nama menjadi kapital ---
    $upperKeys = ['nama', 'nama_ayah', 'nama_ibu', 'nama_wali'];
    foreach ($upperKeys as $key) {
        if (isset($fields[$key])) {
            $fields[$key] = strtoupper($fields[$key]);
        }
    }

    $setPart = [];
    $params = [];

    foreach ($fields as $k => $v) {
        $setPart[] = "`$k` = :$k";
        $params[":$k"] = $v;
    }

    $params[':nim'] = $nim;

    // Kalau kolom check masih kosong/null → set jadi 'Check'
    if (empty($mhs['status_check'])) {
        $setPart[] = "`status_check` = 'Check'";
    }

    $sql = "UPDATE tb_mahasiswa SET " . implode(',', $setPart) . " WHERE nim = :nim";
    $stmt = $this->db->prepare($sql);
    $stmt->execute($params);

    // Kalau nisn berubah → update nim, tb_user.username, tb_pmb.nisn
    if ($newNisn !== $mhs['nisn']) {
        $this->db->prepare("UPDATE tb_mahasiswa SET nisn = :nisn WHERE nim = :nim")
                 ->execute([':nisn' => $newNisn, ':nim' => $nim]);

        $this->db->prepare("UPDATE tb_pmb SET nisn = :nisn WHERE nisn = :old")
                 ->execute([':nisn' => $newNisn, ':old' => $mhs['nisn']]);
    }

    echo json_encode(['success' => true]);
}

public function getKecamatan()
{
    $id = $_GET['id'] ?? null;
    if (!$id) {
        echo json_encode([]);
        return;
    }

    // misalnya tabel referensinya bernama tb_wilayah
    $stmt = $this->db->prepare("SELECT kecamatan, kabupaten FROM tb_wilayah WHERE id_wil = :id");
    $stmt->execute([':id' => $id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($row) {
        echo json_encode($row);
    } else {
        echo json_encode([]);
    }
    exit();
}

public function getKecamatanById()
{
    $id = $_GET['id'] ?? '';
    if (!$id) {
        echo json_encode([]);
        exit;
    }

    $sql = "SELECT k.id_wil, k.nm_wil AS kecamatan, kab.nm_wil AS kabupaten
            FROM tb_wilayah k
            JOIN tb_wilayah kab ON kab.id_wil = k.id_induk_wilayah
            WHERE k.id_wil = ?";
    $stmt = $this->db->prepare($sql);
    $stmt->execute([$id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    header('Content-Type: application/json');
    echo json_encode($row ?: []);
    exit;
}


    
}
