module Sequel::MSSQL::DatabaseMethods

def alter_table_sql(table, op)
  case op[:op]
  when :add_column
    "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}"
  when :rename_column
    "sp_rename #{literal("#{quote_schema_table(table)}.#{quote_identifier(op[:name])}")}, #{literal(op[:new_name].to_s)}, 'COLUMN'"
  when :set_column_type
    sqls = []
    if sch = schema(table)
      if cs = sch.each{|k, v| break v if k == op[:name]; nil}
        cs = cs.dup
        if constraint = default_constraint_name(table, op[:name])
          sqls << "ALTER TABLE #{quote_schema_table(table)} DROP CONSTRAINT #{constraint}"
        end
        cs[:default] = cs[:ruby_default]
        op = cs.merge!(op)
        default = op.delete(:default)
      end
    end
    sqls << "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{column_definition_sql(op)}"
    sqls << alter_table_sql(table, op.merge(:op=>:set_column_default, :default=>default)) if default
    sqls
  when :set_column_null
    sch = schema(table).find{|k,v| k.to_s == op[:name].to_s}.last
    type = sch[:db_type]
    if [:string, :decimal].include?(sch[:type]) and size = (sch[:max_chars] || sch[:column_size])
      type += "(#{size}#{", #{sch[:scale]}" if sch[:scale] && sch[:scale].to_i > 0})"
    end
    "ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(op[:name])} #{type_literal(:type=>type)} #{'NOT ' unless op[:null]}NULL"
  when :set_column_default
    "ALTER TABLE #{quote_schema_table(table)} ADD CONSTRAINT #{quote_identifier("sequel_#{table}_#{op[:name]}_def")} DEFAULT #{literal(op[:default])} FOR #{quote_identifier(op[:name])}"
  else
    super(table, op)
  end
end

def auto_increment_sql
  AUTO_INCREMENT
end

def begin_savepoint_sql(depth)
  SQL_SAVEPOINT % depth
end

def begin_transaction_sql
  SQL_BEGIN
end

def commit_transaction(conn, opts={})
  log_connection_execute(conn, commit_transaction_sql) unless @transactions[conn][:savepoint_level] > 1
end

def commit_transaction_sql
  SQL_COMMIT
end

def create_table_sql(name, generator, options)
  "CREATE TABLE #{quote_schema_table(options[:temp] ? "##{name}" : name)} (#{column_list_sql(generator)})"
end

def database_type
  :mssql
end

def default_constraint_name(table, column)
  from(:sysobjects___c_obj).
    join(:syscomments___com, :id=>:id).
    join(:sysobjects___t_obj, :id=>:c_obj__parent_obj).
    join(:sysconstraints___con, :constid=>:c_obj__id).
    join(:syscolumns___col, :id=>:t_obj__id, :colid=>:colid).
    where{{c_obj__uid=>user_id{}}}.
    where(:c_obj__xtype=>'D', :t_obj__name=>table.to_s, :col__name=>column.to_s).
    get(:c_obj__name)
end

def drop_index_sql(table, op)
  "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))} ON #{quote_schema_table(table)}"
end

def index_definition_sql(table_name, index)
  index_name = index[:name] || default_index_name(table_name, index[:columns])
  raise Error, "Partial indexes are not supported for this database" if index[:where]
  if index[:type] == :full_text
    "CREATE FULLTEXT INDEX ON #{quote_schema_table(table_name)} #{literal(index[:columns])} KEY INDEX #{literal(index[:key_index])}"
  else
    "CREATE #{'UNIQUE ' if index[:unique]}#{'CLUSTERED ' if index[:type] == :clustered}INDEX #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}#{" INCLUDE #{literal(index[:include])}" if index[:include]}"
  end
end

def indexes(table, opts={})
  m = output_identifier_meth
  im = input_identifier_meth
  indexes = {}
  metadata_dataset.from(:sys__tables___t).
   join(:sys__indexes___i, :object_id=>:object_id).
   join(:sys__index_columns___ic, :object_id=>:object_id, :index_id=>:index_id).
   join(:sys__columns___c, :object_id=>:object_id, :column_id=>:column_id).
   select(:i__name, :i__is_unique, :c__name___column).
   where{{t__name=>im.call(table)}}.
   where(:i__is_primary_key=>0, :i__is_disabled=>0).
   order(:i__name, :ic__index_column_id).
   each do |r|
    index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)}
    index[:columns] << m.call(r[:column])
  end
  indexes
end

def information_schema_tables(type, opts)
  m = output_identifier_meth
  metadata_dataset.from(:information_schema__tables___t).
    select(:table_name).
    filter(:table_type=>type, :table_schema=>(opts[:schema]||default_schema||'dbo').to_s).
    map{|x| m.call(x[:table_name])}
end

def metadata_dataset
  ds = super
  ds.quote_identifiers = true
  ds
end

def rename_table_sql(name, new_name)
  "sp_rename #{literal(quote_schema_table(name))}, #{quote_identifier(schema_and_table(new_name).pop)}"
end

def rollback_savepoint_sql(depth)
  SQL_ROLLBACK_TO_SAVEPOINT % depth
end

def rollback_transaction_sql
  SQL_ROLLBACK
end

def schema_column_type(db_type)
  case db_type
  when /\A(bit)\z/io
    :boolean
  else
    super
  end
end

def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])
  m2 = input_identifier_meth(opts[:dataset])
  tn = m2.call(table_name.to_s)
  table_id = get{object_id(tn)}
  pk_index_id = metadata_dataset.from(:sysindexes).
    where(:id=>table_id, :indid=>1..254){{(status & 2048)=>2048}}.
    get(:indid)
  pk_cols = metadata_dataset.from(:sysindexkeys___sik).
    join(:syscolumns___sc, :id=>:id, :colid=>:colid).
    where(:sik__id=>table_id, :sik__indid=>pk_index_id).
    select_order_map(:sc__name)
  ds = metadata_dataset.from(:information_schema__tables___t).
   join(:information_schema__columns___c, :table_catalog=>:table_catalog,
        :table_schema => :table_schema, :table_name => :table_name).
   select(:column_name___column, :data_type___db_type, :character_maximum_length___max_chars, :column_default___default, :is_nullable___allow_null, :numeric_precision___column_size, :numeric_scale___scale).
   filter(:c__table_name=>tn)
  if schema = opts[:schema] || default_schema
    ds.filter!(:c__table_schema=>schema)
  end
  ds.map do |row|
    row[:primary_key] = pk_cols.include?(row[:column])
    row[:allow_null] = row[:allow_null] == 'YES' ? true : false
    row[:default] = nil if blank_object?(row[:default])
    row[:type] = if row[:db_type] =~ DECIMAL_TYPE_RE && row[:scale] == 0
      :integer
    else
      schema_column_type(row[:db_type])
    end
    [m.call(row.delete(:column)), row]
  end
end

def server_version(server=nil)
  return @server_version if @server_version
  @server_version = synchronize(server) do |conn|
    (conn.server_version rescue nil) if conn.respond_to?(:server_version)
  end
  unless @server_version
    m = SERVER_VERSION_RE.match(fetch(SERVER_VERSION_SQL).single_value.to_s)
    @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i
  end
  @server_version
end

def set_mssql_unicode_strings
  @mssql_unicode_strings = typecast_value_boolean(@opts.fetch(:mssql_unicode_strings, true))
end

def supports_savepoints?
  true
end

def supports_transaction_isolation_levels?
  true
end

def tables(opts={})
  information_schema_tables('BASE TABLE', opts)
end

def type_literal_generic_datetime(column)
  :datetime
end

def type_literal_generic_file(column)
  :'varbinary(max)'
end

def type_literal_generic_time(column)
  column[:only_time] ? :time : :datetime
end

def type_literal_generic_trueclass(column)
  :bit
end

def views(opts={})
  information_schema_tables('VIEW', opts)
end