<?php

namespace App\Http\Controllers;

use App\Services\TerritorialRegistry;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class TerritorialRegistryController extends Controller
{
    public function sources(Request $request): JsonResponse
    {
        $data = $request->validate(['limit' => ['nullable', 'integer', 'between:1,100'], 'cursor' => ['nullable', 'string', 'max:512']]);

        return response()->json(DB::table('geo_sources')->orderBy('id')->cursorPaginate($data['limit'] ?? 20)->withQueryString());
    }

    public function drafts(Request $request): JsonResponse
    {
        $data = $request->validate(['limit' => ['nullable', 'integer', 'between:1,100'], 'cursor' => ['nullable', 'string', 'max:512']]);

        return response()->json(DB::table('geo_locations as g')->join('geo_territory_profiles as p', 'p.geo_location_id', '=', 'g.id')->whereNull('g.deleted_at')
            ->select(['g.id', 'g.name', 'g.code', 'g.location_type', 'g.parent_id', 'g.version_no', 'p.source_id', 'p.status', 'p.valid_from', 'p.valid_to', 'p.editor_id', 'p.reviewer_id', 'p.publisher_id'])
            ->orderBy('g.id')->cursorPaginate($data['limit'] ?? 20)->withQueryString());
    }

    public function index(Request $request, TerritorialRegistry $registry, ?string $collection = null): JsonResponse
    {
        $data = $request->validate(['q' => ['nullable', 'string', 'max:150'], 'cursor' => ['nullable', 'string', 'max:512'],
            'limit' => ['nullable', 'integer', 'between:1,100'], 'parent_id' => ['nullable', 'uuid'],
            'country_id' => ['nullable', 'uuid'], 'province_id' => ['nullable', 'uuid'], 'district_id' => ['nullable', 'uuid'], 'admin_post_id' => ['nullable', 'uuid'],
            'include_geometry' => ['nullable', 'in:0,false'], 'bbox' => ['prohibited'], 'near' => ['prohibited'], 'radius' => ['prohibited']]);
        $types = ['countries' => 'country', 'provinces' => 'province', 'districts' => 'district', 'admin-posts' => 'admin_post', 'localities' => 'locality'];
        $query = $registry->published()->select(['g.id', 'g.name', 'g.code', 'g.location_type', 'g.parent_id', 'g.version_no', 'p.valid_from', 'p.valid_to']);
        if (isset($types[$collection])) {
            $query->where('g.location_type', $types[$collection]);
        }
        if (! empty($data['q'])) {
            $term = '%'.str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $data['q']).'%';
            $query->where(fn ($q) => $q->where('g.name', 'ilike', $term)->orWhere('g.code', 'ilike', $term)
                ->orWhereExists(fn ($n) => $n->selectRaw('1')->from('geo_names')->whereColumn('geo_location_id', 'g.id')->where('name', 'ilike', $term)));
        }
        foreach (['parent_id', 'country_id', 'province_id', 'district_id', 'admin_post_id'] as $key) {
            if (! empty($data[$key])) {
                if ($key === 'parent_id') {
                    $query->where('g.parent_id', $data[$key]);
                } else {
                    $type = ['country_id' => 'country', 'province_id' => 'province', 'district_id' => 'district', 'admin_post_id' => 'admin_post'][$key];
                    $query->whereRaw('EXISTS (WITH RECURSIVE a AS (SELECT id,parent_id,location_type,1 AS depth FROM geo_locations WHERE id=g.parent_id UNION ALL SELECT l.id,l.parent_id,l.location_type,a.depth+1 FROM geo_locations l JOIN a ON l.id=a.parent_id WHERE a.depth<5) SELECT 1 FROM a WHERE id=? AND location_type=?)', [$data[$key], $type]);
                }
            }
        }

        return response()->json($query->orderBy('g.id')->cursorPaginate($data['limit'] ?? 20)->withQueryString());
    }

    public function show(Request $request, TerritorialRegistry $registry, string $type, string $id): JsonResponse
    {
        abort_unless(array_key_exists($type, TerritorialRegistry::PARENTS), 404);
        $row = $registry->published()->where('g.id', $id)->where('g.location_type', $type)
            ->first(['g.id', 'g.name', 'g.code', 'g.parent_id', 'g.location_type', 'g.version_no', 'p.valid_from', 'p.valid_to', 'p.capital_territory_id']);
        abort_unless($row !== null, 404);
        // An unapproved capital must not leak through a published province.
        if ($row->capital_territory_id && ! $registry->published()->where('g.id', $row->capital_territory_id)->exists()) {
            $row->capital_territory_id = null;
        }

        return response()->json(['territory' => $row, 'names' => DB::table('geo_names')->where('geo_location_id', $id)->get(['language', 'name', 'official'])]);
    }

    public function save(Request $request, TerritorialRegistry $registry, ?string $id = null): JsonResponse
    {
        $id = $registry->save($request->user(), $request->all(), $id);

        return response()->json(['id' => $id, 'status' => 'draft', 'version_no' => DB::table('geo_locations')->where('id', $id)->value('version_no')]);
    }

    public function source(Request $request): JsonResponse
    {
        $data = $request->validate(['name' => ['required', 'string', 'max:255'], 'responsible_entity' => ['nullable', 'string', 'max:255'],
            'reference' => ['required', 'string', 'min:10', 'max:2000'], 'license' => ['nullable', 'string', 'max:2000'],
            'checksum' => ['required', 'regex:/^[0-9a-f]{64}$/'], 'source_version' => ['required', 'string', 'max:255'],
            'collected_at' => ['required', 'date_format:Y-m-d', 'before_or_equal:today'], 'original_crs' => ['nullable', 'string', 'max:255']]);
        $id = (string) Str::uuid();
        DB::transaction(function () use ($data, $id, $request) {
            DB::select('SELECT pg_advisory_xact_lock(280926, hashtext(?))', [$data['checksum'].':'.$data['source_version']]);
            abort_if(DB::table('geo_sources')->where('checksum', $data['checksum'])->where('source_version', $data['source_version'])->exists(), 422, 'Fonte e versão já registadas.');
            DB::table('geo_sources')->insert([...$data, 'id' => $id, 'editor_id' => $request->user()->id, 'created_at' => now(), 'updated_at' => now()]);
        });

        return response()->json(['id' => $id, 'status' => 'pending'], 201);
    }

    public function transition(Request $request, TerritorialRegistry $registry, string $id): JsonResponse
    {
        $data = $request->validate(['action' => ['required', 'in:pending_review,approved,published,rejected,archived'],
            'version_no' => ['required', 'integer', 'min:1'], 'reason' => ['required', 'string', 'min:10', 'max:1000']]);

        return DB::transaction(function () use ($request, $registry, $id, $data) {
            DB::select('SELECT pg_advisory_xact_lock(280926, 150000)');
            $location = DB::table('geo_locations')->where('id', $id)->whereNull('deleted_at')->lockForUpdate()->first();
            $profile = DB::table('geo_territory_profiles')->where('geo_location_id', $id)->lockForUpdate()->first();
            abort_unless($location && $profile, 404);
            abort_unless($location->version_no === $data['version_no'], 409);
            $from = ['pending_review' => ['draft', 'rejected'], 'approved' => ['pending_review'], 'published' => ['approved'],
                'rejected' => ['pending_review'], 'archived' => ['draft', 'pending_review', 'approved', 'published', 'rejected']];
            abort_unless(in_array($profile->status, $from[$data['action']], true), 409);
            $actor = $request->user()->id;
            $changes = ['status' => $data['action'], 'updated_at' => now()];
            if ($data['action'] === 'pending_review') {
                abort_unless($profile->editor_id === $actor, 403);
            }
            if (in_array($data['action'], ['approved', 'rejected'], true)) {
                abort_if($profile->editor_id === $actor, 403, 'Revisão independente obrigatória.');
                $changes['reviewer_id'] = $actor;
            }
            if ($data['action'] === 'published') {
                abort_if(in_array($actor, [$profile->editor_id, $profile->reviewer_id], true), 403, 'Publicação exige terceiro interveniente.');
                abort_unless(DB::table('geo_sources')->where('id', $profile->source_id)->where('status', 'approved')->exists(), 422, 'Fonte não aprovada.');
                abort_if($profile->valid_from > today()->toDateString() || ($profile->valid_to && $profile->valid_to < today()->toDateString()), 422, 'Vigência inválida.');
                abort_if($location->parent_id && ! $registry->published()->where('g.id', $location->parent_id)->exists(), 422, 'Ascendente não publicado.');
                $changes['publisher_id'] = $actor;
            }
            DB::table('geo_territory_profiles')->where('geo_location_id', $id)->update($changes);
            $version = $location->version_no + 1;
            DB::table('geo_locations')->where('id', $id)->update(['version_no' => $version, 'data_classification' => $data['action'] === 'published' ? 'public' : 'internal']);
            $registry->snapshot($id, $version, $request->user(), $data['reason']);

            return response()->json(['id' => $id, 'status' => $data['action'], 'version_no' => $version]);
        });
    }

    public function sourceTransition(Request $request, string $id): JsonResponse
    {
        $data = $request->validate(['action' => ['required', 'in:approved,revoked'], 'approval_reference' => ['required', 'string', 'min:10', 'max:2000']]);

        return DB::transaction(function () use ($request, $id, $data) {
            $source = DB::table('geo_sources')->where('id', $id)->lockForUpdate()->first();
            abort_unless($source !== null, 404);
            if ($data['action'] === 'approved') {
                abort_unless($source->status === 'pending', 409);
                abort_if($source->editor_id === $request->user()->id, 403);
                abort_unless($source->responsible_entity && $source->license, 422, 'Entidade e licença em falta.');
            }
            DB::table('geo_sources')->where('id', $id)->update(['status' => $data['action'], 'reviewer_id' => $request->user()->id,
                'approval_reference' => $data['approval_reference'], 'updated_at' => now()]);
            DB::table('audit_events')->insert(['source_system' => 'territorial-registry', 'entity_type' => 'geo_sources', 'entity_id' => $id,
                'actor_user_id' => $request->user()->id, 'action' => $data['action'], 'after_data' => json_encode(['status' => $data['action']], JSON_THROW_ON_ERROR)]);

            return response()->json(['id' => $id, 'status' => $data['action']]);
        });
    }
}