-- QUERY PAGOS 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 AS obligacion, -- Direct column access instead of AES_DECRYPT
        c.nuip AS documento,
        COALESCE(SUM(pp.principal_debt_amount), 0) AS Vlr_Desemb,
        DATE(MIN(pp.start_date)) AS start_date,  -- start_date truncado a fecha
        MIN(
            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
        ) AS due_date
    FROM payment_periods pp
    LEFT JOIN "9_cobranzas" c ON c.idCreditos = pp.loan_id
    GROUP BY pp.loan_id, c.obligacion, c.nuip
),
pagos_mensuales AS (
    SELECT 
        pg.radicado AS obligacion,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 12 THEN pg.valor ELSE 0 END) AS Pagos_Dic,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 1 THEN pg.valor ELSE 0 END) AS Pagos_Ener,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 2 THEN pg.valor ELSE 0 END) AS Pagos_Febr,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 3 THEN pg.valor ELSE 0 END) AS Pagos_Marz,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 4 THEN pg.valor ELSE 0 END) AS Pagos_Abri,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 5 THEN pg.valor ELSE 0 END) AS Pagos_Mayo,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 6 THEN pg.valor ELSE 0 END) AS Pagos_Juni,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 7 THEN pg.valor ELSE 0 END) AS Pagos_Juli,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 8 THEN pg.valor ELSE 0 END) AS Pagos_Agos,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 9 THEN pg.valor ELSE 0 END) AS Pagos_Sept,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 10 THEN pg.valor ELSE 0 END) AS Pagos_Octu,
        SUM(CASE WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
                AND EXTRACT(MONTH FROM pg.fecha) = 11 THEN pg.valor ELSE 0 END) AS Pagos_Novi,
        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
    FROM pagos pg
    GROUP BY pg.radicado
)
SELECT 
    pf.loan_id,
    pf.obligacion,
    pf.documento,
    CONCAT('$ ', TO_CHAR(pf.Vlr_Desemb, 'FM999,999,999')) AS Vlr_Desemb,
    pf.start_date,
    pf.due_date,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Dic, 'FM999,999,999')), '$ 0') AS Pagos_Dic,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Ener, 'FM999,999,999')), '$ 0') AS Pagos_Ener,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Febr, 'FM999,999,999')), '$ 0') AS Pagos_Febr,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Marz, 'FM999,999,999')), '$ 0') AS Pagos_Marz,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Abri, 'FM999,999,999')), '$ 0') AS Pagos_Abri,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Mayo, 'FM999,999,999')), '$ 0') AS Pagos_Mayo,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Juni, 'FM999,999,999')), '$ 0') AS Pagos_Juni,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Juli, 'FM999,999,999')), '$ 0') AS Pagos_Juli,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Agos, 'FM999,999,999')), '$ 0') AS Pagos_Agos,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Sept, 'FM999,999,999')), '$ 0') AS Pagos_Sept,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Octu, 'FM999,999,999')), '$ 0') AS Pagos_Octu,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Pagos_Novi, 'FM999,999,999')), '$ 0') AS Pagos_Novi,
    COALESCE(CONCAT('$ ', TO_CHAR(pm.Total_Pagos, 'FM999,999,999')), '$ 0') AS Total_Pagos
FROM pp_fechas pf
LEFT JOIN pagos_mensuales pm
    ON pm.obligacion = pf.obligacion
ORDER BY pf.loan_id;