Investidor-V2/app/Support/SkillsEducationMigration.php

<?php

namespace App\Support;

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

abstract class SkillsEducationMigration extends Migration
{
    protected int $block;

    /** @var list<string> */
    protected array $tables;

    public function up(): void
    {
        if (DB::getDriverName() !== 'pgsql') {
            throw new \RuntimeException('PostgreSQL 17 with existing PostGIS/pgcrypto required.');
        }
        DB::select("SELECT pg_advisory_xact_lock(hashtextextended('skills_edu:migrations',0))");
        DB::statement("SET LOCAL TIME ZONE 'UTC'");
        $version = (int) DB::selectOne("SELECT current_setting('server_version_num') AS n")->n;
        if ($version < 170000 || $version >= 180000 || DB::table('pg_extension')->whereIn('extname', ['pgcrypto', 'postgis'])->count() !== 2) {
            throw new \RuntimeException('Expected PostgreSQL 17 and extensions managed by existing migrations.');
        }
        foreach (['users' => 'bigint', 'sectors' => 'bigint', 'skills' => 'uuid', 'occupations' => 'uuid', 'courses' => 'uuid', 'training_providers' => 'uuid',
            'organizations' => 'uuid', 'projects' => 'uuid', 'economic_clusters' => 'uuid', 'development_corridors' => 'uuid', 'investment_zones' => 'uuid',
            'geo_locations' => 'uuid', 'labour_demands' => 'uuid', 'education_investment_proposals' => 'uuid', 'documents' => 'uuid', 'consents' => 'uuid'] as $table => $type) {
            $actual = DB::selectOne("SELECT format_type(atttypid,atttypmod) AS type FROM pg_attribute WHERE attrelid=to_regclass(?) AND attname='id' AND NOT attisdropped", ['public.'.$table]);
            if (! $actual || $actual->type !== $type) {
                throw new \RuntimeException('Unexpected master identifier: public.'.$table);
            }
        }
        if ($this->block === 1) {
            if (DB::selectOne("SELECT to_regnamespace('skills_edu') AS id")->id !== null) {
                throw new \RuntimeException('Unregistered skills_edu schema exists; reconcile before migration.');
            }
            DB::statement('CREATE SCHEMA skills_edu');
            DB::unprepared('CREATE FUNCTION skills_edu.set_updated_at() RETURNS trigger LANGUAGE plpgsql SET search_path=pg_catalog AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END $$');
        } else {
            $predecessor = match ($this->block) {
                2 => 'occupation_skills', 3 => 'institutional_candidate_registrations', 4 => 'institutional_authorisations',
                5 => 'human_resource_need_certifications', 6 => 'profile_need_matches',
                default => throw new \RuntimeException('Unknown skills education migration block'),
            };
            if (DB::selectOne('SELECT to_regclass(?) AS id', ['skills_edu.'.$predecessor])->id === null) {
                throw new \RuntimeException('Missing previous skills_edu block: '.$predecessor);
            }
        }
        $sql = file_get_contents(database_path('sql/skills-education/'.sprintf('%02d', $this->block).'.sql'));
        if ($sql === false) {
            throw new \RuntimeException('Versioned SQL block missing');
        }
        if (DB::connection()->getPdo()->exec($sql) === false) {
            throw new \RuntimeException('Versioned SQL block failed');
        }
        DB::unprepared(<<<'SQL'
DO $indexes$
DECLARE fk record;
BEGIN
 FOR fk IN SELECT c.conrelid,c.conkey,c.conname,t.relname,
  (SELECT string_agg(quote_ident(a.attname),', ' ORDER BY k.p) FROM unnest(c.conkey) WITH ORDINALITY k(n,p)
   JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=k.n) AS cols
 FROM pg_constraint c JOIN pg_class t ON t.oid=c.conrelid JOIN pg_namespace ns ON ns.oid=t.relnamespace
 WHERE ns.nspname='skills_edu' AND c.contype='f'
 LOOP
  IF NOT EXISTS (SELECT 1 FROM pg_index i JOIN pg_class x ON x.oid=i.indexrelid JOIN pg_am am ON am.oid=x.relam
   WHERE i.indrelid=fk.conrelid AND i.indisvalid AND i.indisready AND i.indpred IS NULL AND am.amname='btree'
   AND i.indnkeyatts>=cardinality(fk.conkey)
   AND ARRAY(SELECT n FROM unnest(i.indkey::smallint[]) WITH ORDINALITY k(n,p) WHERE p<=cardinality(fk.conkey) ORDER BY p)=fk.conkey)
  THEN EXECUTE format('CREATE INDEX %I ON skills_edu.%I (%s)','se_fk_'||md5(fk.relname||fk.conname),fk.relname,fk.cols);
  END IF;
 END LOOP;
END $indexes$;
SQL);
        if ($this->block === 6) {
            DB::unprepared(<<<'SQL'
CREATE FUNCTION skills_edu.reject_audit_mutation() RETURNS trigger LANGUAGE plpgsql SET search_path=pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Skills education audit is append-only' USING ERRCODE='23514'; END $$;
CREATE TRIGGER immutable_skills_education_audit BEFORE UPDATE OR DELETE OR TRUNCATE ON skills_edu.audit_events
FOR EACH STATEMENT EXECUTE FUNCTION skills_edu.reject_audit_mutation();
SQL);
        }
    }

    public function down(): void
    {
        DB::select("SELECT pg_advisory_xact_lock(hashtextextended('skills_edu:migrations',0))");
        foreach ($this->tables as $table) {
            if (DB::table('skills_edu.'.$table)->exists()) {
                throw new \RuntimeException('Skills education data exists; destructive rollback refused: '.$table);
            }
        }
        foreach (array_reverse($this->tables) as $table) {
            DB::statement('DROP TABLE skills_edu.'.$table.' RESTRICT');
        }
        if ($this->block === 6) {
            DB::statement('DROP FUNCTION skills_edu.reject_audit_mutation() RESTRICT');
        }
        if ($this->block === 3) {
            DB::statement('DROP FUNCTION skills_edu.validate_consent_dates() RESTRICT');
        }
        if ($this->block === 1) {
            DB::statement('DROP FUNCTION skills_edu.set_updated_at() RESTRICT');
            DB::statement('DROP SCHEMA skills_edu RESTRICT');
        }
    }
}