
WITH candidate_plan_openings AS (
    SELECT
        pp.id_plan,
        pp.business_id,
        pp.id_transaccion_origen AS id_transaccion,
        tx.id_venta
    FROM commerce.planes_pago pp
    INNER JOIN commerce.transacciones tx
        ON tx.id_transaccion = pp.id_transaccion_origen
       AND tx.business_id = pp.business_id
       AND tx.soft_delete IS NOT TRUE
    INNER JOIN commerce.ventas v
        ON v.id_venta = tx.id_venta
       AND v.business_id = pp.business_id
       AND v.soft_delete IS NOT TRUE
    INNER JOIN LATERAL (
        SELECT
            COUNT(*) AS tx_count,
            BOOL_AND(t.item ILIKE 'Enganche - %') AS solo_enganche
        FROM commerce.transacciones t
        WHERE t.id_venta = v.id_venta
          AND t.business_id = v.business_id
          AND t.soft_delete IS NOT TRUE
    ) sale_tx ON TRUE
    LEFT JOIN LATERAL (
        SELECT COUNT(*) AS total
        FROM commerce.comprobantes_transacciones ct
        WHERE ct.business_id = pp.business_id
          AND ct.soft_delete IS NOT TRUE
          AND (
              ct.id_venta = v.id_venta
              OR ct.id_transaccion = tx.id_transaccion
          )
    ) comprobantes ON TRUE
    LEFT JOIN LATERAL (
        SELECT COUNT(*) AS total
        FROM platform.notas_internas ni
        WHERE ni.business_id = pp.business_id
          AND ni.soft_delete IS NOT TRUE
          AND ni.entidad = 2
          AND ni.id_venta = v.id_venta
    ) notas ON TRUE
    WHERE pp.soft_delete IS NOT TRUE
      AND pp.id_venta IS NULL
      AND pp.id_transaccion_origen IS NOT NULL
      AND v.id_plan_pago = pp.id_plan
      AND tx.id_plan_pago = pp.id_plan
      AND sale_tx.tx_count = 1
      AND sale_tx.solo_enganche IS TRUE
      AND COALESCE(comprobantes.total, 0) = 0
      AND COALESCE(notas.total, 0) = 0
),
soft_deleted_tx AS (
    UPDATE commerce.transacciones tx
       SET soft_delete = TRUE,
           soft_delete_date = NOW()
      FROM candidate_plan_openings cpo
     WHERE tx.id_transaccion = cpo.id_transaccion
       AND tx.business_id = cpo.business_id
       AND tx.soft_delete IS NOT TRUE
    RETURNING tx.id_transaccion
),
soft_deleted_sales AS (
    UPDATE commerce.ventas v
       SET soft_delete = TRUE,
           soft_delete_date = NOW(),
           updated_at = NOW()
      FROM candidate_plan_openings cpo
     WHERE v.id_venta = cpo.id_venta
       AND v.business_id = cpo.business_id
       AND v.soft_delete IS NOT TRUE
    RETURNING v.id_venta
)
UPDATE commerce.planes_pago pp
   SET id_transaccion_origen = NULL,
       updated_at = NOW()
  FROM candidate_plan_openings cpo
 WHERE pp.id_plan = cpo.id_plan
   AND pp.business_id = cpo.business_id
   AND pp.soft_delete IS NOT TRUE;
