import pandas as pd
from serenitas.utils.db import dbconn

dawndb = dbconn("dawndb")

df = pd.read_excel("/home/serenitas/flint/rec.xlsx")

security_balance = df[df["Asset Type"] == "FIXED INCOME SECURITIES"][
    "Base Market Value"
].sum()
print(f"The current security balance is {security_balance}")

bowd_bond_trades = df[df["CUSIP"].notnull()]

asset_classes = ["Subprime", "CRT", "CLO"]

for asset in asset_classes:
    db_bond_trades = pd.read_sql_query(
        f"select * from risk_positions('2021-02-26', %s, 'BOWDST')",
        dawndb,
        params=(asset,),
    )

    new_df = bowd_bond_trades.merge(
        db_bond_trades, left_on="Mellon Security ID", right_on="identifier", how="right"
    )[
        [
            "description",
            "identifier",
            "notional",
            "factor",
            "Shares/Par",
            "Base Market Value",
            "usd_market_value",
        ]
    ]
    new_df["db_notional"] = new_df["Shares/Par"] * new_df["factor"]
#     print(new_df)

date = "2021-02-28"

tranche_trades = pd.read_sql_query(
    f"select security_desc, maturity, orig_attach, orig_detach, sum(notional * tranche_factor) as db_notional, sum(admin_notional) as admin_notional, sum(serenitas_clean_nav) as db_mv, sum(admin_clean_nav) as bowd_mv from tranche_risk_bowdst where date=%s group by security_desc, maturity, orig_attach, orig_detach ;",
    dawndb,
    params=(date,),
)

cdx_trades = pd.read_sql_query(
    f"select security_id, security_desc, index, series, version, maturity, globeop_notional as admin_notional, notional * factor as db_notional, clean_nav as db_nav, globeop_nav as admin_nav from list_cds_marks(%s, null, 'BOWDST')",
    dawndb,
    params=(date,),
)

cdx_swaption_trades = pd.read_sql_query(
    f"select security_id, option_type, strike, expiration_date, sum(serenitas_nav) as db_mv, sum(globeop_nav) as admin_mv from list_swaption_positions_and_risks(%s, 'BOWDST') group by security_id, option_type, strike, expiration_date;",
    dawndb,
    params=(date,),
)