<?php

namespace App\Http\Controllers\School;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Inertia\Response;

class AbsenceReportController extends Controller
{
    public function index(Request $request): Response
    {
        $schoolId = $this->currentSchoolId($request);
        $threshold = max(1, (int) $request->query('min_absences', 3));
        $classroomId = $request->query('classroom_id');

        $query = DB::table('attendance_records as ar')
            ->join('students', 'students.id', '=', 'ar.student_id')
            ->leftJoin('class_students as cs', function ($join): void {
                $join->on('cs.student_id', '=', 'students.id')
                    ->where('cs.status', 'active');
            })
            ->leftJoin('classrooms', 'classrooms.id', '=', 'cs.classroom_id')
            ->where('ar.school_id', $schoolId)
            ->select([
                'students.id',
                'students.first_name',
                'students.last_name',
                'students.student_code',
                'classrooms.id as classroom_id',
                'classrooms.name as classroom_name',
                DB::raw('COUNT(ar.id) as total_records'),
                DB::raw("SUM(CASE WHEN ar.status = 'absent' THEN 1 ELSE 0 END) as absence_count"),
                DB::raw("SUM(CASE WHEN ar.status = 'excused' THEN 1 ELSE 0 END) as excused_count"),
                DB::raw("SUM(CASE WHEN ar.status = 'present' THEN 1 ELSE 0 END) as present_count"),
                DB::raw("SUM(CASE WHEN ar.status = 'late' THEN 1 ELSE 0 END) as late_count"),
            ])
            ->groupBy('students.id', 'students.first_name', 'students.last_name', 'students.student_code', 'classrooms.id', 'classrooms.name')
            ->having('absence_count', '>=', $threshold)
            ->orderByDesc('absence_count')
            ->limit(200);

        if ($classroomId) {
            $query->where('cs.classroom_id', (int) $classroomId);
        }

        $students = $query->get()->map(function ($row): array {
            $total = (int) $row->total_records;
            $absences = (int) $row->absence_count;
            $excused = (int) $row->excused_count;
            $present = (int) $row->present_count;
            $late = (int) $row->late_count;
            $attendancePct = $total > 0 ? round(($present + $late) / $total * 100, 1) : 0;

            return [
                'id' => (int) $row->id,
                'name' => trim($row->first_name . ' ' . $row->last_name),
                'studentCode' => $row->student_code,
                'classroomName' => $row->classroom_name ?: '—',
                'totalSessions' => $total,
                'absenceCount' => $absences,
                'excusedCount' => $excused,
                'presentCount' => $present,
                'lateCount' => $late,
                'attendancePct' => $attendancePct,
            ];
        })->values();

        $classrooms = $schoolId
            ? DB::table('classrooms')
                ->where('school_id', $schoolId)
                ->orderBy('name')
                ->get(['id', 'name'])
                ->map(fn ($c): array => ['id' => (int) $c->id, 'name' => $c->name])
                ->values()
            : collect();

        return Inertia::render('School/AbsenceReport', [
            'students' => $students,
            'classrooms' => $classrooms,
            'filters' => [
                'min_absences' => $threshold,
                'classroom_id' => $classroomId ? (int) $classroomId : null,
            ],
        ]);
    }
}
