1 files changed, 51 insertions, 1 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql
index 2722d877..df6dec39 100644
--- a/sql/serenitasdb.sql
+++ b/sql/serenitasdb.sql
@@ -830,7 +830,8 @@ FROM tranche_risk
 JOIN tranche_quotes ON tranche_id=tranche_quotes.id;
 
 
-CREATE TABLE tranche_risk(
+-- deprecated, renamed to tranche_risk_prev
+CREATE TABLE tranche_risk_prev(
      id serial PRIMARY KEY,
      tranche_id integer UNIQUE REFERENCES tranche_quotes(id) ON DELETE CASCADE,
      index_price float,
@@ -852,6 +853,55 @@ CREATE TABLE tranche_risk(
 
 GRANT ALL ON tranche_risk to serenitas_user;
 
+CREATE TABLE tranche_risk_index(
+     id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+     quoteset integer UNIQUE NOT NULL REFERENCES tranche_quotes_ref ON DELETE CASCADE,
+     price float,
+     basis float,
+     expected_loss float,
+     duration float,
+     theta float);
+
+CREATE TABLE tranche_risk_tranches(
+     id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+     quoteset integer NOT NULL REFERENCES tranche_quotes_ref ON DELETE CASCASDE,
+     tranche_id integer UNIQUE NOT NULL REFERENCES tranche_quotes_tranches(id) ON DELETE CASCADE,
+     corr_at_detach float,
+     delta float,
+     fwd_delta float,
+     gamma float,
+     theta float,
+     corr01 float,
+     duration float,
+     spread float,
+     expected_loss float,
+     quote_price float,
+     calibrated_price float);
+
+CREATE OR REPLACE VIEW tranche_risk AS (
+       SELECT t1.id,
+              t1.tranche_id,
+              t2.price AS index_price,
+              t2.basis AS index_basis,
+              t2.expected_loss AS index_expected_loss,
+              t2.duration AS index_duration,
+              t2.theta AS index_theta,
+              t1.corr_at_detach,
+              t1.delta,
+              t1.fwd_delta,
+              t1.gamma,
+              t1.theta,
+              t1.corr01,
+              t1.duration,
+              t1.spread,
+              t1.expected_loss,
+              t1.quote_price,
+              t1.calibrated_price
+        FROM tranche_risk_tranches t1
+        LEFT JOIN tranche_risk_index t2 USING (quoteset)
+);
+
+
 CREATE TABLE markit_tranche_risk(
      id integer GENERATED BY DEFAULT PRIMARY KEY,
      tranche_id integer UNIQUE REFERENCES markit_tranche_quotes(id) ON DELETE CASCADE,