<?php

namespace App\Services\Scores;

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

class ProjectScoreService
{
    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);

        $headerRows = Project::query()
            ->select('project_index')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->orderBy('project_index')
            ->get();

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

        $studentIds = StudentClass::query()
            ->where('class_section_id', $classSectionId)
            ->where('school_year', $schoolYear)
            ->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 = Project::query()
            ->select('student_id', 'project_index', 'score')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->get()
            ->map(fn ($row) => $row->toArray())
            ->all();

        $scoresMap = [];
        foreach ($scoresRows as $row) {
            $score = $row['score'] ?? null;
            $scoresMap[(int) $row['student_id']][(int) $row['project_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' => GradingLock::isLocked($classSectionId, $semester, $schoolYear),
            'missingOkMap' => MissingScoreOverride::getOverridesMap($classSectionId, $semester, $schoolYear, 'project'),
        ];
    }

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

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

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

    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 $projects) {
            if (!is_array($projects)) {
                continue;
            }
            foreach ($projects 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,
            'project',
            $studentIds,
            array_keys($indexSet),
            $checkedItems,
            $updatedBy
        );

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

            foreach ($projects as $index => $score) {
                if (!is_numeric($index)) {
                    continue;
                }
                $normalizedScore = is_numeric($score) ? (float) $score : null;

                $existing = Project::query()
                    ->where('student_id', $studentId)
                    ->where('project_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,
                    'class_section_id' => $classSectionId,
                    'updated_by' => $updatedBy,
                    'project_index' => (int) $index,
                    'score' => $normalizedScore,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'updated_at' => now(),
                ];

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

        return $count;
    }

    public function addColumn(int $classSectionId, string $semester, string $schoolYear, int $updatedBy): int
    {
        $maxIndex = (int) Project::query()
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->max('project_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 = Project::query()
                ->where('student_id', $student->student_id)
                ->where('project_index', $nextIndex)
                ->where('class_section_id', $classSectionId)
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->first();

            if ($exists) {
                continue;
            }

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

        return $nextIndex;
    }
}
