class MergeCatalystDatabase < ActiveRecord::Migration
  def self.up
    ### Most tables can be copied directly.  However, "forms", "inclusions", and "steps" are duplicated between
    ### the applications and are going to have to be merged in a more defined manner.

    # Temporary credentials
    mysql_user = "hi_pastie"
    mysql_password = "hi_pastie"
    ActiveRecord::Base.establish_connection(:catalyst_migration)
    catalyst_db = ActiveRecord::Base.connection.current_database
    ActiveRecord::Base.establish_connection(:chemistry_migration)
    chemistry_db = ActiveRecord::Base.connection.current_database

    tables_to_copy = "articles footers lists partner_pixels partnerships receipts sites source_filters"
    import_command = "mysql -u #{mysql_user} -p#{mysql_password} #{chemistry_db}"

    ### Merge step tables
    # Rename steps to chemistry_steps
    rename_table :steps, :chemistry_steps

    # Copy the catalyst form table over under a different name

    # Split up the commands for readability
    dump_command = "mysqldump -u #{mysql_user} -p#{mysql_password} #{catalyst_db} steps"

    say_with_time "create steps table from catalyst steps" do
      `#{dump_command} | #{import_command}`
    end

    add_column :steps, :order_num, :integer

    Step.reset_column_information

    ### Merge form tables
    execute "create table forms_backup select * from forms"

    add_column :forms, :site_id, :integer
    add_column :forms, :title, :string
    add_column :forms, :header, :string
    add_column :forms, :subheader, :string
    add_column :forms, :description, :text
    add_column :forms, :form_type, :string
    add_column :forms, :navbar, :text
    add_column :forms, :api_code, :string

    Form.reset_column_information

    # Copy the catalyst form table over under a different name
    rename_table :forms, :chemistry_forms
    dump_command = "mysqldump -u #{mysql_user} -p#{mysql_password} #{catalyst_db} forms"

    say_with_time "copy forms table from catalyst" do
      `#{dump_command} | #{import_command}`
    end

    rename_table :forms, :catalyst_forms
    rename_table :chemistry_forms, :forms

    catalyst_form_fields = ['site_id', 'title', 'header', 'subheader', 'description', 'form_type', 'navbar', 'api_code']
    form_update_sql_fragment = catalyst_form_fields.map { |f| "forms.#{f} = catalyst_forms.#{f}" }.join(', ')

    update "update forms join catalyst_forms on forms.code = catalyst_forms.api_code set #{form_update_sql_fragment}"

    # Split up the commands for readability
    dump_command = "mysqldump -u #{mysql_user} -p#{mysql_password} #{catalyst_db} #{tables_to_copy}"

    say_with_time "direct copy tables from the catalyst db into the chemistry db" do
      `#{dump_command} | #{import_command}`
    end

    # All items associated with Catalyst Forms need to have their foreign key IDs replaced with the new Chemistry Form.
    # Lists
    update "update lists join catalyst_forms on catalyst_forms.id = lists.form_id join forms on forms.code = catalyst_forms.api_code set lists.form_id = forms.id"
    # Steps
    update "update steps join catalyst_forms on catalyst_forms.id = steps.form_id join forms on forms.code = catalyst_forms.api_code set steps.form_id = forms.id"

    say_with_time "update step order_nums" do
      forms = Form.find(:all)
      forms.each do |form|
        form.steps.each do |step|
          step.order_num =
            case form.form_type
            when 'lead_gen'
              case step.name
              when 'index'
                0
              when 'step2'
                1
              when 'step3'
                2
              when 'step4'
                nil
              when 'step4success'
                nil
              end
            when 'scholarships'
              case step.name
              when 'index'
                0
              when 'step2'
                1
              when 'step2b'
                2
              when 'step2success'
                nil
              when 'step3'
                nil
              when 'step4'
                nil
              end
            else
              if step.name == 'index'
                0
              else
                nil
              end
            end
          step.save!
        end
      end
    end

    # All items associated with Chemistry Steps need to have their foreign key IDs replaced with the new Catalyst Step.

    execute "create table sections_backup select * from sections"

    update "update sections
            join chemistry_steps on chemistry_steps.id = sections.step_id
            join steps on steps.form_id = chemistry_steps.form_id and steps.order_num = chemistry_steps.order_num
            set sections.step_id = steps.id"
    #update "update form_attempt_steps
    #        join chemistry_steps on chemistry_steps.id = form_attempt_steps.step_id
    #        join steps on steps.form_id = chemistry_steps.form_id
    #        set form_attempt_steps.step_id = steps.id"

    drop_table :catalyst_forms

    ### Rename Inclusions
    rename_table :inclusions, :question_inclusions

    # Copy the catalyst inclusions table over under a different name
    dump_command = "mysqldump -u #{mysql_user} -p#{mysql_password} #{catalyst_db} inclusions"

    say_with_time "create inclusions table from catalyst" do
      `#{dump_command} | #{import_command}`
    end

    rename_table :inclusions, :article_inclusions
  end

  def self.down
    drop_table :forms
    rename_table :forms_backup, :forms
    drop_table :steps
    rename_table :chemistry_steps, :steps
    rename_table :question_inclusions, :inclusions
    drop_table :sections
    rename_table :sections_backup, :sections
    drop_table :article_inclusions
    drop_table :lists
    drop_table :articles
    drop_table :footers
    drop_table :partner_pixels
    drop_table :partnerships
    drop_table :receipts
    drop_table :sites
    drop_table :source_filters
  end
end