nala
f8d57b52aa
Regista aceitacao funcional do primeiro incremento PGC-NIRF e preserva as dependencias documentais e tecnicas existentes. A validacao do calculo fiscal continua separada e pendente. Sem credenciais, bases operacionais ou publicacao.
69 lines
6.2 KiB
PHP
69 lines
6.2 KiB
PHP
<?php
|
|
|
|
use Illuminate\Database\Migrations\Migration;
|
|
use Illuminate\Database\Schema\Blueprint;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
return new class extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
Schema::create('doc_records', function (Blueprint $table): void {
|
|
$table->id(); $table->unsignedBigInteger('tenant_id'); $table->unsignedBigInteger('company_id'); $table->unsignedBigInteger('unit_id')->nullable();
|
|
$table->string('kind', 30); $table->unsignedBigInteger('record_id'); $table->unsignedInteger('version'); $table->jsonb('data'); $table->timestampsTz();
|
|
$table->foreign(['tenant_id','company_id'])->references(['tenant_id','id'])->on('companies')->restrictOnDelete();
|
|
$table->foreign(['tenant_id','company_id','unit_id'])->references(['tenant_id','company_id','id'])->on('units')->restrictOnDelete();
|
|
});
|
|
DB::statement("CREATE UNIQUE INDEX doc_record_identity ON doc_records(company_id,kind,record_id) WHERE kind <> 'payment'");
|
|
DB::statement("ALTER TABLE doc_records ADD CHECK (version > 0 AND jsonb_typeof(data) = 'object' AND ((kind IN ('issuer','client','payment') AND unit_id IS NULL) OR (kind IN ('service_drafts','billing_drafts') AND unit_id IS NOT NULL)))");
|
|
Schema::create('doc_history', function (Blueprint $table): void {
|
|
$table->id(); $table->foreignId('doc_record_id')->constrained('doc_records')->restrictOnDelete(); $table->unsignedInteger('version');
|
|
$table->foreignId('actor_id')->constrained('users')->restrictOnDelete(); $table->jsonb('snapshot'); $table->timestampTz('created_at');
|
|
$table->unique(['doc_record_id','version']);
|
|
});
|
|
DB::unprepared(<<<'SQL'
|
|
CREATE OR REPLACE FUNCTION sgf_doc_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE item record; target text; instruction jsonb;
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN RAISE EXCEPTION 'SGF_DOC_NO_DELETE'; END IF;
|
|
IF TG_OP = 'INSERT' AND NEW.version <> 1 THEN RAISE EXCEPTION 'SGF_DOC_INITIAL_VERSION'; END IF;
|
|
IF TG_OP = 'UPDATE' THEN
|
|
IF ROW(NEW.id,NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.kind,NEW.record_id) IS DISTINCT FROM ROW(OLD.id,OLD.tenant_id,OLD.company_id,OLD.unit_id,OLD.kind,OLD.record_id) THEN RAISE EXCEPTION 'SGF_DOC_IMMUTABLE_SCOPE'; END IF;
|
|
IF NEW.version <> OLD.version + 1 THEN RAISE EXCEPTION 'SGF_DOC_VERSION'; END IF;
|
|
END IF;
|
|
IF NEW.kind = 'issuer' AND NEW.record_id <> NEW.company_id THEN RAISE EXCEPTION 'SGF_DOC_ISSUER'; END IF;
|
|
IF NEW.kind = 'client' AND NOT EXISTS (SELECT 1 FROM a_clients WHERE id=NEW.record_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id) THEN RAISE EXCEPTION 'SGF_DOC_CLIENT'; END IF;
|
|
IF NEW.kind = 'payment' AND NEW.record_id <> 0 THEN RAISE EXCEPTION 'SGF_DOC_PAYMENT'; END IF;
|
|
IF NEW.kind IN ('service_drafts','billing_drafts') THEN
|
|
target := 'a_' || NEW.kind;
|
|
EXECUTE format('SELECT tenant_id,company_id,unit_id,version FROM %I WHERE id=$1',target) INTO item USING NEW.record_id;
|
|
IF item IS NULL OR ROW(item.tenant_id,item.company_id,item.unit_id) IS DISTINCT FROM ROW(NEW.tenant_id,NEW.company_id,NEW.unit_id) THEN RAISE EXCEPTION 'SGF_DOC_DRAFT_SCOPE'; END IF;
|
|
IF NEW.kind = 'billing_drafts' AND EXISTS (SELECT 1 FROM b_internal_confirmations WHERE billing_draft_id=NEW.record_id) THEN RAISE EXCEPTION 'SGF_DOC_CONFIRMED_IMMUTABLE'; END IF;
|
|
IF (NEW.data->>'draft_version')::integer IS DISTINCT FROM item.version THEN RAISE EXCEPTION 'SGF_DOC_STALE_DRAFT'; END IF;
|
|
IF NEW.data->>'price_mode' NOT IN ('undefined','included','added') THEN RAISE EXCEPTION 'SGF_DOC_PRICE_MODE'; END IF;
|
|
FOR instruction IN SELECT * FROM jsonb_array_elements(NEW.data->'payment_instruction_ids') LOOP
|
|
IF NOT EXISTS (SELECT 1 FROM doc_records WHERE id=instruction::text::bigint AND kind='payment' AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id) THEN RAISE EXCEPTION 'SGF_DOC_FOREIGN_PAYMENT'; END IF;
|
|
END LOOP;
|
|
END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE OR REPLACE FUNCTION sgf_doc_history_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM doc_records WHERE id=NEW.doc_record_id AND version=NEW.version AND data=NEW.snapshot) THEN RAISE EXCEPTION 'SGF_DOC_HISTORY_VERSION'; END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER doc_record_guard BEFORE INSERT OR UPDATE OR DELETE ON doc_records FOR EACH ROW EXECUTE FUNCTION sgf_doc_guard();
|
|
CREATE TRIGGER doc_history_guard BEFORE INSERT ON doc_history FOR EACH ROW EXECUTE FUNCTION sgf_doc_history_guard();
|
|
CREATE TRIGGER doc_history_immutable BEFORE UPDATE OR DELETE ON doc_history FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
|
|
SQL);
|
|
$company = ['company.view','company.edit','unit.create','access.manage','issuer_documents.view','issuer_documents.edit','client_documents.view','client_documents.edit'];
|
|
$unit = ['unit.view','unit.edit','billing_drafts.confirm_internal','draft_documents.view','draft_documents.edit'];
|
|
foreach (['clients','services','service_drafts','billing_drafts'] as $area) { foreach (['view','create','edit','manage'] as $action) { if (in_array($area, ['clients','services'], true)) { $company[] = "$area.$action"; } else { $unit[] = "$area.$action"; } } }
|
|
$companySql = "'".implode("','", $company)."'"; $unitSql = "'".implode("','", $unit)."'";
|
|
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 ((permission IN ($companySql) AND unit_id IS NULL) OR (permission IN ($unitSql) AND unit_id IS NOT NULL))");
|
|
}
|
|
public function down(): void { throw new RuntimeException('Document preparation contains historical data; use a separately reviewed preservation procedure.'); }
|
|
};
|