import os
import os.path
import datetime
from datetime import date
import pdb
import csv
from db import conn
from common import root

def convertToNone(s):
    return None if (s=='' or s=='NA') else s

fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
          "Current SP Rating", "Current Moodys Rating", "Initial Amount",\
          "Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
          "Spread To Maturity"]

c = conn.cursor()
c.execute("SELECT MAX(pricingdate) from markit_prices")
latestdate = c.fetchone()[0]
c.close()

sql_fields = ["LoanXID", "Issuer", "DealName", "Facility", "Industry", "SP",
              "Moodys", "Amount", "Maturity", "Spread", "Bid", "Offer", "Depth",
              "STM", "PricingDate"]

for filename in os.listdir(os.path.join(root, "data", "markit")):
    date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
    # only update the new data
    if latestdate is None or date > latestdate:
        with open( os.path.join(root, "data", "markit", filename) ) as fh:
            dr = csv.DictReader(fh)
            data = []
            for line in dr:
                try:
                    line['Final Maturity'] = \
                        datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
                except ValueError:
                    if line['Final Maturity'] !='':
                        print('Maturity non empty')
                if line['Depth'] == 'implied':
                    line['Depth'] = 0
                try:
                    temp = [convertToNone(line[key]) for key in fields]
                except KeyError:
                    pdb.set_trace()
                data.append(temp)

        c = conn.cursor()
        for row in data:
            sqlstring = "INSERT INTO markit_prices({0}) " \
                "VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields)))
            c.execute(sqlstring, tuple(row) + (date,))
        conn.commit()
        c.close()
conn.close()
print("done")