<?php

namespace App\Models;

use CodeIgniter\Model;

class QrScanLogModel extends Model
{
    protected $table         = 'qr_scan_logs';
    protected $primaryKey    = 'id';
    protected $allowedFields = ['student_id', 'date', 'scan_time', 'source_ip'];
    protected $useTimestamps = true;
    protected $updatedField  = '';

    /** Cek apakah siswa sudah scan hari ini */
    public function alreadyScanned(int $studentId, string $date): bool
    {
        return $this->where('student_id', $studentId)->where('date', $date)->countAllResults() > 0;
    }

    /** Simpan log scan */
    public function logScan(int $studentId, string $date, string $scanTime, ?string $ip = null): bool
    {
        return (bool) $this->insert([
            'student_id' => $studentId,
            'date'       => $date,
            'scan_time'  => $scanTime,
            'source_ip'  => $ip,
        ]);
    }

    /** Rekap hari ini: siapa saja yang sudah scan */
    public function getTodayScans(string $date): array
    {
        return $this->db->table('qr_scan_logs q')
            ->select('q.scan_time, s.nisn, s.full_name, sah.classroom_id, c.name as classroom_name, g.name as grade_name')
            ->join('students s', 's.id = q.student_id')
            ->join('student_academic_histories sah', 'sah.student_id = q.student_id AND sah.academic_period_id = (SELECT id FROM academic_periods WHERE is_active = 1 LIMIT 1)', 'left')
            ->join('classrooms c', 'c.id = sah.classroom_id', 'left')
            ->join('grades g', 'g.id = c.grade_id', 'left')
            ->where('q.date', $date)
            ->orderBy('q.scan_time', 'ASC')
            ->get()->getResultArray();
    }

    /**
     * Rekap lengkap harian: SEMUA siswa aktif + status kehadiran mereka.
     * 
     * Status per siswa:
     *   - 'hadir'      → ada record di qr_scan_logs
     *   - 'Sakit'      → ada record di attendances dengan status Sakit
     *   - 'Izin'       → ada record di attendances dengan status Izin
     *   - 'Alpha'      → ada record di attendances dengan status Alpha
     *   - 'belum'      → tidak ada record di mana-mana (belum diketahui)
     *
     * @param string      $date        Tanggal (Y-m-d)
     * @param int|null    $classroomId Filter per kelas (opsional)
     */
    public function getFullDailyReport(string $date, ?int $classroomId = null): array
    {
        $subPeriod = '(SELECT id FROM academic_periods WHERE is_active = 1 LIMIT 1)';

        $builder = $this->db->table('students s')
            ->select("
                s.id as student_id,
                s.nis,
                s.nisn,
                s.full_name,
                s.gender,
                c.id   as classroom_id,
                c.name as classroom_name,
                g.name as grade_name,
                q.scan_time,
                a.status as att_status,
                CASE
                    WHEN q.id   IS NOT NULL THEN 'hadir'
                    WHEN a.id   IS NOT NULL THEN a.status
                    ELSE 'belum'
                END as kehadiran
            ")
            ->join(
                "student_academic_histories sah",
                "sah.student_id = s.id AND sah.academic_period_id = {$subPeriod}",
                'inner'
            )
            ->join('classrooms c', 'c.id = sah.classroom_id', 'left')
            ->join('grades g',     'g.id = c.grade_id',       'left')
            ->join(
                "qr_scan_logs q",
                "q.student_id = s.id AND q.date = '{$date}'",
                'left'
            )
            ->join(
                "attendances a",
                "a.student_id = s.id AND a.date = '{$date}' AND a.academic_period_id = {$subPeriod}",
                'left'
            )
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif');

        if ($classroomId) {
            $builder->where('sah.classroom_id', $classroomId);
        }

        return $builder
            ->orderBy('g.order', 'ASC')
            ->orderBy('c.name', 'ASC')
            ->orderBy('s.full_name', 'ASC')
            ->get()->getResultArray();
    }

    /** Jumlah scan hari ini */
    public function countToday(string $date): int
    {
        return $this->where('date', $date)->countAllResults();
    }

    /** Rekap bulanan QR */
    public function getQrMonthlyRecap(int $classroomId, int $periodId, int $month, int $year): array
    {
        $startDate = "$year-$month-01";
        $endDate = date('Y-m-t', strtotime($startDate));
        
        $sql = "
            SELECT 
                s.id as student_id,
                s.nis,
                s.full_name,
                COALESCE(att_sakit.cnt, 0) as sakit,
                COALESCE(att_izin.cnt, 0) as izin,
                COALESCE(qr_present.cnt, 0) as hadir
            FROM students s
            INNER JOIN student_academic_histories sah ON sah.student_id = s.id AND sah.academic_period_id = ?
            LEFT JOIN (
                SELECT student_id, COUNT(*) as cnt 
                FROM attendances 
                WHERE status = 'Sakit' 
                  AND date >= ? 
                  AND date <= ? 
                  AND academic_period_id = ?
                GROUP BY student_id
            ) att_sakit ON att_sakit.student_id = s.id
            LEFT JOIN (
                SELECT student_id, COUNT(*) as cnt 
                FROM attendances 
                WHERE status = 'Izin' 
                  AND date >= ? 
                  AND date <= ? 
                  AND academic_period_id = ?
                GROUP BY student_id
            ) att_izin ON att_izin.student_id = s.id
            LEFT JOIN (
                SELECT q.student_id, COUNT(*) as cnt
                FROM qr_scan_logs q
                LEFT JOIN attendances a ON a.student_id = q.student_id AND a.date = q.date AND a.academic_period_id = ?
                WHERE q.date >= ?
                  AND q.date <= ?
                  AND (a.status IS NULL OR a.status NOT IN ('Sakit', 'Izin'))
                GROUP BY q.student_id
            ) qr_present ON qr_present.student_id = s.id
            WHERE sah.classroom_id = ?
              AND s.deleted_at IS NULL
              AND s.status = 'Aktif'
            ORDER BY s.full_name ASC
        ";
        
        return $this->db->query($sql, [
            $periodId,
            $startDate, $endDate, $periodId,
            $startDate, $endDate, $periodId,
            $periodId, $startDate, $endDate,
            $classroomId
        ])->getResultArray();
    }

    /** Rekap semester QR */
    public function getQrSemesterRecap(int $classroomId, int $periodId): array
    {
        $period = $this->db->table('academic_periods')
            ->select('start_date, end_date')
            ->where('id', $periodId)
            ->get()->getRowArray();
        
        if (!$period) return [];
        
        $startDate = $period['start_date'];
        $endDate = $period['end_date'];
        
        $sql = "
            SELECT 
                s.id as student_id,
                s.nis,
                s.full_name,
                COALESCE(att_sakit.cnt, 0) as sakit,
                COALESCE(att_izin.cnt, 0) as izin,
                COALESCE(qr_present.cnt, 0) as hadir
            FROM students s
            INNER JOIN student_academic_histories sah ON sah.student_id = s.id AND sah.academic_period_id = ?
            LEFT JOIN (
                SELECT student_id, COUNT(*) as cnt 
                FROM attendances 
                WHERE status = 'Sakit' 
                  AND date >= ? 
                  AND date <= ? 
                  AND academic_period_id = ?
                GROUP BY student_id
            ) att_sakit ON att_sakit.student_id = s.id
            LEFT JOIN (
                SELECT student_id, COUNT(*) as cnt 
                FROM attendances 
                WHERE status = 'Izin' 
                  AND date >= ? 
                  AND date <= ? 
                  AND academic_period_id = ?
                GROUP BY student_id
            ) att_izin ON att_izin.student_id = s.id
            LEFT JOIN (
                SELECT q.student_id, COUNT(*) as cnt
                FROM qr_scan_logs q
                LEFT JOIN attendances a ON a.student_id = q.student_id AND a.date = q.date AND a.academic_period_id = ?
                WHERE q.date >= ?
                  AND q.date <= ?
                  AND (a.status IS NULL OR a.status NOT IN ('Sakit', 'Izin'))
                GROUP BY q.student_id
            ) qr_present ON qr_present.student_id = s.id
            WHERE sah.classroom_id = ?
              AND s.deleted_at IS NULL
              AND s.status = 'Aktif'
            ORDER BY s.full_name ASC
        ";
        
        return $this->db->query($sql, [
            $periodId,
            $startDate, $endDate, $periodId,
            $startDate, $endDate, $periodId,
            $periodId, $startDate, $endDate,
            $classroomId
        ])->getResultArray();
    }
}
