path: root/sql/serenitasdb.sql

-- -*- mode: sql; sql-product: postgres; -*-
CREATE TABLE tranche_data (
       QuoteDate date,
       TrancheId integer,
       BasketId integer,
       Maturity	 date,
       Tenor varchar(4),
       RefBasketPrice float,
       BPS varchar(1),
       BasketDuration float,
       TQC varchar(1),
       TrancheDuration float,
       TrancheDelta float,
       CorrAtDetachment float,
       Basis float,
       QuoteSource varchar(2),
       Index varchar(4),
       Series smallint,
       Upfront float,
       Running float,
       Attach smallint,
       Detach smallint,
       IndexFactor float,
       CumulativeLoss float
);

GRANT ALL ON tranche_data TO serenitas_user;

CREATE TABLE index_version(
       BasketID serial,
       Index index_type,
       Series smallint,
       Version smallint,
       IndexFactor float,
       CumulativeLoss float,
       lastdate date,
       redindexcode text,
       PRIMARY KEY(BasketID)
);

GRANT ALL ON index_version TO serenitas_users;

CREATE TABLE index_maturity(
       Index index_type,
       series smallint,
       tenor tenor,
       maturity date,
       coupon integer,
       issue_date date,
       PRIMARY KEY(index, series, tenor)
);

CREATE OR REPLACE VIEW index_desc AS
 SELECT b.*, a.tenor, a.maturity, a.coupon, a.issue_date
   FROM index_maturity a
   JOIN index_version b USING (index, series);

GRANT ALL ON index_maturity TO serenitas_user;
GRANT ALL ON index_version TO serenitas_user;
GRANT ALL ON index_desc TO serenitas_user;


CREATE TABLE quotes (
-- DEPRECATED
       QuoteDate timestamp,
       Index index_type,
       Series smallint,
       Version smallint,
       tenor tenor,
       attach smallint,
       detach smallint,
       RefBasketPrice float,
       Upfront float,
       Running float,
       BasketDuration float,
       TrancheDuration float,
       TrancheDelta float,
       CorrAtDetachment float,
       Basis float,
       QuoteSource varchar(4)
);

CREATE TABLE tranche_quotes (
       QuoteDate timestamptz,
       Index index_type,
       Series smallint,
       Version smallint,
       Tenor tenor,
       Attach smallint,
       Detach smallint,
       TrancheUpfrontBid float,
       TrancheUpfrontMid float,
       TrancheUpfrontAsk float,
       TrancheRunningBid float,
       TrancheRunningMid float,
       TrancheRunningAsk float,
       IndexRefPrice real,
       IndexRefSpread smallint,
       IndexDuration real,
       TrancheDuration real,
       TrancheDelta real,
       CorrAtDetachment real,
       Basis real,
       QuoteSource varchar(4),
       PRIMARY KEY(QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource, trancherunningmid)
);

GRANT ALL ON quotes TO serenitas_user;
GRANT ALL ON tranche_quotes TO serenitas_user;

-- Idiosyncracies:

-- for Itraxx 9 index:

--     tr.id	coupon type
-- 3-6 98155	all running
--     98154	U+500
-- 6-9 98160	all running
--     166395 	U+300
--     98159	U+500

-- For IG 9 index:

--        tr.id	coupon type
-- 3-7    162290	all running
--        162291	U+500
-- 7-10   162298	all running
--        162300	U+500
-- 10-15  162301	all running
--        162304	U+500
-- 15-30  162305	all running
--        162306	U+500
-- 30-100 162307	all running
--        162308	U+500

-- All other tranches should have a unique trancheid for a given basketid, attach, detach combination

CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
CREATE TYPE tier AS ENUM('SNRFOR', 'SECDOM', 'SUBLT2', 'PREFT1', 'JRSUBUT2');
CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT');
CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO');
CREATE TYPE BBG_CC AS ENUM('OC');

CREATE TABLE CDS_Issuers_old(
       --DEPRECATED
       Name text,
       company_id integer,
       ticker text,
       currency curr,
       seniority sen,
       doc_clause DocClause,
       cds_curve text[8] UNIQUE,
       index_list integer[],
       markit_ticker text,
       markit_tier tier,
       spread integer,
       PRIMARY KEY(company_id));

GRANT ALL ON  CDS_Issuers_old to serenitas_user;

CREATE TABLE bbg_issuers(
       Name text,
       company_id integer,
       ticker text,
       currency curr,
       seniority sen,
       doc_clause DocClause,
       cds_curve text[8] UNIQUE,
       index_list integer[],
       PRIMARY KEY(company_id));

GRANT ALL ON  bbg_issuers to serenitas_user;

CREATE TABLE bbg_markit_mapping(
       date date,
       company_id integer REFERENCES bbg_Issuers,
       markit_ticker text,
       markit_tier tier,
       spread integer,
       PRIMARY KEY(company_id, date));

GRANT ALL ON  bbg_markit_mapping to serenitas_user;

CREATE TABLE cds_quotes(
       Date Date,
       curve_ticker text,
       UpfrontBid float,
       UpfrontAsk float,
       RunningBid float,
       RunningAsk float,
       Source bbgSource,
       Recovery float,
       PRIMARY KEY(curve_ticker, Date));

GRANT ALL ON cds_quotes TO serenitas_user;

CREATE TABLE markit_tranche_quotes(
       quotedate date,
       basketid integer REFERENCES index_version,
       tenor tenor,
       attach smallint,
       detach smallint,
       upfront_bid float,
       upfront_mid float,
       upfront_ask float,
       tranche_spread smallint,
       index_price float,
       PRIMARY KEY (quotedate, basketid, tenor, attach, detach)
);

GRANT ALL ON markit_tranche_quotes TO serenitas_user;

CREATE TABLE trace_trades(
       cusip varchar(9),
       time timestamptz,
       condition_code BBG_OC,
       size  int,
       price float,
       PRIMARY KEY (cusip, time)
);

CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RETURNS integer AS $$
       DECLARE
	p_index index_type;
      	p_series smallint;
       	p_basketid integer;
       BEGIN
	p_index := upper(left(index_name, 2))::index_type;
       	p_series := right(index_name, -2);
       	SELECT MIN(basketid) INTO p_basketid FROM index_version WHERE Index=p_index and
	Series=p_series and lastdate>=p_date;
       	RETURN p_basketid;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION index_redcode(p_index index_type, p_series smallint, p_date date)
       RETURNS varchar AS $$
       DECLARE
       p_redcode varchar;
       BEGIN
         SELECT redindexcode INTO p_redcode
         FROM index_version WHERE index=p_index AND series=p_series AND lastdate>=p_date ORDER BY lastdate LIMIT 1;
         RETURN p_redcode;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
       RETURNS SETOF cds_issuers_old AS $$
       DECLARE
	basketid integer;
       BEGIN
	SELECT nameToBasketID(index_name, p_date) INTO basketid;
       	RETURN QUERY SELECT * FROM historical_cds_issuers(p_date)
	WHERE index_list @> Array[basketid];
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
       BEGIN
	RETURN QUERY SELECT b.*
       	FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
       	WHERE c.Date<=$1 GROUP BY c.curve_ticker) a
       	JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
       END;
       -- simpler query but slower
       -- BEGIN
       -- RETURN QUERY SELECT DISTINCT ON (curve_ticker) * from cds_quotes where date<=$1 ORDER BY
       -- curve_ticker, DATE desc;
       -- END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_cds_issuers(date DEFAULT current_date)
RETURNS SETOF cds_issuers_old AS $$
  BEGIN
	  RETURN QUERY SELECT bbg_issuers.*, map.markit_ticker, map.markit_tier, map.spread FROM
    (SELECT b.* FROM (SELECT min(c.date) AS latestdate, c.company_id FROM bbg_markit_mapping c
    WHERE c.date>=$1 GROUP BY c.company_id) a
    JOIN bbg_markit_mapping b ON a.company_id = b.company_id AND a.latestdate=b.date) map
    JOIN bbg_issuers USING (company_id);
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
       TABLE(cds_ticker text, date date, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
       BEGIN
       RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
       array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
       array_agg((a.upfrontbid+a.upfrontask)/2 ORDER BY tenor),
       array_agg(a.Recovery ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
       (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
       FROM index_members($1, $2)) b
       ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date) RETURNS
       TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
       BEGIN
       RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
       string_agg(to_char((a.runningbid+a.runningask)/2, 'FM999'), ','  ORDER BY tenor),
       string_agg(to_char((a.upfrontbid+a.upfrontask)/2, 'FM99D99'), ','  ORDER BY tenor),
       string_agg(to_char(a.Recovery, 'FM0D99'), ','  ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
       (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
       FROM index_members($1, $2)) b
       ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
       END;
       $$ LANGUAGE plpgsql;

CREATE TABLE index_quotes(
       date date,
       index index_type,
       series smallint,
       version smallint,
       tenor tenor,
       closeprice float,
       closespread float,
       modelprice float,
       modelspread float,
       adjcloseprice float,
       adjmodelprice float,
       duration float,
       theta float,
       PRIMARY KEY(date, index, series, tenor, version));

CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series integer, pg_tenor text,
       pg_date date)
       RETURNS SETOF tranche_quotes AS
       $$
       DECLARE r RECORD;
       DECLARE indexname text;
       DECLARE lower_attach smallint;
       DECLARE flag boolean;
       BEGIN
       indexname := pg_index_type || pg_series;

       IF lower(indexname) ='hy9' or lower(indexname) ='hy10'
       THEN
       	  lower_attach = 10::smallint;
       ELSE
          lower_attach = 0::smallint;
       END IF;
       flag := FALSE;
       FOR r in EXECUTE
	 'SELECT DISTINCT quotesource, quotedate FROM TRANCHE_QUOTES
	 WHERE index=$1::index_type AND series=$2 AND quotedate::date=$3
	 AND tenor =$4::tenor ORDER BY quotedate desc'
       USING pg_index_type, pg_series, pg_date, pg_tenor LOOP
       IF pg_index_type = 'HY' AND pg_series >=15 THEN
          EXECUTE
          'SELECT array_agg(attach ORDER by attach asc)||100::smallint=$6||array_agg(detach) FROM
          tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
          and quotedate=$4 and detach-attach!=5::smallint and quotesource=$5'
          INTO flag
          USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
          IF flag THEN
             RETURN QUERY
             SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series= pg_series
             AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND detach-attach!=5::smallint AND
             quotesource=r.quotesource ORDER BY attach asc;
	     RETURN;
          END IF;
       ELSE
          EXECUTE
          'SELECT array_agg(attach ORDER BY attach asc)||100::smallint=$6||array_agg(detach) FROM
          tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
	  and quotedate=$4 and quotesource=$5'
          INTO flag
          USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
          IF flag THEN
             RETURN QUERY
             SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series=pg_series
             AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND quotesource=r.quotesource
             ORDER BY attach asc;
	     RETURN;
          END IF;
       END IF;
       END LOOP;
       END;
       $$ language plpgsql;

CREATE TABLE risk_numbers(
       date date,
       index index_type,
       series integer,
       tenor tenor,
       indexprice float,
       indexbasis float,
       indexEL float,
       indexduration float,
       indextheta float,
       attach integer[],
       Skew float[],
       "Dealer Deltas" float[],
       "Model Deltas" float[],
       "Forward Deltas" float[],
       gammas float[],
       thetas float[],
       corr01 float[],
       durations float[],
       el float[],
       PRIMARY KEY(date, index, series, tenor));

GRANT ALL ON risk_numbers to serenitas_user;


CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2(
    IN p_date date,
    IN p_fromseries smallint,
    IN p_index index_type,
    IN tenorarray tenor[])
  RETURNS TABLE(series smallint, redindexcode text, indexfactor float, t1price float, t1maturity date,
  t1sprd float, t1dur float, t2price float, t2maturity date, t2sprd float, t2dur float,
  t3price float, t3maturity date, t3sprd float, t3dur float) AS $$
  DECLARE
    tenor_cat text;
    what_query text;
BEGIN
  tenor_cat  := format('SELECT * FROM unnest(%L::tenor[])', tenorarray);
  what_query := 'SELECT series, tenor, %I FROM index_quotes where index=%L and series>=%L and date = %L ORDER BY series, tenor';

  RETURN QUERY
   WITH mat AS (SELECT a.series, array_agg(a.maturity order by tenor) AS maturity FROM index_maturity a
                WHERE a.series>=p_fromseries AND a.index=p_index AND a.tenor=ANY(tenorarray) GROUP BY a.series),
	    indic AS (SELECT DISTINCT ON (series) * FROM index_version
                WHERE index_version.series>=p_fromseries AND index_version.index=p_index AND lastdate>=p_date
                ORDER BY series, lastdate),
	  pxtable AS (SELECT * FROM crosstab(format(what_query, 'closeprice', p_index, p_fromseries, p_date), tenor_cat)
                AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float)),
	sprdtable AS (SELECT * from crosstab(format(what_query, 'closespread', p_index, p_fromseries, p_date), tenor_cat)                 AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float)),
	 durtable AS (SELECT * from crosstab(format(what_query, 'duration', p_index, p_fromseries, p_date), tenor_cat)
                AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float))

	SELECT mat.series, indic.redindexcode, indic.indexfactor,
	pxtable.tenor1, mat.maturity[1], sprdtable.tenor1, durtable.tenor1,
	pxtable.tenor2, mat.maturity[2], sprdtable.tenor2, durtable.tenor2,
	pxtable.tenor3, mat.maturity[3], sprdtable.tenor3, durtable.tenor3

	FROM mat JOIN pxtable USING (series) JOIN sprdtable USING (series) JOIN durtable USING (series)
  JOIN indic USING (series) ORDER by mat.series;

END;
  $$
  LANGUAGE plpgsql;

CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint,
                  index_factor float, cumulativeloss float)
RETURNS float AS $$
DECLARE
  newattach float;
  newdetach float;

BEGIN
newattach:=LEAST(GREATEST((attach-100*cumulativeloss)/index_factor, 0), 1);
newdetach:=LEAST(GREATEST((detach-100*cumulativeloss)/index_factor, 0), 1);
RETURN (newdetach-newattach)/(detach-attach)*index_factor;
END;
$$ LANGUAGE plpgsql;


CREATE TABLE USD_rates(
       effective_date date PRIMARY KEY,
       "1M" real,
       "2M" real,
       "3M" real,
       "6M" real,
       "9M" real,
       "1Y" real,
       "2Y" real,
       "3Y" real,
       "4Y" real,
       "5Y" real,
       "6Y" real,
       "7Y" real,
       "8Y" real,
       "9Y" real,
       "10Y" real,
       "12Y" real,
       "15Y" real,
       "20Y" real,
       "25Y" real,
       "30Y" real)

CREATE TABLE EUR_rates(
       effective_date date PRIMARY KEY,
       "1M" real,
       "2M" real,
       "3M" real,
       "6M" real,
       "9M" real,
       "1Y" real,
       "2Y" real,
       "3Y" real,
       "4Y" real,
       "5Y" real,
       "6Y" real,
       "7Y" real,
       "8Y" real,
       "9Y" real,
       "10Y" real,
       "12Y" real,
       "15Y" real,
       "20Y" real,
       "25Y" real,
       "30Y" real)

CREATE TABLE USD_curves(
       effective_date date PRIMARY KEY,
       curve bytea)

CREATE TABLE EUR_curves(
       effective_date date PRIMARY KEY,
       curve bytea)

CREATE TABLE swaption_quotes(
       quotedate timestamptz,
       index index_type,
       series smallint,
       expiry date,
       strike float,
       delta_pay float,
       delta_rec float,
       pay_bid float,
       pay_offer float,
       rec_bid float,
       rec_offer float,
       vol float,
       price_vol float,
       gamma float,
       tail float,
       quote_source varchar(4),
       PRIMARY KEY(quotedate, index, series, expiry, strike),
       FOREIGN KEY(quotedate, index, series, expiry) REFERENCES swaption_ref_quotes)

CREATE TABLE swaption_ref_quotes(
       quotedate timestamptz,
       index index_type,
       series smallint,
       expiry date,
       ref float,
       fwdprice float,
       fwdspread  float,
       fwdbpv float,
       PRIMARY KEY(quotedate, index, series, expiry))

CREATE TABLE swaption_calib(
       quotedate timestamptz,
       index index_type,
       series smallint,
       expiry date,
       strike float,
       vol_payer float,
       vol_receiver float
       vol_payer_black float,
       vol_receiver_black float,
       PRIMARY KEY(quotedate, index, series, expiry, strike))