<?php

namespace App\Services;

use App\Models\AttendanceRecordModel;
use App\Models\AttendanceSessionModel;
use App\Models\NotificationModel;
use CodeIgniter\Shield\Entities\User;
use DateTimeImmutable;

class AttendanceService
{
    private AuthorizationService $authorizationService;
    private JourneyService $journeyService;

    public function __construct()
    {
        helper(['academy', 'url']);

        $this->authorizationService = service('authorization');
        $this->journeyService       = service('journey');
    }

    public function calculateRate(int $attendedSessions, int $totalSessions): float
    {
        if ($totalSessions === 0) {
            return 0.0;
        }

        return round(($attendedSessions / $totalSessions) * 100, 1);
    }

    /**
     * @param array<string, int> $totals
     */
    public function calculateRateFromBreakdown(array $totals): float
    {
        $attended = (int) ($totals['present'] ?? 0) + (int) ($totals['recovery'] ?? 0);
        $total    = array_sum($totals);

        return $this->calculateRate($attended, $total);
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, mixed>
     */
    public function getIndexData(array $input, User $user): array
    {
        $filters  = $this->buildFilters($input, (int) $user->id);
        $sessions = $this->getSessionRows($filters);

        return [
            'pageTitle' => 'Prezenta',
            'pageDescription' => 'Planificare sesiuni, check-in rapid pe grupe si control asupra absentelor sau recovery sessions.',
            'filters' => $filters,
            'summary' => $this->buildIndexSummary($sessions),
            'sessions' => $sessions,
            'canCreate' => $filters['accessible_group_ids'] !== [],
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @param array<string, string> $errors
     * @return array<string, mixed>
     */
    public function getFormData(User $user, array $input = [], array $errors = []): array
    {
        $userId          = (int) $user->id;
        $primaryRole     = $this->authorizationService->getPrimaryRole($userId);
        $lockedTrainerId   = $primaryRole === 'coach' ? $this->authorizationService->getCoachIdForUser($userId) : null;
        $groupOptions    = $this->groupOptions($userId, $lockedTrainerId);
        $coachOptions    = $this->coachOptions($lockedTrainerId);
        $recoveryOptions = $this->recoverySessionOptions($userId);

        $values = [
            'academy_group_id' => (string) ($groupOptions[0]['id'] ?? ''),
            'coach_id' => (string) ($lockedTrainerId ?? ''),
            'session_date' => date('Y-m-d'),
            'start_time' => '17:00',
            'end_time' => '18:30',
            'status' => 'scheduled',
            'cancelled_reason' => '',
            'recovery_for_session_id' => '',
            'notes' => '',
        ];

        foreach ($input as $key => $value) {
            if (array_key_exists($key, $values) && is_scalar($value)) {
                $values[$key] = trim((string) $value);
            }
        }

        if ($lockedTrainerId !== null) {
            $values['coach_id'] = (string) $lockedTrainerId;
        }

        return [
            'pageTitle' => 'Sesiune noua',
            'pageDescription' => 'Configureaza o sedinta noua, inclusiv scenariile de anulare sau recovery session.',
            'values' => $values,
            'errors' => $errors,
            'groupOptions' => $groupOptions,
            'coachOptions' => $coachOptions,
            'recoveryOptions' => $recoveryOptions,
            'statusOptions' => session_status_options(),
            'coachLocked' => $lockedTrainerId !== null,
            'submitUrl' => url_to('attendance.create'),
            'cancelUrl' => url_to('attendance.index'),
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @return array{success: bool, session_id?: int, errors?: array<string, string>, forbidden?: bool}
     */
    public function saveSession(array $input, User $user): array
    {
        $validation = service('validation');
        $validation->setRules(config('Validation')->attendanceSession);

        $payload = $this->normalizeSessionPayload($input);

        if (! $validation->run($payload)) {
            return [
                'success' => false,
                'errors' => $validation->getErrors(),
            ];
        }

        $userId      = (int) $user->id;
        $primaryRole = $this->authorizationService->getPrimaryRole($userId);
        $lockedTrainer = $primaryRole === 'coach' ? $this->authorizationService->getCoachIdForUser($userId) : null;
        $groupId     = (int) $payload['academy_group_id'];

        if (! $this->authorizationService->userCanAccessGroup($userId, $groupId)) {
            return ['success' => false, 'forbidden' => true];
        }

        if ($lockedTrainer !== null) {
            $payload['coach_id'] = (string) $lockedTrainer;
        }

        $extraErrors = $this->validateSessionPayload($payload, $userId);
        if ($extraErrors !== []) {
            return ['success' => false, 'errors' => $extraErrors];
        }

        $now         = date('Y-m-d H:i:s');
        $status      = $payload['status'];
        $isCancelled = $status === 'cancelled' ? 1 : 0;

        model(AttendanceSessionModel::class)->insert([
            'academy_group_id' => $groupId,
            'coach_id' => $payload['coach_id'] !== '' ? (int) $payload['coach_id'] : null,
            'session_date' => $payload['session_date'],
            'start_time' => $payload['start_time'] !== '' ? $payload['start_time'] . ':00' : null,
            'end_time' => $payload['end_time'] !== '' ? $payload['end_time'] . ':00' : null,
            'status' => $status,
            'is_cancelled' => $isCancelled,
            'cancelled_reason' => $payload['cancelled_reason'] !== '' ? $payload['cancelled_reason'] : null,
            'recovery_for_session_id' => $payload['recovery_for_session_id'] !== '' ? (int) $payload['recovery_for_session_id'] : null,
            'notes' => $payload['notes'] !== '' ? $payload['notes'] : null,
            'created_at' => $now,
            'updated_at' => $now,
        ]);

        $sessionId = (int) model(AttendanceSessionModel::class)->getInsertID();

        if ($status === 'recovery') {
            $this->recordRecoveryJourneyEvents($sessionId, $groupId, $userId, $payload['session_date'], $payload['recovery_for_session_id'] !== '' ? (int) $payload['recovery_for_session_id'] : null);
        }

        return [
            'success' => true,
            'session_id' => $sessionId,
        ];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getSessionData(int $sessionId, User $user): ?array
    {
        $session = $this->getSessionRow($sessionId);
        if ($session === null || ! $this->authorizationService->userCanAccessSession((int) $user->id, $sessionId)) {
            return null;
        }

        $roster    = $this->getSessionRoster($sessionId, (int) $session['academy_group_id']);
        $breakdown = $this->buildRosterBreakdown($roster);

        return [
            'pageTitle' => 'Check-in sesiune',
            'pageDescription' => 'Operare prezenta pe grupa, inclusiv absente motivate, nemotivate si recuperari.',
            'session' => $session,
            'roster' => $roster,
            'breakdown' => $breakdown,
            'recordOptions' => attendance_record_options(),
            'saveUrl' => url_to('attendance.checkIn', $sessionId),
            'backUrl' => url_to('attendance.index'),
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @return array{success: bool, errors?: array<string, string>, forbidden?: bool}
     */
    public function saveCheckIn(int $sessionId, array $input, User $user): array
    {
        $session = $this->getSessionRow($sessionId);
        if ($session === null) {
            return ['success' => false, 'errors' => ['general' => 'Sesiunea nu exista.']];
        }

        $userId = (int) $user->id;
        if (! $this->authorizationService->userCanAccessSession($userId, $sessionId)) {
            return ['success' => false, 'forbidden' => true];
        }

        if ((int) $session['is_cancelled'] === 1) {
            return ['success' => false, 'errors' => ['general' => 'Sesiunea este anulata si nu poate primi check-in.']];
        }

        $records = $input['records'] ?? null;
        if (! is_array($records)) {
            return ['success' => false, 'errors' => ['general' => 'Nu au fost transmise inregistrari de prezenta.']];
        }

        $roster  = $this->getSessionRoster($sessionId, (int) $session['academy_group_id']);
        $allowed = array_keys(attendance_record_options());
        $now     = date('Y-m-d H:i:s');
        $db      = $this->db();

        $db->transStart();

        foreach ($roster as $row) {
            $childId      = (int) $row['id'];
            $recordInput  = $records[$childId] ?? [];
            $status       = trim((string) ($recordInput['status'] ?? ($row['attendance_status'] ?? 'present')));
            $notes        = trim((string) ($recordInput['notes'] ?? ''));
            $recordId     = $row['attendance_record_id'] !== null ? (int) $row['attendance_record_id'] : null;

            if (! in_array($status, $allowed, true)) {
                $db->transRollback();

                return ['success' => false, 'errors' => ['general' => 'Unul dintre statusurile de prezenta nu este valid.']];
            }

            $payload = [
                'attendance_session_id' => $sessionId,
                'child_id' => $childId,
                'status' => $status,
                'checked_in_at' => in_array($status, ['present', 'recovery'], true)
                    ? $session['session_date'] . ' ' . ($session['start_time'] ?: '17:00:00')
                    : null,
                'notes' => $notes !== '' ? $notes : null,
                'updated_at' => $now,
            ];

            if ($recordId === null) {
                $payload['created_at'] = $now;
                model(AttendanceRecordModel::class)->insert($payload);
            } else {
                model(AttendanceRecordModel::class)->update($recordId, $payload);
            }

            if (in_array($status, ['absent_excused', 'absent_unexcused'], true)) {
                $this->createAttendanceIssueIfNeeded($childId, $userId, $session, $status);
            }
        }

        model(AttendanceSessionModel::class)->update($sessionId, [
            'status' => $session['status'] === 'scheduled' ? 'completed' : $session['status'],
            'updated_at' => $now,
        ]);

        $db->transComplete();

        if (! $db->transStatus()) {
            return ['success' => false, 'errors' => ['general' => 'Check-in-ul nu a putut fi salvat.']];
        }

        return ['success' => true];
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, mixed>
     */
    private function buildFilters(array $input, int $userId): array
    {
        $settings       = $this->settingsMap();
        $primaryRole    = $this->authorizationService->getPrimaryRole($userId);
        $lockedTrainerId  = $primaryRole === 'coach' ? $this->authorizationService->getCoachIdForUser($userId) : null;
        $selectedPeriod = (string) ($input['period'] ?? ($settings['default_dashboard_period'] ?? '30'));
        $today          = new DateTimeImmutable('today');
        [$dateStart, $dateEnd] = $this->resolveDates($selectedPeriod, $today);

        $groupOptions  = $this->groupOptions($userId, $lockedTrainerId);
        $coachOptions  = $this->coachOptions($lockedTrainerId);
        $selectedGroup = (int) ($input['group_id'] ?? 0);
        $selectedTrainer = $lockedTrainerId ?: (int) ($input['coach_id'] ?? 0);
        $selectedStatus = (string) ($input['status'] ?? 'all');

        if (! in_array($selectedStatus, ['all', 'scheduled', 'completed', 'cancelled', 'recovery'], true)) {
            $selectedStatus = 'all';
        }

        return [
            'current_role' => $primaryRole,
            'coach_locked' => $lockedTrainerId !== null,
            'selected_period' => $selectedPeriod,
            'selected_group_id' => $selectedGroup,
            'selected_coach_id' => $selectedTrainer,
            'selected_status' => $selectedStatus,
            'date_start' => $dateStart,
            'date_end' => $dateEnd,
            'period_options' => [
                '7' => 'Ultimele 7 zile',
                '30' => 'Ultimele 30 zile',
                '90' => 'Ultimele 90 zile',
                'month' => 'Luna curenta',
            ],
            'status_options' => [
                'all' => 'Toate statusurile',
                'scheduled' => 'Scheduled',
                'completed' => 'Completed',
                'cancelled' => 'Cancelled',
                'recovery' => 'Recovery',
            ],
            'group_options' => $groupOptions,
            'coach_options' => $coachOptions,
            'accessible_group_ids' => array_values(array_map(static fn (array $row): int => (int) $row['id'], $groupOptions)),
        ];
    }

    /**
     * @return array{0: string, 1: string}
     */
    private function resolveDates(string $selectedPeriod, DateTimeImmutable $today): array
    {
        if ($selectedPeriod === 'month') {
            return [
                $today->modify('first day of this month')->format('Y-m-d'),
                $today->format('Y-m-d'),
            ];
        }

        $days = (int) $selectedPeriod;
        if (! in_array($days, [7, 30, 90], true)) {
            $days = 30;
        }

        return [
            $today->modify('-' . ($days - 1) . ' days')->format('Y-m-d'),
            $today->format('Y-m-d'),
        ];
    }

    /**
     * @param array<string, mixed> $filters
     * @return list<array<string, mixed>>
     */
    private function getSessionRows(array $filters): array
    {
        if ($filters['coach_locked'] && $filters['accessible_group_ids'] === []) {
            return [];
        }

        $builder = $this->db()
            ->table('attendance_sessions s')
            ->select('s.*, groups.name as group_name, groups.code as group_code, coaches.full_name as coach_name')
            ->join('academy_groups groups', 'groups.id = s.academy_group_id')
            ->join('coaches', 'coaches.id = s.coach_id', 'left')
            ->where('s.session_date >=', $filters['date_start'])
            ->where('s.session_date <=', $filters['date_end'])
            ->orderBy('s.session_date', 'DESC')
            ->orderBy('s.id', 'DESC');

        if ($filters['coach_locked']) {
            $builder->whereIn('s.academy_group_id', $filters['accessible_group_ids']);
        }

        if ($filters['selected_group_id'] > 0) {
            $builder->where('s.academy_group_id', $filters['selected_group_id']);
        }

        if ($filters['selected_coach_id'] > 0) {
            $builder->where('s.coach_id', $filters['selected_coach_id']);
        }

        if ($filters['selected_status'] !== 'all') {
            $builder->where('s.status', $filters['selected_status']);
        }

        $sessions = $builder->get()->getResultArray();
        if ($sessions === []) {
            return [];
        }

        $sessionIds = array_values(array_map(static fn (array $row): int => (int) $row['id'], $sessions));
        $groupIds   = array_values(array_unique(array_map(static fn (array $row): int => (int) $row['academy_group_id'], $sessions)));
        $metrics    = $this->sessionMetricMap($sessionIds);
        $rosters    = $this->groupRosterCountMap($groupIds);

        foreach ($sessions as &$session) {
            $sessionId                  = (int) $session['id'];
            $groupId                    = (int) $session['academy_group_id'];
            $stats                      = $metrics[$sessionId] ?? ['attended' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0, 'total' => 0];
            $session['participants']    = $stats['total'] > 0 ? $stats['total'] : ($rosters[$groupId] ?? 0);
            $session['attendance_rate'] = $this->calculateRate((int) $stats['attended'], (int) $stats['total']);
            $session['attendance_label'] = format_percentage((float) $session['attendance_rate'], 1);
            $session['breakdown']       = $stats;
        }
        unset($session);

        return $sessions;
    }

    /**
     * @param list<array<string, mixed>> $sessions
     * @return list<array<string, mixed>>
     */
    private function buildIndexSummary(array $sessions): array
    {
        $attendanceRates = [];
        $cancelled       = 0;
        $completed       = 0;
        $recovery        = 0;

        foreach ($sessions as $session) {
            if ($session['status'] === 'cancelled') {
                $cancelled++;
            }

            if ($session['status'] === 'completed') {
                $completed++;
            }

            if ($session['status'] === 'recovery') {
                $recovery++;
            }

            if ((int) ($session['breakdown']['total'] ?? 0) > 0) {
                $attendanceRates[] = (float) $session['attendance_rate'];
            }
        }

        $avgAttendance = $attendanceRates === [] ? 0.0 : round(array_sum($attendanceRates) / count($attendanceRates), 1);

        return [
            ['label' => 'Sesiuni in selectie', 'value' => count($sessions), 'meta' => 'Toate sedintele filtrate'],
            ['label' => 'Finalizate', 'value' => $completed, 'meta' => 'Sedinte complet operate'],
            ['label' => 'Recovery', 'value' => $recovery, 'meta' => 'Sedinte de recuperare'],
            ['label' => 'Anulate', 'value' => $cancelled, 'meta' => 'Sedinte anulate'],
            ['label' => 'Prezenta medie', 'value' => format_percentage($avgAttendance, 1), 'meta' => 'Doar sesiuni cu check-in salvat'],
        ];
    }

    /**
     * @param array<string, string> $payload
     * @return array<string, string>
     */
    private function validateSessionPayload(array $payload, int $userId): array
    {
        $errors = [];

        if ($payload['start_time'] !== '' && $payload['end_time'] !== '' && strtotime($payload['start_time']) >= strtotime($payload['end_time'])) {
            $errors['end_time'] = 'Ora de final trebuie sa fie dupa ora de start.';
        }

        if ($payload['status'] === 'cancelled' && $payload['cancelled_reason'] === '') {
            $errors['cancelled_reason'] = 'Explica motivul anularii sedintei.';
        }

        if ($payload['status'] === 'recovery' && $payload['recovery_for_session_id'] === '') {
            $errors['recovery_for_session_id'] = 'Selecteaza sedinta anulata pe care o recuperezi.';
        }

        if ($payload['recovery_for_session_id'] !== '' && ! $this->authorizationService->userCanAccessSession($userId, (int) $payload['recovery_for_session_id'])) {
            $errors['recovery_for_session_id'] = 'Nu ai acces la sesiunea selectata pentru recovery.';
        }

        return $errors;
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, string>
     */
    private function normalizeSessionPayload(array $input): array
    {
        return [
            'academy_group_id' => trim((string) ($input['academy_group_id'] ?? '')),
            'coach_id' => trim((string) ($input['coach_id'] ?? '')),
            'session_date' => trim((string) ($input['session_date'] ?? '')),
            'start_time' => trim((string) ($input['start_time'] ?? '')),
            'end_time' => trim((string) ($input['end_time'] ?? '')),
            'status' => trim((string) ($input['status'] ?? 'scheduled')),
            'cancelled_reason' => trim((string) ($input['cancelled_reason'] ?? '')),
            'recovery_for_session_id' => trim((string) ($input['recovery_for_session_id'] ?? '')),
            'notes' => trim((string) ($input['notes'] ?? '')),
        ];
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function groupOptions(int $userId, ?int $lockedTrainerId = null): array
    {
        $builder = $this->db()
            ->table('academy_groups groups')
            ->select('groups.id, groups.name, groups.code, groups.primary_coach_id, coaches.full_name as coach_name')
            ->join('coaches', 'coaches.id = groups.primary_coach_id', 'left')
            ->where('groups.status', 'active')
            ->orderBy('groups.name', 'ASC');

        if ($lockedTrainerId !== null) {
            $builder->where('groups.primary_coach_id', $lockedTrainerId);
        } elseif (! $this->authorizationService->userHasRole($userId, 'admin')) {
            return [];
        }

        return $builder->get()->getResultArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function coachOptions(?int $lockedTrainerId = null): array
    {
        $builder = $this->db()
            ->table('coaches')
            ->select('id, full_name')
            ->where('is_active', 1)
            ->orderBy('full_name', 'ASC');

        if ($lockedTrainerId !== null) {
            $builder->where('id', $lockedTrainerId);
        }

        return $builder->get()->getResultArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function recoverySessionOptions(int $userId): array
    {
        $builder = $this->db()
            ->table('attendance_sessions s')
            ->select('s.id, s.session_date, groups.name as group_name')
            ->join('academy_groups groups', 'groups.id = s.academy_group_id')
            ->where('s.is_cancelled', 1)
            ->orderBy('s.session_date', 'DESC')
            ->limit(12);

        if ($this->authorizationService->userHasRole($userId, 'coach') && ! $this->authorizationService->userHasRole($userId, 'admin')) {
            $groupIds = array_values(array_map(static fn (array $row): int => (int) $row['id'], $this->groupOptions($userId, $this->authorizationService->getCoachIdForUser($userId))));
            if ($groupIds === []) {
                return [];
            }
            $builder->whereIn('s.academy_group_id', $groupIds);
        }

        return $builder->get()->getResultArray();
    }

    /**
     * @param list<int> $sessionIds
     * @return array<int, array<string, int>>
     */
    private function sessionMetricMap(array $sessionIds): array
    {
        if ($sessionIds === []) {
            return [];
        }

        $rows = $this->db()
            ->table('attendance_records')
            ->select("
                attendance_session_id,
                SUM(CASE WHEN status IN ('present', 'recovery') THEN 1 ELSE 0 END) as attended,
                SUM(CASE WHEN status = 'recovery' THEN 1 ELSE 0 END) as recovery,
                SUM(CASE WHEN status = 'absent_excused' THEN 1 ELSE 0 END) as absent_excused,
                SUM(CASE WHEN status = 'absent_unexcused' THEN 1 ELSE 0 END) as absent_unexcused,
                COUNT(*) as total
            ", false)
            ->whereIn('attendance_session_id', $sessionIds)
            ->groupBy('attendance_session_id')
            ->get()
            ->getResultArray();

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['attendance_session_id']] = [
                'attended' => (int) $row['attended'],
                'recovery' => (int) $row['recovery'],
                'absent_excused' => (int) $row['absent_excused'],
                'absent_unexcused' => (int) $row['absent_unexcused'],
                'total' => (int) $row['total'],
            ];
        }

        return $map;
    }

    /**
     * @param list<int> $groupIds
     * @return array<int, int>
     */
    private function groupRosterCountMap(array $groupIds): array
    {
        if ($groupIds === []) {
            return [];
        }

        $rows = $this->db()
            ->table('children')
            ->select('academy_group_id, COUNT(*) as total')
            ->whereIn('academy_group_id', $groupIds)
            ->where('status !=', 'left')
            ->groupBy('academy_group_id')
            ->get()
            ->getResultArray();

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['academy_group_id']] = (int) $row['total'];
        }

        return $map;
    }

    /**
     * @return array<string, mixed>|null
     */
    private function getSessionRow(int $sessionId): ?array
    {
        return $this->db()
            ->table('attendance_sessions s')
            ->select('s.*, groups.name as group_name, groups.code as group_code, groups.primary_coach_id as group_primary_coach_id, coaches.full_name as coach_name, recovery.session_date as recovery_source_date')
            ->join('academy_groups groups', 'groups.id = s.academy_group_id')
            ->join('coaches', 'coaches.id = s.coach_id', 'left')
            ->join('attendance_sessions recovery', 'recovery.id = s.recovery_for_session_id', 'left')
            ->where('s.id', $sessionId)
            ->get()
            ->getRowArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function getSessionRoster(int $sessionId, int $groupId): array
    {
        $rows = $this->db()
            ->table('children c')
            ->select('c.id, c.full_name, c.status as child_status, levels.name as level_name, ar.id as attendance_record_id, ar.status as attendance_status, ar.notes as attendance_notes, ar.checked_in_at')
            ->join('academy_levels levels', 'levels.id = c.academy_level_id', 'left')
            ->join('attendance_records ar', 'ar.child_id = c.id AND ar.attendance_session_id = ' . $sessionId, 'left')
            ->where('c.academy_group_id', $groupId)
            ->where('c.status !=', 'left')
            ->orderBy('c.full_name', 'ASC')
            ->get()
            ->getResultArray();

        foreach ($rows as &$row) {
            $row['selected_status'] = $row['attendance_status'] ?? 'present';
        }
        unset($row);

        return $rows;
    }

    /**
     * @param list<array<string, mixed>> $roster
     * @return array<string, int|float>
     */
    private function buildRosterBreakdown(array $roster): array
    {
        $saved = ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0];

        foreach ($roster as $row) {
            $status = $row['attendance_status'];
            if ($status === null || ! isset($saved[$status])) {
                continue;
            }

            $saved[$status]++;
        }

        $saved['participants'] = count($roster);
        $saved['saved_records'] = array_sum(array_intersect_key($saved, array_flip(['present', 'recovery', 'absent_excused', 'absent_unexcused'])));
        $saved['attendance_rate'] = $this->calculateRateFromBreakdown($saved);

        return $saved;
    }

    private function recordRecoveryJourneyEvents(int $sessionId, int $groupId, int $userId, string $sessionDate, ?int $recoverySourceId): void
    {
        $children = $this->db()
            ->table('children')
            ->select('id')
            ->where('academy_group_id', $groupId)
            ->where('status !=', 'left')
            ->get()
            ->getResultArray();

        foreach ($children as $child) {
            $this->journeyService->recordEvent([
                'child_id' => (int) $child['id'],
                'event_type' => 'recovery_session_added',
                'title' => 'Sesiune de recuperare programata',
                'description' => 'A fost planificata o sesiune de recuperare pentru grupa copilului.',
                'metadata' => json_encode([
                    'attendance_session_id' => $sessionId,
                    'recovery_for_session_id' => $recoverySourceId,
                    'session_date' => $sessionDate,
                ], JSON_UNESCAPED_UNICODE),
                'created_by' => $userId,
                'created_at' => $sessionDate . ' 08:00:00',
            ]);
        }
    }

    /**
     * @param array<string, mixed> $session
     */
    private function createAttendanceIssueIfNeeded(int $childId, int $userId, array $session, string $status): void
    {
        $settings      = $this->settingsMap();
        $windowDays    = max(7, (int) ($settings['default_dashboard_period'] ?? 30));
        $threshold     = (float) ($settings['absence_alert_threshold'] ?? 25);
        $sessionDate   = (string) $session['session_date'];
        $windowStart   = (new DateTimeImmutable($sessionDate))->modify('-' . ($windowDays - 1) . ' days')->format('Y-m-d');
        $attendanceRows = $this->db()
            ->table('attendance_records ar')
            ->select('ar.status')
            ->join('attendance_sessions sessions', 'sessions.id = ar.attendance_session_id')
            ->where('ar.child_id', $childId)
            ->where('sessions.is_cancelled', 0)
            ->where('sessions.session_date >=', $windowStart)
            ->where('sessions.session_date <=', $sessionDate)
            ->get()
            ->getResultArray();

        $breakdown = ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0];
        foreach ($attendanceRows as $row) {
            $rowStatus = (string) $row['status'];
            if (isset($breakdown[$rowStatus])) {
                $breakdown[$rowStatus]++;
            }
        }

        if (array_sum($breakdown) === 0) {
            return;
        }

        $absenceRate = 100 - $this->calculateRateFromBreakdown($breakdown);
        if ($absenceRate < $threshold) {
            return;
        }

        $exists = $this->db()
            ->table('journey_events')
            ->select('id')
            ->where('child_id', $childId)
            ->where('event_type', 'attendance_issue')
            ->where('created_at >=', $sessionDate . ' 00:00:00')
            ->where('created_at <=', $sessionDate . ' 23:59:59')
            ->get()
            ->getRowArray();

        if ($exists === null) {
            $this->journeyService->recordEvent([
                'child_id' => $childId,
                'event_type' => 'attendance_issue',
                'title' => 'Avertizare de prezenta',
                'description' => 'Absenta a depasit pragul configurat pentru perioada operationala curenta.',
                'metadata' => json_encode([
                    'absence_rate' => round($absenceRate, 1),
                    'window_start' => $windowStart,
                    'window_end' => $sessionDate,
                    'last_status' => $status,
                ], JSON_UNESCAPED_UNICODE),
                'created_by' => $userId,
                'created_at' => $sessionDate . ' 20:30:00',
            ]);
        }

        $this->createAttendanceNotifications($childId, $session, $absenceRate);
    }

    /**
     * @param array<string, mixed> $session
     */
    private function createAttendanceNotifications(int $childId, array $session, float $absenceRate): void
    {
        $targetUserIds = [];

        $adminRows = $this->db()
            ->table('user_roles')
            ->select('user_roles.user_id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('roles.slug', 'admin')
            ->get()
            ->getResultArray();

        foreach ($adminRows as $row) {
            $targetUserIds[] = (int) $row['user_id'];
        }

        if (! empty($session['coach_id'])) {
            $coachUserId = $this->db()
                ->table('coaches')
                ->select('user_id')
                ->where('id', (int) $session['coach_id'])
                ->get()
                ->getRowArray();

            if ($coachUserId !== null && ! empty($coachUserId['user_id'])) {
                $targetUserIds[] = (int) $coachUserId['user_id'];
            }
        }

        $targetUserIds = array_values(array_unique($targetUserIds));

        foreach ($targetUserIds as $targetUserId) {
            $existing = $this->db()
                ->table('notifications')
                ->select('id')
                ->where('user_id', $targetUserId)
                ->where('child_id', $childId)
                ->where('type', 'attendance_alert')
                ->where('status', 'unread')
                ->get()
                ->getRowArray();

            if ($existing !== null) {
                continue;
            }

            model(NotificationModel::class)->insert([
                'user_id' => $targetUserId,
                'child_id' => $childId,
                'type' => 'attendance_alert',
                'title' => 'Absente peste prag',
                'message' => 'Copilul a depasit pragul de absenta cu ' . format_percentage($absenceRate, 1) . ' in perioada recenta.',
                'status' => 'unread',
                'action_url' => url_to('children.show', $childId),
                'created_at' => date('Y-m-d H:i:s'),
                'read_at' => null,
            ]);
        }
    }

    /**
     * @return array<string, string>
     */
    private function settingsMap(): array
    {
        $rows = $this->db()
            ->table('settings')
            ->select('`key`, value')
            ->get()
            ->getResultArray();

        $settings = [];
        foreach ($rows as $row) {
            $settings[$row['key']] = $row['value'];
        }

        return $settings;
    }

    private function db()
    {
        return db_connect();
    }
}
