<?php

namespace App\Services\Attendance;

use App\Controllers\View\AttendanceController;
use App\Models\AttendanceDay;
use App\Models\ClassSection;
use App\Models\Configuration;
use App\Models\StaffAttendance;
use App\Models\TeacherClass;
use Carbon\Carbon;
use Illuminate\Contracts\Auth\Authenticatable;
use Illuminate\Support\Facades\DB;
use Symfony\Component\HttpFoundation\StreamedResponse;

class StaffAttendanceService
{
    public function __construct(
        protected Configuration $configuration,
        protected StaffAttendance $staffAttendance,
        protected TeacherClass $teacherClass,
        protected ClassSection $classSection,
        protected SemesterRangeService $semesterRangeService,
    ) {}

    /**
     * legacy {@see AttendanceController::index} — single date / section teacher grid JSON.
     */
    public function teacherAttendanceDayIndex(?string $semester, ?string $schoolYear, string $dateYmd, int $sectionCode): array
    {
        $semester = $semester ?: (string) $this->configuration->getConfig('semester');
        $schoolYear = $schoolYear ?: (string) $this->configuration->getConfig('school_year');

        $assignedBySection = TeacherClass::assignedBySectionForTerm($semester, $schoolYear);
        $sectionCodes = array_keys($assignedBySection);

        $labels = [];
        foreach ($sectionCodes as $code) {
            $code = (int) $code;
            if ($code <= 0) {
                continue;
            }

            $name = $this->classSection->query()
                ->where('class_section_id', $code)
                ->value('class_section_name');
            $name = trim((string) $name);
            $labels[$code] = $name !== '' ? $name : ('Section #'.$code);
        }

        $grid = [];
        $locked = false;

        if ($sectionCode > 0) {
            $assigned = TeacherClass::assignedForSectionTerm($sectionCode, $semester, $schoolYear);
            $teacherIds = array_values(array_filter(array_map(static fn ($r) => (int) ($r['teacher_id'] ?? 0), $assigned)));

            $statusMap = [];
            if ($teacherIds !== []) {
                $rows = DB::table('staff_attendance as sa')
                    ->select(['sa.user_id', 'sa.status', 'sa.reason'])
                    ->where('sa.semester', $semester)
                    ->where('sa.school_year', $schoolYear)
                    ->whereDate('sa.date', $dateYmd)
                    ->whereIn('sa.user_id', $teacherIds)
                    ->get();

                foreach ($rows as $r) {
                    $statusMap[(int) $r->user_id] = [
                        'status' => $r->status ?? null,
                        'reason' => $r->reason ?? null,
                    ];
                }
            }

            foreach ($assigned as $t) {
                $tid = (int) ($t['teacher_id'] ?? 0);
                $posRaw = strtolower((string) ($t['position'] ?? 'main'));
                $pos = in_array($posRaw, ['main', 'ta'], true) ? $posRaw : 'main';
                $name = trim(($t['firstname'] ?? '').' '.($t['lastname'] ?? '')) ?: ('User#'.$tid);

                $cell = $statusMap[$tid] ?? ['status' => null, 'reason' => null];

                $grid[] = [
                    'teacher_id' => $tid,
                    'name' => $name,
                    'position' => $pos,
                    'status' => $cell['status'],
                    'reason' => $cell['reason'],
                ];
            }

            try {
                $locked = AttendanceDay::isFinalized($sectionCode, $dateYmd, $semester, $schoolYear);
            } catch (\Throwable) {
                $locked = false;
            }
        }

        return [
            'semester' => $semester,
            'school_year' => $schoolYear,
            'date' => $dateYmd,
            'class_section_id' => $sectionCode,
            'sections' => $labels,
            'grid' => $grid,
            'locked' => $locked,
        ];
    }

    /**
     * Bulk save for legacy {@see AttendanceController::save} (method missing in legacy; implemented here).
     */
    public function saveTeacherAttendanceBulk(Authenticatable $user, array $payload): array
    {
        $sectionCode = (int) ($payload['class_section_id'] ?? 0);
        $date = substr((string) ($payload['date'] ?? ''), 0, 10);
        $semester = (string) ($payload['semester'] ?? '');
        $schoolYear = (string) ($payload['school_year'] ?? '');
        $teachers = (array) ($payload['teachers'] ?? []);

        if ($sectionCode <= 0 || $date === '' || $semester === '' || $schoolYear === '') {
            throw new \RuntimeException('Missing required fields.');
        }

        $assigned = TeacherClass::assignedForSectionTerm($sectionCode, $semester, $schoolYear);
        $assignedByTeacher = [];
        foreach ($assigned as $row) {
            $assignedByTeacher[(int) ($row['teacher_id'] ?? 0)] = $row;
        }

        DB::transaction(function () use ($teachers, $assignedByTeacher, $date, $semester, $schoolYear) {
            foreach ($teachers as $tidKey => $row) {
                $tid = (int) (is_numeric((string) $tidKey) ? $tidKey : ($row['teacher_id'] ?? 0));
                if ($tid <= 0) {
                    continue;
                }

                $status = strtolower(trim((string) ($row['status'] ?? '')));
                $reason = trim((string) ($row['reason'] ?? ''));

                $assignment = $assignedByTeacher[$tid] ?? null;
                $roleLabel = $assignment
                    ? ((strtolower((string) ($assignment['position'] ?? '')) === 'ta') ? 'Teacher Assistant' : 'Teacher')
                    : 'Teacher';

                if ($status === '') {
                    DB::table('staff_attendance')
                        ->where('user_id', $tid)
                        ->whereDate('date', $date)
                        ->where('semester', $semester)
                        ->where('school_year', $schoolYear)
                        ->delete();

                    continue;
                }

                if (! in_array($status, ['present', 'absent', 'late'], true)) {
                    continue;
                }

                DB::table('staff_attendance')->updateOrInsert(
                    [
                        'user_id' => $tid,
                        'date' => $date,
                        'semester' => $semester,
                        'school_year' => $schoolYear,
                    ],
                    [
                        'role_name' => $roleLabel,
                        'status' => $status,
                        'reason' => $reason !== '' ? $reason : null,
                        'updated_at' => now(),
                        'created_at' => now(),
                    ]
                );
            }
        });

        return ['ok' => true, 'message' => 'Teacher attendance saved successfully.'];
    }

    public function monthData(?string $semester, ?string $schoolYear): array
    {
        $semester = $semester ?: (string) $this->configuration->getConfig('semester');
        [
            'school_year' => $schoolYear,
            'current_year' => $currentYear,
            'school_years' => $schoolYears,
            'missing_year' => $missingYear,
            'is_current_year' => $isCurrentYear,
        ] = $this->resolveMonthSchoolYearContext($schoolYear);

        if ($schoolYear === '') {
            return [
                'filters' => [
                    'semester' => $semester,
                    'school_year' => '',
                    'range_start' => null,
                    'range_end' => null,
                    'range_label' => 'School Year',
                    'month_label' => 'School Year',
                ],
                'sundays' => [],
                'noSchoolDays' => [],
                'sections' => [],
                'admins' => [],
                'schoolYears' => $schoolYears,
                'currentYear' => $currentYear,
                'isCurrentYear' => false,
                'missingYear' => $missingYear,
            ];
        }

        [$rangeStart, $rangeEnd] = $this->semesterRangeService->getSchoolYearRange($schoolYear);
        $semesterNorm = $this->semesterRangeService->normalizeSemester($semester);

        if ($semesterNorm !== '') {
            $semesterRange = $this->semesterRangeService->getSemesterRange($schoolYear, $semesterNorm);
            if ($semesterRange) {
                [$rangeStart, $rangeEnd] = $semesterRange;
            }
        }

        $rangeLabel = $this->buildRangeLabel($schoolYear, $semesterNorm, $rangeStart, $rangeEnd);

        $days = $this->semesterRangeService->buildSundayList($rangeStart, $rangeEnd);

        $assignedBySection = $semesterNorm !== ''
            ? $this->teacherClass->assignedBySectionForTerm($semesterNorm, $schoolYear)
            : $this->teacherClass->assignedBySectionForTerm($semester, $schoolYear);

        $sectionCodes = array_keys($assignedBySection);
        $labels = [];

        if (! empty($sectionCodes)) {
            $rows = $this->classSection->query()
                ->select(['class_section_id', 'class_section_name'])
                ->whereIn('class_section_id', $sectionCodes)
                ->get()
                ->toArray();

            foreach ($rows as $row) {
                $labels[(int) $row['class_section_id']] = trim((string) $row['class_section_name']) ?: 'Section #'.(int) $row['class_section_id'];
            }
        }

        $teacherIds = [];
        foreach ($assignedBySection as $rows) {
            foreach ($rows as $row) {
                $teacherIds[(int) $row['teacher_id']] = true;
            }
        }
        $teacherIds = array_keys($teacherIds);

        $statusByTeacher = [];
        if (! empty($teacherIds) && ! empty($days)) {
            $query = DB::table('staff_attendance')
                ->select('user_id', 'date', 'status', 'reason')
                ->where('school_year', $schoolYear)
                ->whereIn('user_id', $teacherIds)
                ->whereIn('date', $days);

            if ($semesterNorm !== '') {
                $query->where('semester', $semesterNorm);
            }

            $rows = $query->get();

            foreach ($rows as $row) {
                $statusByTeacher[(int) $row->user_id][(string) $row->date] = [
                    'status' => strtolower((string) $row->status),
                    'reason' => $row->reason,
                ];
            }
        }

        $noSchoolRows = DB::table('calendar_events')
            ->select('date')
            ->where('school_year', $schoolYear)
            ->where('no_school', 1)
            ->where('date', '>=', $rangeStart)
            ->where('date', '<=', $rangeEnd)
            ->get();

        $noSchoolDays = [];
        foreach ($noSchoolRows as $row) {
            $noSchoolDays[] = (string) $row->date;
        }

        $sections = [];
        foreach ($sectionCodes as $code) {
            $teachersPayload = [];

            foreach (($assignedBySection[$code] ?? []) as $row) {
                $teacherId = (int) $row['teacher_id'];
                $name = trim(($row['firstname'] ?? '').' '.($row['lastname'] ?? '')) ?: ('User#'.$teacherId);
                $position = strtolower((string) ($row['position'] ?? 'main'));
                $position = in_array($position, ['main', 'ta'], true) ? $position : 'main';

                $cells = [];
                $totals = ['p' => 0, 'a' => 0, 'l' => 0];

                foreach ($days as $date) {
                    $cell = $statusByTeacher[$teacherId][$date] ?? null;
                    if (! $cell || empty($cell['status'])) {
                        continue;
                    }

                    $cells[$date] = $cell;

                    if ($cell['status'] === 'present') {
                        $totals['p']++;
                    } elseif ($cell['status'] === 'absent') {
                        $totals['a']++;
                    } elseif ($cell['status'] === 'late') {
                        $totals['l']++;
                    }
                }

                $teachersPayload[] = [
                    'teacher_id' => $teacherId,
                    'name' => $name,
                    'position' => $position,
                    'cells' => $cells,
                    'totals' => $totals,
                ];
            }

            $sections[] = [
                'section_code' => (int) $code,
                'label' => $labels[$code] ?? ('Section #'.(int) $code),
                'teachers' => $teachersPayload,
            ];
        }

        $adminStatusByUser = [];
        $adminIds = [];
        if (! empty($days)) {
            $query = DB::table('staff_attendance')
                ->select('user_id', 'date', 'status', 'reason')
                ->where('school_year', $schoolYear)
                ->whereIn('date', $days);

            if ($semesterNorm !== '') {
                $query->where('semester', $semesterNorm);
            }

            $rows = $query->get();
            foreach ($rows as $row) {
                $uid = (int) ($row->user_id ?? 0);
                if ($uid <= 0) {
                    continue;
                }
                $adminIds[$uid] = true;
                $adminStatusByUser[$uid][(string) $row->date] = [
                    'status' => strtolower((string) $row->status),
                    'reason' => $row->reason,
                ];
            }
        }

        $adminIds = array_keys($adminIds);
        $admins = [];
        if (! empty($adminIds)) {
            $rolesRows = DB::table('user_roles as ur')
                ->select('ur.user_id', 'r.name as role_name', 'r.slug as role_slug', 'r.priority')
                ->join('roles as r', 'r.id', '=', 'ur.role_id')
                ->whereIn('ur.user_id', $adminIds)
                ->whereNull('ur.deleted_at')
                ->where('r.is_active', 1)
                ->get();

            $rolesByUser = [];
            foreach ($rolesRows as $row) {
                $rolesByUser[(int) $row->user_id][] = (array) $row;
            }

            foreach ($rolesByUser as &$list) {
                usort($list, fn ($a, $b) => ((int) ($a['priority'] ?? 100)) <=> ((int) ($b['priority'] ?? 100)));
            }
            unset($list);

            $excludeSlugs = ['parent', 'guest', 'teacher', 'teacher_assistant', 'ta', 'assistant_teacher'];

            $userRows = DB::table('users')
                ->select('id', 'firstname', 'lastname')
                ->whereIn('id', $adminIds)
                ->get();

            $namesByUser = [];
            foreach ($userRows as $row) {
                $namesByUser[(int) $row->id] = trim(((string) ($row->firstname ?? '')).' '.((string) ($row->lastname ?? '')));
            }

            foreach ($adminIds as $uid) {
                $picked = null;
                foreach (($rolesByUser[$uid] ?? []) as $role) {
                    $slug = strtolower((string) ($role['role_slug'] ?? $role['role_name'] ?? ''));
                    if (! in_array($slug, $excludeSlugs, true)) {
                        $picked = $role;
                        break;
                    }
                }

                if (! $picked) {
                    continue;
                }

                $totals = ['p' => 0, 'a' => 0, 'l' => 0];
                $cells = [];
                foreach ($days as $date) {
                    $cell = $adminStatusByUser[$uid][$date] ?? null;
                    if (! $cell || empty($cell['status'])) {
                        continue;
                    }
                    $cells[$date] = $cell;
                    if ($cell['status'] === 'present') {
                        $totals['p']++;
                    } elseif ($cell['status'] === 'absent') {
                        $totals['a']++;
                    } elseif ($cell['status'] === 'late') {
                        $totals['l']++;
                    }
                }

                $admins[] = [
                    'user_id' => $uid,
                    'name' => $namesByUser[$uid] !== '' ? $namesByUser[$uid] : ('User#'.$uid),
                    'role' => (string) ($picked['role_name'] ?? 'Admin'),
                    'cells' => $cells,
                    'totals' => $totals,
                ];
            }

            usort($admins, fn ($a, $b) => strcasecmp((string) ($a['name'] ?? ''), (string) ($b['name'] ?? '')));
        }

        return [
            'filters' => [
                'semester' => $semester,
                'school_year' => $schoolYear,
                'range_start' => $rangeStart,
                'range_end' => $rangeEnd,
                'range_label' => $rangeLabel,
                'month_label' => $rangeLabel,
            ],
            'sundays' => $days,
            'noSchoolDays' => $noSchoolDays,
            'sections' => $sections,
            'admins' => $admins,
            'schoolYears' => $schoolYears,
            'currentYear' => $currentYear,
            'isCurrentYear' => $isCurrentYear,
            'missingYear' => $missingYear,
        ];
    }

    /**
     * Match the legacy month page behavior:
     * - prefer the requested year
     * - else use configured school year
     * - else fall back to the latest year present in staff_attendance / teacher_class
     *
     * @return array{
     *   school_year:string,
     *   current_year:string,
     *   school_years:array<int,string>,
     *   missing_year:bool,
     *   is_current_year:bool
     * }
     */
    protected function resolveMonthSchoolYearContext(?string $requestedSchoolYear): array
    {
        $requestedSchoolYear = trim((string) $requestedSchoolYear);
        $currentYear = trim((string) $this->configuration->getConfig('school_year'));

        $schoolYears = [];

        try {
            $schoolYears = DB::table('staff_attendance')
                ->select('school_year')
                ->distinct()
                ->whereNotNull('school_year')
                ->orderByDesc('school_year')
                ->pluck('school_year')
                ->map(static fn ($value) => trim((string) $value))
                ->filter()
                ->values()
                ->all();
        } catch (\Throwable) {
            $schoolYears = [];
        }

        if ($schoolYears === []) {
            try {
                $schoolYears = DB::table('teacher_class')
                    ->select('school_year')
                    ->distinct()
                    ->whereNotNull('school_year')
                    ->orderByDesc('school_year')
                    ->pluck('school_year')
                    ->map(static fn ($value) => trim((string) $value))
                    ->filter()
                    ->values()
                    ->all();
            } catch (\Throwable) {
                $schoolYears = [];
            }
        }

        if ($currentYear !== '' && ! in_array($currentYear, $schoolYears, true)) {
            array_unshift($schoolYears, $currentYear);
        }

        $schoolYears = array_values(array_unique(array_filter($schoolYears, static fn ($value) => $value !== '')));

        if ($currentYear === '' && $schoolYears !== []) {
            $currentYear = (string) $schoolYears[0];
            Configuration::setConfigValueByKey('school_year', $currentYear);
        }

        $resolvedYear = $requestedSchoolYear !== ''
            ? $requestedSchoolYear
            : ($currentYear !== '' ? $currentYear : ($schoolYears[0] ?? ''));

        if ($resolvedYear !== '' && ! in_array($resolvedYear, $schoolYears, true)) {
            array_unshift($schoolYears, $resolvedYear);
        }

        return [
            'school_year' => $resolvedYear,
            'current_year' => $currentYear,
            'school_years' => array_values(array_unique($schoolYears)),
            'missing_year' => $currentYear === '',
            'is_current_year' => $currentYear !== '' && $resolvedYear === $currentYear,
        ];
    }

    protected function buildRangeLabel(string $schoolYear, string $semesterNorm, string $rangeStart, string $rangeEnd): string
    {
        if ($semesterNorm === '') {
            return 'School Year '.$schoolYear;
        }

        return sprintf(
            '%s %s–%s',
            $semesterNorm,
            Carbon::parse($rangeStart)->format('d/m'),
            Carbon::parse($rangeEnd)->format('d/m')
        );
    }

    public function adminsAttendanceData(?string $date, ?string $semester, ?string $schoolYear): array
    {
        $date = $date ?: now()->toDateString();
        $semester = $semester ?: (string) $this->configuration->getConfig('semester');
        $schoolYear = $schoolYear ?: (string) $this->configuration->getConfig('school_year');

        $allStaff = $this->staffAttendance->staffWithStatusForDay($date, $semester, $schoolYear, []);
        $userIds = [];

        foreach ($allStaff as $row) {
            $uid = (int) ($row['user_id'] ?? 0);
            if ($uid > 0) {
                $userIds[$uid] = true;
            }
        }

        $userIds = array_keys($userIds);

        $rolesRows = DB::table('user_roles as ur')
            ->select('ur.user_id', 'r.id as role_id', 'r.name as role_name', 'r.slug as role_slug', 'r.priority')
            ->join('roles as r', 'r.id', '=', 'ur.role_id')
            ->where('r.is_active', 1)
            ->whereIn('ur.user_id', $userIds)
            ->get();

        $rolesByUser = [];
        foreach ($rolesRows as $row) {
            $rolesByUser[(int) $row->user_id][] = (array) $row;
        }

        foreach ($rolesByUser as &$list) {
            usort($list, fn ($a, $b) => ((int) ($a['priority'] ?? 100)) <=> ((int) ($b['priority'] ?? 100)));
        }

        $excludeSlugs = ['parent', 'guest', 'teacher', 'teacher_assistant', 'ta', 'assistant_teacher'];

        $staffByUser = [];
        foreach ($allStaff as $row) {
            $uid = (int) ($row['user_id'] ?? 0);
            if ($uid > 0 && ! isset($staffByUser[$uid])) {
                $staffByUser[$uid] = $row;
            }
        }

        $adminsGrid = [];
        foreach ($userIds as $uid) {
            $roles = $rolesByUser[$uid] ?? [];
            $picked = null;

            foreach ($roles as $role) {
                $slug = strtolower((string) ($role['role_slug'] ?? $role['role_name'] ?? ''));
                if (! in_array($slug, $excludeSlugs, true)) {
                    $picked = $role;
                    break;
                }
            }

            if (! $picked) {
                continue;
            }

            $base = $staffByUser[$uid] ?? ['user_id' => $uid];
            $base['role_name'] = $picked['role_name'] ?? 'Admin';
            $base['role_slug'] = $picked['role_slug'] ?? null;
            $adminsGrid[] = $base;
        }

        usort($adminsGrid, function ($a, $b) {
            $an = trim(($a['firstname'] ?? '').' '.($a['lastname'] ?? ''));
            $bn = trim(($b['firstname'] ?? '').' '.($b['lastname'] ?? ''));

            return strcasecmp($an, $bn);
        });

        return [
            'semester' => $semester,
            'school_year' => $schoolYear,
            'date' => $date,
            'admins_grid' => $adminsGrid,
        ];
    }

    public function saveAdmins(Authenticatable $user, array $data): array
    {
        $date = (string) $data['date'];
        $semester = (string) $data['semester'];
        $schoolYear = (string) $data['school_year'];
        $admins = (array) ($data['admins'] ?? $data['staff'] ?? []);
        $allowedStatuses = ['present', 'absent', 'late'];
        $excludedRoleSlugs = ['parent', 'guest', 'teacher', 'teacher_assistant'];

        DB::transaction(function () use ($admins, $date, $semester, $schoolYear, $allowedStatuses, $excludedRoleSlugs) {
            foreach ($admins as $uid => $row) {
                $uid = (int) $uid;
                $status = strtolower(trim((string) ($row['status'] ?? '')));
                $reason = trim((string) ($row['reason'] ?? ''));
                $roleName = (string) ($row['role_name'] ?? '');
                $roleSlug = strtolower(str_replace(['-', ' '], '_', (string) ($row['role_slug'] ?? $roleName)));

                if (in_array($roleSlug, $excludedRoleSlugs, true)) {
                    continue;
                }

                if ($status === '') {
                    DB::table('staff_attendance')
                        ->where('user_id', $uid)
                        ->whereDate('date', $date)
                        ->where('semester', $semester)
                        ->where('school_year', $schoolYear)
                        ->delete();

                    continue;
                }

                if (! in_array($status, $allowedStatuses, true)) {
                    continue;
                }

                DB::table('staff_attendance')->updateOrInsert(
                    [
                        'user_id' => $uid,
                        'date' => $date,
                        'semester' => $semester,
                        'school_year' => $schoolYear,
                    ],
                    [
                        'role_name' => $roleName !== '' ? $roleName : 'Admin',
                        'status' => $status,
                        'reason' => $reason !== '' ? $reason : null,
                        'updated_at' => now(),
                        'created_at' => now(),
                    ]
                );
            }
        });

        return [
            'ok' => true,
            'message' => 'Admins attendance saved successfully.',
        ];
    }

    public function saveCell(Authenticatable $user, array $data): array
    {
        $date = (string) $data['date'];
        $status = (string) ($data['status'] ?? '');
        $targetId = (int) $data['user_id'];
        $roleName = trim((string) ($data['role_name'] ?? ''));
        $semester = (string) $data['semester'];
        $schoolYear = (string) $data['school_year'];
        $reason = trim((string) ($data['reason'] ?? ''));

        if ($status === '') {
            DB::table('staff_attendance')
                ->where('user_id', $targetId)
                ->whereDate('date', $date)
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->delete();

            return ['ok' => true];
        }

        DB::table('staff_attendance')->updateOrInsert(
            [
                'user_id' => $targetId,
                'date' => $date,
                'semester' => $semester,
                'school_year' => $schoolYear,
            ],
            [
                'role_name' => $roleName !== '' ? $roleName : 'Admin',
                'status' => $status,
                'reason' => $reason !== '' ? $reason : null,
                'updated_at' => now(),
                'created_at' => now(),
            ]
        );

        return ['ok' => true];
    }

    public function monthCsv(?string $semester, ?string $schoolYear, ?string $scope): StreamedResponse
    {
        $semester = $semester ?: (string) $this->configuration->getConfig('semester');
        $schoolYear = $schoolYear ?: (string) $this->configuration->getConfig('school_year');
        $scope = strtolower((string) $scope);

        [$rangeStart, $rangeEnd] = $this->semesterRangeService->getSchoolYearRange($schoolYear);
        $semesterNorm = $this->semesterRangeService->normalizeSemester($semester);
        if ($semesterNorm !== '') {
            $semesterRange = $this->semesterRangeService->getSemesterRange($schoolYear, $semesterNorm);
            if ($semesterRange) {
                [$rangeStart, $rangeEnd] = $semesterRange;
            }
        }

        $daysList = $this->semesterRangeService->buildSundayList($rangeStart, $rangeEnd);

        $filename = ($scope === 'admins' ? 'admin_attendance_' : 'teacher_attendance_')
            .$semester.'_'.str_replace('/', '-', $schoolYear).'.csv';

        return response()->streamDownload(function () use ($schoolYear, $scope, $semesterNorm, $rangeStart, $rangeEnd) {
            $out = fopen('php://output', 'w');

            if ($scope === 'admins') {
                fputcsv($out, ['User ID', 'Date', 'Status', 'Reason']);

                $query = DB::table('staff_attendance')
                    ->select('user_id', 'date', 'status', 'reason')
                    ->where('school_year', $schoolYear)
                    ->where('date', '>=', $rangeStart)
                    ->where('date', '<=', $rangeEnd);

                if ($semesterNorm !== '') {
                    $query->where('semester', $semesterNorm);
                }

                foreach ($query->orderBy('date')->get() as $row) {
                    fputcsv($out, [
                        $row->user_id,
                        $row->date,
                        strtoupper(substr((string) $row->status, 0, 1)),
                        $row->reason,
                    ]);
                }

                fclose($out);

                return;
            }

            fputcsv($out, ['Teacher ID', 'Date', 'Status', 'Reason']);

            $query = DB::table('staff_attendance')
                ->select('user_id', 'date', 'status', 'reason')
                ->where('school_year', $schoolYear)
                ->where('date', '>=', $rangeStart)
                ->where('date', '<=', $rangeEnd);

            if ($semesterNorm !== '') {
                $query->where('semester', $semesterNorm);
            }

            foreach ($query->orderBy('date')->get() as $row) {
                fputcsv($out, [
                    $row->user_id,
                    $row->date,
                    strtoupper(substr((string) $row->status, 0, 1)),
                    $row->reason,
                ]);
            }

            fclose($out);
        }, $filename);
    }
}