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
import argparse
import numpy as np
import sys


def send_email(inaccurate_balances, accurate_balances, logger, conn, cob, args):
    for row in inaccurate_balances.itertuples():
        subject = f"ACTION REQUESTED: Missing Trades {row.Index} RED: {row.security_id}"
        try:
            last_date = accurate_balances.xs(row.Index, level="security_desc").index[-1]

        except IndexError:
            logger.warning(f"No matches for {row.security_desc}")
            continue
        buf = StringIO()
        output = pd.read_sql_query(
            "SELECT * FROM cds WHERE trade_date > %s and trade_date  <= %s "
            "AND fund = 'BOWDST' AND security_desc = %s "
            "AND orig_detach IS NULL AND orig_attach IS NULL ORDER BY trade_date DESC",
            conn,
            params=(last_date, cob, row.Index),
        )
        if not output[output.trade_date >= cob - BDay(1)].empty:
            logger.warning(
                f"Mismatch balance {row.Index} {row.globeop_notional} :{row.db_notional} Trades Recently"
            )
            continue
        output.to_csv(buf, index=False)
        attachments = [
            FileAttachment(name=f"{row.Index}.csv", content=buf.getvalue().encode())
        ]
        buf.close()
        msg = (
            "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.Index} RED: {row.security_id}.\n"
            f"Our notionals last matched on {last_date} for ${row.globeop_notional:,.2f}.\n\n"
            "Please see attached all the trades since we last matched. Please ensure they match your data.\n\n"
            "Thanks for your help!\n\n"
            "Flint"
        )
        em = ExchangeMessage()
        if args.globeop:
            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",
            )
        else:
            recipients = ("fyu@lmcg.com",)
            cc_recipients = ()

        em.send_email(
            subject,
            msg,
            to_recipients=recipients,
            cc_recipients=cc_recipients,
            attach=attachments,
        )


def main():
    parser = argparse.ArgumentParser(description="determine sender destination")
    parser.add_argument("--globeop", action="store_true", help="send to globeop")
    parser.add_argument(
        "--debug", "-d", action="store_true", default=False, help="log to the console"
    )

    args = parser.parse_args()

    if args.debug:
        logging.basicConfig()

    logger = logging.getLogger(__name__)
    if not logger.handlers:
        fh = SerenitasFileHandler("mismatched_trades.log")
        logger.addHandler(fh)
    logger.setLevel(logging.INFO)

    conn = dbconn("dawndb")
    cob = (datetime.date.today() - BDay(1)).date()
    dates = pd.date_range(end=cob, periods=50)
    df = pd.concat(
        [
            pd.read_sql_query(
                "SELECT *, notional * factor AS db_notional "
                "FROM list_cds_marks(%s, NULL, 'BOWDST')",
                conn,
                params=(d.date(),),
                index_col=["security_desc"],
            )
            for d in dates
        ],
        keys=dates,
    )

    mask = np.isclose(df.globeop_notional, df.db_notional, atol=1e-2, rtol=0.0)

    try:
        inaccurate_balances = df[~mask].loc[
            pd.Timestamp(cob),
            ["tenor", "security_id", "globeop_notional", "db_notional"],
        ]
    except KeyError:
        logger.info("All Balances Matching")
        sys.exit()
    accurate_balances = df[mask]

    send_email(inaccurate_balances, accurate_balances, logger, conn, cob, args)

    logger.info("Program executed.")


if __name__ == "__main__":
    main()