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

engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
metadata = MetaData(bind = engine)
quotes = Table('quotes', metadata, autoload = True)
ins = quotes.insert()

root_dir = '/home/share/CorpCDOs'
quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Scenarios', 'Calibration')) if 'tranches' in f]
K = [0, 15, 25, 35, 100]

for quotefile in quotefiles:
    with open(os.path.join(root_dir, 'Scenarios', 'Calibration', quotefile)) as fh:
        quotedate = datetime.datetime.strptime(os.path.splitext(quotefile)[0].split("_")[-1], "%Y-%m-%d").date()
        series = 19 if "19" in quotefile else 21
        index = os.path.splitext(quotefile)[0].split("_")
        if quotedate < datetime.date(2013, 9, 23):
            continue
        if quotedate <= datetime.date(2014, 5, 21):
            version=1
        else:
            version=2
        reader = csv.DictReader(fh)
        data = []
        for i, csvdict in enumerate(reader):
            d = {'quotedate' : quotedate,
                 'refbasketprice': csvdict['bidRefPrice'],
                 'tranchedelta': csvdict['bidDelta'],
                 'quotesource' : csvdict['AskContributorCode'],
                 'upfront' : csvdict['Mid'],
                 'running' : float(csvdict['Coupon']) * 10000 if 'Coupon' in csvdict else 500,
                 'tenor' : '5yr',
                 'index' : 'HY',
                 'series': series,
                 'version': version,
                 'attach': K[i],
                 'detach': K[i+1]
            }
            data.append(d)
    with engine.begin() as conn:
        conn.execute(ins, data)