<?php

namespace App\Services;

use App\Models\ChildCoachModel;
use App\Models\ChildGuardianModel;
use App\Models\ChildModel;
use App\Models\GuardianModel;
use CodeIgniter\Shield\Entities\User;

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

    public function __construct()
    {
        helper('academy');

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

    /**
     * @param array<string, mixed> $input
     * @return array<string, mixed>
     */
    public function getIndexData(array $input, User $user): array
    {
        $filters    = $this->buildIndexFilters($input, (int) $user->id);
        $children   = $this->getChildrenRows($filters);
        $metricsMap = $this->buildChildMetricMaps(array_map(static fn (array $row): int => (int) $row['id'], $children));

        foreach ($children as &$child) {
            $childId                   = (int) $child['id'];
            $child['avg_score']        = $metricsMap['scores'][$childId]['avg_score'] ?? null;
            $child['avg_score_label']  = format_score($child['avg_score']);
            $child['attendance_rate']  = $metricsMap['attendance'][$childId] ?? 0.0;
            $child['attendance_label'] = format_percentage((float) $child['attendance_rate'], 1);
            $child['evaluations_count'] = $metricsMap['scores'][$childId]['evaluations_count'] ?? 0;
        }
        unset($child);

        return [
            'pageTitle' => 'Copii',
            'pageDescription' => 'Administrare copii, cautare rapida si acces direct spre profilul individual.',
            'filters' => $filters,
            'children' => $children,
            'canManage' => $filters['current_role'] === 'admin',
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @param array<string, string> $errors
     * @return array<string, mixed>
     */
    public function getFormData(User $user, ?int $childId = null, array $input = [], array $errors = []): array
    {
        $child = $childId !== null ? $this->getChildWithRelations($childId) : null;

        $values = [
            'first_name' => '',
            'last_name' => '',
            'birth_date' => '',
            'gender' => '',
            'academy_level_id' => '',
            'academy_group_id' => '',
            'primary_coach_id' => '',
            'first_class_at' => '',
            'status' => 'active',
            'has_siblings' => '0',
            'notes' => '',
            'guardian_name' => '',
            'guardian_phone' => '',
            'guardian_email' => '',
        ];

        if ($child !== null) {
            $values = [
                'first_name' => $child['first_name'],
                'last_name' => $child['last_name'],
                'birth_date' => $child['birth_date'],
                'gender' => $child['gender'],
                'academy_level_id' => (string) ($child['academy_level_id'] ?? ''),
                'academy_group_id' => (string) ($child['academy_group_id'] ?? ''),
                'primary_coach_id' => (string) ($child['primary_coach_id'] ?? ''),
                'first_class_at' => $child['first_class_at'],
                'status' => $child['status'],
                'has_siblings' => (string) ((int) ($child['has_siblings'] ?? 0)),
                'notes' => $child['notes'],
                'guardian_name' => $child['guardian_name'],
                'guardian_phone' => $child['guardian_phone'],
                'guardian_email' => $child['guardian_email'],
            ];
        }

        foreach ($input as $key => $value) {
            if (array_key_exists($key, $values)) {
                $values[$key] = is_string($value) ? trim($value) : $value;
            }
        }

        return [
            'pageTitle' => $childId === null ? 'Copil nou' : 'Editare copil',
            'pageDescription' => $childId === null
                ? 'Introdu datele de baza ale copilului, tutorului si asignarile initiale.'
                : 'Actualizeaza profilul copilului fara sa pierzi istoricul de evaluari si journey.',
            'child' => $child,
            'values' => $values,
            'errors' => $errors,
            'options' => $this->formOptions(),
            'submitUrl' => $childId === null ? url_to('children.create') : url_to('children.update', $childId),
            'cancelUrl' => $childId === null ? url_to('children.index') : url_to('children.show', $childId),
            'canManage' => $this->authorizationService->userHasRole((int) $user->id, 'admin'),
        ];
    }

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

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

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

        $existing = $childId !== null ? $this->getChildWithRelations($childId) : null;
        $now      = date('Y-m-d H:i:s');

        $guardianPayload = [
            'full_name' => $payload['guardian_name'],
            'phone' => $payload['guardian_phone'] !== '' ? $payload['guardian_phone'] : null,
            'email' => $payload['guardian_email'] !== '' ? $payload['guardian_email'] : null,
            'relationship_type' => 'parent',
            'status' => 'active',
            'notes' => null,
        ];

        $childPayload = [
            'first_name' => $payload['first_name'],
            'last_name' => $payload['last_name'],
            'full_name' => trim($payload['first_name'] . ' ' . $payload['last_name']),
            'birth_date' => $payload['birth_date'] !== '' ? $payload['birth_date'] : null,
            'gender' => $payload['gender'] !== '' ? $payload['gender'] : null,
            'academy_level_id' => $payload['academy_level_id'] !== '' ? (int) $payload['academy_level_id'] : null,
            'academy_group_id' => $payload['academy_group_id'] !== '' ? (int) $payload['academy_group_id'] : null,
            'primary_coach_id' => $payload['primary_coach_id'] !== '' ? (int) $payload['primary_coach_id'] : null,
            'first_class_at' => $payload['first_class_at'] !== '' ? $payload['first_class_at'] : null,
            'status' => $payload['status'],
            'has_siblings' => (int) $payload['has_siblings'],
            'notes' => $payload['notes'] !== '' ? $payload['notes'] : null,
            'updated_at' => $now,
        ];

        $db = $this->db();
        $db->transStart();

        $guardianModel = model(GuardianModel::class);
        $childModel    = model(ChildModel::class);

        if ($existing !== null && ! empty($existing['primary_guardian_id'])) {
            $guardianId = (int) $existing['primary_guardian_id'];
            $guardianModel->update($guardianId, $guardianPayload);
        } else {
            $guardianPayload['created_at'] = $now;
            $guardianModel->insert($guardianPayload);
            $guardianId = (int) $guardianModel->getInsertID();
        }

        $childPayload['primary_guardian_id'] = $guardianId;

        if ($existing === null) {
            $childPayload['created_at'] = $now;
            $childModel->insert($childPayload);
            $childId = (int) $childModel->getInsertID();
        } else {
            $childModel->update($childId, $childPayload);
        }

        $this->syncChildGuardian((int) $childId, $guardianId, $now);
        $this->syncPrimaryCoach((int) $childId, $childPayload['primary_coach_id'], $now);
        $this->recordJourneyEvents((int) $childId, $existing, $childPayload, (int) $user->id, $now);

        $db->transComplete();

        if (! $db->transStatus()) {
            return [
                'success' => false,
                'errors' => ['general' => 'Nu am putut salva copilul. Verifica datele si incearca din nou.'],
            ];
        }

        return [
            'success' => true,
            'child_id' => (int) $childId,
        ];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getProfileData(int $childId, User $user, string $tab = 'overview'): ?array
    {
        $child = $this->getAccessibleChild($childId, $user);
        if ($child === null) {
            return null;
        }

        $evaluations     = $this->getEvaluations($childId);
        $quickChecks     = array_values(array_filter($evaluations, static fn (array $row): bool => $row['type_slug'] === 'quick-check'));
        $fullEvaluations = array_values(array_filter($evaluations, static fn (array $row): bool => $row['type_slug'] === 'full-evaluation'));
        $attendanceRows  = $this->getAttendanceRows($childId);
        $journeyRows     = $this->getJourneyRows($childId);
        $latestAny       = $evaluations[0] ?? null;
        $latestFull      = $fullEvaluations[0] ?? $latestAny;
        $scoreAverage    = $this->averageFinalScore($evaluations);
        $attendanceRate  = $this->calculateAttendanceRate($attendanceRows);
        $criteriaProfile = $latestFull !== null ? $this->getCriteriaProfile((int) $latestFull['id']) : ['labels' => [], 'series' => []];
        $scoreTrend      = $this->buildScoreTrend($evaluations);

        return [
            'pageTitle' => $child['full_name'],
            'pageDescription' => 'Profil individual cu progres, prezenta, quick checks si journey complet.',
            'child' => $child,
            'tabs' => $this->profileTabs($childId, $tab, count($fullEvaluations), count($quickChecks), count($attendanceRows), count($journeyRows)),
            'activeTab' => $tab,
            'canManage' => $this->authorizationService->userHasRole((int) $user->id, 'admin'),
            'canEvaluate' => $this->authorizationService->userHasRole((int) $user->id, ['admin', 'coach']),
            'evaluationActions' => [
                'full' => url_to('evaluations.new', $childId, 'full-evaluation'),
                'quick' => url_to('evaluations.new', $childId, 'quick-check'),
            ],
            'overview' => [
                'average_score' => $scoreAverage,
                'attendance_rate' => $attendanceRate,
                'evaluations_count' => count($evaluations),
                'latest_status' => $latestFull['final_status'] ?? $child['status'],
                'latest_evaluation' => $latestAny,
                'score_trend' => $scoreTrend,
                'criteria_profile' => $criteriaProfile,
            ],
            'fullEvaluations' => $fullEvaluations,
            'quickChecks' => $quickChecks,
            'attendanceRows' => $attendanceRows,
            'journeyRows' => $journeyRows,
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, mixed>
     */
    private function buildIndexFilters(array $input, int $userId): array
    {
        $primaryRole = $this->authorizationService->getPrimaryRole($userId);
        $coachId     = $primaryRole === 'coach' ? $this->authorizationService->getCoachIdForUser($userId) : null;

        $selectedTrainer = $coachId ?: (int) ($input['coach_id'] ?? 0);
        $selectedLevel = (int) ($input['level_id'] ?? 0);
        $selectedGroup = (int) ($input['group_id'] ?? 0);
        $selectedStatus = (string) ($input['status'] ?? 'all');
        $query = trim((string) ($input['q'] ?? ''));

        return [
            'current_role' => $primaryRole,
            'coach_locked' => $primaryRole === 'coach',
            'selected_coach_id' => $selectedTrainer,
            'selected_level_id' => $selectedLevel,
            'selected_group_id' => $selectedGroup,
            'selected_status' => in_array($selectedStatus, ['all', 'active', 'pause', 'left'], true) ? $selectedStatus : 'all',
            'query' => $query,
            'coach_options' => $this->coachOptions(),
            'level_options' => $this->levelOptions(),
            'group_options' => $this->groupOptions($coachId),
            'status_options' => [
                'all' => 'Toate statusurile',
                'active' => 'Activ',
                'pause' => 'Pauza',
                'left' => 'Plecat',
            ],
        ];
    }

    /**
     * @param array<string, mixed> $filters
     * @return list<array<string, mixed>>
     */
    private function getChildrenRows(array $filters): array
    {
        $builder = $this->db()
            ->table('children c')
            ->select('c.*, levels.name as level_name, levels.color_hex, groups.name as group_name, groups.code as group_code, coaches.full_name as coach_name, guardians.full_name as guardian_name')
            ->join('academy_levels levels', 'levels.id = c.academy_level_id', 'left')
            ->join('academy_groups groups', 'groups.id = c.academy_group_id', 'left')
            ->join('coaches', 'coaches.id = c.primary_coach_id', 'left')
            ->join('parents guardians', 'guardians.id = c.primary_guardian_id', 'left')
            ->orderBy('c.full_name', 'ASC');

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

        if ($filters['selected_level_id'] > 0) {
            $builder->where('c.academy_level_id', $filters['selected_level_id']);
        }

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

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

        if ($filters['query'] !== '') {
            $builder
                ->groupStart()
                ->like('c.full_name', $filters['query'])
                ->orLike('guardians.full_name', $filters['query'])
                ->orLike('groups.name', $filters['query'])
                ->groupEnd();
        }

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

    /**
     * @param list<int> $childIds
     * @return array<string, array<int, mixed>>
     */
    private function buildChildMetricMaps(array $childIds): array
    {
        if ($childIds === []) {
            return ['scores' => [], 'attendance' => []];
        }

        $scoreRows = $this->db()
            ->table('evaluations')
            ->select('child_id, ROUND(AVG(final_score), 2) as avg_score, COUNT(*) as evaluations_count')
            ->whereIn('child_id', $childIds)
            ->groupBy('child_id')
            ->get()
            ->getResultArray();

        $attendanceRows = $this->db()
            ->table('attendance_records ar')
            ->select("ar.child_id, SUM(CASE WHEN ar.status IN ('present', 'recovery') THEN 1 ELSE 0 END) as attended, COUNT(*) as total", false)
            ->join('attendance_sessions sessions', 'sessions.id = ar.attendance_session_id')
            ->where('sessions.is_cancelled', 0)
            ->whereIn('ar.child_id', $childIds)
            ->groupBy('ar.child_id')
            ->get()
            ->getResultArray();

        $scores = [];
        foreach ($scoreRows as $row) {
            $scores[(int) $row['child_id']] = [
                'avg_score' => $row['avg_score'] !== null ? (float) $row['avg_score'] : null,
                'evaluations_count' => (int) $row['evaluations_count'],
            ];
        }

        $attendance = [];
        foreach ($attendanceRows as $row) {
            $attendance[(int) $row['child_id']] = $this->attendanceService->calculateRate((int) $row['attended'], (int) $row['total']);
        }

        return ['scores' => $scores, 'attendance' => $attendance];
    }

    /**
     * @return array<string, list<array<string, mixed>>>
     */
    private function formOptions(): array
    {
        return [
            'genders' => [
                ['value' => 'male', 'label' => 'Baiat'],
                ['value' => 'female', 'label' => 'Fata'],
            ],
            'statuses' => [
                ['value' => 'active', 'label' => 'Activ'],
                ['value' => 'pause', 'label' => 'Pauza'],
                ['value' => 'left', 'label' => 'Plecat'],
            ],
            'levels' => $this->levelOptions(),
            'groups' => $this->groupOptions(),
            'coaches' => $this->coachOptions(),
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, string>
     */
    private function normalizeChildPayload(array $input): array
    {
        return [
            'first_name' => trim((string) ($input['first_name'] ?? '')),
            'last_name' => trim((string) ($input['last_name'] ?? '')),
            'birth_date' => trim((string) ($input['birth_date'] ?? '')),
            'gender' => trim((string) ($input['gender'] ?? '')),
            'academy_level_id' => trim((string) ($input['academy_level_id'] ?? '')),
            'academy_group_id' => trim((string) ($input['academy_group_id'] ?? '')),
            'primary_coach_id' => trim((string) ($input['primary_coach_id'] ?? '')),
            'first_class_at' => trim((string) ($input['first_class_at'] ?? '')),
            'status' => trim((string) ($input['status'] ?? 'active')),
            'has_siblings' => (string) ((int) ($input['has_siblings'] ?? 0)),
            'notes' => trim((string) ($input['notes'] ?? '')),
            'guardian_name' => trim((string) ($input['guardian_name'] ?? '')),
            'guardian_phone' => trim((string) ($input['guardian_phone'] ?? '')),
            'guardian_email' => trim((string) ($input['guardian_email'] ?? '')),
        ];
    }

    private function syncChildGuardian(int $childId, int $guardianId, string $now): void
    {
        $pivot = model(ChildGuardianModel::class)
            ->where('child_id', $childId)
            ->where('parent_id', $guardianId)
            ->first();

        $this->db()->table('child_guardians')
            ->where('child_id', $childId)
            ->update(['is_primary' => 0]);

        if ($pivot === null) {
            model(ChildGuardianModel::class)->insert([
                'child_id' => $childId,
                'parent_id' => $guardianId,
                'relationship_type' => 'parent',
                'is_primary' => 1,
                'created_at' => $now,
            ]);

            return;
        }

        model(ChildGuardianModel::class)->update($pivot['id'], ['is_primary' => 1]);
    }

    private function syncPrimaryCoach(int $childId, mixed $coachId, string $now): void
    {
        $this->db()->table('child_coaches')
            ->where('child_id', $childId)
            ->update(['is_primary' => 0, 'unassigned_at' => $now]);

        if ($coachId === null || $coachId === '') {
            return;
        }

        $coachId = (int) $coachId;

        $existing = model(ChildCoachModel::class)
            ->where('child_id', $childId)
            ->where('coach_id', $coachId)
            ->first();

        if ($existing === null) {
            model(ChildCoachModel::class)->insert([
                'child_id' => $childId,
                'coach_id' => $coachId,
                'is_primary' => 1,
                'assigned_at' => $now,
                'unassigned_at' => null,
            ]);

            return;
        }

        model(ChildCoachModel::class)->update($existing['id'], [
            'is_primary' => 1,
            'assigned_at' => $existing['assigned_at'] ?: $now,
            'unassigned_at' => null,
        ]);
    }

    /**
     * @param array<string, mixed>|null $existing
     * @param array<string, mixed> $payload
     */
    private function recordJourneyEvents(int $childId, ?array $existing, array $payload, int $userId, string $now): void
    {
        if ($existing === null) {
            $this->journeyService->recordEvent([
                'child_id' => $childId,
                'event_type' => 'child_created',
                'title' => 'Copil adaugat in academie',
                'description' => 'Profilul copilului a fost creat si asignat in structura academiei.',
                'metadata' => json_encode([
                    'level_id' => $payload['academy_level_id'],
                    'group_id' => $payload['academy_group_id'],
                    'coach_id' => $payload['primary_coach_id'],
                ]),
                'created_by' => $userId,
                'created_at' => $now,
            ]);

            return;
        }

        if ((int) ($existing['academy_level_id'] ?? 0) !== (int) ($payload['academy_level_id'] ?? 0)) {
            $this->journeyService->recordEvent([
                'child_id' => $childId,
                'event_type' => 'level_changed',
                'title' => 'Nivel actualizat',
                'description' => 'Nivelul copilului a fost modificat in profil.',
                'metadata' => json_encode([
                    'from' => $existing['academy_level_id'],
                    'to' => $payload['academy_level_id'],
                ]),
                'created_by' => $userId,
                'created_at' => $now,
            ]);
        }

        if ((int) ($existing['primary_coach_id'] ?? 0) !== (int) ($payload['primary_coach_id'] ?? 0)) {
            $this->journeyService->recordEvent([
                'child_id' => $childId,
                'event_type' => 'coach_changed',
                'title' => 'Trainer principal schimbat',
                'description' => 'Copilul a fost realocat catre un alt trainer principal.',
                'metadata' => json_encode([
                    'from' => $existing['primary_coach_id'],
                    'to' => $payload['primary_coach_id'],
                ]),
                'created_by' => $userId,
                'created_at' => $now,
            ]);
        }

        if ((string) ($existing['status'] ?? '') !== (string) $payload['status']) {
            $this->journeyService->recordEvent([
                'child_id' => $childId,
                'event_type' => 'status_changed',
                'title' => 'Status copil actualizat',
                'description' => 'Statusul operational al copilului a fost schimbat.',
                'metadata' => json_encode([
                    'from' => $existing['status'],
                    'to' => $payload['status'],
                ]),
                'created_by' => $userId,
                'created_at' => $now,
            ]);
        }
    }

    /**
     * @return array<string, mixed>|null
     */
    private function getAccessibleChild(int $childId, User $user): ?array
    {
        $child = $this->getChildWithRelations($childId);
        if ($child === null) {
            return null;
        }

        if (! $this->authorizationService->userHasRole((int) $user->id, 'coach')) {
            return $child;
        }

        $coachId = $this->authorizationService->getCoachIdForUser((int) $user->id);

        if ($coachId !== null && (int) $child['primary_coach_id'] === $coachId) {
            return $child;
        }

        if ($this->authorizationService->userHasRole((int) $user->id, 'admin')) {
            return $child;
        }

        return null;
    }

    /**
     * @return array<string, mixed>|null
     */
    private function getChildWithRelations(int $childId): ?array
    {
        return $this->db()
            ->table('children c')
            ->select('c.*, levels.name as level_name, levels.color_hex, groups.name as group_name, groups.code as group_code, coaches.full_name as coach_name, guardians.full_name as guardian_name, guardians.phone as guardian_phone, guardians.email as guardian_email')
            ->join('academy_levels levels', 'levels.id = c.academy_level_id', 'left')
            ->join('academy_groups groups', 'groups.id = c.academy_group_id', 'left')
            ->join('coaches', 'coaches.id = c.primary_coach_id', 'left')
            ->join('parents guardians', 'guardians.id = c.primary_guardian_id', 'left')
            ->where('c.id', $childId)
            ->get()
            ->getRowArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function getEvaluations(int $childId): array
    {
        return $this->db()
            ->table('evaluations e')
            ->select('e.*, types.name as type_name, types.slug as type_slug, coaches.full_name as evaluator_name')
            ->join('evaluation_types types', 'types.id = e.evaluation_type_id')
            ->join('coaches', 'coaches.id = e.evaluator_coach_id', 'left')
            ->where('e.child_id', $childId)
            ->orderBy('e.evaluation_date', 'DESC')
            ->orderBy('e.id', 'DESC')
            ->get()
            ->getResultArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function getAttendanceRows(int $childId): array
    {
        return $this->db()
            ->table('attendance_records ar')
            ->select('ar.status, ar.notes, sessions.session_date, sessions.start_time, sessions.end_time, groups.name as group_name, coaches.full_name as coach_name')
            ->join('attendance_sessions sessions', 'sessions.id = ar.attendance_session_id')
            ->join('academy_groups groups', 'groups.id = sessions.academy_group_id', 'left')
            ->join('coaches', 'coaches.id = sessions.coach_id', 'left')
            ->where('ar.child_id', $childId)
            ->where('sessions.is_cancelled', 0)
            ->orderBy('sessions.session_date', 'DESC')
            ->limit(20)
            ->get()
            ->getResultArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function getJourneyRows(int $childId): array
    {
        return $this->db()
            ->table('journey_events')
            ->where('child_id', $childId)
            ->orderBy('created_at', 'DESC')
            ->limit(30)
            ->get()
            ->getResultArray();
    }

    /**
     * @return array<string, mixed>
     */
    private function getCriteriaProfile(int $evaluationId): array
    {
        $rows = $this->db()
            ->table('evaluation_scores scores')
            ->select('criteria.name, scores.score')
            ->join('evaluation_criteria criteria', 'criteria.id = scores.evaluation_criteria_id')
            ->where('scores.evaluation_id', $evaluationId)
            ->orderBy('criteria.sort_order', 'ASC')
            ->get()
            ->getResultArray();

        return [
            'labels' => array_map(static fn (array $row): string => $row['name'], $rows),
            'series' => array_map(static fn (array $row): float => (float) $row['score'], $rows),
        ];
    }

    /**
     * @param list<array<string, mixed>> $evaluations
     * @return array<string, mixed>
     */
    private function buildScoreTrend(array $evaluations): array
    {
        $ordered = array_reverse($evaluations);

        return [
            'categories' => array_map(static fn (array $row): string => date('d M', strtotime((string) $row['evaluation_date'])), $ordered),
            'series' => array_map(static fn (array $row): float => (float) $row['final_score'], $ordered),
        ];
    }

    /**
     * @param list<array<string, mixed>> $evaluations
     */
    private function averageFinalScore(array $evaluations): ?float
    {
        if ($evaluations === []) {
            return null;
        }

        $total = array_sum(array_map(static fn (array $row): float => (float) $row['final_score'], $evaluations));

        return round($total / count($evaluations), 2);
    }

    /**
     * @param list<array<string, mixed>> $rows
     */
    private function calculateAttendanceRate(array $rows): float
    {
        $breakdown = ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0];

        foreach ($rows as $row) {
            $status = (string) $row['status'];
            if (! isset($breakdown[$status])) {
                $breakdown[$status] = 0;
            }

            $breakdown[$status]++;
        }

        return $this->attendanceService->calculateRateFromBreakdown($breakdown);
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function profileTabs(int $childId, string $activeTab, int $fullCount, int $quickCount, int $attendanceCount, int $journeyCount): array
    {
        return [
            ['key' => 'overview', 'label' => 'Privire generala', 'href' => url_to('children.show', $childId), 'count' => null, 'active' => $activeTab === 'overview'],
            ['key' => 'evaluations', 'label' => 'Evaluari', 'href' => url_to('children.show', $childId) . '?tab=evaluations', 'count' => $fullCount, 'active' => $activeTab === 'evaluations'],
            ['key' => 'quick-checks', 'label' => 'Quick Check', 'href' => url_to('children.show', $childId) . '?tab=quick-checks', 'count' => $quickCount, 'active' => $activeTab === 'quick-checks'],
            ['key' => 'attendance', 'label' => 'Prezenta', 'href' => url_to('children.show', $childId) . '?tab=attendance', 'count' => $attendanceCount, 'active' => $activeTab === 'attendance'],
            ['key' => 'journey', 'label' => 'Journey', 'href' => url_to('children.show', $childId) . '?tab=journey', 'count' => $journeyCount, 'active' => $activeTab === 'journey'],
        ];
    }

    /**
     * @param int|null $coachId
     * @return list<array<string, mixed>>
     */
    private function groupOptions(?int $coachId = null): array
    {
        $builder = $this->db()
            ->table('academy_groups')
            ->select('id, name, code')
            ->orderBy('name', 'ASC');

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

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

    /**
     * @return list<array<string, mixed>>
     */
    private function levelOptions(): array
    {
        return $this->db()
            ->table('academy_levels')
            ->select('id, name, slug, color_hex')
            ->orderBy('sort_order', 'ASC')
            ->get()
            ->getResultArray();
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function coachOptions(): array
    {
        return $this->db()
            ->table('coaches')
            ->select('id, full_name')
            ->where('is_active', 1)
            ->orderBy('full_name', 'ASC')
            ->get()
            ->getResultArray();
    }

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