import pandas as pd
from . import mysql_engine

def latest_sim(date):
    sql_string = (
        "SELECT model_id_sub FROM model_versions "
        "JOIN model_versions_nonagency USING (model_id_sub) "
        "JOIN simulations_nonagency USING (simulation_id) "
        "WHERE (date(start_time) <= %s) AND (description = 'normal') "
        "ORDER BY start_time DESC")
    conn = mysql_engine.raw_connection()
    c = conn.cursor()
    c.execute(sql_string, (date,))
    model_id_sub, = next(c)
    c.close()
    return model_id_sub

def get_df(date):
    model_id_sub = latest_sim(date)
    df_prices = pd.read_sql_query(
        "SELECT cusip, model_version, pv, modDur, delta_yield, "
        "wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po, "
        "delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB "
        "FROM priced WHERE "
        "timestamp BETWEEN %s  AND date_add(%s, INTERVAL 1 DAY) "
        "AND model_id_sub=%s "
        "AND normalization='current_notional'",
        mysql_engine,
        ["cusip", "model_version"],
        params=(date, date, model_id_sub))
    df_percentiles = pd.read_sql_query("SELECT cusip, PV, percentile "
                                       "FROM priced_percentiles WHERE "
                                       "timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) "
                                       "AND model_version=3 "
                                       "AND model_id_sub=%s "
                                       "AND percentile IN (5, 25, 50, 75, 95) "
                                       "AND normalization='current_notional'",
                                       mysql_engine,
                                       ["cusip", "percentile"],
                                       params=(date, date, model_id_sub))
    df_prices = df_prices.unstack("model_version")
    df_percentiles = df_percentiles.unstack("percentile")
    return df_prices.join(df_percentiles, how="left")

def subprime_risk(date):
    df = get_df(date)
    df_pv = df.xs('pv', axis=1, level=0)
    df_pv.columns = ['pv1', 'pv2', 'pv3']
    df_pv_perct = df.xs('PV', axis=1, level=0)
    df_pv_perct.columns = ['pv5', 'pv25', 'pv50', 'pv75', 'pv95']
    df_modDur = df[('modDur', 1)]
    df_modDur.name = 'modDur'
    df_v1 = df.xs(1, axis=1, level='model_version')[
        ['pv_RnW', 'delta_mult', 'delta_hpi', 'delta_ir']]
    df_v1.columns = ['v1pv_RnW', 'v1_lsdel', 'v1_hpidel', 'v1_irdel']
    df_pv_FB = df[('pv_FB', 3)]
    df_pv_FB.name = 'pv_FB'
    return pd.concat([df_pv, df_modDur, df_pv_perct,
                      df.xs(3, axis=1, level='model_version')[
                          ['delta_yield', 'wal', 'pv_io', 'pv_po', 'pv_RnW', 'delta_ir_io',
                           'delta_ir_po', 'delta_hpi', 'delta_RnW', 'delta_mult']],
                      df_v1, df_pv_FB],
                     axis=1)

def get_rmbs_portfolio(date, conn, fund="SERCGMAST",
                           include_unsettled=True):
    df = pd.read_sql_query("SELECT * FROM list_positions(%s, 'Subprime', %s, %s)",
                           conn, params=(date, include_unsettled, fund))
    df["cusip"] = df.identifier.str.slice(0, 9)
    df = df.set_index("cusip")
    return df