require 'tiny_tds'
require 'pg'
require 'dotenv/load'
require 'pry'

def types_map(mssql_type)
  map = {
    bigint: 'bigint',
    binary: 'bytea',
    bit: 'boolean',
    char: 'char',
    character: 'character',
    date: 'date',
    datetime: 'timestamp',
    datetime2: 'timestamp',
    datetimeoffset: 'timestamp with time zone',
    decimal: 'decimal',
    dec: 'dec',
    double: 'double',
    float: 'double precision',
    int: 'integer',
    money: 'money',
    nchar: 'char',
    ntext: 'text',
    numeric: 'numeric',
    nvarchar: 'varchar',
    real: 'real',
    rowversion: 'bytea',
    smalldatetime: 'timestamp',
    smallint: 'smallint',
    smallmoney: 'money',
    text: 'text',
    time: 'time',
    timestamp: 'bytea',
    tinyint: 'smallint',
    uniqueidentifier: 'char(16)',
    varbinary: 'bytea',
    varchar: 'text',
    xml: 'xml'
  }
  map[mssql_type.to_sym]
end

def mssql_client_params(city)
  {
    username: ENV['MS_SQL_USERNAME'],
    password: ENV['MS_SQL_PASSWORD'],
    host: ENV['MS_SQL_HOST'],
    timeout: 300,
    database: "taxi_#{city}"
  }
end

def pg_client_params(dbname)
  {
    dbname: dbname,
    host: ENV['POSTGRES_DB_HOST'] || 'localhost',
    port: ENV['POSTGRES_DB_PORT'] || '5432',
    user: ENV['POSTGRES_DB_USERNAME'] || 'postgres',
    password: ENV['POSTGRES_DB_PASSWORD'] || 'ferraru'
  }
end

pg_master_client = PG::Connection.new(pg_client_params('postgres'))

cities = ["voronezh", "tula", "kozelsk", "lyudinovo", "kondrovo", "aleksin", "abakan", "almaty", "angarsk", "astana", "astrakhan", "barnaul", "berezniki", "birobidzhan", "blagoveschensk", "bryansk", "cha", "cheboksary", "chelyabinsk", "cherepovets", "chita", "dimitrovgrad", "dzerzhinsk", "eburg", "gatchina", "habarovsk", "irkutsk", "ivanovo", "izhevsk", "kaliningrad", "kaluga", "karaganda", "kazan", "kemerovo", "kirov", "kostroma", "krasnodar", "krasnoyarsk", "kungur", "kursk", "kyzyl", "lipetsk", "lysva", "magnitogorsk", "maykop", "miass", "moscow", "murmansk", "nchelny", "ncherkassk", "nevi", "nmoskovsk", "nn", "novok", "novokuznetsk", "novorossiysk", "novosibirsk", "ntagil", "obninsk", "okt", "omsk", "orenburg", "orsk", "penza", "perm", "petrozavodsk", "praga", "pskov", "puralsk", "rnd", "ryazan", "salavat", "samara", "saransk", "saratov", "serpukhov", "sgorsk", "shakhty", "smolensk", "sochi", "spb", "stavropol", "sterl", "syk", "syzran", "taganrog", "tambov", "tolyatti", "tomsk", "tver", "tyumen", "ufa", "ulan-ude", "ulyanovsk", "vladimir", "vladivostok", "vnovgorod", "volgograd", "vologda", "votkinsk", "yaroslavl", "yola", "zlatoust"]

threads = []
cities.each do |city|
  threads << Thread.new(city) do |city|
    pg_master_client.exec("CREATE DATABASE #{city}")
    puts "DB #{city} created"
  end
end
threads.each { |thr| thr.join }

pg_master_client.finish

threads = []
cities.each do |city|
  threads << Thread.new(city) do |city|
    puts "Thread was created"

    pg_client = PG::Connection.new(pg_client_params(city))

    mssql_client = TinyTds::Client.new(mssql_client_params(city))

    begin
      tables = mssql_client.execute("SELECT name FROM sys.Tables").each(symbolize_keys: true)
    rescue Exception => e
      puts e
      puts "Error on #{city}"
      mssql_client = TinyTds::Client.new(mssql_client_params(city))
      tables = mssql_client.execute("SELECT name FROM sys.Tables").each(symbolize_keys: true)
    end

    tables.each do |table|
      get_columns_query = "
        SELECT c.name name, t.name type
        FROM sys.Columns c
        JOIN sys.Types t
        ON c.system_type_id = t.system_type_id
        WHERE object_id=OBJECT_ID('#{table[:name]}')
      "

      begin
        columns = mssql_client.execute(get_columns_query).each(symbolize_keys: true)
      rescue Exception => e
        puts e
        puts "Error on #{city} #{table[:name]}"
        mssql_client = TinyTds::Client.new(mssql_client_params(city))
        columns = mssql_client.execute(get_columns_query).each(symbolize_keys: true)
      end

      pg_table_attributes = ''
      columns.each do |column|
        pg_table_attributes += "#{column[:name]} #{types_map(column[:type])}," if column[:name].downcase != 'id' && column[:type] != 'sysname'
      end

      pg_client.exec(
        %Q(
          CREATE TABLE IF NOT EXISTS #{table[:name]} (
            id bigserial PRIMARY KEY,
            #{pg_table_attributes.chop}
         );
        )
      )

      begin
        rows = mssql_client.execute("SELECT * from #{table[:name]}").each(symbolize_keys: true)
        mssql_client.close
      rescue Exception => e
        puts e
        puts "Error on #{city} #{table[:name]}"
        mssql_client = TinyTds::Client.new(mssql_client_params(city))
        rows = mssql_client.execute("SELECT * from #{table[:name]}").each(symbolize_keys: true)
        mssql_client.close
      end

      rows.each do |row|
        row.delete(:ID)
        row.delete(:id)
        row.delete(:Id)

        values = row.values.map do |val|
          if val.nil?
            'null'
          elsif val.is_a?(Integer)
            val
          elsif val.is_a?(Float)
            val
          elsif val.is_a?(BigDecimal)
            val.to_f
          else
            "'#{val}'"
          end
        end

        values = values.join(',')
        keys = row.keys.join(',')

        data = pg_client.exec(%Q(INSERT INTO #{table[:name]} (#{keys}) VALUES (#{values}) returning *;))[0]
        puts "Data #{data} was saved to #{table[:name]}"
      end
    end

    pg_client.finish
  end
end

threads.each { |thr| thr.join }