module Flydata
module TableDef

class RedshiftTableDef
  TYPE_MAP_F2R = {
    'binary' => {type: 'varchar', use_params: true},
    'char' => {type: 'char', use_params: true},
    'date' => {type: 'date'},
    'datetime' => {type: 'timestamp'},
    'enum' => {type: 'varchar encode bytedict'},
    'float4' => {type: 'float4'},
    'float4 unsigned' => {type: 'float4'},
    'float8' => {type: 'float8'},
    'float8 unsigned' => {type: 'float8'},
    'int1' => {type: 'int2'},
    'int1 unsigned' => {type: 'int2', unsigned: true},
    'int2' => {type: 'int2'},
    'int2 unsigned' => {type: 'int4', unsigned: true},
    'int3' => {type: 'int4'},
    'int3 unsigned' => {type: 'int4', unsigned: true},
    'int4' => {type: 'int4'},
    'int4 unsigned' => {type: 'int8', unsigned: true},
    'int8' => {type: 'int8'},
    'int8 unsigned' => {type: 'numeric(20,0)', unsigned: true},
    'numeric' => {type: 'numeric', use_params: true, max_size: [38,37]},
    'numeric unsigned' => {type: 'numeric', use_params: true, max_size: [38,37]},
    'text' => {type: 'varchar(max)'},
    'time' => {type: 'timestamp'},
    'varbinary' => {type: 'varchar', use_params: true, max_size: 65535},
    'varchar' => {type: 'varchar', use_params: true, max_size: 65535},
  }
  def self.from_flydata_tabledef(flydata_tabledef, options = {})
    options[:flydata_ctl_table] = true unless options.has_key?(:flydata_ctl_table)
    schema_name = options[:schema_name]

    tabledef = ""
    tabledef += create_flydata_ctl_table_sql(schema_name) if options[:flydata_ctl_table]
    tabledef += create_table_sql(flydata_tabledef, schema_name)
    tabledef += comment_sql(flydata_tabledef, schema_name)
    tabledef += flydata_ctl_sql(flydata_tabledef, schema_name)
  end

  FLYDATA_CTL_COLUMNS_TABLE = "flydata_ctl_columns"
  CREATE_FLYDATA_CTL_TABLE_SQL = <<EOS
DROP TABLE %s;
CREATE TABLE %s(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
EOS

  def self.table_name_for_ddl(table_name, schema_name)
    schema_name.to_s.empty? ? "\"#{table_name}\"" : "\"#{schema_name}\".\"#{table_name}\""
  end

  def self.flydata_ctl_table_for_ddl(schema_name)
    table_name_for_ddl(FLYDATA_CTL_COLUMNS_TABLE, schema_name)
  end

  def self.create_flydata_ctl_table_sql(schema_name)
    # No drop table here intentionally because losing the data is fatal.
    tbl = flydata_ctl_table_for_ddl(schema_name)
    CREATE_FLYDATA_CTL_TABLE_SQL % [tbl, tbl]
  end

  CREATE_TABLE_SQL = <<EOS
DROP TABLE %s;
CREATE TABLE %s (
%s
);
EOS

  def self.create_table_sql(flydata_tabledef, schema_name)
    lines = flydata_tabledef[:columns].collect{|column| column_def_sql(column) }
    pk_def = primary_key_sql(flydata_tabledef)
    lines << pk_def if pk_def

    contents = lines.join(",\n")

    table_name = flydata_tabledef[:table_name]
    redshift_tbl = table_name_for_ddl(table_name, schema_name)
    CREATE_TABLE_SQL % [redshift_tbl, redshift_tbl, contents]
  end

  def self.column_def_sql(column)
    type = column[:type]
    if type =~ /\((.*?)\)/
      type = $` + $'
      params = $1
    end

    type_info = TYPE_MAP_F2R[type]
    raise "Unsupported type '#{column[:type]}'" if type_info.nil?

    rs_type = if type_info[:use_params] && params && !params.nil?
                params = check_and_replace_max(params, Array(type_info[:max_size])) if type_info[:max_size]
                type_info[:type] + "(#{params})"
              else
                type_info[:type]
              end
    line = %Q|  "#{column[:name]}" #{rs_type}|
    line += " NOT NULL" if column[:not_null]
    if (column.has_key?(:default))
      val = replace_default_value(type_info[:type], column[:default])
      line += " DEFAULT #{val}"
    end
# Commented out because no IDENTITY column must be used for a replicated table.
# Values come from the master.
#    line += " IDENTITY(1, 1)" if (column[:auto_increment])

    line
  end

  def self.replace_default_value(type, default_value)
    val = default_value ? "'#{default_value}'" : "NULL"
    case type
    when 'timestamp'
      (val.upcase == "'CURRENT_TIMESTAMP'") ? 'SYSDATE' : val
    else
      val
    end
  end

  def self.primary_key_sql(flydata_tabledef)
    pks = flydata_tabledef[:columns].select{|col| col[:primary_key]}.collect{|col| col[:name]}
    pks.empty? ? nil : "  PRIMARY KEY (#{pks.join(',')})"
  end

  def self.comment_sql(flydata_tabledef, schema_name)
    sql = ""
    flydata_tabledef[:columns].each do |col|
      next unless col[:comment]

      sql += <<EOS
COMMENT ON COLUMN #{table_name_for_ddl(flydata_tabledef[:table_name], schema_name)}."#{col[:name]}"
  IS '#{col[:comment]}';
EOS
    end
    sql
  end

  FLYDATA_CTL_COLUMNS_SQL = <<EOS
DELETE FROM %s WHERE table_name = '%s';
INSERT INTO %s (table_name, column_name, src_data_type, ordinal_position) VALUES
EOS
  def self.flydata_ctl_sql(flydata_tabledef, schema_name)
    flydata_ctl_tbl = flydata_ctl_table_for_ddl(schema_name)
    sql = FLYDATA_CTL_COLUMNS_SQL % [ flydata_ctl_tbl, flydata_tabledef[:table_name], flydata_ctl_tbl ]
    values = []
    flydata_tabledef[:columns].each.with_index(1) do |col, i|
      values << "('#{flydata_tabledef[:table_name]}', '#{col[:name]}', '#{escape(col[:type])}', #{i})"
    end
    sql += values.join(",\n") + ';'
    sql
  end

  def self.escape(text)
    text.gsub("'", "\\\\'")
  end
  
  def self.check_and_replace_max(params, max_size_a)
      final_params = []
      params.split(",").each_with_index do |param, i|
        final_params << (/\d+/.match(param) && max_size_a[i] && param.to_i > max_size_a[i].to_i ? 
                            max_size_a[i] : param)
      end
      final_params.join(",")
  end
end

end
end