<?php

namespace App\Services\CompetitionScores;

use App\Models\Competition;
use App\Models\CompetitionClassWinner;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CompetitionScoresQueryService
{
    public function getCompetitionsForClass(int $classSectionId, string $schoolYear, string $semester): array
    {
        $builder = Competition::query()->orderBy('id', 'DESC');

        if ($classSectionId > 0) {
            $builder->where(function ($q) use ($classSectionId) {
                $q->where('class_section_id', $classSectionId)
                    ->orWhere('class_section_id', 0)
                    ->orWhereNull('class_section_id');
            });
        }

        if ($schoolYear !== '') {
            $builder->where(function ($q) use ($schoolYear) {
                $q->where('school_year', $schoolYear)
                    ->orWhere('school_year', '')
                    ->orWhereNull('school_year');
            });
        }

        if ($semester !== '') {
            $builder->where(function ($q) use ($semester) {
                $q->where('semester', $semester)
                    ->orWhere('semester', '')
                    ->orWhereNull('semester');
            });
        }

        return $builder->get()->map(fn ($row) => (array) $row)->all();
    }

    public function getQuestionCounts(array $competitionIds, int $classSectionId): array
    {
        if (!$this->hasQuestionCount() || empty($competitionIds)) {
            return [];
        }

        $rows = CompetitionClassWinner::query()
            ->select('competition_id', 'question_count')
            ->where('class_section_id', $classSectionId)
            ->whereIn('competition_id', $competitionIds)
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $out = [];
        foreach ($rows as $row) {
            $compId = (int) ($row['competition_id'] ?? 0);
            if ($compId > 0 && $row['question_count'] !== null) {
                $out[$compId] = (int) $row['question_count'];
            }
        }

        return $out;
    }

    public function getScoreCounts(array $competitionIds, int $classSectionId): array
    {
        if (empty($competitionIds)) {
            return [];
        }

        $rows = DB::table('competition_scores')
            ->selectRaw('competition_id, COUNT(*) AS total')
            ->where('class_section_id', $classSectionId)
            ->whereIn('competition_id', $competitionIds)
            ->groupBy('competition_id')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $out = [];
        foreach ($rows as $row) {
            $compId = (int) ($row['competition_id'] ?? 0);
            if ($compId > 0) {
                $out[$compId] = (int) ($row['total'] ?? 0);
            }
        }

        return $out;
    }

    public function getQuestionCountForCompetition(int $competitionId, int $classSectionId): ?int
    {
        if (!$this->hasQuestionCount()) {
            return null;
        }

        $row = CompetitionClassWinner::query()
            ->select('question_count')
            ->where('competition_id', $competitionId)
            ->where('class_section_id', $classSectionId)
            ->first();

        if ($row && $row->question_count !== null) {
            return (int) $row->question_count;
        }

        return null;
    }

    private function hasQuestionCount(): bool
    {
        return Schema::hasTable('competition_class_winners')
            && Schema::hasColumn('competition_class_winners', 'question_count');
    }
}
