#!/usr/bin/python3

import sys, getopt, json, datetime

insertQuery = """insert into quote (
   company_id,name,symbol,last_price,turnover,quantity,internal_turnover,internal_quantity,open_price,bid_price,ask_price,day_low_price,day_high_price,close_price,change_percent,change_percent_1m,tick_size,sector_index,market_cap_category,marketvalue,date_time,close_date_time,created) 
values (
   {},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}
);"""

def formatForInsert(share, key):
   if key in share.keys():
      value = share[key]
      if type(value) == str:
         return "'" + value + "'"
      else:
         return value
   else:
      return 'null'   

def main(argv):
   inputfile = ''
   try:
      opts, args = getopt.getopt(argv,"hi:",["ifile="])
   except getopt.GetoptError:
      print('Error with parameters. Use -h to show usage.')
      sys.exit(2)
   for opt, arg in opts:
      if opt == '-h':
         print('JsonPersister.py -i <inputfile>')
         sys.exit()
      elif opt in ("-i", "--ifile"):
         inputfile = arg

   #print('Input file is', inputfile)


   with open(inputfile, 'r') as f:      
      data = json.load(f) 

   createdTimeStamp = "'" + datetime.datetime.utcnow().replace(microsecond=0).isoformat() + "Z'"
   print('set search_path to stoxnet;')

   for share in data['shares']:
      print(insertQuery.format( \
         formatForInsert(share, 'company'),\
         formatForInsert(share, 'name'),\
         formatForInsert(share, 'symbol'),\
         formatForInsert(share, 'lastPrice'),\
         formatForInsert(share, 'turnover'),\
         formatForInsert(share, 'quantity'),\
         formatForInsert(share, 'internalTurnover'),\
         formatForInsert(share, 'internalQuantity'),\
         formatForInsert(share, 'openPrice'),\
         formatForInsert(share, 'bidPrice'),\
         formatForInsert(share, 'askPrice'),\
         formatForInsert(share, 'dayLowPrice'),\
         formatForInsert(share, 'dayHighPrice'),\
         formatForInsert(share, 'closePrice'),\
         formatForInsert(share, 'changePercent'),\
         formatForInsert(share, 'changePercent1m'),\
         formatForInsert(share, 'tickSize'),\
         formatForInsert(share, 'sectorIndex'),\
         formatForInsert(share, 'marketCapCategory'),\
         formatForInsert(share, 'marketValue'),\
         formatForInsert(share, 'dateTime'),\
         formatForInsert(share, 'closeDateTime'),\
         createdTimeStamp,\
            ))

if __name__ == "__main__":
   main(sys.argv[1:])