import os
import common
import csv
import datetime
from mlpdb import conn
import pdb


def convert(x):
    try:
        return float(x)
    except:
        return None

with open(os.path.join(common.root, "Scenarios", "bbg-markit.csv")) as fh:
    csvreader = csv.DictReader(fh)
    newtickermapping = {line['Markit_ticker']: (line['Bbg_ticker'], line['company_id']) for line in csvreader}

sqlstr = "select cds_curve from cds_issuers where ticker=%s and company_id=%s"

tm = {}
with conn.cursor() as c:
    for k, v in newtickermapping.items():
        c.execute(sqlstr, v)
        r = c.fetchone()
        tm[k] = r['cds_curve']

rootdir = os.path.join(common.root, "Scenarios", "Calibration")
filelist = [f for f in os.listdir(rootdir) if "igs_singlenames" in f]

sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \
         "runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"

sqlstr2 = "SELECT curve_ticker FROM cds_quotes where date=%s"
sqlstr3 = "DELETE from cds_quotes where date=%s and curve_ticker=%s"
tenor = ['6M']+[str(i)+'Y' for i in [1, 2, 3, 4, 5, 7, 10]]
for f in filelist:
    date = datetime.datetime.strptime(f[16:26], "%Y-%m-%d").date()
    with conn.cursor() as c:
        c.execute(sqlstr2, (date,))
        l = set([t[0] for t in c])
    print(f)
    with open(os.path.join(rootdir, f)) as fh:
        csvreader = csv.DictReader(fh)
        for line in csvreader:
            if line['ticker']=='':
                continue
            knowntickers = [(date, t) for t in tm[line['ticker']][1:] if t in l]
            unknowntickers = [(tenor[i], t) for i, t in enumerate(tm[line['ticker']][1:], 1)]
            with conn.cursor() as c:
                c.executemany(sqlstr3, knowntickers)
                conn.commit()
            toinsert = [(date, ticker, convert(line[tenor]), convert(line[tenor]),
                         float(line['running']), float(line['running']), 'MKIT',
                         float(line['recovery'])/100) for tenor, ticker in unknowntickers]
            with conn.cursor() as c:
                c.executemany(sqlstr, toinsert)
                conn.commit()

# tenord = {'3Y': '3yr', '5Y': '5yr', '7Y': '7yr', '10Y': '10yr'}
# sqlstr = "INSERT INTO tranche_quotes VALUES({0})".format(",".join(["%s"]*17))

# # with open("../../CDXNAIG Tranches.csv") as fh:
# #     csvreader=csv.DictReader(fh)
# #     toinsert = [(datetime.datetime.strptime(line['Date'], "%m/%d/%Y"), 'IG', line['Index Series'],
# #                  line['Index Version'], tenord[line['Index Term']], int(float(line['Attachment'])*100),
# #                  int(float(line['Detachment'])*100), convert(line['Tranche Upfront Mid']),
# #                  convert(line['Tranche Spread Mid']),
# #                  0, float(line['IndexRefSpread'])*10000, None, None, None, None, None, 'MKIT')
# #                 for line in csvreader if line['Date']!='7/5/2010']
# #     with conn.cursor() as c:
# #         c.executemany(sqlstr, toinsert)
# #         conn.commit()



# with conn.cursor() as c:
#     c.execute("SELECT * FROM quotes where index='IG'")
#     toinsert = [tuple(r[:7])+(r['upfront'],r['running'])+(None,r['refbasketprice'])+tuple(r[10:]) for r in c]
# with conn.cursor() as c:
#     c.executemany(sqlstr, toinsert)
#     conn.commit()