<?php

namespace App\Models\Panel\Ujian;

use CodeIgniter\Model;

class Draft extends Model
{
    protected $DBGroup              = 'default';
    protected $table                = 'draft_ujian';
    protected $primaryKey           = 'id';
    protected $useAutoIncrement     = true;
    protected $insertID             = 0;
    protected $returnType           = 'array';
    protected $useSoftDeletes       = false;
    protected $protectFields        = true;
    protected $allowedFields        = [
        'nama', 'bank_soal_id', 'mapel_id', 'visible_pg', 'visible_pgmulti',
        'visible_pgtf', 'visible_esai', 'bobot_tf', 'bobot_esai', 'acak_soal',
        'acak_opsi', 'timeout', 'visible_shortentry', 'visible_pgcouple',
        'minFinishTime', 'order_soal'
    ];

    // 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          = [];
    protected $select               = [
        "du.*,mm.id mapel_id,mm.kode kode_mapel,mm.nama nama_mapel",
        "mbs.id bank_soal_id,mbs.kode kode_bank_soal,mbs.deskripsi deskripsi_bank_soal",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=1) count_soal_pg",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=2) count_soal_esai",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=3) count_soal_pgmulti",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=4) count_soal_pgcouple",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=5) count_soal_shortentry",
        "(SELECT COUNT(*) FROM soal_data WHERE bank_id=mbs.id AND jenis_soal=6) count_soal_pgtf",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=1) visible_pg",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=2) visible_esai",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=3) visible_pgmulti",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=4) visible_pgcouple",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=5) visible_shortentry",
        "(SELECT COUNT(*) FROM draft_soal sds INNER JOIN soal_data ssd ON ssd.id=sds.soal_id WHERE sds.draft_id=du.id AND ssd.jenis_soal=6) visible_pgtf",
    ];
    public function insert($data = null, bool $returnID = true)
    {
        $db = $this->db;
        $soal = [];
        $soal_data = [];
        if (isset($data['soal'])) {
            $soal = $data['soal'];
            unset($data['soal']);
        }
        $db->transStart();
        $db->table($this->table)->insert($data);
        $id = $db->insertID();
        if ($soal) {
            foreach ($soal as $k => $v) {
                $soal_data[] = ['draft_id' => $id, 'soal_id' => $v];
            }
        }
        if ($soal_data) $db->table('draft_soal')->insertBatch($soal_data);
        $db->transComplete();
        if ($db->transStatus() === FALSE) return null;
        if ($returnID) return $id;
        return true;
    }
    public function update($id = null, $data = null): bool
    {
        $db = $this->db;
        $soal = [];
        $newSoal = false;
        $soal_data = [];
        if (isset($data['soal'])) {
            $soal = $data['soal'];
            unset($data['soal']);
            $newSoal = true;
        }
        $db->transStart();
        $db->table($this->table)->update($data, ['id' => $id]);
        if ($newSoal) $db->table('draft_soal')->delete(['draft_id' => $id]);
        if ($soal) {
            foreach ($soal as $k => $v) {
                $soal_data[] = ['draft_id' => $id, 'soal_id' => $v];
            }
        }
        if ($soal_data) $db->table('draft_soal')->insertBatch($soal_data);
        $db->transComplete();
        if ($db->transStatus() === FALSE) return null;
        return true;
    }
    public function getAll($opt = [])
    {
        $builder = $this->db->table($this->table . " du");
        $builder->select(implode(',', $this->select));
        $builder->join("m_mapel mm", "mm.id=du.mapel_id", "left");
        $builder->join("m_banksoal mbs", "mbs.id=du.bank_soal_id", "left");
        if (isset($opt['user_id'])) {
            $builder->where("mbs.user_id", $opt['user_id']);
        }
        if (isset($opt['acak_soal'])) {
            $builder->where("du.acak_soal", $opt['acak_soal']);
        }
        if (isset($opt['acak_opsi'])) {
            $builder->where("du.acak_opsi", $opt['acak_opsi']);
        }
        if (isset($opt['mapel'])) {
            $builder->where("du.mapel_id", $opt['mapel']);
        }
        if (isset($opt['bank_soal'])) {
            $builder->where("du.bank_soal_id", $opt['bank_soal']);
        }
        if (isset($opt['visible_pg'])) {
            $builder->where("du.visible_pg" . ($opt['visible_pg'] > 0 ? ' >=' : ''), ($opt['visible_pg'] > 0 ? 1 : 0));
        }
        if (isset($opt['visible_pgmulti'])) {
            $builder->where("du.visible_pgmulti" . ($opt['visible_pgmulti'] > 0 ? ' >=' : ''), ($opt['visible_pgmulti'] > 0 ? 1 : 0));
        }
        if (isset($opt['visible_pgtf'])) {
            $builder->where("du.visible_pgtf" . ($opt['visible_pgtf'] > 0 ? ' >=' : ''), ($opt['visible_pgtf'] > 0 ? 1 : 0));
        }
        if (isset($opt['visible_pgcouple'])) {
            $builder->where("du.visible_pgcouple" . ($opt['visible_pgcouple'] > 0 ? ' >=' : ''), ($opt['visible_pgcouple'] > 0 ? 1 : 0));
        }
        if (isset($opt['visible_shortentry'])) {
            $builder->where("du.visible_shortentry" . ($opt['visible_shortentry'] > 0 ? ' >=' : ''), ($opt['visible_shortentry'] > 0 ? 1 : 0));
        }
        if (isset($opt['visible_esai'])) {
            $builder->where("du.visible_esai" . ($opt['visible_esai'] > 0 ? ' >=' : ''), ($opt['visible_esai'] > 0 ? 1 : 0));
        }
        return $builder->get()->getResultArray();
    }

    public function findId($id, $opt = [])
    {
        $builder = $this->db->table($this->table . " du");
        $builder->select(implode(',', $this->select));
        $builder->join("m_mapel mm", "mm.id=du.mapel_id", "left");
        $builder->join("m_banksoal mbs", "mbs.id=du.bank_soal_id", "left");
        $builder->where(["du.id" => $id]);
        if (isset($opt['user_id'])) {
            $builder->where("mbs.user_id", $opt['user_id']);
        }
        $data = $builder->get()->getRowArray();
        return $data;
    }

    public function getSoals($id)
    {
        $banks = $this->db->table('draft_ujian du');
        $banks->join('m_banksoal mb', 'mb.id=du.bank_soal_id');
        $banks->join('soal_data sd', 'sd.bank_id=mb.id');
        $banks->where('du.id', $id);
        $banks->select('sd.*,(CASE WHEN EXISTS(select * from draft_soal ds where ds.draft_id = du.id AND ds.soal_id = sd.id) THEN 1 ELSE 0 END) selected');
        return $banks->get()->getResultArray();
    }
}
