119 lines
7.6 KiB
PHP
119 lines
7.6 KiB
PHP
<?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);
|
|
}
|
|
});
|
|
}
|
|
}
|