<?php

namespace App\Services\Scores;

use App\Models\GradingLock;
use App\Models\MissingScoreOverride;
use App\Models\Student;
use Illuminate\Support\Facades\DB;

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

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

        $latestSub = DB::table($table)
            ->select('student_id', DB::raw('MAX(id) AS max_id'))
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->groupBy('student_id');

        $rows = DB::table('students as s')
            ->select([
                's.id as student_id',
                's.school_id',
                's.firstname',
                's.lastname',
                'ex.score',
            ])
            ->join('student_class as sc', function ($join) use ($classSectionId, $schoolYear) {
                $join->on('sc.student_id', '=', 's.id')
                    ->where('sc.class_section_id', '=', $classSectionId)
                    ->where('sc.school_year', '=', $schoolYear);
            })
            ->leftJoinSub($latestSub, 'latest', 'latest.student_id', '=', 's.id')
            ->leftJoin($table . ' as ex', 'ex.id', '=', 'latest.max_id')
            ->orderBy('s.lastname')
            ->orderBy('s.firstname')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        return [
            'students' => $rows,
            'semester' => $semester,
            'schoolYear' => $schoolYear,
            'scoresLocked' => GradingLock::isLocked($classSectionId, $semester, $schoolYear),
            'missingOkMap' => MissingScoreOverride::getOverridesMap(
                $classSectionId,
                $semester,
                $schoolYear,
                $table === 'midterm_exam' ? 'midterm_exam' : 'final_exam'
            ),
        ];
    }

    public function update(string $table, 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);
        $checkedItems = [];
        foreach ($missingOk as $studentId => $flags) {
            $checkedItems[] = [
                'student_id' => (int) $studentId,
                'item_index' => null,
            ];
        }

        MissingScoreOverride::replaceOverrides(
            $classSectionId,
            $semester,
            $schoolYear,
            $table === 'midterm_exam' ? 'midterm_exam' : 'final_exam',
            $studentIds,
            null,
            $checkedItems,
            $updatedBy,
            true
        );

        $builder = DB::table($table);
        $count = 0;

        foreach ($scores as $studentId => $data) {
            if (!is_numeric($studentId)) {
                continue;
            }
            $student = Student::query()->find((int) $studentId);
            if (!$student) {
                continue;
            }
            $rawScore = $data['score'] ?? null;
            $normalizedScore = is_numeric($rawScore) ? (float) $rawScore : null;

            $existing = $builder
                ->where('student_id', $studentId)
                ->where('class_section_id', $classSectionId)
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->first();

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

            if ($existing) {
                $builder->where('id', $existing->id)->update($payload);
            } else {
                $payload['created_at'] = now();
                $builder->insert($payload);
            }

            $count++;
        }

        return $count;
    }
}
