-- MIA (MODELO MEDION AGENTES) - PostgreSQL Version
-- Converted from MySQL to PostgreSQL
-- Note: Encrypted columns are no longer encrypted

WITH promesas_base AS (SELECT p.idCallhist,
                              p.order_id,
    DATE (c.fechagestion) AS fchPromTom
   , p.fechaPromesa
   , COALESCE (MAX (pg.fecha)::text
   , '') AS fchUltPago
   , COALESCE ('$ ' || REPLACE(TO_CHAR(p.valorPromesa
   , 'FM999,999,999')
   , ','
   , '.')
   , '$ 0') AS valorPromesa
   , COALESCE (u.usuario
   , '') AS nombreAsesor
   , CASE
    WHEN c.idcasa IN (1
   , 3
   , 18
   , 19
   , 20
   , 25) THEN 'Interno'
    WHEN c.idcasa = 2 THEN 'Puntualmente'
    WHEN c.idcasa = 11 THEN 'Orosibpo'
    WHEN c.idcasa = 13 THEN 'Recovery'
    WHEN c.idcasa = 15 THEN 'Moal'
    WHEN c.idcasa = 17 THEN 'JJAbogados'
    WHEN c.idcasa = 21 THEN 'Wise'
    ELSE 'Sin identificar'
END
AS idcasa,
        c.dpd AS Ddp,
        c.idResultado,
        COALESCE(SUM(pg.valor), 0) AS Pagos_valor_real,
        COALESCE(COUNT(pg.valor), 0) AS NoPagos
    FROM collector_drpeso."6_promesas" p
    LEFT JOIN collector_drpeso."7_callhist" c
        ON p.idCallhist = c.id
    LEFT JOIN collector_usuarios_drpeso.usuarios u
        ON c.idasesor = u.id
    LEFT JOIN collector_drpeso.pagos pg
        ON p.order_id = pg.obligacion
        AND DATE(pg.fecha) BETWEEN DATE(c.fechagestion) AND p.fechaPromesa
        AND pg.status = 'Procesado'
        AND pg.tipopago NOT IN (
            'Discount FEE','Discount LTM','Discount PM',
            'Discount PYA','Discount SUP','Write off'
        )
    WHERE p.fechaPromesa >= '2026-02-23'
    GROUP BY
        p.idCallhist,
        p.order_id,
        DATE(c.fechagestion),
        p.fechaPromesa,
        p.valorPromesa,
        u.usuario,
        c.idcasa,
        c.dpd,
        c.idResultado
),
detecta_duplicados AS (
    SELECT
        order_id,
        fechaPromesa,
        MIN(fchPromTom) AS primera_promesa
    FROM promesas_base
    GROUP BY order_id, fechaPromesa
),
promesa_valida_pago AS (
    SELECT
        order_id,
        fchUltPago,
        MIN(fechaPromesa) AS fechaPromesaValida
    FROM promesas_base
    WHERE Pagos_valor_real > 0
        AND fchUltPago <> ''
        AND fechaPromesa >= fchUltPago::date
    GROUP BY order_id, fchUltPago
)
SELECT pb.idCallhist,
       COALESCE(cbz.documento, '') AS Cedula,
       pb.order_id,
       pb.fchPromTom,
       pb.fechaPromesa,
       pb.fchUltPago,
       pb.valorPromesa,
       pb.nombreAsesor,
       pb.idcasa,
       pb.Ddp,
       CASE
           WHEN pvp.fechaPromesaValida = pb.fechaPromesa
               THEN '$ ' || REPLACE(TO_CHAR(pb.Pagos_valor_real, 'FM999,999,999'), ',', '.')
           ELSE '$ 0'
           END                     AS Pagos,
       CASE
           WHEN pvp.fechaPromesaValida = pb.fechaPromesa
               THEN pb.NoPagos
           ELSE 0
           END                     AS NoPagos,
       CASE
           WHEN pb.idResultado IN (7, 201, 202, 203, 204)
               THEN 'YaPago'
           WHEN pb.fchPromTom > dd.primera_promesa
               THEN 'AlarmaDuplicado'
           WHEN pvp.fechaPromesaValida = pb.fechaPromesa
               THEN 'PromesaCumplida'
           WHEN pvp.fechaPromesaValida IS NOT NULL
               AND pb.fechaPromesa > pvp.fechaPromesaValida
               AND pb.fchUltPago = pvp.fchUltPago
               THEN 'PromesaAnulada'
           WHEN pb.Pagos_valor_real = 0
               AND CURRENT_DATE <= pb.fechaPromesa
               THEN 'PromesaVigente'
           WHEN pb.Pagos_valor_real = 0
               AND CURRENT_DATE > pb.fechaPromesa
               THEN 'PromesaRota'
           ELSE 'Indefinido'
           END                     AS EstadoProm,
       -- COLUMNA NUEVA (New / Old)
       CASE
           WHEN cbz.estrategia = 'New' THEN 'New'
           WHEN cbz.estrategia = 'Old' THEN 'Old'
           ELSE 'Sin definir'
           END                     AS Tipo
FROM promesas_base pb
         LEFT JOIN detecta_duplicados dd
                   ON pb.order_id = dd.order_id
                       AND pb.fechaPromesa = dd.fechaPromesa
         LEFT JOIN promesa_valida_pago pvp
                   ON pb.order_id = pvp.order_id
                       AND pb.fchUltPago = pvp.fchUltPago
         LEFT JOIN collector_drpeso."9_cobranzas" cbz
                   ON pb.order_id = cbz.obligacion
ORDER BY pb.order_id,
         pb.fechaPromesa,
         pb.fchPromTom;
