<?php

namespace App\Models;

use CodeIgniter\Model;

class AscClassModel extends Model
{
    protected $table            = 'asc_classes';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'import_id', 'asc_id', 'name', 'short_name', 'grade_level', 'classroom_id'
    ];

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

    // Ambil semua kelas milik satu import, urut grade lalu nama
    public function getByImport($importId)
    {
        return $this->select('asc_classes.*, asc_classes.name as asc_name, COALESCE(classrooms.name, asc_classes.name) as name')
                    ->join('classrooms', 'classrooms.id = asc_classes.classroom_id', 'left')
                    ->where('asc_classes.import_id', $importId)
                    ->orderBy('asc_classes.grade_level', 'ASC')
                    ->orderBy('name', 'ASC')
                    ->findAll();
    }

    // Ambil kelas berdasarkan grade level (7, 8, atau 9)
    public function getByGrade($importId, $gradeLevel)
    {
        return $this->select('asc_classes.*, asc_classes.name as asc_name, COALESCE(classrooms.name, asc_classes.name) as name')
                    ->join('classrooms', 'classrooms.id = asc_classes.classroom_id', 'left')
                    ->where('asc_classes.import_id', $importId)
                    ->where('asc_classes.grade_level', $gradeLevel)
                    ->orderBy('name', 'ASC')
                    ->findAll();
    }

    // Ambil daftar grade level yang ada (misal: [7, 8, 9])
    public function getGradeLevels($importId)
    {
        $rows = $this->select('grade_level')
                     ->where('import_id', $importId)
                     ->where('grade_level IS NOT NULL')
                     ->groupBy('grade_level')
                     ->orderBy('grade_level', 'ASC')
                     ->findAll();

        return array_column($rows, 'grade_level');
    }

    // Ambil sebagai lookup array: asc_id => [name, grade_level]
    public function getLookup($importId)
    {
        $rows   = $this->getByImport($importId);
        $lookup = [];

        foreach ($rows as $row) {
            $lookup[$row['asc_id']] = [
                'name'        => $row['name'],
                'short_name'  => $row['short_name'],
                'grade_level' => $row['grade_level'],
            ];
        }

        return $lookup;
    }

    // Hapus semua kelas milik satu import (untuk re-import)
    public function deleteByImport($importId)
    {
        return $this->where('import_id', $importId)->delete();
    }
}