-- -*- 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 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 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_contact3 text,
       valuation_email3 text,
       valuation_contact4 = text,
       valuation_email4 = text,
       notes text);

CREATE INDEX ON counterparties(name);

CREATE TABLE bonds(id serial primary key,
       deal_id varchar(28),
       lastupdate timestamp,
       action action,
       folder bond_strat,
       custodian varchar(12),
       cashaccount varchar(10),
       cp_code varchar(12) REFERENCES counterparties(code),
       trade_date date,
       settle_date date,
       cusip varchar(9),
       isin varchar(12),
       description varchar(32),
       faceamount float,
       price float,
       accrued float,
       asset_class asset_class,
       ticket text,
       principal_payment float,
       accrued_payment float);

CREATE TABLE position(date date,
       identifier varchar(12) REFERENCES securities(identifier)
       notional float,
       coupon float,
       factor float,
       price float,
       market_value_local float,
       market_value_usd float,
       accrued float,
       days_accrued float,
       start_accrued_date date,
       factor_pay_date date,
       paydown float,
       writedown float,
       unsettled_principal float,
       unsettled_interest float,
       PRIMARY KEY(identifier, date));

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

CREATE TABLE marks(date date,
       identifier varchar(12) REFERENCES securities(identifier),
       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 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_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,
              last_settle_date date, principal_payment float, accrued_payment float, 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,
  settle_date, temp.principal_payment, temp.accrued_payment, 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 MATERIALIZED VIEW factors_history AS
WITH temp AS
(SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, c.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 asc))
SELECT date, identifier, principal_bal/face_amount AS factor, principal/face_amount*100 AS principal,
interest/face_amount*100 AS interest, losses/face_amount*100 AS losses, temp.coupon
FROM temp JOIN securities USING (identifier);
CREATE UNIQUE INDEX factors_history_pkey ON factors_history(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 date<=p_date ORDER BY identifier, 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;

SELECT identifier, securities.description,notional, securities.coupon,
price, price * notional* coalesce(factor,1) AS local_market_value,
notional * coalesce(factor,1)*yearfrac(start_accrued_date, '2015-08-06', day_count) *
securities.coupon/100. AS int_acc
FROM list_positions('2015-08-06', 'CSO')
JOIN securities USING (identifier)
LEFT JOIN factors_history('2015-08-06') USING (identifier)
LEFT JOIN list_marks('2015-08-06') USING (identifier)
ORDER by identifier asc;