<?php
namespace App\Models;
use Core\Model;

class ApiKelas extends Model
{
    protected $table = 'tb_kelas';

    public function getByTahunAkademik($tahun)
    {
        $sql = "SELECT 
                    MIN(k.id_kelas) AS id_kelas_hosting,
                    TRIM(SUBSTRING_INDEX(k.kode_kelas, ' ', 1)) AS nama_kelas_kuliah,
                    MIN(k.nim) AS nim_sample,
                    m.id_prodi,
                    m.id_kurikulum,
                    mk.kode_mk,
                    mk.nama_mk,
                    CAST(k.smt AS UNSIGNED) AS semester_mk,
                    k.tahun_akademik
                FROM tb_akademik a
                JOIN {$this->table} k 
                    ON k.tahun_akademik = a.tahun_akademik
                   AND k.Status = 'Aktif'
                JOIN tb_mahasiswa m 
                    ON m.nim = k.nim
                JOIN tb_mk mk 
                    ON mk.id_prodi = m.id_prodi
                   AND mk.id_kurikulum = m.id_kurikulum
                   AND mk.smt = CAST(k.smt AS UNSIGNED)
                WHERE a.status = 'Aktif'
                  AND a.tahun_akademik = ?
                GROUP BY 
                    nama_kelas_kuliah,
                    k.smt,
                    m.id_prodi,
                    m.id_kurikulum,
                    mk.kode_mk,
                    mk.nama_mk,
                    k.tahun_akademik
                ORDER BY nama_kelas_kuliah, mk.kode_mk";

        return $this->query($sql, [$tahun])->fetchAll();
    }
}
