<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DataMHSController
{
    private $db;

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

    public function index()
    {
        $username = $_SESSION['user']['username'] ?? null;
        if (!$username) {
            header("Location: /logout");
            exit();
        }

        // Ambil parameter dari GET
        $tahun_masuk = $_GET['tahun_masuk'] ?? '';
        $search = $_GET['search'] ?? '';
        $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $limit = 10;
        $offset = ($page - 1) * $limit;

        // Query untuk dropdown tahun masuk
        $stmtTahun = $this->db->query("SELECT DISTINCT tahun_masuk FROM tb_mahasiswa ORDER BY tahun_masuk DESC");
        $daftar_tahun = $stmtTahun->fetchAll(PDO::FETCH_COLUMN);

        // Query utama mahasiswa
        $query = "
            SELECT m.*, p.nama_prodi 
            FROM tb_mahasiswa m
            INNER JOIN tb_prodi p ON m.id_prodi = p.id_prodi
        ";

        $params = [];
        $conditions = [];

        if (!empty($tahun_masuk)) {
            $conditions[] = "m.tahun_masuk = :tahun_masuk";
            $params[':tahun_masuk'] = $tahun_masuk;
        }

        if (!empty($search)) {
            $conditions[] = "(m.nama LIKE :search OR m.nim LIKE :search)";
            $params[':search'] = "%" . $search . "%";
        }

        if ($conditions) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        $query .= " LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($query);

        // Bind parameter
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }

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

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

        // Data untuk pagination total
        $countQuery = "SELECT COUNT(*) FROM tb_mahasiswa m";
        if ($conditions) {
            $countQuery .= " WHERE " . implode(" AND ", $conditions);
        }

        $stmtCount = $this->db->prepare($countQuery);
        foreach ($params as $key => $value) {
            $stmtCount->bindValue($key, $value);
        }
        $stmtCount->execute();
        $totalData = $stmtCount->fetchColumn();
        $totalPages = ceil($totalData / $limit);

        // Kirim ke view
        require __DIR__ . '/../Views/pegawai/data_mahasiswa.php';
    }

	public function download()
	{
		$username = $_SESSION['user']['username'] ?? null;
		if (!$username) {
			header("Location: /logout");
			exit();
		}

		$tahun_masuk = $_GET['tahun_masuk'] ?? '';

		// Query data
		$query = "
			SELECT m.nama, m.nim, p.nama_prodi 
			FROM tb_mahasiswa m
			INNER JOIN tb_prodi p ON m.id_prodi = p.id_prodi
		";

		$params = [];
		if (!empty($tahun_masuk)) {
			$query .= " WHERE m.tahun_masuk = :tahun_masuk";
			$params[':tahun_masuk'] = $tahun_masuk;
		}

		$stmt = $this->db->prepare($query);
		foreach ($params as $key => $value) {
			$stmt->bindValue($key, $value);
		}
		$stmt->execute();

		// Set nama file
		$filename = $tahun_masuk ? "data_mahasiswa_angkatan_$tahun_masuk.xls" : "data_mahasiswa.xls";

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

		// Output ke file
		$output = fopen("php://output", "w");

		// Header kolom
		fputcsv($output, ['No', 'Nama', 'NIM', 'Program Studi'], "\t");

		// Data isi
		$no = 1;
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
			fputcsv($output, [$no++, $row['nama'], $row['nim'], $row['nama_prodi']], "\t");
		}

		fclose($output);
		exit();
	}

public function importExcel()
{
    if (!isset($_SESSION['user'])) {
        header("Location: /logout");
        exit;
    }

    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/pegawai-data-mhs");
        exit;
    }

    require_once __DIR__ . '/../libraries/PHPExcel/PHPExcel.php';
    require_once __DIR__ . '/../libraries/PHPExcel/IOFactory.php';

    try {
        $excelFile = $_FILES['file_excel']['tmp_name'];
        $reader = \PHPExcel_IOFactory::createReaderForFile($excelFile);
        $excel = $reader->load($excelFile);

        $sheet = $excel->getSheet(0);
        $highestRow = $sheet->getHighestDataRow();

        $tahunMasuk = date('Y');
        $berhasil = 0;
        $duplikat = 0;

        for ($row = 2; $row <= $highestRow; $row++) {
            $pw            = 'mahasiswa';
            $nim           = trim($sheet->getCell("A{$row}")->getValue());
            $idProdi       = trim($sheet->getCell("B{$row}")->getValue());
            $nama          = trim($sheet->getCell("C{$row}")->getValue());
            $tglMasuk      = trim($sheet->getCell("D{$row}")->getValue());
            $mulaiSemester = trim($sheet->getCell("E{$row}")->getValue());
            $tahunMasuk    = trim($sheet->getCell("F{$row}")->getValue());
            $idKurikulum   = trim($sheet->getCell("G{$row}")->getValue());


            if ($nim === '' || $nama === '') {
                continue;
            }

            // 🔴 CEK DUPLIKAT MAHASISWA
            $cek = $this->db->prepare("SELECT COUNT(*) FROM tb_mahasiswa WHERE nim = :nim");
            $cek->execute([':nim' => $nim]);
            if ($cek->fetchColumn() > 0) {
                $duplikat++;
                continue;
            }

            // TRANSAKSI
            $this->db->beginTransaction();

            // INSERT tb_mahasiswa
            $stmtMhs = $this->db->prepare("
                INSERT INTO tb_mahasiswa 
                (nim, id_prodi, nama, tgl_masuk_kuliah, mulai_semester, tahun_masuk, id_kurikulum)
                VALUES
                (:nim, :id_prodi, :nama, :tgl_masuk, :mulai_semester, :tahun_masuk, :id_kurikulum)
            ");

            $stmtMhs->execute([
                ':nim' => $nim,
                ':id_prodi' => $idProdi,
                ':nama' => $nama,
                ':tgl_masuk' => $tglMasuk,
                ':mulai_semester' => $mulaiSemester,
                ':tahun_masuk' => $tahunMasuk,
                ':id_kurikulum' => $idKurikulum
            ]);

            // INSERT tb_user
            $stmtUser = $this->db->prepare("
                INSERT INTO tb_user
                (nama, username, password, level)
                VALUES
                (:nama, :username, :password, 'Mahasiswa')
            ");

            $stmtUser->execute([
                ':nama' => $nama,
                ':username' => $nim,
                ':password' => md5($pw)
            ]);

            $this->db->commit();
            $berhasil++;
        }

        $_SESSION['pesan'] = "Import selesai. Berhasil: {$berhasil}, Duplikat: {$duplikat}";
        $_SESSION['status_alert'] = "success";

    } catch (\Exception $e) {
        if ($this->db->inTransaction()) {
            $this->db->rollBack();
        }
        $_SESSION['pesan'] = "Gagal import: " . $e->getMessage();
        $_SESSION['status_alert'] = "danger";
    }

    header("Location: /dashboard/pegawai-data-mhs");
    exit;
}

}
