fix: preservar etiquetas técnicas extensas del Excel

.github/workflows/android.yml

@@ -3,7 +3,7 @@ name: Android APK
 on:
   push:
     branches:
-      - feature/f2-2-android-v2
+      - 'feature/f2-2*'
     paths:
       - 'android-app/**'
       - '.github/workflows/android.yml'
@@ -53,7 +53,7 @@ jobs:
       - name: Upload APK
         uses: actions/upload-artifact@v4
         with:
-          name: DH-Inspeccion-F2.2-0.10.0-debug
+          name: DH-Inspeccion-F2.2.1-0.10.1-debug
           path: android-app/app/build/outputs/apk/debug/app-debug.apk
           if-no-files-found: error
           retention-days: 14

.github/workflows/f2-2-ci.yml

@@ -3,7 +3,7 @@ name: F2.2 Integration CI
 on:
   push:
     branches:
-      - feature/f2-2-android-v2
+      - 'feature/f2-2*'
     paths:
       - 'api-v3/**'
       - 'web-v2/**'
@@ -34,6 +34,90 @@ jobs:
       - run: npm test
       - run: npm run build
 
+  migrations:
+    name: DB · migrations on PostgreSQL 16/PostGIS
+    runs-on: ubuntu-latest
+    services:
+      postgres:
+        image: postgis/postgis:16-3.4
+        env:
+          POSTGRES_USER: dhv2_owner
+          POSTGRES_PASSWORD: owner_test_password
+          POSTGRES_DB: dhv2_ci
+        ports:
+          - 5432:5432
+        options: >-
+          --health-cmd "pg_isready -U dhv2_owner -d dhv2_ci"
+          --health-interval 5s
+          --health-timeout 5s
+          --health-retries 20
+    env:
+      DB_HOST: 127.0.0.1
+      DB_PORT: 5432
+      DB_NAME: dhv2_ci
+      DB_MIGRATION_USER: dhv2_owner
+      DB_MIGRATION_PASSWORD: owner_test_password
+      DB_APP_USER: dhv2_app
+    defaults:
+      run:
+        working-directory: api-v3
+    steps:
+      - uses: actions/checkout@v4
+      - uses: actions/setup-node@v4
+        with:
+          node-version: '24'
+          cache: npm
+          cache-dependency-path: api-v3/package-lock.json
+      - name: PostgreSQL client
+        run: |
+          command -v psql >/dev/null || {
+            sudo apt-get update
+            sudo apt-get install -y postgresql-client
+          }
+      - name: Prepare roles and extensions
+        env:
+          PGPASSWORD: owner_test_password
+        run: |
+          psql -v ON_ERROR_STOP=1 -h 127.0.0.1 -U dhv2_owner -d dhv2_ci <<'SQL'
+          CREATE EXTENSION IF NOT EXISTS postgis;
+          CREATE EXTENSION IF NOT EXISTS pgcrypto;
+          DO $$
+          BEGIN
+            IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dhv2_app') THEN
+              CREATE ROLE dhv2_app LOGIN PASSWORD 'app_test_password';
+            END IF;
+          END
+          $$;
+          GRANT CONNECT ON DATABASE dhv2_ci TO dhv2_app;
+          GRANT USAGE ON SCHEMA public TO dhv2_app;
+
+          -- This reset was a one-time late production operation. It is marked as
+          -- executed on the clean CI database so the historical schema can be
+          -- constructed chronologically without running that production-only wipe.
+          CREATE TABLE IF NOT EXISTS typeorm_migrations (
+            id SERIAL PRIMARY KEY,
+            timestamp bigint NOT NULL,
+            name varchar NOT NULL
+          );
+          INSERT INTO typeorm_migrations (timestamp,name)
+          VALUES (1788652800000,'ResetProductionOperationalData1788652800000');
+          SQL
+      - run: npm ci
+      - run: npm run build
+      - name: Run every pending migration
+        run: npm run migration:run
+      - name: Assert no pending migrations
+        run: |
+          npm run migration:show | tee /tmp/migrations.txt
+          grep -Fq 'Pending migrations: no' /tmp/migrations.txt
+      - name: Assert F2.2.1 reference import
+        env:
+          PGPASSWORD: owner_test_password
+        run: |
+          test "$(psql -At -h 127.0.0.1 -U dhv2_owner -d dhv2_ci -c "SELECT count(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE a.data_origin='IMPORT' AND a.source_name='Tablas de yacimiento(1).xlsx' AND lower(t.code)='yacimiento'")" = "230"
+          test "$(psql -At -h 127.0.0.1 -U dhv2_owner -d dhv2_ci -c "SELECT count(*) FROM assets WHERE (operational_area_id IS NULL) <> (operator_company_id IS NULL)")" = "0"
+          test "$(psql -At -h 127.0.0.1 -U dhv2_owner -d dhv2_ci -c "SELECT count(*) FROM source_documents WHERE document_number IN ('DH-F221-TERRITORIO','DH-F221-APP')")" = "2"
+
   web:
     name: WEB · typecheck, build
     runs-on: ubuntu-latest

android-app/app/build.gradle.kts

@@ -12,8 +12,8 @@ android {
         applicationId = "com.korexlabs.dhinspeccion"
         minSdk = 26
         targetSdk = 36
-        versionCode = 14
-        versionName = "0.10.0"
+        versionCode = 15
+        versionName = "0.10.1"
 
         testInstrumentationRunner = "androidx.test.runner.AndroidJUnitRunner"
         vectorDrawables.useSupportLibrary = true
@@ -56,11 +56,14 @@ dependencies {
 
     implementation("androidx.core:core-ktx:1.17.0")
     implementation("androidx.activity:activity-compose:1.10.1")
+    implementation("androidx.fragment:fragment-ktx:1.8.9")
     implementation("androidx.lifecycle:lifecycle-runtime-ktx:2.9.2")
     implementation("androidx.lifecycle:lifecycle-viewmodel-compose:2.9.2")
     implementation("androidx.compose.material3:material3")
+    implementation("androidx.compose.material:material-icons-extended")
     implementation("androidx.compose.ui:ui")
     implementation("androidx.compose.ui:ui-tooling-preview")
+    implementation("androidx.biometric:biometric:1.1.0")
     debugImplementation("androidx.compose.ui:ui-tooling")
 
     implementation("org.jetbrains.kotlinx:kotlinx-coroutines-android:1.10.2")

android-app/app/src/main/java/com/korexlabs/dhinspeccion/MainActivity.kt

@@ -1,19 +1,19 @@
 package com.korexlabs.dhinspeccion
 
 import android.os.Bundle
-import androidx.activity.ComponentActivity
 import androidx.activity.compose.setContent
 import androidx.activity.enableEdgeToEdge
+import androidx.fragment.app.FragmentActivity
 import androidx.lifecycle.viewmodel.compose.viewModel
-import com.korexlabs.dhinspeccion.ui.DhApp
+import com.korexlabs.dhinspeccion.ui.DhRoot
 
-class MainActivity : ComponentActivity() {
+class MainActivity : FragmentActivity() {
     override fun onCreate(savedInstanceState: Bundle?) {
         super.onCreate(savedInstanceState)
         enableEdgeToEdge()
         setContent {
             val model: MainViewModel = viewModel()
-            DhApp(model)
+            DhRoot(model, this@MainActivity)
         }
     }
 }

android-app/app/src/main/java/com/korexlabs/dhinspeccion/ui/LoginGate.kt

@@ -0,0 +1,202 @@
+package com.korexlabs.dhinspeccion.ui
+
+import android.content.Context
+import androidx.biometric.BiometricManager
+import androidx.biometric.BiometricPrompt
+import androidx.compose.foundation.layout.Arrangement
+import androidx.compose.foundation.layout.Box
+import androidx.compose.foundation.layout.Column
+import androidx.compose.foundation.layout.fillMaxSize
+import androidx.compose.foundation.layout.fillMaxWidth
+import androidx.compose.foundation.layout.padding
+import androidx.compose.material.icons.Icons
+import androidx.compose.material.icons.filled.Visibility
+import androidx.compose.material.icons.filled.VisibilityOff
+import androidx.compose.material3.Button
+import androidx.compose.material3.Icon
+import androidx.compose.material3.IconButton
+import androidx.compose.material3.MaterialTheme
+import androidx.compose.material3.OutlinedButton
+import androidx.compose.material3.OutlinedTextField
+import androidx.compose.material3.Surface
+import androidx.compose.material3.Text
+import androidx.compose.runtime.Composable
+import androidx.compose.runtime.LaunchedEffect
+import androidx.compose.runtime.getValue
+import androidx.compose.runtime.mutableStateOf
+import androidx.compose.runtime.remember
+import androidx.compose.runtime.saveable.rememberSaveable
+import androidx.compose.runtime.setValue
+import androidx.compose.ui.Alignment
+import androidx.compose.ui.Modifier
+import androidx.compose.ui.platform.LocalContext
+import androidx.compose.ui.text.font.FontWeight
+import androidx.compose.ui.text.input.PasswordVisualTransformation
+import androidx.compose.ui.text.input.VisualTransformation
+import androidx.compose.ui.unit.dp
+import androidx.core.content.ContextCompat
+import androidx.fragment.app.FragmentActivity
+import com.korexlabs.dhinspeccion.MainViewModel
+
+private const val BIOMETRIC_PREFS = "dh_v2_biometric"
+private const val BIOMETRIC_ENABLED = "enabled"
+
+private fun biometricAvailable(context: Context): Boolean =
+    BiometricManager.from(context).canAuthenticate(BiometricManager.Authenticators.BIOMETRIC_STRONG) ==
+        BiometricManager.BIOMETRIC_SUCCESS
+
+private fun biometricEnabled(context: Context): Boolean =
+    context.getSharedPreferences(BIOMETRIC_PREFS, Context.MODE_PRIVATE)
+        .getBoolean(BIOMETRIC_ENABLED, false)
+
+private fun setBiometricEnabled(context: Context, enabled: Boolean) {
+    context.getSharedPreferences(BIOMETRIC_PREFS, Context.MODE_PRIVATE)
+        .edit().putBoolean(BIOMETRIC_ENABLED, enabled).apply()
+}
+
+@Composable
+fun DhRoot(model: MainViewModel, activity: FragmentActivity) {
+    val context = LocalContext.current
+    val capable = remember { biometricAvailable(context) }
+    var unlocked by rememberSaveable { mutableStateOf(false) }
+    var passwordLoginInFlight by rememberSaveable { mutableStateOf(false) }
+    var enabled by remember { mutableStateOf(biometricEnabled(context)) }
+
+    LaunchedEffect(model.session) {
+        if (model.session != null && passwordLoginInFlight) {
+            if (capable) {
+                setBiometricEnabled(context, true)
+                enabled = true
+            }
+            unlocked = true
+            passwordLoginInFlight = false
+        }
+        if (model.session == null) unlocked = false
+    }
+
+    MaterialTheme {
+        Surface(Modifier.fillMaxSize()) {
+            when {
+                model.session == null -> EnhancedLoginScreen(model) {
+                    passwordLoginInFlight = true
+                }
+                enabled && capable && !unlocked -> BiometricUnlockScreen(
+                    activity = activity,
+                    displayName = model.session?.displayName.orEmpty(),
+                    onAuthenticated = { unlocked = true },
+                    onUsePassword = {
+                        setBiometricEnabled(context, false)
+                        enabled = false
+                        model.logout()
+                    },
+                )
+                else -> DhApp(model)
+            }
+        }
+    }
+}
+
+@Composable
+private fun EnhancedLoginScreen(model: MainViewModel, onPasswordLogin: () -> Unit) {
+    var identifier by rememberSaveable { mutableStateOf("") }
+    var password by rememberSaveable { mutableStateOf("") }
+    var passwordVisible by rememberSaveable { mutableStateOf(false) }
+
+    Box(Modifier.fillMaxSize().padding(24.dp), contentAlignment = Alignment.Center) {
+        Column(Modifier.fillMaxWidth(), verticalArrangement = Arrangement.spacedBy(14.dp)) {
+            Text("DH Inspección", style = MaterialTheme.typography.headlineMedium, fontWeight = FontWeight.Bold)
+            Text("Aplicación de campo · Dirección de Hidrocarburos")
+            model.error?.let { Text(it, color = MaterialTheme.colorScheme.error) }
+            model.notice?.let { Text(it, color = MaterialTheme.colorScheme.primary) }
+            OutlinedTextField(
+                value = identifier,
+                onValueChange = { identifier = it },
+                label = { Text("Usuario o email") },
+                modifier = Modifier.fillMaxWidth(),
+                singleLine = true,
+            )
+            OutlinedTextField(
+                value = password,
+                onValueChange = { password = it },
+                label = { Text("Contraseña") },
+                modifier = Modifier.fillMaxWidth(),
+                singleLine = true,
+                visualTransformation = if (passwordVisible) VisualTransformation.None else PasswordVisualTransformation(),
+                trailingIcon = {
+                    IconButton(onClick = { passwordVisible = !passwordVisible }) {
+                        Icon(
+                            imageVector = if (passwordVisible) Icons.Filled.VisibilityOff else Icons.Filled.Visibility,
+                            contentDescription = if (passwordVisible) "Ocultar contraseña" else "Mostrar contraseña",
+                        )
+                    }
+                },
+            )
+            Button(
+                onClick = {
+                    onPasswordLogin()
+                    model.login(identifier, password)
+                },
+                modifier = Modifier.fillMaxWidth(),
+                enabled = !model.busy,
+            ) { Text(if (model.busy) "Ingresando…" else "Ingresar") }
+            Text(
+                "Después del primer ingreso correcto, si la tablet tiene una huella fuerte configurada, se habilita el acceso biométrico. La contraseña no se almacena.",
+                style = MaterialTheme.typography.bodySmall,
+            )
+        }
+    }
+}
+
+@Composable
+private fun BiometricUnlockScreen(
+    activity: FragmentActivity,
+    displayName: String,
+    onAuthenticated: () -> Unit,
+    onUsePassword: () -> Unit,
+) {
+    var error by rememberSaveable { mutableStateOf<String?>(null) }
+
+    fun authenticate() {
+        val executor = ContextCompat.getMainExecutor(activity)
+        val prompt = BiometricPrompt(
+            activity,
+            executor,
+            object : BiometricPrompt.AuthenticationCallback() {
+                override fun onAuthenticationSucceeded(result: BiometricPrompt.AuthenticationResult) {
+                    super.onAuthenticationSucceeded(result)
+                    error = null
+                    onAuthenticated()
+                }
+
+                override fun onAuthenticationError(errorCode: Int, errString: CharSequence) {
+                    super.onAuthenticationError(errorCode, errString)
+                    error = errString.toString()
+                }
+
+                override fun onAuthenticationFailed() {
+                    super.onAuthenticationFailed()
+                    error = "Huella no reconocida."
+                }
+            },
+        )
+        val info = BiometricPrompt.PromptInfo.Builder()
+            .setTitle("Ingresar a DH Inspección")
+            .setSubtitle(if (displayName.isBlank()) "Validá tu identidad" else "Hola, $displayName")
+            .setNegativeButtonText("Usar contraseña")
+            .setAllowedAuthenticators(BiometricManager.Authenticators.BIOMETRIC_STRONG)
+            .build()
+        prompt.authenticate(info)
+    }
+
+    LaunchedEffect(Unit) { authenticate() }
+
+    Box(Modifier.fillMaxSize().padding(24.dp), contentAlignment = Alignment.Center) {
+        Column(Modifier.fillMaxWidth(), verticalArrangement = Arrangement.spacedBy(14.dp), horizontalAlignment = Alignment.CenterHorizontally) {
+            Text("DH Inspección", style = MaterialTheme.typography.headlineMedium, fontWeight = FontWeight.Bold)
+            Text("Ingresá con tu huella")
+            error?.let { Text(it, color = MaterialTheme.colorScheme.error) }
+            Button(onClick = { authenticate() }, modifier = Modifier.fillMaxWidth()) { Text("Usar huella") }
+            OutlinedButton(onClick = onUsePassword, modifier = Modifier.fillMaxWidth()) { Text("Ingresar con contraseña") }
+        }
+    }
+}

api-v3/package.json

@@ -1,6 +1,6 @@
 {
   "name": "dhv2-api",
-  "version": "0.22.0-1",
+  "version": "0.22.1-1",
   "private": true,
   "license": "UNLICENSED",
   "scripts": {

api-v3/src/database/migrations/1789754300000-phase-f2-2-1-base-operational-types.ts

@@ -0,0 +1,50 @@
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+export class PhaseF221BaseOperationalTypes1789754300000 implements MigrationInterface {
+  name = 'PhaseF221BaseOperationalTypes1789754300000';
+
+  public async up(queryRunner: QueryRunner): Promise<void> {
+    await queryRunner.query(`
+      UPDATE asset_types
+      SET operational_role='AREA', can_be_root=true, is_active=true, updated_at=CURRENT_TIMESTAMP
+      WHERE lower(code)='area'
+    `);
+    await queryRunner.query(`
+      INSERT INTO asset_types (code,name,description,can_be_root,is_active,operational_role)
+      SELECT 'area','Área',
+        'Área hidrocarburífera administrada como ancla territorial.',
+        true,true,'AREA'
+      WHERE NOT EXISTS (SELECT 1 FROM asset_types WHERE operational_role='AREA')
+        AND NOT EXISTS (SELECT 1 FROM asset_types WHERE lower(code)='area')
+    `);
+
+    await queryRunner.query(`
+      UPDATE asset_types
+      SET operational_role='COMPANY', can_be_root=true, is_active=true, updated_at=CURRENT_TIMESTAMP
+      WHERE lower(code) IN ('empresa','organizacion')
+    `);
+    await queryRunner.query(`
+      INSERT INTO asset_types (code,name,description,can_be_root,is_active,operational_role)
+      SELECT 'empresa','Organización',
+        'Entidad jurídica u organización administrada. Su rol operativo se registra mediante relaciones históricas.',
+        true,true,'COMPANY'
+      WHERE NOT EXISTS (SELECT 1 FROM asset_types WHERE operational_role='COMPANY')
+        AND NOT EXISTS (SELECT 1 FROM asset_types WHERE lower(code)='empresa')
+    `);
+
+    const rows = (await queryRunner.query(`
+      SELECT
+        count(*) FILTER (WHERE operational_role='AREA')::text AS areas,
+        count(*) FILTER (WHERE operational_role='COMPANY')::text AS companies
+      FROM asset_types
+    `)) as Array<{ areas: string; companies: string }>;
+
+    if (Number(rows[0]?.areas ?? 0) < 1 || Number(rows[0]?.companies ?? 0) < 1) {
+      throw new Error('F2.2.1 could not guarantee AREA and COMPANY asset types');
+    }
+  }
+
+  public async down(): Promise<void> {
+    throw new Error('F2.2.1 base operational types are structural and are not removed automatically');
+  }
+}

api-v3/src/database/migrations/1789754350000-phase-f2-2-1-long-reference-labels.ts

@@ -0,0 +1,28 @@
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+export class PhaseF221LongReferenceLabels1789754350000 implements MigrationInterface {
+  name = 'PhaseF221LongReferenceLabels1789754350000';
+
+  public async up(queryRunner: QueryRunner): Promise<void> {
+    // La matriz APLICACION APP contiene denominaciones técnicas descriptivas que
+    // legítimamente superan el límite histórico de 160 caracteres. No se recorta
+    // información de la fuente: ampliamos sólo columnas descriptivas.
+    await queryRunner.query(`ALTER TABLE asset_types ALTER COLUMN name TYPE varchar(500)`);
+    await queryRunner.query(`ALTER TABLE asset_attribute_definitions ALTER COLUMN name TYPE varchar(500)`);
+  }
+
+  public async down(queryRunner: QueryRunner): Promise<void> {
+    const rows = (await queryRunner.query(`
+      SELECT
+        coalesce(max(length(name)),0)::integer AS max_type_name,
+        (SELECT coalesce(max(length(name)),0)::integer FROM asset_attribute_definitions) AS max_attribute_name
+      FROM asset_types
+    `)) as Array<{ max_type_name: number; max_attribute_name: number }>;
+
+    if ((rows[0]?.max_type_name ?? 0) > 160 || (rows[0]?.max_attribute_name ?? 0) > 160) {
+      throw new Error('F2.2.1 no puede reducir etiquetas a 160 caracteres sin pérdida de información');
+    }
+    await queryRunner.query(`ALTER TABLE asset_types ALTER COLUMN name TYPE varchar(160)`);
+    await queryRunner.query(`ALTER TABLE asset_attribute_definitions ALTER COLUMN name TYPE varchar(160)`);
+  }
+}

api-v3/src/database/migrations/1789754400000-phase-f2-2-1-reference-excel-data.ts

@@ -0,0 +1,615 @@
+import { createHash } from 'node:crypto';
+import { MigrationInterface, QueryRunner } from 'typeorm';
+import { loadF221ExcelSource } from '../../reference-data/f2-2-1-excel-source';
+
+function slug(value: string, max = 70): string {
+  const normalized = value
+    .normalize('NFD')
+    .replace(/[\u0300-\u036f]/g, '')
+    .toLowerCase()
+    .replace(/[^a-z0-9]+/g, '_')
+    .replace(/^_+|_+$/g, '');
+  return (normalized || 'item').slice(0, max);
+}
+
+function stableCode(prefix: string, ...parts: string[]): string {
+  const hash = createHash('sha1').update(parts.join('|')).digest('hex').slice(0, 8).toUpperCase();
+  const readable = slug(parts[0] ?? 'item', 72).toUpperCase();
+  return `${prefix}-${readable}-${hash}`.slice(0, 120);
+}
+
+function legalRightType(value: string): string {
+  const normalized = slug(value);
+  if (normalized.includes('explot')) return 'EXPLOITATION_CONCESSION';
+  if (normalized.includes('explor')) return 'EXPLORATION_PERMIT';
+  if (normalized.includes('transport')) return 'TRANSPORT_CONCESSION';
+  return 'OTHER';
+}
+
+const groupTargets: Record<string, string[]> = {
+  tanques: ['tanque'],
+  separadores: ['separador'],
+  bomba_zona_de_bombas: ['bomba'],
+  calderas: ['caldera'],
+  drenaje: ['drenaje'],
+  antorcha: ['antorcha'],
+  colectores: ['colector'],
+  sist_electrico_iluminacion: ['sistema_electrico_iluminacion'],
+  defensa_contra_incendios: ['defensa_contra_incendios'],
+  cargadero_y_descargadero_de_camiones: ['cargadero_descargadero'],
+  filtros: ['filtro'],
+  eq_flotacion: ['equipo_flotacion'],
+  baterias_y_plantas: ['bateria', 'planta'],
+  fwko_tratadores_calentadores: ['fwo', 'fwko', 'calentador', 'tratador_termico'],
+  bombeo_mecanico: ['bombeo_mecanico'],
+  bombeo_electreosumergible: ['bombeo_electreosumergible'],
+  bombeo_cavidad_progresiva_pcp: ['bombeo_cavidad_progresiva_pcp'],
+  pozos_surgentes_prod_gas: ['pozos_surgentes_prod_gas'],
+  pozos_inyectores_agua: ['pozos_inyectores_agua'],
+};
+
+const referenceAliases: Record<string, string> = {
+  tanque: 'tanque',
+  tanques: 'tanque',
+  separador: 'separador',
+  separadores: 'separador',
+  bombas: 'bomba',
+  bomba: 'bomba',
+  fwo: 'fwo',
+  fwko: 'fwko',
+  calentador: 'calentador',
+  piletas: 'drenaje',
+  pileta: 'drenaje',
+  cargadero_descargadero: 'cargadero_descargadero',
+  generador: 'generador',
+};
+
+type IdRow = { id: string };
+type CountRow = { total: string };
+
+async function assetIdByCode(queryRunner: QueryRunner, code: string): Promise<string | null> {
+  const rows = (await queryRunner.query(
+    `SELECT id FROM assets WHERE lower(code)=lower($1::text) LIMIT 1`,
+    [code],
+  )) as IdRow[];
+  return rows[0]?.id ?? null;
+}
+
+async function assetTypeIdByCode(queryRunner: QueryRunner, code: string): Promise<string | null> {
+  const rows = (await queryRunner.query(
+    `SELECT id FROM asset_types WHERE lower(code)=lower($1::text) LIMIT 1`,
+    [code],
+  )) as IdRow[];
+  return rows[0]?.id ?? null;
+}
+
+export class PhaseF221ReferenceExcelData1789754400000 implements MigrationInterface {
+  name = 'PhaseF221ReferenceExcelData1789754400000';
+
+  public async up(queryRunner: QueryRunner): Promise<void> {
+    const source = loadF221ExcelSource();
+
+    await queryRunner.query(`
+      INSERT INTO source_documents (document_type,document_number,title,issuer,notes)
+      SELECT 'SPREADSHEET','DH-F221-TERRITORIO','Tablas de yacimiento(1).xlsx','Dirección de Hidrocarburos',
+        'Fuente F2.2.1: maestro territorial y operativo. Hoja normalizada cr26e_tabla1.'
+      WHERE NOT EXISTS (
+        SELECT 1 FROM source_documents
+        WHERE document_number='DH-F221-TERRITORIO' AND issuer='Dirección de Hidrocarburos'
+      )
+    `);
+    await queryRunner.query(`
+      INSERT INTO source_documents (document_type,document_number,title,issuer,notes)
+      SELECT 'SPREADSHEET','DH-F221-APP','APLICACION APP(1).xlsx','Dirección de Hidrocarburos',
+        'Fuente F2.2.1: matriz de jerarquía, información y catálogo contextual de hallazgos. Hojas Hoja1 y Hoja2.'
+      WHERE NOT EXISTS (
+        SELECT 1 FROM source_documents
+        WHERE document_number='DH-F221-APP' AND issuer='Dirección de Hidrocarburos'
+      )
+    `);
+
+    const territoryDocuments = (await queryRunner.query(
+      `SELECT id FROM source_documents
+       WHERE document_number='DH-F221-TERRITORIO' AND issuer='Dirección de Hidrocarburos' LIMIT 1`,
+    )) as IdRow[];
+    const appDocuments = (await queryRunner.query(
+      `SELECT id FROM source_documents
+       WHERE document_number='DH-F221-APP' AND issuer='Dirección de Hidrocarburos' LIMIT 1`,
+    )) as IdRow[];
+    const territoryDocumentId = territoryDocuments[0]?.id;
+    const appDocumentId = appDocuments[0]?.id;
+    if (!territoryDocumentId || !appDocumentId) {
+      throw new Error('F2.2.1 source documents could not be resolved');
+    }
+
+    await queryRunner.query(
+      `UPDATE asset_types SET can_be_root=true WHERE operational_role IN ('AREA','COMPANY')`,
+    );
+    await queryRunner.query(`
+      INSERT INTO asset_types (code,name,description,can_be_root,is_active,operational_role)
+      SELECT 'yacimiento','Yacimiento',
+        'Yacimiento físico/operativo perteneciente a un Área. Su operadora se determina por contexto histórico.',
+        false,true,'GENERIC'
+      WHERE NOT EXISTS (SELECT 1 FROM asset_types WHERE lower(code)='yacimiento')
+    `);
+    await queryRunner.query(`
+      INSERT INTO asset_type_parent_rules (child_type_id,parent_type_id)
+      SELECT child.id,parent.id
+      FROM asset_types child CROSS JOIN asset_types parent
+      WHERE lower(child.code)='yacimiento' AND parent.operational_role='AREA'
+      ON CONFLICT DO NOTHING
+    `);
+    await queryRunner.query(`
+      INSERT INTO asset_attribute_definitions
+        (asset_type_id,code,name,data_type,is_required,is_active,sort_order)
+      SELECT type.id,definition.code,definition.name,'TEXT',false,true,definition.sort_order
+      FROM asset_types type
+      CROSS JOIN (VALUES
+        ('departamento_fuente','Departamento informado por la fuente',10),
+        ('tipo_concesion_fuente','Tipo de concesión informado por la fuente',20)
+      ) definition(code,name,sort_order)
+      WHERE lower(type.code)='yacimiento'
+      ON CONFLICT DO NOTHING
+    `);
+    await queryRunner.query(`
+      INSERT INTO asset_attribute_definitions
+        (asset_type_id,code,name,data_type,is_required,is_active,sort_order)
+      SELECT type.id,'tipo_concesion_fuente','Tipo de concesión informado por la fuente',
+        'TEXT',false,true,40
+      FROM asset_types type
+      WHERE type.operational_role='AREA'
+      ON CONFLICT DO NOTHING
+    `);
+
+    const areaCache = new Map<string, string>();
+    const companyCache = new Map<string, string>();
+    const seenRights = new Set<string>();
+
+    for (const row of source.territory) {
+      const areaKey = row.area.trim().toLocaleLowerCase('es');
+      let areaId = areaCache.get(areaKey) ?? null;
+      if (!areaId) {
+        const areaCode = stableCode('AREA', row.area);
+        areaId = await assetIdByCode(queryRunner, areaCode);
+        if (!areaId) {
+          const inserted = (await queryRunner.query(
+            `INSERT INTO assets (
+               asset_type_id,parent_id,code,name,description,information_status,operational_status,
+               data_origin,source_name,source_reference,source_notes,current_version
+             )
+             SELECT type.id,NULL,$1::varchar,$2::varchar,$3::text,
+               'VALIDATED','IN_SERVICE','IMPORT',$4::varchar,$5::varchar,$6::text,0
+             FROM asset_types type
+             WHERE type.operational_role='AREA'
+             LIMIT 1
+             RETURNING id`,
+            [
+              areaCode,
+              row.area,
+              `Área importada desde Tablas de yacimiento. Departamento: ${row.department}.`,
+              'Tablas de yacimiento(1).xlsx',
+              `cr26e_tabla1|AREA|${row.area}`,
+              `Departamento=${row.department}; TipoConcesion=${row.concessionType}`,
+            ],
+          )) as IdRow[];
+          areaId = inserted[0]?.id ?? null;
+        }
+        if (!areaId) throw new Error(`Could not resolve area ${row.area}`);
+        areaCache.set(areaKey, areaId);
+
+        await queryRunner.query(
+          `INSERT INTO asset_source_documents (asset_id,document_id,relation_type,notes)
+           VALUES ($1::uuid,$2::uuid,'SOURCE','Importación F2.2.1 · cr26e_tabla1')
+           ON CONFLICT DO NOTHING`,
+          [areaId, territoryDocumentId],
+        );
+      }
+
+      for (const [definitionCode, value] of [
+        ['departamento', row.department],
+        ['tipo_concesion_fuente', row.concessionType],
+      ] as const) {
+        await queryRunner.query(
+          `INSERT INTO asset_attribute_values (asset_id,definition_id,value)
+           SELECT $1::uuid,definition.id,to_jsonb($3::text)
+           FROM asset_attribute_definitions definition
+           JOIN asset_types type ON type.id=definition.asset_type_id
+           WHERE type.operational_role='AREA'
+             AND lower(definition.code)=lower($2::text)
+           ON CONFLICT (asset_id,definition_id)
+           DO UPDATE SET value=EXCLUDED.value,updated_at=CURRENT_TIMESTAMP`,
+          [areaId, definitionCode, value],
+        );
+      }
+
+      const noOperator = slug(row.operator) === 'sin_empresa_operadora';
+      let companyId: string | null = null;
+      if (!noOperator) {
+        const companyKey = row.operator.trim().toLocaleLowerCase('es');
+        companyId = companyCache.get(companyKey) ?? null;
+        if (!companyId) {
+          const companyCode = stableCode('ORG', row.operator);
+          companyId = await assetIdByCode(queryRunner, companyCode);
+          if (!companyId) {
+            const inserted = (await queryRunner.query(
+              `INSERT INTO assets (
+                 asset_type_id,parent_id,code,name,description,information_status,operational_status,
+                 data_origin,source_name,source_reference,source_notes,current_version
+               )
+               SELECT type.id,NULL,$1::varchar,$2::varchar,
+                 'Organización operadora importada desde Tablas de yacimiento.',
+                 'VALIDATED','IN_SERVICE','IMPORT',$3::varchar,$4::varchar,
+                 'Empresa/Operadora informada por la fuente',0
+               FROM asset_types type
+               WHERE type.operational_role='COMPANY'
+               LIMIT 1
+               RETURNING id`,
+              [
+                companyCode,
+                row.operator,
+                'Tablas de yacimiento(1).xlsx',
+                `cr26e_tabla1|OPERADORA|${row.operator}`,
+              ],
+            )) as IdRow[];
+            companyId = inserted[0]?.id ?? null;
+          }
+          if (!companyId) throw new Error(`Could not resolve operator ${row.operator}`);
+          companyCache.set(companyKey, companyId);
+
+          await queryRunner.query(
+            `INSERT INTO organization_profiles (asset_id,organization_kind,legal_name)
+             SELECT $1::uuid,
+               CASE WHEN lower($2::text) LIKE 'ute %' OR lower($2::text) LIKE 'ute(%'
+                 THEN 'UTE'::organization_kind ELSE 'COMPANY'::organization_kind END,
+               $2::varchar
+             WHERE NOT EXISTS (SELECT 1 FROM organization_profiles WHERE asset_id=$1::uuid)`,
+            [companyId, row.operator],
+          );
+          await queryRunner.query(
+            `INSERT INTO asset_source_documents (asset_id,document_id,relation_type,notes)
+             VALUES ($1::uuid,$2::uuid,'SOURCE','Importación F2.2.1 · cr26e_tabla1')
+             ON CONFLICT DO NOTHING`,
+            [companyId, territoryDocumentId],
+          );
+        }
+
+        await queryRunner.query(
+          `INSERT INTO area_company_relations
+             (area_id,company_id,relation_role,valid_from,start_reason,source_document_id)
+           SELECT $1::uuid,$2::uuid,'OPERATOR',CURRENT_TIMESTAMP,
+             'Importado desde Tablas de yacimiento(1).xlsx',$3::uuid
+           WHERE NOT EXISTS (
+             SELECT 1 FROM area_company_relations
+             WHERE area_id=$1::uuid AND company_id=$2::uuid
+               AND relation_role='OPERATOR' AND valid_until IS NULL
+           )`,
+          [areaId, companyId, territoryDocumentId],
+        );
+      }
+
+      const fieldCode = stableCode('YAC', row.field, row.area);
+      let fieldId = await assetIdByCode(queryRunner, fieldCode);
+      if (!fieldId) {
+        // The operational context is intentionally all-or-nothing. A source row
+        // that explicitly says "Sin Empresa Operadora" stays physically under
+        // its Área but receives no fabricated operator and no half context.
+        const operationalAreaId = companyId ? areaId : null;
+        const inserted = (await queryRunner.query(
+          `INSERT INTO assets (
+             asset_type_id,parent_id,operational_area_id,operator_company_id,code,name,description,
+             information_status,operational_status,data_origin,source_name,source_reference,
+             source_notes,current_version
+           )
+           SELECT type.id,$1::uuid,$2::uuid,$3::uuid,$4::varchar,$5::varchar,$6::text,
+             'VALIDATED','IN_SERVICE','IMPORT',$7::varchar,$8::varchar,$9::text,0
+           FROM asset_types type
+           WHERE lower(type.code)='yacimiento'
+           LIMIT 1
+           RETURNING id`,
+          [
+            areaId,
+            operationalAreaId,
+            companyId,
+            fieldCode,
+            row.field,
+            `Yacimiento del Área ${row.area}.`,
+            'Tablas de yacimiento(1).xlsx',
+            `cr26e_tabla1|YACIMIENTO|${row.field}|AREA|${row.area}`,
+            `Departamento=${row.department}; TipoConcesion=${row.concessionType}; OperadoraFuente=${row.operator}`,
+          ],
+        )) as IdRow[];
+        fieldId = inserted[0]?.id ?? null;
+      }
+      if (!fieldId) throw new Error(`Could not resolve field ${row.field} / ${row.area}`);
+
+      await queryRunner.query(
+        `INSERT INTO asset_source_documents (asset_id,document_id,relation_type,notes)
+         VALUES ($1::uuid,$2::uuid,'SOURCE','Importación F2.2.1 · cr26e_tabla1')
+         ON CONFLICT DO NOTHING`,
+        [fieldId, territoryDocumentId],
+      );
+      for (const [definitionCode, value] of [
+        ['departamento_fuente', row.department],
+        ['tipo_concesion_fuente', row.concessionType],
+      ] as const) {
+        await queryRunner.query(
+          `INSERT INTO asset_attribute_values (asset_id,definition_id,value)
+           SELECT $1::uuid,definition.id,to_jsonb($3::text)
+           FROM asset_attribute_definitions definition
+           JOIN asset_types type ON type.id=definition.asset_type_id
+           WHERE lower(type.code)='yacimiento'
+             AND lower(definition.code)=lower($2::text)
+           ON CONFLICT (asset_id,definition_id)
+           DO UPDATE SET value=EXCLUDED.value,updated_at=CURRENT_TIMESTAMP`,
+          [fieldId, definitionCode, value],
+        );
+      }
+
+      const rightName = `Tipo informado: ${row.concessionType}`;
+      const rightKey = `${areaId}|${slug(row.concessionType)}`;
+      if (!seenRights.has(rightKey)) {
+        seenRights.add(rightKey);
+        const existing = (await queryRunner.query(
+          `SELECT id FROM area_legal_rights
+           WHERE area_id=$1::uuid AND lower(name)=lower($2::text)
+             AND source_document_id=$3::uuid LIMIT 1`,
+          [areaId, rightName, territoryDocumentId],
+        )) as IdRow[];
+        if (!existing[0]?.id) {
+          await queryRunner.query(
+            `INSERT INTO area_legal_rights
+               (area_id,right_type,name,status,source_document_id,notes)
+             VALUES ($1::uuid,$2::area_legal_right_type,$3::varchar,'ACTIVE',$4::uuid,$5::text)`,
+            [
+              areaId,
+              legalRightType(row.concessionType),
+              rightName,
+              territoryDocumentId,
+              `Importado desde cr26e_tabla1. Departamento: ${row.department}.`,
+            ],
+          );
+        }
+      }
+    }
+
+    const topLevelTypes = new Set(source.types.map((entry) => entry.code));
+    for (const pair of source.parentPairs) topLevelTypes.delete(pair.child);
+
+    const allTypeDefinitions = new Map(source.types.map((entry) => [entry.code, entry.name]));
+    for (const targets of Object.values(groupTargets)) {
+      for (const target of targets) {
+        if (!allTypeDefinitions.has(target)) {
+          allTypeDefinitions.set(target, target.replaceAll('_', ' '));
+        }
+      }
+    }
+
+    for (const [typeCode, typeName] of allTypeDefinitions) {
+      if (!(await assetTypeIdByCode(queryRunner, typeCode))) {
+        await queryRunner.query(
+          `INSERT INTO asset_types (code,name,description,can_be_root,is_active,operational_role)
+           VALUES ($1::varchar,$2::varchar,
+             'Tipo incorporado desde APLICACION APP(1).xlsx · F2.2.1',false,true,'GENERIC')`,
+          [typeCode, typeName],
+        );
+      }
+      await queryRunner.query(
+        `INSERT INTO finding_catalog_asset_type_profiles (asset_type_id,reason)
+         SELECT id,'Aplicabilidad inicial importada desde APLICACION APP(1).xlsx'
+         FROM asset_types WHERE lower(code)=lower($1::text)
+         ON CONFLICT (asset_type_id) DO NOTHING`,
+        [typeCode],
+      );
+    }
+
+    for (const typeCode of topLevelTypes) {
+      await queryRunner.query(
+        `INSERT INTO asset_type_parent_rules (child_type_id,parent_type_id)
+         SELECT child.id,parent.id FROM asset_types child CROSS JOIN asset_types parent
+         WHERE lower(child.code)=lower($1::text) AND lower(parent.code)='yacimiento'
+         ON CONFLICT DO NOTHING`,
+        [typeCode],
+      );
+    }
+    for (const pair of source.parentPairs) {
+      if (pair.child === pair.parent) continue;
+      await queryRunner.query(
+        `INSERT INTO asset_type_parent_rules (child_type_id,parent_type_id)
+         SELECT child.id,parent.id FROM asset_types child CROSS JOIN asset_types parent
+         WHERE lower(child.code)=lower($1::text) AND lower(parent.code)=lower($2::text)
+         ON CONFLICT DO NOTHING`,
+        [pair.child, pair.parent],
+      );
+    }
+    for (const targets of Object.values(groupTargets)) {
+      for (const typeCode of targets) {
+        await queryRunner.query(
+          `INSERT INTO asset_type_parent_rules (child_type_id,parent_type_id)
+           SELECT child.id,parent.id FROM asset_types child CROSS JOIN asset_types parent
+           WHERE lower(child.code)=lower($1::text) AND lower(parent.code)='yacimiento'
+           ON CONFLICT DO NOTHING`,
+          [typeCode],
+        );
+      }
+    }
+
+    for (const entry of source.attributes) {
+      let sortOrder = 100;
+      for (const label of entry.labels) {
+        const attributeCode = slug(label, 72);
+        const typeId = await assetTypeIdByCode(queryRunner, entry.type);
+        if (!typeId) continue;
+        const existing = (await queryRunner.query(
+          `SELECT id FROM asset_attribute_definitions
+           WHERE asset_type_id=$1::uuid AND lower(code)=lower($2::text) LIMIT 1`,
+          [typeId, attributeCode],
+        )) as IdRow[];
+        if (!existing[0]?.id) {
+          await queryRunner.query(
+            `INSERT INTO asset_attribute_definitions
+               (asset_type_id,code,name,data_type,is_required,is_active,sort_order)
+             VALUES ($1::uuid,$2::varchar,$3::varchar,'TEXT',false,true,$4::integer)`,
+            [typeId, attributeCode, label, sortOrder],
+          );
+        }
+        sortOrder += 10;
+      }
+    }
+
+    await queryRunner.query(`
+      INSERT INTO finding_categories (code,name,sort_order,is_active)
+      SELECT 'APP26','Aplicación APP 2026',260,true
+      WHERE NOT EXISTS (SELECT 1 FROM finding_categories WHERE lower(code)='app26')
+    `);
+    const categoryRows = (await queryRunner.query(
+      `SELECT id FROM finding_categories WHERE lower(code)='app26' LIMIT 1`,
+    )) as IdRow[];
+    const categoryId = categoryRows[0]?.id;
+    if (!categoryId) throw new Error('Could not resolve APP26 finding category');
+
+    const titlesByType = new Map<string, Set<string>>();
+    const addTitle = (typeCode: string, title: string): void => {
+      const cleanTitle = title.trim();
+      if (!cleanTitle || /^idem\b/i.test(cleanTitle)) return;
+      const set = titlesByType.get(typeCode) ?? new Set<string>();
+      set.add(cleanTitle);
+      titlesByType.set(typeCode, set);
+    };
+
+    for (const entry of source.directFindings) {
+      for (const title of entry.titles) addTitle(entry.type, title);
+    }
+    for (const group of source.groups) {
+      const targets = groupTargets[slug(group.name)] ?? [slug(group.name)];
+      for (const target of targets) {
+        for (const item of group.items) addTitle(target, item.title);
+      }
+    }
+    for (let pass = 0; pass < 3; pass += 1) {
+      for (const entry of source.idemRefs) {
+        for (const rawReference of entry.refs) {
+          const reference = referenceAliases[slug(rawReference)] ?? slug(rawReference);
+          for (const title of titlesByType.get(reference) ?? []) addTitle(entry.type, title);
+        }
+      }
+    }
+
+    const allTitles = [
+      ...new Set([...titlesByType.values()].flatMap((titles) => [...titles])),
+    ].sort((a, b) => a.localeCompare(b, 'es'));
+
+    const itemIdByTitle = new Map<string, string>();
+    let sourceNumber = 1;
+    for (const title of allTitles) {
+      const itemCode = `APP26-${createHash('sha1').update(title).digest('hex').slice(0, 12).toUpperCase()}`;
+      let itemRows = (await queryRunner.query(
+        `SELECT id FROM finding_catalog_items WHERE lower(code)=lower($1::text) LIMIT 1`,
+        [itemCode],
+      )) as IdRow[];
+      if (!itemRows[0]?.id) {
+        itemRows = (await queryRunner.query(
+          `INSERT INTO finding_catalog_items
+             (category_id,code,source_number,title,import_note,revision,is_active)
+           VALUES ($1::uuid,$2::varchar,$3::integer,$4::varchar,
+             'Importado desde APLICACION APP(1).xlsx · F2.2.1',1,true)
+           RETURNING id`,
+          [categoryId, itemCode, sourceNumber, title],
+        )) as IdRow[];
+      }
+      const itemId = itemRows[0]?.id;
+      if (itemId) {
+        itemIdByTitle.set(title, itemId);
+        await queryRunner.query(
+          `INSERT INTO finding_catalog_item_versions
+             (item_id,revision,snapshot,actor_username)
+           SELECT item.id,item.revision,
+             jsonb_build_object(
+               'id',item.id,'categoryId',category.id,'categoryCode',category.code,
+               'categoryName',category.name,'code',item.code,'sourceNumber',item.source_number,
+               'title',item.title,'legalBasis',item.legal_basis,'glossary',item.glossary,
+               'importNote',item.import_note,'revision',item.revision,'isActive',item.is_active
+             ),
+             'migration:F2.2.1'
+           FROM finding_catalog_items item
+           JOIN finding_categories category ON category.id=item.category_id
+           WHERE item.id=$1::uuid
+             AND NOT EXISTS (
+               SELECT 1 FROM finding_catalog_item_versions version
+               WHERE version.item_id=item.id AND version.revision=item.revision
+             )`,
+          [itemId],
+        );
+      }
+      sourceNumber += 1;
+    }
+
+    for (const [typeCode, titles] of titlesByType) {
+      const assetTypeId = await assetTypeIdByCode(queryRunner, typeCode);
+      if (!assetTypeId) continue;
+      for (const title of titles) {
+        const catalogItemId = itemIdByTitle.get(title);
+        if (!catalogItemId) continue;
+        await queryRunner.query(
+          `INSERT INTO finding_catalog_item_asset_types (catalog_item_id,asset_type_id)
+           VALUES ($1::uuid,$2::uuid) ON CONFLICT DO NOTHING`,
+          [catalogItemId, assetTypeId],
+        );
+      }
+    }
+
+    await queryRunner.query(
+      `INSERT INTO asset_source_documents (asset_id,document_id,relation_type,notes)
+       SELECT asset.id,$1::uuid,'MENTIONS',
+         'El tipo/hallazgo del elemento se definió con la matriz APLICACION APP(1).xlsx'
+       FROM assets asset
+       JOIN asset_types type ON type.id=asset.asset_type_id
+       WHERE lower(type.code)='yacimiento'
+       ON CONFLICT DO NOTHING`,
+      [appDocumentId],
+    );
+
+    const importedYacimientos = (await queryRunner.query(
+      `SELECT count(*)::text AS total
+       FROM assets asset JOIN asset_types type ON type.id=asset.asset_type_id
+       WHERE asset.data_origin='IMPORT'
+         AND asset.source_name='Tablas de yacimiento(1).xlsx'
+         AND lower(type.code)='yacimiento'`,
+    )) as CountRow[];
+    if (Number(importedYacimientos[0]?.total ?? 0) !== source.territory.length) {
+      throw new Error(
+        `F2.2.1 verification failed: expected ${source.territory.length} imported yacimientos, found ${importedYacimientos[0]?.total ?? '0'}`,
+      );
+    }
+
+    const importedCatalog = (await queryRunner.query(
+      `SELECT count(*)::text AS total
+       FROM finding_catalog_items item
+       JOIN finding_categories category ON category.id=item.category_id
+       WHERE lower(category.code)='app26'`,
+    )) as CountRow[];
+    if (Number(importedCatalog[0]?.total ?? 0) !== allTitles.length) {
+      throw new Error(
+        `F2.2.1 verification failed: expected ${allTitles.length} APP26 findings, found ${importedCatalog[0]?.total ?? '0'}`,
+      );
+    }
+
+    const halfContexts = (await queryRunner.query(
+      `SELECT count(*)::text AS total FROM assets
+       WHERE (operational_area_id IS NULL) <> (operator_company_id IS NULL)`,
+    )) as CountRow[];
+    if (Number(halfContexts[0]?.total ?? 0) !== 0) {
+      throw new Error('F2.2.1 verification failed: an imported asset has an incomplete operational context');
+    }
+
+    // eslint-disable-next-line no-console
+    console.log(
+      `[F2.2.1] imported territory=${source.territory.length}; yacimientos=${importedYacimientos[0]?.total}; catalog=${importedCatalog[0]?.total}; halfContexts=0`,
+    );
+  }
+
+  public async down(): Promise<void> {
+    throw new Error(
+      'F2.2.1 contiene maestros territoriales y catálogo de referencia; no se revierte destructivamente. Restaurar backup PRE si fuera necesario.',
+    );
+  }
+}

api-v3/src/database/migrations/1789758000000-phase-f2-2-1-reference-baselines.ts

@@ -0,0 +1,57 @@
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+export class PhaseF221ReferenceBaselines1789758000000 implements MigrationInterface {
+  name = 'PhaseF221ReferenceBaselines1789758000000';
+
+  public async up(queryRunner: QueryRunner): Promise<void> {
+    await queryRunner.query(`
+      INSERT INTO asset_versions (
+        asset_id,version_number,change_type,changed_fields,snapshot,source
+      )
+      SELECT
+        asset.id,
+        1,
+        'BASELINE',
+        ARRAY['assetTypeId','parentId','operationalAreaId','operatorCompanyId','code','name','informationStatus','operationalStatus','dataOrigin','sourceName','sourceReference'],
+        jsonb_build_object(
+          'id',asset.id,
+          'assetTypeId',asset.asset_type_id,
+          'parentId',asset.parent_id,
+          'operationalAreaId',asset.operational_area_id,
+          'operatorCompanyId',asset.operator_company_id,
+          'code',asset.code,
+          'name',asset.name,
+          'commonName',asset.common_name,
+          'description',asset.description,
+          'informationStatus',asset.information_status,
+          'operationalStatus',asset.operational_status,
+          'dataOrigin',asset.data_origin,
+          'sourceName',asset.source_name,
+          'sourceReference',asset.source_reference,
+          'sourceNotes',asset.source_notes
+        ),
+        'SYSTEM'
+      FROM assets asset
+      WHERE asset.data_origin='IMPORT'
+        AND asset.source_name='Tablas de yacimiento(1).xlsx'
+        AND NOT EXISTS (
+          SELECT 1 FROM asset_versions version
+          WHERE version.asset_id=asset.id AND version.version_number=1
+        )
+    `);
+    await queryRunner.query(`
+      UPDATE assets asset
+      SET current_version=GREATEST(asset.current_version,1),updated_at=CURRENT_TIMESTAMP
+      WHERE asset.data_origin='IMPORT'
+        AND asset.source_name='Tablas de yacimiento(1).xlsx'
+        AND EXISTS (
+          SELECT 1 FROM asset_versions version
+          WHERE version.asset_id=asset.id AND version.version_number=1
+        )
+    `);
+  }
+
+  public async down(): Promise<void> {
+    throw new Error('F2.2.1 no elimina baselines históricos del maestro importado. Restaurar backup PRE si fuera necesario.');
+  }
+}

api-v3/src/version.ts

@@ -1,2 +1,2 @@
-export const API_VERSION = '0.22.0-1';
-export const API_PHASE = 'F2.2';
+export const API_VERSION = '0.22.1-1';
+export const API_PHASE = 'F2.2.1';

api-v3/test/f2-2-1-reference-excel-data.test.ts

@@ -0,0 +1,49 @@
+import assert from 'node:assert/strict';
+import { test } from 'node:test';
+import { loadF221ExcelSource } from '../src/reference-data/f2-2-1-excel-source';
+
+const source = loadF221ExcelSource();
+
+test('F2.2.1 preserves every normalized territorial source row', () => {
+  assert.equal(source.territory.length, 230);
+  assert.equal(new Set(source.territory.map((row) => row.field)).size, 220);
+  assert.equal(new Set(source.territory.map((row) => `${row.field.toLowerCase()}|${row.area.toLowerCase()}`)).size, 230);
+});
+
+test('F2.2.1 does not collapse repeated field names across different operational contexts', () => {
+  const byName = new Map<string, Set<string>>();
+  for (const row of source.territory) {
+    const key = row.field.toLowerCase();
+    const contexts = byName.get(key) ?? new Set<string>();
+    contexts.add(`${row.area}|${row.operator}`);
+    byName.set(key, contexts);
+  }
+  assert.ok([...byName.values()].some((contexts) => contexts.size > 1));
+});
+
+test('F2.2.1 keeps the APP matrix as configuration rather than physical inventory rows', () => {
+  assert.equal(source.types.length, 113);
+  assert.equal(source.parentPairs.length, 119);
+  assert.equal(source.attributes.length, 44);
+  assert.equal(source.idemRefs.length, 25);
+  assert.ok(source.types.some((entry) => entry.code === 'tanque'));
+  assert.ok(source.types.some((entry) => entry.code === 'bateria'));
+});
+
+test('F2.2.1 keeps IDEM as inheritance metadata, never as a catalog title', () => {
+  const directTitles = source.directFindings.flatMap((entry) => entry.titles);
+  assert.equal(directTitles.some((title) => /^idem\b/i.test(title.trim())), false);
+  assert.ok(source.idemRefs.some((entry) => entry.type === 'tanque'));
+});
+
+test('F2.2.1 imports the grouped Hallazgo catalog supplied in Hoja2', () => {
+  assert.equal(source.groups.length, 19);
+  assert.equal(source.groups.reduce((total, group) => total + group.items.length, 0), 171);
+  const tanks = source.groups.find((group) => group.name.trim().toUpperCase() === 'TANQUES');
+  assert.ok(tanks);
+  assert.ok(tanks.items.some((item) => /ESTADO DEL TANQUE/i.test(item.title)));
+});
+
+test('F2.2.1 preserves rows explicitly reporting no operator without fabricating an organization in source data', () => {
+  assert.ok(source.territory.some((row) => row.operator.toLowerCase().includes('sin empresa operadora')));
+});