<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DosenDownloadNilaiController
{
    private $db;

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

    public function index()
    {
        $kode_mk = $_GET['kode_mk'] ?? '';
        $kode_kelas = $_GET['kelas'] ?? '';
        $tahun_akademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif' LIMIT 1")->fetchColumn();

        $stmt = $this->db->prepare("
            SELECT mhs.nim, mhs.nama, krs.kode_mk, mk.nama_mk,
                khs.nilai, khs.mutu, khs.bobot, khs.sks_x_bobot
            FROM tb_khs khs
            JOIN tb_mahasiswa mhs ON khs.nim = mhs.nim
            JOIN tb_krs krs ON khs.nim = krs.nim AND khs.kode_mk = krs.kode_mk
            JOIN tb_mk mk ON mk.kode_mk = krs.kode_mk
            JOIN tb_kelas kls ON mhs.nim = kls.nim
            WHERE krs.kode_mk = :kode_mk
              AND kls.kode_kelas = :kelas
              AND krs.tahun_akademik = :tahun
              AND kls.tahun_akademik = :tahun
        ");
        $stmt->execute([
            ':kode_mk' => $kode_mk,
            ':kelas' => $kode_kelas,
            ':tahun' => $tahun_akademik
        ]);
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Header untuk Excel
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=Nilai_Kelas_{$kode_kelas}_{$kode_mk}.xls");

        // Ambil data dosen
        $niy = $_SESSION['user']['niy'] ?? '-';
        $dosenStmt = $this->db->prepare("SELECT nama FROM tb_dosen WHERE niy = ?");
        $dosenStmt->execute([$niy]);
        $namaDosen = $dosenStmt->fetchColumn() ?? '-';

        // Ambil nama MK & SKS
        $mkStmt = $this->db->prepare("SELECT nama_mk, sks FROM tb_mk WHERE kode_mk = ?");
        $mkStmt->execute([$kode_mk]);
        $mkData = $mkStmt->fetch(PDO::FETCH_ASSOC);
        $namaMK = $mkData['nama_mk'] ?? '-';
        $sks = $mkData['sks'] ?? '-';

        // Output Excel
        echo "<table border='0' style='font-family:sans-serif;'>";
        echo "<tr><td colspan='7' align='center'><h3>Daftar Nilai Akhir STKIP Babunnajah</h3></td></tr>";
        echo "<tr><td colspan='7' align='center'><strong>Tahun Akademik: {$tahun_akademik}</strong></td></tr>";
        echo "<tr><td colspan='7'>&nbsp;</td></tr>";

        echo "<tr><td colspan='2'>Nama Dosen</td><td colspan='5'>: {$namaDosen}</td></tr>";
        echo "<tr><td colspan='2'>NIY</td><td colspan='5'>: {$niy}</td></tr>";
        echo "<tr><td colspan='2'>Kode MK</td><td colspan='5'>: {$kode_mk}</td></tr>";
        echo "<tr><td colspan='2'>SKS</td><td colspan='5'>: {$sks}</td></tr>";
        echo "<tr><td colspan='2'>Nama MK</td><td colspan='5'>: {$namaMK}</td></tr>";
        echo "<tr><td colspan='2'>Kelas</td><td colspan='5'>: {$kode_kelas}</td></tr>";
        echo "<tr><td colspan='7'>&nbsp;</td></tr>";
        echo "</table>";

        // Tabel Nilai
        echo "<table border='1' style='font-family:sans-serif;'>";
        echo "<tr>
                <th>No</th>
                <th>NIM</th>
                <th>Nama</th>
                <th>Nilai Akhir</th>
                <th>Mutu</th>
                <th>Bobot</th>
                <th>SKS x Bobot</th>
              </tr>";

        $no = 1;
        foreach ($data as $row) {
            echo "<tr>
                    <td>{$no}</td>
                    <td>{$row['nim']}</td>
                    <td>{$row['nama']}</td>
                    <td>{$row['nilai']}</td>
                    <td>{$row['mutu']}</td>
                    <td>{$row['bobot']}</td>
                    <td>{$row['sks_x_bobot']}</td>
                  </tr>";
            $no++;
        }
        echo "</table>";
        exit;
    }
}
