<?php

namespace App\Controllers;

use Core\Database;
use PDO;

require_once __DIR__ . '/../libraries/fpdf/fpdf.php';

class PegawaiTranskipController
{
    private $db;

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

    public function index()
    {
        if ($_SESSION['user']['level'] !== 'Pegawai') {
            header('Location: /login');
            exit;
        }

        $search = $_GET['search'] ?? '';
        $selectedProdi = $_GET['prodi'] ?? '';
        $selectedTahunMasuk = $_GET['tahun_masuk'] ?? '';
        $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $limit = 10;
        $offset = ($page - 1) * $limit;

        // Ambil list tahun masuk
        $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);

        // Hitung total
        $countQuery = "SELECT COUNT(DISTINCT m.nim) as total 
            FROM tb_mahasiswa m
            INNER JOIN tb_prodi p ON m.id_prodi = p.id_prodi
            WHERE (m.nama LIKE :search OR m.nim LIKE :search)";
        if ($selectedProdi) $countQuery .= " AND m.id_prodi = :prodi";
        if ($selectedTahunMasuk) $countQuery .= " AND m.tahun_masuk = :tahun";

        $countStmt = $this->db->prepare($countQuery);
        $countStmt->bindValue(':search', "%$search%");
        if ($selectedProdi) $countStmt->bindValue(':prodi', $selectedProdi);
        if ($selectedTahunMasuk) $countStmt->bindValue(':tahun', $selectedTahunMasuk);
        $countStmt->execute();
        $totalRows = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];
        $totalPages = ceil($totalRows / $limit);

        // Data mahasiswa
        $query = "SELECT m.nim, m.nama, p.nama_prodi, k.kode_kelas 
            FROM tb_mahasiswa m
            INNER JOIN tb_prodi p ON m.id_prodi = p.id_prodi
            LEFT JOIN tb_kelas k ON m.nim = k.nim
            WHERE (m.nama LIKE :search OR m.nim LIKE :search)";
        if ($selectedProdi) $query .= " AND m.id_prodi = :prodi";
        if ($selectedTahunMasuk) $query .= " AND m.tahun_masuk = :tahun";
        $query .= " GROUP BY m.nim ORDER BY m.nim LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($query);
        $stmt->bindValue(':search', "%$search%");
        if ($selectedProdi) $stmt->bindValue(':prodi', $selectedProdi);
        if ($selectedTahunMasuk) $stmt->bindValue(':tahun', $selectedTahunMasuk);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        require __DIR__ . '/../Views/pegawai/transkip_index.php';
    }
    
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.");
    }

    // Ambil nama prodi
    $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 kode MK + 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);

    $kodeMkList = array_column($mkList, 'kode_mk');
    // mapping kode_mk -> smt
    $mkSmtMap = [];
    foreach ($mkList as $m) {
        $mkSmtMap[$m['kode_mk']] = $m['smt'];
    }

    // Query untuk mengambil data seluruh mahasiswa
    $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
                 LEFT JOIN tb_kelas k ON m.nim = k.nim
                 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);

    // Susun data 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' => []
            ];
        }
        $nilaiArray[$nim]['nilai'][$row['kode_mk']] = $row['nilai'];
    }

    // 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>";

    $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 = isset($data['nilai'][$kodeMk]) ? $data['nilai'][$kodeMk] : '';
            echo "<td>$nilai</td>";
        }
        echo "</tr>";
        $no++;
    }

    echo "</table>";
    exit();
}


    public function exportPerMahasiswaPDF()
    {
        $nim = $_GET['nim'] ?? null;
        if (!$nim) die("NIM tidak ditemukan.");

        $stmt = $this->db->prepare("SELECT m.nim, m.nama, p.nama_prodi, m.tahun_masuk, k.kode_kelas
                                     FROM tb_mahasiswa m
                                     JOIN tb_prodi p ON m.id_prodi = p.id_prodi
                                     LEFT JOIN tb_kelas k ON m.nim = k.nim
                                     WHERE m.nim = ?");
        $stmt->execute([$nim]);
        $mhs = $stmt->fetch(PDO::FETCH_ASSOC);

        $stmt = $this->db->prepare("SELECT mk.kode_mk, mk.nama_mk, mk.sks, kh.nilai, kh.mutu, kh.bobot, kh.sks_x_bobot
                                     FROM tb_khs kh
                                     JOIN tb_mk mk ON kh.kode_mk = mk.kode_mk
                                     WHERE kh.nim = ? ORDER BY mk.smt");
        $stmt->execute([$nim]);
        $nilaiList = $stmt->fetchAll(PDO::FETCH_ASSOC);

        ob_clean(); // Bersihkan buffer agar tidak error saat output PDF

        $pdf = new \FPDF();
        $pdf->AddPage();
        $pdf->Image('assets/img/kop_surat.jpg', 3, 3, 205);
        $pdf->Ln(32);

        $pdf->SetFont('Arial', 'B', 14);
        $pdf->Cell(0, 10, 'Transkrip Nilai Mahasiswa', 0, 1, 'C');

        $pdf->SetFont('Arial', '', 12);
        $pdf->Cell(40, 10, 'NIM');
        $pdf->Cell(0, 10, ': ' . $mhs['nim'], 0, 1);
        $pdf->Cell(40, 10, 'Nama');
        $pdf->Cell(0, 10, ': ' . $mhs['nama'], 0, 1);
        $pdf->Cell(40, 10, 'Program Studi');
        $pdf->Cell(0, 10, ': ' . $mhs['nama_prodi'], 0, 1);
        $pdf->Cell(40, 10, 'Kelas');
        $pdf->Cell(0, 10, ': ' . $mhs['kode_kelas'], 0, 1);
        $pdf->Ln(5);

        $pdf->SetFont('Arial', 'B', 11);
        $pdf->Cell(10, 8, 'No', 1);
        $pdf->Cell(25, 8, 'Kode', 1);
        $pdf->Cell(65, 8, 'Nama MK', 1);
        $pdf->Cell(10, 8, 'SKS', 1);
        $pdf->Cell(15, 8, 'Nilai', 1);
        $pdf->Cell(15, 8, 'Mutu', 1);
        $pdf->Cell(15, 8, 'Bobot', 1);
        $pdf->Cell(25, 8, 'SKS x Bobot', 1);
        $pdf->Ln();

        $pdf->SetFont('Arial', '', 11);
        $no = 1;
        foreach ($nilaiList as $row) {
            $pdf->Cell(10, 8, $no++, 1);
            $pdf->Cell(25, 8, $row['kode_mk'], 1);
            $pdf->Cell(65, 8, $row['nama_mk'], 1);
            $pdf->Cell(10, 8, $row['sks'], 1);
            $pdf->Cell(15, 8, $row['nilai'], 1);
            $pdf->Cell(15, 8, $row['mutu'], 1);
            $pdf->Cell(15, 8, $row['bobot'], 1);
            $pdf->Cell(25, 8, $row['sks_x_bobot'], 1);
            $pdf->Ln();
        }

        $pdf->Ln(10);
        $pdf->SetFont('Arial', '', 11);

        $tanggal = date("j F Y");
        $pdf->Cell(0, 6, "Pandeglang, {$tanggal}", 0, 1, 'R');
        $pdf->Ln(5);
        $pdf->Cell(95, 6, "Mengetahui,", 0, 0, 'L');
        $pdf->Cell(95, 6, "Mahasiswa", 0, 1, 'R');
        $pdf->Ln(20);
        $pdf->Cell(95, 6, "................................", 0, 0, 'L');
        $pdf->Cell(95, 6, "{$mhs['nama']}", 0, 1, 'R');

        $pdf->Output("I", "Transkrip_{$nim}.pdf");
        exit;
    }

    public function exportPerMahasiswaXLS()
    {
        $nim = $_GET['nim'] ?? null;
        if (!$nim) die("NIM tidak ditemukan.");
    
        $stmt = $this->db->prepare("SELECT m.nim, m.nama, p.nama_prodi, m.tahun_masuk, k.kode_kelas
                                     FROM tb_mahasiswa m
                                     JOIN tb_prodi p ON m.id_prodi = p.id_prodi
                                     LEFT JOIN tb_kelas k ON m.nim = k.nim
                                     WHERE m.nim = ?");
        $stmt->execute([$nim]);
        $mhs = $stmt->fetch(PDO::FETCH_ASSOC);
    
        $stmt = $this->db->prepare("SELECT mk.kode_mk, mk.nama_mk, mk.sks, kh.nilai, kh.mutu, kh.bobot, kh.sks_x_bobot
                                     FROM tb_khs kh
                                     JOIN tb_mk mk ON kh.kode_mk = mk.kode_mk
                                     WHERE kh.nim = ? ORDER BY mk.smt");
        $stmt->execute([$nim]);
        $nilaiList = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=transkrip_{$nim}.xls");
    
        echo "<table border='0'><tr><td colspan='7'><h3>Transkrip Nilai Mahasiswa</h3></td></tr></table>";
        echo "<table border='0'>";
        echo "<tr><td>NIM</td><td>: {$mhs['nim']}</td></tr>";
        echo "<tr><td>Nama</td><td>: {$mhs['nama']}</td></tr>";
        echo "<tr><td>Prodi</td><td>: {$mhs['nama_prodi']}</td></tr>";
        echo "<tr><td>Kelas</td><td>: {$mhs['kode_kelas']}</td></tr>";
        echo "<tr><td colspan='2'>&nbsp;</td></tr>";
        echo "</table>";
    
        echo "<table border='1'>";
        echo "<tr><th>No</th><th>Kode MK</th><th>Nama MK</th><th>SKS</th><th>Nilai</th><th>Mutu</th><th>Bobot</th><th>SKS x Bobot</th></tr>";
    
        $no = 1;
        foreach ($nilaiList as $row) {
            echo "<tr>
                    <td>{$no}</td>
                    <td>{$row['kode_mk']}</td>
                    <td>{$row['nama_mk']}</td>
                    <td>{$row['sks']}</td>
                    <td>{$row['nilai']}</td>
                    <td>{$row['mutu']}</td>
                    <td>{$row['bobot']}</td>
                    <td>{$row['sks_x_bobot']}</td>
                  </tr>";
            $no++;
        }
    
        echo "</table>";
        exit;
    }

}
