-- BASE DE COBRANZAS TRABAJO DIARIO - PostgreSQL Version
-- Converted from MySQL to PostgreSQL
-- Note: Encrypted columns are no longer encrypted

WITH promesas_ultimas AS (
    SELECT
        p.order_id,
        p.agent_id,
        ROW_NUMBER() OVER (
            PARTITION BY p.order_id
            ORDER BY p.fechapromesa DESC
        ) AS rn
    FROM collector_drpeso."6_promesas" p
),
     base AS (
         SELECT
             cbz.documento AS Cedula,
             cbz.obligacion AS Orden,
             '57' AS phone_code,
             COALESCE(cli.nombre, '') AS Nombre,
             COALESCE(MAX(
                          CASE WHEN tel.parentesco = 'Principal Titular' THEN
                                   REPLACE(
                                       CASE
                                           WHEN LEFT(tel.telefono, 2) = '57' THEN SUBSTRING(tel.telefono, 3)
                                           ELSE tel.telefono
                                           END, '.0', ''
                                   )
                              END
                      ), '') AS Tel_Principal,
             COALESCE(MAX(
                          CASE WHEN tel.parentesco = 'Numero Contacto' THEN
                                   CASE
                                       WHEN LENGTH(REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g')) = 10
                                           AND (REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g') LIKE '3%'
                                               OR REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g') LIKE '60%')
                                           THEN REPLACE(
                                           CASE
                                               WHEN LEFT(tel.telefono, 2) = '57' THEN SUBSTRING(tel.telefono, 3)
                                               ELSE tel.telefono
                                               END, '.0', ''
                                                )
                                       ELSE ''
                                       END
                              END
                      ), '') AS Tel_Contacto,
             COALESCE(MAX(
                          CASE WHEN tel.parentesco = 'Numero Empresa' THEN
                                   CASE
                                       WHEN LENGTH(REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g')) = 10
                                           AND (REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g') LIKE '3%'
                                               OR REGEXP_REPLACE(tel.telefono, '[^0-9]', '', 'g') LIKE '60%')
                                           THEN REPLACE(
                                           CASE
                                               WHEN LEFT(tel.telefono, 2) = '57' THEN SUBSTRING(tel.telefono, 3)
                                               ELSE tel.telefono
                                               END, '.0', ''
                                                )
                                       ELSE ''
                                       END
                              END
                      ), '') AS Tel_Empresa,
             COALESCE(mail.email, '') AS Email,
             COALESCE(cbz.estrategia, '') AS Tipo,
             COALESCE(cbz.estado, '') AS Estado,
             COALESCE(cbz.casa_id::text, '') AS Casa_ID,
             COALESCE(cbz.year::text, '') AS Year,
    COALESCE(cbz.month::text, '') AS Month,
    COALESCE(cbz.week::text, '') AS Week,
    COALESCE(TO_CHAR(cbz.fechadesembolso::date, 'DD/MM/YYYY'), '') AS fechadesembolso,
    COALESCE(TO_CHAR(cbz.fechavencimiento::date, 'DD/MM/YYYY'), '') AS fechavencimiento,
    COALESCE(TO_CHAR(cli.fecha_ultimo_contacto::date, 'DD/MM/YYYY'), '') AS fch_ult_cont,
    CASE
    WHEN p.tiene_pago = 1 THEN 'si'
    ELSE 'no'
END AS Hizo_pago,
        COALESCE('$ ' || REPLACE(TO_CHAR(cbz.credito::numeric, 'FM999,999,999'), ',', '.'), '$ 0') AS Cap_Vigente,
        COALESCE('$ ' || REPLACE(TO_CHAR(cbz.saldoaldia::numeric, 'FM999,999,999'), ',', '.'), '$ 0') AS Pago_total,
        COALESCE('$ ' || REPLACE(TO_CHAR(cbz.amount_for_loan_prologation::numeric, 'FM999,999,999'), ',', '.'), '$ 0') AS Ext_20_dias,
        COALESCE('$ ' || REPLACE(TO_CHAR(cbz.prolongation_10_days::numeric, 'FM999,999,999'), ',', '.'), '$ 0') AS Ext_10_dias,
        COALESCE(cbz.dpd::text, '') AS Dias_mora,
        COALESCE(cbz.dpd, 0) AS Dias_mora_num,
        COALESCE(TO_CHAR(cli.fechapromesa::date, 'DD/MM/YYYY'), '') AS fchprom,
        COALESCE('$ ' || REPLACE(TO_CHAR(cli.valoracuerdo::numeric, 'FM999,999,999'), ',', '.'), '$ 0') AS vlrprom,
        -- ASESOR DESDE 6_PROMESAS (última)
        COALESCE(usr.usuario, '') AS Ases_prom
    FROM collector_drpeso."9_cobranzas" cbz
    LEFT JOIN collector_drpeso."10_clientes" cli
        ON cbz.documento = cli.documento
    LEFT JOIN collector_drpeso."14_telefonos" tel
        ON cbz.documento = tel.documento
    LEFT JOIN collector_drpeso.mails mail
        ON cbz.documento = mail.documento
    -- REEMPLAZA 32_VINCULO POR PROMESAS
    LEFT JOIN promesas_ultimas pu
        ON pu.order_id = cbz.obligacion
        AND pu.rn = 1
    LEFT JOIN collector_usuarios_drpeso.usuarios usr
        ON usr.id = pu.agent_id
    LEFT JOIN (
        SELECT
            obligacion,
            1 AS tiene_pago
        FROM collector_drpeso.pagos
        WHERE status = 'procesado'
            AND status <> 'Reverted'
            AND tipopago NOT IN (
                'Discount FEE','Discount LTM','Discount PM',
                'Discount PYA','Discount SUP','Write off','momo'
            )
        GROUP BY obligacion
    ) p ON cbz.obligacion = p.obligacion
    WHERE cbz.dpd >= -5
        AND cbz.estado IN ('active', 'outloanterm')
        AND cbz.casa_id IN (1, 18, 19, 20, 25)
    GROUP BY
        cbz.documento,
        cbz.obligacion,
        cli.nombre,
        cbz.credito,
        cbz.saldoaldia,
        cbz.amount_for_loan_prologation,
        cbz.prolongation_10_days,
        cbz.dpd,
        cbz.estrategia,
        cbz.estado,
        cbz.casa_id,
        cbz.year,
        cbz.month,
        cbz.week,
        cbz.fechadesembolso,
        cbz.fechavencimiento,
        cli.fecha_ultimo_contacto,
        cli.fechapromesa,
        cli.valoracuerdo,
        mail.email,
        usr.usuario,
        p.tiene_pago
)
SELECT DISTINCT
    '' AS "LIST ID",
    Cedula,
    phone_code,
    Orden,
    Nombre,
    Tel_Principal,
    CASE WHEN Tel_Contacto = Tel_Principal THEN '' ELSE Tel_Contacto END AS Tel_Contacto,
    CASE WHEN Tel_Empresa = Tel_Principal OR Tel_Empresa = Tel_Contacto THEN '' ELSE Tel_Empresa END AS Tel_Empresa,
    Email,
    Tipo,
    Estado,
    Casa_ID,
    Year,
    Month,
    Week,
    fechadesembolso,
    fechavencimiento,
    fch_ult_cont,
    Hizo_pago,
    Cap_Vigente,
    Pago_total,
    Ext_20_dias,
    Ext_10_dias,
    Dias_mora,
    CASE
    WHEN Dias_mora_num <= -6 THEN '(<-6)'
    WHEN Dias_mora_num BETWEEN -5 AND -1 THEN '(-5/-1)'
    WHEN Dias_mora_num = 0 THEN '(cero)'
    WHEN Dias_mora_num BETWEEN 1 AND 5 THEN '(1-5)'
    WHEN Dias_mora_num BETWEEN 6 AND 15 THEN '(6-15)'
    WHEN Dias_mora_num BETWEEN 31 AND 60 THEN '(31-60)'
    WHEN Dias_mora_num BETWEEN 61 AND 90 THEN '(61-90)'
    WHEN Dias_mora_num BETWEEN 16 AND 30 THEN '(16-30)'
    ELSE '(90+)'
END AS Franja,
    fchprom,
    vlrprom,
    Ases_prom,
    CONCAT(
        ROUND(
            (
                (CASE WHEN Hizo_pago = 'si' THEN 1 ELSE 0 END) * 0.5
                + (
                    CASE
                        WHEN fch_ult_cont = '' THEN 0
                        WHEN (CURRENT_DATE - TO_DATE(fch_ult_cont, 'DD/MM/YYYY')) <= 3 THEN 0.9
                        WHEN (CURRENT_DATE - TO_DATE(fch_ult_cont, 'DD/MM/YYYY')) <= 7 THEN 0.7
                        WHEN (CURRENT_DATE - TO_DATE(fch_ult_cont, 'DD/MM/YYYY')) <= 15 THEN 0.5
                        ELSE 0.2
                    END
                ) * 0.3
                + (
                    CASE
                        WHEN Dias_mora_num <= 0 THEN 0.9
                        WHEN Dias_mora_num BETWEEN 1 AND 5 THEN 0.7
                        WHEN Dias_mora_num BETWEEN 6 AND 15 THEN 0.5
                        WHEN Dias_mora_num BETWEEN 16 AND 30 THEN 0.3
                        ELSE 0.1
                    END
                ) * 0.2
            ) * 100
        , 0)
    , '%'
    ) AS Prob_pago_pct,
    '' AS Llamada,
    '' AS Whatsapp,
    '' AS Acuerdo,
    '' AS Tarea
FROM base
GROUP BY
    Cedula, phone_code, Orden, Nombre, Tel_Principal, Tel_Contacto, Tel_Empresa,
    Email, Tipo, Estado, Casa_ID, Year, Month, Week, fechadesembolso, fechavencimiento,
    fch_ult_cont, Hizo_pago, Cap_Vigente, Pago_total, Ext_20_dias, Ext_10_dias,
    Dias_mora, Dias_mora_num, Franja, fchprom, vlrprom, Ases_prom, Prob_pago_pct
ORDER BY Cedula, Orden;
