import pandas as pd
from functools import reduce
from position import get_list
from sqlalchemy import create_engine

def pnl_explain(engine, identifier, start_date = None, end_date = None):
    """ if start_date is None, pnl since inception"""
    trades = pd.read_sql_query("SELECT * FROM bonds where identifier=%s", engine,
                               params=(identifier,), parse_dates=['trade_date', 'settle_date'],
                               index_col=['settle_date'])
    marks = pd.read_sql_query("SELECT * FROM marks where identifier=%s", engine,
                              params=(identifier,), parse_dates = ['date'], index_col='date')
    factors = pd.read_sql_query("SELECT * FROM factors_history where identifier=%s", engine,
                                params=(identifier,), parse_dates = ['last_pay_date', 'prev_cpn_date'],
                                index_col=['last_pay_date'])

    for key in ['faceamount', 'principal_payment', 'accrued_payment']:
        trades.loc[~trades.buysell, key]  = -trades[key][~trades.buysell]

    df = (marks[['price']].join(factors, how='outer').
          join(trades[['principal_payment', 'accrued_payment', 'faceamount']], how='outer'))
    df.sort_index(inplace=True)
    if start_date is None:
        start_date = trades.index.min()
    if end_date is None:
        end_date = pd.datetime.today()
    dates = pd.bdate_range(start_date, end_date)
    keys1 = ['price','factor', 'coupon', 'prev_cpn_date']
    df[keys1] = df[keys1].fillna(method='ffill')
    keys2 = ['losses', 'principal','interest', 'faceamount','accrued_payment', 'principal_payment']
    df[keys2] = df[keys2].fillna(value=0)
    df.faceamount = df.faceamount.cumsum()
    keys = keys1 + ['faceamount']
    df1 = df.reindex(dates, keys, method='ffill')
    keys = ['losses', 'principal','interest', 'accrued_payment', 'principal_payment']
    df2 = df.reindex(dates, keys, fill_value=0)
    daily = pd.concat([df1, df2], axis = 1)

    daily['unrealized_pnl'] = daily.price.diff() * daily.factor.shift()/100 * daily.faceamount
    daily['realized_pnl'] = (daily.price/100*daily.factor.diff()+daily.principal/100) * daily.faceamount
    daily['clean_nav'] = daily.price/100 * daily.factor * daily.faceamount
    daily['realized_accrued'] = daily.interest/100 * daily.faceamount
    days_accrued = daily.index - daily.prev_cpn_date
    daily['unrealized_accrued'] = days_accrued.dt.days/360*daily.coupon/100*daily.factor*daily.faceamount
    extra_pnl = daily.clean_nav.diff() - daily.principal_payment
    daily.loc[daily.principal_payment>0 , 'unrealized_pnl'] += extra_pnl[daily.principal_payment>0]
    daily.loc[daily.principal_payment<0, 'realized_pnl'] += extra_pnl[daily.principal_payment<0]
    daily['realized_accrued'] -= daily.accrued_payment
    daily['accrued'] = daily.unrealized_accrued.diff() + daily.realized_accrued
    return daily[['unrealized_pnl', 'realized_pnl', 'unrealized_accrued', 'realized_accrued', 'accrued']].iloc[1:,]

def pnl_explain_list(engine, id_list, start_date = None, end_date = None):
    return reduce(lambda x,y: x.add(y, fill_value=0),
                  (pnl_explain(engine, identifier, start_date, end_date) for identifier in id_list))

if __name__=="__main__":
    engine = create_engine("postgresql://dawn_user@debian/dawndb")
    workdate = pd.datetime.today()
    clo_list = get_list(engine, workdate, 'Subprime')
    df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30')