1 files changed, 112 insertions, 0 deletions
diff --git a/python/bbh_settlements_email.py b/python/bbh_settlements_email.py
new file mode 100644
index 00000000..30264eec
--- /dev/null
+++ b/python/bbh_settlements_email.py
@@ -0,0 +1,112 @@
+import datetime
+from exchangelib import HTMLBody
+
+from serenitas.utils.db import dbconn
+from serenitas.analytics.dates import bus_day
+
+from report_ops.utils import Monitor
+
+
+cp_contacts = {
+    "GOLDNY": (
+        "Patrick.Bousky@gs.com",
+        "CreditDerivativesSet@ny.email.gs.com",
+    ),
+    "MSCSNY": (
+        "nafidset@morganstanley.com",
+        "Farzad.Taheri@morganstanley.com",
+    ),
+}
+
+
+class BBHSettlementsEmail(
+    Monitor,
+    headers=(
+        "settle_date",
+        "serenitas_id",
+        "counterparty_id",
+        "counterparty",
+        "payment_amount",
+        "currency",
+        "asset_class",
+    ),
+    num_format=[("{0:,.2f}", 4)],
+):
+    @classmethod
+    def email(cls, cp_name, cp_contacts, settlement_date, net_amount, currency):
+        if not cls._staging_queue:
+            return
+        cls._em.send_email(
+            f"{cp_name} Settlements: Brinker Destinations Fund {settlement_date}",
+            HTMLBody(
+                f"""
+<html>
+  <head>
+    <style>
+      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
+      th, td {{ padding: 5px; }}
+    </style>
+  </head>
+  <body>
+  Good morning Team<br><br>We're seeing the following settlements for the Brinker Destinations Fund. Please confirm you will {'receive' if net_amount < 0 else 'pay'} net {net_amount:,.2f} {currency}:<br><br>{cls.to_tabulate()}
+  </body>
+</html>"""
+            ),
+            to_recipients=cp_contacts,
+            cc_recipients=("nyops@lmcg.com",),
+        )
+
+
+def check_trade_settlements(date, fund, conn):
+    with conn.cursor() as c, conn.cursor() as b:
+        c.execute(
+            "SELECT name, cp_code, asset_class, currency, ids FROM payment_settlements ps WHERE settle_date =%s AND fund=%s AND asset_class NOT IN ('BOND');",
+            (date, fund),
+        )
+        for row in c:
+            payment_details = {
+                "settle_date": date,
+                "counterparty": row.cp_code,
+                "currency": row.currency,
+                "asset_class": row.asset_class,
+            }
+            net_amount = 0
+            match row.asset_class:
+                case "TRANCHE_CF":
+                    sql_query = "SELECT cds.dealid as serenitas_id, cds.cpty_id as counterparty_id, coalesce(principal,0) + coalesce(accrued,0) as payment_amount FROM tranche_cashflows LEFT JOIN cds ON tranche_id=cds.id WHERE tranche_id in %s AND date=%s"
+                case "TRANCHE":
+                    sql_query = "SELECT ct.dealid as serenitas_id, c.cpty_id as counterparty_id, ct.upfront as payment_amount FROM cds_trades ct LEFT JOIN cds c USING (id) WHERE ct.id in %s AND ct.settle_date = %s"
+
+            b.execute(
+                sql_query,
+                (
+                    tuple(row.ids),
+                    date,
+                ),
+            )
+            for sett in b:
+                d = sett._asdict() | payment_details
+                BBHSettlementsEmail.stage(d)
+                net_amount += d["payment_amount"]
+
+            BBHSettlementsEmail.email(
+                row.name, cp_contacts[row.cp_code], date, net_amount, row.currency
+            )
+            BBHSettlementsEmail.clear()
+
+
+if __name__ == "__main__":
+    import argparse
+
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "settlement_date",
+        nargs="?",
+        type=datetime.date.fromisoformat,
+        default=datetime.date.today() + bus_day * 2,
+        help="settlement date",
+    )
+    args = parser.parse_args()
+    conn = dbconn("dawndb")
+
+    check_trade_settlements(args.settlement_date, "BRINKER", conn)