fix(db): make common field attribute rollback safe

api-v3/src/database/migrations/1790099200000-reset-operational-hierarchy-data.ts

@@ -1,240 +0,0 @@
-import { MigrationInterface, QueryRunner } from 'typeorm';
-
-const TARGET_CODES = [
-  'departamento',
-  'area',
-  'yacimiento',
-  'instalacion',
-  'subinstalacion',
-] as const;
-
-const DELETE_ORDER = [
-  'subinstalacion',
-  'instalacion',
-  'yacimiento',
-  'area',
-  'departamento',
-] as const;
-
-type ProtectedSnapshot = {
-  users: string;
-  companies: string;
-  companyProfiles: string;
-  assetTypes: string;
-  assetAttributes: string;
-  inventoryFamilies: string;
-  familyAttributes: string;
-  findingCategories: string;
-  findingItems: string;
-};
-
-export class ResetOperationalHierarchyData1790099200000 implements MigrationInterface {
-  name = 'ResetOperationalHierarchyData1790099200000';
-
-  public async up(queryRunner: QueryRunner): Promise<void> {
-    // This is a one-time live-data cleanup, not a new canonical empty seed.
-    // Fresh CI/bootstrap databases intentionally have no admin account while
-    // replaying the historical migration chain, so they must retain the F6.1
-    // presentation seed used by hierarchy/planning contract tests.
-    const adminRows = (await queryRunner.query(`
-      SELECT id
-      FROM users
-      WHERE lower(btrim(username))='admin'
-      ORDER BY id
-    `)) as Array<{ id: string }>;
-    if (adminRows.length === 0) {
-      // eslint-disable-next-line no-console
-      console.log('[hierarchy-reset] skipped: no live admin account on migration replay');
-      return;
-    }
-    if (adminRows.length !== 1) {
-      throw new Error(
-        `Hierarchy reset aborted: expected exactly one live admin account, found ${adminRows.length}`,
-      );
-    }
-
-    const targetTypes = (await queryRunner.query(
-      `
-      SELECT lower(code) AS code
-      FROM asset_types
-      WHERE lower(code)=ANY($1::text[])
-      ORDER BY lower(code)
-      `,
-      [[...TARGET_CODES]],
-    )) as Array<{ code: string }>;
-
-    const found = new Set(targetTypes.map((row) => row.code));
-    const missing = TARGET_CODES.filter((code) => !found.has(code));
-    if (missing.length > 0) {
-      throw new Error(`Hierarchy reset aborted: missing asset types ${missing.join(', ')}`);
-    }
-
-    const before = await this.protectedSnapshot(queryRunner);
-
-    await queryRunner.query(
-      `
-      CREATE TEMP TABLE reset_target_assets ON COMMIT DROP AS
-      SELECT asset.id
-      FROM assets asset
-      JOIN asset_types type ON type.id=asset.asset_type_id
-      WHERE lower(type.code)=ANY($1::text[])
-      `,
-      [[...TARGET_CODES]],
-    );
-    await queryRunner.query(`CREATE UNIQUE INDEX reset_target_assets_pk ON reset_target_assets(id)`);
-
-    const [targetCount] = (await queryRunner.query(
-      `SELECT COUNT(*)::integer AS total FROM reset_target_assets`,
-    )) as Array<{ total: number }>;
-
-    // An Inspection freezes Area/Yacimiento/Operadora from creation and several
-    // inspection tables hold RESTRICT references to the hierarchy. Keeping a
-    // transaction that points to deleted territory would be invalid, so the
-    // complete disposable inspection graph is cleared first.
-    await queryRunner.query('TRUNCATE TABLE inspection_visits CASCADE');
-
-    // Legacy administrative departments are also presentation/operational data.
-    // Current F6 Departments live in assets, but this prevents old rows from
-    // resurfacing through compatibility paths.
-    await queryRunner.query('TRUNCATE TABLE administrative_departments CASCADE');
-
-    // Legal-right participants depend on area_legal_rights rather than directly
-    // on assets. Remove them before the generic direct-FK cleanup below.
-    await queryRunner.query(`
-      DELETE FROM area_legal_right_organizations organization
-      USING area_legal_rights legal_right
-      WHERE organization.right_id=legal_right.id
-        AND legal_right.area_id IN (SELECT id FROM reset_target_assets)
-    `);
-
-    // Clean every table that directly references one of the hierarchy assets.
-    // This deliberately discovers the current schema instead of maintaining a
-    // fragile hand-written list as new dossier/history tables are added.
-    await queryRunner.query(`
-      DO $$
-      DECLARE dependency record;
-      BEGIN
-        FOR dependency IN
-          SELECT
-            namespace.nspname AS schema_name,
-            relation.relname AS table_name,
-            attribute.attname AS column_name
-          FROM pg_constraint constraint_row
-          JOIN pg_class relation ON relation.oid=constraint_row.conrelid
-          JOIN pg_namespace namespace ON namespace.oid=relation.relnamespace
-          JOIN LATERAL unnest(constraint_row.conkey) WITH ORDINALITY local_key(attnum,ordinality)
-            ON true
-          JOIN LATERAL unnest(constraint_row.confkey) WITH ORDINALITY referenced_key(attnum,ordinality)
-            ON referenced_key.ordinality=local_key.ordinality
-          JOIN pg_attribute attribute
-            ON attribute.attrelid=constraint_row.conrelid
-           AND attribute.attnum=local_key.attnum
-          JOIN pg_attribute referenced_attribute
-            ON referenced_attribute.attrelid=constraint_row.confrelid
-           AND referenced_attribute.attnum=referenced_key.attnum
-          WHERE constraint_row.contype='f'
-            AND constraint_row.confrelid='assets'::regclass
-            AND constraint_row.conrelid<>'assets'::regclass
-            AND array_length(constraint_row.conkey,1)=1
-            AND referenced_attribute.attname='id'
-          ORDER BY namespace.nspname,relation.relname,attribute.attname
-        LOOP
-          EXECUTE format(
-            'DELETE FROM %I.%I WHERE %I IN (SELECT id FROM reset_target_assets)',
-            dependency.schema_name,
-            dependency.table_name,
-            dependency.column_name
-          );
-        END LOOP;
-      END $$;
-    `);
-
-    // parent_id is RESTRICT, therefore physical hierarchy rows are deleted from
-    // the leaves upward. Company/Operator assets are intentionally not targets.
-    for (const code of DELETE_ORDER) {
-      await queryRunner.query(
-        `
-        DELETE FROM assets asset
-        USING asset_types type
-        WHERE asset.asset_type_id=type.id
-          AND lower(type.code)=$1
-        `,
-        [code],
-      );
-    }
-
-    const after = await this.protectedSnapshot(queryRunner);
-    for (const key of Object.keys(before) as Array<keyof ProtectedSnapshot>) {
-      if (before[key] !== after[key]) {
-        throw new Error(
-          `Hierarchy reset verification failed: protected ${key} changed (${before[key]} -> ${after[key]})`,
-        );
-      }
-    }
-
-    const [verification] = (await queryRunner.query(`
-      SELECT
-        (
-          SELECT COUNT(*)::integer
-          FROM assets asset
-          JOIN asset_types type ON type.id=asset.asset_type_id
-          WHERE lower(type.code)=ANY($1::text[])
-        ) AS hierarchy_assets,
-        (SELECT COUNT(*)::integer FROM administrative_departments) AS administrative_departments,
-        (SELECT COUNT(*)::integer FROM inspection_visits) AS inspection_visits
-      `, [[...TARGET_CODES]])) as Array<{
-      hierarchy_assets: number;
-      administrative_departments: number;
-      inspection_visits: number;
-    }>;
-
-    if (
-      !verification
-      || Number(verification.hierarchy_assets) !== 0
-      || Number(verification.administrative_departments) !== 0
-      || Number(verification.inspection_visits) !== 0
-    ) {
-      throw new Error(`Hierarchy reset verification failed: ${JSON.stringify(verification ?? {})}`);
-    }
-
-    // eslint-disable-next-line no-console
-    console.log(
-      `[hierarchy-reset] removed ${Number(targetCount?.total ?? 0)} Departamento/Área/Yacimiento/Instalación/Subinstalación assets; inspections and legacy departments cleared; users=${after.users}; companies=${after.companies} preserved`,
-    );
-  }
-
-  public async down(): Promise<void> {
-    throw new Error(
-      'ResetOperationalHierarchyData is intentionally destructive; restore the automatic deploy PRE database backup instead.',
-    );
-  }
-
-  private async protectedSnapshot(queryRunner: QueryRunner): Promise<ProtectedSnapshot> {
-    const [snapshot] = (await queryRunner.query(`
-      SELECT
-        (SELECT COUNT(*)::text FROM users) AS "users",
-        (
-          SELECT COUNT(*)::text
-          FROM assets asset
-          JOIN asset_types type ON type.id=asset.asset_type_id
-          WHERE type.operational_role='COMPANY'
-        ) AS "companies",
-        (
-          SELECT COUNT(*)::text
-          FROM organization_profiles profile
-          JOIN assets asset ON asset.id=profile.asset_id
-          JOIN asset_types type ON type.id=asset.asset_type_id
-          WHERE type.operational_role='COMPANY'
-        ) AS "companyProfiles",
-        (SELECT COUNT(*)::text FROM asset_types) AS "assetTypes",
-        (SELECT COUNT(*)::text FROM asset_attribute_definitions) AS "assetAttributes",
-        (SELECT COUNT(*)::text FROM inventory_families) AS "inventoryFamilies",
-        (SELECT COUNT(*)::text FROM inventory_family_attribute_definitions) AS "familyAttributes",
-        (SELECT COUNT(*)::text FROM finding_categories) AS "findingCategories",
-        (SELECT COUNT(*)::text FROM finding_catalog_items) AS "findingItems"
-    `)) as ProtectedSnapshot[];
-
-    if (!snapshot) throw new Error('Hierarchy reset aborted: could not snapshot protected masters');
-    return snapshot;
-  }
-}