<?php

namespace App\Services\Scores;

use App\Models\ClassSection;
use App\Models\Configuration;
use App\Models\GradingLock;
use App\Models\MissingScoreOverride;
use App\Models\ScoreComment;
use App\Models\SemesterScore;
use App\Models\Student;
use App\Models\StudentClass;
use App\Models\TeacherClass;
use Illuminate\Support\Facades\DB;

class ScoreDashboardService
{
    public function __construct(private ScoreTermService $term)
    {
    }

    public function overview(int $teacherId, ?int $classSectionId, ?string $semester, ?string $schoolYear): array
    {
        $schoolYear = $this->term->schoolYear($schoolYear);
        $semesterLabel = $this->term->semesterLabel($semester, $semester);

        if ($teacherId <= 0 && !empty($classSectionId)) {
            $allowedIds = [(int) $classSectionId];
        } else {
            $allowedIds = TeacherClass::query()
                ->where('teacher_id', $teacherId)
                ->when($schoolYear !== '', fn ($q) => $q->where('school_year', $schoolYear))
                ->pluck('class_section_id')
                ->map(fn ($v) => (int) $v)
                ->filter(fn ($v) => $v > 0)
                ->unique()
                ->values()
                ->all();

            if (empty($allowedIds)) {
                return ['students' => [], 'assignments' => [], 'class_section_id' => null];
            }
        }
        $classSectionId = $classSectionId && in_array($classSectionId, $allowedIds, true) ? $classSectionId : $allowedIds[0];

        $studentQuery = StudentClass::query();
        if (DB::connection()->getDriverName() === 'sqlite') {
            $studentQuery->whereRaw('class_section_id = ' . (int) $classSectionId);
            if ($schoolYear !== '') {
                $quoted = DB::connection()->getPdo()->quote($schoolYear);
                $studentQuery->whereRaw("school_year = {$quoted}");
            }
        } else {
            $studentQuery->where('class_section_id', $classSectionId);
            if ($schoolYear !== '') {
                $studentQuery->where('school_year', $schoolYear);
            }
        }

        $studentIds = $studentQuery
            ->pluck('student_id')
            ->map(fn ($v) => (int) $v)
            ->all();
        if (empty($studentIds) && DB::connection()->getDriverName() === 'sqlite') {
            $schoolYearClause = $schoolYear !== '' ? " and school_year = " . DB::connection()->getPdo()->quote($schoolYear) : '';
            $rows = DB::select(
                "select student_id from student_class where class_section_id = " . (int) $classSectionId . $schoolYearClause
            );
            $studentIds = array_values(array_filter(array_map(
                static fn ($row) => isset($row->student_id) ? (int) $row->student_id : 0,
                $rows
            ), static fn ($id) => $id > 0));
        }

        $students = Student::query()
            ->whereIn('id', $studentIds)
            ->where('is_active', 1)
            ->orderBy('lastname')
            ->orderBy('firstname')
            ->get()
            ->map(fn ($row) => [
                'student_id' => (int) $row->id,
                'firstname' => (string) $row->firstname,
                'lastname' => (string) $row->lastname,
                'school_id' => $row->school_id,
            ])
            ->all();

        $scores = SemesterScore::query()
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semesterLabel)
            ->where('school_year', $schoolYear)
            ->get()
            ->keyBy('student_id');

        $comments = ScoreComment::query()
            ->where('semester', $semesterLabel)
            ->where('school_year', $schoolYear)
            ->whereIn('student_id', $studentIds)
            ->get();

        $commentMap = [];
        foreach ($comments as $comment) {
            $commentMap[(int) $comment->student_id][(string) $comment->score_type] = [
                'comment' => $comment->comment,
                'comment_review' => $comment->comment_review,
            ];
        }

        foreach ($students as &$student) {
            $sid = $student['student_id'];
            $scoreRow = $scores[$sid] ?? null;
            $student['scores'] = $scoreRow ? [
                'homework' => $this->normalizeScore($scoreRow->homework_avg),
                'quiz' => $this->normalizeScore($scoreRow->quiz_avg),
                'project' => $this->normalizeScore($scoreRow->project_avg),
                'midterm_exam' => $this->normalizeScore($scoreRow->midterm_exam_score),
                'final_exam' => $this->normalizeScore($scoreRow->final_exam_score),
                'attendance' => $this->normalizeScore($scoreRow->attendance_score),
                'ptap' => $this->normalizeScore($scoreRow->ptap_score),
                'semester' => $this->normalizeScore($scoreRow->semester_score),
            ] : [];
            $student['comments'] = $commentMap[$sid] ?? [];
        }
        unset($student);

        $classSection = ClassSection::query()->where('class_section_id', $classSectionId)->first();

        return [
            'students' => $students,
            'class_section_id' => $classSectionId,
            'class_section_name' => $classSection?->class_section_name,
            'semester' => $semesterLabel,
            'school_year' => $schoolYear,
            'scoresLocked' => GradingLock::isLocked($classSectionId, $semesterLabel, $schoolYear),
        ];
    }

    public function submitScoresLock(int $classSectionId, string $semester, string $schoolYear, array $missingOk, int $teacherId): void
    {
        $isFall = strtolower($semester) === 'fall';
        $isSpring = strtolower($semester) === 'spring';
        $studentIds = array_keys((array) $missingOk);

        $commentTypes = ['ptap_comment'];
        if ($isFall) {
            $commentTypes[] = 'midterm_comment';
        }
        if ($isSpring) {
            $commentTypes[] = 'final_comment';
        }

        foreach ($commentTypes as $type) {
            $checkedItems = [];
            foreach ($missingOk as $studentId => $types) {
                if (!is_array($types) || empty($types[$type])) {
                    continue;
                }
                $checkedItems[] = [
                    'student_id' => (int) $studentId,
                    'item_index' => null,
                ];
            }
            MissingScoreOverride::replaceOverrides(
                $classSectionId,
                $semester,
                $schoolYear,
                $type,
                $studentIds,
                null,
                $checkedItems,
                $teacherId,
                true
            );
        }

        $existing = GradingLock::getLock($classSectionId, $semester, $schoolYear);
        if ($existing && $existing->is_locked) {
            return;
        }

        if ($existing) {
            $existing->update([
                'is_locked' => 1,
                'locked_by' => $teacherId > 0 ? $teacherId : null,
                'locked_at' => now(),
            ]);
        } else {
            GradingLock::query()->create([
                'class_section_id' => $classSectionId,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'is_locked' => 1,
                'locked_by' => $teacherId > 0 ? $teacherId : null,
                'locked_at' => now(),
            ]);
        }
    }

    public function viewStudentScores(int $parentId, string $schoolYear): array
    {
        $students = DB::table('students')
            ->select('students.id', 'students.school_id', 'students.firstname', 'students.lastname', 'classSection.class_section_name')
            ->leftJoin('student_class', 'student_class.student_id', '=', 'students.id')
            ->leftJoin('classSection', 'classSection.class_section_id', '=', 'student_class.class_section_id')
            ->where('students.parent_id', $parentId)
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $scores = [];
        if (!empty($students)) {
            $studentIdList = array_column($students, 'id');
            $semesterScores = DB::table('semester_scores')
                ->whereIn('student_id', $studentIdList)
                ->where('school_year', $schoolYear)
                ->get()
                ->map(fn ($row) => (array) $row)
                ->all();

            $scoresByStudent = [];
            foreach ($semesterScores as $score) {
                $studentIdKey = (int) ($score['student_id'] ?? 0);
                $semesterName = trim((string) ($score['semester'] ?? ''));
                if ($studentIdKey === 0 || $semesterName === '') {
                    continue;
                }
                $normalizedSemester = ucfirst(strtolower($semesterName));
                $scoresByStudent[$studentIdKey][$normalizedSemester] = $score;
            }

            foreach ($students as $student) {
                $studentId = (int) $student['id'];
                if (empty($scoresByStudent[$studentId])) {
                    continue;
                }
                foreach ($scoresByStudent[$studentId] as $semester => $score) {
                    $scores[$schoolYear][$semester][$studentId] = [
                        'student_id' => $studentId,
                        'school_id' => $student['school_id'],
                        'student_firstname' => $student['firstname'],
                        'student_lastname' => $student['lastname'],
                        'class_section_name' => $student['class_section_name'] ?? 'Unknown Class',
                        'comment' => $score['comment'] ?? null,
                        'scores' => [
                            'homework' => ['score' => $score['homework_avg'] ?? '-'],
                            'quiz' => ['score' => $score['quiz_avg'] ?? '-'],
                            'project' => ['score' => $score['project_avg'] ?? '-'],
                            'attendance' => ['score' => $score['attendance_score'] ?? '-'],
                            'participation_score' => ['score' => $score['participation_score'] ?? '-'],
                            'ptap' => ['score' => $score['ptap_score'] ?? '-'],
                            'midterm_exam' => ['score' => $score['midterm_exam_score'] ?? '-'],
                            'final_exam' => ['score' => $score['final_exam_score'] ?? '-'],
                            'semester' => ['score' => $score['semester_score'] ?? '-'],
                        ],
                    ];
                }
            }
        }

        $schoolYears = DB::table('semester_scores')->select('school_year')->distinct()->pluck('school_year')->all();
        rsort($schoolYears);

        return [
            'scores' => $scores,
            'schoolYears' => $schoolYears,
            'selectedYear' => $schoolYear,
        ];
    }

    private function normalizeScore(mixed $value): mixed
    {
        if ($value === null || $value === '') {
            return $value;
        }

        return is_numeric($value) ? (float) $value : $value;
    }
}
