class PgConn::SchemaMethods

def clean!(schema, exclude: [])
  conn.session.triggers(false) {
    self.list_tables(schema, exclude: exclude).each { |table|
      conn.exec "delete from #{schema}.#{table}"
    }
  }
end

def column_exist_query(schema, relation, column)
  %(
    select  1
    from    pg_class c
    join    pg_attribute a on a.attrelid = c.oid
    where   c.relnamespace::regnamespace::text = '#{schema}'
    and     c.relname = '#{relation}'
    and     a.attname = '#{column}'
    and     a.attnum > 0
  )
end

def column_list_query(schema, relation)
  relation_clause = relation ? "relname = '#{relation}'" : nil
  [
      %(
        select  '#{schema}' || '.' || c.relname || '.' || a.attname
        from    pg_class c
        join    pg_attribute a on a.attrelid = c.oid
        where   relnamespace::regnamespace::text = '#{schema}'
        and     a.attnum > 0
      ),
      relation_clause
  ].compact.join(" and ")
end

def column_list_type_query(schema, relation)
  relation_clause = relation ? "relname = '#{relation}'" : nil
  [
      %(
        select  '#{schema}' || '.' || c.relname || '.' || a.attname as "column",
                a.atttypid::regtype::text as "type"
        from    pg_class c
        join    pg_attribute a on a.attrelid = c.oid
        where   relnamespace::regnamespace::text = '#{schema}'
        and     a.attnum > 0
      ),
      relation_clause
  ].compact.join(" and ")
end

def create(schema, authorization: nil)
  authorization_clause = authorization ? "authorization #{authorization}" : nil
  stmt = ["create schema", schema, authorization_clause].compact.join(" ")
  conn.exec stmt
end

def drop(schema, cascade: false)
  if cascade
    conn.exec "drop schema if exists #{schema} cascade"
  else
    conn.exec "drop schema if exists #{schema}"
  end
  true
end

def empty!(schema, exclude: [])
  self.list_tables(schema, exclude: exclude).each { |table|
    conn.exec "drop table if exists #{schema}.#{table} cascade"
  }
  self.list_views(schema, exclude: exclude).each { |view|
    conn.exec "drop view if exists #{schema}.#{view} cascade"
  }
end

def exist?(schema)
  conn.exist? %(
      select 1
      from information_schema.schemata
      where schema_name = '#{schema}'
  )
end

def exist_column?(schema, relation, column)
  conn.exist? column_exist_query(schema, relation, column)
end

def exist_function(schema, function, signature)
  raise NotImplementedError
end

def exist_relation?(schema, relation)
  conn.exist? relation_exist_query(schema, relation)
end

def exist_table?(schema, table)
  conn.exist? relation_exist_query(schema, table, kind: %w(r f))
end

def exist_view?(schema, view)
  conn.exist? relation_exist_query(schema, view, kind: %w(v m))
end

def get_serial(schema, table, next: false)
  uid = "#{schema}.#{table}"
  next_option = binding.local_variable_get(:next) # because 'next' is a keyword
  seq = sequence(schema, table) or raise ArgumentError, "Table #{uid} does not have a sequence"
  value = conn.value %(
    select
      case is_called
        when true then last_value
        else null
      end as "value"
    from
      #{seq}
  )
  if next_option
    value&.+(1) || 1
  else
    value
  end
end

def initialize(conn)
  @conn = conn
end

def list(all: false, exclude: [])
  conn.values(%(
      select schema_name
      from information_schema.schemata
  )).select { |schema|
    !exclude.include?(schema) && (all ? true : schema !~ /^pg_/ && schema != "information_schema")
  }
end

def list_column_types(schema, relation = nil)
  conn.tuples column_list_type_query(schema, relation)
end

def list_columns(schema, relation = nil)
  conn.values column_list_query(schema, relation)
end

def list_functions(schema, function = nil)
  raise NotImplementedError
end

def list_relations(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude)
end

def list_tables(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude, kind: %w(r f))
end

def list_views(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude, kind: %w(v m))
end

def relation_exist_query(schema, relation, kind: nil)
  kind_sql_list = "'" + (kind.nil? ? %w(r f v m) : Array(kind).flatten).join("', '") + "'"
  %(
      select  1
      from    pg_class
      where   relnamespace::regnamespace::text = '#{schema}'
      and     relname = '#{relation}'
      and     relkind in (#{kind_sql_list})
  )
end

def relation_list_query(schema, exclude: nil, kind: nil)
  kind_list = "'" + (kind.nil? ? %w(r f v m) : Array(kind).flatten).join("', '") + "'"
  kind_expr = "relkind in (#{kind_list})"
  exclude = Array(exclude || []).flatten
  exclude_list = "'#{exclude.flatten.join("', '")}'" if !exclude.empty?
  exclude_expr = exclude.empty? ? "true = true" : "not relname in (#{exclude_list})"
  %(
      select  relname
      from    pg_class
      where   relnamespace::regnamespace::text = '#{schema}'
      and     #{kind_expr}
      and     #{exclude_expr}
  )
end

def sequence(schema, table)
  conn.value "select pg_get_serial_sequence('#{schema}.#{table}', 'id')"
end

def set_serial(schema, table, value)
  uid = "#{schema}.#{table}"
  seq = sequence(schema, table) or raise ArgumentError, "Table #{uid} does not have a sequence"
  if value
    conn.exec "select setval('#{seq}', #{value})"
  else
    conn.exec "select setval('#{seq}', 1, false)"
  end
end