import psycopg2
import os.path
import datetime
from datetime import date
import pdb
import csv


if os.name =='nt':
    root = "//WDsentinel/share/CorpCDOs/data/bloomberg"
elif os.name == 'posix':
    root = '/home/share/CorpCDOS/data/bloomberg'


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

conn = psycopg2.connect(database="ET",
                        user="et_user",
                        password="Serenitas1",
                        host="192.168.1.108")
cursor = conn.cursor()
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"]

for filename in os.listdir(root):
    cursor.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
    corpcusips = dict(cursor)
    with open( os.path.join(root, filename), "r") as fh:
        dr = csv.DictReader(fh)
        if "datacorp" in filename:
            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:
                        cursor.execute(sqlstring, tuple(row))
                    except IndexError:
                        pdb.set_trace()
                conn.commit()
        elif "datamtge" in filename:
            cursor.execute("select * from bloomberg_mtge")
            mtgecusips = {record[0]: None for record in cursor}
            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)))
                    cursor.execute(sqlstring, tuple(row))
            conn.commit()

cursor.close()
conn.close()