namespace :db do

desc "Rebuild (drop, create, migrate) db. Then restore (or seed) and clone the test db.  Will restore db/rebuild.sql by default.  Use `rake db:rebuild[filename]` to restore db/filename.sql. Use `rake db:rebuild[-seed]` to instead load seed data."
task :rebuild, [:filename] do |t, args|
  raise "Nope...Ain't gonna do it.  Your request to drop the PRODUCTION database is denied." if Rails.env.production?
  db = Rails.configuration.database_configuration[Rails.env]
  puts "DB=#{db["database"]}"
  # if something fails and you are unable to access the db, you can drop it with the following:
  # /usr/bin/dropdb 'store-dev'
  # /usr/bin/dropdb 'store-test'
  # modified from http://stackoverflow.com/a/5408501/444774

  puts "Forcibly disconnecting other processes from database...(You may need to restart them.)"

  require 'active_record/connection_adapters/postgresql_adapter'
  module ActiveRecord
    module ConnectionAdapters
      class PostgreSQLAdapter < AbstractAdapter
        def drop_database(name)
          raise "Nope...Ain't gonna do it.  Your request to drop the PRODUCTION database is denied." if Rails.env.production?
          begin
            psql_version_num = execute "select setting from pg_settings where name = 'server_version_num'"
            if psql_version_num.values.first.first.to_i < 90200
              #puts "version < 9.2"
              psql_version_pid_name = 'procpid'
            else
              #puts "version >= 9.2"
              psql_version_pid_name = 'pid'
            end

            execute <<-SQL
              UPDATE pg_catalog.pg_database
              SET datallowconn=false WHERE datname='#{name}'
            SQL

            execute <<-SQL
              SELECT pg_terminate_backend(pg_stat_activity.#{psql_version_pid_name})
              FROM pg_stat_activity
              WHERE pg_stat_activity.datname = '#{name}';
            SQL

            execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
          ensure
            execute <<-SQL
              UPDATE pg_catalog.pg_database
              SET datallowconn=true WHERE datname='#{name}'
            SQL
          end
        end
      end
    end
  end
  puts "Dropping the db..."
  Rake::Task['db:drop'].invoke
  puts "Creating the db..."
  Rake::Task['db:create'].invoke

  if args.filename == "-seed"
    puts "Migrating the db..."
    Rake::Task['db:migrate'].invoke
    puts "Seeding the db..."
    Rake::Task['db:seed'].invoke
  else
    args.with_defaults(:filename => "rebuild")
    puts "Restoring dump: #{args.filename}..."
    Rake::Task['db:restore'].invoke(args.filename)
  end

  if Rails.env.development?
    puts "Cloning the test db..."
    Rake::Task['db:test:prepare'].invoke
  end
  puts "Done with DB=#{db["database"]}"

end

desc "Reload schema, then seed. (Does not try to drop and recreate db.)"
task :reload, [:filename] => :environment do |t, args|
  raise "Nope...Ain't gonna do it.  Your request to drop the PRODUCTION database is denied." if Rails.env.production?
  if args.filename == "seed!"
    puts "Reloading the db..."
    Rake::Task['db:schema:load'].invoke
    puts "Seeding the db..."
    Rake::Task['db:seed'].invoke
  else
    args.with_defaults(:filename => "rebuild")
    puts "Restoring dump: #{args.filename}..."
    Rake::Task['db:restore'].invoke(args.filename)
  end
  puts "Done."
end

desc 'dump database (without schema_migrations) to *.sql using pg_dump. Ex: `rake db:backup[filename]` (filename is optional, default = "rebuild").  Use `rake db:backup[rebuild]` to use dump as default for `rake db:rebuild`.'
task :backup_data_only, :filename do |t, args|
  db = Rails.configuration.database_configuration[Rails.env]
  #cmd = "pg_dump -Fc --no-acl --no-owner -h #{db["host"]} -p #{db["port"]} -U #{db["username"]} #{db["database"]} > #{Rails.root}/db/#{args[:filename]}.dump"
  cmd = "pg_dump -Fp --column-inserts --no-acl --no-owner --data-only -h #{db["host"]} -p #{db["port"]}"
  cmd += " -U #{db["username"]}" unless db["username"].blank?
  #cmd += " --exclude-table=schema_migrations"
  cmd += " --exclude-table=cacheinators -T cacheinators_id_seq"
  cmd += " #{db["database"]} > #{path_with_default(args[:filename])}"
  puts cmd
  unless system({"PGPASSWORD" => db["password"]}, cmd)
    puts "ERROR:"
    puts $?.inspect
  end
end

desc 'dump database (with schema) to *.sql using pg_dump. Ex: `rake db:backup[filename]` (filename is optional, default = "rebuild").  Use `rake db:backup[rebuild]` to use dump as default for `rake db:rebuild`.'
task :backup, :filename do |t, args|
  db = Rails.configuration.database_configuration[Rails.env]
  #cmd = "pg_dump -Fp --column-inserts --no-acl --no-owner --clean -h #{db["host"]} -p #{db["port"]}"
  cmd = "pg_dump -Fp --column-inserts --no-acl --no-owner -h #{db["host"]} -p #{db["port"]}"
  cmd += " -U #{db["username"]}" unless db["username"].blank?
  #cmd += " --exclude-table=cacheinators -T cacheinators_id_seq"
  cmd += " #{db["database"]} > #{path_with_default(args[:filename])}"
  puts cmd
  if system({"PGPASSWORD" => db["password"]}, cmd)
    `sed -i -e 's/SET lock_timeout = 0;/--SET lock_timeout = 0;/g' db/rebuild.sql` # fix sql from 9.3 that is not compatible with 9.1
  else
    puts "ERROR:"
    puts $?.inspect
  end
end

#task :restore_zzz, :filename do |t, args|
#  desc 'restore database from sql file. Ex: `rake db:backup[filename]` (filename is optional, default = "dump").'
#  args.with_defaults(:filename => 'dump')
#  db = Rails.configuration.database_configuration[Rails.env]
#  ap db
#  #cmd = "pg_restore --verbose --clean --no-acl --no-owner -h #{db["host"]} -p #{db["port"]} -U #{db["username"]} -d #{db["database"]} #{Rails.root}/db/#{args[:filename]}.dump"
#  cmd = "psql -h #{db["host"]} -p #{db["port"]} -U #{db["username"]} -d #{db["database"]} -f #{Rails.root}/db/#{args[:filename]}.sql"
#  puts cmd
#  raise "Does not compute...destroying the production database is illogical.  If you are *REALLY* sure, use the command above." if Rails.env.production?
#  if system({"PGPASSWORD" => db["password"]}, cmd)
#    puts
#    puts "*"*80
#    puts "Don't forget to restart the Rails server!"
#    puts "*"*80
#    puts
#  else
#    fail "ERROR:  "+$?.inspect
#  end
#end

desc 'restore database from sql file. Ex: `rake db:backup[filename]` (filename is optional, default = "rebuild").'
task :restore, [:filename] => :environment do |t, args|
  raise "Does not compute...destroying the production database is illogical." if Rails.env.production? && !destroy_prod?
  begin
    #ActiveRecord::Base.connection.execute("DELETE FROM schema_migrations;\n#{File.open(path_with_default(args[:filename])).read}")
    ActiveRecord::Base.connection.execute("#{filter_sql(File.open(path_with_default(args[:filename])).read)}")
  rescue ActiveRecord::StatementInvalid => e
    puts "\n\n\n** DATABASE ERROR *************************************************************"
    puts e.message.truncate(1000)
    raise "*"*80+"\nDB Reload Error :(\n"+"*"*80
  end
end

desc 'shows the current database configuration'
task :info => :environment do |t, args|
  puts Rails.configuration.database_configuration[Rails.env].inspect
end

def path_with_default(basename)
  basename = 'rebuild' if basename.blank?
  basename = File.basename(basename, '.*') # remove everything except basename
  if basename == 'rebuild' && File.exist?(basename_to_path("#{basename}-#{Rails.env}"))
    return basename_to_path("#{basename}-#{Rails.env}")
  end
  basename_to_path(basename)
end

def basename_to_path(basename)
  "#{Rails.root}/db/#{basename}.sql"
end

def destroy_prod?
  puts "Are you *REALLY* sure you want to DESTROY the PRODUCTION database?"
  puts "Type 'destroy production' if you are."
  STDOUT.flush
  input = STDIN.gets.chomp
  if input.downcase == "destroy production"
    puts "ok, going through with the it..."
    return true
  else
    raise "Nope...Ain't gonna do it.  Your request to drop the PRODUCTION database is denied."
  end
end

def filter_sql(sql)
  raise "Nope...Ain't gonna do it.  Your request to drop the PRODUCTION database is denied." if Rails.env.production?
  sql = "DROP SCHEMA IF EXISTS public CASCADE;\nCREATE SCHEMA public;\n" + sql
  sql.gsub!("CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;", '')
  sql.gsub!("COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';", '')
  #sql.gsub!(/(DROP) (INDEX|SEQUENCE|TABLE|SCHEMA|EXTENSION|CONSTRAINT) (.*);$/,'\1 \2 IF EXISTS \3;')
  #sql.gsub(/(DROP|ALTER) (INDEX|SEQUENCE|TABLE|SCHEMA|EXTENSION|CONSTRAINT) (.*);$/,'\1 \2 IF EXISTS \3;')
  sql
end

end

begin

DROP INDEX public.unique_schema_migrations; ALTER TABLE ONLY public.vendors DROP CONSTRAINT vendors_pkey; ALTER TABLE public.vendors ALTER COLUMN id DROP DEFAULT; DROP SEQUENCE public.adjusters_id_seq; DROP TABLE public.adjusters; DROP EXTENSION plpgsql; DROP SCHEMA public;