import pandas as pd
import os
import re
from pickle import dumps
from sqlalchemy import create_engine

def load_counterparties(engine):
    counterparties = pd.read_excel("/home/share/Daily/blotter.xlsm", 'Counterparties')
    counterparties[['city', 'state']] = counterparties.Location.str.split(", ", expand=True)
    counterparties.drop(['Location', 'Valuation Contact4', 'Valuation Contact4 Email'], axis=1, inplace=True)
    counterparties.rename(columns ={'CODE': 'code',
                                    'DTC Number': 'dtc_number',
                                    'Email1': 'sales_email',
                                    'FIRM': 'name',
                                    'Phone': 'sales_phone',
                                    'Sales Contact': 'sales_contact',
                                    'Valuation Contact1': 'valuation_contact1',
                                    'Valuation Contact1 Email': 'valuation_email1',
                                    'Valuation Contact2': 'valuation_contact2',
                                    'Valuation Contact2 Email': 'valuation_email2',
                                    'Valuation Contact3': 'valuation_contact3',
                                    'Valuation Contact3 Email': 'valuation_email3',
                                    'Valuation Note': 'notes'}, inplace=True)
    counterparties.to_sql('counterparties', engine, if_exists='append', index=False)

def load_trades(engine):
    blotter = pd.read_excel("/home/share/Daily/blotter.xlsm", 'Bonds',
                            skiprows = [0, 1, 2, 3, 4])
    blotter.dropna(axis=0, subset=['Deal ID'], inplace=True)
    blotter = blotter.iloc[:,2:]
    blotter.drop(['Unnamed: %s' % (i,) for i in range(19, 28)] +
                 ['Comments', 'Acc Int $', 'Counterparty'], axis=1, inplace=True)
    blotter.rename(columns = {'Date': 'trade_date',
                              'Settle Date': 'settle_date',
                              'Strategy': 'folder',
                              'Custodian': 'custodian',
                              'Cash Account': 'cashaccount',
                              'CP Alias': 'cp_code',
                              'CUSIP': 'cusip',
                              'ISIN': 'isin',
                              'Description': 'description',
                              'Buy/Sell': 'buysell',
                              'Notional': 'faceamount',
                              'Price': 'price',
                              'Acc Int': 'accrued',
                              'Asset Class': 'asset_class',
                              'Deal ID': 'id'}, inplace=True)
    blotter.buysell = blotter.buysell.apply(lambda x: x=='Buy')
    blotter['action'] = 'NEW'
    blotter['cashaccount'] = 'V0NSCLMAMB'
    blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int')
    blotter.loc[blotter.asset_class == 'CLO','id'] = blotter.loc[blotter.asset_class == 'CLO','id'] + 5
    blotter.to_sql('bonds', engine, if_exists='append', index=False)
    return blotter


def bump_rev(filename):
    pattern = "([^r]*)(\srev(\d)|).pdf"
    begin, _, rev_number =  re.match(pattern, filename).groups()
    rev_number = int(rev_number) + 1 if rev_number else 1
    return "{0} rev{1}.pdf".format(begin, rev_number)

def simple_serialize(obj):
    return dumps({c.name: getattr(obj, c.name) for c in  obj.__table__.columns})

if __name__=="__main__":
    engine = create_engine('postgresql://dawn_user@debian/dawndb')
    load_trades(engine)