import pandas as pd
from serenitas.utils.db import dbconn
import argparse
from serenitas.utils.exchange import ExchangeMessage
from exchangelib import FileAttachment
from io import StringIO
from serenitas.utils.env import DAILY_DIR


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 sums(df):
    if ("db_mv" in df.columns) and ("db_notional" in df.columns):
        return df[["db_mv", "admin_mv", "db_notional", "admin_notional"]].sum()
    elif "db_mv" in df.columns:
        return df[["db_mv", "admin_mv"]].sum()
    elif "db_notional" in df.columns:
        return df[["db_notional", "admin_notional"]].sum()


def recon(hierarchy_file, date):
    df = pd.read_excel(hierarchy_file)
    bowd_bond_trades = df[df["CUSIP"].notnull()]
    bond_asset_classes = ["Subprime", "CRT", "CLO"]
    bond_trades_combined = []
    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["notional"] * bond_trades["factor"]
        bond_trades.rename(
            columns={
                "usd_market_value": "db_mv",
                "Shares/Par": "admin_notional",
                "Base Market Value": "admin_mv",
            },
            inplace=True,
        )
        bond_trades_combined.append(bond_trades)
    bond_trades_combined = pd.concat(bond_trades_combined)

    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_combined, tranche_trades, cdx_trades, cdx_swaption_trades]
    names = ["bond_trades", "tranche_trades", "cdx_trades", "cdx_swaption_trades"]
    message = ""
    em = ExchangeMessage()
    attachments = []
    for kind, name in zip(kinds, names):
        # difference(kind).to_csv(f"/home/serenitas/flint/{name}_{date}.csv")
        buf = StringIO()
        difference(kind.round(decimals=2)).to_csv(buf)
        attachments.append(
            FileAttachment(name=f"{name}_{date}.csv", content=buf.getvalue().encode())
        )
        pd.set_option("display.float_format", lambda x: "%.2f" % x)
        message += f"\n{name}: {pd.DataFrame(sums(kind), columns=['sums'])}"

        # print(f"{name}: {sums(kind)}"
    em.send_email(
        subject="Notional Totals",
        body=message,
        to_recipients=("fyu@lmcg.com",),
        attach=attachments,
    )


parser = argparse.ArgumentParser()
parser.add_argument("end_date")
args = parser.parse_args()

if __name__ == "__main__":
    dawndb = dbconn("dawndb")
    date = args.end_date
    hierarchy_file = DAILY_DIR / date / "hierarchy.xls"
    recon(hierarchy_file, date)