-- QUERY CALCULO FPD IL - 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,
        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,
        SUM(pp.principal_debt_amount) AS Vlr_Desemb_credito   -- valor desembolsado por crédito
    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
),
datos_credito AS (
    SELECT 
        pf.loan_id,
        pf.due_date_1 AS due_date,
        pf.Vlr_Desemb_credito,
        COALESCE(pc.fch_primer_pago, NULL) AS fch_primer_pago,
        CASE 
            WHEN pc.fch_primer_pago IS NULL THEN NULL
            ELSE EXTRACT(DAY FROM (pc.fch_primer_pago - pf.due_date_1))::int
        END AS dias_due_date,
        COALESCE(pc.No_pagos, 0) AS No_pagos,
        -- FPD acumulativos: 1 = NO ha pagado dentro del rango
        CASE WHEN pc.fch_primer_pago IS NULL OR 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 OR 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 OR 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 OR 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
)
SELECT 
    due_date,
    CONCAT('$ ', TO_CHAR(SUM(Vlr_Desemb_credito), 'FM999,999,999')) AS Vlr_Desemb_total,
    COUNT(*) AS loans,
    CONCAT(ROUND((SUM(Fpd_0)::numeric / COUNT(*)::numeric * 100), 2), '%') AS FPD_0,
    CONCAT(ROUND((SUM(Fpd_5)::numeric / COUNT(*)::numeric * 100), 2), '%') AS FPD_5,
    CONCAT(ROUND((SUM(Fpd_15)::numeric / COUNT(*)::numeric * 100), 2), '%') AS FPD_15,
    CONCAT(ROUND((SUM(Fpd_30)::numeric / COUNT(*)::numeric * 100), 2), '%') AS FPD_30
FROM datos_credito
GROUP BY due_date
ORDER BY due_date;