<?php

namespace App\Http\Controllers;

use App\Models\JadwalUjian;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;
use Validator;
use Illuminate\Support\Facades\DB;

class JadwalUjianController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth', ['only' => ['store', 'update', 'destroy', 'index', 'show']]);
    }

    public function index()
    {
        // Define the raw SQL query
        $jadwalujian = DB::select("
            SELECT
                a.kd_ujian, a.kd_tahun_ajar, a.kd_semester, a.kd_jurusan, a.tanggal_ujian, a.no_ujian,
                a.nim, k.komponen, c.tahun_ajar, d.semester, e.jurusan, f.fakultas, g.nama_lengkap,
                h.tgl_ujian, h.jam, i.kd_matakuliah, i.matakuliah, h.kd_kelas
            FROM 
                kartu_ujian a 
            INNER JOIN m_ujian b ON a.kd_ujian = b.kd_ujian 
            INNER JOIN m_komponen_nilai_matakuliah k ON b.kd_komponen = k.kd_komponen 
            INNER JOIN m_tahun_ajar c ON a.kd_tahun_ajar = c.kd_tahun_ajar 
            INNER JOIN m_semester d ON a.kd_semester = d.kd_semester 
            INNER JOIN m_jurusan e ON a.kd_jurusan = e.kd_jurusan 
            INNER JOIN m_fakultas f ON e.kd_fakultas = f.kd_fakultas 
            INNER JOIN m_mahasiswa g ON a.nim = g.nim AND a.kd_jurusan = g.kd_jurusan
            INNER JOIN m_kelas_detail z ON g.nim = z.nim AND a.kd_jurusan = a.kd_jurusan
                AND a.kd_tahun_ajar = z.kd_tahun_ajar AND a.kd_semester = z.kd_semester
            INNER JOIN jadwal_ujian h ON a.kd_ujian = h.kd_ujian AND a.kd_semester = h.kd_semester 
                AND a.kd_tahun_ajar = h.kd_tahun_ajar AND a.kd_jurusan = h.kd_jurusan 
                AND h.kd_kelas = z.kode_kelas
            INNER JOIN m_matakuliah i ON h.kd_matakuliah = i.kd_matakuliah AND h.kd_jurusan = i.kd_jurusan
            GROUP BY h.kd_matakuliah, h.kd_ujian
            ORDER BY a.kd_semester, h.kd_ujian, h.kd_matakuliah
        ");
    
        // Return the result as a JSON response
        return response()->json($jadwalujian);
    }


    // Mendapatkan data KRS berdasarkan NIM
    public function show($id)
    {
        // Define the raw SQL query
        $jadwalujian = DB::select("
            SELECT
                a.kd_ujian, a.kd_tahun_ajar, a.kd_semester, a.kd_jurusan, a.tanggal_ujian, a.no_ujian,
                a.nim, k.komponen, c.tahun_ajar, d.semester, e.jurusan, f.fakultas, g.nama_lengkap,
                h.tgl_ujian, h.jam, i.kd_matakuliah, i.matakuliah, h.kd_kelas
            FROM 
                kartu_ujian a 
            INNER JOIN m_ujian b ON a.kd_ujian = b.kd_ujian 
            INNER JOIN m_komponen_nilai_matakuliah k ON b.kd_komponen = k.kd_komponen 
            INNER JOIN m_tahun_ajar c ON a.kd_tahun_ajar = c.kd_tahun_ajar 
            INNER JOIN m_semester d ON a.kd_semester = d.kd_semester 
            INNER JOIN m_jurusan e ON a.kd_jurusan = e.kd_jurusan 
            INNER JOIN m_fakultas f ON e.kd_fakultas = f.kd_fakultas 
            INNER JOIN m_mahasiswa g ON a.nim = g.nim AND a.kd_jurusan = g.kd_jurusan
            INNER JOIN m_kelas_detail z ON g.nim = z.nim AND a.kd_jurusan = a.kd_jurusan
                AND a.kd_tahun_ajar = z.kd_tahun_ajar AND a.kd_semester = z.kd_semester
            INNER JOIN jadwal_ujian h ON a.kd_ujian = h.kd_ujian AND a.kd_semester = h.kd_semester 
                AND a.kd_tahun_ajar = h.kd_tahun_ajar AND a.kd_jurusan = h.kd_jurusan 
                AND h.kd_kelas = z.kode_kelas
            INNER JOIN m_matakuliah i ON h.kd_matakuliah = i.kd_matakuliah AND h.kd_jurusan = i.kd_jurusan
            WHERE a.nim = '$id'
            GROUP BY h.kd_matakuliah, h.kd_ujian
            ORDER BY a.kd_semester, h.kd_ujian, h.kd_matakuliah
        ");
    
        // Return the result as a JSON response
        return response()->json($jadwalujian);
    }


}
