from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from exchangelib import HTMLBody
import pandas as pd
from io import StringIO
import datetime
import argparse

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "--trade_date", type=datetime.date.fromisoformat, default=datetime.date.today()
    )
    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",
    }

    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;",
        con=dawndb,
        params=(args.trade_date,),
    )
    with dawndb.cursor() as d:
        for row in df.itertuples():
            d.execute(
                f"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 = ("fyu@lmcg.com", "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"])
                .round(2)
            )
            bondtrade.to_csv(buf, index=False)
            body = (
                f"<p>For {row.identifier} please allocate :<br><br><ul>"
                + "".join(allocations)
                + "</ul><br><br>Trade Details are attached in csv."
                + "<br><br><br>Thanks,<br>Flint<br></p><br>"
                + bondtrade.to_html(index=False)
            )
            em.send_email(
                subject=subject,
                to_recipients=row.allocation_contacts
                if row.allocation_contacts
                else ("fyu@lmcg.com,"),
                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()