import pandas as pd
from analytics import Index
from db import dbengine

serenitas_engine = dbengine('serenitasdb')
tenors = {"IG": ("3yr", "5yr", "7yr", "10yr"),
          "HY": ("3yr", "5yr", "7yr"),
          "EU": ("3yr", "5yr", "7yr", "10yr"),
          "XO": ("3yr", "5yr", "7yr", "10yr")}

sql_str = "INSERT INTO index_risk VALUES(%s, %s, %s)"

conn = serenitas_engine.raw_connection()
for index in ["IG", "HY", "EU", "XO"]:
    for series in range(18, 31):
        if index in ["EU", "XO"] and series == 30:
            continue
        indices = {t: Index.from_name(index, series, t) for t in tenors[index]}
        index_quotes = pd.read_sql_query(
            "SELECT id, date, tenor, close_price FROM index_quotes_pre "
            "LEFT JOIN index_risk USING (id) "
            "WHERE index=%s AND series=%s "
            "AND source='MKIT' AND duration is NULL AND tenor IN %s",
            serenitas_engine,
            params=(index, series, tenors[index]),
            parse_dates=['date'],
            index_col='id')
        if index_quotes.empty:
            continue
        with conn.cursor() as c:
            for k, v in index_quotes.groupby('date'):
                for id, tenor, close_price in v[['tenor', 'close_price']].itertuples():
                    indices[tenor].value_date = k
                    indices[tenor].price = close_price
                    duration = indices[tenor].risky_annuity
                    theta = - indices[tenor].theta / indices[tenor].notional
                    c.execute(sql_str, (id, theta, duration))
        conn.commit()
conn.close()