-- QUERY PRODUCTO NUEVO - PostgreSQL Version
-- Converted from MySQL to PostgreSQL
-- Note: Encrypted columns are no longer encrypted (removed AES_DECRYPT)

WITH pp_fechas AS (
    SELECT
        pp.loan_id,
        c.obligacion, -- Direct column access instead of AES_DECRYPT
        COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN DATE(pp.start_date) END), NULL) AS start_date_1,
        COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN
                              CASE
                                  WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 1 AND 5 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' - INTERVAL '1 day'
                     WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 6 AND 20 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' + INTERVAL '14 days'
                     WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 21 AND 31 THEN DATE_TRUNC('month', pp.start_date + INTERVAL '1 month') + INTERVAL '1 month' - INTERVAL '1 day'
                     END
                     END), NULL) AS due_date_1,
        COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN DATE(pp.start_date) END), NULL) AS start_date_2,
        COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN
                              CASE
                                  WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 1 AND 5 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' - INTERVAL '1 day'
                     WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 6 AND 20 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' + INTERVAL '14 days'
                     WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 21 AND 31 THEN DATE_TRUNC('month', pp.start_date + INTERVAL '1 month') + INTERVAL '1 month' - INTERVAL '1 day'
                     END
                     END), NULL) AS due_date_2
    FROM payment_periods pp
             LEFT JOIN "9_cobranzas" c ON c.id = pp.loan_id
    GROUP BY pp.loan_id, c.obligacion
),
     pagos_calculados AS (
         SELECT
             pg.obligacion AS obligacion,
             MIN(CASE
                     WHEN pg.status = 'Procesado'
                         AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                         THEN DATE(pg.fecha)
                 END) AS fch_primer_pago,
             SUM(CASE
                     WHEN pg.status = 'Procesado'
                         AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                         THEN pg.valor ELSE 0
                 END) AS Total_pagos_val,
             SUM(CASE
                     WHEN pg.status = 'Procesado'
                         AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                         AND DATE(pg.fecha) BETWEEN pf.start_date_1 AND pf.due_date_1
                 THEN pg.valor ELSE 0
                 END) AS Pagos_1_val,
             COUNT(CASE
                       WHEN pg.status = 'Procesado'
                           AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                           AND DATE(pg.fecha) BETWEEN pf.start_date_1 AND pf.due_date_1
                   THEN 1
                   END) AS NoPagos_1_val,
             SUM(CASE
                     WHEN pg.status = 'Procesado'
                         AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                         AND DATE(pg.fecha) BETWEEN pf.start_date_2 AND pf.due_date_2
                 THEN pg.valor ELSE 0
                 END) AS Pagos_2_val
         FROM pagos pg
                  LEFT JOIN pp_fechas pf ON pf.obligacion = pg.obligacion
         GROUP BY pg.obligacion
     ),
     promesas_ultimas AS (
         SELECT
             p.order_id,
    DATE(p.fechapromesa) AS fechapromesa,
    p.agent_id,
    ROW_NUMBER() OVER (
    PARTITION BY p.order_id
    ORDER BY p.fechapromesa DESC
    ) AS rn
FROM "6_promesas" p
    ),
    telefonos_principal AS (
SELECT
    documento,
    MIN(telefono) AS telefono
FROM "14_telefonos"
WHERE parentesco = 'Principal Titular'
GROUP BY documento
    )
SELECT
    pp.loan_id,
    c.obligacion, -- Direct column access instead of AES_DECRYPT
    c.documento AS cedula,
    COALESCE(pc.fch_primer_pago::text, '') AS fch_primer_pago,
    MAX(pp.period_number) AS period_number,
    COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN pps.code END), '') AS status_1,
    CASE WHEN MAX(CASE WHEN pp.period_number = 1 THEN pps.code END) = 'repaid' THEN ''
         ELSE COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN pp.dpd::text END), '')
        END AS dpd_1,
    COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN DATE(pp.start_date) END)::text, '') AS start_date_1,
    COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN
                          CASE
                              WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 1 AND 5 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' - INTERVAL '1 day'
                 WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 6 AND 20 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' + INTERVAL '14 days'
                 WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 21 AND 31 THEN DATE_TRUNC('month', pp.start_date + INTERVAL '1 month') + INTERVAL '1 month' - INTERVAL '1 day'
                 END
                 END)::text, '') AS due_date_1,
    COALESCE('$ ' || REPLACE(TO_CHAR(MAX(CASE WHEN pp.period_number = 1 THEN pp.initial_payment_amount END), 'FM999,999,999'), ',', '.'), '$ 0') AS Cuota_1,
    COALESCE('$ ' || REPLACE(TO_CHAR(pc.Pagos_1_val, 'FM999,999,999'), ',', '.'), '$ 0') AS Pagos_1,
    COALESCE(pc.NoPagos_1_val, 0) AS No_pagos,
    COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN DATE(pp.start_date) END)::text, '') AS start_date_2,
    COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN
                          CASE
                              WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 1 AND 5 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' - INTERVAL '1 day'
                 WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 6 AND 20 THEN DATE_TRUNC('month', pp.start_date) + INTERVAL '1 month' + INTERVAL '14 days'
                 WHEN EXTRACT(DAY FROM pp.start_date) BETWEEN 21 AND 31 THEN DATE_TRUNC('month', pp.start_date + INTERVAL '1 month') + INTERVAL '1 month' - INTERVAL '1 day'
                 END
                 END)::text, '') AS due_date_2,
    CASE WHEN MAX(CASE WHEN pp.period_number = 2 THEN pps.code END) = 'repaid' THEN ''
         ELSE COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN pp.dpd::text END), '')
        END AS dpd_2,
    COALESCE('$ ' || REPLACE(TO_CHAR(MAX(CASE WHEN pp.period_number = 2 THEN pp.initial_payment_amount END), 'FM999,999,999'), ',', '.'), '$ 0') AS Cuota_2,
    COALESCE('$ ' || REPLACE(TO_CHAR(MAX(CASE WHEN pp.period_number = 2 THEN pp.delay_penalty_amount + pp.overdue_interest_amount END), 'FM999,999,999'), ',', '.'), '$ 0') AS Gastos_2,
    COALESCE('$ ' || REPLACE(TO_CHAR(MAX(CASE WHEN pp.period_number = 2 THEN pp.initial_payment_amount + pp.delay_penalty_amount + pp.overdue_interest_amount END), 'FM999,999,999'), ',', '.'), '$ 0') AS Total_cuota_2,
    COALESCE('$ ' || REPLACE(TO_CHAR(pc.Pagos_2_val, 'FM999,999,999'), ',', '.'), '$ 0') AS Pagos_2,
    COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN pps.code END), '') AS status_2,
    COALESCE('$ ' || REPLACE(TO_CHAR(pc.Total_pagos_val, 'FM999,999,999'), ',', '.'), '$ 0') AS Total_pagos,
    /* 🔹 DIF_TOTAL CON NUEVA CONDICIÓN SIN ALTERAR ESTRUCTURA */
    CASE
        WHEN
            MAX(CASE WHEN pp.period_number = 1 THEN pps.code END) = 'repaid'
                AND (
                MAX(CASE WHEN pp.period_number = 2 THEN pps.code END) = 'repaid'
                    OR MAX(CASE WHEN pp.period_number = 2 THEN pps.code END) IS NULL
                    OR MAX(CASE WHEN pp.period_number = 2 THEN pps.code END) = ''
                )
            THEN '$ 0'
        WHEN
            (
                COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN pp.initial_payment_amount END),0)
                    +
                COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN
                                      pp.initial_payment_amount + pp.delay_penalty_amount + pp.overdue_interest_amount
                    END),0)
                    -
                COALESCE(pc.Total_pagos_val,0)
                ) <= 25000
            THEN '$ 0'
        ELSE
            COALESCE('$ ' || REPLACE(TO_CHAR(
                (
                    COALESCE(MAX(CASE WHEN pp.period_number = 1 THEN pp.initial_payment_amount END),0)
                        +
                    COALESCE(MAX(CASE WHEN pp.period_number = 2 THEN
                                          pp.initial_payment_amount + pp.delay_penalty_amount + pp.overdue_interest_amount
                        END),0)
                        -
                    COALESCE(pc.Total_pagos_val,0)
                    ), 'FM999,999,999'), ',', '.'), '$ 0')
        END AS Dif_total,
    '' AS Valor_Cobrar,
    '' AS Estrategia,
    COALESCE(MAX(CASE WHEN pu.rn = 1 THEN pu.fechapromesa::text END), '') AS fchPromesa,
    COALESCE(MAX(CASE WHEN pu.rn = 1 THEN u.usuario END), '') AS Asesor,
    COALESCE(MAX(cli.nombre), '') AS Nombre,
    COALESCE(MAX(m.email), '') AS correo,
    COALESCE(MAX(tp.telefono), '') AS Numero
FROM payment_periods pp
         LEFT JOIN "9_cobranzas" c ON c.id = pp.loan_id
         LEFT JOIN payment_period_statuses pps ON pps.id = pp.status_id
         LEFT JOIN pagos_calculados pc
                   ON pc.obligacion = c.obligacion -- Direct column access instead of AES_DECRYPT
         LEFT JOIN promesas_ultimas pu
                   ON pu.order_id = c.obligacion -- Direct column access instead of AES_DECRYPT
                       AND pu.rn = 1
         LEFT JOIN collector_usuarios_drpeso.usuarios u
                   ON u.id = pu.agent_id
         LEFT JOIN "10_clientes" cli
                   ON cli.documento = c.documento
         LEFT JOIN mails m
                   ON m.documento = c.documento
         LEFT JOIN telefonos_principal tp
                   ON tp.documento = c.documento
GROUP BY pp.loan_id, c.obligacion, c.documento, pc.Total_pagos_val, pc.Pagos_1_val, pc.NoPagos_1_val, pc.Pagos_2_val, pc.fch_primer_pago;
