<?php

namespace App\Services;

use Illuminate\Database\Connection;
use Illuminate\Database\Query\Builder;

class CandidateTerritory
{
    /** Filter only immutable received snapshots; never consult the current candidate profile. */
    public function apply(Builder $query, string $location): void
    {
        $geography = app(AdministrativeGeography::class);
        abort_unless($geography->enabled(), 503);
        $ids = $geography->descendants($location);
        $edition = $geography->catalogue()['manifest']['source_sha256'];
        $connection = $query->getConnection();
        abort_unless($connection instanceof Connection, 503);
        $driver = $connection->getDriverName();
        abort_unless(in_array($driver, ['sqlite', 'pgsql'], true), 503, 'Filtro territorial ainda não disponível nesta base.');
        $parameters = implode(',', array_fill(0, count($ids), '?'));
        if ($driver === 'pgsql') {
            $source = "candidate_submissions.snapshot::jsonb #> '{profile,data,additional_availability_references}'";
            $array = "CASE WHEN jsonb_typeof($source) = 'array' THEN $source ELSE '[]'::jsonb END";
            $additional = "EXISTS (SELECT 1 FROM jsonb_array_elements($array) AS territory(value) WHERE territory.value->>'catalogue' = ? AND territory.value->>'country' = 'MZ' AND territory.value->>'record_id' IN ($parameters))";
        } else {
            $source = "json_extract(candidate_submissions.snapshot, '$.profile.data.additional_availability_references')";
            $array = "CASE WHEN json_type(candidate_submissions.snapshot, '$.profile.data.additional_availability_references') = 'array' THEN $source ELSE '[]' END";
            $object = "CASE WHEN territory.type = 'object' THEN territory.value ELSE '{}' END";
            $additional = "EXISTS (SELECT 1 FROM json_each($array) AS territory WHERE json_extract($object, '$.catalogue') = ? AND json_extract($object, '$.country') = 'MZ' AND json_extract($object, '$.record_id') IN ($parameters))";
        }
        $query->where(function (Builder $group) use ($ids, $edition, $additional) {
            $group->where(fn (Builder $main) => $main->where('candidate_submissions.snapshot->profile->data->availability_reference->catalogue', $edition)
                ->where('candidate_submissions.snapshot->profile->data->availability_reference->country', 'MZ')
                ->whereIn('candidate_submissions.snapshot->profile->data->availability_reference->record_id', $ids))
                ->orWhereRaw($additional, [$edition, ...$ids]);
        });
    }
}