import datetime
import argparse

import pandas as pd
from pandas.tseries.offsets import BDay
from io import StringIO
from exchangelib import HTMLBody

from serenitas.utils.db import dawn_engine, dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "--trade_date",
        type=datetime.date.fromisoformat,
        default=(datetime.date.today() - BDay(1)).date(),
    )
    args = parser.parse_args()
    dawndb = dbconn("dawndb")

    alert = {
        "SERCGMAST": "Serenitas Credit Gamma Master Fund",
        "BOWDST": "Bowdoin St. (ALERT acronym: LMUNDER, Account: SER003)",
        "BRINKER": "Brinker Destinations Multi-Strategy Alternatives - LMCG",
        "ISOSEL": "ISO Selene (ALERT acronym: LMUNDER, Account: SER004)",
    }

    df = pd.read_sql_query(
        "SELECT bonds.id as tradeid, trade_date, settle_date, CASE WHEN buysell THEN 'Buy' ELSE 'Sell' end as buysell, identifier, description , faceamount AS notional, price, accrued, principal_payment + accrued_payment as net_amount, name as counterparty, allocation_contacts FROM bonds LEFT JOIN counterparties on cp_code=code WHERE trade_date >= %s and not EMAILED and allocated AND ctm_code is NULL;",
        con=dawn_engine,
        params=(args.trade_date,),
    )
    with dawndb.cursor() as d:
        formatters = {
            "net_amount": "{:.2f}".format,
            "price": "{:.4f}".format,
            "accrued": "{:.8f}".format,
            "notional": "{:.0f}".format,
        }

        for row in df.itertuples():
            d.execute(
                "SELECT tradeid, notional, fund FROM bond_allocation "
                "LEFT JOIN accounts a  USING (code) WHERE account_type='Cash' "
                "AND active AND tradeid=%s;",
                (row.tradeid,),
            )
            em = ExchangeMessage()
            subject = (
                f"LMCG - Trade Allocations - TD {row.trade_date} - {row.counterparty}"
            )
            cc_recipients = ("NYOps@lmcg.com",)
            allocations = [
                "<li>" + f" {row.notional:,.0f} to {alert[row.fund]}" + "</li>"
                for row in d
            ]
            buf = StringIO()
            bondtrade = df[df["tradeid"] == row.tradeid].drop(
                columns=["allocation_contacts", "tradeid"]
            )
            bondtrade.to_csv(buf, index=False)
            body = (
                f"<p>For {row.identifier} please allocate :<br><br><ul>"
                + "".join(allocations)
                + f"</ul><br><i>Please confirm net amount of {row.net_amount:.2f}.</i>"
                + "<br><br><br>Thanks,<br>Flint<br></p><br>"
                + bondtrade.to_html(index=False, formatters=formatters)
            )
            if not row.allocation_contacts:
                raise Exception(
                    f"There are no contacts for {row.trade_date} - {row.counterparty}"
                )
            em.send_email(
                subject=subject,
                to_recipients=row.allocation_contacts,
                cc_recipients=cc_recipients,
                reply_to=("NYOps@lmcg.com",),
                body=HTMLBody(body),
                attach=[
                    FileAttachment(
                        name=f"{row.trade_date}-{row.counterparty}-{row.identifier}.csv",
                        content=buf.getvalue().encode(),
                    )
                ],
            )
            d.execute(
                "UPDATE bonds SET emailed = True WHERE id = %(tradeid)s",
                {"tradeid": row.tradeid},
            )
    dawndb.commit()