<?php

namespace Tempo\Purchasing\SgfCandidate;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Schema\Blueprint;

/** Host-owned additive schema, deliberately outside automatic package installation. */
final class Schema
{
    public static function install(ConnectionInterface $db): void
    {
        $schema = $db->getSchemaBuilder();
        if ($schema->hasTable('ir_requests')) {
            throw new \RuntimeException('Schema já existe; não redefinir dados.');
        }
        $db->transaction(function () use ($schema, $db) {
            if ($db->getDriverName() === 'pgsql') {
                $db->statement('ALTER TABLE permission_grants ALTER COLUMN permission TYPE varchar(100)');
                $check = $db->selectOne("select pg_get_constraintdef(oid) as definition from pg_constraint where conrelid='permission_grants'::regclass and conname='permission_grants_scope_check'");
                if (! $check || ! preg_match('/^CHECK\s*\((.*)\)$/s', $check->definition, $parts)) {
                    throw new \RuntimeException('Conferir o catálogo de permissões antes de migrar.');
                }
                $db->statement('ALTER TABLE permission_grants DROP CONSTRAINT permission_grants_scope_check');
                $db->statement("ALTER TABLE permission_grants ADD CONSTRAINT permission_grants_scope_check CHECK (({$parts[1]}) OR (unit_id IS NOT NULL AND permission::text IN ('purchasing_requisitions.view'::text,'purchasing_requisitions.prepare'::text,'purchasing_requisitions.configure'::text,'purchasing_requisitions.approve.department_head'::text,'purchasing_requisitions.approve.procurement'::text,'purchasing_requisitions.approve.finance'::text)))");
            }
            $schema->create('ir_requests', function (Blueprint $t) {
                $t->id();
                foreach (['tenant_id', 'company_id', 'unit_id', 'requester_id', 'cost_center_id'] as $c) {
                    $t->unsignedBigInteger($c);
                }
                $t->string('work_context', 40)->default('procurement.internal');
                $t->uuid('origin_key');
                $t->string('creation_hash', 64);
                $t->string('number')->nullable();
                $t->string('department_ref', 100);
                $t->string('status')->default('draft');
                $t->unsignedInteger('version')->default(1);
                $t->bigInteger('subtotal_minor');
                $t->bigInteger('tax_minor');
                $t->bigInteger('total_minor');
                $t->jsonb('data');
                $t->jsonb('approval_snapshot')->nullable();
                $t->unsignedBigInteger('budget_origin_id')->nullable();
                $t->jsonb('reservation')->nullable();
                $t->timestamps();
                $t->unique(['tenant_id', 'company_id', 'unit_id', 'origin_key']);
                $t->unique(['id', 'tenant_id', 'company_id', 'unit_id']);
            });
            foreach (['steps', 'events', 'commands', 'policies', 'policy_versions'] as $kind) {
                $schema->create('ir_'.$kind, function (Blueprint $t) use ($kind) {
                    $t->id();
                    foreach (['tenant_id', 'company_id', 'unit_id'] as $c) {
                        $t->unsignedBigInteger($c);
                    }
                    if (in_array($kind, ['steps', 'events'], true)) {
                        $t->unsignedBigInteger('request_id');
                        $t->foreign(['request_id', 'tenant_id', 'company_id', 'unit_id'])->references(['id', 'tenant_id', 'company_id', 'unit_id'])->on('ir_requests')->restrictOnDelete();
                    }
                    if ($kind === 'steps') {
                        $t->unsignedInteger('position');
                        $t->string('role');
                        $t->string('decision')->default('pending');
                        $t->unsignedBigInteger('approver_id')->nullable();
                        $t->text('comment')->nullable();
                        $t->timestamp('decided_at')->nullable();
                        $t->unique(['request_id', 'position']);
                        $t->unique(['request_id', 'approver_id']);
                    } elseif ($kind === 'commands') {
                        $t->unsignedBigInteger('actor_id');
                        $t->uuid('operation_key');
                        $t->string('request_hash', 64);
                        $t->jsonb('result');
                        $t->timestamp('created_at');
                        $t->unique(['tenant_id', 'company_id', 'unit_id', 'actor_id', 'operation_key']);
                    } else {
                        $t->unsignedInteger('version');
                        $t->unsignedBigInteger('actor_id');
                        $t->jsonb('payload');
                        $t->timestamp('created_at');
                        if ($kind === 'events') {
                            $t->string('action');
                            $t->unique(['request_id', 'version']);
                        } else {
                            $t->unique($kind === 'policies' ? ['tenant_id', 'company_id', 'unit_id'] : ['tenant_id', 'company_id', 'unit_id', 'version']);
                        }
                    }
                });
            }
            if ($db->getDriverName() === 'pgsql') {
                $db->statement("ALTER TABLE ir_requests ADD CONSTRAINT ir_internal_context CHECK (work_context='procurement.internal')");
                foreach (['ir_events', 'ir_commands', 'ir_policy_versions'] as $table) {
                    $db->unprepared("CREATE FUNCTION {$table}_immutable() RETURNS trigger LANGUAGE plpgsql AS \$\$ BEGIN RAISE EXCEPTION 'Append-only history'; END; \$\$; CREATE TRIGGER {$table}_immutable BEFORE UPDATE OR DELETE ON {$table} FOR EACH ROW EXECUTE FUNCTION {$table}_immutable();");
                }
                $db->unprepared(<<<'SQL'
CREATE FUNCTION ir_origin_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP = 'DELETE' THEN RAISE EXCEPTION 'Preserve requisition origin'; END IF;
 IF (NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.work_context,NEW.origin_key,NEW.requester_id,NEW.creation_hash) IS DISTINCT FROM (OLD.tenant_id,OLD.company_id,OLD.unit_id,OLD.work_context,OLD.origin_key,OLD.requester_id,OLD.creation_hash) THEN RAISE EXCEPTION 'Immutable origin'; END IF;
 IF OLD.status <> 'draft' AND (NEW.data,NEW.subtotal_minor,NEW.tax_minor,NEW.total_minor,NEW.cost_center_id,NEW.department_ref,NEW.approval_snapshot,NEW.budget_origin_id,NEW.reservation) IS DISTINCT FROM (OLD.data,OLD.subtotal_minor,OLD.tax_minor,OLD.total_minor,OLD.cost_center_id,OLD.department_ref,OLD.approval_snapshot,OLD.budget_origin_id,OLD.reservation) THEN RAISE EXCEPTION 'Preserve submitted snapshot'; END IF;
 IF NEW.version <= OLD.version THEN RAISE EXCEPTION 'Version must advance'; END IF;
 RETURN NEW;
END; $$;
CREATE TRIGGER ir_origin_guard BEFORE UPDATE OR DELETE ON ir_requests FOR EACH ROW EXECUTE FUNCTION ir_origin_guard();
CREATE FUNCTION ir_step_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP = 'DELETE' THEN RAISE EXCEPTION 'Preserve approval steps'; END IF;
 IF OLD.decision <> 'pending' THEN RAISE EXCEPTION 'Final decision immutable'; END IF;
 IF NEW.approver_id = (SELECT requester_id FROM ir_requests WHERE id=NEW.request_id) THEN RAISE EXCEPTION 'Independent identity required'; END IF;
 IF (NEW.request_id,NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.position,NEW.role) IS DISTINCT FROM (OLD.request_id,OLD.tenant_id,OLD.company_id,OLD.unit_id,OLD.position,OLD.role) THEN RAISE EXCEPTION 'Step origin immutable'; END IF;
 RETURN NEW;
END; $$;
CREATE TRIGGER ir_step_guard BEFORE UPDATE OR DELETE ON ir_steps FOR EACH ROW EXECUTE FUNCTION ir_step_guard();
SQL);
            }
        });
    }
}