class DropPatchVersionVersionField < ActiveRecord::Migration
  def self.up
    execute "DROP VIEW patch_view"
    execute <<EOS
    CREATE VIEW patch_view AS SELECT patches.*, programs.name AS program_name, pv.id AS patch_version_id, pv.for_upstream_version, pv.comment FROM patch_versions pv JOIN (patches JOIN programs ON patches.program_id = programs.id) ON pv.patch_id = patches.id WHERE pv.id = (SELECT id FROM patch_versions WHERE patch_id = pv.patch_id ORDER BY pv.created_at DESC LIMIT 1);
EOS
    remove_column :patch_versions, :version
  end

  def self.down
    add_column :patch_versions, :version, :integer
    execute "DROP VIEW patch_view"
    execute <<EOS
    CREATE VIEW patch_view AS SELECT patches.*, programs.name AS program_name, pv.id AS patch_version_id, pv.for_upstream_version, pv.version, pv.comment FROM patch_versions pv JOIN (patches JOIN programs ON patches.program_id = programs.id) ON pv.patch_id = patches.id WHERE pv.id = (SELECT id FROM patch_versions WHERE patch_id = pv.patch_id ORDER BY version DESC LIMIT 1);
EOS
    Patch.find(:all).each do |p|
      p.patch_versions.find(:all, :order => "created_at").each_with_index do |pv,i|
        pv.version = i+1
        pv.save
      end
    end
  end
end