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

from serenitas.utils import SerenitasFileHandler
import logging

fh = SerenitasFileHandler("mismatched_trades.log")
logger = logging.getLogger("notify_bowdst")
logger.addHandler(fh)
logger.setLevel(logging.WARNING)

conn = dbconn("dawndb")
cob = (datetime.date.today() - BDay(1)).date()
dates = [str((cob - datetime.timedelta(days=days))) for days in range(60)]
df = 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
    }
).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:
        logger.error(
            f"Mismatch balance {row.security_desc} {row.globeop_notional} :{row.db_notional} Trades Recently"
        )
        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,
    )