from analytics import CreditIndex
from utils.db import serenitas_pool
import numpy as np
import pandas as pd

def load_sheet(index, series):
    df = pd.read_excel("/home/serenitas/CorpCDOs/Tranche_data/USTrancheClientFile11072019.XLS",
                       sheet_name=f'5Y {index.upper()}{series}', skiprows=[0, 1, 2, 3, 4])

    sql_str = ("INSERT INTO tranche_quotes("
               "quotedate, index, series, version, tenor, attach, detach, "
               "trancheupfrontmid, trancherunningmid, indexrefprice, "
               "tranchedelta, corratdetachment, quotesource) "
               f"VALUES({','.join(['%s'] * 13)})")

    df = df.set_index("Date")
    if index == "HY":
        cols = [0., 0.15, 0.25, 0.35]
    else:
        cols = [0., 0.03, 0.07, 0.15]
    if index == "HY":
        df_upfront = df[['0% - 15%', '15% - 25%', '25% - 35%', '35% - 100%']]
    else:
        df_upfront = df[['0-3%', '3-7%', '7-15%', '15-100%']]
    df_upfront.columns = cols
    df_upfront = df_upfront.stack()
    df_upfront.name = 'upfront'
    df_delta = df[['Delta', 'Delta.1', 'Delta.2', 'Delta.3']]
    df_delta.columns = cols
    df_delta = df_delta.stack()
    df_delta.name = 'delta'
    df_corr = df[['Correlation', 'Correlation.1', 'Correlation.2']]
    df_corr.columns = cols[:-1]
    df_corr = df_corr.stack()
    df_corr.name = 'correlation'
    df_detach = pd.DataFrame(np.repeat([cols[1:] + [1.]], len(df.index), 0),
                             index=df.index, columns=cols).stack()
    df_detach.name='detach'
    df_merged = pd.concat([df_upfront, df_delta, df_corr, df_detach], axis=1)
    df_merged.index.names = ['date', 'attach']
    if index == "HY":
        df_merged['price'] = 100. * (1 - df_merged.upfront)
    else:
        df_merged['price'] = 100 * df_merged.upfront
    df_merged = df_merged.reset_index("attach")
    df_final = df_merged.join(df['Spread (bp)'])
    df_final = df_final.rename(columns={'Spread (bp)': 'indexspread'})

    conn = serenitas_pool.getconn()
    credit_index = CreditIndex(index, series, "5yr")
    with conn.cursor() as c:
        for t in df_final.itertuples():
            credit_index.value_date = t.Index.date()
            credit_index.spread = t.indexspread
            c.execute(sql_str, (t.Index + pd.DateOffset(hours=17), index, series,
                                credit_index.version, "5yr",
                                int(t.attach * 100),
                                int(t.detach*100), t.price, 500, credit_index.price, t.delta, t.correlation, "MSre"))
    conn.commit()
    serenitas_pool.putconn(conn)

if __name__ == "__main__":
    for index in ("IG", "HY"):
        for series in (25, 27, 29, 31, 33):
            if index == "IG" and series <= 29:
                continue
            load_sheet(index, series)