from sqlalchemy import create_engine, MetaData, select, Column, func, bindparam
from collections import defaultdict
import pandas as pd
import numpy as np
from bbg_helpers import init_bbg_session, retrieve_data, BBG_IP

engine = create_engine('postgresql://et_user@debian/ET')
meta = MetaData(bind=engine)
meta.reflect(only = ['bloomberg_corp_ref', 'bloomberg_corp', 'deal_indicative'])

deal_indicative =  meta.tables['deal_indicative']
bloomberg_corp_ref = meta.tables['bloomberg_corp_ref']
bloomberg_corp = meta.tables['bloomberg_corp']

s = select([Column('cusip'), Column('loanxid')]).\
    select_from(func.et_latestdealinfo(bindparam('dealname'))).where(Column('cusip')!=None)

# we build a dictionary with cusips as keys and values is a set of loanxids mapped to this cusip
result = select([deal_indicative.c.dealname]).execute()
d = defaultdict(set)
for r in result:
    result2 = engine.execute(s, dealname = r.dealname)
    for t in result2:
        d[t.cusip].add(t.loanxid)

clean_mapping = ((cusip, loanxid - {None}) for cusip, loanxid in d.items())

def f(s):
    if s:
        return "{%s}" % ",".join(s)
    else:
        return None

clean_mapping = {cusip: f(loanxid) for cusip, loanxid in clean_mapping}
mapping = pd.DataFrame.from_dict(clean_mapping, orient='index')
mapping.index.name = 'cusip'
mapping.columns = ['loanxid']
currentdata = pd.read_sql_query("select id_bb_unique, cusip from bloomberg_corp_ref",
                                engine, index_col='cusip')
mapping = mapping.ix[mapping.index.difference(currentdata.index)]

with init_bbg_session(BBG_IP) as session:
    all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", "ID_BB_UNIQUE",
                  "SECURITY_TYP", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT",
                  "ISSUER", "MATURITY","CPN","CPN_TYP", "CPN_FREQ","FLT_SPREAD",
                  "LIBOR_FLOOR","LN_CURRENT_MARGIN", "LN_TRANCHE_SIZE", "AMT_ISSUED",
                  "LN_COVENANT_LITE","SECOND_LIEN_INDICATOR","DEFAULTED", "DEFAULT_DATE",
                  "CALLED", "CALLED_DT", "PRICING_SOURCE"]
    securities = ["{0} Corp".format(cusip) for cusip in mapping.index]
    df = retrieve_data(session, securities, all_fields)
df = pd.DataFrame.from_dict(df, orient='index')
df.index = df.index.str.slice(0,9)
df.security = df.index.to_series()
df['loanxid'] = mapping.loanxid
df.dropna(subset=['ID_BB_UNIQUE'], inplace=True)
df.loc[df.LN_TRANCHE_SIZE.isnull(),'LN_TRANCHE_SIZE'] = df[df.LN_TRANCHE_SIZE.isnull()].AMT_ISSUED.values
df.drop_duplicates(subset='ID_BB_UNIQUE', inplace=True)
df.set_index('ID_BB_UNIQUE', inplace=True, drop=False)
currentdata.set_index('id_bb_unique', inplace=True)
df = df.ix[df.index.difference(currentdata.index)]
sql_colnames = [c.name for c in bloomberg_corp_ref.columns]
pd_colnames = ['ID_BB_UNIQUE', 'ID_CUSIP', 'ISSUER', 'MATURITY', 'CPN', 'CPN_TYP',
                'CPN_FREQ', 'FLT_SPREAD', 'LIBOR_FLOOR', 'LN_TRANCHE_SIZE', 'LN_COVENANT_LITE',
                'SECOND_LIEN_INDICATOR', 'SECURITY_TYP', 'ISSUE_DT', 'DEFAULTED',
                'DEFAULT_DATE', 'CALLED', 'CALLED_DT', 'LN_ISSUE_STATUS', 'loanxid']
to_insert = df.filter(pd_colnames)
to_insert.rename(columns={k: v for k, v in zip(pd_colnames, sql_colnames)}, inplace=True)
to_insert.to_sql("bloomberg_corp_ref", engine, if_exists='append', index=False)

pd_colnames = ['ID_BB_UNIQUE','LAST_UPDATE_DT','PX_LAST','LN_CURRENT_MARGIN',
               'AMT_OUTSTANDING','PRICING_SOURCE']
sql_colnames = [c.name for c in bloomberg_corp.columns]
to_insert2 = df.filter(pd_colnames)
to_insert2.rename(columns = {k: v for k, v in zip(pd_colnames, sql_colnames)}, inplace=True)
to_insert2.dropna(subset=['pricingdate'], inplace=True)
to_insert2.set_index(['id_bb_unique', 'pricingdate'], inplace=True)
to_insert2.to_sql("bloomberg_corp", engine, if_exists='append', index=True)