<?php

namespace App\Services\CompetitionScores;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CompetitionScoresRosterService
{
    private string $classStudentTable;

    public function __construct()
    {
        if (Schema::hasTable('class_student')) {
            $this->classStudentTable = 'class_student';
        } elseif (Schema::hasTable('student_class')) {
            $this->classStudentTable = 'student_class';
        } else {
            $this->classStudentTable = '';
        }
    }

    public function getClassStudentCount(int $classSectionId, ?string $schoolYear): int
    {
        if ($classSectionId <= 0 || $this->classStudentTable === '') {
            return 0;
        }

        $builder = DB::table($this->classStudentTable)
            ->selectRaw('COUNT(*) AS total')
            ->where('class_section_id', $classSectionId);

        if ($schoolYear && Schema::hasColumn($this->classStudentTable, 'school_year')) {
            $builder->where('school_year', $schoolYear);
        }

        $row = (array) $builder->first();
        return (int) ($row['total'] ?? 0);
    }

    public function getStudentsForCompetition(array $competition, int $classSectionId): array
    {
        if ($classSectionId <= 0 || $this->classStudentTable === '') {
            return [];
        }

        $builder = DB::table('students as s')
            ->select('s.id', 's.school_id', 's.firstname', 's.lastname')
            ->join($this->classStudentTable . ' as cs', 'cs.student_id', '=', 's.id')
            ->where('cs.class_section_id', $classSectionId);

        if (Schema::hasColumn($this->classStudentTable, 'school_year')) {
            $schoolYear = (string) ($competition['school_year'] ?? '');
            if ($schoolYear !== '') {
                $builder->where('cs.school_year', $schoolYear);
            }
        }

        return $builder
            ->distinct()
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();
    }
}
