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


CREATE TABLE position(date date,
       isin varchar(12),
       cusip varchar(9),
       identifier varchar(12),
       description varchar(32),
       notional float,
       face_amount float,
       coupon float,
       currency currency,
       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,
       bbg_type bbg_type,
       strategy bond_strat,
       asset_class asset_class,
       presettle_principal float,
       presettle_interest float,
       settle_date date,
       PRIMARY KEY(identifier, date));

CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL)
RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat,
              last_settle_date date, notional float) AS $$
DECLARE sqlquery text;
BEGIN
  sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder,
          sum(faceamount*(2*buysell::int-1))
          OVER (PARTITION by bonds.identifier) from bonds where trade_date<=$1)
  SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional
  FROM temp
  WHERE (sum>0 or settle_date>=$1)';
  IF p_class is not NULL THEN
	sqlquery := sqlquery || 'and asset_class=$2';
  END IF;
  sqlquery := sqlquery || 'order by identifier, settle_date';
  RETURN QUERY EXECUTE sqlquery
  USING p_date, p_class;
END;
$$ LANGUAGE plpgsql;