<?php

namespace App\Services\Settings\SchoolCalendar;

use App\Models\ParentMeetingSchedule;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class SchoolCalendarMeetingService
{
    public function listMeetings(string $schoolYear, ?string $audience, ?int $parentUserId = null): array
    {
        $audience = $audience !== null ? strtolower(trim($audience)) : null;

        $builder = ParentMeetingSchedule::query()
            ->where('school_year', $schoolYear)
            ->where(function (Builder $q) {
                $q->where('status', 'scheduled')
                    ->orWhere('status', 'Scheduled')
                    ->orWhere('status', '')
                    ->orWhereNull('status');
            });

        if ($audience === 'parent' && $parentUserId) {
            $studentIds = $this->getStudentIdsForParent($parentUserId);
            if (! empty($studentIds)) {
                $builder->where(function (Builder $q) use ($parentUserId, $studentIds) {
                    $q->where('parent_user_id', $parentUserId)
                        ->orWhereIn('student_id', $studentIds);
                });
            } else {
                $builder->where('parent_user_id', $parentUserId);
            }
        }

        $rows = $builder->orderBy('date', 'ASC')->get()->toArray();
        if (! empty($rows)) {
            return $rows;
        }

        $fallback = ParentMeetingSchedule::query()
            ->where(function (Builder $q) {
                $q->where('status', 'scheduled')
                    ->orWhere('status', 'Scheduled')
                    ->orWhere('status', '')
                    ->orWhereNull('status');
            });

        if ($audience === 'parent' && $parentUserId) {
            $studentIds = $this->getStudentIdsForParent($parentUserId);
            if (! empty($studentIds)) {
                $fallback->where(function (Builder $q) use ($parentUserId, $studentIds) {
                    $q->where('parent_user_id', $parentUserId)
                        ->orWhereIn('student_id', $studentIds);
                });
            } else {
                $fallback->where('parent_user_id', $parentUserId);
            }
        }

        return $fallback->orderBy('date', 'ASC')->get()->toArray();
    }

    private function getStudentIdsForParent(int $parentUserId): array
    {
        try {
            $rows = DB::table('family_guardians as fg')
                ->select('fs.student_id')
                ->join('family_students as fs', 'fs.family_id', '=', 'fg.family_id')
                ->where('fg.user_id', $parentUserId)
                ->distinct()
                ->get()
                ->map(fn ($r) => (int) $r->student_id)
                ->all();

            $legacyRows = DB::table('students')
                ->select('id as student_id')
                ->where('parent_id', $parentUserId)
                ->get()
                ->map(fn ($r) => (int) $r->student_id)
                ->all();

            return array_values(array_filter(array_unique(array_merge($rows, $legacyRows))));
        } catch (\Throwable $e) {
            return [];
        }
    }
}