class FixTableCharacterset < ActiveRecord::Migration[6.0]
  def change
    execute("SET foreign_key_checks = 0")

    latin_tables_sql = <<~SQL
      SELECT TABLE_NAME, TABLE_COLLATION
      FROM information_schema.TABLES
      WHERE table_schema = '#{ActiveRecord::Base.connection.current_database}'
      AND table_collation != 'utf8_unicode_ci';
    SQL

    results = ActiveRecord::Base.connection.execute(latin_tables_sql)
    say "Total: #{results.count}"

    results.each do |result|
      alter_table_sql = "ALTER TABLE #{result[0]} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
      execute alter_table_sql
    end

    execute("SET foreign_key_checks = 1")
  end
end