<?php

namespace App\Models;

use CodeIgniter\Model;

class ScheduleEntryModel extends Model
{
    protected $table      = 'schedule_entries';
    protected $primaryKey = 'id';

    protected $allowedFields = [
        'schedule_id', 'schedule_load_id',
        'teacher_id', 'classroom_id', 'school_subject_id',
        'day', 'slot_number', 'timeslot_id',
        'is_manual', 'is_locked',
    ];

    protected $useTimestamps = true;

    // ============================================================
    // HARD CONSTRAINT CHECKS
    // ============================================================

    public function isTeacherBusy(int $scheduleId, int $teacherId, int $day, int $slotNumber, ?int $excludeId = null): bool
    {
        $builder = $this->where('schedule_id', $scheduleId)
                        ->where('teacher_id', $teacherId)
                        ->where('day', $day)
                        ->where('slot_number', $slotNumber);
        if ($excludeId) $builder->where('id !=', $excludeId);
        return $builder->countAllResults() > 0;
    }

    public function isClassroomBusy(int $scheduleId, int $classroomId, int $day, int $slotNumber, ?int $excludeId = null): bool
    {
        $builder = $this->where('schedule_id', $scheduleId)
                        ->where('classroom_id', $classroomId)
                        ->where('day', $day)
                        ->where('slot_number', $slotNumber);
        if ($excludeId) $builder->where('id !=', $excludeId);
        return $builder->countAllResults() > 0;
    }

    // ============================================================
    // QUERY UNTUK GENERATOR
    // ============================================================

    public function countTeacherHoursOnDay(int $scheduleId, int $teacherId, int $day): int
    {
        return $this->where('schedule_id', $scheduleId)
                    ->where('teacher_id', $teacherId)
                    ->where('day', $day)
                    ->countAllResults();
    }

    public function countSubjectInClassOnDay(int $scheduleId, int $classroomId, int $subjectId, int $day): int
    {
        return $this->where('schedule_id', $scheduleId)
                    ->where('classroom_id', $classroomId)
                    ->where('school_subject_id', $subjectId)
                    ->where('day', $day)
                    ->countAllResults();
    }

    public function countByLoad(int $scheduleId, int $loadId): int
    {
        return $this->where('schedule_id', $scheduleId)
                    ->where('schedule_load_id', $loadId)
                    ->countAllResults();
    }

    public function getDayDistribution(int $scheduleId, int $loadId): array
    {
        $rows = $this->select('day, COUNT(*) as cnt')
                     ->where('schedule_id', $scheduleId)
                     ->where('schedule_load_id', $loadId)
                     ->groupBy('day')
                     ->findAll();
        $map = [];
        foreach ($rows as $row) {
            $map[(int)$row['day']] = (int)$row['cnt'];
        }
        return $map;
    }

    // ============================================================
    // QUERY UNTUK TAMPILAN GRID
    // ============================================================

    public function getGridData(int $scheduleId): array
    {
        return $this->db->table('schedule_entries se')
            ->select([
                'se.*',
                't.name as teacher_name',
                't.nip as teacher_nip',
                't.user_id as teacher_user_id',
                't.short_code as teacher_short_code',
                '(SELECT MIN(color) FROM asc_teachers WHERE teacher_id = t.id) as teacher_color',
                'ss.name as subject_name',
                'ss.code as subject_code',
                'c.name as classroom_name',
                'g.name as grade_name',
                'ts.start_time',
                'ts.end_time',
                'ts.label as slot_label',
            ])
            ->join('teachers t',            't.id = se.teacher_id',          'left')
            ->join('school_subjects ss',    'ss.id = se.school_subject_id',  'left')
            ->join('classrooms c',          'c.id = se.classroom_id',        'left')
            ->join('grades g',              'g.id = c.grade_id',             'left')
            ->join('schedule_timeslots ts', 'ts.id = se.timeslot_id',        'left')
            ->where('se.schedule_id', $scheduleId)
            ->orderBy('se.day')
            ->orderBy('se.slot_number')
            ->get()->getResultArray();
    }

    public function getGridByClassroom(int $scheduleId): array
    {
        $rows = $this->getGridData($scheduleId);
        $grid = [];
        foreach ($rows as $row) {
            $grid[$row['classroom_id']][$row['day']][$row['slot_number']][] = $row;
        }
        return $grid;
    }

    public function getGridByTeacher(int $scheduleId): array
    {
        $rows = $this->getGridData($scheduleId);
        $grid = [];
        foreach ($rows as $row) {
            $grid[$row['teacher_id']][$row['day']][$row['slot_number']][] = $row;
        }
        return $grid;
    }

    // ============================================================
    // OPERASI LOCK
    // ============================================================

    /**
     * Lock satu entry
     */
    public function lockEntry(int $id): void
    {
        $this->update($id, ['is_locked' => 1, 'is_manual' => 1]);
    }

    /**
     * Unlock satu entry
     */
    public function unlockEntry(int $id): void
    {
        $this->update($id, ['is_locked' => 0]);
    }

    /**
     * Lock semua entry dari satu load (kunci hasil jadwal guru tertentu)
     */
    public function lockByLoad(int $scheduleId, int $loadId): int
    {
        $this->db->table('schedule_entries')
            ->where('schedule_id', $scheduleId)
            ->where('schedule_load_id', $loadId)
            ->update(['is_locked' => 1, 'is_manual' => 1]);
        return $this->db->affectedRows();
    }

    /**
     * Unlock semua entry dari satu load
     */
    public function unlockByLoad(int $scheduleId, int $loadId): int
    {
        $this->db->table('schedule_entries')
            ->where('schedule_id', $scheduleId)
            ->where('schedule_load_id', $loadId)
            ->update(['is_locked' => 0]);
        return $this->db->affectedRows();
    }

    /**
     * Lock semua entry guru tertentu dalam satu jadwal
     */
    public function lockByTeacher(int $scheduleId, int $teacherId): int
    {
        $this->db->table('schedule_entries')
            ->where('schedule_id', $scheduleId)
            ->where('teacher_id', $teacherId)
            ->update(['is_locked' => 1, 'is_manual' => 1]);
        return $this->db->affectedRows();
    }

    /**
     * Unlock semua entry guru tertentu dalam satu jadwal
     */
    public function unlockByTeacher(int $scheduleId, int $teacherId): int
    {
        $this->db->table('schedule_entries')
            ->where('schedule_id', $scheduleId)
            ->where('teacher_id', $teacherId)
            ->update(['is_locked' => 0]);
        return $this->db->affectedRows();
    }

    // ============================================================
    // OPERASI BATCH
    // ============================================================

    /**
     * Hapus entri generated (is_manual=0 AND is_locked=0) untuk re-generate
     */
    public function deleteGenerated(int $scheduleId): void
    {
        $this->where('schedule_id', $scheduleId)
             ->where('is_manual', 0)
             ->where('is_locked', 0)
             ->delete();
    }

    /**
     * Hapus semua entri kecuali yang locked
     */
    public function deleteAllExceptLocked(int $scheduleId): void
    {
        $this->where('schedule_id', $scheduleId)
             ->where('is_locked', 0)
             ->delete();
    }

    /**
     * Hapus SEMUA entri (termasuk locked) — untuk reset total / hapus jadwal
     */
    public function deleteAll(int $scheduleId): void
    {
        $this->where('schedule_id', $scheduleId)->delete();
    }

    /**
     * Statistik entri untuk satu jadwal
     */
    public function getStats(int $scheduleId): array
    {
        $total    = $this->where('schedule_id', $scheduleId)->countAllResults();
        $generated = $this->where('schedule_id', $scheduleId)->where('is_manual', 0)->countAllResults();
        $manual   = $this->where('schedule_id', $scheduleId)->where('is_manual', 1)->countAllResults();
        $locked   = $this->where('schedule_id', $scheduleId)->where('is_locked', 1)->countAllResults();

        return [
            'total'     => $total,
            'generated' => $generated,
            'manual'    => $manual,
            'locked'    => $locked,
        ];
    }
}