<?php

namespace App\Models;

use CodeIgniter\Model;

class TahfidzSetoranModel extends Model
{
    protected $table            = 'tahfidz_setoran';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'program_id', 'student_id', 'teacher_id', 'surah_id',
        'ayat_start', 'ayat_end', 'jenis', 'nilai', 'catatan', 'setoran_date',
        'is_iqra', 'iqra_no', 'iqra_page_start', 'iqra_page_end'
    ];

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

    /**
     * Ambil setoran satu siswa di satu program (dengan info surah).
     */
    public function getByStudentProgram(int $studentId, int $programId, ?string $limitDate = null): array
    {
        $builder = $this
            ->select('tahfidz_setoran.*, qs.name_latin, qs.name_arabic, qs.name_id, qs.total_ayat,
                      t.name as teacher_name')
            ->join('quran_surahs qs', 'qs.id = tahfidz_setoran.surah_id', 'left')
            ->join('teachers t', 't.id = tahfidz_setoran.teacher_id', 'left')
            ->where('tahfidz_setoran.student_id', $studentId)
            ->where('tahfidz_setoran.program_id', $programId)
            ->orderBy('tahfidz_setoran.setoran_date', 'DESC')
            ->orderBy('tahfidz_setoran.id', 'DESC');

        if ($limitDate) {
            $builder->where('tahfidz_setoran.setoran_date >=', $limitDate);
        }

        return $builder->findAll();
    }

    /**
     * Rekap setoran per siswa dalam rentang tanggal untuk satu program.
     * Return: [student_id => [total_setoran, rata_nilai, last_date]]
     */
    public function getRekapByProgram(int $programId, string $startDate, string $endDate, ?int $classroomId = null): array
    {
        $db = db_connect();
        $sql = "
            SELECT
                ts.student_id,
                s.nis,
                s.full_name,
                COUNT(ts.id)       as total_setoran,
                ROUND(AVG(ts.nilai), 1) as rata_nilai,
                MAX(ts.setoran_date)   as last_setoran,
                SUM(COALESCE(ts.ayat_end - ts.ayat_start + 1, 0)) as total_ayat_disetorkan
            FROM tahfidz_setoran ts
            JOIN students s ON s.id = ts.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)
            WHERE ts.program_id = ?
              AND ts.setoran_date BETWEEN ? AND ?
              AND s.deleted_at IS NULL
        ";
        $params = [$programId, $startDate, $endDate];

        if ($classroomId) {
            $sql .= " AND sah.classroom_id = ?";
            $params[] = $classroomId;
        }

        $sql .= " GROUP BY ts.student_id ORDER BY s.full_name ASC";
        return $db->query($sql, $params)->getResultArray();
    }

    /**
     * Daftar siswa yang BELUM setor dalam X hari terakhir (untuk alert).
     */
    public function getSiswaBlumSetor(int $programId, int $days = 7, ?int $classroomId = null): array
    {
        $db        = db_connect();
        $sinceDate = date('Y-m-d', strtotime("-{$days} days"));
        $sql = "
            SELECT s.id, s.nis, s.full_name, s.gender,
                   MAX(ts.setoran_date) as last_setoran
            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 tahfidz_setoran ts
                   ON ts.student_id = tps.student_id
                   AND ts.program_id = tps.program_id
                   AND ts.setoran_date >= ?
            WHERE tps.program_id = ?
              AND s.deleted_at IS NULL
        ";
        $params = [$sinceDate, $programId];

        if ($classroomId) {
            $sql .= " AND sah.classroom_id = ?";
            $params[] = $classroomId;
        }

        $sql .= " GROUP BY s.id HAVING last_setoran IS NULL ORDER BY s.full_name ASC";
        return $db->query($sql, $params)->getResultArray();
    }

    /**
     * Setoran hari ini untuk satu program (untuk dashboard guru).
     */
     public function getTodayByProgram(int $programId): array
     {
         $today = date('Y-m-d');
         return $this
             ->select('tahfidz_setoran.*, s.full_name, s.nis, s.father_phone, s.mother_phone, qs.name_latin, qs.name_arabic')
             ->join('students s', 's.id = tahfidz_setoran.student_id')
             ->join('quran_surahs qs', 'qs.id = tahfidz_setoran.surah_id', 'left')
             ->where('tahfidz_setoran.program_id', $programId)
             ->where('tahfidz_setoran.setoran_date', $today)
             ->orderBy('tahfidz_setoran.id', 'DESC')
             ->findAll();
     }

    /**
     * Rata-rata nilai setoran siswa dalam satu program.
     */
    public function getAvgNilai(int $studentId, int $programId): ?float
    {
        $db  = db_connect();
        $row = $db->query("
            SELECT ROUND(AVG(nilai), 1) as avg_nilai
            FROM tahfidz_setoran
            WHERE student_id = ? AND program_id = ?
        ", [$studentId, $programId])->getRowArray();

        return $row ? (float) $row['avg_nilai'] : null;
    }

    /**
     * Ambil data untuk notifikasi WA (setoran terakhir per siswa).
     */
    public function getLastSetoranForWa(int $programId, ?int $studentId = null): array
    {
        $db  = db_connect();
        $sql = "
            SELECT ts.*, s.full_name, s.father_phone, s.mother_phone,
                   qs.name_latin, qs.name_arabic,
                   t.name as teacher_name
            FROM tahfidz_setoran ts
            JOIN students s ON s.id = ts.student_id
            LEFT JOIN quran_surahs qs ON qs.id = ts.surah_id
            LEFT JOIN teachers t ON t.id = ts.teacher_id
            WHERE ts.program_id = ?
        ";
        $params = [$programId];

        if ($studentId) {
            $sql .= " AND ts.student_id = ?";
            $params[] = $studentId;
        }

        $sql .= " ORDER BY ts.setoran_date DESC, ts.id DESC";
        return $db->query($sql, $params)->getResultArray();
    }
}
