require 'prick/command.rb'
#require 'prick/ensure.rb'

require 'csv'

module Prick
  module Rdbms
#   extend Ensure

    ### EXECUTE SQL

    # Execute the SQL statement and return stdout as an array of tuples
    def self.exec_sql(db, sql, user: ENV['USER'])
      stdout = Command.command %(
        {
          echo "set role #{user};"
          echo "set search_path to public;"
          cat <<'EOF'
          #{sql}
EOF
        } | psql --csv --tuples-only --quiet -v ON_ERROR_STOP=1 -d #{db}
      )
      CSV.new(stdout.join("\n")).read
    end

    # Execute the given file and return stdout as an array of tuples
    def self.exec_file(db, file, user: ENV['USER'])
      self.exec_sql(db, File.read(file), user: user)
    end

    # Execute the SQL statement and return the result as an array of record tuples
    # Just an alias for ::exec_sql
    def self.select(db, sql, user: ENV['USER']) exec_sql(db, sql, user: user) end

    # Execute the SQL statement and return an array of values, one for each
    # single-valued record in the result. Raises an exception if the SQL
    # statement doesn't return records with exactly one field
    def self.select_values(db, sql, user: ENV['USER'])
      records = exec_sql(db, sql, user: user)
      return [] if records.empty?
      records.first.size == 1 or raise Prick::Fail, "Expected records with one field"
      records.flatten
    end

    # Execute the SQL statement and return a single record as an array of values.
    # Raises an exception if the SQL statement doesn't return exactly one
    # record
    def self.select_record(db, sql, user: ENV['USER'])
      records = exec_sql(db, sql, user: user)
      records.size == 1 or raise Prick::Fail, "Expected one row only"
      records.first
    end

    # Execute the SQL statement and return a value. The value is the first
    # field of the first record in the result. Raises an exception if the SQL
    # statement doesn't return exactly one record with one field
    def self.select_value(db, sql, user: ENV['USER'])
      row = select_record(db, sql, user: user)
      row.size == 1 or raise Prick::Fail, "Expected one field only"
      row.first
    end

    ### DETECT SCHEMAS, TABLES, AND RECORDS

    def self.exist_schema?(db, schema)
      exist_record?(db, %(
          select 1
          from information_schema.schemata
          where catalog_name = '#{db}'
          and schema_name = '#{schema}'
      ))
    end

    def self.exist_table?(db, schema, table)
      exist_record?(db, %(
          select 1
          from information_schema.tables
          where table_schema = '#{schema}'
          and table_name = '#{table}'
      ))
    end

    def self.exist_record?(db, sql)
      !select(db, sql).empty?
    end

    ### MAINTAIN USERS AND DATABASES

    def self.exist_user?(user)
      exist_record?("template1", "select 1 from pg_roles where rolname = '#{user}'")
    end

    def self.create_user(user)
      Command.command("createuser #{user}")
    end

    def self.drop_user(user, fail: true)
      Command.command "dropuser #{user}", fail: fail
    end

    def self.exist_database?(db)
      exist_record?("template1", "select 1 from pg_database where datname = '#{db}'")
    end

    # TODO: make `owner` an option
    def self.create_database(db, owner: ENV['USER'], template: "template1") 
      owner_option = (owner ? "-O #{owner}" : "")
      Command.command "createdb -T #{template} #{owner_option} #{db}" 
    end

    def self.copy_database(from, to, owner: ENV['USER'])
      create_database(to, owner: owner, template: from)
    end

    def self.drop_database(db, fail: true) 
      Command.command "dropdb #{db}", fail: fail
    end

    def self.list_databases(re = /.*/)
      select_values("template1", "select datname from pg_database").select { |db| db =~ re }
    end

    def self.load(db, file, user: ENV['USER'])
      Command.command %(
        {
            echo "set role #{user};"
            gunzip --to-stdout #{file}
        } | psql -v ON_ERROR_STOP=1 -d #{db}
      )
    end

    def self.save(db, file, data: true)
      data_opt = (data ? "" : "--schema-only")
      Command.command "pg_dump --no-owner #{data_opt} #{db} | gzip --to-stdout >#{file}"
    end

  private
    @ensure_states = {
      exist_user: [:create_user, :drop_user],
      exist_database: [
        lambda { |this, db, user| this.ensure_state(:exist_user, user) },
        lambda { |this, db, user| this.create_database(db, owner: user) },
        :drop_database
      ]
    }
  end
end