<?php

namespace App\Models;

use CodeIgniter\Model;

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

    protected $primaryKey = 'id_kelas';

    protected $returnType = 'array';

    protected $allowedFields = [
        'nama_kelas'
    ];

    public function getRekapSkorKelas($id_kelas = null)
    {
        $builder = $this->db->table('tb_kelas k');

        $builder->select("
        k.id_kelas,
        k.nama_kelas,
        g.nama AS wali_kelas,

        (
            SELECT COALESCE(SUM(p.skor),0)
            FROM tb_rekap_pelanggaran rp
            JOIN tb_pelanggaran p
                ON p.id_pelanggaran = rp.id_pelanggaran
            JOIN tb_siswa s
                ON s.id_siswa = rp.id_siswa
            WHERE s.id_kelas = k.id_kelas
        ) AS skor_pelanggaran,

        (
            SELECT COALESCE(SUM(pg.poin),0)
            FROM tb_rekap_penghargaan rph
            JOIN tb_penghargaan pg
                ON pg.id_penghargaan = rph.id_penghargaan
            JOIN tb_siswa s2
                ON s2.id_siswa = rph.id_siswa
            WHERE s2.id_kelas = k.id_kelas
        ) AS skor_prestasi
    ");

        $builder->join('tb_guru g', 'g.walikelas = k.id_kelas', 'left');

        if ($id_kelas != null) {
            $builder->where('k.id_kelas', $id_kelas);
        }

        $builder->groupBy([
            'k.id_kelas',
            'k.nama_kelas',
            'g.nama'
        ]);

        return $builder->get()->getResultArray();
    }
}
