from serenitas.utils.db import dbconn
import pandas as pd
from pandas.tseries.offsets import BDay
import datetime
from io import StringIO
from exchangelib import FileAttachment
from serenitas.utils.exchange import ExchangeMessage

conn = dbconn("dawndb")
cob = (datetime.date.today() - BDay(1)).date()
dates = [str((cob - datetime.timedelta(days=days))) for days in range(60)]
# dates = [str((cob - BDay(days)).date()) for days in range(60)]
df_rates = pd.concat(
    {
        date: pd.read_sql_query(
            f"""SELECT *, notional * factor as db_notional FROM list_cds_marks( %(date)s , NULL, 'BOWDST');""",
            conn,
            params={"date": str(date)},
        )
        for date in dates
    }
)
df = df_rates.droplevel(level=1)
data = df[df.index == str(cob)][
    ["tenor", "security_desc", "security_id", "globeop_notional", "db_notional"]
]
inaccurate_balances = data[data["db_notional"] != data["globeop_notional"]]


for row in inaccurate_balances.itertuples():
    subject = (
        f"ACTION REQUESTED: Missing Trades {row.security_desc} RED: {row.security_id}"
    )

    new_df = df[df["security_desc"] == row.security_desc]
    try:
        data = new_df[new_df["globeop_notional"] == new_df["db_notional"]].iloc[0, :]
        security_desc = data.security_desc
        date = data.name
        globeop_notional = data.globeop_notional
    except IndexError:
        print("No matches")
        continue

    buf = StringIO()
    csv = pd.read_sql_query(
        f"""SELECT * FROM cds WHERE trade_date > %(date)s and fund = 'BOWDST' and security_desc = %(security_desc)s and orig_detach is null and orig_attach is null order by trade_date desc""",
        conn,
        params={"date": date, "security_desc": security_desc},
    )
    if len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index) != 0:
        print(len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index))
        continue
    csv.to_csv(buf)
    attachments = [
        FileAttachment(name=f"{security_desc}.csv", content=buf.getvalue().encode())
    ]
    buf.close()
    msg = (
        f"Good morning!\n\n"
        f"We notice a difference in our notional ({row.db_notional:,.2f}) versus yours ({row.globeop_notional:,.2f}) "
        f"for security {row.security_desc} RED: {row.security_id}.\n"
        f"Our notionals last matched on {date} for ${globeop_notional:,.2f}.\n\n"
        f"Please see attached all the trades since we last matched. Please ensure they match your data.\n\n"
        f"Thanks for your help!\n\n"
        f"Flint"
    )
    em = ExchangeMessage()
    em.send_email(
        subject,
        msg,
        to_recipients=("fyu@lmcg.com",),
        # to_recipients=(
        #     "caagtradecapture@bnymellon.com",
        #     "hm-operations@bnymellon.com",
        #     "caagprim@bnymellon.com",
        #     "julie.picariello@bnymellon.com",
        #     "sa1futures.optionsprocessing@bnymellon.com"
        # ),
        # cc_recipients=("fyu@lmcg.com", "Bowdoin-Ops@LMCG.com",),
        attach=attachments,
    )