class AddUniqueIndexToLabels < ActiveRecord::Migration
  include Gitlab::Database::MigrationHelpers

  DOWNTIME = true
  DOWNTIME_REASON = 'This migration removes duplicated labels.'

  disable_ddl_transaction!

  def up
    select_all('SELECT title, COUNT(id) as cnt FROM labels GROUP BY project_id, title HAVING COUNT(id) > 1').each do |label|
      label_title = quote_string(label['title'])
      duplicated_ids = select_all("SELECT id FROM labels WHERE title = '#{label_title}' ORDER BY id ASC").map{ |label| label['id'] }
      label_id = duplicated_ids.first
      duplicated_ids.delete(label_id)

      execute("UPDATE label_links SET label_id = #{label_id} WHERE label_id IN(#{duplicated_ids.join(",")})")
      execute("DELETE FROM labels WHERE id IN(#{duplicated_ids.join(",")})")
    end

    remove_index :labels, column: :project_id if index_exists?(:labels, :project_id)
    remove_index :labels, column: :title if index_exists?(:labels, :title)

    add_concurrent_index :labels, [:group_id, :project_id, :title], unique: true
  end

  def down
    remove_index :labels, column: [:group_id, :project_id, :title] if index_exists?(:labels, [:group_id, :project_id, :title], unique: true)

    add_concurrent_index :labels, :project_id
    add_concurrent_index :labels, :title
  end
end

class AddUniqueIndexToLabels < ActiveRecord::Migration
  include Gitlab::Database::MigrationHelpers

  DOWNTIME = true
  DOWNTIME_REASON = 'This migration removes duplicated labels.'

  disable_ddl_transaction!

  def up
    select_all('SELECT title, project_id, COUNT(id) as cnt FROM labels GROUP BY project_id, title HAVING COUNT(id) > 1').each do |label|
      label_title = quote_string(label['title'])
      duplicated_ids = select_all("SELECT id FROM labels WHERE project_id = #{label['project_id']} AND title = '#{label_title}' ORDER BY id ASC").map{ |label| label['id'] }
      label_id = duplicated_ids.first
      duplicated_ids.delete(label_id)

      execute("UPDATE label_links SET label_id = #{label_id} WHERE label_id IN(#{duplicated_ids.join(",")})")
      execute("DELETE FROM labels WHERE id IN(#{duplicated_ids.join(",")})")
    end

    remove_index :labels, column: :project_id if index_exists?(:labels, :project_id)
    remove_index :labels, column: :title if index_exists?(:labels, :title)

    add_concurrent_index :labels, [:group_id, :project_id, :title], unique: true
  end

  def down
    remove_index :labels, column: [:group_id, :project_id, :title] if index_exists?(:labels, [:group_id, :project_id, :title], unique: true)

    add_concurrent_index :labels, :project_id
    add_concurrent_index :labels, :title
  end
end

Fix broken label uniqueness label migration
Fix broken label uniqueness label migration

The previous implementation of the migration failed on staging because
the migration was attempted to remove labels from projects that did not
actually have duplicates. This occurred because the SQL query did not
account for the project ID when selecting the labels.

To replicate the problem:

1. Disable the uniqueness validation in app/models/label.rb.
2. Create a duplicate label "bug" in project A.
3. Create the same label in project B with label "bug".

The migration will attempt to remove the label in B even if there are no duplicates.

Closes #23609
