import pandas as pd
from functools import reduce
from position import get_list
from sqlalchemy import create_engine
from dates import bus_day, imm_dates

def pnl_explain(identifier, start_date = None, end_date = None,
                uri = 'postgresql://dawn_user@debian/dawndb'):
    """ if start_date is None, pnl since inception"""
    engine = create_engine(uri)
    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.date_range(start_date, end_date, freq = bus_day)
    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))

def cds_explain(engine, index, series, tenor, attach = None, detach = None,
                start_date = None, end_date = None):
    factors = pd.read_sql_query("SELECT * FROM index_desc WHERE index=%s AND series=%s AND tenor=%s "\
                                "ORDER BY lastdate",
                                engine, parse_dates=['lastdate'],
                                index_col='lastdate', params = (index, series, tenor))
    if attach is None:
        quotes = pd.read_sql_query("SELECT * from index_quotes WHERE index=%s AND series=%s AND tenor=%s " \
                                   "ORDER BY date",
                                   engine, parse_dates=['date'],
                                   index_col='date', params = (index, series, tenor))
    else:
        quotes = pd.read_sql_query("SELECT quotedate, upfront_mid AS closeprice, tranche_spread " \
                                   "FROM markit_tranche_quotes JOIN index_version " \
                                   "USING (basketid) WHERE index=%s AND series=%s " \
                                   "AND tenor=%s AND attach=%s AND detach=%s " \
                                   "ORDER by quotedate",
                                   engine, parse_dates=['quotedate'], index_col='quotedate',
                                   params = (index, series, tenor, attach, detach))
    if start_date is None:
        start_date = quotes.index.min()
    if end_date is None:
        end_date = pd.datetime.today()
    coupon = 0.01
    dates = pd.date_range(start_date, end_date, freq = bus_day)
    yearfrac = imm_dates(start_date, end_date)
    yearfrac = yearfrac.to_series().reindex(dates, method='ffill')
    yearfrac = yearfrac.index-yearfrac
    yearfrac = (yearfrac.dt.days+1)/360
    yearfrac.name = 'yearfrac'
    quotes = quotes.reindex(dates, method='ffill')
    recovery = -factors.indexfactor.diff()-factors.cumulativeloss.diff()
    recovery.name = 'recovery'
    recovery = recovery.shift(-1)/100
    recovery = recovery.reindex(dates, fill_value=0).shift()
    df = (quotes.
          join(factors[['indexfactor']], how='left').
          join(recovery).join(yearfrac))
    df.indexfactor = df.indexfactor.bfill()/100
    df.loc[df.indexfactor.isnull(), 'indexfactor'] = factors.indexfactor.iat[-1]/100
    df['unrealized_accrued'] = df.yearfrac*coupon*df.indexfactor
    df['accrued'] = df.unrealized_accrued.diff()
    df['realized_accrued'] = -df.accrued.where(df.accrued<0, 0)
    df.accrued = df.accrued.where(df.accrued>0, df.unrealized_accrued)
    df.loc[df.realized_accrued>0, 'realized_accrued'] += df.loc[df.realized_accrued>0, 'accrued']
    df['unrealized_pnl'] = df.closeprice.diff() * df.indexfactor.shift()/100
    df['realized_pnl'] = df.closeprice/100*df.indexfactor.diff()+df.recovery
    return df

if __name__=="__main__":
    # workdate = pd.datetime.today()
    # clo_list = get_list(workdate, 'Subprime')
    # df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30')
    engine = create_engine("postgresql://serenitas_user@debian/serenitasdb")
    df = cds_explain(engine, 'IG', 9, '10yr')