
WITH candidate_final_sales AS (
    SELECT DISTINCT ON (pp.id_plan)
        pp.id_plan,
        v.id_venta
    FROM commerce.planes_pago pp
    JOIN commerce.ventas v
      ON v.id_plan_pago = pp.id_plan
     AND v.business_id = pp.business_id
     AND v.soft_delete IS NOT TRUE
    LEFT JOIN LATERAL (
        SELECT
            COUNT(*) FILTER (
                WHERE t.soft_delete IS NOT TRUE
                  AND t.item ILIKE 'Enganche - %'
            ) AS enganche_count,
            COUNT(*) FILTER (
                WHERE t.soft_delete IS NOT TRUE
            ) AS total_count
        FROM commerce.transacciones t
        WHERE t.id_venta = v.id_venta
          AND t.business_id = v.business_id
    ) tx ON TRUE
    WHERE pp.estado = 3
      AND pp.soft_delete IS NOT TRUE
      AND COALESCE(tx.total_count, 0) > 0
      AND COALESCE(tx.enganche_count, 0) < COALESCE(tx.total_count, 0)
    ORDER BY pp.id_plan, v.fecha_venta DESC NULLS LAST, v.id_venta DESC
),
normalized_completed AS (
    UPDATE commerce.planes_pago pp
       SET id_venta = cfs.id_venta,
           updated_at = NOW()
      FROM candidate_final_sales cfs
     WHERE pp.id_plan = cfs.id_plan
       AND pp.estado = 3
       AND pp.soft_delete IS NOT TRUE
       AND pp.id_venta IS DISTINCT FROM cfs.id_venta
    RETURNING pp.id_plan
)
UPDATE commerce.planes_pago pp
   SET id_venta = NULL,
       updated_at = NOW()
 WHERE pp.soft_delete IS NOT TRUE
   AND pp.id_venta IS NOT NULL
   AND (
       pp.estado <> 3
       OR NOT EXISTS (
           SELECT 1
           FROM candidate_final_sales cfs
           WHERE cfs.id_plan = pp.id_plan
       )
   );
