path: root/sql/et_tables.sql

CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT');

CREATE TABLE et_collateral (
    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,
    Cusip varchar(9),
    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,
    PRIMARY KEY (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 (
       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 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),
       industry varchar(50),
       initial_amount float(15),
       initial_spread float(7),
       maturity date,
       created_time date,
       modified_time date
);


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');

CREATE TABLE bloomberg_corp (
       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,
       PRIMARY KEY (Cusip, 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 (
       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,
       PRIMARY KEY (dealname, "Latest Update")
);

GRANT ALL ON clo_universe TO et_user;

CREATE TABLE cusip_universe (
       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,
       PRIMARY KEY(Cusip)
);

GRANT ALL ON cusip_universe TO et_user;

CREATE VIEW latest_clo_universe AS
       SELECT b.*
       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"
       ORDER by dealname asc;

GRANT ALL ON latest_clo_universe TO et_user;

CREATE TABLE dealcusipmapping (
       dealname varchar(10),
       Cusip varchar(9),
       PRIMARY KEY(Cusip)
);

GRANT ALL ON dealcusipmapping 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;

GRANT ALL ON latest_markit_prices TO et_user;

CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date)
       RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
       currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
       grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
       secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
       et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
       userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
       defaultedflag boolean, covlite text, iscdo boolean) AS $$
       DECLARE latestdate date;
       BEGIN
	RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname
	FROM et_collateral GROUP BY et_collateral.dealname) b
	JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION best_price (price1 float, pricedt1 date, price2 float, pricedt2 date)
       RETURNS float
AS $$
   if price1 is None:
      return price2
   if price2 is None:
      return price1
   return price1 if pricedt1> pricedt2 else price2
$$ LANGUAGE plpythonu;

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), cusip varchar(9),
       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)) AS price,
	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
	a.loanxid, a.cusip, (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_collateral a
	left join latest_markit_prices b on a.loanxid=b.loanxid left
	join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname
	group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
	a.frequency, a.cusip ORDER BY issuername;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date date)
       RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
       currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
       grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
       secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
       et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
       userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
       defaultedflag boolean, covlite text, iscdo boolean) 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_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 FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$
       BEGIN
       SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date)
       RETURNS TABLE(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 integer, stm float, pricingdate date) AS $$
       BEGIN
       RETURN QUERY SELECT b.loanxid, b.issuer, b.dealname, b.facility, b.industry, b.sp,
       b.moodys, b.amount, b.maturity, b.spread, b.bid, b.offer, b.depth, b.stm,
       a.latestdate
       FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c
       WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a
       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
       END;
       $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date)
  RETURNS TABLE(cusip character varying, price double precision, pricingdate date, issuer text, maturity date, coupon double precision, coupontype bloomberg_cpntype, frequency integer, spread double precision, libor_floor double precision, loan_margin double precision, covlite boolean, secondlien boolean, defaulted boolean, source bloomberg_source) AS
$BODY$
       BEGIN
       RETURN QUERY SELECT b.* FROM
       (SELECT MAX(c.pricingdate) AS latestdate, c.cusip FROM bloomberg_corp c
       WHERE c.pricingdate<=p_date GROUP BY c.cusip) a
       JOIN bloomberg_corp b ON a.cusip = b.cusip AND a.latestdate= b.pricingdate;
       END;
       $BODY$
  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), cusip varchar(9),
       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)) AS price,
	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
	a.loanxid, a.cusip, (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_latestdealinfo(p_dealname, p_date) a
	left join historical_markit_prices(p_date) b on a.loanxid=b.loanxid left
	join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname
	group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
	a.frequency, a.cusip, 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_cusip_details(p_cusips VARIADIC varchar(10)[])
       RETURNS TABLE(orig_moody text, curr_moody text, empty1 unknown, issuer text,
       manager text, empty2 unknown, spread float, issuedate date,
       reinvenddate date, maturity date, stale_percentage float,
       cdo_percentage float, wap_basis float, portfolio_spread float,
       subordination float, thickness float ) AS $$
       BEGIN
       RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL,
       a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity,
       CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float),
       a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100
       FROM cusip_universe a LEFT JOIN latest_clo_universe b
       ON a.dealname = b.dealname
       JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c
       ON a.cusip = c.cusip ORDER BY c.id;
       END;
       $$ LANGUAGE plpgsql;

ALTER FUNCTION et_cusip_details(varchar(10)[])
    OWNER TO et_user;