from sqlalchemy import create_engine
import pandas as pd
from db import conn
import numpy as np
from bbg_helpers import init_bbg_session, retreive_data, process_msgs

engine = create_engine('postgresql://et_user@debian/ET')

session = init_bbg_session('192.168.1.108', 8194)

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"]

# append securities to request
currentdata = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip " \
                                "from bloomberg_corp_ref", engine, index_col='cusip')

mapping = pd.read_csv("/home/share/CorpCDOs/data/bbg_loanxid.csv", index_col=0)
mapping = mapping.ix[mapping.index.difference(currentdata.index)]

securities = ["{0} Corp".format(cusip) for cusip in mapping.index]
data = retreive_data(session, securities, all_fields)
df = process_msgs(data, all_fields)
df.security = df.security.str.slice(0,9)
df.set_index('security', inplace=True)
df['loanxid'] = mapping['loanxid']
df.reset_index(inplace=True)

sqlstr = "INSERT INTO bloomberg_corp_ref VALUES({0})".format(",".join(["%s"]*20))
with conn.cursor() as c:
    for i in range(df.shape[0]):
        issue_size = df.iloc[i]['LN_TRANCHE_SIZE']
        if np.isnan(issue_size):
            issue_size = df.iloc[i]['AMT_ISSUED']
        if np.isnan(issue_size):
            issue_size = None
        try:
            c.execute(sqlstr,
                      (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['ID_CUSIP'], df.iloc[i]['ISSUER'],
                       df.iloc[i]['MATURITY'], df.iloc[i]['CPN'], df.iloc[i]['CPN_TYP'],
                       df.iloc[i]['CPN_FREQ'], df.iloc[i]['FLT_SPREAD'], df.iloc[i]['LIBOR_FLOOR'],
                       issue_size, df.iloc[i]["LN_COVENANT_LITE"],  df.iloc[i]["SECOND_LIEN_INDICATOR"],
                       df.iloc[i]["SECURITY_TYP"], df.iloc[i]["ISSUE_DT"], df.iloc[i]["DEFAULTED"],
                       df.iloc[i]["DEFAULT_DATE"], df.iloc[i]["CALLED"], df.iloc[i]["CALLED_DT"],
                       df.iloc[i]["LN_ISSUE_STATUS"], [df.iloc[i]['loanxid']]))
            conn.commit()
        except IntegrityError:
            conn.rollback()

sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)"
with conn.cursor() as c:
    for i in range(df.shape[0]):
        margin = df.iloc[i]['LN_CURRENT_MARGIN']
        if np.isnan(margin):
            margin = None
        amt_outstanding = df.iloc[i]['AMT_OUTSTANDING']
        if np.isnan(amt_outstanding):
            amt_outstanding = None
        try:
            c.execute(sqlstr, (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['LAST_UPDATE_DT'],
                               df.iloc[i]['PX_LAST'], margin, amt_outstanding,
                               df.iloc[i]['PRICING_SOURCE']))
            conn.commit()
        except IntegrityError:
            conn.rollback()

conn.close()