-- -*- mode: sql; sql-product: postgres; -*-
CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_MEZZ', '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', 'M_MTG_CRT_SD', 'CRT_LD', 'CRT_LD_JNR');

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', 'SER_ITRXCURVE',
       'HYEQY', 'HYMEZ', 'HYSNR', 'HYINX',
       'IGEQY', 'IGMEZ', 'IGSNR', 'IGINX',
       'BSPK');

CREATE TYPE swaption_strat AS ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC', 'STEEP');
CREATE TYPE swaption_type AS ENUM('CD_INDEX_OPTION', 'SWAPTION');

CREATE TYPE repo_strat AS ENUM('');

CREATE TYPE future_strat AS ENUM('M_STR_MAV', 'M_MTG_IO', 'M_STR_MEZZ', 'M_MTG_RW',
       'SER_ITRXCURVE', 'M_CSH_CASH');

CREATE TYPE cash_strat AS ENUM('M_CSH_CASH', 'MBSCDSCSH', 'SER_IGCVECSH',
       'SER_ITRXCVCSH', 'CSOCDSCSH', 'IGCDSCSH', 'HYCDSCSH', 'CLOCDSCSH',
       'IGTCDSCSH', 'MACCDSCSH', 'M_STR_MEZZ', 'IRDEVCSH', 'TCSH', 'COCSH');

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', 'JPY', 'GBP');
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 last_period_convention AS ENUM('Adjusted', 'Unadjusted');
CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO', 'BS');
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', 'CD_INDEX_OPTION', 'SWAPTION', 'CREDIT_DEFAULT_SWAP');
CREATE TYPE repo_type AS ENUM('REPO', 'REVERSE REPO');
CREATE TYPE option_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 TYPE settlement_type AS ENUM('Delivery', 'Cash');

CREATE TABLE accounts(
       code varchar(5) PRIMARY KEY,
       name text,
       custodian text,
       cash_account text,
       counterparty varchar(12) REFERENCES counterparties(code))

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) UNIQUE,
       fund fund NOT NULL DEFAULT 'SERCGMAST';
       lastupdate timestamp DEFAULT now(),
       action action NOT NULL,
       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) UNIQUE,
       fund fund NOT NULL DEFAULT 'SERCGMAST',
       lastupdate timestamptz DEFAULT now(),
       action action NOT NULL,
       portfolio portfolio NOT NULL,
       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,
       orig_attach smallint,
       orig_detach smallint,
       attach float,
       detach float,
       clearing_facility varchar(12) DEFAULT NULL,
       isda_definition isda,
       termination_date date DEFAULT NULL,
       termination_amount float DEFAULT NULL,
       termination_cp varchar(12) DEFAULT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
       initial_margin_percentage float DEFAULT NULL,
       index_ref float DEFAULT NULL,
       corr_attach float DEFAULT NULL,
       corr_detach float DEFAULT NULL,
       account_code varchar(5) NOT NULL,
       CONSTRAINT tranche_check CHECK ((
       swap_type IN ('CD_INDEX_TRANCHE',  'BESPOKE') AND
       (orig_attach IS NOT NULL AND orig_detach IS NOT NULL AND clearing_facility IS NULL))
       OR (swap_type='CD_INDEX' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_facility='ICE-CREDIT')
       OR (swap_type='ABS_CDS' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_faciliy IS 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) UNIQUE,
       fund fund NOT NULL DEFAULT 'SERCGMAST',
       lastupdate timestamptz DEFAULT now(),
       action action NOT NULL,
       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) UNIQUE,
       fund fund NOT NULL DEFAULT 'SERCGMAST',
       lastupdate timestamptz DEFAULT now(),
       action action NOT NULL,
       portfolio portfolio NOT NULL,
       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,
       swap_type swaption_type NOT NULL,
       trade_date date NOT NULL,
       settle_date date NOT NULL,
       buysell bool NOT NULL,
       notional float NOT NULL,
       option_type option_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),
       maturity date NOT NULL,
       currency currency NOT NULL,
       settlement_type settlement_type NOT NULL,
       fixed_rate float,
       termination_date date,
       termination_amount float,
       termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE);

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

CREATE TABLE capfloors (
	id serial PRIMARY KEY,
	fund fund NOT NULL DEFAULT 'SERCGMAST',
	dealid varchar(28) UNIQUE,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	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,
    "comments" varchar(100),
	floating_rate_index varchar(12) NOT NULL,
	floating_rate_index_desc varchar(32),
	buysell bool NOT NULL,
	cap_or_floor cap_or_floor NOT NULL,
	strike float8 NOT NULL,
	value_date date NOT NULL,
	expiration_date date NOT NULL,
	premium_percent float8 NOT NULL,
	pricing_type pricing_type NOT NULL,
	payment_frequency frequency NOT NULL,
	fixing_frequency frequency NOT NULL,
	day_count_counvention day_count NULL,
	bdc_convention bus_day_convention NULL,
	payment_mode payment_mode NOT NULL,
	payment_at_beginning_or_end begin_or_end NOT NULL,
	initial_margin_percentage float8 NULL,
	initial_margin_currency currency NULL,
	amount float8 NOT NULL,
	trade_date date NOT NULL,
	swap_type capfloor_type NOT NULL,
	reset_lag int4,
	trade_confirm varchar,
	termination_date date,
	termination_amount float8,
    termination_cp varchar(12) REFERENCES counterparties(code)  ON UPDATE CASCADE,
	cpty_id text,
)
CREATE TRIGGER dealid AFTER INSERT ON capfloors
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();

CREATE TABLE futures (
	id serial NOT NULL,
	dealid varchar(28),
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	folder future_strat NOT NULL,
	custodian varchar(12) NOT NULL,
	cashaccount varchar(10) NOT NULL,
	cp_code varchar(12) NOT NULL,
	trade_date date NOT NULL,
	settle_date date NOT NULL,
	buysell bool NOT NULL,
	bbg_ticker varchar(32) NOT NULL,
	quantity float8 NOT NULL,
	price float8 NOT NULL,
	commission float8 NULL,
	swap_type future_type NOT NULL,
	security_desc varchar(32) NOT NULL,
	maturity date NOT NULL,
	currency currency NOT NULL,
	exchange varchar(3) NOT NULL,
	fund fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	CONSTRAINT futures_dealid_key UNIQUE (dealid),
	CONSTRAINT futures_pkey PRIMARY KEY (id),
	CONSTRAINT futures_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code)
);
ALTER table futures OWNER TO dawn_user;
CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid();

CREATE TABLE wires (
	id serial NOT NULL,
	dealid varchar(28) NULL,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	folder cash_strat NOT NULL,
	code varchar(5) NOT NULL,
	amount float8 NOT NULL,
	currency currency NOT NULL,
	trade_date date NOT NULL,
	CONSTRAINT wires_pkey PRIMARY KEY (id),
	CONSTRAINT wires_code_fkey FOREIGN KEY (code) REFERENCES accounts(code)
);
ALTER table wires OWNER TO dawn_user;
CREATE trigger dealid after insert on wires for each row execute procedure auto_dealid();

CREATE TABLE spots (
	id serial NOT NULL,
	fund fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	dealid varchar(28) NULL,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	folder spot_strat NOT NULL,
	custodian varchar(12) NOT NULL,
	cashaccount varchar(10) NOT NULL,
	cp_code varchar(12) NOT NULL,
	trade_date date NOT NULL,
	settle_date date NOT NULL,
	spot_rate float8 NOT NULL,
	buy_currency currency NOT NULL,
	buy_amount float8 NOT NULL,
	sell_currency currency NOT NULL,
	sell_amount float8 NOT NULL,
	commission_currency currency NULL,
	commission float8 NULL,
	CONSTRAINT spots_pkey PRIMARY KEY (id),
	CONSTRAINT spots_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code)
);

create
    trigger dealid after insert
        on
        spots for each row execute procedure auto_dealid();;

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);
       ELSE
          CASE TG_TABLE_NAME
          WHEN 'cds' THEN
              stub := 'SCCDS';
          WHEN 'repo' THEN
              stub := 'SC_REP';
          WHEN 'swaptions' THEN
              stub := 'SWPTN';
          WHEN 'futures' THEN
              stub := 'SCFUT';
          WHEN 'wires' THEN
              stub := 'SCCSH';
          WHEN 'capfloors' THEN
              stub := 'CAP';
          WHEN 'spots' THEN
              stub := 'SCFX';
          END CASE;
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       END IF;
       EXECUTE sqlstr USING stub;
       RETURN NEW;
    END;
    $$ language plpgsql;

CREATE OR REPLACE function update_attach()
RETURNS TRIGGER AS $$
DECLARE
  factor float;
  cum_loss float;
BEGIN
IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN
RETURN NEW;
ELSE
SELECT indexfactor, cumulativeloss INTO factor, cum_loss
  FROM index_version WHERE redindexcode=NEW.security_id;
UPDATE cds
       SET attach=factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1),
           detach=factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1)
WHERE id=NEW.id;
RETURN NEW;
END IF;
END
$$ LANGUAGE plpgsql;


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

CREATE TRIGGER cds_attach AFTER
INSERT OR UPDATE OF orig_attach, orig_detach, security_id
ON cds
FOR EACH ROW EXECUTE PROCEDURE update_attach();

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 external_marks_deriv(date date NOT NULL,
       identifier text NOT NULL,
       nav float,
       cpty varchar(4),
       trade_date
       trade_type trade_type,
       PRIMARY KEY(identifier, date));

CREATE OR REPLACE VIEW id_mappings AS
       SELECT trade_date, dealid, cpty_id, notional*price/100 as nav,
       'SWAPTION'::trade_type as trade_type from swaptions
       UNION SELECT trade_date, dealid, cds.cpty_id, upfront as nav,
       'TRANCHE' FROM cds WHERE attach IS NOT NULL ORDER BY trade_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)
RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$
BEGIN
  RETURN SELECT DISTINCT ON (identifier) marks.date, marks.identifier, marks.price FROM marks
WHERE date<= p_date ORDER BY identifier, marks.date DESC;
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, p_fund fund DEFAULT 'SERCGMAST'::fund)
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, p_fund) 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,
       p_fund fund DEFAULT 'SERCGMAST'::fund)
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
          AND fund=$3)
  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, p_fund;
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,
                                          p_fund fund DEFAULT 'SERCGMAST'::fund) 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, true, p_fund) a
  LEFT JOIN factors_history(p_date) b USING (identifier)
  LEFT JOIN list_marks(p_date) 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, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END))
OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)  AS notional
FROM cds WHERE cds.fund=$2 AND (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.orig_attach,cds.orig_detach,cds.attach,cds.detach,'
                        'cds.initial_margin_percentage,',
                        'AND cds.orig_attach is NOT NULL', ',tmp.orig_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,
                                               strat cds_strat DEFAULT NULL::cds_strat,
                                               fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, currency currency, notional float) AS $$
BEGIN
IF strat IS NULL THEN
   RETURN QUERY EXECUTE query_positions('cds') USING p_date, fund;
ELSE
   RETURN QUERY SELECT a.security_id, a.security_desc, a.maturity, a.fixed_rate,
                       a.currency, a.notional
                FROM list_cds_positions_by_strat(p_date, fund) a
                WHERE folder=strat;
END IF;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE function list_cds_positions_by_strat(p_date date,
                                                       p_fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, currency currency, folder cds_strat, notional float) AS $$
BEGIN
RETURN QUERY
  WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate,
       cds.currency, cds.folder,
       SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder)  AS notional
       FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND
       cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fund=p_fund)
  SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_cds_positions_by_strat_fcm(p_date date,
                                                           fcm text)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, currency currency, folder cds_strat, notional float) AS $$
BEGIN
RETURN QUERY
  WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate,
       cds.currency, cds.folder,
       SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code)  AS notional
       FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND
       cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS'
       AND cds.account_code=fcm)
  SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cds_globeop_name(redcode text, fixed_rate float, maturity date,
       index index_type, tenor tenor)
RETURNS text AS $$
DECLARE
    result text;
    stub text;
    fcm text;

BEGIN
result := 'CDS_%s%s_%s.0000000000_ICE-CREDIT_%s_%s';
IF tenor = '3yr' AND redcode = '2I65BYDJ1' THEN
   stub := '.3';
ELSE
   stub := '';
END IF;
IF redcode = '2I65BRQY9' OR redcode = '2I65BYBE4' OR redcode = '2I666VCT0' THEN
   fcm = 'WELLSFCM';
ELSE
   fcm = 'BOMLCM';
END IF;
result := format(result, redcode, stub, fixed_rate, fcm, to_char(maturity, 'YYYYMMDD'));
RETURN result;
END;
$$ LANGUAGE plpgsql;

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

CREATE OR REPLACE function list_tranche_positions_by_strat(p_date date,
                                                           p_fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
              fixed_rate float, currency currency, orig_attach smallint,
              orig_detach smallint, attach float, detach float,
              folder cds_strat, notional float) AS $$
BEGIN
RETURN QUERY
  WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate,
       cds.currency, cds.orig_attach, cds.orig_detach,
       cds.attach, cds.detach, cds.folder,
       SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder)  AS notional
       FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND
       cds.trade_date <= p_date AND cds.orig_attach is NOT NULL AND cds.fund=p_fund)
  SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.orig_attach, tmp.folder) * FROM tmp WHERE tmp.notional!=0;
END;
$$ LANGUAGE plpgsql;


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

CREATE OR REPLACE function list_cds_marks_old(p_date date, strat cds_strat DEFAULT NULL::cds_strat)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint,
              p_version smallint, tenor tenor, maturity date, notional float, factor float,
              name text, coupon float, duration float, theta float, price float, closespread float,
              clean_nav float, accrued float) AS $$
DECLARE
  days integer;
  eur_fx float;
  params text;
  sqlquery text;
BEGIN
days:=days_accrued(p_date);
SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date;
IF strat IS NOT NULL THEN
   params := '$1, $4';
ELSE
   params := '$1';
END IF;

sqlquery := format(
'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact,
              cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor)
              FROM list_cds_positions(%s) a
              LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)),
index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta2
                FROM index_quotes d WHERE date=$1)
SELECT
    temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor,
    temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100,
    index_price.duration, index_price.theta2, index_price.closeprice, index_price.closespread,
    (1.-index_price.closeprice/100.) * temp.notional * temp.fact *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END),
    -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END)
FROM temp
LEFT JOIN index_price USING (index, series, version, tenor)', params);
IF strat IS NOT NULL THEN
   RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat;
ELSE
    RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type,
              p_series smallint,
              p_version smallint, tenor tenor, maturity date, notional float,
              factor float, coupon float, duration float, theta float, price float,
              closespread float, clean_nav float, accrued float,
              globeop_nav float, globeop_notional float) AS $$
DECLARE
  days integer;
  eur_fx float;
  params text;
  sqlquery text;
  and_clause text;
BEGIN
days:=days_accrued(p_date);
SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date;
IF strat IS NOT NULL THEN
   params := '$1, $4';
   and_clause := 'AND strat = ltrim($4::text, ''SER_'')::strategy';
ELSE
   params := '$1';
   and_clause := '';
END IF;

sqlquery := format(
'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact
              FROM list_cds_positions(%s) a
              LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)),
index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2
                FROM index_quotes d WHERE date=$1),
globeop_marks AS (SELECT security_id, maturity, sum(endqty) AS current_notional,
                  sum(endbooknav) AS nav
                  FROM (SELECT *, split_part(invid, ''_'', 2) AS security_id,
                               split_part(invid, ''_'', 6)::date AS maturity
                        FROM valuation_reports
                        WHERE periodenddate=$1 and invid like ''CDS\_%%'' %s) a
                  GROUP BY security_id, maturity)
SELECT
    temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor,
    temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100,
    index_price.duration2, index_price.theta2, index_price.closeprice, index_price.closespread,
    (1.-index_price.closeprice/100.) * temp.notional * temp.fact *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END),
    -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END),
    globeop_marks.nav, globeop_marks.current_notional
FROM temp
LEFT JOIN index_price USING (index, series, version, tenor)
LEFT JOIN globeop_marks USING (security_id, maturity)', params, and_clause);
IF strat IS NOT NULL THEN
   RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat;
ELSE
    RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days;
END IF;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE function list_cds_marks_by_strat(p_date date)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat,
              p_index index_type, p_series smallint,
              p_version smallint, tenor tenor, maturity date, notional float, factor float,
              name text, coupon float, duration float, theta float, price float, closespread float,
              clean_nav float, accrued float) AS $$
DECLARE
  days integer;
  eur_fx float;
  params text;
  sqlquery text;
BEGIN
days:=days_accrued(p_date);
SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date;

sqlquery := 'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact,
              cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor)
              FROM list_cds_positions_by_strat($1) a
              LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)),
index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta
                FROM index_quotes d WHERE date=$1)
SELECT
    temp.security_id, temp.security_desc, temp.folder, temp.index, temp.series, temp.version, temp.tenor,
    temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100,
    index_price.duration, index_price.theta, index_price.closeprice, index_price.closespread,
    (1.-index_price.closeprice/100.) * temp.notional * temp.fact *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END),
    -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 *
    (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END)
FROM temp
LEFT JOIN index_price USING (index, series, version, tenor)';
RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days;
END;
$$ LANGUAGE plpgsql;


CREATE TABLE tranche_risk(
       date date,
       tranche_id integer REFERENCES cds(id),
       clean_nav float,
       accrued float,
       duration float,
       delta float,
       gamma float,
       theta float,
       tranche_factor float,
       upfront float,
       running float,
       corr_attach float,
       corr_detach float,
       index_refprice float,
       index_refspread float,
       index_duration float,
       PRIMARY KEY (date, tranche_id))

CREATE OR REPLACE function list_tranche_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type,
              p_series smallint, p_version smallint, p_tenor tenor, maturity date,
              notional float, factor float, coupon integer, clean_nav float,
              accrued float, initial_margin_percentage float, theta float,
              duration float, tranchedelta float4, trancheupfrontmid float,
              indexrefprice float4, indexrefspread smallint,
              attach smallint, detach smallint, index_duration float) AS $$
DECLARE
  days integer;
  eur_fx float;
BEGIN
days:=days_accrued(p_date);
SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date;
RETURN QUERY
WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor,
             (a.detach-a.attach)/(a.orig_detach-a.orig_attach) * c.indexfactor / 100 AS fact
              FROM list_tranche_positions(p_date, fund) a
              LEFT JOIN index_version c ON a.security_id=c.redindexcode
              LEFT JOIN index_maturity d USING (index, series, maturity)),
risk_num AS (SELECT DISTINCT ON (index, series, a.attach, a.detach, tenor) * from risk_num_per_quote a
              WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date + interval '1 day'
              ORDER by index, series, a.attach, a.detach, tenor, quotedate desc)
SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor,
       temp.maturity, temp.notional, temp.fact,
       trancherunningmid::integer,
       temp.notional * temp.fact * (case when temp.index = 'HY' then (1.-risk_num.trancheupfrontmid/100) else risk_num.trancheupfrontmid/100 end) *
       (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END),
       -temp.notional * temp.fact * trancherunningmid/10000. * days / 360 *
       (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END),
       temp.initial_margin_percentage, risk_num.theta, risk_num.duration, risk_num.tranchedelta,
       risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread,
       temp.orig_attach, temp.orig_detach, risk_num.index_duration
FROM temp
LEFT JOIN risk_num  ON
     temp.series=risk_num.series
     AND temp.orig_attach = risk_num.attach
     AND temp.orig_detach = risk_num.detach
     AND temp.tenor = risk_num.tenor;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION list_swaption_positions_and_risks(IN p_date date)
  RETURNS TABLE(deal_id character varying, security_id character varying,
  maturity date, notional double precision, option_type option_type,
  strike double precision, expiration_date date, serenitas_nav double precision,
  globeop_nav double precision,
  initial_margin_percentage double precision, latest_model_date date,
  delta double precision, gamma double precision, vega double precision,
  theta double precision, index index_type, series smallint, tenor tenor,
  duration double precision) AS $$
BEGIN
RETURN QUERY
-- TODO: fix the case of multiple index versions
SELECT DISTINCT ON (a.dealid) a.dealid, a.security_id, c.maturity,
       (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * a.notional,
       a.option_type, a.strike, a.expiration_date, b.market_value,
       endbooknav,
       a.initial_margin_percentage, b.date, b.delta, b.gamma, b.vega,
       b.theta, c.index, c.series, c.tenor, d.duration
FROM swaptions a
LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid)
LEFT JOIN index_desc c ON a.security_id=redindexcode AND a.maturity=c.maturity
LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, tenor)
LEFT JOIN valuation_reports ON invid = a.globeop_id
WHERE (termination_date IS NULL OR termination_date > p_date)
AND a.expiration_date > p_date AND trade_date <= p_date
AND swap_type = 'CD_INDEX_OPTION'
AND periodenddate = p_date
ORDER BY dealid, date DESC;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION list_ir_swaption_positions(IN p_date date)
  RETURNS TABLE(deal_id character varying, strategy swaption_strat, notional double precision,
  option_type option_type, strike double precision, expiration_date date, maturity date,
  initial_margin_percentage double precision) AS $$
BEGIN
RETURN QUERY
SELECT a.dealid, folder, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * a.notional,
a.option_type, a.strike, a.expiration_date, a.maturity, a.initial_margin_percentage
FROM swaptions a WHERE (termination_date IS NULL OR termination_date > p_date)
AND  a.expiration_date > p_date
AND a.trade_date <= p_date
AND a.swap_type = 'SWAPTION';
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*(100.-temp.price)/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 imm_date(p_date date)
RETURNS date AS $$
from dates import imm_date
return imm_date(p_date)
$$ LANGUAGE plpython2u;

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 $$
-- index_factor and cumulativeloss are in percents, eg:
-- tranche_factor(15::smallint, 25::smallint, 98, 0.71)
DECLARE
  newattach float;
  newdetach float;

BEGIN
newattach:=LEAST(GREATEST((attach-cumulativeloss)/index_factor, 0), 1);
newdetach:=LEAST(GREATEST((detach-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 latest_sim(p_date date) RETURNS integer AS $$
DECLARE
	id integer;
BEGIN
SELECT model_id_sub into id
  FROM priced
  INNER JOIN model_versions USING (model_id_sub)
  INNER JOIN model_versions_nonagency USING (model_id_sub)
  INNER JOIN simulations_nonagency USING (simulation_id)
    WHERE description = 'normal' AND timestamp >= p_date
    LIMIT 1;
RETURN id;
End;
$$ LANGUAGE plpgsql;

-- Not sure how to map enums so use text for now
CREATE FOREIGN TABLE priced(
     model_id_sub smallint,
     cusip varchar(9),
     model_version smallint,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_RnW float,
     pv_FB 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(
     model_id_sub smallint,
     cusip varchar(9),
     model_version smallint,
     normalization text,
     timestamp timestamp,
     pv float,
     pv_RnW float,
     pv_FB 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(
     model_id_sub smallint,
     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(
     model_id_sub smallint,
     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 TYPE INDEXFAMILY AS ENUM('ITRAXX-Asian', 'LCDXNA', 'MCDXNA',
'ITRAXX-SOVX', 'ITRAXX-SDI', 'ITRAXX-L', 'CDX', 'ITRAXX-European');
CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');

IMPORT FOREIGN SCHEMA public LIMIT TO
(index_desc,
 index_version,
 index_version_markit,
 index_maturity,
 index_quotes,
 markit_tranche_quotes,
 risk_num_per_quote,
 rates) FROM SERVER postgresql_server INTO public;


CREATE FOREIGN TABLE model_versions(
     model_id smallint,
     asset_class text,
     start_time timestamp,
     model_id_sub smallint)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

CREATE FOREIGN TABLE model_versions_nonagency(
     model_id_sub smallint,
     granularity_agg text,
     num_LS_mult smallint,
     d_LS_mult float,
     simulation_id smallint,
     remark text)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

CREATE FOREIGN TABLE simulations_nonagency(
     simulation_id smallint,
     granularity_simu text,
     num_hpi_scens smallint,
     num_ir_scens smallint,
     macro_timestamp timestamp,
     data_source text,
     forecast_date_roll date,
     forecast_date date,
     hamp_stepup_credit float,
     description text)
     SERVER mysql_server
     OPTIONS (dbname 'rmbs_model');

-- #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, delta_mult float,
        v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float, pv_FB 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 cusip, model_version, pv, modDur, delta_yield,
                          wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po,
                          delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB
                   FROM %I
                   WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day''
                   AND model_id_sub=$3 ' || opt_constraint || ')
          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, c.delta_mult, a.pv_RnW as v1pv_RnW,
                 a.delta_mult as v1_lsdel, a.delta_hpi as v1_hpidel, a.delta_ir as v1_irdel, c.pv_FB
          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 model_id_sub=$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.delta_mult,
       left_table.v1pv_RnW, left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel, left_table.pv_FB
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, latest_sim(p_date);
END
$$ LANGUAGE plpgsql;


CREATE TABLE swaption_marks(
       dealid varchar(28) REFERENCES swaptions(dealid),
       market_value float,
       delta float,
       gamma float,
       vega float)

CREATE TABLE subprime_risk(
       date           date,
       cusip          varchar(9),
       pv1            float,
       pv2            float,
       pv3            float,
       modDur         float,
       pv5            float,
       pv25           float,
       pv50           float,
       pv75           float,
       pv95           float,
       delta_yield    float,
       wal            float,
       pv_io          float,
       pv_po          float,
       pv_RnW         float,
       delta_ir_io    float,
       delta_ir_po    float,
       delta_hpi      float,
       delta_RnW      float,
       delta_mult     float,
       v1pv_RnW       float,
       v1_lsdel       float,
       v1_hpidel      float,
       v1_irdel       float,
       pv_FB          float,
       PRIMARY KEY (date, cusip))


CREATE TYPE portfolio AS ENUM('CASH', 'CLO', 'CURVE', 'GFS_HELPER_BUSINESS_UNIT',
       'HEDGE_MAC', 'HY', 'IG', 'LQD_TRANCH', 'MORTGAGES', 'OPTIONS',
       'SERCGLLC__SERCGLLC', 'SERCGLTD__SERCGLTD', 'SER_TEST__SER_TEST', 'STRUCTURED',
       'IR', 'TRANCHE');

CREATE TYPE fund AS ENUM('SERCGLLC', 'SERCGLTD', 'SERCGMAST', 'SER_TEST')

CREATE TYPE strategy AS ENUM(
-- CLO portfolio
'CLOCDSCSH', 'CLO_AAA', 'CLO_BB20', 'CLO_BBB', 'HEDGE_CLO',
-- TRANCHE portfolio
'HYEQY', 'HYMEZ', 'HYSNR', 'HYINX', 'IGEQY', 'IGMEZ', 'IGSNR', 'IGINX', 'BSPK', 'TCSH'
-- CURVE portfolio
'IGCURVE', 'IGCVECSH', 'ITRXCURVE', 'ITRXCVCSH', 'HYCURVE',
-- OPTIONS portfolio
'IGOPTDEL', 'IGPAYER', 'IGREC', 'HYOPTDEL', 'HYPAYER', 'HYREC', 'IGCDSCSH', 'HYCDSCSH', 'COCSH'
-- IR portfolio
'IR', 'IRDEVCSH', 'STEEP', 'FLAT',
-- STRUCTURED portfolio
'STR_MAV', 'STR_MEZZ', 'HEDGE_CSO', 'CSO_TRANCH', 'CSOCDSCSH',
-- MORTGAGES portfolio
'HEDGE_MBS', 'MTG_B4PR', 'MTG_FP', 'MTG_GOOD', 'MTG_IO', 'MTG_LMG',
'MTG_PR', 'MTG_RW', 'MTG_SD', 'MTG_THRU', 'MBSCDS', 'MBSCDSCSH',
'CRT_LD', 'CRT_SD', 'CRT_LD_JNR',
-- IG portfolio (deprecated)
'IGTCDSCSH', 'IGCURVE', 'IGEQY', 'IGINX', 'IGMEZ', 'IGSNR',
-- HY portfolio (deprecated)
'HYTCDSCSH', 'HYCURVE', 'HYEQY', 'HYINX', 'HYMEZ', 'HYSNR',
-- HEDGE_MAC portfolio
'HEDGE_MAC', 'MACCDSCSH',
-- CASH portfolio
'M_CSH_CASH', 'M_CSH_EXP',
-- GFS_HELPER_BUSINESS_UNIT portfolio
'GFS_TRANSFER_HELPER',
-- LQD_TRANCH (deprecated)
'LQD_TRANCH')

CREATE TABLE valuation_reports(
        custacctname text,
        endbookcost float,
        endbookmv float,
        endbooknav float,
        endbookunrealfxgl float,
        endbookunrealincome float,
        endbookunrealmtm float,
        endlocalcost float,
        endlocalmv float,
        endlocalmarketprice float,
        endqty float,
        fund fund,
        gfstranid1 text,
        invccy currency,
        invdesc text,
        invid text,
        invtype text,
        knowledgedate timestamp NOT NULL,
        periodenddate date NOT NULL,
        port portfolio,
        strat strategy,
        row integer NOT NULL,
        counterparty varchar(12) REFERENCES counterparties(code),
        PRIMARY KEY(periodenddate, row)
);

CREATE INDEX on valuation_reports (periodenddate);

CREATE TYPE longshort AS ENUM('L', 'S');

CREATE TABLE pnl_reports(
        date date,
        fund fund,
        port portfolio,
        strat strategy,
        longshortindicator longshort,
        custacctname text,
        pricelist text,
        invassettype text,
        invccy currency,
        invdesc text,
        invid text,
        endqty float,
        endlocalmarketprice float,
        dailybookrealmtm float,
        dailybookunrealmtm float,
        dailybookrealfxgl float,
        dailybookunrealfxgl float,
        dailybookrealincome float,
        dailybookunrealincome float,
        dailybookmiscrevexp float,
        dailytotalbookpl float,
        mtdbookrealmtm float,
        mtdbookunrealmtm float,
        mtdbookrealfxgl float,
        mtdbookunrealfxgl float,
        mtdbookrealincome float,
        mtdbookunrealincome float,
        mtdbookmiscrevexp float,
        mtdtotalbookpl float,
        row integer,
        PRIMARY KEY(date, row)
);

CREATE INDEX on pnl_reports (date);

CREATE TYPE clearing_cp AS ENUM('ICE-CREDIT', 'NOT CLEARED');
CREATE TYPE trade_type AS ENUM('CREDIT_DEFAULT_SWAP', 'SWAPTION', 'TRANCHE');
CREATE TYPE transaction_status AS ENUM('Bilateral', 'Cleared');
CREATE TYPE calendar AS ENUM('Payment-GB,US', 'Payment-US,GB', 'Payment-EU,GB');
CREATE TYPE clearing_broker AS ENUM('ML', 'SGFCM', 'BOMLCM', 'WELLSFCM');
CREATE TYpe frequency AS ENUM('Monthly', 'Quarterly');

CREATE TABLE cds_reports(
       date date NOT NULL,
       row integer NOT NULL,
       attachment_point float,
       exhaustion_point float,
       basis day_count,
       "buy/sell" protection,
       ccp clearing_cp,
       calendar calendar,
       cash_account text,
       ccy currency,
       client_ref_id text,
       comments text,
       commission float,
       contractual_definition isda,
       counterparty text REFERENCES counterparties(code),
       cpty_ref_id text,
       created_date timestamp,
       description text,
       effective_date date,
       external_trade_id text,
       factor float,
       fixed_rate float,
       frequency frequency,
       fund fund,
       gtid text,
       geneva_id text,
       independent_amount float,
       independent_perc float,
       maturity_date  date,
       notional float,
       original_gtid text,
       original_notional float,
       period_end_date last_period_convention,
       price float,
       prime_broker clearing_broker,
       product_sub_type swap_type,
       red_code text,
       recovery_rate float,
       remaining_notional float,
       roll_convention bus_day_convention,
       strategy strategy,
       strike float,
       swapswire_id text,
       trade_date date,
       trade_type trade_type,
       transaction_status transaction_status,
       underlying_id text,
       underlying_name text,
       upfront_fee float,
       upfront_fee_date date,
       executing_broker text
       PRIMARY KEY(date, row));

CREATE INDEX on cds_reports (date);

CREATE TYPE mark_list AS (date date,
                      identifier text,
                      "BROKER" float,
                      "BVAL" float,
                      "IDC" float,
                      "MANAGER" float,
                      "MARKIT" float,
                      "PB" float,
                      "PRICESERVE" float,
                      "PRICINGDIRECT" float,
                      "REUTERS" float,
                      "S&P" float);

CREATE OR REPLACE FUNCTION get_mark_matrix(identifier varchar(9)) RETURNS SETOF mark_list
AS $$

import numpy as np
import pandas as pd

source_list = ["BROKER", "BVAL", "IDC", "MANAGER", "MARKIT", "PB",
               "PRICESERVE", "PRICINGDIRECT", "REUTERS", "S&P"]
sql_string = """SELECT identifier, date, source, mark
FROM external_marks_mapped WHERE identifier = $1 ORDER BY date, source"""
plan = plpy.prepare(sql_string, ["varchar"])
df = pd.DataFrame.from_records(plpy.cursor(plan, [identifier]))

df = df.groupby(['date', 'identifier', 'source']).mean().unstack(-1)
df.columns = df.columns.droplevel(level=0)
df = df.reindex(columns=source_list).reset_index()

for t in (df.itertuples(index=False)):
   yield [t[0], t[1]] + [None if np.isnan(v) else v for v in t[2:]]

$$ LANGUAGE plpythonu;


CREATE OR REPLACE VIEW globeop_tranche_risk AS
SELECT date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach,
       orig_detach, CASE protection WHEN 'Buyer' THEN notional ELSE -notional END AS notional,
       endqty as globeop_notional,
       clean_nav as serenitas_clean_nav,
       endbooknav-endbookunrealincome as globeop_clean_nav,
       accrued as serenitas_accrued, endbookunrealincome as globeop_accrued,
       duration, delta, gamma, theta, tranche_factor,
       tranche_risk.corr_attach, tranche_risk.corr_detach,
       tranche_risk.upfront, tranche_risk.running,
       index_refprice, index_refspread, index_duration, initial_margin_percentage
FROM tranche_risk
LEFT JOIN cds ON (tranche_id=id)
LEFT JOIN valuation_reports ON (invid=globeop_id AND periodenddate=date)
LEFT JOIN index_version ON (security_id=redindexcode)
ORDER BY index, series, orig_attach;


CREATE TABLE fcm_im(
       date date NOT NULL,
       account text NOT NULL, -- REFERENCES accounts(cash_account)
       currency currency NOT NULL,
       amount float NOT NULL,
       PRIMARY KEY(date, account, currency)
)

CREATE TABLE strategy_im(
       date date NOT NULL,
       broker text NOT NULL,
       strategy strategy NOT NULL,
       amount float NOT NULL,
       currency currency NOT NULL,
       PRIMARY KEY (date, strategy, broker)
)

CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR');