<?php

namespace App\Services\Students;

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

class StudentScoreCardService
{
    public function scoreCard(int $studentId): array
    {
        $student = Student::query()
            ->select('id', 'firstname', 'lastname', 'school_id')
            ->where('id', $studentId)
            ->first();

        if (!$student) {
            return ['ok' => false, 'message' => 'Student not found.'];
        }

        $rows = DB::table('semester_scores as ss')
            ->select([
                'ss.school_year',
                'ss.semester',
                'ss.homework_avg',
                'ss.project_avg',
                'ss.participation_score',
                'ss.quiz_avg',
                'ss.test_avg',
                'ss.attendance_score',
                'ss.ptap_score',
                'ss.midterm_exam_score',
                'ss.semester_score',
                'cs.class_section_name',
            ])
            ->leftJoin('classSection as cs', 'cs.class_section_id', '=', 'ss.class_section_id')
            ->where('ss.student_id', $studentId)
            ->get()
            ->map(fn ($r) => (array) $r)
            ->all();

        usort($rows, static function (array $a, array $b): int {
            $ay = (string) ($a['school_year'] ?? '');
            $by = (string) ($b['school_year'] ?? '');
            if ($ay !== $by) {
                return strcmp($by, $ay);
            }
            return strcmp((string) ($a['semester'] ?? ''), (string) ($b['semester'] ?? ''));
        });

        $yearScoreMap = [];
        foreach ($rows as $row) {
            $year = (string) ($row['school_year'] ?? '');
            $score = $row['semester_score'] ?? null;
            if ($year === '' || !is_numeric($score)) {
                continue;
            }
            $yearScoreMap[$year][] = (float) $score;
        }

        $yearAvg = [];
        foreach ($yearScoreMap as $year => $scores) {
            $yearAvg[$year] = round(array_sum($scores) / count($scores), 1);
        }

        $commentRows = DB::table('score_comments')
            ->select('school_year', 'semester', 'score_type', 'comment', 'comment_review')
            ->where('student_id', $studentId)
            ->orderByDesc('created_at')
            ->get()
            ->map(fn ($r) => (array) $r)
            ->all();

        $commentMap = [];
        foreach ($commentRows as $row) {
            $year = (string) ($row['school_year'] ?? '');
            $semester = strtolower(trim((string) ($row['semester'] ?? '')));
            $type = strtolower(trim((string) ($row['score_type'] ?? '')));
            if ($year === '' || $type === '') {
                continue;
            }
            if ($type === 'attendance_comment') {
                $type = 'attendance';
            }
            $commentVal = trim((string) ($row['comment'] ?? ''));
            $reviewVal = trim((string) ($row['comment_review'] ?? ''));
            if (in_array($type, ['midterm', 'final', 'ptap'], true)) {
                $commentVal = $reviewVal !== '' ? $reviewVal : $commentVal;
            } elseif ($type === 'attendance' && $commentVal === '' && $reviewVal !== '') {
                $commentVal = $reviewVal;
            }
            if ($commentVal === '') {
                continue;
            }
            $key = $year . '|' . $semester;
            $commentMap[$key][$type] = $commentVal;
        }

        foreach ($rows as &$row) {
            $year = (string) ($row['school_year'] ?? '');
            $semester = strtolower(trim((string) ($row['semester'] ?? '')));
            $row['comments'] = $commentMap[$year . '|' . $semester] ?? [];
            $row['year_score'] = $yearAvg[$year] ?? null;
        }
        unset($row);

        return [
            'ok' => true,
            'student' => $student->toArray(),
            'rows' => $rows,
        ];
    }
}
