<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DataPPLKController
{
    private $db;

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

    private function authCheck()
    {
        if (!isset($_SESSION['user']['username'])) {
            header("Location: /logout");
            exit;
        }
    }

    /* =======================
     * HALAMAN DATA PPLK
     * ======================= */
    public function index()
    {
        $this->authCheck();

        $tahun_akademik = $_GET['tahun_akademik'] ?? '';

        // Ambil list tahun akademik (dropdown)
        $listAkademik = $this->db
            ->query("SELECT tahun_akademik FROM tb_akademik ORDER BY tahun_akademik DESC")
            ->fetchAll(PDO::FETCH_ASSOC);

        // Query utama PPLK
        $sql = "SELECT * FROM tb_pplk WHERE 1=1";
        $params = [];

        if (!empty($tahun_akademik)) {
            $sql .= " AND tahun_akademik = :tahun";
            $params[':tahun'] = $tahun_akademik;
        }

        $sql .= " ORDER BY tanggal_daftar DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $pplk = $stmt->fetchAll(PDO::FETCH_ASSOC);

        require __DIR__ . '/../Views/pegawai/data_pplk.php';
    }

    /* =======================
     * DOWNLOAD EXCEL PPLK
     * ======================= */
public function download()
{
    // Session & auth
    if (session_status() === PHP_SESSION_NONE) session_start();
    if (!isset($_SESSION['user']['username'])) {
        header("Location: /logout");
        exit;
    }

    // Ambil tahun akademik
    $tahun_akademik = $_GET['tahun_akademik'] ?? null;

    if (!$tahun_akademik) {
        die("Tahun akademik wajib dipilih.");
    }

    // Query data PPLK
    $sql = "
        SELECT 
            nim,
            nama,
            tmpt_lahir,
            tgl_lahir,
            prodi,
            semester,
            jenis_kelamin,
            no_hp,
            alamat,
            status_pekerjaan,
            status_perkawinan,
            tanggal_daftar
        FROM tb_pplk
        WHERE tahun_akademik = :tahun
        ORDER BY nama ASC
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->bindValue(':tahun', $tahun_akademik, PDO::PARAM_STR);
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Nama file
    $namaFile = "DATA_PPLK_" . str_replace('/', '-', $tahun_akademik) . ".xls";

    // Header Excel
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"$namaFile\"");
    header("Cache-Control: max-age=0");

    // Style Excel
    echo "
    <style>
        table {
            font-family: 'Times New Roman';
            font-size: 11pt;
            border-collapse: collapse;
        }
        th, td {
            font-family: 'Times New Roman';
            font-size: 11pt;
            border: 1px solid #999;
            padding: 4px;
            vertical-align: middle;
        }
        th {
            text-align: center;
            font-weight: bold;
            background-color: #f2f2f2;
        }
    </style>
    ";

    echo "<table>";

    // Header kolom
    echo "
    <tr>
        <th width='40'>No</th>
        <th width='150'>NIM</th>
        <th width='300'>Nama</th>
        <th width='130'>Tempat Lahir</th>
        <th width='110'>Tanggal Lahir</th>
        <th width='335'>Prodi</th>
        <th width='170'>SMT</th>
        <th width='90'>JK</th>
        <th width='140'>No WA</th>
        <th width='700'>Alamat</th>
        <th width='160'>Pekerjaan</th>
        <th width='150'>Status Nikah</th>
        <th width='160'>Tanggal Daftar</th>
    </tr>";

    // Data
    $no = 1;
    foreach ($data as $row) {
        echo "<tr>";
        echo "<td align='center'>{$no}</td>";
        echo "<td>{$row['nim']}</td>";
        echo "<td>{$row['nama']}</td>";
        echo "<td>{$row['tmpt_lahir']}</td>";
        echo "<td>{$row['tgl_lahir']}</td>";
        echo "<td>{$row['prodi']}</td>";
        echo "<td align='center'>{$row['semester']}</td>";
        echo "<td align='center'>{$row['jenis_kelamin']}</td>";
        echo "<td>'{$row['no_hp']}</td>";
        echo "<td>{$row['alamat']}</td>";
        echo "<td>{$row['status_pekerjaan']}</td>";
        echo "<td>{$row['status_perkawinan']}</td>";
        echo "<td>{$row['tanggal_daftar']}</td>";
        echo "</tr>";
        $no++;
    }

    echo "</table>";
    exit;
}

}
