test(F6.1): lock area-owned DB context constraint

.github/workflows/ci.yml

@@ -116,9 +116,9 @@ jobs:
 
           docker compose --env-file .env.example --profile tools run --rm migrate
 
-          # F5.1 intentionally ends with zero operational/domain instances. The
+          # F5.1 performs the historical clean start. F6.1 then seeds only the
-          # technical family and finding masters remain, but territory preload,
+          # presentation territory approved by DH: 7 Departamentos, 64 Áreas,
-          # imports, applicability links and old audits are deliberately gone.
+          # 230 Yacimientos and 12 real Empresas.
           docker compose --env-file .env.example exec -T db \
             psql -v ON_ERROR_STOP=1 -U dhv2_owner -d dhv2 <<'SQL'
           DO $$
@@ -128,6 +128,7 @@ jobs:
             audits integer;
             applicability integer;
             territory_sources integer;
+            presentation_sources integer;
             source_installations integer;
             source_subinstallations integer;
             source_findings integer;
@@ -149,18 +150,18 @@ jobs:
             END IF;
 
             SELECT COUNT(*) INTO domain_assets FROM assets;
-            IF domain_assets <> 0 THEN
+            IF domain_assets <> 313 THEN
-              RAISE EXCEPTION 'F5.1 clean start must contain 0 Assets, got %', domain_assets;
+              RAISE EXCEPTION 'F6.1 presentation preload must contain 313 Assets, got %', domain_assets;
             END IF;
 
             SELECT COUNT(*) INTO audits FROM audit_events;
             IF audits <> 0 THEN
-              RAISE EXCEPTION 'F5.1 clean start must contain 0 audit events, got %', audits;
+              RAISE EXCEPTION 'F6.1 presentation preload must start with 0 audit events, got %', audits;
             END IF;
 
             SELECT COUNT(*) INTO applicability FROM finding_catalog_item_inventory_families;
             IF applicability <> 0 THEN
-              RAISE EXCEPTION 'F5.1 clean start must contain 0 finding applicability links, got %', applicability;
+              RAISE EXCEPTION 'Clean migration rehearsal must contain 0 finding applicability links, got %', applicability;
             END IF;
 
             SELECT COUNT(*) INTO territory_sources
@@ -170,13 +171,20 @@ jobs:
               RAISE EXCEPTION 'F5.1 must remove the old territory source preload, got % rows', territory_sources;
             END IF;
 
+            SELECT COUNT(*) INTO presentation_sources
+            FROM source_documents
+            WHERE document_number='DH-F6.1-PRESENTATION-TERRITORY-20260909';
+            IF presentation_sources <> 1 THEN
+              RAISE EXCEPTION 'F6.1 must contain exactly one presentation territory source, got %', presentation_sources;
+            END IF;
+
             SELECT COUNT(*) FILTER (WHERE level='INSTALLATION'),
                    COUNT(*) FILTER (WHERE level='SUBINSTALLATION')
             INTO source_installations,source_subinstallations
             FROM inventory_families
             WHERE is_active=true AND source_reference LIKE 'F5:final_modelov2.xlsx%';
             IF source_installations <> 14 OR source_subinstallations <> 109 THEN
-              RAISE EXCEPTION 'F5.1 must preserve technical family masters: installations %, subinstallations %', source_installations,source_subinstallations;
+              RAISE EXCEPTION 'F6.1 must preserve technical family masters: installations %, subinstallations %', source_installations,source_subinstallations;
             END IF;
 
             SELECT COUNT(*) INTO source_findings
@@ -184,7 +192,7 @@ jobs:
             JOIN finding_categories category ON category.id=item.category_id
             WHERE lower(category.code)='f5model' AND item.is_active=true;
             IF source_findings <> 177 THEN
-              RAISE EXCEPTION 'F5.1 must preserve finding master catalog, got %', source_findings;
+              RAISE EXCEPTION 'F6.1 must preserve finding master catalog, got %', source_findings;
             END IF;
 
             SELECT COUNT(*) INTO department_types
@@ -205,13 +213,13 @@ jobs:
           END $$;
           SQL
 
-          # F5.1 is intentionally one-way: production rollback is the PRE database
+          # Both destructive cuts are intentionally one-way: production rollback is
-          # backup, not migration:revert. Prove instead that the completed chain is
+          # the PRE database backup, not migration:revert. Prove the completed chain
-          # idempotent and has no pending migration on a second run.
+          # is idempotent and has no pending migration on a second run.
           rerun_log="$(mktemp)"
           docker compose --env-file .env.example --profile tools run --rm migrate 2>&1 | tee "$rerun_log"
           grep -Eq 'No pending migrations|Applied migrations: 0' "$rerun_log" || {
-            echo "ERROR: F5.1 migration chain is not idempotent." >&2
+            echo "ERROR: F6.1 migration chain is not idempotent." >&2
             cat "$rerun_log" >&2
             exit 1
           }

api-v3/src/database/migrations/1790094500000-f6-1-area-owned-operational-context-check.ts

@@ -0,0 +1,35 @@
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+/**
+ * F5/F6 permits physical Inventory to belong to an Área without persisting a
+ * Company snapshot. Company snapshots are optional creation/history metadata;
+ * current ownership is resolved from area_company_relations.
+ *
+ * The F4 paired CHECK survived the trigger migration and contradicted that
+ * model. Keep only the invariant that a Company snapshot cannot exist without
+ * an Área.
+ */
+export class F61AreaOwnedOperationalContextCheck1790094500000 implements MigrationInterface {
+  name='F61AreaOwnedOperationalContextCheck1790094500000';
+
+  public async up(queryRunner:QueryRunner):Promise<void>{
+    await queryRunner.query('ALTER TABLE assets DROP CONSTRAINT IF EXISTS chk_assets_operational_assignment_pair');
+    await queryRunner.query(`
+      ALTER TABLE assets
+      ADD CONSTRAINT chk_assets_operational_assignment_pair
+      CHECK (operator_company_id IS NULL OR operational_area_id IS NOT NULL)
+    `);
+  }
+
+  public async down(queryRunner:QueryRunner):Promise<void>{
+    await queryRunner.query('ALTER TABLE assets DROP CONSTRAINT IF EXISTS chk_assets_operational_assignment_pair');
+    await queryRunner.query(`
+      ALTER TABLE assets
+      ADD CONSTRAINT chk_assets_operational_assignment_pair
+      CHECK (
+        (operational_area_id IS NULL AND operator_company_id IS NULL)
+        OR (operational_area_id IS NOT NULL AND operator_company_id IS NOT NULL)
+      )
+    `);
+  }
+}

api-v3/src/database/migrations/1790094600000-f6-1-presentation-territory-reset.ts

@@ -0,0 +1,163 @@
+import { createHash } from 'node:crypto';
+import { MigrationInterface, QueryRunner } from 'typeorm';
+import { F61_PRESENTATION_TERRITORY_SOURCE as SOURCE } from '../../reference-data/f6-1-presentation-territory-source';
+
+type IdRow={id:string};
+const NO_OPERATOR='Sin Empresa Operadora';
+const DOC='DH-F6.1-PRESENTATION-TERRITORY-20260909';
+
+const key=(value:string)=>value.normalize('NFD').replace(/[\u0300-\u036f]/g,'').toLowerCase()
+  .replace(/[^a-z0-9]+/g,' ').trim().replace(/\s+/g,' ');
+const code=(prefix:string,value:string,length=12)=>
+  `${prefix}-${createHash('sha1').update(value).digest('hex').slice(0,length).toUpperCase()}`;
+const uniqueBy=<T>(rows:T[],identity:(row:T)=>string)=>{
+  const seen=new Set<string>(); return rows.filter((row)=>{const id=identity(row);if(seen.has(id))return false;seen.add(id);return true;});
+};
+
+export class F61PresentationTerritoryReset1790094600000 implements MigrationInterface {
+  name='F61PresentationTerritoryReset1790094600000';
+
+  public async up(q:QueryRunner):Promise<void>{
+    const rows=SOURCE.rows;
+    if(SOURCE.file!=='Tablas de yacimiento y areas(1).xlsx'||SOURCE.sheet!=='cr26e_tabla1'
+      ||SOURCE.sha256!=='afc8991eed0c0175cf121b6e11e6ca7cc5459ba371c5a186966706e386033cdb'||rows.length!==230)
+      throw new Error('F6.1 presentation source contract mismatch');
+
+    const areas=uniqueBy(rows,(r)=>key(r.area));
+    const pairs=uniqueBy(rows,(r)=>`${key(r.area)}|${key(r.yacimiento)}`);
+    const departments=uniqueBy(rows,(r)=>key(r.departamento));
+    const companies=[...new Set(rows.map((r)=>r.empresaOperadora.trim())
+      .filter((name)=>name&&key(name)!==key(NO_OPERATOR)))].sort((a,b)=>a.localeCompare(b,'es'));
+    if(areas.length!==64||pairs.length!==230||departments.length!==7||companies.length!==12
+      ||areas.filter((r)=>key(r.empresaOperadora)!==key(NO_OPERATOR)).length!==47)
+      throw new Error('F6.1 presentation source cardinality mismatch');
+
+    const concessions=new Set(rows.map((r)=>r.tipoConcesion.trim()));
+    if(concessions.size!==2||!concessions.has('Exploración')||!concessions.has('Explotación'))
+      throw new Error('F6.1 presentation concession contract mismatch');
+    for(const area of areas){
+      const same=rows.filter((r)=>key(r.area)===key(area.area));
+      for(const field of ['departamento','tipoConcesion','empresaOperadora'] as const)
+        if(new Set(same.map((r)=>key(r[field]))).size!==1) throw new Error(`Conflicting ${field}: ${area.area}`);
+    }
+
+    const companyType=await this.type(q,
+      `SELECT id FROM asset_types WHERE operational_role='COMPANY' AND is_active=true ORDER BY (lower(code)='empresa') DESC,created_at LIMIT 1`);
+    const departmentType=await this.type(q,
+      `SELECT id FROM asset_types WHERE lower(code)='departamento' AND is_active=true AND can_be_root=true LIMIT 1`);
+    const areaType=await this.type(q,
+      `SELECT id FROM asset_types WHERE lower(code)='area' AND operational_role='AREA' AND is_active=true LIMIT 1`);
+    const yacimientoType=await this.type(q,
+      `SELECT id FROM asset_types WHERE lower(code)='yacimiento' AND is_active=true LIMIT 1`);
+    const [rules]=await q.query(`
+      SELECT COUNT(*)::integer AS total FROM asset_type_parent_rules rule
+      JOIN asset_types child ON child.id=rule.child_type_id JOIN asset_types parent ON parent.id=rule.parent_type_id
+      WHERE (lower(child.code)='area' AND lower(parent.code)='departamento')
+         OR (lower(child.code)='yacimiento' AND lower(parent.code)='area')`);
+    if(Number(rules?.total)!==2) throw new Error('F6.1 canonical hierarchy rules are incomplete');
+
+    // Destructive clean start requested for the presentation. Users, RBAC,
+    // technical families, Finding masters and system configuration are preserved.
+    // Recovery is the automatic PRE database backup made by deploy-github.sh.
+    await q.query('TRUNCATE TABLE assets CASCADE');
+    await q.query('TRUNCATE TABLE administrative_departments CASCADE');
+    await q.query('TRUNCATE TABLE source_documents CASCADE');
+    await q.query('TRUNCATE TABLE audit_events');
+    await q.query(`DO $$ DECLARE t text; BEGIN FOR t IN SELECT tablename FROM pg_tables
+      WHERE schemaname=current_schema() AND tablename LIKE 'asset_import_%'
+      LOOP EXECUTE format('TRUNCATE TABLE %I CASCADE',t); END LOOP; END $$;`);
+
+    const [doc]=(await q.query(`INSERT INTO source_documents(document_type,document_number,title,issuer,external_reference,notes)
+      VALUES('SPREADSHEET',$1,$2,'Dirección de Hidrocarburos',$3,$4) RETURNING id`,
+      [DOC,SOURCE.file,`sha256:${SOURCE.sha256}`,`F6.1 · ${SOURCE.sheet} · ${rows.length} filas`])) as IdRow[];
+    if(!doc?.id) throw new Error('Could not create F6.1 presentation source');
+
+    const companyIds=new Map<string,string>();
+    for(const name of companies){
+      const id=await this.asset(q,companyType,null,null,code('PRES-ORG',key(name)),name,
+        'Empresa/Operadora del corte de presentación.',`F6.1:PRESENTATION:COMPANY:${code('SRC',key(name),10)}`,'Empresa');
+      companyIds.set(key(name),id);
+      await q.query(`INSERT INTO organization_profiles(asset_id,organization_kind,legal_name)
+        VALUES($1::uuid,$2::organization_kind,$3)`,[id,name.toUpperCase().startsWith('UTE (')?'UTE':'COMPANY',name]);
+      await this.link(q,id,doc.id,'Empresa/Operadora');
+    }
+
+    const departmentIds=new Map<string,string>();
+    for(const row of departments.sort((a,b)=>a.departamento.localeCompare(b.departamento,'es'))){
+      const id=await this.asset(q,departmentType,null,null,code('PRES-DEP',key(row.departamento)),row.departamento,
+        'Departamento administrativo raíz F6.1.',`F6.1:PRESENTATION:DEPARTMENT:${code('SRC',key(row.departamento),10)}`,'Departamento');
+      departmentIds.set(key(row.departamento),id); await this.link(q,id,doc.id,'Departamento');
+    }
+
+    const areaIds=new Map<string,string>();
+    for(const row of areas.sort((a,b)=>a.area.localeCompare(b.area,'es'))){
+      const parent=departmentIds.get(key(row.departamento)); if(!parent)throw new Error(`Missing Departamento ${row.departamento}`);
+      const id=await this.asset(q,areaType,parent,null,code('PRES-AREA',key(row.area)),row.area,
+        `Área del Departamento ${row.departamento}.`,`F6.1:PRESENTATION:AREA:${code('SRC',key(row.area),10)}`,'Área');
+      areaIds.set(key(row.area),id); await this.link(q,id,doc.id,`Área · ${row.departamento}`);
+      await q.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,'ACTIVE',$4::uuid,$5)`,[
+          id,row.tipoConcesion==='Exploración'?'EXPLORATION_PERMIT':'EXPLOITATION_CONCESSION',
+          `${row.tipoConcesion} · ${row.area}`,doc.id,`F6.1 · ${SOURCE.file}`]);
+      if(key(row.empresaOperadora)!==key(NO_OPERATOR)){
+        const company=companyIds.get(key(row.empresaOperadora)); if(!company)throw new Error(`Missing Empresa ${row.empresaOperadora}`);
+        await q.query(`INSERT INTO area_company_relations(area_id,company_id,relation_role,source_document_id,valid_from,start_reason)
+          VALUES($1::uuid,$2::uuid,'OPERATOR',$3::uuid,CURRENT_TIMESTAMP,$4)`,
+          [id,company,doc.id,`F6.1 · operadora vigente según ${SOURCE.file}`]);
+      }
+    }
+
+    for(const row of pairs){
+      const area=areaIds.get(key(row.area)); if(!area)throw new Error(`Missing Área ${row.area}`);
+      const id=await this.asset(q,yacimientoType,area,area,code('PRES-YAC',`${key(row.area)}|${key(row.yacimiento)}`),
+        row.yacimiento,`Yacimiento del Área ${row.area}.`,
+        `F6.1:PRESENTATION:YAC:${code('SRC',`${key(row.area)}|${key(row.yacimiento)}`,12)}`,`${SOURCE.sheet} · fila ${row.sourceRow}`);
+      await this.link(q,id,doc.id,`Yacimiento · ${row.area} · fila ${row.sourceRow}`);
+    }
+
+    const [c]=await q.query(`SELECT
+      (SELECT COUNT(*) FROM assets)::integer total_assets,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE t.operational_role='COMPANY')::integer companies,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE lower(t.code)='departamento')::integer departments,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE lower(t.code)='area')::integer areas,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE lower(t.code)='yacimiento')::integer yacimientos,
+      (SELECT COUNT(*) FROM area_legal_rights WHERE status='ACTIVE')::integer legal_rights,
+      (SELECT COUNT(*) FROM area_legal_rights WHERE status='ACTIVE' AND right_type='EXPLORATION_PERMIT')::integer exploration_rights,
+      (SELECT COUNT(*) FROM area_legal_rights WHERE status='ACTIVE' AND right_type='EXPLOITATION_CONCESSION')::integer exploitation_rights,
+      (SELECT COUNT(*) FROM area_company_relations WHERE relation_role='OPERATOR' AND valid_until IS NULL)::integer operators,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id WHERE lower(t.code)='area'
+        AND NOT EXISTS(SELECT 1 FROM area_company_relations r WHERE r.area_id=a.id AND r.relation_role='OPERATOR' AND r.valid_until IS NULL))::integer areas_without_operator,
+      (SELECT COUNT(*) FROM assets a JOIN asset_types t ON t.id=a.asset_type_id LEFT JOIN assets p ON p.id=a.parent_id
+        LEFT JOIN asset_types pt ON pt.id=p.asset_type_id WHERE lower(t.code)='area' AND (p.id IS NULL OR lower(pt.code)<>'departamento'))::integer invalid_area_parents,
+      (SELECT COUNT(*) FROM assets y JOIN asset_types t ON t.id=y.asset_type_id LEFT JOIN assets a ON a.id=y.parent_id
+        LEFT JOIN asset_types at ON at.id=a.asset_type_id WHERE lower(t.code)='yacimiento'
+        AND (a.id IS NULL OR lower(at.code)<>'area' OR y.operational_area_id IS DISTINCT FROM a.id))::integer invalid_yacimiento_contexts,
+      (SELECT COUNT(*) FROM asset_source_documents WHERE document_id=$1::uuid)::integer source_links,
+      (SELECT COUNT(*) FROM source_documents WHERE document_number=$2)::integer source_documents`,[doc.id,DOC]);
+    const expected:Record<string,number>={total_assets:313,companies:12,departments:7,areas:64,yacimientos:230,
+      legal_rights:64,exploration_rights:18,exploitation_rights:46,operators:47,areas_without_operator:17,
+      invalid_area_parents:0,invalid_yacimiento_contexts:0,source_links:313,source_documents:1};
+    for(const [field,value] of Object.entries(expected))
+      if(Number(c?.[field]??-1)!==value)throw new Error(`F6.1 seed verification failed: ${field}=${c?.[field]} expected=${value}`);
+  }
+
+  public async down():Promise<void>{
+    throw new Error('F6.1 presentation reset is destructive. Restore the PRE deploy database backup.');
+  }
+
+  private async type(q:QueryRunner,sql:string):Promise<string>{
+    const rows=(await q.query(sql)) as IdRow[]; if(!rows[0]?.id)throw new Error(`Missing F6.1 master type: ${sql}`); return rows[0].id;
+  }
+  private async asset(q:QueryRunner,typeId:string,parentId:string|null,areaId:string|null,assetCode:string,name:string,
+    description:string,sourceReference:string,sourceNotes:string):Promise<string>{
+    const [row]=(await q.query(`INSERT INTO assets(asset_type_id,parent_id,operational_area_id,operator_company_id,inventory_family_id,
+      code,name,description,information_status,operational_status,data_origin,source_name,source_reference,source_notes,is_inventory_instance)
+      VALUES($1::uuid,$2::uuid,$3::uuid,NULL,NULL,$4,$5,$6,'VALIDATED','UNKNOWN','PROVIDED_DOCUMENT',$7,$8,$9,false) RETURNING id`,
+      [typeId,parentId,areaId,assetCode,name,description,SOURCE.file,sourceReference,sourceNotes])) as IdRow[];
+    if(!row?.id)throw new Error(`Could not seed ${name}`); return row.id;
+  }
+  private async link(q:QueryRunner,assetId:string,documentId:string,notes:string):Promise<void>{
+    await q.query(`INSERT INTO asset_source_documents(asset_id,document_id,relation_type,notes)
+      VALUES($1::uuid,$2::uuid,'SOURCE',$3)`,[assetId,documentId,notes]);
+  }
+}

api-v3/src/reference-data/f6-1-presentation-territory-source.ts

@@ -0,0 +1,30 @@
+import { gunzipSync } from 'node:zlib';
+
+export interface F61PresentationTerritoryRow {
+  sourceRow: number;
+  yacimiento: string;
+  area: string;
+  departamento: string;
+  tipoConcesion: string;
+  empresaOperadora: string;
+}
+
+/**
+ * F6.1 · fuente única del corte de presentación.
+ * Archivo: Tablas de yacimiento y areas(1).xlsx
+ * Hoja: cr26e_tabla1
+ * SHA-256: afc8991eed0c0175cf121b6e11e6ca7cc5459ba371c5a186966706e386033cdb
+ *
+ * Las 230 filas se guardan comprimidas para versionar exactamente la fuente
+ * aprobada sin depender del binario Excel durante CI o deploy.
+ */
+const SNAPSHOT_GZIP_BASE64 = 'H4sIADTzoWoC/+2d23IiuRmAX0Xli1RSNeOiwZzmrmFY21OAWfBStZvNhdzIWI6QvN0tZ00qj5CLPMI+wtbepXLnF4vU+NA6AbPLINXgOyNh+PpH0n/Qr19//edRxniaoDH7x9GH6rujB5jgBUY0Z0cfjrrw8Vc4g2DGU3j07gimCIrWKV4mMLlBKcxE4wzdwTSHi6d/mUAKujAlTPbl+I51GU1QhhkVnb2f7wjLxTc8/kZFN1rcpSiDF3fio2YslZ89Qnn6+BtBLAMTPhOfmuIEUvGqR1E6fwCT4/j46F/vFOqaRj3iNBfMCBCYgQ5MZwXnE3wfgnvxPkihyT7G9+Jp7zHcG/qJhi5oU0iTMnC5SePt89vHX6h80i5/YHuDrmvQ380FFBIDImTohgYdzzkEHfE9s9LIVhs18AEkMJ0//g/9LubxRffMCtbUwYj4cAIpTjASUiJgBB84LTG6+neK+91lD/z52+/Oh5fxMAa9P41APD7tDS/PxavJ8fi4f/wOvHYPe+PT78H5cNqbXA7EuybFk74Dp7H46/JiHJ/2QDz8CAbnHyeX4148AJPeeHre7T2/sXsxGMWP/3n8dwwuJ52i8S+6oFr64oTSlIE+5nc8gW/y+dC2ymeI5il7k86HqOIWj1hF0jcRfYgifSkSD4uznzguCafU5F17RlUXsPaLBgVdM4ysNMVLSKRKjK/gLQsVXDdWegR8y9FCfGaoxLql0meFQShQCc5ZFip2wzBoUZYzEJNrmAYra92OucRigZUGONEM2rCwW+uxCwUwQDMcrNzbGx5gyIRxHih8tWIs4FzI20CWjQVBuiJ4eYfF+RzDa4iI6xHSdY8wwRT0Vs3gtV1njqzMmsJ5anqlZnTnuGcwz4WQ/16W8MSUcdWyCI5Yoi+AQcm4Zl8BB1h4lYrDFhb2iXs4g5cZxKjOr71hBfHZ/Gtn5yWkc7Z2KtZdoQhwIWQftG9fbTjZlWkZILmuNfucEhm+ev9sFQZN3/o83RPgExhOswxs8ry0xJRads88RZRhYo9qGg6rcA2KbyPimbrwChJSXsFFt1z+zP79hLFqkQt3wAimMBzQqjUQ0GEEwRkq29e+QWuG4l6IOTVCRFhpYlQri7JvVl3zjTFNxM8fL2CKBUk4oHVjmJIFTxTA54bPJfrjBkStYd2AcQhSdklZlvq+9hBVrWmdvE+jaYhn6op4iBJqmVGa7g0W1t/b4Gmbohnh5AbSspl1kKI5qbhFo9rQhyke3bjpQ9BHyQ1fwkOXjG5NjaB4WpYevFyMCIZ0hZbC2z/0VfjEYSoWPuPBC6duF44wcHpZAgmjBz+xDBMZXQtPn5ZHzmuL/wSfpqk5xC8p1gGsBBlCQm7Zg6+jFKM8VDHbkx8+MjFDwCcOlWlj6dq/q1c3tj5ojhMZKSFAOvogzlN4Lz5iZqB/I1QJFwLZ+XT/fvSNPeErskq3zOEZsLoeUA9h+8KsbcBULW1PkCeOvRdIM6FjAhiNuo6cMiIWCDCBMu1zIDRhAIwN6y89YEKAqR4WL/ftGnDUHdsBm/ZfecxuzR0fL4CtdRIMAdBQOTdQOMw3HFFtHuvtexmBjYqJJzSe+HdwJjdp1EiZrU/DvOR3nM5hzr7M/mPD0DE3+Ibj2Sq4B8Xs1nYRHN1fu/3bqG4jJ9AVvTK98U1gjdrnCGzyJrDGyVYC05XYoUqrvpW0NI1woLIy8zRuGZD5SH12peojtwy/eiE1dzSgDkVerW3lVYTQ3oRWCM22FSYWdEb1vIIDFlLTsin2A1RDjYcsnsgZq+4ymWP/JqZCTNUNYnpbyl9EZY+PSQNhgAjBy3U2QpF6WJzngvvysZu6pfwRZdeYFGlkoAMfWFi09Q20G5Z+H8gNcwUesDQwuTYtuhTiDNLAOHVD6ZTDmZL5EgBj29wu6zKiboL4x2xVLFkPcmQGhhnZg7xnKKWQztAyLNqqY+OBrY/8+UB1nE8ZQcIWYc36lhHNGYLp+TmIS5SvLTvdB3UiGSGT4ftpGad4tVOU7Y5KtYxdG0avCUc0wbqVZnTs9MfcGC1v6Tqnw8UCNEbkni9eKaW2hHQJ6c73vdacW2i1DDND6EOU4/I+sR+ytuUk3IDNWY4yz2jtigVtta/pn03XIgPpkVzeIOydzHCtIP2Jizd3fQ+1ds1w+hg4lYcXkW8y44yicLGE7d9lUlehxLvk6na+lRNIecK8jzqbU/JC4ZWs6STTd8z98LXcfIp69UNni4nCTD1144UsqhjqAXPQ5UQYxt7RIssptefia73sTuiKzDujw7MQzuW3Ql1A73w1ZzRe22V96rCeEP8KSzZZKs5MWEaRUnJGbdztsXlX8k9UqZtl28QiphyyOmVX0tEWHwXiB0h27vKNCM/uUJ4y17CyJ6GdwQVMjP1777D2k31nHFJOAsJsrcFUlZh3VIs+67I0YVcBjdGo4oQMbYQa5dE+QfldghdmhcYbYTRLoRZF905d3Y46qKEb1bZaDOyPsndYXUt9//JCDOWzCUAUdBHoIIqucSL8wE/dUXDPUDenoaX6XyCwDbfAe93hSt5FNl/YMm+aj3EMBBs3Z2fxSB0U5GO0fscCEwh62z2QhG8QE8Eylzs7AYu/am5FLe4gmKK0HGoKhNVyjLqjJIq8Nuwlhz8yi6MRGYzQI3V7x6q5HNZM/LJzrZ75ftFcx4P4469s6ZFL12DS8/tGlpvCWTmouXeuhjnmi21CddS/NO3JfzUKj8kA9RmjMxZqqfzIqDZmMVBCQ247LlT4qFyoEBh1zeKTdViOCCmXLgwNOnJWJ9YdydDIqyb5mGUw4KlolCaTChN04K0c26vif+Gyn5hpUBQmzChAERp33VQk0xe+QJkbTmbw41H841G45E03eeFclhPnQmNvudnV4FRo4G03eNhruFE/q0SuOL6hcZseWSbMwHSB5+FemhQZRaf6mBZZzGXP9hnd0rVTy3+71LLIKAhlgBlDPBTyk43k6hAPhbtu8e1lQlV5j05p249HaFQwKnbMu+LHl6n4il4xOvZfpyY6sW/MxUQ4XkINUg13lek6sB5o/YPObO94cNw7tsu05cxCeGYJALJtD50QmYaArlIly90jZ71iE6bwSj7ie5xpRZKLPqT1+Zju9ciSQnnJ0plM4DFKmtg79QLfMi6z/IJzy6hbNJDv12K3SttOi3L8vjt1arZ00Oc6vMS2MJTe8dT7+AvdX6zLqGgkr/kTjmqcIvGwpTk3ghlT74103SbxZXL1I6O0UakoWAEkA/i5mpHmH9pIM+FpikFXCXH5p7TkRsYELrTMQ/+cFudtNVCVgoH+OQ1lZhAFRNuobKTVzW//zM7z1U8jNyRWswzwA6e60hWNj/+lLm37hY/MREYhoJV3kskDhkwaX+UBq3e972V5ypNcLGrgnOYoXaAZhrtWyOuSRBuWjTeCSlr25bUHJ8GoguM+aWjp2dMYddTkE99G9GtSlaOHX2YubSnYpvUKw8dfy8cgwqFtbT4XGw5s2wWrRUmDITYKktjJgsOOtqv4HQ6w47zAiC2Z9GMSual4kSR4JoOixHgC9/t2qi22fJja5ofZ8AQp/iL8m1W2UWBDK/SpQhe1S/cWxms6VF48Y1fIN1tjTXVX32xNW0JMJpCIoiWo9HNhYe2ecbFgWEMGe1kNWs6rHEBxuQ7DP3EUJroZcdTZIA2S3KiwIWM2soAbozNcjpOO0TWfYwbKS9Fug2Q3DFH8Mzgl7EoYimO0wswc1wlH2zsZoaEbp6lXfOEC65qt/OWBIp+4kHVfOTTwuvVqWx1aABcdu/ZCndrEKNkxLmRG+UN5YVMbA7h0XNeBT5Vh4qV+iMzs8ODdGyU9nqikmhiie8WAtHX5QG7b9McMgTjF5TV4CglBBa1SoWE/EYn22jtAZAzvHquXvPrFdV7xekFgrtTF9Mtpvw3k+UZlnAcDaj/mttrxDwbS7oPZ9oL9ctY3cKoLq19WeyhSeBOnMJzRaU/8GImvxuGMTnO7bM7pSv+MMLsNhrNty6mISXlPzydgtVJxAWrRUL+UkZMymNldNcqNOO/z8otZs20tiDkuFGQWCuOJhbEL72E4hHUnYVATR9c4q22v4l7JHFKcEchCQW06UAkYoBkOBrNlPXg7Et+m3CfvF7K9RR06r4RGAZBXhoCWdKP2hzyFW8D0WSgWcNUo9VFAGr+20rjbFLzTh1NwIRMYUHqPE/RUOMoErTmORfaJ8HiXalLeFC8TmNyIL8nsvMKvW/nI+wneVKMTS4FTeaBzqXjAwWHX7ZZysf2C74OWeMNySuUTp7kSyQmO2jhijVZRq76sKhEyeMuyJE9Qskoxk+YNS1NIkJFUHd6T6OrvB6mjSxv2L68/Nyb8x0OUVaNyR59TIgf1++dIlWZAdkSzPK1iEW6f3z7+srpThD/sLxm8Wo0cG/TyY/X7JCxd+1GMRpWP7hCcn58bV9iprfuPWleNuh/PBY01Ur3ZB6quBTuMIC0eWG7ygWhzzUZiCVpRZOppELXDB27DfaGscdmi0eMDuOkw41b2L01Wnx+qcqja1NwUo9twT75WjYIipbTkbsqXthOwAaoNo8KIOjhCHTFGjZEpFGIFA47SPKRB87f/A/kcwxXWpwAA';
+
+export const F61_PRESENTATION_TERRITORY_SOURCE = {
+  file: 'Tablas de yacimiento y areas(1).xlsx',
+  sheet: 'cr26e_tabla1',
+  sha256: 'afc8991eed0c0175cf121b6e11e6ca7cc5459ba371c5a186966706e386033cdb',
+  rows: JSON.parse(
+    gunzipSync(Buffer.from(SNAPSHOT_GZIP_BASE64, 'base64')).toString('utf8'),
+  ) as F61PresentationTerritoryRow[],
+} as const;

api-v3/test/unit/f6-1-operational-context-db-check.test.ts

@@ -0,0 +1,13 @@
+import assert from 'node:assert/strict';
+import { readFileSync } from 'node:fs';
+import { resolve } from 'node:path';
+import test from 'node:test';
+
+test('F6.1 DB check allows Área-only physical context and rejects Company without Área', () => {
+  const migration=readFileSync(
+    resolve(process.cwd(),'src/database/migrations/1790094500000-f6-1-area-owned-operational-context-check.ts'),
+    'utf8',
+  );
+  assert.match(migration,/CHECK \(operator_company_id IS NULL OR operational_area_id IS NOT NULL\)/);
+  assert.doesNotMatch(migration,/operational_area_id IS NULL OR operator_company_id IS NULL/);
+});

api-v3/test/unit/f6-1-presentation-territory-source.test.ts

@@ -0,0 +1,86 @@
+import assert from 'node:assert/strict';
+import { readFileSync } from 'node:fs';
+import { resolve } from 'node:path';
+import test from 'node:test';
+import { F61_PRESENTATION_TERRITORY_SOURCE } from '../../src/reference-data/f6-1-presentation-territory-source';
+
+function key(value: string): string {
+  return value.normalize('NFD')
+    .replace(/[\u0300-\u036f]/g, '')
+    .toLowerCase()
+    .replace(/[^a-z0-9]+/g, ' ')
+    .trim()
+    .replace(/\s+/g, ' ');
+}
+
+test('F6.1 presentation territory snapshot matches the approved spreadsheet cardinalities', () => {
+  const source = F61_PRESENTATION_TERRITORY_SOURCE;
+  const rows = source.rows;
+
+  assert.equal(source.file, 'Tablas de yacimiento y areas(1).xlsx');
+  assert.equal(source.sheet, 'cr26e_tabla1');
+  assert.equal(source.sha256, 'afc8991eed0c0175cf121b6e11e6ca7cc5459ba371c5a186966706e386033cdb');
+  assert.equal(rows.length, 230);
+
+  const areas = new Map<string, typeof rows[number][]>();
+  for (const row of rows) {
+    const id = key(row.area);
+    const current = areas.get(id) ?? [];
+    current.push(row);
+    areas.set(id, current);
+  }
+
+  const pairs = new Set(rows.map((row) => `${key(row.area)}|${key(row.yacimiento)}`));
+  const departments = new Set(rows.map((row) => key(row.departamento)));
+  const companies = new Set(
+    rows
+      .map((row) => row.empresaOperadora)
+      .filter((name) => key(name) !== key('Sin Empresa Operadora'))
+      .map(key),
+  );
+  const areasWithoutOperator = [...areas.values()]
+    .filter((sameArea) => key(sameArea[0]!.empresaOperadora) === key('Sin Empresa Operadora'));
+  const concessions = new Set(rows.map((row) => row.tipoConcesion));
+
+  assert.equal(areas.size, 64);
+  assert.equal(pairs.size, 230);
+  assert.equal(departments.size, 7);
+  assert.equal(companies.size, 12);
+  assert.equal(areasWithoutOperator.length, 17);
+  assert.deepEqual([...concessions].sort((a, b) => a.localeCompare(b, 'es')), ['Exploración', 'Explotación']);
+
+  for (const [area, sameArea] of areas) {
+    assert.equal(new Set(sameArea.map((row) => key(row.departamento))).size, 1, `${area}: Departamento conflictivo`);
+    assert.equal(new Set(sameArea.map((row) => key(row.tipoConcesion))).size, 1, `${area}: concesión conflictiva`);
+    assert.equal(new Set(sameArea.map((row) => key(row.empresaOperadora))).size, 1, `${area}: Operadora conflictiva`);
+  }
+});
+
+test('F6.1 presentation reset preserves system masters and enforces the physical hierarchy', () => {
+  const migration = readFileSync(
+    resolve(process.cwd(), 'src/database/migrations/1790094600000-f6-1-presentation-territory-reset.ts'),
+    'utf8',
+  );
+
+  assert.match(migration, /TRUNCATE TABLE assets CASCADE/);
+  assert.match(migration, /TRUNCATE TABLE audit_events/);
+  assert.doesNotMatch(migration, /TRUNCATE TABLE users/i);
+  assert.doesNotMatch(migration, /TRUNCATE TABLE roles/i);
+  assert.doesNotMatch(migration, /TRUNCATE TABLE permissions/i);
+  assert.doesNotMatch(migration, /TRUNCATE TABLE inventory_families/i);
+  assert.doesNotMatch(migration, /TRUNCATE TABLE finding_catalog_items/i);
+
+  assert.match(migration, /area_company_relations/);
+  assert.match(migration, /area_legal_rights/);
+  assert.match(migration, /key\(row\.empresaOperadora\)!==key\(NO_OPERATOR\)/);
+  for (const contract of [
+    /total_assets:313/,
+    /companies:12/,
+    /departments:7/,
+    /areas:64/,
+    /yacimientos:230/,
+    /legal_rights:64/,
+    /operators:47/,
+    /areas_without_operator:17/,
+  ]) assert.match(migration, contract);
+});