BEGIN;

WITH resolved AS (
    SELECT
        pp.id_plan,
        pp.business_id,
        CASE
            WHEN NULLIF(BTRIM(COALESCE(pp.contract_folio, '')), '') IS NOT NULL
                THEN BTRIM(pp.contract_folio)
            ELSE
                'PP-'
                || COALESCE(
                    NULLIF(REGEXP_REPLACE(UPPER(COALESCE(pp.business_id, '')), '[^A-Z0-9]+', '', 'g'), ''),
                    'BUSINESS'
                )
                || '-'
                || pp.id_plan::text
        END AS resolved_contract_folio,
        CASE
            WHEN UPPER(COALESCE(pp.currency_code, '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.currency_code)
            WHEN UPPER(COALESCE(pp.configuracion_plan->>'currency_code', '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.configuracion_plan->>'currency_code')
            WHEN UPPER(COALESCE(pp.configuracion_plan->'contrato_snapshot'->>'currency_code', '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.configuracion_plan->'contrato_snapshot'->>'currency_code')
            WHEN UPPER(COALESCE(pp.configuracion_plan->'resumen_financiero'->>'currency_code', '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.configuracion_plan->'resumen_financiero'->>'currency_code')
            WHEN UPPER(COALESCE(pp.configuracion_plan->'hechos_contractuales'->'modelo_financiero'->>'currency_code', '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.configuracion_plan->'hechos_contractuales'->'modelo_financiero'->>'currency_code')
            WHEN UPPER(COALESCE(pp.configuracion_plan->'hechos_contractuales'->'negocio'->>'currency_code', '')) ~ '^[A-Z]{3}$'
                THEN UPPER(pp.configuracion_plan->'hechos_contractuales'->'negocio'->>'currency_code')
            ELSE 'MXN'
        END AS resolved_currency_code,
        COALESCE(
            pp.fecha_firma_contrato,
            CASE
                WHEN PG_INPUT_IS_VALID(pp.configuracion_plan->>'captured_at', 'timestamp with time zone')
                    THEN (pp.configuracion_plan->>'captured_at')::timestamptz
                ELSE NULL
            END,
            pp.created_at
        ) AS resolved_fecha_firma_contrato
    FROM commerce.planes_pago pp
    WHERE pp.soft_delete IS NOT TRUE
)
UPDATE commerce.planes_pago AS pp
SET
    contract_folio = resolved.resolved_contract_folio,
    currency_code = resolved.resolved_currency_code,
    fecha_firma_contrato = COALESCE(pp.fecha_firma_contrato, resolved.resolved_fecha_firma_contrato),
    updated_at = NOW()
FROM resolved
WHERE pp.id_plan = resolved.id_plan
  AND pp.business_id = resolved.business_id
  AND (
      NULLIF(BTRIM(COALESCE(pp.contract_folio, '')), '') IS NULL
      OR UPPER(COALESCE(pp.currency_code, '')) !~ '^[A-Z]{3}$'
      OR pp.fecha_firma_contrato IS NULL
  );

COMMIT;
