1 files changed, 55 insertions, 0 deletions
diff --git a/python/trade_dataclasses.py b/python/trade_dataclasses.py
index 4dcbec71..b54b9914 100644
--- a/python/trade_dataclasses.py
+++ b/python/trade_dataclasses.py
@@ -1131,3 +1131,58 @@ class IRSDeal(
         obj["State"] = "Valid"
         obj.update(d)
         return obj
+
+
+@dataclass
+class TrancheProduct(
+    Deal,
+    deal_type=DealType.TrancheProduct,
+    table_name="citco_tranche",
+    insert_ignore=("id", "dealid", "birth_date", "death_date"),
+):
+    underlying_security_id: str = field(metadata={"citco": "Underlying Security Id"})
+    attach: float
+    detach: float
+    birth_date: datetime.date = field(
+        init=False, metadata={"insert": False, "citco": "Birth_date"}
+    )
+    death_date: datetime.date = field(
+        init=False, metadata={"insert": False, "citco": "Death_date"}
+    )
+    instrument_type: str = field(default="CDS", metadata={"citco": "Instrument Type"})
+    underlying_id_source: str = field(
+        default="RED", metadata={"citco": "Underlying ID Source"}
+    )
+    committed: bool = field(default=False)
+    id: int = field(default=None, metadata={"insert": False})
+    dealid: str = field(
+        default=None, metadata={"insert": False, "citco": "Unique Identifier"}
+    )
+
+    def get_dealid(self, underlying_id, attach, detach, conn):
+        sql_str = "SELECT id, dealid, committed from citco_tranche where underlying_security_id = %s and attach = %s and detach = %s"
+        with conn.cursor() as c:
+            c.execute(sql_str, (underlying_id, attach, detach))
+            if results := c.fetchone():
+                return results.id, results.dealid, results.committed
+
+    def __post_init__(self):
+        sql_str = "SELECT issue_date, maturity FROM index_version LEFT JOIN index_Maturity USING (series, INDEX) WHERE tenor='5yr' AND redindexcode=%s;"
+        with self._conn.cursor() as c:
+            c.execute(sql_str, (self.underlying_security_id.removesuffix("_5"),))
+            (self.birth_date, self.death_date) = c.fetchone()
+            if results := self.get_dealid(
+                self.underlying_security_id, self.attach, self.detach, self._conn
+            ):
+                (self.id, self.dealid, self.committed) = results
+
+    def to_citco(self):
+        if not self.id:
+            self.stage()
+            self.commit()
+            (self.id, self.dealid, self.committed) = self.get_dealid(
+                self.underlying_security_id, self.attach, self.detach, self._conn
+            )
+
+        obj = self.serialize("citco")
+        obj["Command"] = "N"