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 PRIMARY KEY,
       Index index_type NOT NULL,
       Series smallint NOT NULL,
       Version smallint NOT NULL,
       IndexFactor float DEFAULT 100,
       CumulativeLoss float DEFAULT 0.,
       lastdate date DEFAULT 'infinity',
       redindexcode text,
       description text,
       PRIMARY KEY(BasketID)
);
CREATE TYPE INDEXFAMILY AS ENUM('ITRAXX-Asian', 'LCDXNA', 'MCDXNA',
'ITRAXX-SOVX', 'ITRAXX-SDI', 'ITRAXX-L', 'CDX', 'ITRAXX-European');

CREATE TABLE index_version_markit(
       redindexcode text PRIMARY KEY,
       indexfamily INDEXFAMILY,
       indexsubfamily text,
       indexname text,
       ccy curr,
       indexfactor float,
       recoveryrate float,
       series smallint,
       version smallint,
       annexdate date,
       effectivedate date,
       firstpaymentdate date,
       activeversion bool,
       nextredindexcode text,
       prevredindexcode text)

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 TABLE index_maturity_markit(
       redindexcode text REFERENCES index_version_markit,
       tenor tenor,
       maturity date,
       tradeid text PRIMARY KEY,
       coupon integer,
);

CREATE OR REPLACE VIEW risk_num_per_quote AS
  SELECT a.*, b.index, b.series, b.tenor, b.attach, b.detach,
         b.trancheupfrontmid, b.trancherunningmid, b.indexrefprice, b.indexrefspread,
         b.tranchedelta, b.quotesource, b.quotedate
    FROM tranche_risk a
    JOIN tranche_quotes b ON a.tranche_id = b.id;

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 (
       id serial PRIMARY KEY,
       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),
       markit_id integer UNIQUE,
       deleted bool DEFAULT False NOT NULL,
       UNIQUE (QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource, trancheupfrontmid)
);
-- partial index constraint, didn't clean the db further back
CREATE UNIQUE INDEX
ON tranche_quotes(quotedate, index, series, version, tenor, attach, detach, quotesource, trancherunningmid)
WHERE quotedate>='2018-11-01';

CREATE TABLE tranche_quotes_csv(
       id serial PRIMARY KEY,
       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),
       markit_id integer UNIQUE,
       UNIQUE (QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource, trancherunningmid, markit_id)
);

CREATE INDEX tranche_quotes_idx ON
tranche_quotes(index, series, DATE(quotedate AT TIME ZONE 'localtime'), tenor, attach ASC)

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 entitytype AS ENUM('Corp', 'Sov', 'State', 'StatBody', 'Supra', 'Insurer',
       'Monoline', 'Index', 'Muni');
CREATE TYPE depthlevel AS ENUM('high', 'med', 'low', 'High', 'Med', 'Low', 'HIGH', 'MED', 'LOW');

CREATE TABLE IF NOT EXISTS RefEntity(
       referenceentity text NOT NULL,
       shortname text NOT NULL,
       ticker text NOT NULL,
       redentitycode varchar(6) NOT NULL PRIMARY KEY,
       entitycusip text NOT NULL,
       lei text,
       entitytype text NOT NULL,
       jurisdiction text NOT NULL,
       depthlevel depthlevel,
       markitsector text,
       isdatradingdefinition text,
       recorddate date,
       ratings text[],
       entityform text,
       companynumber jsonb,
       alternativenames text,
       isdatransactiontypes jsonb,
       validto date,
       validfrom date,
       events jsonb,
       holdco bool,
       country varchar(3));

CREATE TABLE IF NOT EXISTS RefObligation(
       id serial PRIMARY KEY,
       obligationname text NOT NULL,
       prospectusinfo jsonb,
       refentities text[],
       type text NOT NULL,
       isconvert bool NOT NULL,
       isperp bool NOT NULL,
       coupontype text NOT NULL,
       ccy varchar(3) NOT NULL,
       maturity date,
       issuedate date,
       coupon numeric(8,6) NOT NULL,
       isin varchar(12) NOT NULL,
       cusip varchar(9),
       event text);

CREATE TABLE IF NOT EXISTS RedPairMapping(
       redpaircode varchar(9) PRIMARY KEY,
       role text NOT NULL,
       referenceentity text NOT NULL,
       redentitycode text NOT NULL REFERENCES RefEntity,
       tier text NOT NULL,
       pairiscurrent boolean,
       pairvalidfrom date,
       pairvalidto date,
       ticker text NOT NULL,
       ispreferred boolean,
       preferreddate date,
       indexconstituents text[],
       recorddate date NOT NULL,
       publiccomments text,
       myticker text,
       subordinationtype text,
       holdco bool,
       preferredremovaldate date);

CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
CREATE TYPE sen AS ENUM('Senior', 'Subordinated', 'SLA');
CREATE TYPE tier AS ENUM('SNRFOR', 'SECDOM', 'SUBLT2', 'PREFT1', 'JRSUBUT2', 'SNRLAC');
CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT', 'CMAN', 'SRNTAS');
CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring', 'Modified Restructuring');
CREATE TYPE ShortCode AS ENUM('CR14', 'XR14', 'MM14');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
CREATE TYPE TENOR AS ENUM('1M', '3M', '6M', '9M', '1Y', '2Y', '3Y', '4Y', '5Y', '6Y', '7Y', '8Y', '9Y', '10Y', '12Y', '15Y', '20Y', '25Y', '30Y');
CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO', 'BS', 'HY.BB');
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 TYPE cds_issuers AS (
       Name text,
       company_id integer,
       ticker text,
       currency curr,
       seniority sen,
       short_code ShortCode,
       cds_curve text[8],
       markit_ticker text,
       markit_tier tier,
       spread integer,
       event_date date);

GRANT ALL ON  cds_Issuers to serenitas_user;

CREATE TYPE cds_issuers_weight AS (
       Name text,
       company_id integer,
       ticker text,
       currency curr,
       seniority sen,
       short_code ShortCode,
       cds_curve text[8],
       markit_ticker text,
       markit_tier tier,
       spread integer,
       event_date date,
       weight float
);

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[],
       short_code ShortCode,
       PRIMARY KEY(company_id, seniority));

GRANT ALL ON  bbg_issuers to serenitas_user;

CREATE TABLE bbg_markit_mapping(
       date date,
       company_id integer,
       seniority sen,
       markit_ticker text,
       markit_tier tier,
       spread integer,
       FOREIGN KEY (company_id, seniority) REFERENCES bbg_issuers,
       PRIMARY KEY (date, company_id, seniority));

GRANT ALL ON  bbg_markit_mapping to serenitas_user;

CREATE TABLE basket_constituents(
       company_id integer,
       seniority sen,
       basketid integer REFERENCES index_version,
       weight float,
       FOREIGN KEY (company_id, seniority) REFERENCES bbg_issuers,
       PRIMARY KEY (company_id, seniority, basket_id));

CREATE OR REPLACE VIEW basket_constituents_current AS
SELECT company_id, seniority, basketid, weight/sum(weight) OVER (PARTITION BY basketid) AS curr_weight FROM basket_constituents;

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

GRANT ALL ON cds_quotes TO serenitas_user;
CREATE INDEX IF NOT EXISTS cds_quotes_date_index ON cds_quotes(date);

CREATE TABLE markit_tranche_quotes(
       id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       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,
       UNIQUE (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(substring(index_name, '[A-Za-z]{2,4}'))::index_type;
       p_series := substring(index_name, '[0-9]{1,2}')::smallint;
       	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_weight AS $$
   DECLARE
      p_basketid integer;
   BEGIN
   SELECT nameToBasketID(index_name, p_date) INTO p_basketid;
   RETURN QUERY SELECT a.*, b.curr_weight FROM historical_cds_issuers(p_date) a
                LEFT JOIN basket_constituents_current b USING (company_id, seniority)
                WHERE b.basketid=p_basketid;
   END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION index_curves(index_name varchar(4), p_date date)
RETURNS TABLE(weight float, p_curve bytea) AS $$
   DECLARE
      p_basketid integer;
   BEGIN
   SELECT nameToBasketID(index_name, p_date) INTO p_basketid;
   RETURN QUERY SELECT curr_weight, curve FROM basket_constituents_current
                LEFT JOIN cds_curves USING (company_id, seniority)
                WHERE basketid=p_basketid and (date=p_date OR date IS NULL);
   END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_cds_quotes(date, bbgSource) 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 BETWEEN $1 - interval '40 days' AND $1) AND source=$2 GROUP BY c.curve_ticker) a
       	JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date
       	WHERE source=$2;
       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 AS $$
  BEGIN
	  RETURN QUERY SELECT bbg_issuers.name,
                          bbg_issuers.company_id,
                          bbg_issuers.ticker,
                          bbg_issuers.currency,
                          bbg_issuers.seniority,
                          bbg_issuers.short_code,
                          bbg_issuers.cds_curve,
                          map.markit_ticker,
                          map.markit_tier,
                          map.spread,
                          event_date
                   FROM
                        (SELECT b.*
                         FROM (SELECT min(c.date) AS latestdate, c.company_id, c.seniority
                               FROM bbg_markit_mapping c
                               WHERE c.date>=$1 GROUP BY c.company_id, c.seniority) a
                         JOIN bbg_markit_mapping b
                         ON a.company_id = b.company_id
                         AND a.seniority = b.seniority
                         AND a.latestdate=b.date) map
                   JOIN bbg_issuers USING (company_id, seniority)
                   LEFT JOIN (SELECT * FROM defaulted WHERE event_date <= $1 ) c
                   ON map.company_id = id AND map.seniority = c.seniority;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date, bbgSource DEFAULT 'MKIT') 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, $3) 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_quotes2(
    varchar(4),
    date,
    float[] DEFAULT '{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[],
    bbgsource DEFAULT 'MKIT'::bbgsource) RETURNS
    TABLE(cds_ticker text, seniority sen, doc_clause shortcode,
          weight float, date date, spread_curve float[],
          upfront_curve float[], recovery_curve float[], currency curr,
          event_date date) AS $$
    BEGIN
       RETURN QUERY SELECT max(markit_ticker) AS t, b.seniority,
       short_code, max(b.weight), 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),
       max(b.currency), max(b.event_date) FROM historical_cds_quotes($2, $4) a
       RIGHT JOIN
             (SELECT curve_ticker, markit_ticker, company_id, c.seniority, short_code, c.weight, c.event_date, c.currency, tenor FROM
                     (SELECT unnest('{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[]) AS tenor,
                             unnest(cds_curve) AS curve_ticker,
                              markit_ticker,
                              company_id,
                              short_code,
                              d.seniority,
                              d.weight,
                              d.currency,
                              d.event_date
                FROM index_members($1, $2) d) c
        WHERE tenor=Any($3))  b
       ON b.curve_ticker = a.curve_ticker GROUP by company_id, b.seniority, short_code ORDER BY t;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date, bbgSource DEFAULT 'MKIT') RETURNS
TABLE(cds_ticker text, seniority sen, date date, weight float, spread_curve text,
      upfront_curve text, recovery_curve text) AS $$
   BEGIN
       RETURN QUERY SELECT max(markit_ticker) AS t, b.seniority, max(a.date), max(b.weight),
       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, $3) a
       RIGHT JOIN (SELECT
                     generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker,
                     markit_ticker, company_id, c.seniority, c.weight
                   FROM index_members($1, $2) c) b
       ON b.curve_ticker = a.curve_ticker
       GROUP by company_id, b.seniority ORDER BY t;
   END;
$$ LANGUAGE plpgsql;

CREATE TABLE index_quotes_old(
       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,
       duration2 float,
       theta2 float,
       PRIMARY KEY(date, index, series, tenor, version));

CREATE TABLE index_quotes_pre (
	id serial NOT NULL PRIMARY KEY,
	date date NOT NULL,
	index index_type NOT NULL,
	series smallint NOT NULL,
	version smallint NOT NULL,
	tenor tenor NOT NULL,
	close_price float,
	close_spread float,
	model_price float,
	model_spread float,
	source bbgsource NOT NULL,
    UNIQUE (date, index, series, tenor, version, source)
);

CREATE TABLE index_risk(
	id integer PRIMARY REFERENCES index_quotes_pre ON DELETE CASCADE,
    theta float,
    duration float);

CREATE TABLE index_risk2(
    id integer PRIMARY REFERENCES index_quotes_pre ON DELETE CASCADE,
    theta float,
    duration float,
    tweak float,
    dispersion float,
    gini float
);

CREATE OR REPLACE VIEW index_quotes AS
SELECT id, date, index, series, version, tenor, close_price AS closeprice,
close_spread AS closespread,
model_price AS modelprice, model_spread AS modelspread,
index_risk.duration, index_risk.theta,
index_risk2.duration AS duration2, index_risk2.theta AS theta2,
dispersion, gini
FROM index_quotes_pre
LEFT JOIN index_risk USING (id)
LEFT JOIN index_risk2 USING(id)
WHERE SOURCE IN ('MKIT', 'SRNTAS');

CREATE TABLE bbg_ticker_mapping(
       ticker text PRIMARY KEY,
       index index_type,
       series smallint,
       version smallint,
       tenor tenor
);

CREATE TABLE bbg_index_quotes(
       date date,
       ticker text REFERENCES bbg_ticker_mapping,
       index index_type,
       series smallint,
       version smallint,
       tenor tenor,
       last_price float,
       source bbgSource,
       PRIMARY KEY(date, ticker, version));

CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series integer, pg_tenor text,
       pg_date date, source text DEFAULT NULL) RETURNS SETOF tranche_quotes AS
$$
       DECLARE r RECORD;
       DECLARE best_quote RECORD DEFAULT NULL;
       DECLARE lower_attach smallint;
       DECLARE flag boolean;
       BEGIN

       IF lower(pg_index_type) ='hy' AND pg_series in (9, 10)
       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 date(timezone(''localtime'', quotedate))=$3
	 AND tenor =$4::tenor AND NOT deleted ORDER BY quotedate desc'
       USING pg_index_type, pg_series, pg_date, pg_tenor LOOP
       -- The JPM quotes are untriggered whereas Citi quotes are triggered
       IF pg_index_type = 'XO' AND pg_series = 22 AND
          r.quotesource = 'JPM' AND pg_date > '2016-04-25' THEN
           CONTINUE;
       END IF;
       IF r.quotesource = 'MSre' THEN
           CONTINUE;
       END IF;
       If r.quotesource != source and source is not null THEN
           CONTINUE;
       END IF;
       EXECUTE
          'SELECT array_agg(attach ORDER BY attach ASC)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
           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 AND NOT deleted'
       INTO flag
       USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
       IF flag THEN
           best_quote := r;
       END IF;
       EXIT WHEN r.quotesource != 'CITI' AND flag;
       END LOOP;
       IF best_quote IS NULL THEN
          RETURN;
       END IF;
       IF pg_index_type = 'HY' AND pg_series >=15 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=best_quote.quotedate AND detach-attach!=5::smallint AND
          quotesource=best_quote.quotesource AND NOT deleted ORDER BY attach ASC;
       ELSE
          RETURN QUERY
          SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series=pg_series
          AND tenor=pg_tenor::tenor AND quotedate=best_quote.quotedate AND quotesource=best_quote.quotesource AND NOT deleted
          ORDER BY attach asc;
       END IF;
       END;
$$ language plpgsql;

-- deprecated
CREATE TABLE risk_numbers_really_old(
       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));

-- deprecated
CREATE TABLE risk_numbers_old(
       id serial PRIMARY KEY,
       tranche_id integer REFERENCES tranche_quotes(id),
       date date,
       index index_type,
       series integer,
       tenor tenor,
       index_price float,
       index_basis float,
       "index_EL" float,
       index_duration float,
       index_theta float,
       attach smallint,
       detach smallint,
       corr_at_detach float,
       delta float,
       forward_delta float,
       gamma float,
       theta float,
       corr01 float,
       duration float,
       "EL" float);

GRANT ALL ON risk_numbers to serenitas_user;

CREATE OR REPLACE VIEW risk_numbers AS
SELECT tranche_id, quotedate as date, index, series, tenor,
       index_price, index_basis, index_expected_loss, index_duration,
       index_theta, attach, detach, corr_at_detach,
       delta, fwd_delta as forward_delta, gamma, theta, corr01,
       duration, expected_loss
FROM tranche_risk
JOIN tranche_quotes ON tranche_id=tranche_quotes.id;


CREATE TABLE tranche_risk(
     id serial PRIMARY KEY,
     tranche_id integer UNIQUE REFERENCES tranche_quotes(id) ON DELETE CASCADE,
     index_price float,
     index_basis float,
     index_expected_loss float,
     index_duration float,
     index_theta float,
     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);

GRANT ALL ON tranche_risk to serenitas_user;

CREATE TABLE markit_tranche_risk(
     id integer GENERATED BY DEFAULT PRIMARY KEY,
     tranche_id integer UNIQUE REFERENCES markit_tranche_quotes(id) ON DELETE CASCADE,
     index_price float,
     index_basis float,
     index_expected_loss float,
     index_duration float,
     index_theta float,
     corr_at_detach float,
     delta float,
     fwd_delta float,
     gamma float,
     theta float,
     corr01 float,
     duration float,
     spread float,
     expected_loss float);


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 "USD_OIS_rates"(
       effective_date date PRIMARY KEY,
       "1M" real,
       "2M" real,
       "3M" real,
       "6M" 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 "EUR_OIS_rates"(
       effective_date date PRIMARY KEY,
       "1M" real,
       "2M" real,
       "3M" real,
       "6M" 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 "JPY_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 "JPY_OIS_rates"(
       effective_date date PRIMARY KEY,
       "1M" real,
       "2M" real,
       "3M" real,
       "6M" 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,
       "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 JPY_curves(
       effective_date date PRIMARY KEY,
       curve bytea);

CREATE TABLE rate_curves(
       "id" integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       effective_date date NOT NULL,
       curve_type smallint,
       curve bytea,
       UNIQUE (effective_date, curve_type));

CREATE INDEX ON rate_curves(effective_date, curve_type);

CREATE TYPE rate_type AS ENUM('OIS', 'FUT', 'SWP', 'DEP', 'IND', 'BASIS', 'SFR_FUT');

CREATE TABLE bbg_rate_tickers(
       bbg_ticker text PRIMARY KEY,
       quote_type rate_type NOT NULL,
       currency curr NOT NULL,
       tenor text,
       start_date date,
       end_date date
);

CREATE TABLE bbg_rate_quotes(
       date date,
       bbg_ticker text REFERENCES bbg_rate_tickers,
       quote1 double,
       quote2 double,
       PRIMARY KEY (date, bbg_ticker)
);

-- require the btree_gist extension
CREATE TABLE bbg_curves(
       curve_type smallint,
       members text[] NOT NULL,
       in_effect daterange,
       EXCLUDE USING gist (curve_type WITH =, in_effect WITH &&)
);

CREATE TABLE cds_curves(
       date date NOT NULL,
       company_id integer NOT NULL,
       seniority sen NOT NULL,
       redcode text NOT NULL,
       curve bytea NOT NULL,
       FOREIGN KEY (company_id, seniority) REFERENCES bbg_issuers,
       PRIMARY KEY (date, company_id, seniority)
);


CREATE TABLE USD_swap_fixings(
       fixing_date date PRIMARY KEY,
       "1y" numeric(5, 3),
       "2y" numeric(5, 3),
       "3y" numeric(5, 3),
       "4y" numeric(5, 3),
       "5y" numeric(5, 3),
       "6y" numeric(5, 3),
       "7y" numeric(5, 3),
       "8y" numeric(5, 3),
       "9y" numeric(5, 3),
       "10y" numeric(5, 3),
       "15y" numeric(5, 3),
       "20y" numeric(5, 3),
       "30y" numeric(5, 3)
);

CREATE TYPE VOL_SOURCE AS ENUM('BVOL', 'CMPN', 'BBIR', 'GFIS');
CREATE TYPE "VOL_TYPE" AS ENUM('Normal', 'LogNormal');

CREATE TABLE swaption_normal_vol(
	date date,
	"1y" float[18],
	"2y" float[18],
	"3y" float[18],
	"4y" float[18],
	"5y" float[18],
	"6y" float[18],
	"7y" float[18],
	"8y" float[18],
	"9y" float[18],
	"10y" float[18],
	"15y" float[18],
	"20y" float[18],
	"25y" float[18],
	"30y" float[18],
    source VOL_SOURCE,
    PRIMARY KEY(date, source));

CREATE TABLE swaption_lognormal_vol(
	date date,
	"1y" float[18],
	"2y" float[18],
	"3y" float[18],
	"4y" float[18],
	"5y" float[18],
	"6y" float[18],
	"7y" float[18],
	"8y" float[18],
	"9y" float[18],
	"10y" float[18],
	"15y" float[18],
	"20y" float[18],
	"25y" float[18],
	"30y" float[18],
    source VOL_SOURCE,
    PRIMARY KEY(date, source));

CREATE TABLE swaption_vol(
       date date,
       expiry "TENOR",
       tenor "TENOR",
       vol_type "VOL_TYPE",
       source vol_source,
       vol double precision,
PRIMARY KEY (date, expiry, tenor, vol_type, source));

CREATE TABLE swaption_quotes(
       quote_id SERIAL PRIMARY KEY,
       ref_id integer REFERENCES swaption_ref_quotes ON DELETE CASCADE,
       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);

ALTER TABLE swaption_quotes ADD UNIQUE (ref_id, strike);

CREATE TABLE swaption_ref_quotes(
       ref_id serial PRIMARY KEY,
       quotedate timestamptz,
       index index_type,
       series smallint,
       expiry date,
       ref float,
       fwdprice float,
       fwdspread  float,
       fwdbpv float,
       quote_source varchar(4),
       msg_id bigint);

CREATE INDEX  ON swaption_ref_quotes (quotedate, index, series);
ALTER TABLE swaption_ref_quotes ADD UNIQUE(quotedate, index, series, expiry, quote_source);

CREATE TYPE swaption_quote AS (
       strike float,
       pay_bid float,
       pay_offer float,
       delta_pay float,
       rec_bid float,
       rec_offer float,
       delta_rec float);

CREATE OR REPLACE FUNCTION get_latest_swaption_quotes(
       pg_index index_type,
       pg_series integer,
       pg_expiry date,
       pg_date date,
       pg_dealer text)
       RETURNS SETOF swaption_quote AS $$
       DECLARE refid int;
       BEGIN

       EXECUTE 'SELECT ref_id from swaption_ref_quotes WHERE quotedate::date=$1
       and quote_source=$2 AND index=$3 AND series=$4 and expiry=$5
       ORDER BY quotedate LIMIT 1' INTO refid
       USING pg_date, pg_dealer, pg_index, pg_series, pg_expiry;
       RETURN QUERY SELECT strike, pay_bid, pay_offer, delta_pay, rec_bid, rec_offer, delta_rec
              FROM swaption_quotes WHERE ref_id=refid;
END;
$$ language plpgsql;

CREATE TABLE swaption_calib(
       quote_id integer PRIMARY KEY REFERENCES swaption_quotes ON DELETE CASCADE,
       vol_payer float,
       vol_receiver float,
       vol_payer_black float,
       vol_receiver_black float);


CREATE TABLE swaption_vol_cube(
       id serial PRIMARY KEY,
       date date NOT NULL,
       cube bytea NOT NULL,
       source vol_source,
       UNIQUE (date, vol_source))

CREATE OR REPLACE VIEW public.on_the_run AS
 SELECT DISTINCT ON (index_quotes.date, index_quotes.index) index_quotes.date,
    index_quotes.index,
    index_quotes.series,
    index_quotes.version,
    index_quotes.duration,
    index_quotes.theta,
    index_quotes.closeprice,
    index_quotes.closespread,
    index_quotes.duration2
   FROM index_quotes
  WHERE index_quotes.tenor = '5yr'::tenor
  ORDER BY index_quotes.date, index_quotes.index, index_quotes.series DESC, index_quotes.version;


CREATE TABLE defaulted(
    id integer,
    event_date date,
    auction_date date,
    recovery float,
    seniority sen,
    PRIMARY KEY (id, seniority)
    FOREIGN KEY (id, seniority) REFERENCES bbg_issuers);

CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR');

CREATE TABLE rates(
       date date NOT NULL,
       name cash_rate NOT NULL,
       rate float not NULL
       PRIMARY KEY (date, name));


CREATE TABLE dtcc_corrections(
    dissemination_id bigint,
    correction bytea)