-- T2026-05-027 / Paso 1: detección de registros afectados (solo lectura)
-- Ejecutar ANTES de la migración como línea base y DESPUÉS de la corrección
-- (las queries deben regresar 0 filas al final).
--
-- Catálogo confirmado de document_type:
--   4 = acuse EDoc, 7 = acuse COVE, 19/23 = request COVE, 21/25 = request EDoc,
--   20 = error COVE, 22 = error EDoc, 24 = error acuse COVE, 26 = error acuse EDoc
-- Confirmar con: SELECT id, nombre, descripcion FROM document_type ORDER BY id;

-- (a) Acuses de EDocument marcados descargados SIN documento tipo 4 en BD
SELECT e.id, e.numero_edocument, p.pedimento, p.id AS pedimento_id, e.organizacion_id
FROM edocs e
JOIN pedimento p ON p.id = e.pedimento_id
WHERE e.acuse_descargado = TRUE
  AND NOT EXISTS (
    SELECT 1 FROM document d
    WHERE d.pedimento_id = e.pedimento_id
      AND d.document_type_id = 4
      AND d.archivo ILIKE '%' || e.numero_edocument || '%'
  );

-- (b) EDocuments marcados descargados SIN documento general
SELECT e.id, e.numero_edocument, p.pedimento
FROM edocs e
JOIN pedimento p ON p.id = e.pedimento_id
WHERE e.edocument_descargado = TRUE
  AND NOT EXISTS (
    SELECT 1 FROM document d
    WHERE d.pedimento_id = e.pedimento_id
      AND d.archivo ILIKE '%' || e.numero_edocument || '%'
      AND d.document_type_id NOT IN (4, 21, 22, 25, 26)
  );

-- (c) Acuses de COVE marcados descargados SIN documento tipo 7
SELECT c.id, c.numero_cove, p.pedimento
FROM coves c
JOIN pedimento p ON p.id = c.pedimento_id
WHERE c.acuse_cove_descargado = TRUE
  AND NOT EXISTS (
    SELECT 1 FROM document d
    WHERE d.pedimento_id = c.pedimento_id
      AND d.document_type_id = 7
      AND d.archivo ILIKE '%' || c.numero_cove || '%'
  );

-- (d) COVEs marcados descargados SIN documento general
SELECT c.id, c.numero_cove, p.pedimento
FROM coves c
JOIN pedimento p ON p.id = c.pedimento_id
WHERE c.cove_descargado = TRUE
  AND NOT EXISTS (
    SELECT 1 FROM document d
    WHERE d.pedimento_id = c.pedimento_id
      AND d.archivo ILIKE '%' || c.numero_cove || '%'
      AND d.document_type_id NOT IN (7, 19, 20, 23, 24)
  );

-- (e) Pendientes con evidencia de error (documentos tipo 20/22/24/26)
SELECT 'cove_acuse_pendiente_con_error' AS categoria, c.id::text, c.numero_cove AS numero
FROM coves c
WHERE c.acuse_cove_descargado = FALSE
  AND EXISTS (SELECT 1 FROM document d
              WHERE d.pedimento_id = c.pedimento_id AND d.document_type_id = 24
                AND d.archivo ILIKE '%' || c.numero_cove || '%')
UNION ALL
SELECT 'cove_pendiente_con_error', c.id::text, c.numero_cove
FROM coves c
WHERE c.cove_descargado = FALSE
  AND EXISTS (SELECT 1 FROM document d
              WHERE d.pedimento_id = c.pedimento_id AND d.document_type_id = 20
                AND d.archivo ILIKE '%' || c.numero_cove || '%')
UNION ALL
SELECT 'edoc_pendiente_con_error', e.id::text, e.numero_edocument
FROM edocs e
WHERE e.edocument_descargado = FALSE
  AND EXISTS (SELECT 1 FROM document d
              WHERE d.pedimento_id = e.pedimento_id AND d.document_type_id = 22
                AND d.archivo ILIKE '%' || e.numero_edocument || '%')
UNION ALL
SELECT 'edoc_acuse_pendiente_con_error', e.id::text, e.numero_edocument
FROM edocs e
WHERE e.acuse_descargado = FALSE
  AND EXISTS (SELECT 1 FROM document d
              WHERE d.pedimento_id = e.pedimento_id AND d.document_type_id = 26
                AND d.archivo ILIKE '%' || e.numero_edocument || '%');

-- (f) Verificación puntual del caso reportado por QA (pedimento del documento de pruebas)
SELECT e.id, e.numero_edocument, e.edocument_descargado, e.acuse_descargado
FROM edocs e
WHERE e.pedimento_id = 'b4a6c3dd-5966-45a8-aa50-79b626ffd9c1';