path: root/sql/dawn.sql

-- -*- mode: sql; sql-product: postgres; -*-
CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH',
       'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU',
       'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG',
       'M_MTG_SD', 'M_MTG_PR');

CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS',
       'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR',
       'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS',
       'IGOPTDEL', 'HYOPTDEL');

CREATE TYPE swaption_strat AS ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC');

CREATE TYPE repo_strat AS ENUM('');

CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared');

CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL');
CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN');
CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp');
CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365');
CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding',
       'Second-Day-After', 'End-of-Month');
CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');

CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS');
CREATE TYPE repo_type AS ENUM('REPO', 'REVERSE REPO');
CREATE TYPE swaption_type AS ENUM('PAYER', 'RECEIVER');

CREATE TYPE isda AS ENUM('ISDA2014', 'ISDA2003Cred');
CREATE TYPE protection AS ENUM('Buyer', 'Seller');

CREATE TYPE call_notice AS ENUM('24H', '48H', '3D', '4D', '5D', '6D',
'1W', '8D', '9D', '10D', '2W', '1M', '2M');

CREATE TABLE counterparties(code varchar(12) primary key,
       name text,
       city text,
       state varchar(2),
       location text,
       dtc_number integer,
       sales_contact text,
       sales_email text,
       sales_phone text,
       valuation_contact1 text,
       valuation_email1 text,
       valuation_contact2 text,
       valuation_email2 text,
       valuation_contact3 text,
       valuation_email3 text,
       valuation_contact4 text,
       valuation_email4 text,
       notes text,
       instructions text);

CREATE INDEX ON counterparties(name);

CREATE TABLE bonds(id serial primary key,
       dealid varchar(28),
       lastupdate timestamp DEFAULT now(),
       action action,
       folder bond_strat NOT NULL,
       custodian varchar(12) NOT NULL,
       cashaccount varchar(10) NOT NULL,
       cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
       trade_date date NOT NULL,
       settle_date date NOT NULL,
       cusip varchar(9),
       isin varchar(12),
       identifier varchar(12),
       description varchar(32) NOT NULL,
       buysell bool NOT NULL,
       faceamount float NOT NULL,
       price float NOT NULL,
       accrued float NOT NULL,
       asset_class asset_class,
       ticket text,
       principal_payment float,
       accrued_payment float,
       CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL));

CREATE TRIGGER dealid
       AFTER INSERT ON bonds
       FOR EACH ROW
       EXECUTE PROCEDURE auto_dealid();


CREATE TABLE cds(id serial primary key,
       dealid varchar(28),
       lastupdate timestamp DEFAULT now(),
       action action,
       folder cds_strat NOT NULL,
       custodian varchar(12) NOT NULL,
       cashaccount varchar(10) NOT NULL,
       cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
       trade_date date NOT NULL,
       effective_date date NOT  NULL,
       maturity date NOT NULL,
       currency currency NOT NULL,
       payment_rolldate bus_day_convention NOT NULL,
       notional float NOT NULL,
       fixed_rate float NOT NULL,
       day_count day_count NOT NULL,
       frequency smallint NOT NULL,
       protection protection NOT NULL,
       security_id varchar(12) NOT NULL,
       security_desc varchar(32) NOT NULL,
       upfront float NOT NULL,
       upfront_settle_date date NOT NULL,
       swap_type swap_type NOT NULL,
       attach smallint,
       detach smallint,
       clearing_facility varchar(12),
       isda_definition isda,
       termination_date date DEFAULT NULL,
       termination_amount float DEFAULT NULL,
       CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR
                                                (attach IS NOT NULL AND detach IS NOT NULL)));

ALTER TABLE cds OWNER TO dawn_user;

CREATE TRIGGER cds_dealid AFTER INSERT ON cds
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();

CREATE TABLE repo(id serial primary key,
       dealid varchar(28),
       lastupdate timestamp DEFAULT now(),
       action action,
       folder bond_strat NOT NULL,
       custodian varchar(12) NOT NULL,
       cashaccount varchar(10) NOT NULL,
       cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
       trade_date date NOT NULL,
       settle_date date NOT NULL,
       cusip varchar(9),
       isin varchar(12),
       identifier varchar(12),
       description varchar(32) NOT NULL,
       transaction_indicator repo_type NOT NULL,
       faceamount float NOT NULL,
       price float NOT NULL,
       currency currency NOT NULL,
       expiration_date date,
       weighted_amount float,
       haircut float,
       repo_rate float NOT NULL,
       call_notice call_notice,
       daycount day_count,
       ticket text
       CHECK ( (haircut is NOT NULL AND weighted_amount is NULL) OR
               (haircut is NULL AND weighted_amount is NOT NULL) );
       CHECK (cusip is NOT NULL OR isin is NOT NULL)
);

CREATE TRIGGER repo_dealid AFTER INSERT ON repo
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();

ALTER TABLE repo OWNER TO dawn_user;

CREATE TABLE swaptions(id serial PRIMARY KEY,
       dealid varchar(28),
       lastupdate timestamp DEFAULT now(),
       action action,
       folder swaption_strat NOT NULL,
       custodian varchar(12) NOT NULL,
       cashaccount varchar(10) NOT NULL,
       cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
       trade_date date NOT NULL,
       settle_date date NOT NULL,
       buysell bool NOT NULL,
       notional float NOT NULL,
       swaption_type swaption_type NOT NULL,
       strike float NOT NULL,
       price float NOT NULL,
       expiration_date date NOT NULL,
       initial_margin_percentage float,
       security_id varchar(12) NOT NULL,
       security_desc varchar(32) NOT NULL,
       maturity date NOT NULL,
       currency currency NOT NULL,
       fixed_rate float NOT NULL);


CREATE OR REPLACE FUNCTION auto_dealid()
    RETURNS TRIGGER AS $$
    DECLARE stub text;
            sqlstr text;
    BEGIN
      sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %s WHERE id = %L AND dealid is NULL';

       IF (TG_TABLE_NAME = 'bonds') THEN
          stub := 'SC_';
          sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id,
identifier = COALESCE(identifier, cusip, isin)', NEW.id);
       ELSIF (TG_TABLE_NAME = 'cds') THEN
          stub := 'SCCDS';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'repo') THEN
          stub := 'SC_REP';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'swaptions') THEN
          stub := 'SWPTN';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       END IF;
       EXECUTE sqlstr USING stub;
       RETURN NEW;
    END;
    $$ language plpgsql;

CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();

ALTER TABLE swaptions OWNER TO dawn_user;

CREATE TABLE securities(identifier varchar(12) PRIMARY KEY,
       cusip varchar(9),
       isin varchar(12),
       description varchar(32),
       face_amount float,
       maturity date,
       floater boolean,
       spread float,
       coupon float,
       frequency smallint,
       day_count day_count,
       first_coupon_date date,
       pay_delay smallint,
       currency currency default 'USD',
       bbg_type bbg_type default 'Mtge',
       asset_class asset_class,
       paid_down date default 'Infinity',
       start_accrued_date date);

ALTER TABLE securities OWNER TO dawn_user;

CREATE TABLE marks(date date,
       identifier varchar(12) REFERENCES securities(identifier) ON DELETE CASCADE ON UPDATE CASCADE,
       price float,
       PRIMARY KEY(identifier, date));

CREATE TABLE cashflow_history(
       identifier varchar(12) REFERENCES securities ON UPDATE CASCADE,
       date date,
       principal_bal float,
       principal float,
       interest float,
       coupon float,
       PRIMARY KEY (identifier, date));

CREATE TABLE risk_numbers(
       identifier varchar(12) REFERENCES securities,
       date date,
       delta float,
       index_delta index_type,
       duration float,
       wal float,
       undiscounted_price float,
       model_price float,
       PRIMARY KEY (identifier, date));

CREATE TABLE fx(date date PRIMARY KEY,
       eurusd float,
       cadusd float);

CREATE TABLE external_marks(
       identifier varchar(12) REFERENCES securities ON UPDATE CASCADE,
       date date,
       mark float,
       source text,
       PRIMARY KEY (identifier, date, source));

CREATE TABLE mark_source_mapping(
       globeop text,
       final text,
       PRIMARY KEY (globeop));

CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False)
RETURNS TABLE(identifier varchar(12), price float) AS $$
BEGIN
  RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN
(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks
WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_marks_var(p_date date, VARIADIC p_identifier varchar(12)[])
RETURNS TABLE(identifier varchar(12), price float) AS $$
BEGIN
  RETURN QUERY SELECT a.identifier, b.price FROM (SELECT unnest(p_identifier) AS identifier) a
  LEFT JOIN
(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks
WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False)
RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float,
undiscounted_price float, model_price float) AS $$
BEGIN
  RETURN QUERY SELECT a.identifier, a.description, b.price, c.delta, c.index_delta,
  c.duration, c.wal, c.undiscounted_price, c.model_price
  FROM list_positions(p_date, assetclass, include_unsettled) a
  LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks
WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier)
  LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers
  WHERE date<=p_date ORDER BY identifier, date DESC) c USING (identifier);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_positions(p_date date,
       p_class asset_class DEFAULT NULL,
       include_unsettled boolean DEFAULT True)
RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat,
              curr_cpn float, start_accrued_date date, last_settle_date date,
              principal_payment float, accrued_payment float, currency currency, daycount day_count,
              bbg_type bbg_type) AS $$
DECLARE sqlquery text;
DECLARE asset_opt text;
DECLARE unsettled_opt text;
BEGIN

  IF p_class is not NULL THEN
	   asset_opt := 'and securities.asset_class=$2 ';
  ELSE
     asset_opt := '';
  END IF;
  IF include_unsettled THEN
     unsettled_opt = 'or settle_date>=$1';
  ELSE
     unsettled_opt = '';
  END IF;
  sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder,
          principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) )
          OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1)
  SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder,
  securities.coupon, start_accrued_date, settle_date, temp.principal_payment,
  temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type
  FROM temp LEFT JOIN securities USING (identifier)
  WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt
  ||' ORDER BY identifier, settle_date desc';
  RETURN QUERY EXECUTE sqlquery USING p_date, p_class;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_positions_range(start_date date,
          end_date date,
          p_class asset_class DEFAULT NULL)
RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat,
              curr_cpn float, start_accrued_date date, last_settle_date date,
              principal_payment float, accrued_payment float, currency currency, daycount day_count,
              bbg_type bbg_type) AS $$
DECLARE sqlquery text;
DECLARE asset_opt text;
DECLARE unsettled_opt text;
BEGIN

  sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder,
          principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) )
          OVER (PARTITION BY bonds.identifier) notional FROM bonds
          WHERE trade_date <=$2)
  SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder,
  securities.coupon, start_accrued_date, settle_date, temp.principal_payment,
  temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type
  FROM temp LEFT JOIN securities USING (identifier)
  WHERE (temp.notional>0 or (temp.notional==0 and settle_date>=$1)) AND paid_down>$1 '||asset_opt
  ||' ORDER BY identifier, settle_date desc';
  RETURN QUERY EXECUTE sqlquery USING start_date, end_date, p_class;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE
(description varchar(32), identifier varchar(12), notional float, price float,
strategy bond_strat, factor float, local_market_value float, usd_market_value float,
curr_cpn float, int_acc float, last_pay_date date, principal_payment float,
accrued_payment float, last_settle_date date) AS $$
BEGIN
  RETURN QUERY
  SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1),
      c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END),
      c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate,
      b.coupon,
      a.notional * coalesce(b.factor,1) * fxrate *
      yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100.,
      b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date
  FROM list_positions(p_date, p_assetclass) a
  LEFT JOIN factors_history(p_date) b USING (identifier)
  LEFT JOIN list_marks(p_date, True) c USING (identifier)
  LEFT JOIN fx_rate(p_date) USING (currency)
  ORDER by identifier asc;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW orig_cds AS
SELECT DISTINCT ON (dealid) dealid, folder, index, series, version, tenor, fixed_rate, notional, upfront,
protection, attach, detach, trade_date, upfront_settle_date FROM cds
JOIN index_desc ON index_desc.redindexcode=cds.security_id AND index_desc.maturity=cds.maturity
ORDER BY dealid, trade_date;

CREATE OR REPLACE VIEW external_marks_mapped AS
select date, identifier, mark, b.final as source from external_marks a
left join mark_source_mapping b on a.source = b.globeop order by a.date asc;

CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL)
RETURNS text AS $$
DECLARE
  query text;

BEGIN
  query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity,
cds.fixed_rate, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN -1 ELSE 1 END))
OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach)  AS notional
FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date> $1) and cds.trade_date <=$1 %s)
SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0';

IF p_type = 'tranche' THEN
   RETURN format(query, 'cds.attach, cds.detach,', 'AND cds.attach is NOT NULL', ',tmp.attach');
ELSIF p_type = 'cds' THEN
   RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', '');
ELSIF p_type = 'abs' THEN
   RETURN format(query, '', 'AND cds.folder=''MBSCDS''', '');
ELSE
   RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach');
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_cds_positions (p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, notional float) AS $$
BEGIN
RETURN QUERY EXECUTE query_positions('cds') USING p_date;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_tranche_positions(p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, attach smallint, detach smallint, notional float) AS $$
BEGIN
RETURN QUERY EXECUTE query_positions('tranche') USING p_date;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_abscds_positions(p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, notional float) AS $$
BEGIN
RETURN QUERY EXECUTE query_positions('abs') USING p_date;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_cds_marks(p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              notional float, factor float, coupon float, duration float, clean_nav float, accrued float) AS $$
DECLARE
  days integer;
BEGIN
days:=days_accrued(p_date);
RETURN QUERY
WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact
              FROM list_cds_positions(p_date) a
              LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)),
index_price AS (SELECT index, series, version, tenor, closeprice, index_quotes.duration
                FROM index_quotes WHERE date=p_date)
SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100,
index_price.duration, (index_price.closeprice/100.-1)*temp.notional*temp.fact,
temp.notional*temp.fixed_rate/100.*temp.fact*days/360
FROM temp
LEFT JOIN index_price USING (index, series, version, tenor);
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_tranche_marks(p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              notional float, factor float, coupon integer, clean_nav float, accrued float) AS $$
DECLARE
  days integer;
BEGIN
days:=days_accrued(p_date);
RETURN QUERY
WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor,
             tranche_factor(a.attach, a.detach, c.indexfactor, c.cumulativeloss) AS fact
              FROM list_tranche_positions(p_date) a
              LEFT JOIN index_version c ON a.security_id=c.redindexcode
              LEFT JOIN index_desc d USING (redindexcode, maturity)),
tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, attach, detach, upfront_mid, tranche_spread FROM
              markit_tranche_quotes WHERE quotedate<=p_date ORDER by basketid, tenor, attach, detach, quotedate desc)
SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, tranche_spread::integer,
-upfront_mid*temp.notional*temp.fact,
temp.notional*tranche_spread/10000.*temp.fact*days/360
FROM temp
LEFT JOIN tranche_price USING (basketid, attach, detach, tenor);
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_abscds_marks(p_date date)
RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date,
              notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$
BEGIN
RETURN QUERY
WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date, d.cusip FROM list_abscds_positions(p_date) a
LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price
           FROM marks WHERE date<=p_date ORDER BY identifier, date desc) b
ON a.security_id=b.identifier
LEFT JOIN factors_history(p_date) c ON a.security_id=c.identifier
LEFT JOIN securities d ON a.security_id=d.identifier)
SELECT temp.security_id, temp.cusip, temp.security_desc, temp.maturity, temp.notional, temp.factor,
temp.fixed_rate, temp.notional*temp.factor*(temp.price-100)/100,
-yearfrac(temp.start_accrued_date, p_date+1, 'ACT/360')*temp.fixed_rate/100*temp.notional*temp.factor
FROM temp;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function days_accrued(p_date date)
RETURNS integer AS $$
FROM dates import days_accrued
RETURN days_accrued(p_date)
$$ LANGUAGE plpython2u;

CREATE MATERIALIZED VIEW factors_history AS
 WITH temp AS (
         SELECT c.date,
            c.identifier,
            c.principal,
            c.principal_bal,
            c.interest,
            lead(c.coupon) OVER w AS coupon,
            (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses
           FROM cashflow_history c
          WINDOW w AS (PARTITION BY c.identifier ORDER BY c.date)
        )
 SELECT temp.date AS last_pay_date,
    temp.date-securities.pay_delay AS prev_cpn_date,
    temp.identifier,
    temp.principal_bal / securities.face_amount AS factor,
    temp.principal / securities.face_amount * 100::float AS principal,
    temp.interest / securities.face_amount * 100::float AS interest,
    temp.losses / securities.face_amount * 100::float AS losses,
    COALESCE(temp.coupon, securities.coupon) AS coupon
   FROM temp
     JOIN securities USING (identifier);

CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier);

CREATE OR REPLACE function factors_history(p_date date)
RETURNS SETOF factors_history AS $$
BEGIN
RETURN QUERY
SELECT DISTINCT ON (identifier) * FROM factors_history
       WHERE prev_cpn_date<=p_date ORDER BY identifier, prev_cpn_date desc;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION isleapyear (
  D date
) RETURNS boolean
AS $$
DECLARE
  y INTEGER;
BEGIN
 y := extract (year from D);
 if (y % 4) != 0 then
   return false;
 end if;

 if (y % 400) = 0 then
   return true;
 end if;

 return (( y % 100) != 0);
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION yearfrac(date1 date, date2 date, daycount day_count)
RETURNS float AS $$
DECLARE
  factor float;
  y1 integer;
  y2 integer;
  m1 integer;
  m2 integer;
  d1 integer;
  d2 integer;
BEGIN

IF daycount='30/360' THEN
   y1 := extract(YEAR FROM date1);
   y2 := extract(YEAR FROM date2);
   m1 := extract(MONTH FROM date1);
   m2 := extract(MONTH FROM date2);
   d1 := extract(DAY FROM date1);
   d2 := extract(DAY FROM date2);
   IF d2=31 and (d1=30 or d1=31) THEN
      d2:=30;
   END IF;
   IF d1=31 THEN
      d1:=30;
   END IF;
   factor:= (360*(y2-y1) + 30*(m2-m1)+d2-d1)/360.;
ELSIF daycount='ACT/365' THEN
  factor:=(date2-date1)/365.;
ELSIF daycount='ACT/360' THEN
  factor:=(date2-date1)/360.;
ELSIF daycount='ACT/ACT' THEN
  IF isleapyear(date1) THEN
    factor:=(date2-date1)/366.;
  ELSE
    factor:=(date2-date1)/365.;
  END IF;
END IF;
RETURN factor;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint,
                  index_factor float, cumulativeloss float)
RETURNS float AS $$
DECLARE
  newattach float;
  newdetach float;

BEGIN
newattach:=LEAST(GREATEST((attach-100*cumulativeloss)/index_factor, 0), 1);
newdetach:=LEAST(GREATEST((detach-100*cumulativeloss)/index_factor, 0), 1);
RETURN (newdetach-newattach)/(detach-attach)*index_factor;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$
BEGIN
  RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency,
  unnest(Array[1, eurusd, cadusd]) FROM fx WHERE date<=p_date ORDER by date desc LIMIT 3;
END;
$$ LANGUAGE plpgsql;


-- Not sure how to map enums so use text for now
CREATE FOREIGN TABLE priced(
     cusip varchar(9),
     model_version smallint,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_RnW float,
     pv_io float,
     pv_po float,
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float,
     delta_hpi float,
     delta_ir float,
     delta_ir_io float,
     delta_ir_po float,
     delta_mult float,
     delta_yield float,
     delta_quantile float,
     delta_RnW float,
     tot_gamma_hpi float,
     tot_gamma_ir float,
     tot_gamma float)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');


-- We want to use INHERITS here, but will only be available on 9.5
CREATE FOREIGN TABLE priced_orig_ntl(
     cusip varchar(9),
     model_version smallint,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_RnW float,
     pv_io float,
     pv_po float,
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float,
     delta_hpi float,
     delta_ir float,
     delta_ir_io float,
     delta_ir_po float,
     delta_mult float,
     delta_yield float,
     delta_quantile float,
     delta_RnW float,
     tot_gamma_hpi float,
     tot_gamma_ir float,
     tot_gamma float)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

CREATE FOREIGN TABLE priced_percentiles(
     cusip varchar(9),
     model_version smallint,
     percentile float,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_io float,
     pv_po float,
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

CREATE FOREIGN TABLE priced_percentiles_orig_ntl(
     cusip varchar(9),
     model_version smallint,
     percentile float,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_io float,
     pv_po float,
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

CREATE FOREIGN TABLE index_desc(
       basketid integer,
       index text,
       series smallint,
       version smallint,
       indexfactor float,
       cumulativeloss float,
       lastdate date,
       redindexcode text,
       tenor tenor,
       maturity date,
       coupon integer)
   SERVER postgresql_server;

CREATE FOREIGN TABLE index_version(
       basketID serial,
       Index index_type,
       Series smallint,
       Version smallint,
       IndexFactor float,
       CumulativeLoss float,
       lastdate date,
       redindexcode text)
  SERVER postgresql_server;

CREATE FOREIGN TABLE index_maturity(
       index index_type,
       series smallint,
       tenor tenor,
       maturity date)
  SERVER postgresql_server;

CREATE FOREIGN TABLE index_quotes(
       date date,
       index index_type,
       series smallint,
       version smallint,
       tenor tenor,
       closeprice float,
       closespread float,
       modelprice float,
       modelspread float,
       adjcloseprice float,
       adjmodelprice float,
       duration float,
       theta float)
SERVER postgresql_server;

CREATE FOREIGN TABLE markit_tranche_quotes(
       quotedate date,
       basketid integer,
       tenor tenor,
       attach smallint,
       detach smallint,
       upfront_bid float,
       upfront_mid float,
       upfront_ask float,
       tranche_spread smallint,
       index_price float)
SERVER postgresql_server;



-- #bonds that get written down
-- update securities set identifier='073879R75_A' where identifier='073879R75';
-- update bonds set identifier='073879R75_A' where identifier='073879R75';
-- refresh materialized view factors_history;

CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIADIC p_cusip varchar(9)[])
RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float,
        percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float,
        io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float, delta_rnw float, v1pv_RnW float) AS $$
DECLARE
  query text;
  opt_constraint text;
BEGIN
IF NOT orig_flag THEN
   opt_constraint := 'AND normalization =''current_notional''';
ELSE
  opt_constraint := '';
END IF;
query:=
'WITH left_table AS (
     WITH temp AS (SELECT * from %I where date(timestamp)=$1)
          SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield,
                 c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po,
                 c.delta_hpi, c.delta_RnW, a.pv_RnW as v1pv_RnW
          FROM (SELECT * FROM temp WHERE model_version=1) a,
               (SELECT * FROM temp WHERE model_version=2) b,
               (SELECT * FROM temp WHERE model_version=3) c
          WHERE a.cusip = b.cusip AND a.cusip=c.cusip),
     right_table AS (
     WITH temp AS(
          SELECT cusip, PV, percentile
          FROM %I
          WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day''
                AND model_version=3
                AND percentile in (5, 25, 50, 75, 95)' || opt_constraint
                 ||')
          SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95
          FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a,
               (SELECT cusip, PV FROM temp WHERE percentile=25) b,
               (SELECT cusip, PV FROM temp WHERE percentile=50) c,
               (SELECT cusip, PV FROM temp WHERE percentile=75) d,
               (SELECT cusip, PV FROM temp WHERE percentile=95) e
          WHERE a.cusip=b.cusip AND b.cusip=c.cusip and c.cusip=d.cusip and d.cusip=e.cusip and e.cusip=a.cusip)
SELECT left_table.v1, left_table.v2, left_table.v3,
       left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95,
       left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW,
       left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table.delta_RnW, left_table.v1pv_RnW
FROM (SELECT unnest($2) AS cusip) l
LEFT JOIN left_table ON left_table.cusip=l.cusip
LEFT JOIN right_table ON left_table.cusip=right_table.cusip';
IF orig_flag THEN
   query := format(query, 'priced_orig_ntl', 'priced_percentiles_orig_ntl');
ELSE
   query := format(query, 'priced', 'priced_percentiles');
END IF;

RETURN QUERY EXECUTE query USING p_date, p_cusip;
END
$$ LANGUAGE plpgsql;