from glob import iglob
import os
import pandas as pd
from itertools import chain
from dates import bus_day
from db import dbengine

def get_globs(fname, years=['2013', '2014', '2015', '2016', '2017']):
    basedir = '/home/serenitas/Daily'
    globs = [iglob(os.path.join(basedir,
                                year,
                                "{0}_*/{0}*/Reports/{1}.csv".format(year, fname)))
             for year in years]
    for year in years[-2:]:
        globs.append(iglob(os.path.join(basedir,
                                        '{0}-*/Reports/{1}.csv'.format(year,
                                                                       fname))))
    return globs

def valuation_reports():
    df = pd.DataFrame()
    for f in chain.from_iterable(get_globs('Valuation_Report')):
        try:
            date = pd.Timestamp(f.split('/')[6])
        except ValueError:
            date = pd.Timestamp(f.split('/')[4])

        if date >= pd.Timestamp('2013-02-06'):
            newdf = pd.read_csv(f, parse_dates=['KnowledgeDate','PeriodEndDate'])
        else:
            newdf = pd.read_csv(f)
            newdf['KnowledgeDate'] = date
            newdf['PeriodEndDate'] = date - bus_day
        newdf['row'] = newdf.index
        if newdf.empty or ('PeriodEndDate' in df and \
                           not df[df.PeriodEndDate == newdf.PeriodEndDate.iat[0]].empty):
            continue
        df = df.append(newdf)
    del df['AccountingPeriod']

    ## cleanups
    df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
    df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
    for col in ['Strat', 'InvCcy', 'Fund', 'Port']:
        df[col] = df[col].astype('category')
    df.columns = [c.lower() for c in df.columns]

    df.to_sql('val_reports', dbengine('dawndb'), if_exists='append', index=False)

def pnl_reports():
    df = {}
    for f in chain.from_iterable(get_globs('Pnl')):
        try:
            date = pd.Timestamp(f.split('/')[6])
        except ValueError:
            date = pd.Timestamp(f.split('/')[4])
        date = date - bus_day
        df[date] = pd.read_csv(f)
        df[date]['row'] = df[date].index
    df = pd.concat(df, names=['date', 'to_drop'])
    df.reset_index(level='to_drop', drop=True, inplace=True)
    df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
    df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
    for col in ['Fund', 'Strat', 'Port', 'LongShortIndicator', 'InvCcy']:
        df[col] = df[col].astype('category')

    ## cleanups
    df = df.reset_index()
    df.columns = [c.lower() for c in df.columns]
    df['longshortindicator'] = df['longshortindicator'].str.strip()
    df.columns = [c.replace(" ", "") for c in df.columns]

    df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False)

def monthly_pnl_bycusip(df, strats):
    df = df[(df.Strat.isin(strats)) & (df.CustAcctName=='V0NSCLMAMB')]
    pnl_cols = ['BookUnrealMTM', 'BookRealMTM', 'BookRealIncome', 'BookUnrealIncome',
                'TotalBookPL']
    return df.groupby('InvId').resample('M').last()[['MTD '+col for col in pnl_cols]]

if __name__=='__main__':
    valuation_reports()
    pnl_reports()
    df_val = pd.read_hdf('globeop.hdf', 'valuation_report')
    df_pnl = pd.read_hdf('globeop.hdf', 'pnl')
    nav = df_val[df_val.Fund == 'SERCGMAST'].groupby('PeriodEndDate')['EndBookNAV'].sum()
    subprime_strats = ['MTG_GOOD', 'MTG_RW', 'MTG_IO','MTG_THRU', 'MTG_B4PR']
    clo_strats = ['CLO_BBB', 'CLO_AAA', 'CLO_BB20']

    ## daily pnl by cusip
    #subprime_daily_pnl = daily_pnl_bycusip(df_pnl, subprime_strats)

    df_monthly = monthly_pnl_bycusip(df_pnl, subprime_strats)
    #df_monthly.loc[idx[ts('2015-01-01'):ts('2015-01-31'),:],:]

    # clo = df_pnl[df_pnl.Strat.isin(clo_strats)]
    # clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M').last()

    # clo.groupby(level=0).sum()['2015-12-01':'2015-12-31']
    df_val.set_index(['custacctname', 'periodenddate', 'invid', 'strat'])