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,
       PRIMARY KEY(BasketID)
);

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

CREATE OR REPLACE VIEW index_desc AS
 SELECT b.basketid, b.index, b.series, b.version, a.tenor, a.maturity, b.indexfactor, b.cumulativeloss, b.lastdate
   FROM index_maturity a
   JOIN index_version b ON a.index = b.index AND a.series = b.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 (
       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 timestamp,
       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 TABLE CDS_Issuers(
       Name text,
       company_id integer,
       ticker text,
       currency curr,
       seniority sen,
       doc_clause DocClause,
       cds_curve text[8],
       index_list integer[],
       markit_ticket text,
       markit_tier tier,
       PRIMARY KEY(company_id));

GRANT ALL ON  CDS_Issuers 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 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_members(index_name varchar(4), p_date date)
       RETURNS SETOF CDS_Issuers AS $$
       DECLARE
	basketid integer;
       BEGIN
	SELECT nameToBasketID(index_name, p_date) INTO basketid;
       	RETURN QUERY SELECT * FROM CDS_Issuers 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 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;