defmodule Towerops.Repo.Migrations.AddIsDefaultToOrganizationMemberships do
  use Ecto.Migration

  def change do
    alter table(:organization_memberships) do
      add :is_default, :boolean, default: false, null: false
    end

    create index(:organization_memberships, [:user_id, :is_default],
             where: "is_default = true",
             name: :organization_memberships_user_id_default_index
           )

    # Set the first organization for each user as their default
    execute(
      """
      UPDATE organization_memberships
      SET is_default = true
      WHERE id IN (
        SELECT DISTINCT ON (user_id) id
        FROM organization_memberships
        ORDER BY user_id, inserted_at ASC
      )
      """,
      # Rollback: clear all defaults
      "UPDATE organization_memberships SET is_default = false"
    )
  end
end
