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 = df.columns.str.lower()

    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 = df.columns.str.lower()
    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 cds_reports():
    df = {}
    for f in chain.from_iterable(get_globs('CDS_Report')):
        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)
    for col in ['Buy/Sell', 'Counterparty', 'CCP', 'Ccy', 'Direction', 'Price Ccy',
                'Period End Date', 'Basis', 'Roll Convention', 'Settle Mode',
                'Strategy', 'Trade Type', 'Trade Status', 'Prime Broker']:
        df[col] = df[col].astype('category')
    for col in df.columns:
        if 'Date' in col and col != 'Period End Date':
            df[col] = pd.to_datetime(df[col])
    for col in df.columns:
        vc = len(df[col].value_counts())
        if vc == 0:
            del df[col]
            continue
        if df[col].dtype == 'object' and vc < 20:
            df[col] = df[col].astype('category')
    contract = df['Contractual Definition']
    contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014').astype('category')
    df['Contractual Definition'] = contract
    df = df.drop(['Bloomberg Yellow key', 'Created User', 'Last Modified User',
                  'Last Modified Date', 'Fund Long Name', 'Instrument Sub Type',
                  'Netting Id', 'Client', 'Trade Status', 'Position Status',
                  'Clearing Broker', 'Settle Mode', 'Off Price', 'On Price',
                  'Price Ccy'],
                 axis=1)
    df.columns = df.columns.str.lower()
    df.columns = df.columns.str.replace(" ", "_")
    df.roll_convention = df.roll_convention.str.title()
    df = df[df.strategy != 'SER_TEST']
    df.loc[df.strategy == 'SERCGMAST__MBSCDS', 'strategy'] = 'MBSCDS'
    df.strategy = df.strategy.str.replace("SER_","")
    df.loc[df['buy/sell'].isnull(), 'buy/sell'] = df.loc[df['buy/sell'].isnull(), 'direction']
    df['buy/sell'].cat.categories = ['Buyer', 'Seller']
    del df['direction']
    df.prime_broker = df.prime_broker.cat.remove_categories('NONE')
    df.calendar = df.calendar.str.replace(" ", "")
    df['executing_broker'] = df['executing_broker'].astype('object')
    df.loc[df.executing_broker.isnull(),'executing_broker'] = df[df.executing_broker.isnull()].counterparty
    del df['counterparty']
    df = df.rename(columns={'executing_broker': 'counterparty'})
    return df

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'])