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

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

def sanitize_float(intex_float):
    intex_float = intex_float.replace(",", "")
    if "(" in intex_float:
        intex_float = - float(intex_float[1:-1])
    else:
        try:
            intex_float = float(intex_float)
        except ValueError:
            pdb.set_trace()
    return intex_float

def upload_data(workdate, conn, cursor):
    dealupdate = {}
    for cusip_universe_file in os.listdir(os.path.join(common.root, "data", "Trinfo_" + workdate)):
        with open( os.path.join(common.root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh:
            dr = csv.DictReader(fh, dialect='excel-tab')
            data = []
            deals_to_update = []
            for line in dr:
                if "ISIN" not in line:
                    line['ISIN'] = None
                if "," in line['Tranche']:
                    line["dealname"], line["tranche"] = line["Tranche"].split(",")
                else:
                    continue
                line["dealname"] = line["dealname"].lower()
                dealname = line['dealname']
                line = {k: convertToNone(v) for k, v in line.iteritems()}
                if dealname not in dealupdate:
                    cursor.execute("SELECT \"Latest Update\" FROM clo_universe " \
                        "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,))
                    dealupdate[dealname] = cursor.fetchone()[0]
                sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s"
                cursor.execute(sqlstring, (line['CUSIP'],))
                curr_date = cursor.fetchone()
                if not curr_date or curr_date[0] < dealupdate[dealname]:
                    if dealname not in deals_to_update:
                        deals_to_update.append(dealname)
                    line['updatedate'] = dealupdate[dealname]
                    for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point',
                                'Curr Attachment Point', 'Orig Detachment Point',
                                'Curr Detachment Point', 'Factor', 'Coupon', 'Floater Spread']:
                        if line[key]:
                            line[key] = sanitize_float(line[key])
                        line[key] = convertToNone(line[key])
                    if "Curr Moody" not in line:
                        line['Curr Moody'] = line['Orig Moody']
                    sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, dealname, tranche, " \
                        "Coupon, Orig_Balance, Curr_Balance, Factor, Orig_Moody, Curr_Moody, " \
                        "Orig_Attach, Orig_Detach, Curr_Attach, Curr_Detach, Floater_Index, " \
                        "Spread, updatedate)" \
                        "VALUES (%(CUSIP)s, %(ISIN)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \
                        "%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \
                        "%(Orig Attachment Point)s, %(Orig Detachment Point)s, %(Curr Attachment Point)s," \
                        "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \
                        "%(updatedate)s)"
                    try:
                        cursor.execute(sqlstring, line)
                    except psycopg2.DataError:
                        pdb.set_trace()
                print("uploaded: {0}".format(line['CUSIP']))
            conn.commit()
            for dealname in deals_to_update:
                try:
                    cursor.execute("SELECT p_cusip, p_curr_subordination, "\
                                       "p_curr_thickness from et_deal_subordination(%s)",
                                   (dealname,))
                except psycopg2.DataError:
                    pdb.set_trace()
                data = cursor.fetchall()
                data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data]
                cursor.executemany("UPDATE cusip_universe SET subordination = %s, "
                                   "thickness = %s WHERE cusip = %s AND "
                                   "updatedate = %s", data)
            conn.commit()

if __name__=="__main__":
    if len(sys.argv) > 1:
        workdate = sys.argv[1]
    else:
        workdate = str(datetime.date.today())

    upload_data(workdate, common.conn, common.cursor)
    common.cursor.close()
    common.conn.close()