require 'tiny_tds'
require 'csv'

## Connect to database
client = TinyTds::Client.new username: '<db-user>', password: '<db-pass>', host: '<host or IP>', port: 1433, database: '<db-name>'
if client.active?
  puts "Connection established"
end

## Open and readCSV file

logs = CSV.read('test.csv')
record = 1
index = 0

## Store basic information and loop through each record

logs[record].each do |value|
    host = logs[record][0]
    domain = logs[record][1]
    type = logs[record][2]
    server_type = logs[record][3]

    ## Insert Host information to database and test by verifying the generated index number for the entry
    results = client.execute("INSERT dbo.Host_Name (Host_Name)
    OUTPUT INSERTED.Host_Name_Index VALUES ('#{host}' )")
    results.each(:as => :array) do |row|
      puts row
      index = row.pop ## removes the array and grabs the device index of the current host record and stores it for use in Asset table entry
    end

    results = client.execute("INSERT dbo.Domain (FQ_Domain_Name)
    OUTPUT INSERTED.Domain_Index VALUES ('#{domain}' )")
    results.each do |row|
      puts row
    end

    ## Check device type and device index value to insert into Assets table
    if type == 'server'
      results = client.execute("INSERT dbo.Assets (Host_Name_Index, Type_Index)
        OUTPUT INSERTED.Device_Index VALUES (#{index.to_i},1)")
      results.each do |row|
        puts row
      end
    elsif type == 'desktop'
      results = client.execute("INSERT dbo.Assets (Host_Name_Index, Type_Index)
        OUTPUT INSERTED.Device_Index VALUES (#{index.to_i},2)")
      results.each do |row|
        puts row
      end
    else type == 'laptop'
      results = client.execute("INSERT dbo.Assets (Host_Name_Index, Type_Index)
        OUTPUT INSERTED.Device_Index VALUES (#{index.to_i},3)")
      results.each do |row|
        puts row
      end
    end

    ## increase record count to move to next record entry
    record += 1
end

print 'Done processing csv file'