import pandas as pd
from sqlalchemy.sql import text
import os
import re
from pickle import dumps

def load_counterparties(engine):
    counterparties = pd.read_csv("/home/share/CorpCDOs/data/counterparties.csv")
    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 add_triggers(engine):
    engine.execute(text("""
    CREATE OR REPLACE FUNCTION mydealid()
    RETURNS TRIGGER AS $$
    BEGIN
       UPDATE bonds SET dealid = 'SC_' ||left(asset_class::text,3)||id WHERE bonds.id = NEW.id;
       RETURN NEW;
    END;
    $$ language plpgsql"""))
    engine.execute(text("""CREATE TRIGGER dealid
       AFTER INSERT ON bonds
       FOR EACH ROW
       EXECUTE PROCEDURE mydealid()"""))

def bump_rev(filename):
    (begin, end) = os.path.splitext(filename)
    m = re.search("\srev(\d)$", begin)
    rev_number = 1
    if m:
        rev_number += int(m.groups()[0])
    return "{0} rev{1}{2}".format(begin, rev_number, end)

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