class AddChoiceIndexUniqIndexToExerciseAnswers < ActiveRecord::Migration[5.2]
  def change
    sql = %Q(delete from exercise_answers where (exercise_question_id, user_id, choice_index) in
            (select * from (select exercise_question_id, user_id, choice_index from exercise_answers group by exercise_question_id, user_id, choice_index having count(*) > 1) a)
            and id not in (select * from (select max(id) from exercise_answers group by exercise_question_id, user_id, choice_index having count(*) > 1 order by id) b))
    ActiveRecord::Base.connection.execute sql

    add_index :exercise_answers, [:exercise_question_id, :user_id, :choice_index], name: 'exercise_user_choice_index', unique: true

    remove_index :exercise_answers, name: :exercise_choice_index

  end
end
