<?php

namespace App\Services;

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

final class EducationWorkQueue
{
    /** @return array{actor_id:int,drafts:int|null,demands:array<string,int>,capacities:array<string,int>} */
    public function summary(User $actor): array
    {
        app(EducationAccess::class)->authorize($actor);

        return ['actor_id' => $actor->id, 'drafts' => app(EducationDeclarationDrafts::class)->installed() ? DB::table('education_declaration_drafts')->where('owner_user_id', $actor->id)->where('state', 'draft')->count() : null,
            'demands' => $this->counts('labour_demands', $actor), 'capacities' => $this->counts('training_capacities', $actor)];
    }

    /** @return array<string,int> */
    private function counts(string $table, User $actor): array
    {
        $today = now()->toDateString();
        $id = $actor->id;
        $row = DB::table($table)->whereNull('deleted_at')->selectRaw("count(*) FILTER(WHERE submitted_by=?) AS total,
            count(*) FILTER(WHERE submitted_by=? AND valid_until>=? AND status='submitted') AS submitted,
            count(*) FILTER(WHERE submitted_by=? AND valid_until>=? AND status='reviewed') AS reviewed,
            count(*) FILTER(WHERE submitted_by=? AND valid_until>=? AND status='rejected') AS rejected,
            count(*) FILTER(WHERE submitted_by=? AND valid_until<?) AS expired,
            count(*) FILTER(WHERE submitted_by<>? AND valid_until>=? AND status='submitted') AS to_review", [$id, $id, $today, $id, $today, $id, $today, $id, $today, $id, $today])->first();
        if ($row === null) {
            throw new \LogicException('Education aggregate result missing.');
        }
        $result = [];
        foreach (['total', 'submitted', 'reviewed', 'rejected', 'expired', 'to_review'] as $key) {
            $result[$key] = (int) $row->$key;
        }

        return $result;
    }
}