BEGIN;

WITH normalized AS (
    SELECT
        pp.id_plan,
        pp.business_id,
        pp.id_plan::text AS normalized_contract_folio,
        CASE
            WHEN jsonb_typeof(COALESCE(pp.configuracion_plan, '{}'::jsonb)) = 'object'
                THEN jsonb_set(
                    CASE
                        WHEN jsonb_typeof(COALESCE(pp.configuracion_plan->'contrato_snapshot', 'null'::jsonb)) = 'object'
                            THEN jsonb_set(
                                COALESCE(pp.configuracion_plan, '{}'::jsonb),
                                '{contrato_snapshot,contract_folio}',
                                to_jsonb(pp.id_plan::text),
                                true
                            )
                        ELSE COALESCE(pp.configuracion_plan, '{}'::jsonb)
                    END,
                    '{contract_folio}',
                    to_jsonb(pp.id_plan::text),
                    true
                )
            ELSE COALESCE(pp.configuracion_plan, '{}'::jsonb)
        END AS normalized_configuracion_plan
    FROM commerce.planes_pago pp
    WHERE pp.soft_delete IS NOT TRUE
)
UPDATE commerce.planes_pago AS pp
SET
    contract_folio = normalized.normalized_contract_folio,
    configuracion_plan = normalized.normalized_configuracion_plan,
    updated_at = NOW()
FROM normalized
WHERE pp.id_plan = normalized.id_plan
  AND pp.business_id = normalized.business_id
  AND (
      NULLIF(BTRIM(COALESCE(pp.contract_folio, '')), '') IS DISTINCT FROM normalized.normalized_contract_folio
      OR COALESCE(pp.configuracion_plan->>'contract_folio', '') IS DISTINCT FROM normalized.normalized_contract_folio
      OR (
          jsonb_typeof(COALESCE(pp.configuracion_plan->'contrato_snapshot', 'null'::jsonb)) = 'object'
          AND COALESCE(pp.configuracion_plan->'contrato_snapshot'->>'contract_folio', '') IS DISTINCT FROM normalized.normalized_contract_folio
      )
  );

COMMIT;
