path: root/python/report_ops/utils.py

import datetime
import math
import logging
from dataclasses import dataclass
from typing import ClassVar
from exchangelib import HTMLBody
from tabulate import tabulate

from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from serenitas.analytics.dates import next_business_day

from .misc import (
    _recipients,
    _cc_recipients,
    _settlement_recipients,
    _valuation_recipients,
)

logger = logging.getLogger(__name__)


def next_business_days(date, offset):
    for i in range(offset + 1):
        date = next_business_day(date)
    return date


def round_up(n, decimals=0):
    multiplier = 10**decimals
    return math.ceil(n * multiplier) / multiplier


def notify_payment_settlements(date, fund, conn):
    end_date = next_business_days(date, 2)
    with conn.cursor() as c:
        c.execute(
            "SELECT * from payment_settlements WHERE settle_date BETWEEN %s AND %s AND fund= %s AND asset_class in ('SPOT', 'SWAPTION', 'TRANCHE') ORDER BY settle_date asc",
            (date, end_date, fund),
        )
        for row in c:
            d = row._asdict()
            d["settlement_amount"] = d["payment_amount"]
            PaymentMonitor.stage(d)
    PaymentMonitor.email(fund)
    PaymentMonitor._staging_queue.clear()


def notify_fx_hedge(date, fund, conn):
    with conn.cursor() as c:
        c.execute(
            "SELECT * from fcm_moneyline LEFT JOIN accounts2 ON account=cash_account WHERE date=%s AND currency='EUR' AND fund=%s AND abs(current_excess_deficit) > 1000000",
            (date, fund),
        )
        for row in c:
            d = row._asdict()
            d["amount"] = d["current_excess_deficit"]
            d["category"] = "FCM"
            FxHedge.stage(d)
    FxHedge.email(fund)
    FxHedge._staging_queue.clear()


def check_cleared_cds(date, fund, conn):
    _tolerance = {"IG": 0.10, "HY": 0.20, "EU": 0.20, "XO": 0.30}
    with conn.cursor() as c:
        c.execute(
            "SELECT * FROM list_cds_marks(%s, NULL, %s), fx WHERE date=%s AND abs((notional*factor) - globeop_notional) < 100;",
            (date, fund, date),
        )
        for row in c:
            d = row._asdict()
            d["serenitas_quote"] = d["price"]
            match d["index"]:
                case "XO" | "EU":
                    d["admin_quote"] = 100 - (
                        ((d["globeop_nav"] - d["accrued"]) / d["eurusd"])
                        / (d["globeop_notional"] / 100)
                    )
                case _:
                    d["admin_quote"] = 100 - (
                        (d["globeop_nav"] - d["accrued"])
                        / (d["globeop_notional"] / 100)
                    )
            d["difference"] = abs(d["price"] - d["admin_quote"])
            if d["difference"] > _tolerance[d["index"]]:
                CDXQuoteMonitor.stage(d)
        CDXQuoteMonitor.email(fund)
        CDXQuoteMonitor._staging_queue.clear()


@dataclass
class Monitor:
    date: datetime.date
    headers: ClassVar = ()
    num_format: ClassVar = []
    _staging_queue: ClassVar[list] = []
    _em: ClassVar = ExchangeMessage()

    def __init_subclass__(cls, headers, num_format=[]):
        cls.headers = headers
        cls.num_format = num_format

    @classmethod
    def stage(cls, d: dict):
        cls._staging_queue.append(list(d[key] for key in cls.headers))

    @classmethod
    def format(cls):
        for line in cls._staging_queue:
            for f, i in cls.num_format:
                line[i] = f.format(line[i])

    @classmethod
    def to_tabulate(cls):
        cls.format()
        t = tabulate(
            cls._staging_queue,
            headers=cls.headers,
            tablefmt="unsafehtml",
        )
        return t

    @classmethod
    def clear(cls):
        cls._staging_queue.clear()


class GFSMonitor(
    Monitor,
    headers=(
        "date",
        "portfolio",
        "amount",
        "currency",
    ),
    num_format=[("{0:,.2f}", 2)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"GFS Helper Strategy Issue: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Good morning,<br><br>Could you please help us with the below transfer breaks:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_recipients[fund],
            cc_recipients=_cc_recipients[fund],
        )


class BondMarkMonitor(
    Monitor,
    headers=(
        "periodenddate",
        "invid",
        "geneva_identifier",
        "pricelist",
        "knowledgedate",
    ),
    num_format=[],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"Incorrectly marked trades: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Good morning,<br><br>Could you please use Manager marks for the below trades:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_valuation_recipients[fund],
            cc_recipients=_cc_recipients[fund],
        )


class CDXQuoteMonitor(
    Monitor,
    headers=(
        "security_desc",
        "security_id",
        "maturity",
        "admin_quote",
        "serenitas_quote",
        "difference",
    ),
    num_format=[("{0:,.2f}", 3), ("{0:,.2f}", 4), ("{0:,.2f}", 5)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"CDX Quote Outside of our Tolerance: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Good morning,<br><br>Could you please help us with the below cleared CDX quotes outside of our tolerance:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_valuation_recipients[fund],
            cc_recipients=_cc_recipients[fund],
        )


class CDXNotionalMonitor(
    Monitor,
    headers=(
        "security_desc",
        "security_id",
        "maturity",
        "admin_notional",
        "serenitas_notional",
        "difference",
    ),
    num_format=[("{0:,.2f}", 3), ("{0:,.2f}", 4), ("{0:,.2f}", 5)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"CDX Notional Mismatches: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Good morning,<br><br>Mismatched cleared cds notional mismatches below:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_cc_recipients[fund],
        )


class SettlementMonitor(
    Monitor,
    headers=("date", "account", "currency", "projected_balance"),
    num_format=[("{0:,.2f}", 3)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"*ACTION REQUESTED* Projected Overdraft: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Hello,<br><br>We see a projected overdraft on the below dates. Please move to cover:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_recipients[fund],
            cc_recipients=_cc_recipients[fund],
        )


class PaymentMonitor(
    Monitor,
    headers=(
        "settle_date",
        "account",
        "name",
        "cp_code",
        "settlement_amount",
        "currency",
        "asset_class",
        "ids",
    ),
    num_format=[("{0:,.2f}", 4)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"Projected Settlements: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Hello,<br><br>We see the below settlements in the next two days (Positive=Receive, Negative=Pay):<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=_settlement_recipients[fund],
            cc_recipients=_cc_recipients[fund],
        )


class FxHedge(
    Monitor,
    headers=(
        "date",
        "account",
        "amount",
        "currency",
        "fund",
        "category",
    ),
    num_format=[("{0:,.2f}", 2)],
):
    @classmethod
    def email(cls, fund):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"Projected Hedges: {fund}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  Hello,<br><br>Here are the positions we need to hedge:<br><br>{cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=("fyu@lmcg.com",),
        )


class QuantifiMonitor(
    Monitor,
    headers=(
        "uploadtime",
        "filename",
        "errors",
        "warnings",
        "successes",
        "total",
    ),
    num_format=[],
):
    @classmethod
    def email(cls, filename, errors, buf):
        if not cls._staging_queue:
            return
        cls._em.send_email(
            f"Quantifi Report: {filename} {'**Errors**' if errors else ''}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  {cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=("fyu@lmcg.com",),
            attach=[FileAttachment(name=filename + ".xml", content=buf)],
        )


class CitcoMonitor(
    Monitor,
    headers=(
        "process_date",
        "submit_date",
        "identifier_type",
        "citco_id",
        "serenitas_id",
        "id",
    ),
    num_format=[],
):
    @classmethod
    def email(cls, filename, buf):
        if not cls._staging_queue:
            return
        recipients = _recipients["NY_CREW"]
        action_requested = ""
        if cls.check_csm():
            recipients += ("SYamamiya@citco.com", "DataOpsTC@citco.com")
            action_requested += "**Action Requested, TradeID Failed**"
        cls._em.send_email(
            f"(CITCO) UPLOAD REPORT: {filename} {action_requested}",
            HTMLBody(
                f"""
<html>
  <head>
    <style>
      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
      th, td {{ padding: 5px; }}
    </style>
  </head>
  <body>
  {cls.to_tabulate()}
  </body>
</html>"""
            ),
            to_recipients=recipients,
            attach=[FileAttachment(name=filename, content=buf)],
        )

    @classmethod
    def check_csm(cls):
        for line in cls._staging_queue:
            if "TID NOT FOUND" in line[3]:
                return True