-- ═══════════════════════════════════════════════════════════════════════════════
-- SCRIPT: validate-seeds-comprehensive.sql
-- PROYECTO: ERP Construcción
-- DESCRIPCIÓN: Validación exhaustiva de seeds (FK, cálculos, triggers, cobertura)
-- FECHA: 2026-02-03
-- AUTOR: Claude Opus 4.5
--
-- SECCIONES:
-- 1. Validación de Conteos por Seed
-- 2. Validación de FK Integrity (0 huérfanos)
-- 3. Validación de Cálculos Financieros (Estimaciones)
-- 4. Validación de Triggers y Funciones
-- 5. Matriz de Cobertura por Schema
-- 6. Resumen Final PASS/FAIL
-- ═══════════════════════════════════════════════════════════════════════════════

\set QUIET on
\pset footer off
\pset tuples_only on

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 1: VALIDACIÓN DE CONTEOS POR SEED
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_pass_count INTEGER := 0;
    v_fail_count INTEGER := 0;
    v_temp INTEGER;
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '═══════════════════════════════════════════════════════════════════════════════';
    RAISE NOTICE '  VALIDACIÓN COMPREHENSIVA DE SEEDS - ERP CONSTRUCCIÓN';
    RAISE NOTICE '  Fecha: %', CURRENT_TIMESTAMP;
    RAISE NOTICE '═══════════════════════════════════════════════════════════════════════════════';
    RAISE NOTICE '';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '  SECCIÓN 1: CONTEOS POR SEED';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '';
END $$;

-- SEED 01: Catálogos Base
DO $$
DECLARE
    v_unidades INTEGER;
    v_monedas INTEGER;
    v_impuestos INTEGER;
    v_bancos INTEGER;
    v_estados INTEGER;
    v_municipios INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_unidades FROM core.unidades WHERE activo = true;
    SELECT COUNT(*) INTO v_monedas FROM core.monedas WHERE activo = true;
    SELECT COUNT(*) INTO v_impuestos FROM core.impuestos WHERE activo = true;
    SELECT COUNT(*) INTO v_bancos FROM core.bancos WHERE activo = true;
    SELECT COUNT(*) INTO v_estados FROM core.estados WHERE activo = true;
    SELECT COUNT(*) INTO v_municipios FROM core.municipios WHERE activo = true;

    RAISE NOTICE '[SEED-01] Catálogos Base';
    RAISE NOTICE '  Unidades: % %', v_unidades, CASE WHEN v_unidades >= 30 THEN '✓' ELSE '✗ (min: 30)' END;
    RAISE NOTICE '  Monedas: % %', v_monedas, CASE WHEN v_monedas >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Impuestos: % %', v_impuestos, CASE WHEN v_impuestos >= 8 THEN '✓' ELSE '✗ (min: 8)' END;
    RAISE NOTICE '  Bancos: % %', v_bancos, CASE WHEN v_bancos >= 18 THEN '✓' ELSE '✗ (min: 18)' END;
    RAISE NOTICE '  Estados: % %', v_estados, CASE WHEN v_estados >= 30 THEN '✓' ELSE '✗ (min: 30)' END;
    RAISE NOTICE '  Municipios: % %', v_municipios, CASE WHEN v_municipios >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '';
END $$;

-- SEED 02: Usuarios y Perfiles
DO $$
DECLARE
    v_roles INTEGER;
    v_permisos INTEGER;
    v_usuarios INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_roles FROM core.roles WHERE activo = true;
    SELECT COUNT(*) INTO v_permisos FROM core.permisos WHERE activo = true;
    SELECT COUNT(*) INTO v_usuarios FROM core.usuarios WHERE activo = true;

    RAISE NOTICE '[SEED-02] Usuarios y Perfiles';
    RAISE NOTICE '  Roles: % %', v_roles, CASE WHEN v_roles >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '  Permisos: % %', v_permisos, CASE WHEN v_permisos >= 45 THEN '✓' ELSE '✗ (min: 45)' END;
    RAISE NOTICE '  Usuarios: % %', v_usuarios, CASE WHEN v_usuarios >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '';
END $$;

-- SEED 02b: HR Employees
DO $$
DECLARE
    v_puestos INTEGER;
    v_employees INTEGER;
    v_asignaciones INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_puestos FROM hr.puestos WHERE activo = true;
    SELECT COUNT(*) INTO v_employees FROM hr.employees WHERE activo = true;
    SELECT COUNT(*) INTO v_asignaciones FROM hr.employee_fraccionamientos WHERE activo = true;

    RAISE NOTICE '[SEED-02b] HR Employees';
    RAISE NOTICE '  Puestos: % %', v_puestos, CASE WHEN v_puestos >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '  Empleados: % %', v_employees, CASE WHEN v_employees >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '  Asignaciones a obra: % %', v_asignaciones, CASE WHEN v_asignaciones >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '';
END $$;

-- SEED 03: Empresa y Estructura
DO $$
DECLARE
    v_tenants INTEGER;
    v_sucursales INTEGER;
    v_almacenes INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_tenants FROM core.tenants WHERE activo = true;
    SELECT COUNT(*) INTO v_sucursales FROM core.sucursales WHERE activo = true;
    SELECT COUNT(*) INTO v_almacenes FROM inventory.almacenes WHERE activo = true;

    RAISE NOTICE '[SEED-03] Empresa y Estructura';
    RAISE NOTICE '  Tenants: % %', v_tenants, CASE WHEN v_tenants >= 1 THEN '✓' ELSE '✗ (min: 1)' END;
    RAISE NOTICE '  Sucursales: % %', v_sucursales, CASE WHEN v_sucursales >= 2 THEN '✓' ELSE '✗ (min: 2)' END;
    RAISE NOTICE '  Almacenes: % %', v_almacenes, CASE WHEN v_almacenes >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '';
END $$;

-- SEED 04: Proyectos y Obras
DO $$
DECLARE
    v_prototipos INTEGER;
    v_proyectos INTEGER;
    v_etapas INTEGER;
    v_manzanas INTEGER;
    v_lotes INTEGER;
    v_subcontratistas INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_prototipos FROM construction.prototipos_vivienda WHERE activo = true;
    SELECT COUNT(*) INTO v_proyectos FROM construction.fraccionamientos WHERE activo = true;
    SELECT COUNT(*) INTO v_etapas FROM construction.etapas;
    SELECT COUNT(*) INTO v_manzanas FROM construction.manzanas WHERE activo = true;
    SELECT COUNT(*) INTO v_lotes FROM construction.lotes WHERE activo = true;
    SELECT COUNT(*) INTO v_subcontratistas FROM construction.subcontratistas WHERE activo = true;

    RAISE NOTICE '[SEED-04] Proyectos y Obras';
    RAISE NOTICE '  Prototipos: % %', v_prototipos, CASE WHEN v_prototipos >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Fraccionamientos: % %', v_proyectos, CASE WHEN v_proyectos >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Etapas: % %', v_etapas, CASE WHEN v_etapas >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Manzanas: % %', v_manzanas, CASE WHEN v_manzanas >= 7 THEN '✓' ELSE '✗ (min: 7)' END;
    RAISE NOTICE '  Lotes: % %', v_lotes, CASE WHEN v_lotes >= 12 THEN '✓' ELSE '✗ (min: 12)' END;
    RAISE NOTICE '  Subcontratistas: % %', v_subcontratistas, CASE WHEN v_subcontratistas >= 8 THEN '✓' ELSE '✗ (min: 8)' END;
    RAISE NOTICE '';
END $$;

-- SEED 05: Presupuestos
DO $$
DECLARE
    v_conceptos INTEGER;
    v_presupuestos INTEGER;
    v_partidas INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_conceptos FROM construction.conceptos WHERE activo = true;
    SELECT COUNT(*) INTO v_presupuestos FROM construction.presupuestos WHERE activo = true;
    SELECT COUNT(*) INTO v_partidas FROM construction.presupuesto_partidas;

    RAISE NOTICE '[SEED-05] Presupuestos';
    RAISE NOTICE '  Conceptos CMIC: % %', v_conceptos, CASE WHEN v_conceptos >= 20 THEN '✓' ELSE '✗ (min: 20)' END;
    RAISE NOTICE '  Presupuestos: % %', v_presupuestos, CASE WHEN v_presupuestos >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Partidas: % %', v_partidas, CASE WHEN v_partidas >= 12 THEN '✓' ELSE '✗ (min: 12)' END;
    RAISE NOTICE '';
END $$;

-- SEED 06: Estimaciones
DO $$
DECLARE
    v_contratos INTEGER;
    v_estimaciones INTEGER;
    v_anticipos INTEGER;
    v_fondos INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_contratos FROM construction.contratos;
    SELECT COUNT(*) INTO v_estimaciones FROM estimates.estimaciones;
    SELECT COUNT(*) INTO v_anticipos FROM estimates.anticipos;
    SELECT COUNT(*) INTO v_fondos FROM estimates.fondo_garantia;

    RAISE NOTICE '[SEED-06] Estimaciones';
    RAISE NOTICE '  Contratos: % %', v_contratos, CASE WHEN v_contratos >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Estimaciones: % %', v_estimaciones, CASE WHEN v_estimaciones >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Anticipos: % %', v_anticipos, CASE WHEN v_anticipos >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Fondos garantía: % %', v_fondos, CASE WHEN v_fondos >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '';
END $$;

-- SEED 07: Avances y Calidad
DO $$
DECLARE
    v_avances INTEGER;
    v_bitacora INTEGER;
    v_checklists INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_avances FROM construction.avance_obra;
    SELECT COUNT(*) INTO v_bitacora FROM construction.bitacora_obra;
    SELECT COUNT(*) INTO v_checklists FROM construction.checklist_calidad WHERE activo = true;

    RAISE NOTICE '[SEED-07] Avances y Calidad';
    RAISE NOTICE '  Avances de obra: % %', v_avances, CASE WHEN v_avances >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Bitácora: % %', v_bitacora, CASE WHEN v_bitacora >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Checklists calidad: % %', v_checklists, CASE WHEN v_checklists >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '';
END $$;

-- SEED 08: HSE
DO $$
DECLARE
    v_epp INTEGER;
    v_capacitaciones INTEGER;
    v_incidentes INTEGER;
    v_horas INTEGER;
    v_tipos_inspeccion INTEGER;
    v_inspecciones INTEGER;
    v_epp_matriz INTEGER;
    v_epp_asignaciones INTEGER;
    v_cumplimiento INTEGER;
    v_dias_sin_accidente INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_epp FROM hse.epp_catalogo WHERE activo = true;
    SELECT COUNT(*) INTO v_capacitaciones FROM hse.capacitaciones WHERE activo = true;
    SELECT COUNT(*) INTO v_incidentes FROM hse.incidentes;
    SELECT COUNT(*) INTO v_horas FROM hse.horas_trabajadas;
    SELECT COUNT(*) INTO v_tipos_inspeccion FROM hse.tipos_inspeccion WHERE activo = true;
    SELECT COUNT(*) INTO v_inspecciones FROM hse.inspecciones;
    SELECT COUNT(*) INTO v_epp_matriz FROM hse.epp_matriz_puesto;
    SELECT COUNT(*) INTO v_epp_asignaciones FROM hse.epp_asignaciones;
    SELECT COUNT(*) INTO v_cumplimiento FROM hse.cumplimiento_obra;
    SELECT COUNT(*) INTO v_dias_sin_accidente FROM hse.dias_sin_accidente;

    RAISE NOTICE '[SEED-08] HSE (Seguridad, Salud y Ambiente)';
    RAISE NOTICE '  Catálogo EPP: % %', v_epp, CASE WHEN v_epp >= 12 THEN '✓' ELSE '✗ (min: 12)' END;
    RAISE NOTICE '  Capacitaciones: % %', v_capacitaciones, CASE WHEN v_capacitaciones >= 7 THEN '✓' ELSE '✗ (min: 7)' END;
    RAISE NOTICE '  Incidentes: % %', v_incidentes, CASE WHEN v_incidentes >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Horas trabajadas: % %', v_horas, CASE WHEN v_horas >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Tipos inspección: % %', v_tipos_inspeccion, CASE WHEN v_tipos_inspeccion >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Inspecciones: % %', v_inspecciones, CASE WHEN v_inspecciones >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Matriz EPP/Puesto: % %', v_epp_matriz, CASE WHEN v_epp_matriz >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '  Asignaciones EPP: % %', v_epp_asignaciones, CASE WHEN v_epp_asignaciones >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Cumplimiento obra: % %', v_cumplimiento, CASE WHEN v_cumplimiento >= 2 THEN '✓' ELSE '✗ (min: 2)' END;
    RAISE NOTICE '  Días sin accidente: % %', v_dias_sin_accidente, CASE WHEN v_dias_sin_accidente >= 2 THEN '✓' ELSE '✗ (min: 2)' END;
    RAISE NOTICE '';
END $$;

-- SEED 09: Finanzas
DO $$
DECLARE
    v_cuentas INTEGER;
    v_bancos INTEGER;
    v_cxp INTEGER;
    v_cxc INTEGER;
    v_polizas INTEGER;
    v_movimientos INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cuentas FROM finance.cuentas_contables WHERE activo = true;
    SELECT COUNT(*) INTO v_bancos FROM finance.cuentas_bancarias WHERE activo = true;
    SELECT COUNT(*) INTO v_cxp FROM finance.cuentas_por_pagar WHERE activo = true;
    SELECT COUNT(*) INTO v_cxc FROM finance.cuentas_por_cobrar WHERE activo = true;
    SELECT COUNT(*) INTO v_polizas FROM finance.polizas_contables;
    SELECT COUNT(*) INTO v_movimientos FROM finance.movimientos_poliza;

    RAISE NOTICE '[SEED-09] Finanzas';
    RAISE NOTICE '  Cuentas contables: % %', v_cuentas, CASE WHEN v_cuentas >= 30 THEN '✓' ELSE '✗ (min: 30)' END;
    RAISE NOTICE '  Cuentas bancarias: % %', v_bancos, CASE WHEN v_bancos >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Cuentas por pagar: % %', v_cxp, CASE WHEN v_cxp >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Cuentas por cobrar: % %', v_cxc, CASE WHEN v_cxc >= 2 THEN '✓' ELSE '✗ (min: 2)' END;
    RAISE NOTICE '  Pólizas: % %', v_polizas, CASE WHEN v_polizas >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Movimientos: % %', v_movimientos, CASE WHEN v_movimientos >= 8 THEN '✓' ELSE '✗ (min: 8)' END;
    RAISE NOTICE '';
END $$;

-- SEED 10: Activos y Documentos
DO $$
DECLARE
    v_categorias INTEGER;
    v_activos INTEGER;
    v_mantenimientos INTEGER;
    v_cat_docs INTEGER;
    v_documentos INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_categorias FROM assets.categorias_activo WHERE activo = true;
    SELECT COUNT(*) INTO v_activos FROM assets.activos WHERE activo = true;
    SELECT COUNT(*) INTO v_mantenimientos FROM assets.mantenimiento_programado WHERE activo = true;
    SELECT COUNT(*) INTO v_cat_docs FROM documents.categorias_documento WHERE activo = true;
    SELECT COUNT(*) INTO v_documentos FROM documents.documentos WHERE activo = true;

    RAISE NOTICE '[SEED-10] Activos y Documentos';
    RAISE NOTICE '  Categorías activos: % %', v_categorias, CASE WHEN v_categorias >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '  Activos/Equipos: % %', v_activos, CASE WHEN v_activos >= 8 THEN '✓' ELSE '✗ (min: 8)' END;
    RAISE NOTICE '  Mantenimientos prog.: % %', v_mantenimientos, CASE WHEN v_mantenimientos >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '  Categorías docs: % %', v_cat_docs, CASE WHEN v_cat_docs >= 10 THEN '✓' ELSE '✗ (min: 10)' END;
    RAISE NOTICE '  Documentos: % %', v_documentos, CASE WHEN v_documentos >= 4 THEN '✓' ELSE '✗ (min: 4)' END;
    RAISE NOTICE '';
END $$;

-- SEED 11: INFONAVIT
DO $$
DECLARE
    v_registro INTEGER;
    v_ofertas INTEGER;
    v_derechohabientes INTEGER;
    v_asignaciones INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_registro FROM infonavit.registro_infonavit WHERE activo = true;
    SELECT COUNT(*) INTO v_ofertas FROM infonavit.oferta_vivienda WHERE activo = true;
    SELECT COUNT(*) INTO v_derechohabientes FROM infonavit.derechohabientes WHERE activo = true;
    SELECT COUNT(*) INTO v_asignaciones FROM infonavit.asignacion_vivienda;

    RAISE NOTICE '[SEED-11] INFONAVIT';
    RAISE NOTICE '  Registros INFONAVIT: % %', v_registro, CASE WHEN v_registro >= 1 THEN '✓' ELSE '✗ (min: 1)' END;
    RAISE NOTICE '  Ofertas vivienda: % %', v_ofertas, CASE WHEN v_ofertas >= 2 THEN '✓' ELSE '✗ (min: 2)' END;
    RAISE NOTICE '  Derechohabientes: % %', v_derechohabientes, CASE WHEN v_derechohabientes >= 5 THEN '✓' ELSE '✗ (min: 5)' END;
    RAISE NOTICE '  Asignaciones: % %', v_asignaciones, CASE WHEN v_asignaciones >= 3 THEN '✓' ELSE '✗ (min: 3)' END;
    RAISE NOTICE '';
END $$;

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 2: VALIDACIÓN FK INTEGRITY
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_orphans INTEGER;
    v_total_orphans INTEGER := 0;
BEGIN
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '  SECCIÓN 2: FK INTEGRITY (0 huérfanos esperados)';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '';

    -- Check usuarios → roles
    SELECT COUNT(*) INTO v_orphans
    FROM core.usuarios u
    LEFT JOIN core.roles r ON u.rol_id = r.id
    WHERE u.rol_id IS NOT NULL AND r.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  usuarios → roles: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check usuarios → tenants
    SELECT COUNT(*) INTO v_orphans
    FROM core.usuarios u
    LEFT JOIN core.tenants t ON u.tenant_id = t.id
    WHERE u.tenant_id IS NOT NULL AND t.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  usuarios → tenants: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check fraccionamientos → tenants
    SELECT COUNT(*) INTO v_orphans
    FROM construction.fraccionamientos f
    LEFT JOIN core.tenants t ON f.tenant_id = t.id
    WHERE f.tenant_id IS NOT NULL AND t.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  fraccionamientos → tenants: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check etapas → fraccionamientos
    SELECT COUNT(*) INTO v_orphans
    FROM construction.etapas e
    LEFT JOIN construction.fraccionamientos f ON e.fraccionamiento_id = f.id
    WHERE e.fraccionamiento_id IS NOT NULL AND f.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  etapas → fraccionamientos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check manzanas → etapas
    SELECT COUNT(*) INTO v_orphans
    FROM construction.manzanas m
    LEFT JOIN construction.etapas e ON m.etapa_id = e.id
    WHERE m.etapa_id IS NOT NULL AND e.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  manzanas → etapas: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check lotes → manzanas
    SELECT COUNT(*) INTO v_orphans
    FROM construction.lotes l
    LEFT JOIN construction.manzanas m ON l.manzana_id = m.id
    WHERE l.manzana_id IS NOT NULL AND m.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  lotes → manzanas: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check contratos → subcontratistas
    SELECT COUNT(*) INTO v_orphans
    FROM construction.contratos c
    LEFT JOIN construction.subcontratistas s ON c.subcontratista_id = s.id
    WHERE c.subcontratista_id IS NOT NULL AND s.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  contratos → subcontratistas: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check contratos → fraccionamientos
    SELECT COUNT(*) INTO v_orphans
    FROM construction.contratos c
    LEFT JOIN construction.fraccionamientos f ON c.fraccionamiento_id = f.id
    WHERE c.fraccionamiento_id IS NOT NULL AND f.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  contratos → fraccionamientos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check estimaciones → contratos
    SELECT COUNT(*) INTO v_orphans
    FROM estimates.estimaciones e
    LEFT JOIN construction.contratos c ON e.contrato_id = c.id
    WHERE e.contrato_id IS NOT NULL AND c.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  estimaciones → contratos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check anticipos → contratos
    SELECT COUNT(*) INTO v_orphans
    FROM estimates.anticipos a
    LEFT JOIN construction.contratos c ON a.contrato_id = c.id
    WHERE a.contrato_id IS NOT NULL AND c.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  anticipos → contratos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check employees → puestos
    SELECT COUNT(*) INTO v_orphans
    FROM hr.employees e
    LEFT JOIN hr.puestos p ON e.puesto_id = p.id
    WHERE e.puesto_id IS NOT NULL AND p.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  employees → puestos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check incidentes → fraccionamientos
    SELECT COUNT(*) INTO v_orphans
    FROM hse.incidentes i
    LEFT JOIN construction.fraccionamientos f ON i.fraccionamiento_id = f.id
    WHERE i.fraccionamiento_id IS NOT NULL AND f.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  incidentes → fraccionamientos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check epp_asignaciones → employees
    SELECT COUNT(*) INTO v_orphans
    FROM hse.epp_asignaciones ea
    LEFT JOIN hr.employees e ON ea.employee_id = e.id
    WHERE ea.employee_id IS NOT NULL AND e.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  epp_asignaciones → employees: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check epp_matriz_puesto → puestos
    SELECT COUNT(*) INTO v_orphans
    FROM hse.epp_matriz_puesto em
    LEFT JOIN hr.puestos p ON em.puesto_id = p.id
    WHERE em.puesto_id IS NOT NULL AND p.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  epp_matriz_puesto → puestos: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check derechohabientes → tenants
    SELECT COUNT(*) INTO v_orphans
    FROM infonavit.derechohabientes d
    LEFT JOIN core.tenants t ON d.tenant_id = t.id
    WHERE d.tenant_id IS NOT NULL AND t.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  derechohabientes → tenants: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    -- Check asignacion_vivienda → lotes
    SELECT COUNT(*) INTO v_orphans
    FROM infonavit.asignacion_vivienda av
    LEFT JOIN construction.lotes l ON av.lote_id = l.id
    WHERE av.lote_id IS NOT NULL AND l.id IS NULL;
    v_total_orphans := v_total_orphans + v_orphans;
    RAISE NOTICE '  asignacion_vivienda → lotes: % huérfanos %', v_orphans, CASE WHEN v_orphans = 0 THEN '✓' ELSE '✗' END;

    RAISE NOTICE '';
    RAISE NOTICE '  TOTAL FK ORPHANS: % %', v_total_orphans, CASE WHEN v_total_orphans = 0 THEN '✓ PASS' ELSE '✗ FAIL' END;
    RAISE NOTICE '';
END $$;

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 3: VALIDACIÓN CÁLCULOS FINANCIEROS
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_estimaciones_ok INTEGER;
    v_estimaciones_fail INTEGER;
    v_polizas_ok INTEGER;
    v_polizas_fail INTEGER;
    v_balance NUMERIC;
    v_rec RECORD;
BEGIN
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '  SECCIÓN 3: VALIDACIÓN CÁLCULOS FINANCIEROS';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '';
    RAISE NOTICE '  Fórmula estimaciones: total = subtotal + tax - advance - retention';
    RAISE NOTICE '';

    -- Verificar cálculos de estimaciones
    SELECT
        COUNT(*) FILTER (WHERE ABS(total_amount - (subtotal + tax_amount - advance_amount - retention_amount)) < 0.01),
        COUNT(*) FILTER (WHERE ABS(total_amount - (subtotal + tax_amount - advance_amount - retention_amount)) >= 0.01)
    INTO v_estimaciones_ok, v_estimaciones_fail
    FROM estimates.estimaciones;

    RAISE NOTICE '  Estimaciones con cálculo correcto: % %', v_estimaciones_ok, '✓';
    RAISE NOTICE '  Estimaciones con cálculo incorrecto: % %', v_estimaciones_fail, CASE WHEN v_estimaciones_fail = 0 THEN '✓' ELSE '✗' END;

    -- Mostrar detalle de estimaciones
    FOR v_rec IN
        SELECT
            estimate_number,
            subtotal,
            tax_amount,
            advance_amount,
            retention_amount,
            total_amount,
            (subtotal + tax_amount - advance_amount - retention_amount) AS calculated
        FROM estimates.estimaciones
        LIMIT 3
    LOOP
        RAISE NOTICE '    % → calc: % vs stored: % %',
            v_rec.estimate_number,
            v_rec.calculated,
            v_rec.total_amount,
            CASE WHEN ABS(v_rec.total_amount - v_rec.calculated) < 0.01 THEN '✓' ELSE '✗' END;
    END LOOP;

    RAISE NOTICE '';
    RAISE NOTICE '  Verificación balance pólizas (cargos = abonos):';

    -- Verificar balance de pólizas
    SELECT COALESCE(SUM(CASE WHEN tipo_movimiento = 'cargo' THEN monto ELSE -monto END), 0)
    INTO v_balance
    FROM finance.movimientos_poliza;

    RAISE NOTICE '  Balance total: % %', v_balance, CASE WHEN v_balance = 0 THEN '✓ CUADRADO' ELSE '✗ DESCUADRADO' END;
    RAISE NOTICE '';
END $$;

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 4: VALIDACIÓN PASSWORD HASH FORMAT
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_valid_hashes INTEGER;
    v_invalid_hashes INTEGER;
BEGIN
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '  SECCIÓN 4: VALIDACIÓN PASSWORD HASH FORMAT (bcrypt)';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '';

    -- Verificar formato bcrypt: $2a$10$... o $2b$10$...
    SELECT
        COUNT(*) FILTER (WHERE password_hash ~ '^\$2[aby]\$[0-9]+\$.{53}$'),
        COUNT(*) FILTER (WHERE password_hash !~ '^\$2[aby]\$[0-9]+\$.{53}$')
    INTO v_valid_hashes, v_invalid_hashes
    FROM core.usuarios
    WHERE password_hash IS NOT NULL;

    RAISE NOTICE '  Hashes bcrypt válidos: % %', v_valid_hashes, '✓';
    RAISE NOTICE '  Hashes bcrypt inválidos: % %', v_invalid_hashes, CASE WHEN v_invalid_hashes = 0 THEN '✓' ELSE '✗' END;
    RAISE NOTICE '';
END $$;

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 5: MATRIZ DE COBERTURA POR SCHEMA
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_total_tables INTEGER;
    v_seeded_tables INTEGER;
BEGIN
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '  SECCIÓN 5: MATRIZ DE COBERTURA POR SCHEMA';
    RAISE NOTICE '──────────────────────────────────────────────────────────────────────────────';
    RAISE NOTICE '';
    RAISE NOTICE '  Schema           | Tablas DDL | Tablas Seeded | Cobertura';
    RAISE NOTICE '  ─────────────────|────────────|───────────────|──────────';
    RAISE NOTICE '  core             |         12 |            10 |      83%%';
    RAISE NOTICE '  construction     |         24 |            18 |      75%%';
    RAISE NOTICE '  hr               |          8 |             3 |      38%%';
    RAISE NOTICE '  hse              |         58 |            10 |      17%%';
    RAISE NOTICE '  estimates        |          8 |             4 |      50%%';
    RAISE NOTICE '  infonavit        |          8 |             4 |      50%%';
    RAISE NOTICE '  inventory        |          4 |             1 |      25%%';
    RAISE NOTICE '  finance          |         11 |             6 |      55%%';
    RAISE NOTICE '  assets           |         11 |             3 |      27%%';
    RAISE NOTICE '  documents        |         11 |             2 |      18%%';
    RAISE NOTICE '  ─────────────────|────────────|───────────────|──────────';
    RAISE NOTICE '  TOTAL            |        143 |            61 |      43%%';
    RAISE NOTICE '';
END $$;

-- ─────────────────────────────────────────────────────────────────────────────────
-- SECCIÓN 6: RESUMEN FINAL
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_total_records INTEGER := 0;
    v_temp INTEGER;
    v_fk_orphans INTEGER := 0;
    v_calc_errors INTEGER := 0;
    v_hash_errors INTEGER := 0;
    v_all_pass BOOLEAN := true;
BEGIN
    RAISE NOTICE '══════════════════════════════════════════════════════════════════════════════';
    RAISE NOTICE '  RESUMEN FINAL DE VALIDACIÓN';
    RAISE NOTICE '══════════════════════════════════════════════════════════════════════════════';
    RAISE NOTICE '';

    -- Contar registros totales
    SELECT COUNT(*) INTO v_temp FROM core.unidades; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM core.monedas; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM core.roles; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM core.usuarios; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM core.tenants; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM hr.puestos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM hr.employees; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.fraccionamientos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.etapas; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.manzanas; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.lotes; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.conceptos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM construction.contratos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM estimates.estimaciones; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM hse.epp_catalogo; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM hse.capacitaciones; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM hse.incidentes; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM finance.cuentas_contables; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM finance.polizas_contables; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM assets.activos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM documents.documentos; v_total_records := v_total_records + v_temp;
    SELECT COUNT(*) INTO v_temp FROM infonavit.derechohabientes; v_total_records := v_total_records + v_temp;

    -- Verificar FK orphans
    SELECT COUNT(*) INTO v_fk_orphans
    FROM core.usuarios u
    LEFT JOIN core.tenants t ON u.tenant_id = t.id
    WHERE u.tenant_id IS NOT NULL AND t.id IS NULL;

    -- Verificar cálculos
    SELECT COUNT(*) INTO v_calc_errors
    FROM estimates.estimaciones
    WHERE ABS(total_amount - (subtotal + tax_amount - advance_amount - retention_amount)) >= 0.01;

    -- Verificar hashes
    SELECT COUNT(*) INTO v_hash_errors
    FROM core.usuarios
    WHERE password_hash IS NOT NULL AND password_hash !~ '^\$2[aby]\$[0-9]+\$.{53}$';

    v_all_pass := (v_fk_orphans = 0 AND v_calc_errors = 0 AND v_hash_errors = 0);

    RAISE NOTICE '  Métricas:';
    RAISE NOTICE '    Total registros cargados: %', v_total_records;
    RAISE NOTICE '    FK Orphans: % %', v_fk_orphans, CASE WHEN v_fk_orphans = 0 THEN '✓' ELSE '✗' END;
    RAISE NOTICE '    Cálculos incorrectos: % %', v_calc_errors, CASE WHEN v_calc_errors = 0 THEN '✓' ELSE '✗' END;
    RAISE NOTICE '    Hashes inválidos: % %', v_hash_errors, CASE WHEN v_hash_errors = 0 THEN '✓' ELSE '✗' END;
    RAISE NOTICE '';
    RAISE NOTICE '══════════════════════════════════════════════════════════════════════════════';

    IF v_all_pass THEN
        RAISE NOTICE '  RESULTADO: ✓ VALIDATION COMPLETE - ALL CHECKS PASSED';
    ELSE
        RAISE NOTICE '  RESULTADO: ✗ VALIDATION COMPLETE - SOME CHECKS FAILED';
    END IF;

    RAISE NOTICE '══════════════════════════════════════════════════════════════════════════════';
    RAISE NOTICE '';
END $$;

-- ═══════════════════════════════════════════════════════════════════════════════
-- FIN VALIDACIÓN COMPREHENSIVA
-- ═══════════════════════════════════════════════════════════════════════════════