<?php

namespace App\Services\Scores;

use Illuminate\Support\Facades\DB;

class ScorePredictorDataService
{
    public function classSections(string $schoolYear): array
    {
        return DB::table('classSection as cs')
            ->select('cs.*')
            ->join('student_class as sc', 'sc.class_section_id', '=', 'cs.class_section_id')
            ->where('sc.school_year', $schoolYear)
            ->where('cs.class_section_name', 'not like', 'KG%')
            ->groupBy('cs.id')
            ->orderBy('cs.class_section_name', 'ASC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();
    }

    public function students(string $schoolYear, ?int $classSectionId = null): array
    {
        $builder = DB::table('students as s')
            ->select([
                's.id as student_id',
                's.school_id',
                's.firstname',
                's.lastname',
                'fall.semester_score as fall_score',
                'spring.semester_score as spring_score',
                'sc.class_section_id as class_section_id',
            ])
            ->leftJoin('student_class as sc', function ($join) use ($schoolYear) {
                $join->on('sc.student_id', '=', 's.id')
                    ->where('sc.school_year', '=', $schoolYear);
            })
            ->leftJoin('classSection as cs', 'cs.class_section_id', '=', 'sc.class_section_id')
            ->leftJoin('semester_scores as fall', function ($join) use ($schoolYear) {
                $join->on('fall.student_id', '=', 's.id')
                    ->where('fall.semester', '=', 'fall')
                    ->where('fall.school_year', '=', $schoolYear);
            })
            ->leftJoin('semester_scores as spring', function ($join) use ($schoolYear) {
                $join->on('spring.student_id', '=', 's.id')
                    ->where('spring.semester', '=', 'spring')
                    ->where('spring.school_year', '=', $schoolYear);
            })
            ->where('s.is_active', 1)
            ->where(function ($q) {
                $q->whereNull('cs.class_section_name')
                    ->orWhere('cs.class_section_name', 'not like', 'KG%');
            });

        if (!empty($classSectionId)) {
            $builder->where('sc.class_section_id', $classSectionId);
        }

        return $builder
            ->groupBy('s.id')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();
    }

    public function springStats(string $schoolYear, ?int $classSectionId = null): array
    {
        $builder = DB::table('semester_scores')
            ->select('semester_scores.semester_score')
            ->join('student_class', 'student_class.student_id', '=', 'semester_scores.student_id')
            ->leftJoin('classSection as cs', 'cs.class_section_id', '=', 'student_class.class_section_id')
            ->where('semester_scores.semester', 'spring')
            ->where('semester_scores.school_year', $schoolYear)
            ->where('student_class.school_year', $schoolYear)
            ->where(function ($q) {
                $q->whereNull('cs.class_section_name')
                    ->orWhere('cs.class_section_name', 'not like', 'KG%');
            });

        if (!empty($classSectionId)) {
            $builder->where('student_class.class_section_id', $classSectionId);
        }

        $scores = $builder->pluck('semester_scores.semester_score')->filter(function ($v) {
            return is_numeric($v);
        })->map(fn ($v) => (float) $v)->all();

        if (empty($scores)) {
            return ['mean' => 70.0, 'std' => 0.0];
        }

        $mean = array_sum($scores) / count($scores);
        $variance = 0.0;
        foreach ($scores as $value) {
            $variance += ($value - $mean) ** 2;
        }
        $variance = $variance / count($scores);

        return [
            'mean' => (float) $mean,
            'std' => (float) sqrt($variance),
        ];
    }
}
