<?php
/**
 * Model Nilai
 * Struktur: satu nilai rapor per siswa per mapel per semester
 */
class NilaiModel {

    private $db;

    public function __construct() {
        $this->db = getDB();
    }

    // Semua nilai satu kelas, satu semester
    public function getByKelas($kelasId, $tahunAjaran, $semesterNo) {
        $stmt = $this->db->prepare(
            "SELECT n.*, s.nama AS nama_siswa, s.nis,
                    mp.nama AS nama_mapel, mp.kkm, mp.kode
             FROM nilai n
             JOIN siswa s          ON n.siswa_id = s.id
             JOIN mata_pelajaran mp ON n.mapel_id = mp.id
             WHERE n.kelas_id = ? AND n.tahun_ajaran = ? AND n.semester_no = ?
             ORDER BY s.nama, mp.nama"
        );
        $stmt->bind_param('isi', $kelasId, $tahunAjaran, $semesterNo);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }

    // Nilai satu kelas, satu mapel, satu semester (untuk pre-fill form input)
    public function getByKelasMapel($kelasId, $mapelId, $tahunAjaran, $semesterNo) {
        $stmt = $this->db->prepare(
            "SELECT n.*, s.nama AS nama_siswa, s.nis
             FROM nilai n
             JOIN siswa s ON n.siswa_id = s.id
             WHERE n.kelas_id=? AND n.mapel_id=? AND n.tahun_ajaran=? AND n.semester_no=?
             ORDER BY s.nama"
        );
        $stmt->bind_param('iisi', $kelasId, $mapelId, $tahunAjaran, $semesterNo);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }

    // Nilai satu siswa, semua mapel, satu semester (untuk rapor)
    public function getBySiswa($siswaId, $tahunAjaran, $semesterNo) {
        $stmt = $this->db->prepare(
            "SELECT n.*, mp.nama AS nama_mapel, mp.kkm, mp.kode
             FROM nilai n
             JOIN mata_pelajaran mp ON n.mapel_id = mp.id
             WHERE n.siswa_id = ? AND n.tahun_ajaran = ? AND n.semester_no = ?
             ORDER BY mp.nama"
        );
        $stmt->bind_param('isi', $siswaId, $tahunAjaran, $semesterNo);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }

    // Nilai rapor untuk cetak rapor (dengan info guru)
    public function getRaporSiswa($siswaId, $kelasId, $tahunAjaran, $semesterNo) {
        $stmt = $this->db->prepare(
            "SELECT n.*, mp.nama AS nama_mapel, mp.kode, mp.kkm,
                    g.nama AS nama_guru
             FROM nilai n
             JOIN mata_pelajaran mp ON n.mapel_id = mp.id
             LEFT JOIN guru g       ON mp.guru_id = g.id
             WHERE n.siswa_id=? AND n.kelas_id=? AND n.tahun_ajaran=? AND n.semester_no=?
             ORDER BY mp.nama"
        );
        $stmt->bind_param('iisi', $siswaId, $kelasId, $tahunAjaran, $semesterNo);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }

    // Semua mata pelajaran — tampilkan nama lengkap + guru pengampu
    public function getAllMapel() {
        return query_fetch_all($this->db,
            "SELECT mp.*, g.nama AS nama_guru
             FROM mata_pelajaran mp
             LEFT JOIN guru g ON mp.guru_id = g.id
             ORDER BY mp.nama"
        );
    }

    // Simpan / update nilai rapor (INSERT or UPDATE)
    public function upsert($data) {
        $siswaId   = (int)$data['siswa_id'];
        $kelasId   = (int)$data['kelas_id'];
        $mapelId   = (int)$data['mapel_id'];
        $tahun     = $data['tahun_ajaran'];
        $semNo     = (int)$data['semester_no'];
        $nilaiRapor = round((float)$data['nilai_rapor'], 2);
        $keterangan = $data['keterangan'] ?? '';

        // Predikat otomatis
        $predikat = 'D';
        if ($nilaiRapor >= 90)      $predikat = 'A';
        elseif ($nilaiRapor >= 80)  $predikat = 'B';
        elseif ($nilaiRapor >= 70)  $predikat = 'C';

        // Cek sudah ada?
        $stmt = $this->db->prepare(
            "SELECT id FROM nilai
             WHERE siswa_id=? AND kelas_id=? AND mapel_id=? AND tahun_ajaran=? AND semester_no=?"
        );
        $stmt->bind_param('iiisi', $siswaId, $kelasId, $mapelId, $tahun, $semNo);
        $stmt->execute();
        $existing = stmt_get_result($stmt)->fetch_assoc();

        if ($existing) {
            $stmt = $this->db->prepare(
                "UPDATE nilai SET nilai_rapor=?, predikat=?, keterangan=? WHERE id=?"
            );
            $stmt->bind_param('dssi', $nilaiRapor, $predikat, $keterangan, $existing['id']);
        } else {
            $stmt = $this->db->prepare(
                "INSERT INTO nilai
                 (siswa_id, kelas_id, mapel_id, tahun_ajaran, semester_no, nilai_rapor, predikat, keterangan)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $stmt->bind_param('iiisidss',
                $siswaId, $kelasId, $mapelId, $tahun, $semNo,
                $nilaiRapor, $predikat, $keterangan
            );
        }
        return $stmt->execute();
    }

    // Hapus nilai
    public function delete($id) {
        $stmt = $this->db->prepare("DELETE FROM nilai WHERE id=?");
        $stmt->bind_param('i', $id);
        return $stmt->execute();
    }

    // ----------------------------------------------------------------
    // Rekap nilai siswa: semua mapel x semester 1-6 (SEMUA tahun ajaran)
    // Query berdasarkan siswa_id saja, tanpa filter tahun/semester
    // ----------------------------------------------------------------
    public function getRekapNilaiSiswa($siswaId, $tahunAjaran = null) {
        // Ambil semua mapel — ORDER BY nama lengkap
        $mapelList = query_fetch_all($this->db,
            "SELECT id, kode, nama, kkm FROM mata_pelajaran ORDER BY nama"
        );

        // Ambil SEMUA nilai siswa ini (tanpa filter tahun/semester)
        $stmt = $this->db->prepare(
            "SELECT mapel_id, semester_no, nilai_rapor
             FROM nilai
             WHERE siswa_id = ?
             ORDER BY semester_no"
        );
        $stmt->bind_param('i', $siswaId);
        $stmt->execute();
        $nilaiRows = stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);

        // Susun: [mapel_id][semester_no] = nilai_rapor
        $nilaiMap = [];
        foreach ($nilaiRows as $n) {
            // Jika ada duplikat (beda tahun ajaran), ambil yang terbaru/terbesar
            if (!isset($nilaiMap[$n['mapel_id']][$n['semester_no']])) {
                $nilaiMap[$n['mapel_id']][$n['semester_no']] = $n['nilai_rapor'];
            }
        }

        // Bangun rekap per mapel
        $rekap = [];
        foreach ($mapelList as $mp) {
            $row = [
                'mapel_id' => $mp['id'],
                'kode'     => $mp['kode'],
                'nama'     => $mp['nama'],
                'kkm'      => $mp['kkm'],
                'semester' => [],
                'rata'     => null,
                'predikat' => '-',
            ];

            $total = 0;
            $count = 0;
            for ($s = 1; $s <= 6; $s++) {
                $val = $nilaiMap[$mp['id']][$s] ?? null;
                $row['semester'][$s] = $val;
                if ($val !== null) {
                    $total += $val;
                    $count++;
                }
            }

            if ($count > 0) {
                $rata = round($total / $count, 2);
                $row['rata'] = $rata;
                if ($rata >= 90)     $row['predikat'] = 'A';
                elseif ($rata >= 80) $row['predikat'] = 'B';
                elseif ($rata >= 70) $row['predikat'] = 'C';
                else                 $row['predikat'] = 'D';
            }

            $rekap[] = $row;
        }

        return $rekap;
    }

    // Rata-rata per semester untuk satu siswa (baris footer) — semua tahun
    public function getRataPerSemester($siswaId, $tahunAjaran = null) {
        $stmt = $this->db->prepare(
            "SELECT semester_no,
                    ROUND(AVG(nilai_rapor), 2) AS rata
             FROM nilai
             WHERE siswa_id = ?
             GROUP BY semester_no
             ORDER BY semester_no"
        );
        $stmt->bind_param('i', $siswaId);
        $stmt->execute();
        $rows = stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);

        $result = [];
        foreach ($rows as $r) {
            $result[$r['semester_no']] = $r['rata'];
        }
        return $result;
    }

    // Ambil siswa berdasarkan kelas (untuk dropdown AJAX)
    public function getSiswaByKelas($kelasId) {
        $stmt = $this->db->prepare(
            "SELECT id, nama, nis FROM siswa
             WHERE kelas_id = ? AND status IN ('Aktif','Gaib')
             ORDER BY nama"
        );
        $stmt->bind_param('i', $kelasId);
        $stmt->execute();
        return stmt_get_result($stmt)->fetch_all(MYSQLI_ASSOC);
    }
}
