from bbg_helpers import init_bbg_session, BBG_IP, retrieve_data
import datetime
from utils.db import dbconn

securities = {}
for series in range(9, 32):
    for index_type in ["IG", "HY"]:
        for t in [3, 5, 7, 10]:
            securities[f"CDX {index_type} CDSI S{series} {t}Y Corp"] = (
                series,
                index_type,
                f"{t}yr",
            )
for series in range(10, 31):
    for index_type in ["EUR", "XOVER"]:
        for t in [3, 5, 7, 10]:
            securities[f"ITRX {index_type} CDSI S{series} {t}Y Corp"] = (
                series,
                index_type,
                f"{t}yr",
            )

place_holders = ",".join(["%s"] * 7)
sql_str_price = (
    "INSERT INTO index_quotes_pre"
    "(date, index, series, version, tenor, close_price, source)"
    f" VALUES({place_holders}) ON CONFLICT DO NOTHING"
)
sql_str_spread = (
    "INSERT INTO index_quotes_pre"
    "(date, index, series, version, tenor, close_spread, source)"
    f" VALUES({place_holders}) ON CONFLICT DO NOTHING"
)

start_date = datetime.date.today() - datetime.timedelta(days=7)  # one weeek of overlap
conn = dbconn("serenitasdb")
with init_bbg_session(BBG_IP) as session:
    d = retrieve_data(session, securities.keys(), fields=["TICKER", "VERSION"])
    ticker_mapping = {
        v["TICKER"]: securities[k] + (v["VERSION"],) for k, v in d.items() if v
    }
    for pcs in ["MSG1", "CMAN", "CBGN"]:
        securities = [f"{e['TICKER']} {pcs} Curncy" for e in d.values() if e]
        d2 = retrieve_data(
            session, securities, fields=["PX_LAST"], start_date=start_date
        )
        with conn.cursor() as c:
            for k, v in d2.items():
                ticker = k.split()[0]
                series, index, tenor, version = ticker_mapping[ticker]
                if index == "EUR":
                    index = "EU"
                if index == "XOVER":
                    index = "XO"
                if index == "HY":
                    sql_str = sql_str_price
                else:
                    sql_str = sql_str_spread
                if not v.empty:
                    c.executemany(
                        sql_str,
                        [
                            (t[0], index, series, version, tenor, t[1], pcs)
                            for t in v.itertuples()
                        ],
                    )
        conn.commit()