<?php

namespace App\Services\Frontend;

use App\Models\Invoice;
use App\Models\StudentClass;
use Illuminate\Support\Facades\DB;

class LandingPageParentDashboardService
{
    public function __construct(private LandingPageContextService $context)
    {
    }

    public function summary(int $parentUserId, string $userType): array
    {
        $context = $this->context->context();
        $schoolYear = (string) ($context['school_year'] ?? '');
        $semester = (string) ($context['semester'] ?? '');

        $parentId = $this->resolveParentId($parentUserId, $userType);
        if (!$parentId) {
            return [
                'ok' => false,
                'message' => 'Unable to retrieve student data. Please contact support.',
            ];
        }

        $notifications = $this->notificationsForParent($parentId);

        $students = DB::table('students')
            ->where('parent_id', $parentId)
            ->get()
            ->map(function ($row) use ($schoolYear) {
                $student = (array) $row;
                $classSection = StudentClass::getClassSectionsByStudentId($student['id'], $schoolYear);
                $student['class_section'] = $classSection;
                $student['grade'] = $classSection;
                return $student;
            })
            ->all();

        $attendance = DB::table('attendance_data')
            ->select('attendance_data.*', 'students.firstname', 'students.lastname')
            ->join('students', 'students.id', '=', 'attendance_data.student_id')
            ->where('students.parent_id', $parentId)
            ->where('attendance_data.school_year', $schoolYear)
            ->where('attendance_data.semester', $semester)
            ->orderBy('attendance_data.date', 'DESC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $grades = DB::table('final_score')
            ->select('final_score.*', 'students.firstname', 'students.lastname')
            ->join('students', 'students.id', '=', 'final_score.student_id')
            ->where('students.parent_id', $parentId)
            ->where('final_score.school_year', $schoolYear)
            ->where('final_score.semester', $semester)
            ->orderBy('final_score.created_at', 'DESC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $enrollments = DB::table('enrollments')
            ->select('enrollments.*', 'classes.class_name')
            ->join('students', 'students.id', '=', 'enrollments.student_id')
            ->join('classes', 'classes.id', '=', 'enrollments.class_section_id')
            ->where('students.parent_id', $parentId)
            ->where('enrollments.school_year', $schoolYear)
            ->where('enrollments.semester', $semester)
            ->orderBy('enrollments.enrollment_date', 'DESC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $paymentBalance = Invoice::getLatestInvoiceTotalAmount($parentId);

        return [
            'ok' => true,
            'notifications' => $notifications,
            'students' => $students,
            'attendance' => $attendance,
            'grades' => $grades,
            'enrollments' => $enrollments,
            'last_day_of_registration' => $context['last_day_of_registration'] ?? 'Not set',
            'withdrawal_deadline' => $context['refund_deadline'] ?? 'Not set',
            'payment_balance' => $paymentBalance,
        ];
    }

    private function resolveParentId(int $userId, string $userType): ?int
    {
        if ($userType === 'primary') {
            return $userId;
        }

        if ($userType === 'secondary') {
            $row = DB::table('parents')
                ->select('parent_id')
                ->where('secondparent_user_id', $userId)
                ->first();
            return $row ? (int) $row->parent_id : null;
        }

        if ($userType === 'tertiary') {
            $row = DB::table('authorized_users')
                ->select('user_id as parent_id')
                ->where('authorized_user_id', $userId)
                ->first();
            return $row ? (int) $row->parent_id : null;
        }

        return null;
    }

    private function notificationsForParent(int $parentId): array
    {
        return DB::table('notifications')
            ->select([
                'notifications.id',
                'notifications.title',
                'notifications.message',
                'notifications.target_group',
                'notifications.created_at',
                'notifications.expires_at',
                'user_notifications.user_id',
                DB::raw("CASE WHEN user_notifications.user_id IS NOT NULL THEN 'personal' ELSE 'broadcast' END as notification_type"),
            ])
            ->leftJoin('user_notifications', function ($join) use ($parentId) {
                $join->on('user_notifications.notification_id', '=', 'notifications.id')
                    ->where('user_notifications.user_id', '=', $parentId);
            })
            ->where(function ($q) use ($parentId) {
                $q->where('notifications.target_group', 'parent')
                    ->orWhere('user_notifications.user_id', $parentId);
            })
            ->whereNull('notifications.deleted_at')
            ->where(function ($q) {
                $q->whereNull('notifications.expires_at')
                    ->orWhere('notifications.expires_at', '>', now());
            })
            ->orderBy('notifications.created_at', 'DESC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();
    }
}
