<?php

namespace App\Models;

use App\Models\BaseModel;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class AttendanceTracking extends BaseModel
{
    protected $table = 'attendance_tracking';

    // ✅ CI: useTimestamps = true (created_at/updated_at)
    public $timestamps = true;

    protected $fillable = [
        'student_id',
        'date',
        'is_reported',
        'reason',
        'note',
        'is_notified',
        'notif_counter',
        'semester',
        'school_year',
        'created_at',
        'updated_at',
    ];

    protected $casts = [
        'student_id'     => 'integer',
        'date'           => 'datetime', // CI dateFormat = datetime
        'is_reported'    => 'integer',
        'is_notified'    => 'integer',
        'notif_counter'  => 'integer',
    ];

    /* ----------------------- Helpers ----------------------- */

    /** Normalize Y-m-d (or Y-m-d H:i:s) into a full 'Y-m-d H:i:s' at midnight if time missing. */
    private static function normalizeDateTime(string $date): string
    {
        if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $date)) {
            return $date;
        }
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            return $date . ' 00:00:00';
        }

        try {
            return Carbon::parse($date, 'UTC')->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return Carbon::now('UTC')->startOfDay()->format('Y-m-d H:i:s');
        }
    }

    /** Apply semester/schoolYear filters if provided. */
    private static function applyTermFilters($q, ?string $semester, ?string $schoolYear)
    {
        if ($semester !== null && $semester !== '') {
            $q->where('semester', $semester);
        }
        if ($schoolYear !== null && $schoolYear !== '') {
            $q->where('school_year', $schoolYear);
        }
        return $q;
    }

    /** Derive (semester, school_year) from a Y-m-d date (Fall=Aug–Dec; Spring=Jan–Jul). */
    private static function deriveTermFromDate(string $ymd): array
    {
        try {
            $d = Carbon::createFromFormat('Y-m-d', substr($ymd, 0, 10), 'UTC');
        } catch (\Throwable $e) {
            $d = Carbon::now('UTC');
        }

        $m = (int) $d->format('n');
        $y = (int) $d->format('Y');

        $semester = ($m >= 8 && $m <= 12) ? 'Fall' : 'Spring';
        $startY   = ($m >= 8) ? $y : $y - 1;
        $endY     = $startY + 1;

        return [$semester, sprintf('%d-%d', $startY, $endY)];
    }

    /** Day bounds for DATETIME searches (index-friendly). */
    private static function dayRange(string $ymd): array
    {
        $day = substr($ymd, 0, 10);
        $start = Carbon::parse($day, 'UTC')->startOfDay();
        $endEx = Carbon::parse($day, 'UTC')->addDay()->startOfDay(); // exclusive
        return [$start, $endEx];
    }

    /* ----------------------- CRUD-ish APIs ----------------------- */

    /**
     * Records a student absence (stores time at 00:00:00 if only a day is given).
     * NOTE: $semester/$schoolYear are optional; will be derived from $date if omitted.
     * Returns inserted id.
     */
    public static function recordAbsence(
        int $studentId,
        string $date,
        ?string $semester = null,
        ?string $schoolYear = null,
        bool $isExcused = false,
        ?string $reason = null
    ): int {
        $dt = static::normalizeDateTime($date);

        if ($semester === null || $schoolYear === null) {
            [$sem, $yr] = static::deriveTermFromDate($dt);
            $semester   = $semester   ?? $sem;
            $schoolYear = $schoolYear ?? $yr;
        }

        $row = static::create([
            'student_id'     => $studentId,
            'date'           => $dt,
            'is_reported'    => $isExcused ? 1 : 0,
            'reason'         => $reason,
            'is_notified'    => 0,
            'notif_counter'  => 0,
            'semester'       => $semester,
            'school_year'    => $schoolYear,
        ]);

        return (int) $row->id;
    }

    /**
     * All absences for a student; start/end accept Y-m-d or Y-m-d H:i:s.
     * For day-only ranges, we use DATETIME bounds (>= startOfDay, < nextDay) for performance.
     */
    public static function getStudentAbsences(
        int $studentId,
        ?string $startDate = null,
        ?string $endDate = null,
        ?string $semester = null,
        ?string $schoolYear = null
    ) {
        $q = static::query()->where('student_id', $studentId);

        if ($startDate) {
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) {
                [$start, ] = static::dayRange($startDate);
                $q->where('date', '>=', $start);
            } else {
                $q->where('date', '>=', static::normalizeDateTime($startDate));
            }
        }

        if ($endDate) {
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) {
                [, $endEx] = static::dayRange($endDate);
                $q->where('date', '<', $endEx);
            } else {
                $q->where('date', '<=', static::normalizeDateTime($endDate));
            }
        }

        static::applyTermFilters($q, $semester, $schoolYear);

        return $q->orderByDesc('date')->get();
    }

    /**
     * Unexcused + unnotified absences (optionally for a specific day).
     */
    public static function getUnnotifiedUnexcusedAbsences(
        ?string $date = null,
        ?string $semester = null,
        ?string $schoolYear = null
    ) {
        $q = static::query()
            ->where('is_reported', 0)
            ->where('is_notified', 0);

        if ($date) {
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                [$start, $endEx] = static::dayRange($date);
                $q->where('date', '>=', $start)->where('date', '<', $endEx);
            } else {
                $q->where('date', static::normalizeDateTime($date));
            }
        }

        static::applyTermFilters($q, $semester, $schoolYear);

        return $q->orderByDesc('date')->get();
    }

    /**
     * Has at least N absences in the last N days (rough heuristic).
     */
    public static function hasConsecutiveAbsences(
        int $studentId,
        int $days,
        ?string $semester = null,
        ?string $schoolYear = null
    ): bool {
        $startDay = Carbon::now('UTC')->subDays($days)->toDateString();
        [$start, ] = static::dayRange($startDay);

        $q = static::query()
            ->where('student_id', $studentId)
            ->where('date', '>=', $start);

        static::applyTermFilters($q, $semester, $schoolYear);

        return $q->count() >= $days;
    }

    /**
     * TRUE if last $threshold absences are exactly weekly (7-day spacing).
     */
    public static function checkConsecutiveAbsences(
        int $studentId,
        int $threshold,
        ?string $semester = null,
        ?string $schoolYear = null
    ): bool {
        $q = static::query()
            ->select(['date', 'is_reported', 'is_notified'])
            ->where('student_id', $studentId)
            ->where('is_reported', 0)
            ->where('is_notified', 0);

        static::applyTermFilters($q, $semester, $schoolYear);

        $absences = $q->orderByDesc('date')->limit($threshold)->get()->toArray();

        if (count($absences) < $threshold) return false;

        for ($i = 0; $i < $threshold - 1; $i++) {
            $d1 = new \DateTime($absences[$i]['date']);
            $d2 = new \DateTime($absences[$i + 1]['date']);
            $diffDays = (int) $d2->diff($d1)->format('%a');
            if ($diffDays !== 7) return false;
        }

        return true;
    }

    /**
     * TRUE if at least $absencesThreshold absences in the last $weeksStreak weeks.
     * (Mirrors your CI behavior: count of last N fetched records)
     */
    public static function checkAbsencesInStreak(
        int $studentId,
        int $absencesThreshold,
        int $weeksStreak,
        ?string $semester = null,
        ?string $schoolYear = null
    ): bool {
        $q = static::query()
            ->select(['date', 'is_reported', 'is_notified'])
            ->where('student_id', $studentId)
            ->where('is_reported', 0)
            ->where('is_notified', 0);

        static::applyTermFilters($q, $semester, $schoolYear);

        $records = $q->orderByDesc('date')->limit($weeksStreak)->get()->toArray();

        if (count($records) < $weeksStreak) return false;

        $count = 0;
        foreach ($records as $r) {
            if ((int)($r['is_reported'] ?? 0) === 0 && (int)($r['is_notified'] ?? 0) === 0) {
                $count++;
            }
        }

        return $count >= $absencesThreshold;
    }

    /**
     * Summarize a student's status this term.
     */
    public static function getStudentAttendanceStats(
        int $studentId,
        ?string $semester = null,
        ?string $schoolYear = null
    ): array {
        $q1 = static::query()->where('student_id', $studentId);
        static::applyTermFilters($q1, $semester, $schoolYear);
        $total = (int) $q1->count();

        $q2 = static::query()->where('student_id', $studentId)->where('is_reported', 0);
        static::applyTermFilters($q2, $semester, $schoolYear);
        $unexcused = (int) $q2->count();

        $q3 = static::query()->where('student_id', $studentId);
        static::applyTermFilters($q3, $semester, $schoolYear);
        $last = $q3->orderByDesc('date')->first();

        return [
            'total_absences'     => $total,
            'unexcused_absences' => $unexcused,
            'last_absence'       => $last ? $last->toArray() : null,
        ];
    }

    /**
     * Fetch records by semester/schoolYear (with optional limit).
     */
    public static function getAttendanceBySemester(
        ?string $semester = null,
        ?string $schoolYear = null,
        int $limit = 0
    ) {
        $q = static::query();
        static::applyTermFilters($q, $semester, $schoolYear);
        if ($limit > 0) $q->limit($limit);
        return $q->orderByDesc('date')->get();
    }

    /**
     * Summary counts for a term.
     */
    public static function getSemesterSummary(?string $semester = null, ?string $schoolYear = null): array
    {
        $q1 = static::query();
        static::applyTermFilters($q1, $semester, $schoolYear);
        $total = (int) $q1->count();

        $q2 = static::query()->where('is_reported', 0);
        static::applyTermFilters($q2, $semester, $schoolYear);
        $unexcused = (int) $q2->count();

        $q3 = static::query();
        static::applyTermFilters($q3, $semester, $schoolYear);
        $students = (int) $q3->distinct('student_id')->count('student_id');

        return [
            'total_absences'         => $total,
            'unexcused_absences'     => $unexcused,
            'students_with_absences' => $students,
        ];
    }

    /**
     * Update/create violation rows (matching by student + day-range(date) + term).
     * Expects input rows like: ['id'=>..., 'violation'=>..., 'absences'=>[['date'=>'Y-m-d'], ...]]
     */
    public static function updateAttendanceViolations(array $studentsWithViolations, string $semester, string $schoolYear): array
    {
        $results = ['added' => 0, 'updated' => 0, 'skipped' => 0, 'notified' => 0];

        foreach ($studentsWithViolations as $student) {
            if (empty($student['absences'])) {
                $results['skipped']++;
                continue;
            }

            foreach ($student['absences'] as $absence) {
                $ymd = substr((string)($absence['date'] ?? now()->toDateString()), 0, 10);
                [$start, $endEx] = static::dayRange($ymd);

                $data = [
                    'student_id'     => (int)($student['id'] ?? 0),
                    'date'           => static::normalizeDateTime($ymd),
                    'is_reported'    => 0,
                    'reason'         => (string)($student['violation'] ?? ''),
                    'is_notified'    => 0,
                    'notif_counter'  => 0,
                    'semester'       => $semester,
                    'school_year'    => $schoolYear,
                ];

                if ($data['student_id'] <= 0) {
                    $results['skipped']++;
                    continue;
                }

                try {
                    // Match by student + day range + term
                    $existing = static::query()
                        ->where('student_id', $data['student_id'])
                        ->where('semester', $semester)
                        ->where('school_year', $schoolYear)
                        ->where('date', '>=', $start)
                        ->where('date', '<', $endEx)
                        ->orderByDesc('date')
                        ->first();

                    if ($existing) {
                        if (($existing->reason ?? '') !== $data['reason']) {
                            $existing->fill($data)->save();
                            $results['updated']++;

                            if (!empty($existing->is_notified)) {
                                static::resetNotification((int)$existing->id);
                                $results['notified']++;
                            }
                        } else {
                            $results['skipped']++;
                        }
                    } else {
                        $new = static::create($data);
                        $results['added']++;

                        // Only match standalone "4"
                        if (preg_match('/\b4\b/', $data['reason'])) {
                            static::markAsNotified((int)$new->id);
                            $results['notified']++;
                        }
                    }
                } catch (\Throwable $e) {
                    // keep behavior: skip on error
                    $results['skipped']++;
                }
            }
        }

        return $results;
    }

    /* ----------------------- Notification ops ----------------------- */

    public static function markAsNotified(int $id): bool
    {
        // atomic increment + set flag
        $affected = static::query()
            ->whereKey($id)
            ->update([
                'notif_counter' => DB::raw('COALESCE(notif_counter,0)+1'),
                'is_notified'   => 1,
                'updated_at'    => now(),
            ]);

        return $affected > 0;
    }

    public static function resetNotification(int $id): bool
    {
        $affected = static::query()
            ->whereKey($id)
            ->update([
                'is_notified'   => 0,
                'notif_counter' => 0,
                'updated_at'    => now(),
            ]);

        return $affected >= 0;
    }

    /**
     * Mark rule as notified by matching latest row for that day + term.
     * $code is the rule code stored in "reason" (ABS_2, etc).
     * If no row exists, insert one.
     */
    public static function markRuleAsNotified(
        int $studentId,
        string $code,
        string $ymd,
        ?string $semester = null,
        ?string $schoolYear = null
    ): bool {
        // If you want to keep the old dependency on ConfigurationModel, you can inject it elsewhere.
        // Here we require term to be passed or derived from date.
        if ($semester === null || $schoolYear === null) {
            [$sem, $yr] = static::deriveTermFromDate($ymd);
            $semester   = $semester   ?? $sem;
            $schoolYear = $schoolYear ?? $yr;
        }

        [$start, $endEx] = static::dayRange($ymd);

        // 1) Match by code + day (preferred)
        $row = static::query()
            ->where('student_id', $studentId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->where('date', '>=', $start)
            ->where('date', '<',  $endEx)
            ->where('reason', 'like', "%{$code}%")
            ->orderByDesc('date')
            ->first();

        // 2) Fallback: match by day only
        if (!$row) {
            $row = static::query()
                ->where('student_id', $studentId)
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->where('date', '>=', $start)
                ->where('date', '<',  $endEx)
                ->orderByDesc('date')
                ->first();
        }

        if ($row) {
            $counter = ((int)($row->notif_counter ?? 0)) + 1;

            $affected = static::query()
                ->whereKey((int)$row->id)
                ->update([
                    'is_notified'   => 1,
                    'notif_counter' => $counter,
                    'is_reported'   => 0,
                    'updated_at'    => now(),
                ]);

            return $affected > 0;
        }

        // Insert a row so future runs see it as notified
        $created = static::create([
            'student_id'     => $studentId,
            'date'           => static::normalizeDateTime($ymd),
            'is_reported'    => 0,
            'reason'         => $code,
            'is_notified'    => 1,
            'notif_counter'  => 1,
            'semester'       => $semester,
            'school_year'    => $schoolYear,
            'note'           => null,
        ]);

        return !empty($created->id);
    }

    /**
     * Mark the latest matching tracking row as notified and increment counter.
     * Optionally bound by $lastDate (DATETIME string).
     */
    public static function markNotified(
        int $studentId,
        string $schoolYear,
        ?string $semester,
        string $code,
        ?string $lastDate = null
    ): int {
        $q = static::query()
            ->where('student_id', $studentId)
            ->where('school_year', $schoolYear);

        if (!empty($semester)) {
            $q->where('semester', $semester);
        }

        if (!empty($lastDate)) {
            $q->where('date', '<=', static::normalizeDateTime($lastDate));
        }

        $row = $q->where('reason', 'like', "%{$code}%")
            ->orderByDesc('date')
            ->first();

        if (!$row) return 0;

        $counter = ((int)($row->notif_counter ?? 0)) + 1;

        $affected = static::query()
            ->whereKey((int)$row->id)
            ->update([
                'is_notified'   => 1,
                'notif_counter' => $counter,
                'updated_at'    => now(),
            ]);

        return $affected > 0 ? 1 : 0;
    }
}