path: root/sql/et_tables.sql

-- -*- mode: sql; sql-product: postgres; -*-
CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT');

CREATE TABLE et_collateral (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dealname varchar(8) NOT NULL,
    updatedate date,
    Name text,
    IssuerName text,
    CurrentBalance float,
    Maturity date,
    AssetSubtype varchar(10),
    AssetType varchar(25),
    GrossCoupon float default NULL,
    Spread float default NULL,
    Frequency varchar(1),
    NextPaydate date,
    SecondLien boolean,
    LoanXID varchar(10),
    ET_LoanXID varchar(10) default NULL,
    figi varchar(12),
    ET_Cusip varchar(10) default NULL,
    IntexPrice float,
    IntexPriceSource text,
    IntexPriceDate date,
    UserPrice float default NULL,
    UserPriceDate date default NULL,
    FixedOrFloat intex_fixedorfloat,
    DefaultedFlag boolean,
    CovLite text,
    isCDO boolean,
    Liborfloor float,
    ReinvFlag boolean,
    Currency varchar(3),
    Industry text,
    Country text,
    amort_schedule_dates date[] DEFAULT NULL,
    amort_schedule_amounts float[] DEFAULT NULL,
    UNIQUE (updatedate, Name, dealname)
);

CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate);

ALTER TABLE et_collateral OWNER TO et_user;

CREATE TABLE cusip_user_mapping (
       IssuerName text,
       Maturity date,
       GrossCoupon float default NULL,
       Spread float default NULL,
       LoanXID varchar(8) default NULL,
       Cusip varchar(9) default NULL
);

CREATE TABLE loanx_user_mapping (
       IssuerName text,
       Maturity date,
       Spread float default NULL,
       LoanxID varchar(8) default NULL
);

ALTER TABLE cusip_user_mapping OWNER TO et_user;
ALTER TABLE loanx_user_mapping OWNER TO et_user;


CREATE TABLE markit_prices_old (
       -- DEPRECATED (use markit_prices)
       LoanXID varchar(8),
       Issuer text,
       DealName text,
       Facility text,
       Industry text,
       SP varchar(4),
       Moodys varchar(4),
       Amount float,
       Maturity date,
       Spread float,
       Bid float,
       Offer float,
       Depth int,
       STM float,
       PricingDate date,
       PRIMARY KEY (LoanXID, PricingDate)
);

ALTER TABLE markit_prices_old OWNER TO et_user;

CREATE TABLE markit_prices (
       loanxid varchar(8),
       bid float,
       offer float,
       depth int,
       pricingdate date,
       PRIMARY KEY (loanxid, pricingdate)
);

ALTER TABLE markit_prices OWNER TO et_user;

CREATE TABLE markit_facility (
       LoanXID varchar(8),
       PMDID float(10),
       IssuerName varchar(100),
       dealname varchar(60),
       facility_type varchar(40),
       loanx_facility_type varchar(50),
       initial_amount float(15),
       initial_spread float(7),
       maturity date,
       industry varchar(50),
       LoanXstatus varchar(1),
       created_time date,
       modified_time date,
       PRIMARY KEY (LoanXID, modified_time)
);

ALTER TABLE markit_facility OWNER TO et_user;

CREATE VIEW latest_markit_facility AS
       SELECT b.*
       FROM (SELECT MAX(modified_time) AS latestdate, loanxid FROM markit_facility GROUP BY loanxid) a
       JOIN markit_facility b ON a.loanxid = b.loanxid AND a.latestdate = b.modified_time;

GRANT ALL ON latest_markit_facility TO et_user;

CREATE OR REPLACE FUNCTION historical_facility(p_date date)
       RETURNS TABLE(p_loanxid varchar(8), pmdid float(10), issuername varchar(100),
       dealname varchar(60), facility_type varchar(40), loanx_facility_type varchar(50),
       initial_amount float(15), initial_spread float(7), maturity date, industry varchar(50),
       loanxstatus varchar(1), created_time date, p_modified_time date) AS $$
       DECLARE latestdate date;
       BEGIN
       RETURN QUERY SELECT b.*
       FROM (SELECT MAX(modified_time) AS latestdate, loanxid
       FROM markit_facility WHERE modified_time <= p_date GROUP BY loanxid) a
       JOIN markit_facility b ON a.loanxid = b.loanxid AND a.latestdate = b.modified_time;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION historical_facility(p_date date)
    OWNER TO et_user;

CREATE OR REPLACE VIEW latest_markit_prices AS
       SELECT c.loanxid, c.issuername, c.dealname, c.facility_type, c.loanx_facility_type,
       c.initial_amount, c.initial_spread, c.maturity, c.industry, b.bid, b.Offer, b.depth, a.latestdate
       FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate
       JOIN latest_markit_facility c ON a.loanxid = c.loanxid;

GRANT ALL ON latest_markit_prices TO et_user;

CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date)
       RETURNS TABLE(loanxid varchar(8), issuername varchar(100), dealname varchar(60),
       facility_type varchar(40), loanx_facility_type varchar(50), initial_amount float(15),
       initial_spread float(7), maturity date, industry varchar(5),
       bid float, offer float, depth integer, pricing_date date) AS $$
       DECLARE latestdate date;
       BEGIN
       RETURN QUERY SELECT c.p_loanxid, c.issuername, c.dealname, c.facility_type,
       c.loanx_facility_type, c.initial_amount, c.initial_spread, c.maturity,
       c.industry, b.bid, b.Offer, b.depth, a.latestdate
       FROM (SELECT MAX(pricingdate) AS latestdate, markit_prices.loanxid FROM markit_prices
       WHERE pricingdate <= p_date GROUP BY markit_prices.loanxid) a
       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate = b.pricingdate
       JOIN historical_facility(p_date) c ON c.p_loanxid = a.loanxid;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION historical_markit_prices(p_date date)
      OWNER TO et_user;

CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV');

CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULTED',
'PAY-IN-KIND', 'VARIABLE', 'ZERO COUPON', 'STEP CPN', 'FLAT TRADING', 'NONE', 'FUNGED',
'STEP', 'ZERO', 'ORIG ISSUE DISC');

CREATE TYPE bloomberg_ln_status AS ENUM('ACCELERATED DEFAULT', 'ADMINISTRATION', 'DEFAULTED',
       'EVENT OF DEFAULT', 'FUNGED', 'GENL SYNDICATION', 'IN BANKRUPTCY', 'MANDATE', 'MATURED',
       'REFINANCED', 'REPLACED', 'RESTRUCTURED', 'RESTRUCTURED - EXCH',
       'RESTRUCTURED - FULL', 'RESTRUCTURED - STLMT', 'RETIRED', 'RETIRED DEFAULT',
       'SENIOR SYNDICATION', 'SIGNED', 'SIGNED NOT EFFECTIVE',
       'SIGNED/SYNDICATION', 'SYNDICATION CLOSED', 'WITHDRAWN', 'CANCELLED');

CREATE TABLE bloomberg_corp_old (
       Cusip varchar(9),
       Price float,
       PricingDate date,
       Issuer text,
       Maturity date,
       Coupon float,
       CouponType bloomberg_cpntype,
       Frequency int,
       Spread float,
       Libor_floor float,
       loan_margin float,
       covlite boolean,
       secondlien boolean,
       defaulted boolean,
       Source bloomberg_source,
       id_bb_unique text,
       security_type text,
       amount_outstanding float,
       PRIMARY KEY (Cusip, PricingDate)
);

CREATE TABLE bloomberg_corp_ref (
       figi varchar(12) PRIMARY KEY,
       cusip varchar(9),
       Issuer text,
       Maturity date,
       Coupon float,
       CouponType bloomberg_cpntype,
       Frequency int,
       Spread float,
       Libor_floor float,
       issue_size float,
       covlite boolean,
       secondlien boolean,
       security_type text,
       issue_date date,
       defaulted boolean,
       default_date date,
       called boolean,
       called_date date,
       status bloomberg_ln_status,
       loanxid varchar(8)[],
       reset_idx text);

GRANT ALL ON bloomberg_corp_ref TO et_user;

CREATE TABLE bloomberg_corp(
       figi varchar(12) REFERENCES bloomberg_corp_ref,
       pricingdate date,
       price float,
       loan_margin float,
       amount_outstanding float,
       source bloomberg_source,
       PRIMARY KEY (figi, pricingdate));


GRANT ALL ON bloomberg_corp TO et_user;

CREATE TABLE bloomberg_mtge (
       Cusip varchar(9),
       Issuer text,
       Maturity date,
       Coupon float,
       CouponType bloomberg_cpntype,
       Frequency int,
       Spread float,
       Moody text,
       InitialMoody text,
       PRIMARY KEY (CUSIP)
);

GRANT ALL ON bloomberg_mtge TO et_user;

CREATE TABLE clo_universe_old (
       -- DEPRECATED
       dealname varchar(10),
       "Deal Name" text,
       Manager text,
       "Orig Deal Bal" float,
       "Curr Deal Bal" float,
       "Orig Collat Bal" float,
       "Curr Collat Bal" float,
       "Tranche Factor" float,
       "Principal Bal" float,
       "Interest Bal" float,
       "CDO Percentage" float,
       "Defaulted Bal" float,
       "Curr Coupon" float,
       "Deal Issue Date" date,
       Maturity date,
       "Deal Next Pay Date" date,
       "Reinv End Date" date,
       "Latest Update" date,
       "Deal Cusip List" text[],
       PaidDown date,
       "Pay Day" date,
       PRIMARY KEY (dealname, "Latest Update")
);

CREATE TABLE deal_indicative (
       dealname varchar(10) PRIMARY KEY,
       "Deal Name" text,
       Manager text,
       deal_issue_date date,
       pay_day smallint,
       first_pay_date date,
       paid_down date,
);

GRANT ALL ON deal_indicative TO et_user;

CREATE TABLE clo_universe (
       dealname varchar(10) REFERENCES deal_indicative,
       "Curr Deal Bal" float,
       "Curr Collat Bal" float,
       "Tranche Factor" float,
       "Principal Bal" float,
       "Interest Bal" float,
       "CDO Percentage" float,
       "Defaulted Bal" float,
       "Curr Coupon" float,
       "Latest Update" date,
       latest_refi_date date,
       "Deal Cusip List" text[],
       maturity date,
       reinv_end_date date,
       orig_deal_bal float,
       orig_collat_bal float,
       PRIMARY KEY (dealname, "Latest Update"),
);

GRANT ALL ON clo_universe TO et_user;

CREATE TABLE cusip_universe_old (
--deprecated
       Cusip varchar(9),
       ISIN varchar(12),
       dealname varchar(10),
       tranche text,
       Coupon float,
       Orig_Balance float,
       Curr_Balance float,
       Factor float,
       Orig_Moody text,
       Curr_Moody text,
       Orig_Attach float,
       Orig_Detach float,
       Curr_Attach float,
       Curr_Detach float,
       Floater_Index text,
       Spread float,
       subordination float,
       thickness float,
       updatedate date,
       "Bloomberg Ticker" text,
       PRIMARY KEY(Cusip, updatedate)
);

GRANT ALL ON cusip_universe TO et_user;

CREATE TABLE tranche_ref(
       id serial PRIMARY KEY,
       Cusip varchar(9) NOT NULL,
       ISIN varchar(12),
       bloomberg_ticker text,
       dealname varchar(10) NOT NULL,
       tranche text,
       paripassu_tranches text[],
       Orig_Balance float,
       Orig_Moody text,
       Orig_Attach float,
       Orig_Detach float,
       Floater_Index text,
       Spread float,
       type text,
       figi varchar(12),
       UNIQUE (Cusip, dealname));

CREATE TABLE tranche_update(
       id int REFERENCES tranche_ref,
       curr_balance float,
       factor float,
       coupon float,
       Curr_Moody text,
       Curr_Attach float,
       Curr_Detach float,
       updatedate date,
       PRIMARY KEY (id, updatedate));

CREATE OR REPLACE VIEW tranche_universe AS
       SELECT a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche,
       a.paripassu_tranches, a.orig_balance, a.orig_moody,
       a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type,
       b.curr_balance, b.factor, b.coupon, b.curr_moody,
       b.curr_attach, b.curr_detach,
       b.updatedate FROM tranche_ref a
       JOIN tranche_update b USING (id);

CREATE OR REPLACE VIEW latest_clo_universe AS
       SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal",
       b.orig_collat_bal, b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal",
       b."Interest Bal", b."CDO Percentage", b."Defaulted Bal", b."Curr Coupon",
       deal_issue_date, b.maturity, b.reinv_end_date, b."Latest Update",
       b.last_refi_date, b."Deal Cusip List", pay_day, first_pay_date, paid_down
       FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a
       JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update"
       JOIN deal_indicative ON b.dealname = deal_indicative.dealname
       ORDER BY dealname ASC;

GRANT ALL ON latest_clo_universe TO et_user;

CREATE OR REPLACE VIEW latest_tranche_universe AS
SELECT DISTINCT ON (id) a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname,
       a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody,
       a.orig_attach, a.orig_detach, a.floater_index, a.spread,
       a.type, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach,
       b.curr_detach, b.updatedate from tranche_update b JOIN tranche_ref a USING (id)
       JOIN deal_indicative USING (dealname)
WHERE paid_down IS NULL ORDER BY id, updatedate DESC;


CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date)
       RETURNS SETOF latest_clo_universe AS $$
       DECLARE latestdate date;
       BEGIN
           SELECT max(clo_universe."Latest Update") INTO latestdate FROM clo_universe
	   WHERE clo_universe.dealname = p_dealname AND clo_universe."Latest Update"<=p_date;

	   RETURN QUERY SELECT a.dealname, "Deal Name", Manager, a.orig_deal_bal, a."Curr Deal Bal",
           a.orig_collat_bal, a."Curr Collat Bal", a."Tranche Factor", a."Principal Bal",
           a."Interest Bal", a."CDO Percentage", a."Defaulted Bal", a."Curr Coupon",
           deal_issue_date, a.maturity, a.reinv_end_date, a."Latest Update",
           a.last_refi_date, a."Deal Cusip List", pay_day, first_pay_date, paid_down
           FROM clo_universe a
           JOIN deal_indicative ON a.dealname = deal_indicative.dealname
           WHERE a.dealname = p_dealname
	         AND a."Latest Update" = latestdate;
       END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_tranche_universe(p_figi varchar(12), p_date date)
       RETURNS SETOF tranche_universe AS $$
       DECLARE latestdate date;
       BEGIN
        SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_update
	WHERE tranche_universe.figi = p_figi AND tranche_imoverse.updatedate<=p_date;
	RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.figi=p_figi
	AND a.updatedate=latestdate;
       END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10), p_date date)
       RETURNS SETOF tranche_universe AS $$
       DECLARE latestdate date;
       BEGIN
        SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_universe
	WHERE tranche_universe.dealname = p_dealname AND tranche_universe.updatedate<=p_date;
	RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.dealname=p_dealname
	AND a.updatedate=latestdate;
       END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW dealfigimapping AS
       select figi, dealname from deal_indicative join tranche_ref using (dealname) where paid_down is null;


GRANT ALL ON dealfigimapping TO et_user;

CREATE VIEW latest_markit_prices AS
       SELECT b.*
       FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;


CREATE OR REPLACE FUNCTION et_historicaldealinfo (p_dealname varchar(10), p_date date)
       RETURNS SETOF et_collateral AS $$
       DECLARE latestdate date;
       BEGIN
	SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral
	WHERE et_collateral.dealname = p_dealname
	AND et_collateral.updatedate <= p_date;
	RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND
	a.updatedate=latestdate ORDER BY lower(a.issuername);
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10))
       RETURNS SETOF et_collateral AS $$
       DECLARE latestdate date;
       BEGIN
	SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral
	WHERE et_collateral.dealname = p_dealname;
	RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND
	a.updatedate=latestdate ORDER BY lower(a.issuername);
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10))
       RETURNS TABLE(issuername text, currentbalance float, maturity date,
       fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
       frequency varchar(1), nextpaydate date, loanxid varchar(10), figi varchar(12),
       covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$
       BEGIN
	RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
	currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
	avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price,
	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
	a.loanxid, a.figi, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
	bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
	bool_or(a.defaultedflag) AS defaultedflag FROM et_latestdealinfo(p_dealname) a
	LEFT JOIN latest_markit_prices b ON coalesce(a.ET_LoanXID, a.loanxid) = b.loanxid
	LEFT JOIN latest_bloomberg_corp c ON coalesce(a.cusip, a.ET_cusip)=c.cusip WHERE a.ReinvFlag IS NOT TRUE
	GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
	a.frequency, a.figi ORDER BY issuername;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_spread float)
       RETURNS TABLE(loanxid varchar(10), cusip varchar(9)) AS $$
       BEGIN
	RETURN QUERY SELECT loanxid, cusip from et_user_mapping a where a.issuername = p_issuername and a.maturity = p_maturity and a.spread = p_spread;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW latest_bloomberg_corp AS
SELECT DISTINCT ON (figi) * FROM bloomberg_corp_ref JOIN
bloomberg_corp USING (figi) ORDER BY figi, pricingdate DESC;


CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date)
  RETURNS SETOF latest_bloomberg_corp AS $$
       BEGIN
       RETURN QUERY SELECT DISTINCT ON (figi) * FROM bloomberg_corp_ref JOIN bloomberg_corp USING (figi)
       WHERE pricingdate <=p_date ORDER BY figi, pricingdate DESC;
       END;
$$ LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION historical_bloomberg_mtge(p_date date)
--        RETURNS TABLE(CUSIP varchar(9), updatedate date, issuer text, maturity date,
--        coupon float, coupontype bloomberg_cpntype, frequency integer, spread float, Moody text,
--        InitialMoody text) AS $$
--        BEGIN
--        RETURN QUERY SELECT b.* FROM
--        (SELECT MAX(c.updatedate) AS updatedate, c.cusip FROM bloomberg_mtge c
--        WHERE c.updatedate<=p_date GROUP BY c.cusip) a
--        JOIN bloomberg_mtge b ON a.cusip = b.cusip AND a.updatedate= b.updatedate;
--        END;
--        $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
       RETURNS TABLE(issuername text, currentbalance float, maturity date,
       fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float,
       spread float, frequency varchar(1), nextpaydate date, loanxid varchar(10),
       figi varchar(12), industry text, covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean,
       et_loanxid varchar(10), et_cusip varchar(9)) AS $$
       BEGIN
	RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
	currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
	avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price,
	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
	a.loanxid, a.figi, max(a.industry), (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
	bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
	bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip
	FROM et_historicaldealinfo(p_dealname, p_date) a
	LEFT JOIN historical_markit_prices(p_date) b ON coalesce(a.ET_LoanXID, a.loanxid)=b.loanxid
	LEFT JOIN historical_bloomberg_corp(p_date) c ON coalesce(a.cusip, a.ET_cusip)=c.cusip
	WHERE a.ReinvFlag IS NOT TRUE
	GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
	a.frequency, a.figi, a.et_loanxid, a.et_cusip ORDER BY issuername;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION et_aggdealinfo_historical(varchar(10), date)
    OWNER TO et_user;

CREATE OR REPLACE FUNCTION et_figi_details(p_figis VARIADIC varchar(12)[])
       RETURNS TABLE(orig_moody text, curr_moody text, empty1 text, issuer text,
       manager text, empty2 text, spread float, coupon float, issuedate date,
       reinvenddate date, maturity date, stale_percentage float,
       cdo_percentage float, wap_basis float, portfolio_spread_5y float,
       portfolio_spread float, subordination float, thickness float,
       mvoc float, mvcoverage float,
       empty3 text, pricingdate date, delta float, duration float, wal float, price float) AS $$
       BEGIN
       RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager, NULL,
       a.Spread/100, a.Coupon/100, b.deal_issue_date, b.reinv_end_date, b.Maturity,
       e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), e.wapbasis,
       e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, d.mvoc, d.mvcoverage,
       NULL, d.updatedate,
       d.delta, d.duration, d.wal, d.price
       FROM latest_tranche_universe a LEFT JOIN latest_clo_universe b
       ON a.dealname = b.dealname
       LEFT JOIN latest_deal_model_numbers e
       ON a.dealname = e.dealname
       RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_figis, 1) AS id) c
       ON c.figi = a.figi
       LEFT JOIN latest_tranche_model_numbers d ON d.figi = c.figi ORDER BY c.id;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION et_tranche_details(varchar(12)[])
    OWNER TO et_user;

CREATE OR REPLACE FUNCTION historical_tranche_details(p_date date, p_figis VARIADIC varchar(12)[])
       RETURNS TABLE(orig_moody text, curr_moody text, issuer text,
       manager text, spread float, coupon float, issuedate date,
       reinvenddate date, maturity date, stale_percentage float,
       cdo_percentage float, wap_basis float, portfolio_spread_5y float,
       portfolio_spread float, subordination float, thickness float,
       mvoc float, mvcoverage float, pricingdate date, delta float,
       duration float, wal float, price float) AS $$
       BEGIN
       RETURN QUERY SELECT a.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager,
       a.spread/100, a.coupon/100, deal_issue_date, d.reinv_end_date, d.maturity,
       stalepercentage, cdopercentage, wapbasis, dealspread5y, dealspread, curr_attach/100,
       (curr_detach-curr_attach)/100, b.mvoc, b.mvcoverage, b.updatedate,
       b.delta, b.duration, b.wal, b.price
       FROM (SELECT DISTINCT ON (figi) * FROM tranche_ref LEFT JOIN tranche_update USING (id)
             WHERE updatedate<=p_date ORDER by figi, updatedate DESC) a
       LEFT JOIN
            (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date
                    ORDER by figi, updatedate DESC) b
       USING (figi)
	   LEFT JOIN (
            SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date
             ORDER by dealname, updatedate DESC) c USING (dealname)
	   LEFT JOIN deal_indicative USING (dealname)
       LEFT JOIN (
            SELECT DISTINCT ON (dealname) * FROM clo_universe WHERE "Latest Update" <= p_date
             ORDER by dealname, "Latest Update" DESC) d USING (dealname)
	     RIGHT JOIN (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) e USING (figi) ORDER BY e.id;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION historical_tranche_details(varchar(12)[])
    OWNER TO et_user;

CREATE OR REPLACE FUNCTION historical_tranche_risk(p_date date, p_figis VARIADIC varchar(12)[])
       RETURNS TABLE(duration float, delta float, price float, pricingdate date) AS $$
       BEGIN
       RETURN QUERY SELECT b.duration, b.delta, b.price, b.updatedate FROM
       (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) a
       LEFT JOIN (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date
       ORDER BY figi, updatedate DESC)
        AS b USING (figi) ORDER BY id;
       END
$$ LANGUAGE plpgsql;


ALTER FUNCTION historical_tranche_risk(date, varchar(12)[])
    OWNER TO et_user;


CREATE OR REPLACE FUNCTION dealname_from_figi(p_figis VARIADIC varchar(12)[])
       -- NOT USED anymore, wasn't working very well
       RETURNS TABLE(p_dealname varchar(10)) AS $$
       BEGIN
       RETURN QUERY SELECT dealname FROM dealfigimapping a
       RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_cusips, 1) AS id) b
       USING (figi) ORDER BY b.id;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION dealname_from_figi(varchar(12)[])
      OWNER TO et_user;

CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10))
       RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float,
       	             p_orig_thickness float, p_curr_thickness float) AS $$
       DECLARE
       	       cusip_row RECORD;
	       curr_cumsum_cusip_bal float;
	       orig_cumsum_cusip_bal float;
	       curr_adjusted_collat_bal float;
	       orig_adjusted_collat_bal float;
	       curr_deal_bal float;
	       defaulted_price float:=0.7;
	       curr_subordination float[];
	       orig_subordination float[];
	       curr_thickness float[];
	       orig_thickness float[];
	       deal_info RECORD;
	       i int := 1;
	       previous_moody text;
	       cusips varchar(9)[];
       BEGIN
       SELECT "Principal Bal", "Orig Collat Bal", "Curr Collat Bal", "Defaulted Bal", "Orig Deal Bal", "Curr Deal Bal" FROM latest_clo_universe where dealname = p_dealname INTO deal_info;
       curr_adjusted_collat_bal := deal_info."Curr Collat Bal" + deal_info."Principal Bal" -
       					    (1-defaulted_price)*deal_info."Defaulted Bal";
       orig_adjusted_collat_bal := deal_info."Orig Collat Bal";
       curr_cumsum_cusip_bal := 0;
       orig_cumsum_cusip_bal := 0;
       FOR cusip_row in SELECT a.* from latest_cusip_universe a JOIN
       	   (SELECT generate_subscripts("Deal Cusip List",1) AS cusip_id,
       	       unnest("Deal Cusip List") AS cusip
	       FROM latest_clo_universe WHERE dealname=p_dealname) b
	   ON a.cusip = b.cusip ORDER BY b.cusip_id LOOP
	   curr_cumsum_cusip_bal := curr_cumsum_cusip_bal + cusip_row.curr_balance;
	   orig_cumsum_cusip_bal := orig_cumsum_cusip_bal + cusip_row.orig_balance;

	   IF deal_info."Curr Deal Bal" = 0 THEN
	      curr_subordination := curr_subordination || cast(NULL AS float);
	   ELSE
		curr_subordination := curr_subordination || (curr_adjusted_collat_bal-curr_cumsum_cusip_bal)/curr_adjusted_collat_bal;
	   END IF;
	   orig_subordination := orig_subordination || (orig_adjusted_collat_bal - orig_cumsum_cusip_bal)/orig_adjusted_collat_bal;
	   cusips := cusips || cusip_row.Cusip;
	   IF i=1 THEN
	     curr_thickness := curr_thickness || 1 - curr_subordination[i];
	     orig_thickness := orig_thickness || 1 - orig_subordination[i];
	   ELSE
		curr_thickness := curr_thickness || curr_subordination[i-1] - curr_subordination[i];
	   	orig_thickness := orig_thickness || orig_subordination[i-1] - orig_subordination[i];
	   END IF;
	   if i>1 THEN
	      IF cusip_row.orig_moody = previous_moody THEN
	      	 curr_subordination[i-1] := curr_subordination[i];
	   	 orig_subordination[i-1] := orig_subordination[i];
	      	 IF i=2 THEN
	     	    curr_thickness[i-1] := 1 - curr_subordination[i-1];
	     	    orig_thickness[i-1] := 1 - orig_subordination[i-1];
		    curr_thickness[i] := curr_thickness[i-1];
		    orig_thickness[i] := orig_thickness[i-1];
	   	 ELSE
	   	    curr_thickness[i-1] := curr_subordination[i-2] - curr_subordination[i-1];
	            orig_thickness[i-1] := orig_subordination[i-2] - orig_subordination[i-1];
		    curr_thickness[i] := curr_thickness[i-1];
		    orig_thickness[i] := orig_thickness[i-1];
	   	 END IF;
	      END IF;
	   END IF;
	   i := i+1;
	   previous_moody := cusip_row.orig_moody;
       END LOOP;
       RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination), unnest(orig_thickness), unnest(curr_thickness);
       END;
       $$ LANGUAGE plpgsql;


CREATE TABLE et_deal_model_numbers(
       dealname varchar(10),
       cdopercentage float,
       stalepercentage float,
       dealspread float,
       dealspread5y float,
       marketvalue float,
       updatedate date,
       wapbasis float,
       PRIMARY KEY(dealname, updatedate)
       );

CREATE TABLE et_tranche_model_numbers(
       figi varchar(12),
       price float,
       wal float,
       duration float,
       delta float,
       updatedate date,
       mvoc float,
       mvcoverage float,
       PRIMARY KEY(figi, updatedate)
       );

GRANT ALL ON et_deal_model_numbers TO et_user;
GRANT ALL ON et_tranche_model_numbers TO et_user;


CREATE VIEW latest_deal_model_numbers AS
       SELECT b.*
       FROM (SELECT MAX(updatedate) AS latestdate, dealname FROM et_deal_model_numbers GROUP BY dealname) a
       JOIN et_deal_model_numbers b ON a.dealname = b.dealname AND a.latestdate= b.updatedate
       ORDER by dealname asc;

GRANT ALL ON latest_deal_model_numbers TO et_user;

CREATE VIEW latest_tranche_model_numbers AS
       SELECT b.*
       FROM (SELECT MAX(updatedate) AS latestdate, figi FROM et_tranche_model_numbers GROUP BY figi) a
       JOIN et_tranche_model_numbers b ON a.figi = b.figi AND a.latestdate = b.updatedate
       ORDER by figi asc;

GRANT ALL ON latest_tranche_model_numbers TO et_user;

CREATE TABLE color(
       ListDate date,
       ListInfo text,
       figi varchar(12),
       Notional float,
       Indications text,
       Cover text,
       ListColor text,
       Bid text,
       Bid_note text,
       PRIMARY KEY(figi, Notional, ListDate, ListInfo)
);

GRANT ALL ON color TO et_user;

CREATE VIEW latest_color AS
       SELECT b.*
       FROM (SELECT MAX(ListDate) AS latestdate, cusip FROM color GROUP BY figi) a
       JOIN color b ON a.figi = b.figi AND a.latestdate=b.listdate;

GRANT ALL ON latest_color TO et_user;