class PgConn::RdbmsMethods

def copy(from_database, to_database, owner: ENV['USER'])
  create(to_database, owner: owner, template: from_database)
end

def create(database, owner: ENV['USER'], template: "template1")
  owner_clause = owner ? "owner = \"#{owner}\"" : nil
  template_clause = template ? "template = \"#{template}\"" : nil
  stmt = ["create database \"#{database}\"", owner_clause, template_clause].compact.join(" ")
  conn.execute stmt # Note we're using #execute instead of #exec because
                    # create database can't run within a transaction
end

def drop(database, force: false)
  conn.execute "drop database if exists \"#{database}\"#{force ? ' with (force)' : ''}"
  true
end

def empty!(database = nil, public: true, exclude: [])
  local = !database.nil?
  begin
    conn = local ? PgConn.new(database) : self.conn
    schemas =
        conn
          .values("select nspname from pg_namespace where nspowner != 10 or nspname = 'public'")
          .select { |schema| !exclude.include?(schema) }
          .join(", ")
    conn.exec "drop schema #{schemas} cascade"
    # FIXME FIXME FIXME SECURITY Why grant 'create' to public?
    conn.exec %(
        create schema public authorization postgres;
        grant usage, create on schema public to public
    ) if public
  ensure
    conn&.terminate if local
  end
end

def exist?(database)
  conn.exist? %(
      select 1
      from pg_database
      where datname = '#{database}'
  )
end

def initialize(conn)
  @conn = conn
  # TODO: Check if conn is a superuser connection
end

def list(all: false, exclude: [])
  exclude += POSTGRES_DATABASES if !all
  exclude_sql_list = "'" + exclude.join("', '") + "'"
  exclude_clause = exclude.empty? ? nil : "where datname not in (#{exclude_sql_list})"
  stmt = ["select datname from pg_database", exclude_clause].compact.join(" ")
  conn.values stmt
end

def load(database, file, role: ENV['USER'], gzip: nil)
  command_opt = role ? "-c \"set role #{role}\";\n" : nil
  if gzip
    pipe_cmd = file ? "gunzip --to-stdout #{file} |" : "gunzip --to-stdout |"
    file_opt = nil
  else
    pipe_cmd = nil
    file_opt = file ? "-f #{file}" : nil
  end
  cmd = [pipe_cmd, "psql -v ON_ERROR_STOP=1", command_opt, file_opt, database].compact.join(" ")
  stdout, stderr, status = Open3.capture3(cmd)
  status == 0 or raise PsqlError.new(stderr)
end

def owner(database)
  conn.value %(
    select r.rolname
    from (values ('#{database}')) as v (database)
    left join pg_database d on d.datname = v.database
    left join pg_roles r on r.oid = d.datdba
  )
end

def save(database, file, data: true, schema: true, gzip: nil)
  data_opt = data ? nil : "--schema-only"
  schema_opt = schema ? nil : "--data-only"
  if gzip
    pipe_cmd = file ? "| gzip >#{file}" : "| gzip"
    file_opt = nil
  else
    pipe_cmd = nil
    file_opt = file ? "-f #{file}" : nil
  end
  cmd = ["pg_dump --no-owner", data_opt, schema_opt, file_opt, database, pipe_cmd].compact.join(" ")
  stdout, stderr, status = Open3.capture3(cmd)
  status == 0 or raise PsqlError.new(stderr)
end

def users(database)
  database_clause = database ? "datname = '#{database}'" : nil
  query = ["select usename from pg_stat_activity", database_clause].compact.join(" where ")
  conn.values query
end