import pandas as pd
from bbg_helpers import init_bbg_session, BBG_IP, retrieve_data
from db import dbengine
indices = ["CDX HY CDSI S19 5Y", "ITRX XOVER CDSI S25 5Y",
           "CDX HY CDSI S15 5Y", "ITRX XOVER CDSI S20 5Y",
           "CDX HY CDSI S25 5Y",
           "ITRX EUR CDSI S17 10Y"]
with init_bbg_session(BBG_IP) as session:
    d = retrieve_data(session, [f"{i} Corp" for i in indices], "CDS_INDEX_DEFAULT_INFORMATION")

df = pd.concat([v["CDS_INDEX_DEFAULT_INFORMATION"] for v in d.values()],
               ignore_index=True)
df['Company ID'] = df['Company ID'].astype('int')
df = df[~df.duplicated(['Company ID'])]
breakpoint()
df = df[['Company ID', 'Auction Date', 'Event Date', 'CDS Recovery Rate']]
df.columns = ['id', 'auction_date', 'event_date', 'recovery']
serenitas_engine = dbengine('serenitasdb')
df.to_sql('defaulted', serenitas_engine, if_exists='append', index=False)