-- FPD 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,
        -- 🔹 Aplicando misma lógica de redondeo al millar sin decimales
        CONCAT('$ ', TO_CHAR(ROUND(COALESCE(SUM(pp.principal_debt_amount), 0), -3), 'FM999,999,999')) AS Vlr_Desemb,
        DATE(MIN(pp.start_date)) AS start_date_1,
        CASE 
            WHEN EXTRACT(DAY FROM MIN(pp.start_date)) BETWEEN 1 AND 5 
                THEN DATE_TRUNC('month', MIN(pp.start_date)) + INTERVAL '1 month' - INTERVAL '1 day'
            WHEN EXTRACT(DAY FROM MIN(pp.start_date)) BETWEEN 6 AND 20 
                THEN DATE_TRUNC('month', MIN(pp.start_date)) + INTERVAL '1 month' + INTERVAL '14 days'
            WHEN EXTRACT(DAY FROM MIN(pp.start_date)) BETWEEN 21 AND 31 
                THEN DATE_TRUNC('month', MIN(pp.start_date) + INTERVAL '1 month') + INTERVAL '1 month' - INTERVAL '1 day'
        END AS due_date_1
    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_calculados AS (
    SELECT 
        pg.radicado 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,
        COUNT(CASE 
            WHEN pg.status = 'Procesado'
                AND pg.tipopago NOT IN ('Discount FEE','Discount LTM','Discount PM','Discount PYA','Discount SUP','Write off')
            THEN 1
        END) AS No_pagos
    FROM pagos pg
    GROUP BY pg.radicado
)
SELECT 
    pf.loan_id,
    pf.obligacion,
    pf.documento,
    pf.Vlr_Desemb,
    pf.start_date_1 AS start_date,
    pf.due_date_1 AS due_date,
    COALESCE(pc.fch_primer_pago::text, '') AS fch_primer_pago,
    CASE 
        WHEN pc.fch_primer_pago IS NULL THEN ''
        ELSE EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1))::text
    END AS dias_due_date,
    COALESCE(pc.No_pagos, 0) AS No_pagos,
    CASE 
        WHEN pc.fch_primer_pago IS NULL THEN 0
        WHEN EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1)) <= 0 THEN 1
        ELSE 0
    END AS Fpd_0,
    CASE 
        WHEN pc.fch_primer_pago IS NULL THEN 0
        WHEN EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1)) <= 5 THEN 1
        ELSE 0
    END AS Fpd_5,
    CASE 
        WHEN pc.fch_primer_pago IS NULL THEN 0
        WHEN EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1)) <= 15 THEN 1
        ELSE 0
    END AS Fpd_15,
    CASE 
        WHEN pc.fch_primer_pago IS NULL THEN 0
        WHEN EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1)) <= 30 THEN 1
        ELSE 0
    END AS Fpd_30
FROM pp_fechas pf
LEFT JOIN pagos_calculados pc
    ON pc.obligacion = pf.obligacion
ORDER BY pf.loan_id;