<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class AturJadwalUjianController
{
    private $db;

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

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

			$stmtDelete = $this->db->prepare("DELETE FROM tb_jadwal_ujian WHERE id_jadwal_u = ?");
			if ($stmtDelete->execute([$id_jadwal_u])) {
				// Alert sukses pakai flash message atau js alert
				echo "<script>
					alert('Jadwal ujian berhasil dihapus!');
					window.location.href = '/dashboard/pegawai-jadwal-ujian';
				</script>";
				exit();
			} else {
				echo "<script>
					alert('Gagal menghapus jadwal ujian!');
					window.location.href = '/dashboard/pegawai-jadwal-ujian';
				</script>";
				exit();
			}
		}

        $queryAkademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif'");
        $tahun_akademik = $queryAkademik->fetchColumn();

        if (!$tahun_akademik) {
            $jadwal_ujian = [];
            $per_page = 10;
            $page = 1;
            $offset = 0;
            $total_pages = 0;
            $search = '';
            $dosen_mk = [];
            $jenis_soal = [];
            require __DIR__ . '/../Views/pegawai/atur_jadwal_ujian.php';
            return;
        }

		$jadwal_edit = null;
		$dosenedit_mk = [];

		if (isset($_GET['edit'])) {
			$id_jadwal_u = $_GET['edit'];

			$stmtEdit = $this->db->prepare("SELECT * FROM tb_jadwal_ujian WHERE id_jadwal_u = ?");
			$stmtEdit->execute([$id_jadwal_u]);
			$jadwal_edit = $stmtEdit->fetch(PDO::FETCH_ASSOC);

			if ($jadwal_edit) {
				$iddmk = $jadwal_edit['id_dmk'];
				$stmtEditMK = $this->db->prepare("
					SELECT s.id_dmk, dk.tipe_soal, d.nama AS nama_dosen, mk.nama_mk, dk.kode_kelas, dk.tahun_akademik
					FROM tb_dosen_mk s
					JOIN tb_dosen d ON s.niy = d.niy
					JOIN tb_mk mk ON s.kode_mk = mk.kode_mk
					JOIN tb_soal dk ON s.niy = dk.niy 
									AND s.kode_mk = dk.kode_mk 
									AND s.kode_kelas = dk.kode_kelas
					WHERE 
						dk.tahun_akademik = :tahun_akademik 
						AND s.tahun_akademik = :tahun_akademik
						AND s.id_dmk = :id_dmk
						AND dk.tipe_soal IN (SELECT jenis_soal FROM tb_jenis_soal WHERE status = 'Aktif')
					GROUP BY s.id_dmk
				");
				$stmtEditMK->bindValue(':tahun_akademik', $tahun_akademik, PDO::PARAM_STR);
				$stmtEditMK->bindValue(':id_dmk', $iddmk, PDO::PARAM_STR);
				$stmtEditMK->execute();
				$dosenedit_mk = $stmtEditMK->fetchAll(PDO::FETCH_ASSOC);
			}
		}

        $per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 10;
        $allowed_sizes = [10, 25, 50, 100, 200];
        if (!in_array($per_page, $allowed_sizes)) {
            $per_page = 10;
        }

        $search = $_GET['search'] ?? '';
        $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
        $offset = ($page - 1) * $per_page;

        $query = "
            SELECT 
                dk.niy, dk.kode_mk, mk.nama_mk, mk.id_prodi, d.nama AS nama_dosen, 
                ju.tahun_akademik, dk.kode_kelas,
                ju.id_jadwal_u, ju.tgl_mulai_ujian, ju.jam_mulai, ju.jenis_soal, 
                (
                    SELECT COUNT(*) 
                    FROM tb_soal s 
                    WHERE s.kode_mk = dk.kode_mk 
                      AND s.niy = dk.niy
                ) AS soal_terupload 
            FROM tb_dosen_mk dk 
            JOIN tb_mk mk ON dk.kode_mk = mk.kode_mk
            JOIN tb_dosen d ON dk.niy = d.niy
            JOIN tb_jadwal_ujian ju ON ju.id_dmk = dk.id_dmk 
            JOIN tb_jenis_soal js ON ju.jenis_soal = js.jenis_soal 
            WHERE dk.tahun_akademik = :tahun_akademik 
              AND js.status = 'Aktif' 
              AND (d.nama LIKE :search OR mk.nama_mk LIKE :search)
            LIMIT :limit OFFSET :offset
        ";

        $stmt = $this->db->prepare($query);
        $stmt->bindValue(':tahun_akademik', $tahun_akademik, PDO::PARAM_STR);
        $stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
        $stmt->bindValue(':limit', $per_page, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $jadwal_ujian = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $countQuery = "
            SELECT COUNT(*) 
            FROM tb_dosen_mk dk 
            JOIN tb_mk mk ON dk.kode_mk = mk.kode_mk 
            JOIN tb_dosen d ON dk.niy = d.niy
            WHERE dk.tahun_akademik = :tahun_akademik 
              AND (d.nama LIKE :search OR mk.nama_mk LIKE :search)
        ";

        $stmtCount = $this->db->prepare($countQuery);
        $stmtCount->bindValue(':tahun_akademik', $tahun_akademik, PDO::PARAM_STR);
        $stmtCount->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
        $stmtCount->execute();
        $total_data = $stmtCount->fetchColumn();
        $total_pages = ceil($total_data / $per_page);

        // Query tambahan untuk modal "Tambah Jadwal"
        $queryJenisSoal = $this->db->query("SELECT * FROM tb_jenis_soal WHERE status = 'Aktif'");
        $jenis_soal = $queryJenisSoal->fetchAll(PDO::FETCH_ASSOC);

        $queryDosenMK = $this->db->prepare("
            SELECT s.id_dmk, dk.tipe_soal, d.nama AS nama_dosen, mk.nama_mk, s.kode_kelas, s.tahun_akademik
            FROM tb_dosen_mk s
            JOIN tb_dosen d ON s.niy = d.niy
            JOIN tb_mk mk ON s.kode_mk = mk.kode_mk
            JOIN tb_soal dk ON s.niy = dk.niy 
                            AND s.kode_mk = dk.kode_mk 
                            AND s.kode_kelas = dk.kode_kelas
            WHERE 
                dk.tahun_akademik = :tahun_akademik 
                AND s.tahun_akademik = :tahun_akademik
                AND dk.tipe_soal IN (SELECT jenis_soal FROM tb_jenis_soal WHERE status = 'Aktif')
            GROUP BY s.id_dmk
        ");
        $queryDosenMK->bindValue(':tahun_akademik', $tahun_akademik, PDO::PARAM_STR);
        $queryDosenMK->execute();
        $dosen_mk = $queryDosenMK->fetchAll(PDO::FETCH_ASSOC);

        require __DIR__ . '/../Views/pegawai/atur_jadwal_ujian.php';
    }

    public function store()
    {
        $username = $_SESSION['user']['username'] ?? null;
        if (!$username) {
            header("Location: /login");
            exit();
        }
    
        // Ambil tahun akademik aktif
        $stmtAkademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif'");
        $tahun_akademik = $stmtAkademik->fetchColumn();
    
        if (!$tahun_akademik) {
            echo "<script>alert('Tahun akademik aktif tidak ditemukan!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
            exit;
        }
    
        // Ambil data dari form
        $id_dmk = $_POST['id_dmk'];
        $tgl_mulai_ujian = $_POST['tgl_mulai_ujian'];
        $jam_mulai = $_POST['jam'] . ':' . $_POST['menit'] . ':00';
        $jam_berakhir = $_POST['jama'] . ':' . $_POST['menita'] . ':00';
        $jenis_soal = $_POST['jenis_soal'];
    
        // Cek apakah jadwal sudah ada untuk id_dmk, tahun akademik, dan jenis soal yang aktif
        $stmtCheck = $this->db->prepare("
            SELECT COUNT(*) 
            FROM tb_jadwal_ujian ju
            JOIN tb_jenis_soal js ON ju.jenis_soal = js.jenis_soal
            WHERE ju.id_dmk = ? 
              AND js.status = 'Aktif'
              AND ju.tahun_akademik = ?
              AND ju.jenis_soal = ?
        ");
        $stmtCheck->execute([$id_dmk, $tahun_akademik, $jenis_soal]);
        $count = $stmtCheck->fetchColumn();
    
        if ($count > 0) {
            echo "<script>alert('Mata kuliah pada dosen yang bersangkutan sudah dijadwalkan!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
            exit;
        }
    
        // Cek apakah soal sudah diupload untuk mata kuliah, dosen, tipe soal, dan tahun akademik
        $stmtDMK = $this->db->prepare("SELECT kode_mk, niy FROM tb_dosen_mk WHERE id_dmk = ?");
        $stmtDMK->execute([$id_dmk]);
        $dmk = $stmtDMK->fetch(PDO::FETCH_ASSOC);
    
        if (!$dmk) {
            echo "<script>alert('Data mata kuliah tidak ditemukan!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
            exit;
        }
    
        $stmtSoal = $this->db->prepare("
            SELECT COUNT(*) 
            FROM tb_soal s
            JOIN tb_jenis_soal js ON s.tipe_soal = js.jenis_soal
            WHERE s.kode_mk = ? 
              AND s.niy = ? 
              AND s.tahun_akademik = ? 
              AND s.tipe_soal = ? 
              AND js.status = 'Aktif'
        ");
        $stmtSoal->execute([$dmk['kode_mk'], $dmk['niy'], $tahun_akademik, $jenis_soal]);
        $soalCount = $stmtSoal->fetchColumn();
    
        if ($soalCount == 0) {
            echo "<script>alert('Belum ada soal yang diupload untuk mata kuliah ini!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
            exit;
        }
    
        // Insert jadwal baru
        $stmtInsert = $this->db->prepare("
            INSERT INTO tb_jadwal_ujian 
            (id_jadwal_u, id_dmk, tgl_mulai_ujian, jam_mulai, tgl_berakhir, jam_berakhir, jenis_soal, tahun_akademik) 
            VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)
        ");
    
        $inserted = $stmtInsert->execute([
            $id_dmk,
            $tgl_mulai_ujian,
            $jam_mulai,
            $tgl_mulai_ujian,
            $jam_berakhir,
            $jenis_soal,
            $tahun_akademik
        ]);
    
        if ($inserted) {
            echo "<script>alert('Jadwal ujian berhasil ditambahkan!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
        } else {
            echo "<script>alert('Gagal menambahkan jadwal ujian!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
        }
    }

	public function downloadBelumUpload()
	{
		$stmtAkademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif'");
		$tahun_akademik = $stmtAkademik->fetchColumn();

		$query = "
			SELECT
				d.nama AS nama_dosen, dk.niy, dk.kode_mk, dk.kode_kelas, mk.nama_mk, mk.id_prodi
			FROM tb_dosen_mk dk
			JOIN tb_dosen d ON dk.niy = d.niy
			JOIN tb_mk mk ON dk.kode_mk = mk.kode_mk
			WHERE
				dk.tahun_akademik = :tahun_akademik
				AND NOT EXISTS (
					SELECT 1
					FROM tb_soal s
					WHERE s.niy = dk.niy
					  AND s.tahun_akademik = :tahun_akademik
					  AND s.kode_mk = dk.kode_mk
					  AND s.tipe_soal IN (SELECT id_jenis_s FROM tb_jenis_soal WHERE status = 'Aktif')
				) ORDER BY d.nama ASC
		";

		$stmt = $this->db->prepare($query);
		$stmt->bindParam(':tahun_akademik', $tahun_akademik);
		$stmt->execute();
		$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

		// Header untuk file Excel XML
		header("Content-Type: application/vnd.ms-excel");
		header("Content-Disposition: attachment; filename=daftar_dosen_blm_upload.xls");

		// Output XML Spreadsheet
		echo "<?xml version=\"1.0\"?>\n";
		echo "<?mso-application progid=\"Excel.Sheet\"?>\n";
		echo '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
			  xmlns:o="urn:schemas-microsoft-com:office:office"
			  xmlns:x="urn:schemas-microsoft-com:office:excel"
			  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
			<Worksheet ss:Name="Dosen Belum Upload">
				<Table>
					<Row>
						<Cell><Data ss:Type="String">No</Data></Cell>
						<Cell><Data ss:Type="String">Nama Dosen</Data></Cell>
						<Cell><Data ss:Type="String">NIY</Data></Cell>
						<Cell><Data ss:Type="String">Kode MK</Data></Cell>
						<Cell><Data ss:Type="String">Mata Kuliah</Data></Cell>
						<Cell><Data ss:Type="String">Kode Prodi</Data></Cell>
						<Cell><Data ss:Type="String">Kelas</Data></Cell>
					</Row>';

		$no = 1;
		foreach ($data as $row) {
			echo '<Row>';
			echo '<Cell><Data ss:Type="Number">' . $no . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['nama_dosen']) . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['niy']) . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['kode_mk']) . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['nama_mk']) . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['id_prodi']) . '</Data></Cell>';
			echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['kode_kelas']) . '</Data></Cell>';
			echo '</Row>';
			$no++;
		}

		echo '</Table>
			</Worksheet>
		</Workbook>';
		exit;
	}

    public function downloadSudahUpload()
    {
        $stmtAkademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif'");
        $tahun_akademik = $stmtAkademik->fetchColumn();
    
        $query = "
            SELECT DISTINCT
                d.nama AS nama_dosen,
                dk.niy,
                dk.kode_mk,
                dk.kode_kelas,
                mk.nama_mk,
                mk.id_prodi
            FROM tb_dosen_mk dk
            JOIN tb_dosen d ON dk.niy = d.niy
            JOIN tb_mk mk ON dk.kode_mk = mk.kode_mk
            WHERE
                dk.tahun_akademik = :tahun_akademik
                AND EXISTS (
                    SELECT 1
                    FROM tb_soal s
                    WHERE s.niy = dk.niy
                      AND s.tahun_akademik = :tahun_akademik
                      AND s.kode_mk = dk.kode_mk
                      AND s.tipe_soal IN (
                          SELECT id_jenis_s 
                          FROM tb_jenis_soal 
                          WHERE status = 'Aktif'
                      )
                ) ORDER BY d.nama ASC
        ";
    
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':tahun_akademik', $tahun_akademik);
        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=daftar_dosen_sudah_upload.xls");
    
        echo "<?xml version=\"1.0\"?>\n";
        echo "<?mso-application progid=\"Excel.Sheet\"?>\n";
        echo '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
              xmlns:o="urn:schemas-microsoft-com:office:office"
              xmlns:x="urn:schemas-microsoft-com:office:excel"
              xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
            <Worksheet ss:Name="Dosen Sudah Upload">
                <Table>
                    <Row>
                        <Cell><Data ss:Type="String">No</Data></Cell>
                        <Cell><Data ss:Type="String">Nama Dosen</Data></Cell>
                        <Cell><Data ss:Type="String">NIY</Data></Cell>
                        <Cell><Data ss:Type="String">Kode MK</Data></Cell>
                        <Cell><Data ss:Type="String">Mata Kuliah</Data></Cell>
                        <Cell><Data ss:Type="String">Kode Prodi</Data></Cell>
                        <Cell><Data ss:Type="String">Kelas</Data></Cell>
                    </Row>';
    
        $no = 1;
        foreach ($data as $row) {
            echo '<Row>';
            echo '<Cell><Data ss:Type="Number">' . $no . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['nama_dosen']) . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['niy']) . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['kode_mk']) . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['nama_mk']) . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['id_prodi']) . '</Data></Cell>';
            echo '<Cell><Data ss:Type="String">' . htmlspecialchars($row['kode_kelas']) . '</Data></Cell>';
            echo '</Row>';
            $no++;
        }
    
        echo '</Table>
            </Worksheet>
        </Workbook>';
        exit;
    }

    public function edit()
    {
        $db = Database::getConnection();

        $id_jadwal_u     = $_POST['id_jadwal_u'];
        $id_dmk          = $_POST['id_dmk'];
        $tgl_mulai       = $_POST['tgl_mulai_ujian'];
        $tgl_berakhir    = $_POST['tgl_berakhir'];
        $jam_mulai       = $_POST['jam_mulai'];
        $jam_berakhir    = $_POST['jam_berakhir'];
        $jenis_soal      = $_POST['jenis_soal'];

        $stmt = $db->prepare("UPDATE tb_jadwal_ujian 
            SET id_dmk = ?, tgl_mulai_ujian = ?, jam_mulai = ?, tgl_berakhir = ?, jam_berakhir = ?, jenis_soal = ? 
            WHERE id_jadwal_u = ?
        ");

        if ($stmt->execute([$id_dmk, $tgl_mulai, $jam_mulai, $tgl_berakhir, $jam_berakhir, $jenis_soal, $id_jadwal_u])) {
            echo "<script>alert('Jadwal ujian berhasil diperbarui!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
        } else {
            echo "<script>alert('Gagal memperbarui jadwal ujian!'); window.location.href = '/dashboard/pegawai-jadwal-ujian';</script>";
        }
    }

public function cariDosen()
{
    header('Content-Type: application/json');

    $q = $_GET['q'] ?? '';
    if (strlen($q) < 1) {
        echo json_encode([]);
        return;
    }

    // Ambil tahun akademik aktif
    $stmtAkademik = $this->db->query("SELECT tahun_akademik FROM tb_akademik WHERE status = 'Aktif'");
    $tahun_akademik = $stmtAkademik->fetchColumn();

    if (!$tahun_akademik) {
        echo json_encode([]);
        return;
    }

    // Hanya dosen yang SUDAH UPLOAD soal
    $stmt = $this->db->prepare("
        SELECT s.id_dmk, dk.tipe_soal, d.nama AS nama_dosen, mk.nama_mk, s.kode_kelas
        FROM tb_dosen_mk s
        JOIN tb_dosen d ON s.niy = d.niy
        JOIN tb_mk mk ON s.kode_mk = mk.kode_mk
        JOIN tb_soal dk ON s.niy = dk.niy 
                        AND s.kode_mk = dk.kode_mk 
                        AND s.kode_kelas = dk.kode_kelas
        WHERE 
            dk.tahun_akademik = :tahun_akademik
            AND s.tahun_akademik = :tahun_akademik
            AND dk.tipe_soal IN (SELECT jenis_soal FROM tb_jenis_soal WHERE status = 'Aktif')
            AND (d.nama LIKE :keyword OR mk.nama_mk LIKE :keyword)
        GROUP BY s.id_dmk
        ORDER BY d.nama ASC
        LIMIT 20
    ");
    $stmt->bindValue(':tahun_akademik', $tahun_akademik, PDO::PARAM_STR);
    $stmt->bindValue(':keyword', '%' . $q . '%', PDO::PARAM_STR);
    $stmt->execute();

    $results = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $results[] = [
            'id' => $row['id_dmk'],
            'text' => $row['nama_dosen'] . ' - ' . $row['nama_mk'] . ' (' . $row['kode_kelas'] . ')'
        ];
    }

    echo json_encode($results);
}


}
