-- -*- mode: sql; sql-product: postgres; -*-
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,
    Liborfloor float,
    ReinvFlag boolean,
    Currency varchar(3),
    Industry text,
    Country text,
    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_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 (
       id_bb_unique text 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)[]);

GRANT ALL ON bloomberg_corp_ref TO et_user;

CREATE TABLE bloomberg_corp(
       id_bb_unique text REFERENCES bloomberg_corp_ref,
       pricingdate date,
       price float,
       loan_margin float,
       amount_outstanding float,
       source bloomberg_source,
       PRIMARY KEY (id_bb_unique, 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,
       maturity date,
       pay_day smallint,
       reinv_end_date date,
       first_pay_date date,
       orig_deal_bal float,
       orig_collat_bal float,
       "Deal Cusip List" text[],
       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,
       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 cusip_ref(
       Cusip varchar(9) PRIMARY KEY,
       ISIN varchar(12),
       bloomberg_ticker text,
       dealname varchar(10),
       tranche text,
       paripassu_tranches text[],
       Orig_Balance float,
       Orig_Moody text,
       Orig_Attach float,
       Orig_Detach float,
       Floater_Index text,
       Spread float,
       type text);

CREATE TABLE cusip_update(
       Cusip varchar(9) REFERENCES cusip_ref,
       curr_balance float,
       factor float,
       coupon float,
       Curr_Moody text,
       Curr_Attach float,
       Curr_Detach float,
       updatedate date,
       PRIMARY KEY(cusip, updatedate));

CREATE OR REPLACE VIEW cusip_universe AS
       SELECT a.*, b.curr_balance, b.factor, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach,
       b.updatedate FROM cusip_ref a
       JOIN cusip_update b USING (cusip);

CREATE OR REPLACE VIEW latest_clo_universe AS
       SELECT b.dealname, "Deal Name", Manager, orig_deal_bal, b."Curr Deal Bal", 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, maturity, reinv_end_date, b."Latest Update",
       "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_cusip_universe AS
       WITH latest_cusip_update AS
         (SELECT DISTINCT ON (cusip) * FROM cusip_update
          ORDER BY cusip, updatedate DESC)
       SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach,
           b.curr_detach, b.updatedate FROM cusip_ref a
       JOIN latest_cusip_update b USING (cusip);

GRANT ALL ON latest_cusip_universe TO et_user;

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, orig_deal_bal, a."Curr Deal Bal", 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, maturity, reinv_end_date, a."Latest Update",
       	   "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_cusip_universe(p_cusip varchar(9), p_date date)
       RETURNS SETOF cusip_universe AS $$
       DECLARE latestdate date;
       BEGIN
        SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe
	WHERE cusip_universe.cusip = p_cusip AND cusip_universe.updatedate<=p_date;
	RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.cusip=p_cusip
	AND a.updatedate=latestdate;
       END;
$$ LANGUAGE plpgsql;

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

CREATE OR REPLACE VIEW dealcusipmapping AS
       SELECT dealname, cusip from cusip_ref;

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;


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), 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, 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.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_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.cusip 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 b.pricingdate, CASE
			WHEN c.cusip is NULL THEN substring(id_bb_unique from 3)::varchar(9)
			ELSE c.cusip
		      END, b.price, c.issuer, c.issue_size,
       b.amount_outstanding, c.covlite, c.secondlien, c.defaulted FROM
       	(SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp
	GROUP BY id_bb_unique) a
       	JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c
	USING (id_bb_unique);

GRANT ALL ON latest_bloomberg_corp TO et_user;

CREATE OR REPLACE VIEW latest_bloomberg_corp2 AS
SELECT b.pricingdate, id_bb_unique, b.price, b.loan_margin, b.source, c.issuer, c.issue_size,
       b.amount_outstanding, c.covlite, c.secondlien, c.defaulted FROM
       	(SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp
	GROUP BY id_bb_unique) a
       	JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c
	USING (id_bb_unique);

GRANT ALL ON latest_bloomberg_corp2 TO et_user;

CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date)
  RETURNS SETOF latest_bloomberg_corp AS $$
       BEGIN
       RETURN QUERY SELECT b.pricingdate, CASE
					   WHEN c.cusip is NULL THEN substring(id_bb_unique from 3)::varchar(9)
					   ELSE c.cusip
		                          END, b.price,
       c.issuer, c.issue_size, b.amount_outstanding, c.covlite, c.secondlien, c.defaulted FROM
       	(SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp WHERE
	pricingdate<=p_date GROUP BY id_bb_unique) a
       	JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c
	USING (id_bb_unique);
       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),
       cusip varchar(9), 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.cusip, 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.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, 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 unknown, 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_cusip_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 cusip, generate_subscripts(p_cusips, 1) AS id) c
       ON c.cusip = a.cusip
       LEFT JOIN latest_cusip_model_numbers d ON d.cusip = c.cusip ORDER BY c.id;
       END;
       $$ LANGUAGE plpgsql;

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

CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIADIC varchar(10)[])
       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 cusip_ref.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager,
       cusip_ref.spread/100, a.coupon/100, deal_issue_date, reinv_end_date, deal_indicative.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 cusip_ref
       LEFT JOIN
            (SELECT DISTINCT ON (cusip) * FROM cusip_update WHERE updatedate<=p_date ORDER by cusip, updatedate DESC) a
       USING (cusip)
       LEFT JOIN
            (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date
                    ORDER by cusip, updatedate DESC) b
       USING (cusip)
	     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)
	     RIGHT JOIN (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) d USING (cusip) ORDER BY d.id;
       END;
       $$ LANGUAGE plpgsql;

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

CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC varchar(10)[])
       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_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) a
       LEFT JOIN (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date
       ORDER BY cusip, updatedate DESC)
        AS b USING (cusip) ORDER BY id;
       END
       $$ LANGUAGE plpgsql;


ALTER FUNCTION historical_cusip_risk(date, varchar(10)[])
    OWNER TO et_user;


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

ALTER FUNCTION dealname_from_cusip(varchar(10)[])
      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_cusip_model_numbers(
       Cusip varchar(9),
       price float,
       wal float,
       duration float,
       delta float,
       updatedate date,
       mvoc float,
       mvcoverage float,
       PRIMARY KEY(Cusip, updatedate)
       );

GRANT ALL ON et_deal_model_numbers TO et_user;
GRANT ALL ON et_cusip_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_cusip_model_numbers AS
       SELECT b.*
       FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM et_cusip_model_numbers GROUP BY cusip) a
       JOIN et_cusip_model_numbers b ON a.cusip = b.cusip AND a.latestdate = b.updatedate
       ORDER by cusip asc;

GRANT ALL ON latest_cusip_model_numbers TO et_user;

CREATE TABLE color(
       ListDate date,
       ListInfo text,
       Cusip varchar(9),
       Notional float,
       Indications text,
       Cover text,
       ListColor text,
       Bid text,
       Bid_note text,
       PRIMARY KEY(Cusip, 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 cusip) a
       JOIN color b ON a.cusip = b.cusip AND a.latestdate=b.listdate;

GRANT ALL ON latest_color TO et_user;