from glob import iglob
import os
import pandas as pd
import datetime
from db import dbengine
import load_globeop_report as load_globeop

def get_monthly_pnl():
    sql_string = "SELECT * FROM pnl_reports"
    df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'])
    df_pnl = df_pnl.set_index('date')
    zero_factor = df_pnl[df_pnl.invid.str.match('^.{9}_A$')]['invid'].unique()
    df_pnl.invid = df_pnl.invid.apply(lambda x: x[:9] if x in zero_factor.tolist() else x)
    pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl']
    df_pnl = df_pnl.reset_index().groupby(['date','invid']).sum().reset_index('invid')
    df_pnl = df_pnl.groupby('invid').resample('M').last()[['mtd'+col for col in pnl_cols]]
    df_pnl.index.names = ['identifier','date']
    return df_pnl.reset_index()

def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'):
    sql_string = "SELECT * FROM risk_positions(%s, %s) WHERE notional > 0"
    df_positions = pd.read_sql_query(sql_string, dbengine('dawndb'),params = [date, asset_class])
    df_pnl = get_monthly_pnl()
    df_all = df_positions.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier'])
    return df_all

def check_valuation():
    sql_string = "SELECT * FROM valuation_reports"
    df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate'])
    nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum()
    return nav.resample('M').last()

def trade_performance():
    sql_string = "SELECT * FROM bonds"
    df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates= ['lastupdate', 'trade_date','settle_date'])
    df_trades = df_trades[df_trades.asset_class == 'Subprime']
    df_pnl = get_monthly_pnl()

    df_sell = df_trades[df_trades.buysell == False].groupby('identifier').last().reset_index()
    df_sell.identifier = df_sell.identifier.str[:9]
    df_buy = df_trades[df_trades.buysell == True].groupby('identifier').last().reset_index()

    df_all = df_sell.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier'])

    #now build up the table
    g = df_buy.groupby('identifier').sum()
    init_inv = g.principal_payment + g.accrued_payment
    init_inv.name = 'initialinvestment'

    first_buy_date = df_buy.groupby('identifier').first().trade_date
    first_buy_date.name = 'firstbuydate'

    df_all = df_all.join(init_inv, on='identifier')
    df_all = df_all.join(first_buy_date, on='identifier')
    df_all['percent_gain'] = df_all.mtdtotalbookpl / df_all.initialinvestment
    df_all['days_held'] = df_all.trade_date - df_all.firstbuydate

    df_all = df_all.sort_values('trade_date', ascending=False)

    table = pd.DataFrame()
    #table['average_days_held'] = df_all.days_held.mean()

    return df_all

if __name__=='__main__':
    nav = check_valuation()
    df_pnl = trade_performance()