<?php

namespace App\Models;

use CodeIgniter\Model;

class SchoolSubjectModel extends Model
{
    protected $table            = 'school_subjects';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = true;
    protected $allowedFields    = ['subject_id', 'name', 'code', 'is_active', 'sort_order'];

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Ambil semua mapel sekolah yang aktif
    public function getActiveSubjects()
    {
        return $this->select('school_subjects.*, subjects.level as master_level, subjects.group as subject_group')
                    ->join('subjects', 'subjects.id = school_subjects.subject_id', 'left')
                    ->where('school_subjects.is_active', 1)
                    ->orderBy('school_subjects.sort_order', 'ASC')
                    ->findAll() ?? [];
    }

    public function updateSortOrder($items)
    {
        if (empty($items)) {
            return false;
        }
        
        $success = 0;
        foreach ($items as $index => $id) {
            $result = $this->update($id, ['sort_order' => $index + 1]);
            if ($result) {
                $success++;
            }
        }
        return $success;
    }

    // Cek apakah mapel sudah ada (berdasarkan subject_id atau name)
    public function exists($subjectId = null, $name = null, $excludeId = null)
    {
        $builder = $this->db->table($this->table);

        // Wrap kondisi OR dalam group agar tidak bocor ke kondisi AND lainnya
        $builder->groupStart();
        if ($subjectId) {
            $builder->where('subject_id', $subjectId);
        }
        if ($name) {
            $builder->orWhere('name', $name);
        }
        $builder->groupEnd();

        if ($excludeId) {
            $builder->where('id !=', $excludeId);
        }

        return $builder->get()->getRowArray() ? true : false;
    }

    // Ambil dari master berdasarkan level
    public function getFromMasterByLevel($level)
    {
        $db = \Config\Database::connect();
        return $db->table('subjects')
                  ->select('id, name, code, level')
                  ->where('level', $level)
                  ->orWhere('level', 'ALL')
                  ->orderBy('name', 'ASC')
                  ->get()
                  ->getResultArray();
    }
}