chore(db): add full live data reset

Limpia Departamento, Área, Yacimiento, Instalación y Subinstalación en el entorno vivo, preservando usuarios, Empresas/Operadoras y catálogos estructurales.

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

@@ -0,0 +1,240 @@
+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;
+  }
+}

api-v3/src/database/migrations/1790103000000-full-live-data-reset.ts

@@ -0,0 +1,173 @@
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+/**
+ * One-time full live-data reset requested before loading the definitive source files.
+ *
+ * The database is left with only the single `admin` user and the minimum product
+ * scaffolding required to keep authentication/authorization and the core asset
+ * model functional. Every business, operational, imported, catalog, history,
+ * document, inspection and tenant/company row is removed.
+ *
+ * Fresh migration replays/CI do not have the live `admin` account at this point,
+ * so this migration intentionally no-ops there.
+ */
+export class FullLiveDataReset1790103000000 implements MigrationInterface {
+  name = 'FullLiveDataReset1790103000000';
+
+  public async up(queryRunner: QueryRunner): Promise<void> {
+    const adminRows = (await queryRunner.query(`
+      SELECT id, username
+      FROM users
+      WHERE lower(btrim(username))='admin'
+      ORDER BY id
+    `)) as Array<{ id: string; username: string }>;
+
+    if (adminRows.length === 0) {
+      // eslint-disable-next-line no-console
+      console.log('[full-live-reset] skipped: no live admin account on migration replay');
+      return;
+    }
+
+    if (adminRows.length !== 1) {
+      throw new Error(
+        `Full live reset aborted: expected exactly one username admin, found ${adminRows.length}`,
+      );
+    }
+
+    const adminId = adminRows[0].id;
+
+    // These are product/schema scaffolding, not customer/business data.
+    // Everything else in public is disposable live data for this reset.
+    const structuralTables = [
+      'roles',
+      'permissions',
+      'role_permissions',
+      'asset_types',
+      'asset_attribute_definitions',
+      'asset_type_parent_rules',
+    ] as const;
+
+    const preservedTables = new Set<string>([
+      'typeorm_migrations',
+      'users',
+      'user_roles',
+      ...structuralTables,
+    ]);
+
+    const structuralCounts = new Map<string, string>();
+    for (const table of structuralTables) {
+      const safeTable = `"${table.replace(/"/g, '""')}"`;
+      const rows = (await queryRunner.query(
+        `SELECT count(*)::text AS total FROM ${safeTable}`,
+      )) as Array<{ total: string }>;
+      structuralCounts.set(table, rows[0]?.total ?? '0');
+    }
+
+    const adminRolesBefore = (await queryRunner.query(
+      `SELECT count(*)::text AS total FROM user_roles WHERE user_id=$1`,
+      [adminId],
+    )) as Array<{ total: string }>;
+    const adminRoleCount = adminRolesBefore[0]?.total ?? '0';
+    if (adminRoleCount === '0') {
+      throw new Error('Full live reset aborted: admin has no assigned role');
+    }
+
+    const tableRows = (await queryRunner.query(`
+      SELECT table_name
+      FROM information_schema.tables
+      WHERE table_schema='public'
+        AND table_type='BASE TABLE'
+      ORDER BY table_name
+    `)) as Array<{ table_name: string }>;
+
+    const disposableTables = tableRows
+      .map((row) => row.table_name)
+      .filter((table) => !preservedTables.has(table));
+
+    if (disposableTables.length > 0) {
+      const quoted = disposableTables
+        .map((table) => `"${table.replace(/"/g, '""')}"`)
+        .join(', ');
+      await queryRunner.query(`TRUNCATE TABLE ${quoted} RESTART IDENTITY CASCADE`);
+    }
+
+    // Keep only the owner's administrator account. user_roles for other users
+    // are removed through their FK cascade.
+    await queryRunner.query(`DELETE FROM users WHERE id<>$1`, [adminId]);
+
+    // Invalidate any previous login state and unlock the preserved account.
+    await queryRunner.query(
+      `
+      UPDATE users
+      SET failed_login_attempts=0,
+          locked_until=NULL,
+          last_login_at=NULL,
+          updated_at=CURRENT_TIMESTAMP
+      WHERE id=$1
+      `,
+      [adminId],
+    );
+
+    const finalUsers = (await queryRunner.query(`
+      SELECT
+        count(*)::text AS total,
+        count(*) FILTER (WHERE lower(btrim(username))='admin')::text AS admins
+      FROM users
+    `)) as Array<{ total: string; admins: string }>;
+
+    if (finalUsers[0]?.total !== '1' || finalUsers[0]?.admins !== '1') {
+      throw new Error('Full live reset verification failed: users table is not admin-only');
+    }
+
+    const finalAdminRoles = (await queryRunner.query(
+      `
+      SELECT
+        count(*) FILTER (WHERE user_id=$1)::text AS total,
+        count(*) FILTER (WHERE user_id<>$1)::text AS foreign_users
+      FROM user_roles
+      `,
+      [adminId],
+    )) as Array<{ total: string; foreign_users: string }>;
+
+    if (
+      finalAdminRoles[0]?.total !== adminRoleCount ||
+      finalAdminRoles[0]?.foreign_users !== '0'
+    ) {
+      throw new Error('Full live reset verification failed: admin role assignments changed');
+    }
+
+    for (const table of structuralTables) {
+      const safeTable = `"${table.replace(/"/g, '""')}"`;
+      const rows = (await queryRunner.query(
+        `SELECT count(*)::text AS total FROM ${safeTable}`,
+      )) as Array<{ total: string }>;
+      const before = structuralCounts.get(table) ?? '0';
+      if (rows[0]?.total !== before) {
+        throw new Error(
+          `Full live reset verification failed: structural table ${table} changed (${before} -> ${rows[0]?.total ?? 'unknown'})`,
+        );
+      }
+    }
+
+    for (const table of disposableTables) {
+      const safeTable = `"${table.replace(/"/g, '""')}"`;
+      const rows = (await queryRunner.query(
+        `SELECT count(*)::text AS total FROM ${safeTable}`,
+      )) as Array<{ total: string }>;
+      if (rows[0]?.total !== '0') {
+        throw new Error(`Full live reset verification failed: ${table} is not empty`);
+      }
+    }
+
+    // eslint-disable-next-line no-console
+    console.log(
+      `[full-live-reset] kept admin=${adminRows[0].username} (${adminId}); preserved ${structuralTables.length} product tables; cleared ${disposableTables.length} data tables`,
+    );
+  }
+
+  public async down(): Promise<void> {
+    throw new Error(
+      'FullLiveDataReset is intentionally destructive; restore the automatic deploy PRE database backup instead.',
+    );
+  }
+}