import os
from common import root
import csv
import datetime
from db import connmlpdb
import pdb
import re

workdate= datetime.date(2014, 7, 3)

def convert(x):
    try:
        return float(x[:-1])
    except ValueError:
        return None

sqlstr = "SELECT markit_ticker, markit_tier, cds_curve from index_members(%s, %s)"
d = {}
d2 = set([])
for index in ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'IG9', 'IG19', 'IG21']:
    spread=0.05 if 'HY' in index else 0.01
    with connmlpdb.cursor() as c:
        c.execute(sqlstr, (index, workdate))
        for line in c:
            d2.add((line['markit_ticker'], line['markit_tier']))
            key = (line['markit_ticker'], line['markit_tier'], 'USD', 'XR', spread)
            hykey = key[:-1]+(0.05,)
            if hykey in d:
                del d[hykey] ## we only keep the tightest quote
            d[key] = line['cds_curve']


colnames = ['Upfront'+tenor for tenor in ['6m', '1y', '2y', '3y', '4y', '5y', '7y', '10y']]
sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \
         "runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"

filelist = [f for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")) if f.endswith('csv')]

for f in filelist:
    d3 = set([])
    workdate = datetime.datetime.strptime(re.match("[^\d]*(\d*)", f).groups()[0], "%Y%m%d")
    with connmlpdb.cursor() as c:
        c.execute("DELETE from cds_quotes where date=%s", (workdate,))
    with open(os.path.join(root, "Tranche_data", "CDS", f)) as fh:
        csvreader = csv.DictReader(fh)
        with connmlpdb.cursor() as c:
            for line in csvreader:
                d3.add((line['Ticker'], line['Tier']))
                k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon']))
                if k in d:
                    c.executemany(sqlstr,
                                  [(workdate, t, convert(line[colnames[i]]), convert(line[colnames[i]]),
                                    float(line['RunningCoupon'])*10000, float(line['RunningCoupon'])*10000, 'MKIT',
                                    convert(line['RealRecovery'])/100) for i, t in enumerate(d[k])])
        connmlpdb.commit()
    print(workdate)
    print(d2-d3)