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

if os.name=='nt':
    root = "//WDSENTINEL/share/CorpCDOs/"
elif os.name=='posix':
    root = "/home/share/CorpCDOs/"

workdate = '2013-01-04'
universe = os.path.join("data", "clo_universe_intex_" + workdate + ".txt")

def convertToNone(s):
    return None if s=="-" or s=="" else s

conn = psycopg2.connect(database="ET",
                        user="et_user",
                        password="Serenitas1",
                        host="192.168.1.108")
cursor = conn.cursor()

cursor.execute("select dealname, max(\"Latest Update\") from clo_universe group by dealname")
deallist = dict(cursor.fetchall())

with open( os.path.join(root, universe), "r") as fh:
    dr = csv.DictReader(fh, dialect='excel-tab')
    data = []
    for line in dr:
        if line['Deal Name'] == 'Unknown Security':
            continue
        if not line['Latest Update']:
            break
        for key in line.keys():
            line[key] = convertToNone(line[key])
        line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"]
        if line["CDOpercent"] == "NR":
            line["CDOpercent"] = None
        line["Paid Down"] = None
        if "Paid Down" in line["Latest Update"]:
            line["Paid Down"] = re.sub("Paid Down: ","", line["Latest Update"])
            line["Latest Update"] = line["Paid Down"]
        for field in ["Deal Issue Date", "Deal Termination Date", \
                          "Deal Next Pay Date", "Reinv End Date", "Latest Update"]:
            if line[field]:
                try:
                    line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date()
                except ValueError:
                    pdb.set_trace()
        for key in ["Collection Account Principal Balance", "Collection Account Interest Balance",
                    "Orig Deal Bal", "Curr Deal Bal", "Tranche Curr Bal", "Tranche Orig Bal",
                    "CDO Pct of Assets that are Structured Finance Obligations",
                    "CDO Defaulted Security Balance (Reported)"]:
            if line[key]:
                line[key] = line[key].replace(",", "")
        dealname = line['Deal,Tr/CUSIP/ISIN']
        line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"]
        line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",")
        if dealname not in deallist or line['Latest Update'] > deallist[dealname]:
            sqlstring = \
                "INSERT INTO clo_universe " \
                "VALUES (%(Deal,Tr/CUSIP/ISIN)s, %(Deal Name)s, %(Collateral Manager)s, %(Orig Deal Bal)s," \
                "%(Curr Deal Bal)s, %(Tranche Orig Bal)s, %(Tranche Curr Bal)s, %(Tranche Factor)s," \
                "%(Collection Account Principal Balance)s," \
                "%(Collection Account Interest Balance)s, %(CDOpercent)s, %(defaultedbal)s, " \
                "%(Curr Coupon)s, %(Deal Issue Date)s," \
                "%(Deal Termination Date)s, %(Deal Next Pay Date)s," \
                "%(Reinv End Date)s, %(Latest Update)s, %(Deal CUSIP List)s, %(Paid Down)s)"
            try:
                cursor.execute(sqlstring, line)
            except psycopg2.DataError:
                pdb.set_trace()

conn.commit()

cursor.close()
conn.close()