<?php

namespace App\Models\Panel\MasterData;

use CodeIgniter\Model;

class Kelas extends Model
{
    protected $DBGroup              = 'default';
    protected $table                = 'm_kelas';
    protected $primaryKey           = 'id';
    protected $useAutoIncrement     = true;
    protected $insertID             = 0;
    protected $returnType           = 'array';
    protected $useSoftDeletes       = false;
    protected $protectFields        = true;
    protected $allowedFields        = ['kode', 'nama', 'is_alumni'];

    // Dates
    protected $useTimestamps        = false;
    protected $dateFormat           = 'datetime';
    protected $createdField         = 'created_at';
    protected $updatedField         = 'updated_at';
    protected $deletedField         = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks       = true;
    protected $beforeInsert         = [];
    protected $afterInsert          = [];
    protected $beforeUpdate         = [];
    protected $afterUpdate          = [];
    protected $beforeFind           = [];
    protected $afterFind            = [];
    protected $beforeDelete         = [];
    protected $afterDelete          = [];

    public function findId($id)
    {
        $builder = $this->db->table($this->table);
        $builder->select("$this->table.*,mt.id tingkat_id,mt.kode kode_tingkat,mj.id jurusan_id,mj.kode kode_jurusan,mj.nama nama_jurusan,tp.id tahun_pelajaran_id,tp.nama nama_tahun_pelajaran,tp.kode kode_tahun_pelajaran,tp.semester semester_tahun_pelajaran");
        $builder->join("kelas_data kd", "kd.kelas_id=$this->table.id", "left");
        $builder->join("m_tingkat mt", "mt.id=kd.tingkat_id", "left");
        $builder->join("m_jurusan mj", "mj.id=kd.jurusan_id", "left");
        $builder->join("tahun_pelajaran tp", "tp.id=kd.tahun_pelajaran_id", "left");
        $builder->where(["$this->table.id" => $id]);
        return $builder->get()->getFirstRow();
    }

    public function getAll($opt = [])
    {
        $builder = $this->db->table($this->table);
        $builder->select("$this->table.*,mt.id tingkat_id,mt.kode kode_tingkat,mj.id jurusan_id,mj.kode kode_jurusan,mj.nama nama_jurusan,tp.id tahun_pelajaran_id,tp.nama nama_tahun_pelajaran,tp.kode kode_tahun_pelajaran,tp.semester semester_tahun_pelajaran");
        $builder->join("kelas_data kd", "kd.kelas_id=$this->table.id", "left");
        $builder->join("m_tingkat mt", "mt.id=kd.tingkat_id", "left");
        $builder->join("m_jurusan mj", "mj.id=kd.jurusan_id", "left");
        $builder->join("tahun_pelajaran tp", "tp.id=kd.tahun_pelajaran_id", "left");

        if (isset($opt['is_alumni'])) $builder->where("$this->table.is_alumni", $opt['is_alumni']);
        return $builder->get()->getResultArray();
    }
    public function ubah($kelas)
    {
        $dumpkelas = [];
        foreach ($this->allowedFields as $key => $value) {
            if (!isset($kelas[$value])) continue;
            $dumpkelas[$value] = $kelas[$value];
        }
        $kelaskey = isset($kelas['id']) ? ['id' => $kelas['id']] : ['kode' => $kelas['kode']];
        $this->db->transStart();
        $this->db->table($this->table)->update($dumpkelas, $kelaskey);
        $this->db->query("INSERT INTO kelas_data (kelas_id) SELECT * FROM (SELECT $kelas[id]) AS tmp WHERE NOT EXISTS (SELECT kelas_id FROM kelas_data WHERE kelas_id = $kelas[id]) LIMIT 1");
        if (isset($kelas['tingkat_id'])) {
            $this->db->table('kelas_data')->update(['tingkat_id' => $kelas['tingkat_id']], ['kelas_id' => $kelas['id']]);
        } elseif (isset($kelas['kode_tingkat'])) {
            $this->db->query("UPDATE kelas_data SET tingkat_id=(SELECT id FROM m_tingkat WHERE kode = '$kelas[kode_tingkat]' LIMIT 1) WHERE kelas_id = $kelas[id]");
        } else {
            $this->db->table('kelas_data')->update(['tingkat_id' => null], ['kelas_id' => $kelas['id']]);
        }
        if (isset($kelas['jurusan_id'])) {
            $this->db->table('kelas_data')->update(['jurusan_id' => $kelas['jurusan_id']], ['kelas_id' => $kelas['id']]);
        } elseif (isset($kelas['kode_jurusan'])) {
            $this->db->query("UPDATE kelas_data SET jurusan_id=(SELECT id FROM m_jurusan WHERE kode = '$kelas[kode_jurusan]' LIMIT 1) WHERE kelas_id = $kelas[id]");
        } else {
            $this->db->table('kelas_data')->update(['jurusan_id' => null], ['kelas_id' => $kelas['id']]);
        }
        if (isset($kelas['tahun_pelajaran_id'])) {
            $this->db->table('kelas_data')->update(['tahun_pelajaran_id' => $kelas['tahun_pelajaran_id']], ['kelas_id' => $kelas['id']]);
        } elseif (isset($kelas['kode_tahun_pelajaran'])) {
            $this->db->query("UPDATE kelas_data SET tahun_pelajaran_id=(SELECT id FROM tahun_pelajaran WHERE kode = '$kelas[kode_tahun_pelajaran]' LIMIT 1) WHERE kelas_id = $kelas[id]");
        } else {
            $this->db->table('kelas_data')->update(['tahun_pelajaran_id' => null], ['kelas_id' => $kelas['id']]);
        }
        $this->db->transComplete();
        return  !($this->db->transStatus() === FALSE);
    }
    public function tambah($kelas)
    {
        $dumpkelas = [];
        foreach ($this->allowedFields as $key => $value) {
            if (!isset($kelas[$value])) continue;
            $dumpkelas[$value] = $kelas[$value];
        }
        $kelas_data = "INSERT INTO kelas_data SET kelas_id=(SELECT id FROM m_kelas WHERE kode = '$kelas[kode]' LIMIT 1)";
        if (isset($kelas['tingkat_id'])) {
            $kelas_data .= ",tingkat_id=$kelas[tingkat_id]";
        } elseif (isset($kelas['kode_tingkat'])) {
            $kelas_data .= ",tingkat_id=(SELECT id FROM m_tingkat WHERE kode = '$kelas[kode]' LIMIT 1)";
        }
        if (isset($kelas['jurusan_id'])) {
            $kelas_data .= ", jurusan_id=$kelas[jurusan_id]";
        } elseif (isset($kelas['kode_jurusan'])) {
            $kelas_data .= ",jurusan_id=(SELECT id FROM m_jurusan WHERE kode = '$kelas[kode]' LIMIT 1)";
        }
        if (isset($kelas['tahun_pelajaran_id'])) {
            $kelas_data .= ", tahun_pelajaran_id=$kelas[tahun_pelajaran_id]";
        } elseif (isset($kelas['kode_tahun_pelajaran'])) {
            $kelas_data .= ",tahun_pelajaran_id=(SELECT id FROM tahun_pelajaran WHERE kode = '$kelas[kode]' LIMIT 1)";
        }
        $this->db->transStart();
        $this->db->table($this->table)->insert($dumpkelas);
        $this->db->query($kelas_data);
        $this->db->transComplete();
        return  !($this->db->transStatus() === FALSE);
    }
}
