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

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

fields_corp = ["Cusip", "Price", "PricingDate", "Issuer", "Maturity", "Coupon",
               "CouponType", "Frequency", "Spread", "Libor_floor", "loan_margin",
               "covlite", "secondlien", "defaulted", "Source"]
fields_mtge = ["Cusip", "Issuer", "Maturity", "Coupon", "CouponType", "Frequency",
               "Spread", "Moody", "InitialMoody"]

root = os.path.join(root, "data", "bloomberg")

for filename in os.listdir(root):
    with conn.cursor() as c:
        c.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
        corpcusips = dict(c)
    with open( os.path.join(root, filename), "r") as fh:
        dr = csv.DictReader(fh)
        if "datacorp" in filename:
            c = conn.cursor()
            for line in dr:
                if line["LAST_UPDATE_DT"] != 'NA':
                    line["LAST_UPDATE_DT"] = \
                        datetime.datetime.strptime(line["LAST_UPDATE_DT"], '%Y-%m-%d').date()
                else:
                    line["LAST_UPDATE_DT"] = \
                        datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
                    line["PX_LAST"] = None
                    if line["MATURITY"] != 'NA':
                        line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d')
                row = [convertToNone(line[field]) for field in dr.fieldnames]
                # cursor.execute("SELECT max(PricingDate) from bloomberg_corp where Cusip = %s", (line['CUSIP'],))
                # currentpricingdate = cursor.fetchone()[0]
                if line['CUSIP'] not in corpcusips or corpcusips[line['CUSIP']]<line['LAST_UPDATE_DT']:
                    sqlstring = "INSERT INTO bloomberg_corp({0}) " \
                        "VALUES({1})".format(",".join(fields_corp), ",".join(["%s"] * len(fields_corp)))
                    try:
                        c.execute(sqlstring, tuple(row))
                    except IndexError:
                        pdb.set_trace()
            c.close()
            conn.commit()

        elif "datamtge" in filename:
            c = conn.cursor()
            c.execute("select * from bloomberg_mtge")
            mtgecusips = {record[0]: None for record in c}
            for line in dr:
                if line["MATURITY"] != 'NA':
                    line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d').date()
                row = [convertToNone(line[field]) for field in dr.fieldnames]
                # sqlstr = "SELECT * from bloomberg_mtge where Cusip=%s"
                # cursor.execute(sqlstr, (line['CUSIP'],))
                # found = cursor.fetchone()
                if line['CUSIP'] not in mtgecusips:
                    sqlstring = "INSERT INTO bloomberg_mtge({0}) " \
                        "VALUES({1})".format(",".join(fields_mtge),
                                             ",".join(["%s"] * len(fields_mtge)))
                    c.execute(sqlstring, tuple(row))
            c.close()
            conn.commit()

conn.close()
print("done")