class CreateWorkers < Debci::DB::LEGACY_MIGRATION
  def up
    create_table(:workers) do |t|
      t.string :name, limit: 128, unique: true
    end
    add_index :workers, :name

    add_column :jobs, :worker_id, :integer, null: true
    add_foreign_key :jobs, :workers, column: :worker_id

    execute "INSERT INTO workers(name) SELECT distinct(worker) FROM jobs"
    execute "UPDATE jobs SET worker_id = (SELECT workers.id FROM workers WHERE jobs.worker = workers.name)"
    remove_column :jobs, :worker
  end

  def down
    add_column :jobs, :worker, :string
    execute "UPDATE jobs SET worker = (SELECT workers.name FROM workers WHERE jobs.worker_id = workers.id)"
    remove_foreign_key :jobs, :workers
    remove_column :jobs, :worker_id
    drop_table :workers
  end
end