<?php

namespace App\Models;

use CodeIgniter\Model;

class AscTeacherModel extends Model
{
    protected $table            = 'asc_teachers';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'import_id', 'asc_id', 'name', 'short_code', 'gender', 'color', 'teacher_id'
    ];

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

    // Ambil semua guru milik satu import, urut kode
    public function getByImport($importId)
    {
        return $this->select('asc_teachers.*, 
                              asc_teachers.name as asc_name,
                              asc_teachers.short_code as asc_short_code,
                              COALESCE(teachers.name, asc_teachers.name) as name,
                              COALESCE(teachers.short_code, asc_teachers.short_code) as short_code')
                    ->join('teachers', 'teachers.id = asc_teachers.teacher_id', 'left')
                    ->where('asc_teachers.import_id', $importId)
                    ->orderBy('CAST(CASE WHEN teachers.short_code IS NOT NULL THEN teachers.short_code ELSE asc_teachers.short_code END AS UNSIGNED)', 'ASC')
                    ->findAll();
    }

    // Ambil sebagai lookup array: asc_id => [name, short_code, color]
    // Berguna saat parse XML dan render grid
    public function getLookup($importId)
    {
        $rows   = $this->getByImport($importId);
        $lookup = [];

        foreach ($rows as $row) {
            $lookup[$row['asc_id']] = [
                'name'       => $row['name'],
                'short_code' => $row['short_code'],
                'color'      => $row['color'],
                'gender'     => $row['gender'],
            ];
        }

        return $lookup;
    }

    // Ambil lookup by short_code: short_code => [asc_id, name, color]
    // Berguna untuk selector highlight di view
    public function getLookupByCode($importId)
    {
        $rows   = $this->getByImport($importId);
        $lookup = [];

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

        return $lookup;
    }

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