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


def difference(df):
    if ("db_mv" in df.columns) and ("db_notional" in df.columns):
        df["mv_difference"] = df["db_mv"] - df["admin_mv"]
        df["notional_difference"] = df["db_notional"] - df["admin_notional"]
    elif "db_mv" in df.columns:
        df["mv_difference"] = df["db_mv"] - df["admin_mv"]
    elif "db_notional" in df.columns:
        df["notional_difference"] = df["db_notional"] - df["admin_notional"]
    return df


def recon(hierarchy_file, date):
    df = pd.read_excel(hierarchy_file)
    security_balance = df[df["Asset Type"] == "FIXED INCOME SECURITIES"][
        "Base Market Value"
    ].sum()
    bowd_bond_trades = df[df["CUSIP"].notnull()]
    bond_asset_classes = ["Subprime", "CRT", "CLO"]

    for asset in bond_asset_classes:
        db_bond_trades = pd.read_sql_query(
            f"select * from risk_positions(%s, %s, 'BOWDST')",
            dawndb,
            params=(date, asset),
        )

        bond_trades = 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",
            ]
        ]
        bond_trades["db_notional"] = bond_trades["Shares/Par"] * bond_trades["factor"]
        bond_trades.rename(
            columns={
                "usd_market_value": "db_mv",
                "Shares/Par": "admin_notional",
                "Base Market Value": "admin_mv",
            },
            inplace=True,
        )

    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 admin_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_mv, globeop_nav as admin_mv 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,),
    )

    kinds = [bond_trades, tranche_trades, cdx_trades, cdx_swaption_trades]
    names = ["bond_trades", "tranche_trades", "cdx_trades", "cdx_swaption_trades"]
    for kind, name in zip(kinds, names):
        difference(kind).to_csv(f"/home/serenitas/flint/{name}_{date}.csv")


if __name__ == "__main__":
    dawndb = dbconn("dawndb")
    hierarchy_file = "/home/serenitas/flint/rec.xlsx"
    date = "2021-02-28"
    recon(hierarchy_file, date)