<?php

namespace App\Models;

use CodeIgniter\Model;

class TahfidzProgramModel extends Model
{
    protected $table            = 'tahfidz_programs';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = true;
    protected $allowedFields    = [
        'name', 'type', 'academic_period_id', 'description', 'is_active', 'created_by'
    ];

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

    /**
     * Ambil semua program beserta nama periode.
     */
    public function getAllWithPeriod(?int $periodId = null): array
    {
        $builder = $this
            ->select('tahfidz_programs.*, academic_periods.semester, academic_years.name as year_name')
            ->join('academic_periods', 'academic_periods.id = tahfidz_programs.academic_period_id')
            ->join('academic_years', 'academic_years.id = academic_periods.academic_year_id')
            ->where('tahfidz_programs.deleted_at', null)
            ->orderBy('tahfidz_programs.type', 'ASC')
            ->orderBy('tahfidz_programs.name', 'ASC');

        if ($periodId) {
            $builder->where('tahfidz_programs.academic_period_id', $periodId);
        }

        return $builder->findAll();
    }

    /**
     * Ambil program yang diampu oleh seorang guru (untuk menu/filter guru).
     */
    public function getByTeacher(int $teacherId, ?int $periodId = null): array
    {
        $db  = db_connect();
        $sql = "SELECT tp.*, ap.semester, ay.name as year_name
                FROM tahfidz_programs tp
                JOIN tahfidz_program_teachers tpt ON tpt.program_id = tp.id
                JOIN academic_periods ap ON ap.id = tp.academic_period_id
                JOIN academic_years ay ON ay.id = ap.academic_year_id
                WHERE tpt.teacher_id = ?
                  AND tp.deleted_at IS NULL
                  AND tp.is_active = 1";
        $params = [$teacherId];

        if ($periodId) {
            $sql .= " AND tp.academic_period_id = ?";
            $params[] = $periodId;
        }

        $sql .= " ORDER BY tp.type ASC, tp.name ASC";
        return $db->query($sql, $params)->getResultArray();
    }

    /**
     * Cek apakah seorang guru diampu di minimal 1 program tahfidz aktif.
     */
    public function isTeacherAssigned(int $teacherId, ?int $periodId = null): bool
    {
        return count($this->getByTeacher($teacherId, $periodId)) > 0;
    }

    /**
     * Ambil guru-guru pengampu sebuah program.
     */
    public function getTeachers(int $programId): array
    {
        $db = db_connect();
        return $db->query("
            SELECT t.id, t.name, t.nip
            FROM tahfidz_program_teachers tpt
            JOIN teachers t ON t.id = tpt.teacher_id
            WHERE tpt.program_id = ?
            ORDER BY t.name ASC
        ", [$programId])->getResultArray();
    }

    /**
     * Simpan (replace) guru pengampu satu program.
     */
    public function saveTeachers(int $programId, array $teacherIds): void
    {
        $db  = db_connect();
        $now = date('Y-m-d H:i:s');

        // Hapus semua guru lama
        $db->table('tahfidz_program_teachers')
           ->where('program_id', $programId)
           ->delete();

        // Insert guru baru
        foreach ($teacherIds as $tid) {
            $tid = (int) $tid;
            if ($tid > 0) {
                $db->table('tahfidz_program_teachers')->insert([
                    'program_id' => $programId,
                    'teacher_id' => $tid,
                    'created_at' => $now,
                ]);
            }
        }
    }

    public function getStudents(int $programId): array
    {
        $db = db_connect();
        return $db->query("
            SELECT s.id, s.nis, s.full_name, s.gender, tps.joined_at,
                   c.name as classroom_name, c.id as classroom_id
            FROM tahfidz_program_students tps
            JOIN students s ON s.id = tps.student_id
            LEFT JOIN student_academic_histories sah
                   ON sah.student_id = s.id AND sah.academic_period_id = (SELECT id FROM academic_periods WHERE is_active = 1 LIMIT 1)
            LEFT JOIN classrooms c ON c.id = sah.classroom_id
            WHERE tps.program_id = ?
              AND s.deleted_at IS NULL
            ORDER BY s.full_name ASC
        ", [$programId])->getResultArray();
    }

    public function addStudentsFromClassroom(int $programId, int $classroomId, int $periodId): int
    {
        $db  = db_connect();
        $now = date('Y-m-d H:i:s');

        // Ambil semua student_id dari kelas & periode ini yang statusnya 'Aktif'
        $students = $db->query("
            SELECT student_id FROM student_academic_histories
            WHERE classroom_id = ? AND academic_period_id = ? AND status = 'Aktif'
        ", [$classroomId, $periodId])->getResultArray();

        if (empty($students)) {
            return 0;
        }

        // Ambil student_id yang sudah terdaftar di program ini
        $existing = $db->table('tahfidz_program_students')
            ->select('student_id')
            ->where('program_id', $programId)
            ->get()->getResultArray();
        $existingIds = array_column($existing, 'student_id');

        $toInsert = [];
        foreach ($students as $st) {
            if (!in_array($st['student_id'], $existingIds)) {
                $toInsert[] = [
                    'program_id' => $programId,
                    'student_id' => $st['student_id'],
                    'joined_at'  => date('Y-m-d'),
                    'created_at' => $now,
                ];
            }
        }

        if (!empty($toInsert)) {
            $db->table('tahfidz_program_students')->insertBatch($toInsert);
        }

        return count($toInsert);
    }

    /**
     * Tambah/hapus siswa satu-satu (untuk Boarding).
     */
    public function toggleStudent(int $programId, int $studentId): string
    {
        $db  = db_connect();
        $now = date('Y-m-d H:i:s');

        $exists = $db->table('tahfidz_program_students')
            ->where('program_id', $programId)
            ->where('student_id', $studentId)
            ->countAllResults();

        if ($exists) {
            $db->table('tahfidz_program_students')
               ->where('program_id', $programId)
               ->where('student_id', $studentId)
               ->delete();
            return 'removed';
        } else {
            $db->table('tahfidz_program_students')->insert([
                'program_id' => $programId,
                'student_id' => $studentId,
                'joined_at'  => date('Y-m-d'),
                'created_at' => $now,
            ]);
            return 'added';
        }
    }

    /**
     * Hapus semua siswa dari program.
     */
    public function clearStudents(int $programId): void
    {
        db_connect()->table('tahfidz_program_students')
            ->where('program_id', $programId)
            ->delete();
    }
}
