from sqlalchemy import Table, create_engine, MetaData
import os
import csv
import datetime

##script to load quotes from markit composite quotes csv file
engine = create_engine('postgresql://serenitas_user@debian/serenitasdb')
metadata = MetaData(bind = engine)
quotes = Table('tranche_quotes', metadata, autoload = True)
ins = quotes.insert()

root_dir = '/home/share/CorpCDOs'

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

tenordict = {'3Y': '3yr', '5Y': '5yr', '7Y':'7yr', '10Y':'10yr'}

with open(os.path.join(root_dir, 'Tranche_data', 'CDXNAIG Tranches.csv')) as fh:
    reader = csv.DictReader(fh)
    data = []
    for csvdict in reader:
        timestamp =  datetime.datetime.strptime(csvdict['Date'], "%m/%d/%Y")
        if timestamp==datetime.datetime(2010, 7, 5):
            continue
        attach = int(float(csvdict['Attachment'])*100)
        detach = int(float(csvdict['Detachment'])*100)
        series = int(csvdict['Index Series'])
        version = int(csvdict['Index Version'])

        d = {'quotedate' : timestamp,
             'indexrefprice': float(csvdict['Index Price Mid'])*100,
             'indexrefspread': 80 if series==9 else 100,
             'quotesource' : 'MKIT',
             'trancheupfront' : convert(csvdict['Tranche Upfront Mid']),
             'trancherunning' : convert(csvdict['Tranche Spread Mid']),
             'tenor' : tenordict[csvdict['Index Term']],
             'index' : 'IG',
             'series': series,
             'version': version,
             'attach': attach,
             'detach': detach,
             'corratdetachment': convert(csvdict['Base Correlation'])
             }
        data.append(d)

with engine.begin() as conn:
    conn.execute(ins, data)