<?php

namespace App\Models;

use CodeIgniter\Model;

class AscScheduleModel extends Model
{
    protected $table            = 'asc_schedules';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'import_id',
        'class_asc_id', 'class_name', 'grade_level',
        'subject_asc_id', 'subject_name', 'subject_code',
        'teacher_asc_id', 'teacher_name', 'teacher_code', 'teacher_color',
        'day_number', 'day_name', 'period_number', 'start_time', 'end_time',
    ];

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

    // Nama hari untuk konversi
    public static $dayNames = [
        1 => 'Senin',
        2 => 'Selasa',
        3 => 'Rabu',
        4 => 'Kamis',
        5 => 'Jumat',
        6 => 'Sabtu',
    ];

    // ── QUERY DENGAN DISPLAY CODE (PRIORITAS DARI EXISTING) ───────────────

    private function getWithDisplayFields($importId, $gradeLevel = null)
    {
        $builder = $this->select('asc_schedules.*, 
                                  COALESCE(school_subjects.code, asc_subjects.short_code) as display_code,
                                  COALESCE(school_subjects.name, asc_subjects.name) as display_name,
                                  COALESCE(teachers.name, asc_teachers.name) as display_teacher_name,
                                  COALESCE(teachers.short_code, asc_teachers.short_code) as display_teacher_code,
                                  COALESCE(classrooms.name, asc_classes.name) as display_class_name')
                        ->join('asc_subjects', 'asc_subjects.asc_id = asc_schedules.subject_asc_id AND asc_subjects.import_id = asc_schedules.import_id', 'left')
                        ->join('school_subjects', 'school_subjects.id = asc_subjects.school_subject_id', 'left')
                        ->join('asc_teachers', 'asc_teachers.asc_id = asc_schedules.teacher_asc_id AND asc_teachers.import_id = asc_schedules.import_id', 'left')
                        ->join('teachers', 'teachers.id = asc_teachers.teacher_id', 'left')
                        ->join('asc_classes', 'asc_classes.asc_id = asc_schedules.class_asc_id AND asc_classes.import_id = asc_schedules.import_id', 'left')
                        ->join('classrooms', 'classrooms.id = asc_classes.classroom_id', 'left')
                        ->where('asc_schedules.import_id', $importId);

        if ($gradeLevel) {
            $builder = $builder->where('asc_schedules.grade_level', $gradeLevel);
        }

        return $builder;
    }

    // ── GRID DATA (Keseluruhan) ───────────────────────────────────────────
    public function getGridData($importId, $gradeLevel = null)
    {
        $rows = $this->getWithDisplayFields($importId, $gradeLevel)
                     ->orderBy('day_number', 'ASC')
                     ->orderBy('period_number', 'ASC')
                     ->orderBy('class_name', 'ASC')
                     ->findAll();

        $grid = [];
        foreach ($rows as $row) {
            $grid[$row['day_number']][$row['period_number']][$row['class_asc_id']] = $row;
        }

        return $grid;
    }

    // ── JADWAL PER KELAS ──────────────────────────────────────────────────
    public function getByClass($importId, $classAscId)
    {
        $rows = $this->getWithDisplayFields($importId)
                     ->where('class_asc_id', $classAscId)
                     ->orderBy('day_number', 'ASC')
                     ->orderBy('period_number', 'ASC')
                     ->findAll();

        $result = [];
        foreach ($rows as $row) {
            $result[$row['day_number']][$row['period_number']] = $row;
        }

        return $result;
    }

    // ── JADWAL PER GURU ──────────────────────────────────────────────────
    public function getByTeacher($importId, $teacherAscId)
    {
        $rows = $this->getWithDisplayFields($importId)
                     ->where('teacher_asc_id', $teacherAscId)
                     ->orderBy('day_number', 'ASC')
                     ->orderBy('period_number', 'ASC')
                     ->findAll();

        $result = [];
        foreach ($rows as $row) {
            $result[$row['day_number']][$row['period_number']] = $row;
        }

        return $result;
    }

    // ── METHOD LAINNYA (tidak berubah) ────────────────────────────────────
    
    public function getTeacherHours($importId)
    {
        return $this->select('teacher_asc_id, teacher_name, teacher_code, teacher_color, COUNT(*) as total_hours')
                    ->where('import_id', $importId)
                    ->groupBy('teacher_asc_id')
                    ->orderBy('total_hours', 'DESC')
                    ->findAll();
    }

    public function getClassList($importId, $gradeLevel = null)
    {
        $builder = $this->select('class_asc_id, class_name, grade_level')
                        ->where('import_id', $importId)
                        ->groupBy('class_asc_id');

        if ($gradeLevel) {
            $builder = $builder->where('grade_level', $gradeLevel);
        }

        return $builder->orderBy('grade_level', 'ASC')
                       ->orderBy('class_name', 'ASC')
                       ->findAll();
    }

    public function getPeriodList($importId)
    {
        return $this->select('period_number, start_time, end_time')
                    ->where('import_id', $importId)
                    ->groupBy('period_number')
                    ->orderBy('period_number', 'ASC')
                    ->findAll();
    }

    public function getDayList($importId)
    {
        return $this->select('day_number, day_name')
                    ->where('import_id', $importId)
                    ->groupBy('day_number')
                    ->orderBy('day_number', 'ASC')
                    ->findAll();
    }

    public function deleteByImport($importId)
    {
        return $this->where('import_id', $importId)->delete();
    }
}