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

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 read_valuation_report(f):
    date = pd.Timestamp(f.rsplit('/', 3)[1])
    if date >= pd.Timestamp('2013-02-06'):
        df = pd.read_csv(f, parse_dates=['KnowledgeDate', 'PeriodEndDate'])
    else:
        df = pd.read_csv(f)
        df['KnowledgeDate'] = date
        df['PeriodEndDate'] = date - bus_day
    df['row'] = df.index
    if 'AccountingPeriod' in df:
        del df['AccountingPeriod']
    if 'CounterParty' in df:
        del df['CounterParty']
    df = df.rename(columns={'CounterPartyCode': 'counterparty'})
    if "Strat" in df:
        df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
    if "Port" in df:
        df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
    df.columns = df.columns.str.lower()
    return df

def valuation_reports():
    df = pd.concat(read_valuation_report(f) for f in
                   chain.from_iterable(get_globs('Valuation_Report')))
    # There can be duplicates in case of holidays
    df = df.sort_values(['periodenddate', 'row', 'knowledgedate'])
    df = df.drop_duplicates(['periodenddate', 'row'], 'last')
    df.to_sql('valuation_reports', dbengine('dawndb'), if_exists='append', index=False)

def read_pnl_report(f):
    df = pd.read_csv(f)
    df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
    df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
    df['LongShortIndicator'] = df['LongShortIndicator'].str.strip()
    df.columns = df.columns.str.lower().str.replace(" ", "")
    return df

def pnl_reports():
    df = {}
    for f in chain.from_iterable(get_globs('Pnl*')):
        if not (f.endswith("Pnl.csv") and f.endswith("Pnl_Report.csv")):
            continue
        date = pd.Timestamp(f.rsplit('/', 3)[1])
        date = date - bus_day
        df[date] = read_pnl_report(f)
    df = pd.concat(df, names=['date', 'row']).reset_index()
    df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False)

def read_cds_report(f, old_report=False):
    df = pd.read_csv(f)
    for col in df.columns:
        vc = len(df[col].value_counts())
        if vc == 0:
            del df[col]
            continue
    if 'Contractual Definition' in df:
        contract = df['Contractual Definition']
        contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014')
        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', 'VAT', 'SEC Fee', 'Clearing Fee',
                  'Remaining Notional', 'Trading Notional', 'BBGID'],
                 axis=1, errors='ignore')
    df.columns = df.columns.str.lower().str.replace(" ", "_")
    if old_report:
        df.calendar = df.calendar.str.replace(" ", "")
        df = df.rename(columns={'direction': 'buy/sell'})
        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['buy/sell'] = df['buy/sell'].astype('category')
    df['buy/sell'].cat.categories = ['Buyer', 'Seller']
    df.prime_broker = df.prime_broker.where(df.prime_broker != 'NONE')
    df.loc[df.executing_broker.isnull(),'executing_broker'] = df[df.executing_broker.isnull()].counterparty
    del df['counterparty']
    df = df.rename(columns={'executing_broker': 'counterparty',
                            'independent_%': 'independent_perc'})
    return df

def cds_reports():
    df = {}
    for f in chain.from_iterable(get_globs('CDS_Report')):
        date = pd.Timestamp(f.rsplit('/', 3)[1])
        old_report = date <= pd.Timestamp('2017-02-28') or date == pd.Timestamp('2017-03-02')
        date = date - bus_day
        df[date] = read_cds_report(f, old_report)
    df = pd.concat(df, names=['date', 'row']).reset_index()
    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'])