import pandas as pd
import datetime
from db import dbengine
from yieldcurve import YC

def ver_one():
    date = (datetime.date.today() + pd.offsets.BMonthEnd(-1)).date()
    df = pd.read_sql_query("select * from risk_positions(%s, 'Subprime') where price >0 and length(identifier) = 9"
                        , dbengine('dawndb'), params = [date], index_col=['identifier'])
    v1_temp = {}
    for cusip in df.index:
        v1_temp[cusip] = pd.read_sql_query("select * from priced_percentiles where date(timestamp) = %s and cusip = %s and model_version = 1"
                        , dbengine('dawndb'), params = [date, cusip])
    v1 = pd.concat(v1_temp)
    v1 = v1.set_index('cusip')
    v1 = v1.merge(df, left_index=True, right_index=True)
    yc = YC(evaluation_date=date)
    v1 = v1.replace('M_MTG_GOOD','M_MTG_LMG')

    v1['return'] = v1.apply(lambda df: (df.pv/(df.price/100)) ** (1/df.moddur) - 1 + yc.zero_rate(df.moddur).rate, axis = 1)

    port_ret = v1.groupby('percentile').apply(wavg, 'return', 'usd_market_value')
    strat_ret = v1.groupby(['percentile','strategy']).apply(wavg, 'return', 'usd_market_value').unstack(-1)
    port_ret = port_ret.rename('Portfolio')
    strat_ret = strat_ret.join(port_ret)

    return strat_ret

def plot_strat():

    import seaborn as sns

    strat_ret = ver_one()
    strat_ret = strat_ret.rename(columns={ \
                'M_MTG_FP': 'First Pay',\
                'M_MTG_IO': 'Interest Only',\
                'M_MTG_LMG': 'Long Duration Par',\
                'M_MTG_PR': 'Pro-Rata Pay',\
                'M_MTG_RW': 'R&W Dependent',\
                'M_MTG_SD': 'Stepdown',\
                'M_MTG_THRU': 'Pass-through'
                })

    sns.set(style="darkgrid")
    ax = strat_ret.plot(figsize = [10, 3.5])
    ax.set_ylim([-.2,.6])

    vals = ax.get_yticks()
    ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals])
    ax.set_ylabel('return')

    ax.legend(loc='upper center', bbox_to_anchor=(0.5, -.05),  shadow=True, ncol=3)

def wavg(group, avg_name, weight_name):
    d = group[avg_name]
    w = group[weight_name]
    try:
        return (d * w).sum() / w.sum()
    except ZeroDivisionError:
        return d.mean()