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

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

root = root + "Collaterals_2012-11-20"

fields = ['Asset Name', 'Issuer', 'Current Balance', 'Maturity Date', 'Asset Subtype', \
          'Asset Type', 'Gross Coupon', 'Spread', 'Frequency', 'Next Paydate', 'Second Lien', \
          'LoanX ID', 'CUSIP', 'Market Price', 'Market Price Source', 'Price Date', 'Fixed or Float', \
          'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security']

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

conn = psycopg2.connect(database="ET", user="et_user", password="Serenitas1", host="192.168.1.108")
cursor = conn.cursor()
prog = re.compile("\((.*)\)")

for dealname in os.listdir(root):
    #dealname, updatedate = line.rstrip().split()
    # updatedate = datetime.datetime.strptime(updatedate, '%m/%d/%Y')
    #dealname = dealname.upper() + ",AD.txt"
    with open( os.path.join(root, dealname), "r") as fh:
        dr = csv.DictReader(fh, dialect = 'excel-tab')
        dealname , ending = dealname.split(',')
        dealname = dealname.lower()
        data = []
        for line in dr:
            missingfields = list(set(fields) - set(dr.fieldnames))
            for field in missingfields:
                line[field] = None
            if line['Fixed or Float'] is not None:
                line['Fixed or Float'] = line['Fixed or Float'].upper()
            if 'LoanX ID' in dr.fieldnames and len(line['LoanX ID']) >8:
                print "dubious id found: {0}".format(line['LoanX ID'])
                line['LoanX ID'] = line['LoanX ID'][:8]
            try:
                # make sure the string is utf8 safe
                line['Issuer']= line['Issuer'].decode('windows-1252')
                if line['Issuer']=='' or line['Issuer'].lower() == 'dummy':
                    break
            except AttributeError as detail:
                print dealname
            #convert (x) to -x
            for field in ['Spread', 'Gross Coupon']:
                if line[field] and prog.match(line[field]):
                    line[field] = - float(prog.match(line[field]).group(1))
            if line['Market Price'] != '' and isinstance(line['Market Price'], str):
                line['Market Price'] = float(line['Market Price'].replace(',',''))
            if isinstance(line['Current Balance'], str):
                line['Current Balance'] = float(line['Current Balance'].replace(',',''))
            try:
                r = [convertToNone(line[field]) for field in fields]
            except KeyError as detail:
                print detail
                pdb.set_trace()
            data.append(r)
    cursor.execute( "select \"Latest Update\" from clo_universe "
                    "where dealname='{0}'".format(dealname))
    try:
        updatedate = cursor.fetchone()[0]
    except TypeError:
        pdb.set_trace()
    cursor.execute( "select max(updatedate) from et_collateral where dealname='{0}'".format(dealname))
    updatedate2 = cursor.fetchone()[0]
    if updatedate2 is None:
        pdb.set_trace()
    if updatedate>updatedate2:
        for row in data:
            # print dealname, row
            sqlstr = "INSERT INTO ET_COLLATERAL(dealname, updatedate, name, IssuerName," \
                "CurrentBalance, Maturity, AssetSubtype, AssetType, GrossCoupon," \
                "Spread, Frequency, NextPaydate, SecondLien, LoanXID, Cusip, IntexPrice," \
                "IntexPriceSource, IntexPriceDate, FixedOrFloat, DefaultedFlag, CovLite, isCDO)" \
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
                "%s, %s, %s)"
            try:
                cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
            except psycopg2.DataError:
                pdb.set_trace()
            except psycopg2.IntegrityError:
                # crazy hack intex unique id is not really unique
                if dealname in ["caval1", "octag11", "windriv2"]:
                    conn.rollback()
                    row[0] = "Affinion Group - Tranche B Term Loan - 18"
                    cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
                else:
                    pdb.set_trace()
            conn.commit()

cursor.close()
conn.close()