migration

class CreateUsersPropositions < ActiveRecord::Migration
  NOW_QUERY = "CAST(NOW() at time zone 'utc' AS timestamp)"

  def up
    create_table :widget_to_do_users_propositions do |t|
      t.integer :user_id
      t.integer :proposition_id
      t.timestamps
    end

    add_index :widget_to_do_users_propositions, :user_id
    add_index :widget_to_do_users_propositions, :proposition_id,
              name: "index_widget_to_do_users_propositions_on_proposition_id"
    add_index :widget_to_do_users_propositions, [:user_id, :proposition_id],
              name: "index_widget_to_do_users_propositions_on_user_and_proposition"

    propositions = select_all <<-SQL
      SELECT id, user_id FROM widget_to_do_propositions WHERE user_id IS NOT NULL
    SQL

    propositions.each do |proposition|
      insert <<-SQL
        INSERT INTO widget_to_do_users_propositions(user_id, proposition_id, created_at, updated_at)
        VALUES('#{proposition['user_id']}', '#{proposition['id']}', #{NOW_QUERY}, #{NOW_QUERY})
      SQL
    end

    remove_column :widget_to_do_propositions, :user_id, :integer
  end

  def down
    add_column :widget_to_do_propositions, :user_id, :integer

    users_propositions = select_all <<-SQL
      SELECT user_id, proposition_id FROM widget_to_do_users_propositions
    SQL

    users_propositions.each do |user_proposition|
      update <<-SQL
        UPDATE widget_to_do_propositions
          SET user_id = #{user_proposition['user_id']}
          WHERE id = #{user_proposition['proposition_id']}
      SQL
    end

    drop_table :widget_to_do_users_propositions
  end
end