Reconcile Seat Overage

What does this MR do and why?

Reconcile Seat Overage

When the quantity of AddOnPurchase decreases during renewal or by direct amendment in Zuora (via Sales), we want to automatically remove the seat overage.The code diff introduces a new service, ReconcileSeatOverageService, which ensures that the number of assigned users to an add-on purchase does not exceed the allowed quantity. If there is an overage, the service removes the extra assignments, prioritizing users who haven't used the add-on's features recently. Additionally, a new scope, order_by_id_desc, is added to the UserAddOnAssignment model, allowing assignments to be ordered by their ID in descending order.

This is a new service which will be used in follow-up MR whenever GitlabSubscriptions::AddOnPurchases::UpdateService#execute is called.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

1. Check out this branch
2. Create a new root group namespace
3. Setup some seed records

namespace = Namespace.last
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}

# create new add_on_purchase 
add_on_purchase = GitlabSubscriptions::AddOnPurchase.create!(
  add_on: add_on, namespace: namespace, expires_on: 1.month.from_now, quantity: 5, purchase_xid: 'A-S0001', created_at: 1.week.ago
)

user_1, user_2 = User.last(2)

# assign seat to the user
add_on_purchase.assigned_users.create(user: user_1)
add_on_purchase.assigned_users.create(user: user_2)

add_on_purchase.assigned_users.count # 2
add_on_purchase.quantity # 5

# instantiate the service and execute
service = GitlabSubscriptions::AddOnPurchases::ReconcileSeatOverageService.new(add_on_purchase: add_on_purchase)
service.execute.payload # {:removed_seats_count=>0}

add_on_purchase.update!(quantity: 1) # create overage
add_on_purchase.reload

service = GitlabSubscriptions::AddOnPurchases::ReconcileSeatOverageService.new(add_on_purchase: add_on_purchase)
service.execute.payload # {:removed_seats_count=>1}

add_on_purchase.assigned_users.where(user: user_2).count # 0 , user removed

With Clickhouse enabled

1. Ensure that ClickHouse is set up for your GDK: https://docs.gitlab.com/ee/development/database/clickhouse/clickhouse_within_gitlab.html

2. In rails console enable following settings

   Feature.enable(:code_suggestion_events_in_click_house)
   Feature.enable(:ai_tracking_data_gathering)
   Gitlab::CurrentSettings.current_application_settings.update(use_clickhouse_for_analytics: true)

3. Insert some data

add_on_purchase.assigned_users.destroy_all
user_1, user_2, user_3 = User.last(3)

add_on_purchase.assigned_users.create(user: user_1)
add_on_purchase.assigned_users.create(user: user_2)
add_on_purchase.assigned_users.create(user: user_3)

add_on_purchase.assigned_users.count # 3

insert_query = <<~SQL
  INSERT INTO code_suggestion_usages
  (user_id, event, timestamp)
  VALUES
  (#{user_1.id}, 1, #{4.days.ago.to_time.utc.to_f})
  (#{user_2.id}, 1, #{3.days.ago.to_time.utc.to_f})
  (#{user_1.id}, 1, #{2.days.ago.to_time.utc.to_f})
  (#{user_3.id}, 1, #{1.day.ago.to_time.utc.to_f})
SQL

ClickHouse::Client.execute(insert_query, :main)

3. Run the Service

# instantiate the service and execute
service = GitlabSubscriptions::AddOnPurchases::ReconcileSeatOverageService.new(add_on_purchase: add_on_purchase.reload)
service.execute.payload # {:removed_seats_count=>3}

add_on_purchase.assigned_users.where(user: user_1).count # 0
add_on_purchase.assigned_users.where(user: user_2).count # 0

add_on_purchase.assigned_users.where(user: user_3).count # 1
add_on_purchase.assigned_users.count # 1
add_on_purchase.quantity # 1

Database

No new query was added as a part of this MR, but existing query were reused. For the shake of completeness:

Fetching user_ids

SELECT "subscription_user_add_on_assignments"."user_id" FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 64 ORDER BY "subscription_user_add_on_assignments"."id" DESC

PostgresAI: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27622/commands/86156

Deleting assignments

DELETE FROM "subscription_user_add_on_assignments" WHERE "subscription_user_add_on_assignments"."add_on_purchase_id" = 64 AND "subscription_user_add_on_assignments"."id" >= 111 AND "subscription_user_add_on_assignments"."user_id" IN (615, 616)

PostgresAI: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27622/commands/86155

---

Related to #456823