<?php

namespace App\Services;

use App\Models\Equipment;
use App\Models\EquipmentSparePart;
use App\Models\SparePart;
use App\Models\StockItem;
use App\Models\User;
use App\Models\WarehouseRequest;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/** Read-only workdesk over the existing request, event and reservation journals. */
class WarehouseWorkdesk
{
    /** @param array<string,mixed> $filters
     * @return Builder<WarehouseRequest>
     */
    public function filteredBase(User $actor, array $filters): Builder
    {
        $base = $this->visible($actor);
        if (! empty($filters['warehouse_id'])) {
            $base->where('warehouse_id', $filters['warehouse_id']);
        }
        if (! empty($filters['search'])) {
            $search = trim($filters['search']);
            if (preg_match('/^(?:RA-)?([1-9][0-9]{0,9})$/iD', $search, $match)) {
                $base->whereKey($match[1]);
            } else {
                $base->where(fn ($q) => $q->whereLike('snapshot->requester->name', '%'.$search.'%')->orWhereLike('snapshot->destination', '%'.$search.'%'));
            }
        }

        return $base;
    }

    public function canView(User $actor, WarehouseRequest $request): bool
    {
        return $actor->tenant_id === $request->tenant_id && (
            ($request->created_by === $actor->id && $actor->hasFunctions(['warehouse_request_prepare']))
            || ($actor->hasFunctions(['warehouse_request_review']) && $actor->department_id !== null && $actor->department_id === $request->snapshot['requester']['department_id'])
            || ($actor->hasFunctions(['warehouse_request_deliver']) && $actor->hasFunctions(['warehouse']))
            || $actor->hasFunctions(['admin', 'warehouse_request_read']));
    }

    /** @return Builder<WarehouseRequest> */
    public function visible(User $actor): Builder
    {
        $query = WarehouseRequest::query();
        if (! $actor->hasFunctions(['admin', 'warehouse_request_read']) && ! ($actor->hasFunctions(['warehouse_request_deliver']) && $actor->hasFunctions(['warehouse']))) {
            $query->where(function ($q) use ($actor) {
                $q->where('created_by', $actor->id);
                if ($actor->hasFunctions(['warehouse_request_review']) && $actor->department_id !== null) {
                    $q->orWhere('snapshot->requester->department_id', $actor->department_id);
                }
            });
        }

        return $query;
    }

    /** @param literal-string $action
     * @return literal-string
     */
    private function eventCount(string $action): string
    {
        if (! Schema::hasTable('warehouse_request_events')) {
            return '0';
        }

        return "(SELECT COUNT(*) FROM warehouse_request_events e WHERE e.tenant_id=warehouse_requests.tenant_id AND e.warehouse_request_id=warehouse_requests.id AND e.action='$action')";
    }

    /** SQL built solely from fixed identifiers/actions; filters use bound parameters.
     * @return literal-string
     */
    public function stateExpression(): string
    {
        if (! Schema::hasTable('warehouse_request_events')) {
            return "'draft'";
        }
        $requested = $this->quantityExpression(false);
        $delivered = $this->quantityExpression(true);
        $deliveries = $this->eventCount('deliver');
        $receipts = $this->eventCount('receive');

        return "CASE WHEN {$this->eventCount('cancel')}>0 THEN 'cancelled' WHEN {$this->eventCount('reject')}>0 THEN 'rejected' WHEN {$this->eventCount('authorize')}>0 THEN CASE WHEN $delivered >= $requested THEN CASE WHEN $deliveries=$receipts THEN 'closed' ELSE 'delivered' END WHEN $deliveries>0 THEN 'partial' ELSE 'authorized' END WHEN {$this->eventCount('submit')}>0 THEN 'submitted' ELSE 'draft' END";
    }

    /** @return literal-string */
    private function quantityExpression(bool $delivery): string
    {
        $source = $delivery ? 'e.data' : 'warehouse_requests.snapshot';
        if (DB::getDriverName() === 'pgsql') {
            $json = "json_array_elements($source->'lines') j(value)";
            $quantity = "CAST(j.value->>'quantity_milli' AS BIGINT)";
        } elseif (DB::getDriverName() === 'sqlite') {
            $json = "json_each(json_extract($source, '$.lines')) j";
            $quantity = "CAST(json_extract(j.value, '$.quantity_milli') AS INTEGER)";
        } else {
            throw new \LogicException('A projecção de requisições suporta PostgreSQL e SQLite.');
        }

        return $delivery ? "(SELECT COALESCE(SUM($quantity),0) FROM warehouse_request_events e CROSS JOIN $json WHERE e.tenant_id=warehouse_requests.tenant_id AND e.warehouse_request_id=warehouse_requests.id AND e.action='deliver')" : "(SELECT COALESCE(SUM($quantity),0) FROM $json)";
    }

    /** @param Builder<WarehouseRequest> $query
     * @return Builder<WarehouseRequest>
     */
    public function forView(Builder $query, User $actor, string $view): Builder
    {
        if ($view === 'all') {
            return $query;
        }
        if (! Schema::hasTable('warehouse_request_events')) {
            return $query->whereRaw('1=0');
        }
        $maintenance = app(OrganizationAccess::class)->maintenanceOnly($actor);
        if ($maintenance) {
            return $query->whereRaw('1=0');
        }
        $state = $this->stateExpression();
        if (in_array($view, ['review', 'reserve'], true)) {
            if (! $actor->hasFunctions(['warehouse_request_review']) || $actor->department_id === null) {
                return $query->whereRaw('1=0');
            }

            return $query->where('created_by', '!=', $actor->id)->where('snapshot->requester->department_id', $actor->department_id)->whereRaw($state.($view === 'review' ? " = 'submitted'" : " IN ('authorized','partial')"));
        }
        if ($view === 'delivery') {
            if (! $actor->hasFunctions(['warehouse_request_deliver']) || ! $actor->hasFunctions(['warehouse'])) {
                return $query->whereRaw('1=0');
            }

            return $query->where('created_by', '!=', $actor->id)->whereRaw("$state IN ('authorized','partial')")->whereNotExists(fn ($q) => $q->selectRaw('1')->from('warehouse_request_events as decision')->whereColumn('decision.tenant_id', 'warehouse_requests.tenant_id')->whereColumn('decision.warehouse_request_id', 'warehouse_requests.id')->where('decision.action', 'authorize')->where('decision.actor_id', $actor->id));
        }
        if (! $actor->hasFunctions(['warehouse_request_prepare'])) {
            return $query->whereRaw('1=0');
        }

        return $query->where('created_by', $actor->id)->whereRaw($this->eventCount('deliver').'>'.$this->eventCount('receive'));
    }

    /** @param Builder<WarehouseRequest> $base
     * @return list<array{key:string,label:string,total:int}>
     */
    public function views(Builder $base, User $actor): array
    {
        $views = ['all' => 'Todos no meu âmbito'];
        if (! app(OrganizationAccess::class)->maintenanceOnly($actor)) {
            if ($actor->hasFunctions(['warehouse_request_review'])) {
                $views += ['review' => 'Para autorizar', 'reserve' => 'Reservas a acompanhar'];
            }
            if ($actor->hasFunctions(['warehouse_request_deliver']) && $actor->hasFunctions(['warehouse'])) {
                $views['delivery'] = 'Entregas pendentes';
            }
            if ($actor->hasFunctions(['warehouse_request_prepare'])) {
                $views['receipt'] = 'Recepções por confirmar';
            }
        }
        $rows = [];
        foreach ($views as $key => $label) {
            $rows[] = ['key' => $key, 'label' => $label, 'total' => $this->forView(clone $base, $actor, $key)->count()];
        }

        return $rows;
    }

    /** @param array<string,mixed> $p
     * @return array<string,bool>
     */
    public function actions(User $actor, WarehouseRequest $r, array $p): array
    {
        $active = ! app(OrganizationAccess::class)->maintenanceOnly($actor);
        $owner = $r->created_by === $actor->id;
        $prepare = $actor->hasFunctions(['warehouse_request_prepare']);
        $review = $actor->hasFunctions(['warehouse_request_review']) && $actor->department_id !== null && $actor->department_id === $r->snapshot['requester']['department_id'] && ! $owner;
        $open = in_array($p['state'], ['authorized', 'partial'], true);

        return ['canSubmit' => $active && $owner && $prepare && $p['state'] === 'draft', 'canReview' => $active && $review && $p['state'] === 'submitted',
            'canDeliver' => $active && ! $owner && $p['reviewer_id'] !== $actor->id && $actor->hasFunctions(['warehouse_request_deliver']) && $actor->hasFunctions(['warehouse']) && $open,
            'canManageReservation' => $active && $review && $open, 'canReceive' => $active && $owner && $prepare];
    }

    /** @param array<string,mixed> $p
     * @return array<string,mixed>
     */
    public function health(WarehouseRequest $r, array $p): array
    {
        $rows = [];
        $needsReserve = false;
        $needsCheck = false;
        $readyLines = 0;
        $open = in_array($p['state'], ['authorized', 'partial'], true);
        foreach ($r->snapshot['lines'] as $index => $line) {
            $item = StockItem::query()->whereKey($line['item']['id'])->first();
            $sourceChanged = $item === null || $item->only(['id', 'code', 'name', 'unit']) !== $line['item'];
            if ($line['equipment'] !== null) {
                $equipment = Equipment::query()->whereKey($line['equipment']['id'])->first();
                $sourceChanged = $sourceChanged || $equipment === null || $equipment->only(['id', 'code', 'description']) !== $line['equipment'] || $equipment->department_id !== $r->snapshot['requester']['department_id'];
            }
            $part = SparePart::query()->where('stock_item_id', $line['item']['id'])->first();
            if ($part !== null && ($line['equipment'] === null || ! EquipmentSparePart::query()->where('spare_part_id', $part->id)->where('equipment_id', $line['equipment']['id'])->exists())) {
                $sourceChanged = true;
            }
            $gap = $open ? max(0, $p['remaining'][$index] - $p['reserved'][$index]) : 0;
            $issue = null;
            $ready = false;
            if ($open) {
                $lots = app(InventoryLots::class)->context($line['item']['id'], $r->warehouse_id);
                $usable = $lots === null ? app(StockWorkflow::class)->balance($line['item']['id'], $r->warehouse_id)['quantity'] : ($lots['reconciled'] ? $lots['usable_milli'] : null);
                $commitment = app(WarehouseReservations::class)->committed($line['item']['id'], $r->warehouse_id);
                if ($sourceChanged) {
                    $issue = 'O cadastro ou destino mudou; é necessária revisão da origem.';
                } elseif ($usable === null) {
                    $issue = 'Reconciliar lotes antes de actualizar a reserva ou entregar.';
                } elseif ($p['reserved'][$index] > 0 && $usable < $commitment) {
                    $issue = 'O artigo tem material comprometido indisponível; conferir qualidade, validade e reservas.';
                } elseif (! $p['reservation_ready']) {
                    $issue = 'Reserva física por instalar.';
                } elseif ($gap > 0) {
                    $issue = 'Saldo ainda sem reserva; rever reposição e actualizar a reserva autorizadamente.';
                }
                $ready = ! $sourceChanged && $usable !== null && $usable >= $commitment && $p['reserved'][$index] > 0;
                $needsReserve = $needsReserve || $gap > 0;
                $needsCheck = $needsCheck || $sourceChanged || $usable === null || ($p['reserved'][$index] > 0 && $usable < $commitment);
            }
            if ($ready) {
                $readyLines++;
            }
            $rows[] = ['index' => $index, 'unreserved_milli' => $gap, 'attention' => $issue, 'source_changed' => $sourceChanged, 'ready' => $ready];
        }
        $unreceived = count(array_filter($p['events'], fn ($e) => $e['action'] === 'deliver' && ! in_array($e['id'], $p['received_deliveries'], true)));

        return ['lines' => $rows, 'needs_reservation' => $needsReserve, 'needs_check' => $needsCheck, 'unconfirmed_deliveries' => $unreceived,
            'delivery_ready' => $open && ! in_array(true, array_column($rows, 'source_changed'), true) && ($p['partial_allowed'] ? $readyLines > 0 : ($readyLines === count($rows) && ! $needsReserve))];
    }

    /** Current eligible people, not assignments or new grants.
     * @param  array<string,mixed>  $p
     * @return array<string,list<array{id:int,name:string}>>
     */
    public function people(WarehouseRequest $r, array $p): array
    {
        $result = ['requester' => [], 'review' => [], 'delivery' => []];
        foreach (User::query()->where('tenant_id', $r->tenant_id)->orderBy('name')->get(['id', 'tenant_id', 'name', 'department_id', 'purchasing_role']) as $user) {
            if (app(OrganizationAccess::class)->maintenanceOnly($user)) {
                continue;
            }
            $context = app(WorkContext::class)->enforced($user) ? 'procurement.internal' : null;
            $permissions = app(OrganizationAccess::class)->permissions($user, $context);
            if ($context !== null && ! in_array($context, app(WorkContext::class)->available($user), true)) {
                continue;
            }
            $person = $user->only(['id', 'name']);
            if ($user->id === $r->created_by && in_array('warehouse_request_prepare', $permissions, true)) {
                $result['requester'][] = $person;
            }
            if ($user->id !== $r->created_by && $user->department_id === $r->snapshot['requester']['department_id'] && in_array('warehouse_request_review', $permissions, true)) {
                $result['review'][] = $person;
            }
            if ($user->id !== $r->created_by && $user->id !== $p['reviewer_id'] && in_array('warehouse_request_deliver', $permissions, true) && in_array('warehouse', $permissions, true)) {
                $result['delivery'][] = $person;
            }
        }

        return $result;
    }
}