<?php
namespace App\Controllers;

use Core\Database;
use PDO;
use App\Middleware\Security;

class ManageNIMMBRController
{
    private $db;
    private $limit = 20;

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

    /* =========================
     * INDEX + LIST DATA
     * ========================= */
    public function index()
    {
        if (!isset($_SESSION['user'])) {
            header("Location: /logout");
            exit;
        }

        $csrf   = Security::generateCSRF();
        $search = $_GET['search'] ?? '';
        $page   = max(1, (int)($_GET['page'] ?? 1));
        $offset = ($page - 1) * $this->limit;

        /* ===== WHERE SEARCH ===== */
        $where = '';
        $param = [];

        if ($search !== '') {
            $where = "WHERE m.nim LIKE :s OR m.nisn LIKE :s OR m.nama LIKE :s";
            $param[':s'] = "%{$search}%";
        }

        /* ===== COUNT (WAJIB DARI tb_mahasiswa) ===== */
        $count = $this->db->prepare("
            SELECT COUNT(*) FROM tb_mahasiswa m $where
        ");
        $count->execute($param);
        $totalData = $count->fetchColumn();
        $totalPage = ceil($totalData / $this->limit);

        /* ===== DATA (AMBIL KELAS TERAKHIR) ===== */
        $stmt = $this->db->prepare("
            SELECT 
                m.nim,
                m.nisn,
                m.nama,
                m.tahun_masuk,
                k.kode_kelas,
                k.smt
            FROM tb_mahasiswa m
            LEFT JOIN tb_kelas k ON k.id_kelas = (
                SELECT MAX(id_kelas)
                FROM tb_kelas
                WHERE nim = m.nim
            )
            $where
            ORDER BY m.tahun_masuk DESC
            LIMIT :limit OFFSET :offset
        ");

        foreach ($param as $k => $v) {
            $stmt->bindValue($k, $v);
        }

        $stmt->bindValue(':limit', $this->limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

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

        require_once __DIR__ . '/../Views/admin/nim_maba.php';
    }

    /* =========================
     * IMPORT EXCEL
     * ========================= */
    public function importExcel()
    {
        if (!isset($_SESSION['user'])) {
            header("Location: /logout");
            exit;
        }
    
        Security::csrfCheck();
    
        if (!isset($_FILES['file_excel']) || $_FILES['file_excel']['error'] !== UPLOAD_ERR_OK) {
            $_SESSION['pesan'] = 'File Excel tidak valid';
            $_SESSION['status_alert'] = 'danger';
            header("Location: /dashboard/admin-nim-mbr");
            exit;
        }
    
        require_once __DIR__ . '/../libraries/PHPExcel/PHPExcel.php';
        require_once __DIR__ . '/../libraries/PHPExcel/IOFactory.php';
    
        $berhasil = 0;
        $skip     = 0;
        $gagal    = 0;
        $logError = [];
    
        try {
    
            $excelFile = $_FILES['file_excel']['tmp_name'];
            $reader = \PHPExcel_IOFactory::createReaderForFile($excelFile);
            $excel  = $reader->load($excelFile);
    
            $sheet = $excel->getSheet(0);
            $max   = $sheet->getHighestDataRow();
    
            $ta = $this->db->query("
                SELECT tahun_akademik FROM tb_akademik 
                WHERE status='Aktif' LIMIT 1
            ")->fetch(PDO::FETCH_ASSOC);
    
            if (!$ta) {
                throw new \Exception('Tahun akademik aktif tidak ditemukan');
            }
    
            for ($row = 2; $row <= $max; $row++) {
    
                $nisn  = trim($sheet->getCell("A{$row}")->getValue());
                $nim   = trim($sheet->getCell("B{$row}")->getValue());
                $kelas = trim($sheet->getCell("C{$row}")->getValue());
                $smt   = trim($sheet->getCell("D{$row}")->getValue());
    
                if ($nisn === '' || $nim === '') {
                    $skip++;
                    continue;
                }
    
                // cek mahasiswa berdasarkan NISN
                $mhs = $this->db->prepare("
                    SELECT nama, nim FROM tb_mahasiswa WHERE nisn=:nisn
                ");
                $mhs->execute([':nisn' => $nisn]);
                $mhs = $mhs->fetch(PDO::FETCH_ASSOC);
    
                if (!$mhs) {
                    $skip++;
                    continue;
                }
    
                try {
                    $this->db->beginTransaction();
    
                    /**
                     * PRE-CHECK DUPLIKAT NIM
                     */
                    $cekNim = $this->db->prepare("
                        SELECT 1 FROM tb_mahasiswa 
                        WHERE nim = :nim AND nisn <> :nisn
                    ");
                    $cekNim->execute([
                        ':nim'  => $nim,
                        ':nisn' => $nisn
                    ]);
    
                    if ($cekNim->fetch()) {
                        $this->db->rollBack();
                        $gagal++;
                        $logError[] = "NISN {$nisn} dan NIM {$nim} sudah terdaftar (data ganda)";
                        continue;
                    }
    
                    // UPDATE tb_mahasiswa
                    $this->db->prepare("
                        UPDATE tb_mahasiswa 
                        SET nim = :nim 
                        WHERE nisn = :nisn
                    ")->execute([
                        ':nim'  => $nim,
                        ':nisn' => $nisn
                    ]);
    
                    // ===== tb_user =====
                    $cekUser = $this->db->prepare("
                        SELECT id_user, username 
                        FROM tb_user 
                        WHERE username IN (:nisn, :nim)
                        ORDER BY id_user ASC
                    ");
                    $cekUser->execute([
                        ':nisn' => $nisn,
                        ':nim'  => $nim
                    ]);
                    $users = $cekUser->fetchAll(PDO::FETCH_ASSOC);
    
                    if (count($users) > 1) {
                        // hapus user duplikat, sisakan yang paling lama
                        for ($i = 1; $i < count($users); $i++) {
                            $this->db->prepare("
                                DELETE FROM tb_user WHERE id_user=:id
                            ")->execute([':id' => $users[$i]['id_user']]);
                        }
                    }
    
                    if (!empty($users)) {
                        $this->db->prepare("
                            UPDATE tb_user SET
                                username = :u,
                                nama     = :n,
                                password = :p,
                                level    = 'Mahasiswa'
                            WHERE id_user = :id
                        ")->execute([
                            ':u'  => $nim,
                            ':n'  => $mhs['nama'],
                            ':p'  => md5('mahasiswa'),
                            ':id' => $users[0]['id_user']
                        ]);
                    }
    
                    // ===== tb_kelas (FIX SESUAI PERMINTAAN) =====
                    $cekKelas = $this->db->prepare("
                        SELECT id_kelas 
                        FROM tb_kelas
                        WHERE nim = :nim AND tahun_akademik = :ta
                        ORDER BY id_kelas ASC
                    ");
                    $cekKelas->execute([
                        ':nim' => $nim,
                        ':ta'  => $ta['tahun_akademik']
                    ]);
    
                    $kelasData = $cekKelas->fetchAll(PDO::FETCH_COLUMN);
    
                    if (count($kelasData) > 0) {
    
                        // UPDATE data terlama
                        $this->db->prepare("
                            UPDATE tb_kelas SET
                                kode_kelas = :k,
                                smt        = :s,
                                Status     = 'Aktif'
                            WHERE id_kelas = :id
                        ")->execute([
                            ':k'  => $kelas,
                            ':s'  => $smt,
                            ':id' => $kelasData[0]
                        ]);
    
                        // hapus duplikat terbaru
                        if (count($kelasData) > 1) {
                            for ($i = 1; $i < count($kelasData); $i++) {
                                $this->db->prepare("
                                    DELETE FROM tb_kelas WHERE id_kelas = :id
                                ")->execute([':id' => $kelasData[$i]]);
                            }
                        }
    
                    } else {
    
                        // belum ada → INSERT
                        $this->db->prepare("
                            INSERT INTO tb_kelas
                            (kode_kelas, nim, smt, tahun_akademik, Status)
                            VALUES (:k, :n, :s, :ta, 'Aktif')
                        ")->execute([
                            ':k'  => $kelas,
                            ':n'  => $nim,
                            ':s'  => $smt,
                            ':ta' => $ta['tahun_akademik']
                        ]);
                    }
    
                    $this->db->commit();
                    $berhasil++;
    
                } catch (\PDOException $e) {
    
                    if ($this->db->inTransaction()) {
                        $this->db->rollBack();
                    }
    
                    $gagal++;
                    $logError[] = "NISN {$nisn} dan NIM {$nim} sudah terdaftar (data ganda)";
                    continue;
                }
            }
    
            $_SESSION['pesan'] =
                "Import selesai.<br>
                 Berhasil: {$berhasil}<br>
                 Skip: {$skip}<br>
                 Gagal: {$gagal}" .
                (!empty($logError)
                    ? "<br><br>Detail Error:<br>" . implode('<br>', $logError)
                    : '');
    
            $_SESSION['status_alert'] = $gagal > 0 ? 'warning' : 'success';
    
        } catch (\Exception $e) {
            $_SESSION['pesan'] = $e->getMessage();
            $_SESSION['status_alert'] = 'danger';
        }
    
        header("Location: /dashboard/admin-nim-mbr");
        exit;
    }
    
}
