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

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', 'XO', 'LCDX');
CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS');
CREATE type isda AS ENUM('ISDA2014', 'ISDA2003Cred');
CREATE type protection AS ENUM('Buyer', 'Seller');

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),
       trade_date date NOT NULL,
       settle_date date NOT NULL,
       cusip varchar(9),
       isin varchar(12),
       identifier varchar(12),
       description varchar(32) 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 OR REPLACE FUNCTION mydealid()
    RETURNS TRIGGER AS $$
    BEGIN
       UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id,
    identifier = COALESCE(identifier, cusip, isin) WHERE bonds.id = NEW.id;
       RETURN NEW;
    END;
    $$ language plpgsql;

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


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),
       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,
       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 OR REPLACE FUNCTION auto_cds_dealid()
    RETURNS TRIGGER AS $$
    BEGIN
       UPDATE cds SET dealid = 'SCCDS' ||id WHERE cds.id = NEW.id AND dealid is Null;
       RETURN NEW;
    END;
    $$ language plpgsql;

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

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,
       price float,
       PRIMARY KEY(identifier, date));

CREATE TABLE cashflow_history(
       identifier varchar(12) REFERENCES securities,
       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 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_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_cds_positions (p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, attach smallint,
        detach smallint, notional float) AS $$
BEGIN

RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.attach, cds.detach,
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.trade_date <=p_date)
SELECT DISTINCT ON (security_id, maturity, attach) * FROM tmp WHERE tmp.notional!=0;
END;
$$ LANGUAGE plpgsql;

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::double precision AS principal,
    temp.interest / securities.face_amount * 100::double precision AS interest,
    temp.losses / securities.face_amount * 100::double precision 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;

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;

-- 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 numeric(23,6),
     pv_RnW numeric(23,6),
     pv_io numeric(23,6),
     pv_po numeric(23,6),
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float,
     delta_hpi numeric(23,6),
     delta_ir numeric(23,6),
     delta_ir_io numeric(23,6),
     delta_ir_po numeric(23,6),
     delta_mult numeric(23,6),
     delta_yield numeric(23,6),
     delta_quantile numeric(23,6),
     delta_RnW numeric(23,6),
     tot_gamma_hpi numeric(23,6),
     tot_gamma_ir numeric(23,6),
     tot_gamma numeric(23,6))
     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 numeric(23,6),
     pv_RnW numeric(23,6),
     pv_io numeric(23,6),
     pv_po numeric(23,6),
     modDur float,
     modDur_io float,
     modDur_po float,
     wal float,
     wal_width float,
     wal_io float,
     wal_po float,
     delta_hpi numeric(23,6),
     delta_ir numeric(23,6),
     delta_ir_io numeric(23,6),
     delta_ir_po numeric(23,6),
     delta_mult numeric(23,6),
     delta_yield numeric(23,6),
     delta_quantile numeric(23,6),
     delta_RnW numeric(23,6),
     tot_gamma_hpi numeric(23,6),
     tot_gamma_ir numeric(23,6),
     tot_gamma numeric(23,6))
     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 numeric(23,6),
     pv_io numeric(23,6),
     pv_po numeric(23,6),
     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 numeric(23,6),
     pv_io numeric(23,6),
     pv_po numeric(23,6),
     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 text,
       maturity date)
   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,
       PRIMARY KEY(BasketID)
);

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