<?php

namespace App\Controllers;

use Core\Database;
use PDO;

class IPSIPKController
{
    private $db;

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

    public function index()
    {
        // Ambil tahun_masuk unik
        $stmt = $this->db->query("SELECT DISTINCT tahun_masuk FROM tb_mahasiswa ORDER BY tahun_masuk ASC");
        $tahunMasukList = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // --- Ambil parameter pencarian & halaman ---
        $search   = $_GET['search'] ?? '';
        $page     = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $limit    = 25;
        $offset   = ($page - 1) * $limit;

        // --- Query IPK ---
        $queryIPK = "
            SELECT 
                m.nim,
                m.nama,
                ROUND(SUM(k.sks_x_bobot) / NULLIF(SUM(mk.sks), 0), 2) AS ipk
            FROM tb_mahasiswa m
            JOIN tb_khs k 
                ON m.nim = k.nim 
               AND k.accept = 1
            JOIN tb_mk mk 
                ON k.kode_mk = mk.kode_mk 
               AND mk.id_kurikulum = m.id_kurikulum
            GROUP BY m.nim, m.nama
        ";
        $stmtIPK = $this->db->query($queryIPK);
        $ipkData = $stmtIPK->fetchAll(PDO::FETCH_ASSOC);

        $ipkMap = [];
        foreach ($ipkData as $row) {
            $ipkMap[$row['nim']] = $row['ipk'];
        }

        // --- Query IPS ---
        $queryIPS = "
            SELECT 
                m.nim,
                m.nama,
                kls.smt,
                kls.tahun_akademik,
                ROUND(SUM(k.sks_x_bobot) / NULLIF(SUM(mk.sks), 0), 2) AS ips
            FROM tb_mahasiswa m
            JOIN tb_kelas kls 
                ON m.nim = kls.nim 
               AND kls.Status = 'Aktif'
            JOIN tb_khs k 
                ON k.nim = m.nim 
               AND k.smt = kls.smt
            JOIN tb_mk mk 
                ON k.kode_mk = mk.kode_mk 
               AND mk.id_kurikulum = m.id_kurikulum
            GROUP BY m.nim, m.nama, kls.smt, kls.tahun_akademik
            ORDER BY m.nim
        ";
        $stmtIPS = $this->db->query($queryIPS);
        $ipsData = $stmtIPS->fetchAll(PDO::FETCH_ASSOC);

        // Gabungkan IPS + IPK
        $result = [];
        if (!empty($ipsData)) {
            foreach ($ipsData as $row) {
                $result[] = [
                    'nim'            => $row['nim'],
                    'nama'           => $row['nama'],
                    'smt'            => $row['smt'],
                    'tahun_akademik' => $row['tahun_akademik'],
                    'ips'            => $row['ips'],
                    'ipk'            => $ipkMap[$row['nim']] ?? '-',
                ];
            }
        }

        // Tambahkan mahasiswa yang hanya punya IPK
        foreach ($ipkData as $row) {
            $nim = $row['nim'];
            $alreadyExists = array_filter($result, function ($r) use ($nim) {
                return $r['nim'] == $nim;
            });
            if (!$alreadyExists) {
                $result[] = [
                    'nim'            => $nim,
                    'nama'           => $row['nama'],
                    'smt'            => '-',
                    'tahun_akademik' => '-',
                    'ips'            => '-',
                    'ipk'            => $row['ipk'],
                ];
            }
        }

        // --- Filter Pencarian ---
        if (!empty($search)) {
            $result = array_filter($result, function ($row) use ($search) {
                return stripos($row['nim'], $search) !== false || 
                       stripos($row['nama'], $search) !== false;
            });
        }

        // --- Pagination ---
        $totalData   = count($result);
        $totalPages  = ceil($totalData / $limit);
        $pagedResult = array_slice($result, $offset, $limit);

        $data['ipsIpkData'] = $pagedResult;
        $data['page']       = $page;
        $data['totalPages'] = $totalPages;
        $data['search']     = $search;

        require_once __DIR__ . '/../Views/pegawai/ips_ipk.php';
    }

    public function export()
    {
        // Ambil tahun_masuk dari form POST
        $tahun_masuk = $_GET['tahun_masuk'] ?? null;
    
        if (!$tahun_masuk) {
            die("Tahun masuk wajib dipilih.");
        }
    
        // Query gabungan IPS & IPK dengan filter tahun_masuk
        $query = "
            SELECT m.nim, m.nama,
                   ROUND(SUM(k.sks_x_bobot) / NULLIF(SUM(mk.sks), 0), 2) AS ipk
            FROM tb_mahasiswa m
            JOIN tb_khs k ON m.nim = k.nim AND k.accept = 1
            JOIN tb_mk mk ON k.kode_mk = mk.kode_mk AND mk.id_kurikulum = m.id_kurikulum
            WHERE m.tahun_masuk = :tahun_masuk
            GROUP BY m.nim, m.nama
        ";
        $stmt = $this->db->prepare($query);
        $stmt->bindValue(':tahun_masuk', $tahun_masuk, PDO::PARAM_STR);
        $stmt->execute();
        $ipkData = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        $queryIps = "
            SELECT m.nim, m.nama, kls.smt, kls.tahun_akademik,
                   ROUND(SUM(k.sks_x_bobot) / NULLIF(SUM(mk.sks), 0), 2) AS ips
            FROM tb_mahasiswa m
            JOIN tb_kelas kls ON m.nim = kls.nim AND kls.Status = 'Aktif'
            JOIN tb_khs k ON k.nim = m.nim AND k.smt = kls.smt
            JOIN tb_mk mk ON k.kode_mk = mk.kode_mk AND mk.id_kurikulum = m.id_kurikulum
            WHERE m.tahun_masuk = :tahun_masuk
            GROUP BY m.nim, m.nama, kls.smt, kls.tahun_akademik
            ORDER BY m.nim
        ";
        $stmtIps = $this->db->prepare($queryIps);
        $stmtIps->bindValue(':tahun_masuk', $tahun_masuk, PDO::PARAM_STR);
        $stmtIps->execute();
        $ipsData = $stmtIps->fetchAll(PDO::FETCH_ASSOC);
    
        // Susun data gabungan
        $ipsByNim = [];
        foreach ($ipsData as $row) {
            $ipsByNim[$row['nim']][] = $row; 
        }
    
        // Nama file, bisa tambahkan tahun_masuk
        $namaFile = "ips_ipk_mahasiswa_{$tahun_masuk}.xls";
    
        // 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>Semester</th>
                <th>Tahun Akademik</th>
                <th>IPS</th>
                <th>IPK</th>
              </tr>";
    
        $no = 1;
        foreach ($ipkData as $row) {
            $nim = $row['nim'];
            $ipk = $row['ipk'];
    
            if (!empty($ipsByNim[$nim])) {
                foreach ($ipsByNim[$nim] as $ipsRow) {
                    echo "<tr>";
                    echo "<td>{$no}</td>";
                    echo "<td>{$nim}</td>";
                    echo "<td>{$row['nama']}</td>";
                    echo "<td>{$ipsRow['smt']}</td>";
                    echo "<td>{$ipsRow['tahun_akademik']}</td>";
                    echo "<td>{$ipsRow['ips']}</td>";
                    echo "<td>{$ipk}</td>";
                    echo "</tr>";
                    $no++;
                }
            } else {
                // Kalau tidak ada IPS, tetap tampilkan IPK
                echo "<tr>";
                echo "<td>{$no}</td>";
                echo "<td>{$nim}</td>";
                echo "<td>{$row['nama']}</td>";
                echo "<td>-</td>";
                echo "<td>-</td>";
                echo "<td>-</td>";
                echo "<td>{$ipk}</td>";
                echo "</tr>";
                $no++;
            }
        }
    
        echo "</table>";
        exit();
    }

}
