<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Application;
use App\Models\Intake;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    /** Aggregate admissions reporting (PRD 76). */
    public function index(Request $request): View
    {
        $intakeId = $request->integer('intake') ?: Intake::current()?->id;

        return view('admin.reports.index', [
            'intakes' => Intake::orderByDesc('year')->get(['id', 'name']),
            'selectedIntake' => $intakeId,
            'byStatus' => $this->applications($intakeId)
                ->selectRaw('status, count(*) as total')
                ->groupBy('status')
                ->pluck('total', 'status'),
            'byGender' => $this->byApplicantField($intakeId, 'gender'),
            'byNationality' => $this->byApplicantField($intakeId, 'nationality', 10),
            'byDistrict' => $this->byApplicantField($intakeId, 'district', 10),
            'bySchool' => $this->byFirstChoice($intakeId, 'schools.name'),
            'byProgramme' => $this->byFirstChoice($intakeId, 'programmes.name', 15),
        ]);
    }

    protected function applications(?int $intakeId): Builder
    {
        return Application::query()
            ->submitted()
            ->when($intakeId, fn (Builder $q) => $q->where('intake_id', $intakeId));
    }

    /** Demographic breakdown from the applicant record. */
    protected function byApplicantField(?int $intakeId, string $field, ?int $limit = null)
    {
        return $this->applications($intakeId)
            ->join('applicants', 'applicants.id', '=', 'applications.applicant_id')
            ->selectRaw("coalesce(nullif(applicants.{$field}, ''), 'Not stated') as label, count(*) as total")
            ->groupBy('label')
            ->orderByDesc('total')
            ->when($limit, fn ($q) => $q->limit($limit))
            ->pluck('total', 'label');
    }

    /** Demand measured by first-choice programme. */
    protected function byFirstChoice(?int $intakeId, string $column, ?int $limit = null)
    {
        return DB::table('application_choices')
            ->join('applications', 'applications.id', '=', 'application_choices.application_id')
            ->join('programmes', 'programmes.id', '=', 'application_choices.programme_id')
            ->join('schools', 'schools.id', '=', 'programmes.school_id')
            ->where('application_choices.choice_number', 1)
            ->whereNotNull('applications.submitted_at')
            ->when($intakeId, fn ($q) => $q->where('applications.intake_id', $intakeId))
            ->selectRaw("{$column} as label, count(*) as total")
            ->groupBy($column)
            ->orderByDesc('total')
            ->when($limit, fn ($q) => $q->limit($limit))
            ->pluck('total', 'label');
    }
}
