<?php

namespace App\Services\Scores;

use App\Models\GradingLock;
use App\Models\Homework;
use App\Models\MissingScoreOverride;
use App\Models\Student;
use App\Models\StudentClass;

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

    public function list(int $classSectionId, ?string $semester, ?string $schoolYear): array
    {
        $semester = $this->term->semesterLabel($semester);
        $schoolYear = $this->term->schoolYear($schoolYear);
        $semVariants = $this->term->semesterVariants($semester);
        $schoolYearVariants = $this->term->schoolYearVariants($schoolYear);
        if (empty($schoolYearVariants)) {
            $schoolYearVariants = [$schoolYear];
        }

        $headerRows = Homework::query()
            ->select('homework_index')
            ->where('class_section_id', $classSectionId)
            ->whereIn('semester', $semVariants)
            ->whereIn('school_year', $schoolYearVariants)
            ->orderBy('homework_index')
            ->get();

        $headers = $headerRows->pluck('homework_index')->map(fn ($v) => (int) $v)->unique()->values()->all();

        $studentIds = StudentClass::query()
            ->where('class_section_id', $classSectionId)
            ->whereIn('school_year', $schoolYearVariants)
            ->pluck('student_id')
            ->map(fn ($v) => (int) $v)
            ->all();

        $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,
                'scores' => [],
            ])
            ->all();

        $scoresRows = Homework::query()
            ->select('student_id', 'homework_index', 'score')
            ->where('class_section_id', $classSectionId)
            ->whereIn('semester', $semVariants)
            ->whereIn('school_year', $schoolYearVariants)
            ->get()
            ->map(fn ($row) => $row->toArray())
            ->all();

        $scoresMap = [];
        foreach ($scoresRows as $row) {
            $score = $row['score'] ?? null;
            $scoresMap[(int) $row['student_id']][(int) $row['homework_index']] = is_numeric($score) ? (float) $score : $score;
        }

        foreach ($students as &$student) {
            foreach ($headers as $index) {
                $student['scores'][(int) $index] = $scoresMap[$student['student_id']][$index] ?? '';
            }
        }
        unset($student);

        return [
            'students' => $students,
            'headers' => $headers,
            'semester' => $semester,
            'schoolYear' => $schoolYear,
            'scoresLocked' => $this->isLockedForAnySchoolYear($classSectionId, $semester, $schoolYearVariants),
            'missingOkMap' => $this->getMissingOverridesForAnySchoolYear($classSectionId, $semester, $schoolYearVariants),
        ];
    }

    public function listBySchoolYear(?string $schoolYear): array
    {
        $schoolYear = $this->term->schoolYear($schoolYear);
        $schoolYearVariants = $this->term->schoolYearVariants($schoolYear);
        if (empty($schoolYearVariants)) {
            $schoolYearVariants = [$schoolYear];
        }

        $rows = Homework::query()
            ->select([
                'homework.id',
                'homework.student_id',
                'homework.school_id',
                'homework.class_section_id',
                'homework.updated_by',
                'homework.homework_index',
                'homework.score',
                'homework.comment',
                'homework.semester',
                'homework.school_year',
                'homework.created_at',
                'homework.updated_at',
                's.firstname as student_firstname',
                's.lastname as student_lastname',
            ])
            ->leftJoin('students as s', 's.id', '=', 'homework.student_id')
            ->whereIn('homework.school_year', $schoolYearVariants)
            ->orderBy('homework.class_section_id')
            ->orderBy('s.lastname')
            ->orderBy('s.firstname')
            ->orderBy('homework.homework_index')
            ->get()
            ->map(fn ($row) => $row->toArray())
            ->all();

        return [
            'rows' => $rows,
            'schoolYear' => $schoolYear,
        ];
    }

    private function isLockedForAnySchoolYear(int $classSectionId, string $semester, array $schoolYearVariants): bool
    {
        foreach ($schoolYearVariants as $year) {
            if (GradingLock::isLocked($classSectionId, $semester, $year)) {
                return true;
            }
        }
        return false;
    }

    private function getMissingOverridesForAnySchoolYear(int $classSectionId, string $semester, array $schoolYearVariants): array
    {
        foreach ($schoolYearVariants as $year) {
            $map = MissingScoreOverride::getOverridesMap($classSectionId, $semester, $year, 'homework');
            if (!empty($map)) {
                return $map;
            }
        }
        return [];
    }

    public function update(int $classSectionId, string $semester, string $schoolYear, array $scores, array $missingOk, int $updatedBy): int
    {
        if (GradingLock::isLocked($classSectionId, $semester, $schoolYear)) {
            throw new \RuntimeException('Scores are locked for this class.');
        }

        $studentIds = array_keys($scores);
        $indexSet = [];
        foreach ($scores as $hwData) {
            if (!is_array($hwData)) {
                continue;
            }
            foreach ($hwData as $index => $_) {
                if (is_numeric($index)) {
                    $indexSet[(int) $index] = true;
                }
            }
        }

        $checkedItems = [];
        foreach ($missingOk as $studentId => $indexes) {
            if (!is_array($indexes)) {
                continue;
            }
            foreach ($indexes as $index => $flag) {
                if (!is_numeric($index)) {
                    continue;
                }
                $checkedItems[] = [
                    'student_id' => (int) $studentId,
                    'item_index' => (int) $index,
                ];
            }
        }

        MissingScoreOverride::replaceOverrides(
            $classSectionId,
            $semester,
            $schoolYear,
            'homework',
            $studentIds,
            array_keys($indexSet),
            $checkedItems,
            $updatedBy
        );

        $count = 0;
        foreach ($scores as $studentId => $hwData) {
            if (!is_numeric($studentId) || !is_array($hwData)) {
                continue;
            }
            $student = Student::query()->find((int) $studentId);
            if (!$student) {
                continue;
            }

            foreach ($hwData as $index => $score) {
                if (!is_numeric($index)) {
                    continue;
                }
                $rawScore = $score ?? null;
                $isBlank = $rawScore === null || (is_string($rawScore) && trim($rawScore) === '');
                $normalizedScore = (!$isBlank && is_numeric($rawScore)) ? (float) $rawScore : null;

                $existing = Homework::query()
                    ->where('student_id', $studentId)
                    ->where('homework_index', $index)
                    ->where('class_section_id', $classSectionId)
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->first();

                $payload = [
                    'student_id' => (int) $studentId,
                    'school_id' => $student->school_id ?? null,
                    'class_section_id' => $classSectionId,
                    'updated_by' => $updatedBy,
                    'homework_index' => (int) $index,
                    'score' => $normalizedScore,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'updated_at' => now(),
                ];

                if ($existing) {
                    $existing->update($payload);
                } else {
                    $payload['created_at'] = now();
                    Homework::query()->create($payload);
                }
                $count++;
            }
        }

        return $count;
    }

    public function addColumn(int $classSectionId, string $semester, string $schoolYear, int $updatedBy): int
    {
        $semVariants = $this->term->semesterVariants($semester);
        $maxIndex = (int) Homework::query()
            ->where('class_section_id', $classSectionId)
            ->whereIn('semester', $semVariants)
            ->where('school_year', $schoolYear)
            ->max('homework_index');

        $nextIndex = $maxIndex + 1;

        $students = StudentClass::query()
            ->where('class_section_id', $classSectionId)
            ->where('school_year', $schoolYear)
            ->get();

        foreach ($students as $student) {
            $studentRow = Student::query()->find((int) $student->student_id);
            if (!$studentRow) {
                continue;
            }
            $exists = Homework::query()
                ->where('student_id', $student->student_id)
                ->where('homework_index', $nextIndex)
                ->where('class_section_id', $classSectionId)
                ->whereIn('semester', $semVariants)
                ->where('school_year', $schoolYear)
                ->first();

            if ($exists) {
                continue;
            }

            Homework::query()->create([
                'student_id' => (int) $student->student_id,
                'school_id' => $studentRow->school_id,
                'class_section_id' => $classSectionId,
                'updated_by' => $updatedBy,
                'homework_index' => $nextIndex,
                'score' => null,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        return $nextIndex;
    }
}
