import glob
import pandas as pd
from sqlalchemy import create_engine
import pdb
engine = create_engine("postgresql://serenitas_user@debian/serenitasdb")
index_version = pd.read_sql_table("index_version", engine, index_col='redindexcode')

for f in glob.iglob("/home/share/CorpCDOs/Tranche_data/Composite_reports/Tranche Composites*"):
    df = pd.read_csv(f, skiprows=2, parse_dates=['Date'])
    df.rename(columns={'Date':'quotedate',
                       'Index Term':'tenor',
                       'Attachment':'attach',
                       'Detachment':'detach',
                       'Tranche Upfront Bid': 'upfront_bid',
                       'Tranche Upfront Mid': 'upfront_mid',
                       'Tranche Upfront Ask': 'upfront_ask',
                       'Index Price Mid': 'index_price',
                       'Tranche Spread Mid': 'tranche_spread',
                       'Red Code':'redindexcode'}, inplace=True)
    df.attach = df.attach *100
    df.detach = df.detach * 100
    df.tranche_spread = df.tranche_spread*10000
    df.tenor = df.tenor.str.lower() + 'r'
    df.set_index('redindexcode', inplace=True)
    df = df.join(index_version)
    df = df.filter(['basketid', 'quotedate', 'tenor', 'attach', 'detach',
                    'upfront_bid', 'upfront_ask', 'upfront_mid',
                    'tranche_spread', 'index_price'])
    df.to_sql('markit_tranche_quotes', engine, if_exists='append', index=False)