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/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.',
+    );
+  }
+}