from sqlalchemy import create_engine, MetaData, Table, bindparam
import pandas as pd
import pdb

#index='HY'
#series=[10, 11, 13, 15, 17, 19, 21, 23]
index='IG'
series= [9, 11, 13, 15, 17, 19, 21, 23]
engine = create_engine('postgresql://serenitas_user:Serenitas1@debian/serenitasdb')
metadata = MetaData(bind=engine)
index_quotes= Table('index_quotes', metadata, autoload=True)

for s in series:
    factors = pd.read_sql_query("select lastdate, indexfactor, cumulativeloss from index_version " \
                                "where index=%s and series=%s", engine, params = (index, s),
                                parse_dates = ['lastdate'])
    factors['recovery'] = 1+factors.cumulativeloss.diff(-1)
    for t in ['3yr', '5yr', '7yr', '10yr']:
        df = pd.read_sql_query("select date as b_date, version, closeprice, modelprice from index_quotes " \
                               "where index=%s and series=%s and tenor=%s",
                               engine, params = (index, s, t), parse_dates =['b_date'])
        df.sort_index(inplace=True)
        df['adjcloseprice'] = df['closeprice']
        df['adjmodelprice'] = df['modelprice']

        for i in range(factors.shape[0]-1):
            r = factors.ix[i].indexfactor/factors.ix[i+1].indexfactor
            df.ix[df.version<=i+1,['adjcloseprice', 'adjmodelprice']] *= r
            df.ix[df.version<=i+1,['adjcloseprice', 'adjmodelprice']] -= (r-1) * factors.ix[i].recovery * 100

        del df['closeprice']
        del df['modelprice']
        stmt = index_quotes.update().where((index_quotes.c.date==bindparam('b_date')) &
                                          (index_quotes.c.index==index) &
                                           (index_quotes.c.series==s) &
                                           (index_quotes.c.tenor==t))
        engine.execute(stmt, df.to_dict(orient='record'))