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,
    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;

-- markit_prices is deprecated (use markit_prices2)
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_prices2 (
       LoanXID varchar(8),
       Bid float,
       Offer float,
       Depth int,
       PricingDate date,
       PRIMARY KEY (LoanXID, PricingDate)
);

ALTER TABLE markit_prices2 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 VIEW latest_markit_prices2 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_prices2 GROUP BY loanxid) a
       JOIN markit_prices2 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_prices2 TO et_user;

CREATE OR REPLACE FUNCTION historical_markit_prices2(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_prices2.loanxid FROM markit_prices2
       WHERE pricingdate <= p_date GROUP BY markit_prices2.loanxid) a
       JOIN markit_prices2 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_prices2(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');

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,
       subordination float,
       thickness float,
       updatedate date,
       PRIMARY KEY(Cusip, updatedate)
);

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 VIEW latest_cusip_universe AS
       SELECT b.*
       FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM cusip_universe GROUP BY cusip) a
       JOIN cusip_universe b ON a.cusip = b.cusip AND a.latestdate= b.updatedate
       ORDER by cusip asc;

GRANT ALL ON latest_cusip_universe TO et_user;

-- CREATE VIEW dealcusipmapping AS
--        SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe;

CREATE VIEW dealcusipmapping AS
       SELECT dealname, cusip from latest_cusip_universe;

GRANT ALL ON dealcusipmapping TO et_user;

-- latest_market_prices is deprecated, (use latest_market_prices2)
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 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_historicaldealinfo (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, userpricedate date, fixedorfloat intex_fixedorfloat,
       defaultedflag boolean, covlite text, iscdo boolean, liborfloor float,
       reinvflag 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_latestdealinfo (p_dealname varchar(10))
       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, userpricedate date, fixedorfloat intex_fixedorfloat,
       defaultedflag boolean, covlite text, iscdo boolean, liborfloor float,
       reinvflag boolean) 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.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_prices2 b ON a.loanxid = b.loanxid
	LEFT JOIN bloomberg_corp c ON a.cusip=c.cusip WHERE a.dealname = p_dealname
	and 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;

-- historical_markit_prices id deprecated (use historical_markit_prices2)
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.*
       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, 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, a.et_loanxid, a.et_cusip
	FROM et_historicaldealinfo(p_dealname, p_date) a
	LEFT JOIN historical_markit_prices2(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 AND 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,
       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.subordination, a.thickness, 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 dealname_from_cusip(p_cusips VARIADIC varchar(10)[])
       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)/deal_info."Curr Deal Bal";
	   END IF;
	   orig_subordination := orig_subordination || (orig_adjusted_collat_bal - orig_cumsum_cusip_bal)/deal_info."Orig Deal 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];
	   	 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];
	   	 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,
       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;
