<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DonNilaiDosenController
{
    private $db;

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

    public function index()
    {
        // ✅ Cek sesi dosen
        $niy = $_SESSION['user']['niy'] ?? null;
        if (!$niy) {
            header("Location: /login");
            exit();
        }
    
        // ✅ Setup pagination
        $recordsPerPage = 10;
        $currentPage = isset($_GET['page']) && is_numeric($_GET['page']) ? (int)$_GET['page'] : 1;
        $offset = ($currentPage - 1) * $recordsPerPage;
    
        // ✅ Ambil keyword pencarian
        $search = $_GET['search'] ?? '';
    
        // ✅ Ambil id_prodi dosen langsung dari tb_dosen
        $stmt = $this->db->prepare("SELECT id_prodi FROM tb_dosen WHERE niy = :niy LIMIT 1");
        $stmt->bindParam(':niy', $niy);
        $stmt->execute();
        $prodi = $stmt->fetchColumn();  
    
        // ✅ Ambil list tahun angkatan
        $tahunStmt = $this->db->query("SELECT DISTINCT tahun_masuk FROM tb_mahasiswa ORDER BY tahun_masuk DESC");
        $tahunAngkatanList = $tahunStmt->fetchAll(PDO::FETCH_ASSOC);
        
        // ✅ Ambil list kurikulum
        $kurikulumStmt = $this->db->query("SELECT id_kurikulum, id_prodi, keterangan FROM tb_kurikulum");
        $kurikulumList = $kurikulumStmt->fetchAll(PDO::FETCH_ASSOC);
    
        // ✅ Jika tidak ada prodi (dosen tidak ditemukan)
        if (!$prodi) {
            $mahasiswa = [];
            $totalPages = 1;
            require __DIR__ . '/../Views/dosen/form_download_nilai.php';
            return;
        }
    
        // ✅ Query kondisi dasar
        $where = "WHERE tm.id_prodi = :prodi AND ta.status = 'Aktif'";
        $params = [':prodi' => $prodi];
    
        // ✅ Tambahkan pencarian jika ada
        if (!empty($search)) {
            $where .= " AND (td.nama LIKE :search OR td.niy LIKE :search)";
            $params[':search'] = "%$search%";
        }
    
        // ✅ Ambil data dosen MK
        $sql = "SELECT tdm.kode_mk, tm.id_prodi, tdm.niy, ta.status, ta.tahun_akademik,
                       td.nama, tm.singkatan, tm.smt, tdm.kode_kelas 
                FROM tb_dosen_mk tdm
                INNER JOIN tb_mk tm ON tdm.kode_mk = tm.kode_mk
                INNER JOIN tb_akademik ta ON tdm.tahun_akademik = ta.tahun_akademik
                INNER JOIN tb_dosen td ON tdm.niy = td.niy
                $where
                ORDER BY tm.smt ASC
                LIMIT :offset, :recordsPerPage";
    
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindValue(':recordsPerPage', $recordsPerPage, PDO::PARAM_INT);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        $mahasiswa = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        // ✅ Hitung total data untuk pagination
        $totalSql = "SELECT COUNT(*) AS total
                     FROM tb_dosen_mk tdm
                     INNER JOIN tb_mk tm ON tdm.kode_mk = tm.kode_mk
                     INNER JOIN tb_akademik ta ON tdm.tahun_akademik = ta.tahun_akademik
                     INNER JOIN tb_dosen td ON tdm.niy = td.niy
                     $where";
    
        $totalStmt = $this->db->prepare($totalSql);
        foreach ($params as $key => $value) {
            $totalStmt->bindValue($key, $value);
        }
        $totalStmt->execute();
        $total = $totalStmt->fetch(PDO::FETCH_ASSOC)['total'];
        $totalPages = ceil($total / $recordsPerPage);
    
        // ✅ Kirim semua data ke view
        require __DIR__ . '/../Views/dosen/form_download_nilai.php';
    }

    public function downloadExcel()
    {
        // Ambil data dari POST
        $namaDosen = $_POST['nama'] ?? 'Tidak diketahui';
        $smt = $_POST['smt'] ?? '-';
        $namaMk = $_POST['nama_mk'] ?? '-';
        $kodeMk = $_POST['kode_mk'] ?? '-';
        $kodeKelas = $_POST['kode_kelas'] ?? '-';
        $tahunAkademik = $_POST['tahun_akademik'] ?? '-';

        // Nama file
        $namafile = "$namaMk, $namaDosen, $kodeKelas laporan-excel.xls";

        // Header untuk Excel
        header("Content-Type: application/vnd-ms-excel");
        header("Content-Disposition: attachment; filename=\"$namafile\"");

        // Ambil data mahasiswa
        $stmt = $this->db->prepare("
            SELECT 
                tk.nim, tm.nama, tk.nilai, tk.mutu, tk.bobot
            FROM 
                tb_khs tk
            INNER JOIN 
                tb_mahasiswa tm ON tk.nim = tm.nim
            INNER JOIN 
                tb_kelas tl ON tk.nim = tl.nim
            WHERE 
                tk.kode_mk = :kode_mk AND 
                tl.kode_kelas = :kode_kelas AND 
                tl.tahun_akademik = :tahun_akademik
            ORDER BY tk.nim ASC
        ");
        $stmt->execute([
            ':kode_mk' => $kodeMk,
            ':kode_kelas' => $kodeKelas,
            ':tahun_akademik' => $tahunAkademik
        ]);

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

        // Output HTML sebagai Excel
        echo "
        <html>
        <head>
            <meta charset='UTF-8'>
            <title>Export Excel</title>
        </head>
        <body>
        <p align='center' style='font-weight:bold;font-size:16pt'>DAFTAR NILAI</p><br/>
        <table border='0'>
            <tr><td><strong>Nama Dosen</strong></td><td>: {$namaDosen}</td></tr>
            <tr><td><strong>Kode/Nama MK</strong></td><td>: {$kodeMk} / {$namaMk}</td></tr>
            <tr><td><strong>Kelas/SMT</strong></td><td>: {$kodeKelas} / {$smt}</td></tr>
        </table>
        <br/>
        <table border='1' align='center'>
            <tr>
                <th width='50'>No</th>
                <th width='170'>NIM</th>
                <th width='380'>Nama</th>
                <th width='100'>Nilai</th>
                <th width='100'>Mutu</th>
                <th width='100'>Bobot</th>
            </tr>
        ";

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

        echo "</table></body></html>";
    }

    public function export()
    {
        $selectedProdi = $_GET['prodi'] ?? '';
        $selectedTahunAngkatan = $_GET['tahun_masuk'] ?? '';
        $selectedKurikulum = $_GET['id_kurikulum'] ?? '';
    
        if (!$selectedProdi || !$selectedTahunAngkatan || !$selectedKurikulum) {
            die("Parameter prodi dan tahun_masuk wajib diisi.");
        }
    
        // Nama file
        $stmtProdi = $this->db->prepare("SELECT nama_prodi FROM tb_prodi WHERE id_prodi = ?");
        $stmtProdi->execute([$selectedProdi]);
        $namaProdi = $stmtProdi->fetchColumn() ?: 'Prodi';
        $namaFile = 'transkrip_nilai_' . strtolower(str_replace(' ', '_', $namaProdi)) . '_angkatan_' . $selectedTahunAngkatan . '_idkrklm_' . $selectedKurikulum . '.xls';
    
        // Ambil semua MK sesuai kurikulum & prodi (dengan semester)
        $kodeMkQuery = "SELECT kode_mk, smt FROM tb_mk 
                        WHERE id_prodi = :prodi AND id_kurikulum = :id_kurikulum 
                        ORDER BY smt ASC";
        $kodeMkStmt = $this->db->prepare($kodeMkQuery);
        $kodeMkStmt->bindValue(':prodi', $selectedProdi, PDO::PARAM_INT);
        $kodeMkStmt->bindValue(':id_kurikulum', $selectedKurikulum, PDO::PARAM_STR);
        $kodeMkStmt->execute();
        $mkList = $kodeMkStmt->fetchAll(PDO::FETCH_ASSOC);
    
        // buat list kode MK saja untuk header excel
        $kodeMkList = array_column($mkList, 'kode_mk');
        // mapping kode_mk -> smt
        $mkSmtMap = [];
        foreach ($mkList as $m) {
            $mkSmtMap[$m['kode_mk']] = $m['smt'];
        }
    
        // Ambil data mahasiswa yang punya kelas saja
        $queryAll = "SELECT m.nim, m.nama, p.nama_prodi, mk.kode_mk, kh.nilai, k.kode_kelas
                     FROM tb_mahasiswa m
                     INNER JOIN tb_prodi p ON m.id_prodi = p.id_prodi
                     INNER JOIN tb_kelas k ON m.nim = k.nim   -- ✅ hanya mahasiswa dengan kelas
                     LEFT JOIN tb_khs kh ON m.nim = kh.nim
                     LEFT JOIN tb_mk mk ON kh.kode_mk = mk.kode_mk
                     WHERE m.id_prodi = :prodi AND m.tahun_masuk = :tahun_masuk 
                     ORDER BY m.nim ASC, k.kode_kelas ASC";
    
        $stmtAll = $this->db->prepare($queryAll);
        $stmtAll->bindValue(':prodi', $selectedProdi, PDO::PARAM_INT);
        $stmtAll->bindValue(':tahun_masuk', $selectedTahunAngkatan, PDO::PARAM_STR);
        $stmtAll->execute();
        $dataAll = $stmtAll->fetchAll(PDO::FETCH_ASSOC);
    
        // Ambil semua nim & smt dari tb_token_khs untuk cek
        $tokenStmt = $this->db->prepare("SELECT nim, smt FROM tb_token_khs");
        $tokenStmt->execute();
        $tokenRows = $tokenStmt->fetchAll(PDO::FETCH_ASSOC);
    
        $nimSmtToken = [];
        foreach ($tokenRows as $tr) {
            $nimSmtToken[$tr['nim']][$tr['smt']] = true;
        }
    
        // Susun array nilai per mahasiswa
        $nilaiArray = [];
        foreach ($dataAll as $row) {
            $nim = $row['nim'];
    
            if (!isset($nilaiArray[$nim])) {
                $nilaiArray[$nim] = [
                    'nim' => $row['nim'],
                    'nama' => $row['nama'],
                    'prodi' => $row['nama_prodi'],
                    'kode_kelas' => $row['kode_kelas'],
                    'nilai' => array_fill_keys($kodeMkList, '') // isi semua MK kosong
                ];
            }
    
            $kodeMk = $row['kode_mk'];
            if (!$kodeMk || !isset($mkSmtMap[$kodeMk])) continue; // skip jika MK tidak valid
    
            $smt = $mkSmtMap[$kodeMk];
            $nilaiKhs = $row['nilai'];
    
            // cek apakah nim punya token di semester mk ini
            $adaToken = isset($nimSmtToken[$nim][$smt]);
    
            if ($adaToken) {
                // mahasiswa punya token → pakai nilai asli
                $nilaiArray[$nim]['nilai'][$kodeMk] = $nilaiKhs !== null ? $nilaiKhs : 0;
            } else {
                // mahasiswa tidak punya token di smt mk ini
                $nilai = $nilaiKhs !== null ? (float)$nilaiKhs : 0;
                $nilaiArray[$nim]['nilai'][$kodeMk] = $nilai < 65 ? 'TL' : 'L';
            }
        }
    
        // warna per semester
        $warnaSmt = [
            1 => '#d6eaff', // biru muda
            2 => '#d6ffd6', // hijau muda
            3 => '#fff5d6', // kuning muda
            4 => '#ffd6d6', // merah muda
            5 => '#e6ccff', // ungu muda
            6 => '#ccffe6', // hijau tosca
            7 => '#ffe6cc', // oranye muda
            8 => '#ccccff', // biru lavender
        ];
    
        // Output header Excel
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=\"$namaFile\"");
    
        echo "<table border='1' style='font-family:sans-serif;'>";
        echo "<tr><th>No</th><th>NIM</th><th>Nama</th><th>Program Studi</th><th>Kelas</th>";
        foreach ($kodeMkList as $kodeMk) {
            $smt = $mkSmtMap[$kodeMk] ?? 0;
            $bgColor = isset($warnaSmt[$smt]) ? $warnaSmt[$smt] : '#ffffff'; 
            echo "<th style='background-color:{$bgColor};'>$kodeMk</th>";
        }
        echo "</tr>";
    
        // Isi data
        $no = 1;
        foreach ($nilaiArray as $data) {
            echo "<tr>";
            echo "<td>{$no}</td>";
            echo "<td>{$data['nim']}</td>";
            echo "<td>{$data['nama']}</td>";
            echo "<td>{$data['prodi']}</td>";
            echo "<td>{$data['kode_kelas']}</td>";
            foreach ($kodeMkList as $kodeMk) {
                $nilai = $data['nilai'][$kodeMk];
                echo "<td>$nilai</td>";
            }
            echo "</tr>";
            $no++;
        }
    
        echo "</table>";
        exit();
    }

    
}
