path: root/sql/dawn.sql

-- -*- 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',
       'MTG_REPO');

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',
       'XOEQY', 'XOMEZ', 'XOINX',
       'EUEQY', 'EUMEZ', 'EUSNR', 'EUINX',
       'BSPK', 'XCURVE', 'SER_HYCURVE', '*');

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

CREATE TYPE repo_strat AS ENUM('');

CREATE TYPE spot_strat AS ENUM('M_STR_MAV', 'M_STR_MEZZ', 'SER_ITRXCURVE', 'M_CSH_CASH', 'TCSH');

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

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',
       'SER_ITRXCURVE', 'XCURVE', 'BSPK', 'SER_HYINX', 'IGOPTDEL', 'IGINX',
       'HYINX', 'HEDGE_CLO', 'CVECSH');

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

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', 'HYBB');
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 TYPE beta_type AS ENUM('IG', 'CRT', 'EU', 'SUBPRIME', 'XO');

CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future');

CREATE TYPE equity_option_type AS ENUM ('CALL', 'PUT');

CREATE TABLE accounts(
       code varchar(5) PRIMARY KEY,
       name text,
       custodian text NOT NULL,
       cash_account text NOT NULL,
       counterparty varchar(12) REFERENCES counterparties(code),
       fund fund NOT NULL,
       account_type account_type NOT NULL,
       active boolean NOT NULL);

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,
       cash_counterparty boolean NOT NULL DEFAULT true,
       cds_counterparty boolean NOT NULL DEFAULT false);

CREATE INDEX ON counterparties(name);

-- deprecated
CREATE TABLE bond_old(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(12) 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,
       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 TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       dealid varchar(28) GENERATED ALWAYS AS ('SC_'||CASE WHEN asset_class ='Subprime' THEN 'SUB'
                                                           WHEN asset_class='CLO' THEN 'CLO'
                                                           WHEN asset_class='CSO' THEN 'CSO'
                                                           WHEN asset_class='CRT' THEN 'CRT'
                                                      END
                                                    ||id::text) STORED,
       folder bond_strat NOT NULL,
       portfolio portfolio 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 numeric NOT NULL,
       accrued numeric,
       asset_class asset_class NOT NULL,
       ticket text,
       principal_payment float,
       accrued_payment float,
       current_face float,
       allocated boolean NOT NULL DEFAULT false,
       stale bool NOT NULL DEFAULT true,
       emailed bool NULL DEFAULT false,
       CONSTRAINT bonds_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL));


CREATE OR REPLACE FUNCTION notify_id()
RETURNS trigger
AS $$
BEGIN
PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text);
NEW.stale = true;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_portf()
RETURNS TRIGGER
AS $$
BEGIN
NEW.portfolio = (
CASE WHEN NEW.folder::text LIKE '%MTG%' THEN 'MORTGAGES'
WHEN NEW.folder::TEXT LIKE '%CLO%' THEN 'CLO'
WHEN NEW.folder::TEXT LIKE '%CRT%' THEN 'MORTGAGES'
WHEN NEW.folder::TEXT LIKE '%STR%' THEN 'STRUCTURED'
WHEN NEW.folder::TEXT='M_CSH_CASH' THEN 'CASH'
WHEN NEW.folder::TEXT='TCSH' THEN 'TRANCHE'
WHEN NEW.folder::TEXT='SER_ITRXCURVE' THEN 'CURVE'
END)::portfolio;
RETURN NEW;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER portf BEFORE
INSERT OR UPDATE OF
    folder
ON
    bonds
FOR EACH ROW
EXECUTE PROCEDURE update_portf();

CREATE TRIGGER fx_portf BEFORE
INSERT OR UPDATE OF
    folder
ON
    spots
FOR EACH ROW
EXECUTE PROCEDURE update_portf();

CREATE TRIGGER bond_notify BEFORE
INSERT OR UPDATE OF
    identifier,
    cusip,
    isin,
	faceamount,
    price,
    trade_date,
	settle_date,
    accrued
ON
	bonds
FOR EACH ROW
WHEN (current_setting('application_name') != 'update_loop')
EXECUTE PROCEDURE notify_id();


CREATE OR REPLACE FUNCTION set_identifier()
RETURNS trigger
AS $$
BEGIN
NEW.identifier =  COALESCE(NEW.identifier, NEW.cusip, NEW.isin);
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_identifier BEFORE
INSERT OR UPDATE OF
    identifier,
    cusip,
    isin
ON
	bonds
FOR EACH ROW EXECUTE PROCEDURE set_identifier();

CREATE TABLE bond_allocation(
       id integer generated always as identity primary key,
       tradeid integer not null references bonds ON DELETE CASCADE,
       notional float not null,
       code text not null references accounts,
       submitted boolean default False,
       UNIQUE (tradeid, code));

CREATE TYPE status AS ENUM('Pending', 'Processed', 'Submitted', 'Aknowledged');

CREATE TABLE bond_submission(
       id integer generated always as identity primary key,
       allocation_id integer not null references allocation on delete cascade,
       "action" action not null,
       submit_date = timestamptz DEFAULT now(),
       status status NOT NULL DEFAULT 'PENDING'
);


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(12) 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,
       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 REFERENCES accounts(code),
       cpty_id text,
       globeop_id int,
       full_globeop_id text GENERATED ALWAYS AS ('CDS'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED,
       stale bool default true,
       traded_level numeric(9, 5) NULL,
       exercised_from text REFERENCES swaptions(dealid),
       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 TRIGGER cds_notify AFTER
INSERT
	OR
UPDATE
	OF security_id,
	maturity,
	trade_date,
	upfront_settle_date,
	traded_level,
	notional,
	protection ON
	cds FOR EACH ROW EXECUTE FUNCTION notify_id();


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;
NEW.attach = factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1);
NEW.detach = factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1);
RETURN NEW;
END IF;
END
$$ LANGUAGE plpgsql;

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

CREATE TABLE repos (
	id int4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	dealid varchar(28) NULL GENERATED ALWAYS AS ((('SCREPO'::text || id::text))) STORED,
	"fund" fund NOT NULL,
	lastupdate timestamptz NULL DEFAULT now(),
	"action" action NULL,
	folder bond_strat NOT NULL,
	cp_code varchar(12) NOT NULL REFERENCES counterparties(code),
	trade_date date NOT NULL,
	settle_date date NOT NULL,
	cusip varchar(9) NULL,
	isin varchar(12) NULL,
	identifier varchar(12) NULL,
	description varchar(32) NOT NULL,
	transaction_indicator repo_type NULL,
	faceamount float8 NOT NULL,
	price float8 NOT NULL,
	"currency" currency NOT NULL DEFAULT 'USD'::currency,
	expiration_date date NULL,
	weighted_amount float8 NULL,
	haircut float8 NULL,
	repo_rate float8 NOT NULL,
	"call_notice" call_notice NULL,
	day_count day_count NULL,
	ticket varchar NULL,
	open_repo bool NULL,
    account_code text NOT NULL DEFAULT 'BAC' REFERENCES accounts(code),
    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)
);


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(12) 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_fee float,
       termination_amount float,
       termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE,
       cpty_id text,
       globeop_id int,
       full_globeop_id text GENERATED ALWAYS AS ('SWO'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED,
);

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(12) 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,
	cpty_id text,
	globeop_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,
	account_code varchar(12) NOT NULL REFERENCES accounts(code),
	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),
);

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,
	account_code varchar(12) NOT NULL REFERENCES accounts(code),
	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),
);

CREATE TABLE terminations (
    id int GENERATED BY default as identity primary KEY,
    dealid varchar(28) NOT NULL,
    termination_date date NOT NULL,
    termination_cp varchar(12) REFERENCES counterparties(code),
    termination_amount float8,
    termination_fee float8 NOT NULL,
    partial_termination bool NOT null default false,
    new_gtid int,
    fee_payment_date date NOT NULL,
);

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:= format('UPDATE '|| TG_TABLE_SCHEMA ||'.' ||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 '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 TRIGGER dealid AFTER INSERT ON capfloors
FOR EACH ROW EXECUTE PROCEDURE auto_dealid() ;

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,
       issuer text,
       reset_index text,
       coupon_type text,
       payment_day integer,
       issue_date date,
       figi text);

ALTER TABLE securities OWNER TO dawn_user;

CREATE TABLE marks(date date,
       identifier varchar(12) REFERENCES securities(figi) 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,
       local_nav float, --trade ccy nav
       base_nav float, --USD nav
       cpty varchar(4),
       ia float, -- ia in USD
       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(figi) 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 TABLE subscription_and_fee(
       date date,
       fund fund,
       subscription float,
       redemption float,
       incentive_fee float,
       management_fee float,
       PRIMARY KEY (fund, date));

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

CREATE TABLE bbh_val(
       accounting_date date NOT NULL,
       row integer NOT NULL,
       custody_head_account_number float,
       security_id text,
       security_description text,
       asset_currency currency,
       original_face float,
       base_price float,
       local_unit_cost float,
       base_unit_cost float,
       local_market_value float,
       base_market_value float,
       security_id_type text,
       sub_security_type_code text,
       source text,
       investment_type_code text,
       investment_type_description text,
       security_long_description  text,
       security_type_code  text,
       total_current_assets float,
       total_current_liabilities float,
       total_net_assets float,
       interest_rate float,
       quantity float,
       quantity_scale float,
       long_short_indicator LS,
       fx_rate float,
       maturity_date date,
       PRIMARY KEY (accounting_date, row));

CREATE TABLE bbh_pnl(
       accounting_date date NOT NULL,
       row integer NOT NULL,
       security_id text,
       custody_head_account_number int,
       issue_name text,
       local_currency currency,
       base_market_value float,
       base_change_income float,
       base_change_fx_realized_gain_loss float,
       base_change_fx_unrealized_gain_loss float,
       base_change_unrealized_gain_loss float,
       base_change_realized_gain_loss float,
       base_change_miscellaneous_income float,
       base_change_expense float,
       base_change_total float,
       sub_security_type_code text,
       source text,
       PRIMARY KEY (accounting_date, row));


CREATE TYPE bony_asset_type AS ENUM(
'CASH & CASH EQUIVALENTS', 'FIXED INCOME SECURITIES', 'FUTURES CONTRACTS', 'NON CUSIP RELATED INCOME');

CREATE TABLE bowdst_val(
       row integer not null,
       as_of_date                  date not null,
       source_account_number       integer REFERENCES bowdst_accounts(account_number),
       mellon_security_id          text,
       asset_type                  bony_asset_type,
       security_description_1      text,
       security_description_2      text,
       maturity_date               date,
       coupon_rate                 float,
       current_notional            float,
       local_price                 float,
       local_currency_code         currency,
       base_price                  float,
       local_cost                  float,
       base_cost                   float,
       local_market_value          float,
       base_market_value           float,
       local_unrealized_pnl        float,
       base_unrealized_pnl         float,
       local_notional_cost         float,
       base_notional_cost          float,
       local_notional_value        float,
       base_notional_value         float,
       gen_ledger_acct             integer,
       report_run_date             date,
       link_ref                    text,
       counterparty_name           text,
       exchange_rate               float,
       original_strike_price       float,
       current_strike_price        float,
       cusip                       text,
       ticker                      text,
       isin                        text,
       PRIMARY KEY (as_of_date, row)
);

CREATE TABLE bowdst_accounts(
       account_number integer NOT NULL PRIMARY KEY,
       account_name text NOT NULL
);

CREATE TABLE bowdst_pnl(
       row integer not null,
      begin_date	date not null,
      end_date	date not null,
      derivative_type	text,
      security_id	text,
      security_description_1	text,
      security_description_2	text,
      local_currency	currency,
      coupon_rate	float,
      asset_type_name	text,
      asset_type_category	bony_asset_type,
      unrealized_currency	float,
      unrealized_investment	float,
      total_unrealized	float,
      ending_shares_par	float,
      opening_receivables	float,
      closing_receivables	float,
      income	float,
      journal_entry_income	float,
      accretion_amortization	float,
      journal_entry_accretion_amortization	float,
      realized_gain_loss	float,
      journal_entry_realized_gl	float,
      realized_loss_impaired_securities	float,
      net_investment_income	float,
      cusip	text,
      ticker	text,
      isin	text,
      link_ref	text,
      realized_investment	float,
      realized_currency	float,
      realized_settled	float,
      realized_traded	float,
       PRIMARY KEY (begin_date, end_date, row)
);


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 VIEW bond_trades
AS SELECT bonds.dealid,
    bond_allocation.tradeid,
    bond_allocation.id,
    bonds.identifier,
    bonds.description,
    bonds.asset_class,
    bonds.trade_date,
    bonds.settle_date,
    bonds.buysell,
    bonds.price,
    bonds.accrued,
    bonds.folder,
    bonds.portfolio,
    bonds.principal_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS principal_payment,
    bonds.accrued_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS accrued_payment,
    (bonds.principal_payment + bonds.accrued_payment) * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS net_amount,
    bonds.current_face * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS current_face,
    bond_allocation.notional AS faceamount,
    accounts.fund,
    counterparties.code AS cp_code,
    counterparties.name AS counterparty
   FROM bonds
     LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid
     LEFT JOIN accounts USING (code)
     LEFT JOIN counterparties ON bonds.cp_code = counterparties.code
  WINDOW w AS (PARTITION BY bond_allocation.tradeid);

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, cusip varchar(9)) 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 bond_trades.identifier, asset_class, settle_date, folder,
          principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) )
          OVER (PARTITION BY bond_trades.identifier) notional FROM bond_trades WHERE trade_date<=$1
          AND fund=$3)
  SELECT DISTINCT ON (temp.identifier) securities.figi, 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, securities.cusip
  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, cusip varchar(9)) 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, a.cusip
  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 %s SUM(cds.notional)
OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)  AS notional
FROM list_cds($1, $2) cds WHERE %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,',
                        'sum(initial_margin_percentage * abs(notional)/100) '
                        'OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)'
                        'as initial_margin, ',
                        'cds.orig_attach is NOT NULL', ',tmp.orig_attach');
ELSIF p_type = 'cds' THEN
   RETURN format(query, '', '', 'cds.attach is NULL AND cds.folder!=''MBSCDS''', '');
ELSIF p_type = 'abs' THEN
   RETURN format(query, '', '', 'cds.folder=''MBSCDS''', '');
ELSE
   RETURN format(query, 'cds.attach, cds.detach,', '', '', ',tmp.attach');
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE LIST_CDS AS(
       id integer,
       trade_date date,
       security_id varchar(12),
       security_desc varchar(32),
       maturity date,
       fixed_rate float,
       currency currency,
       folder cds_strat,
       protection protection,
       notional float,
       orig_attach smallint,
       orig_detach smallint,
       attach float,
       detach float,
       fcm text,
       initial_margin_percentage float,
       cpty_id text,
       cp_code varchar(12)
);

CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS SETOF LIST_CDS AS $$
BEGIN
RETURN QUERY
SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency,
       folder, protection, (notional - coalesce(terminated_amount, 0.)) *
       (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)
       AS notional,
       orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage,
       cpty_id,
       cp_code
FROM cds LEFT JOIN (
     SELECT dealid, SUM(termination_amount) AS terminated_amount
     FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid)
WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount
      AND trade_date <= p_date AND maturity > p_date;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_cds2(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
-- Do not include unsettled terminations
RETURNS SETOF LIST_CDS AS $$
BEGIN
RETURN QUERY
SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency,
       folder, protection, (notional - coalesce(terminated_amount, 0.)) *
       (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)
       AS notional,
       orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage,
       cpty_id,
       cp_code
FROM cds LEFT JOIN (
     SELECT dealid, SUM(termination_amount) AS terminated_amount
     FROM terminations WHERE fee_payment_date < p_date GROUP BY dealid) b USING (dealid)
WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount
      AND trade_date <= p_date AND maturity > p_date;
END;
$$ LANGUAGE plpgsql;


CREATE TYPE LIST_SWAPTION AS(
       id integer,
       trade_date date,
       busell bool,
       option_type option_type,
       security_id varchar(12),
       security_desc varchar(32),
       maturity date,
       fixed_rate float,
       currency currency,
       folder swaption_strat,
       notional float,
       strike float,
       expiration_date date,
       initial_margin_percentage float,
       cpty_id text,
       cp_code varchar(12)
);

CREATE OR REPLACE function list_swaptions(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
-- Do not include unsettled terminations
RETURNS SETOF LIST_SWAPTION AS $$
BEGIN
RETURN QUERY
SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency,
       folder, buysell, (notional - coalesce(terminated_amount, 0.)) *
       (2* buysell -1) AS notional,
       strike, expiration_date, initial_margin_percentage,
       cpty_id,
       cp_code
FROM swaptions LEFT JOIN (
     SELECT dealid, SUM(termination_amount) AS terminated_amount
     FROM terminations WHERE fee_payment_date < p_date GROUP BY dealid) b USING (dealid)
WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount
      AND trade_date <= p_date AND expiration_date > p_date;
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)
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder)  AS notional
       FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS'))
  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,
                                                           p_fcm text,
                                                           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)
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.fcm)  AS notional
       FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=p_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, initial_margin 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)
       OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder)  AS notional
       FROM list_cds(p_date, p_fund) cds WHERE cds.orig_attach is NOT NULL)
  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_pre(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), 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) AS $$
DECLARE
  days integer;
  eur_fx float;
  params text;
  sqlquery text;
  and_clause text;
BEGIN
days:=days_accrued(p_date);
SELECT DISTINCT ON (date) eurusd INTO eur_fx FROM fx WHERE date BETWEEN p_date - INTERVAL '3 DAYS' AND p_date;
IF strat IS NOT NULL THEN
   params := '$1, $5, $2';
   and_clause := 'AND strat = ltrim($5::text, ''SER_'')::strategy';
ELSE
   params := '$1, NULL, $2';
   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 DISTINCT ON (index, series, version, d.tenor) index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2
                FROM index_quotes d WHERE date BETWEEN $1 - INTERVAL ''3 days'' AND $1 ORDER BY index, series, version, d.tenor, date desc)
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 $3 ELSE 1 END),
    -temp.notional * temp.fixed_rate/100. * temp.fact * $4 / 360 *
    (CASE WHEN temp.currency = ''EUR'' THEN $3 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, fund, eur_fx, days, strat;
ELSE
    RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW bowdst_index AS
SELECT as_of_date, p_index, p_series, maturity, admin_notional, admin_clean_nav FROM (
SELECT as_of_date, substring(security_description_1 FROM '(IG|HY|EU|XO)(?:R|VER)?(?:\.| )(?:[0-9]{2})')::index_type AS p_index,
    substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)(?:\.| )([0-9]{2})')::smallint as p_series,
    maturity_date AS maturity,
    (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional,
    sum(base_market_value) OVER w as admin_clean_nav,
    row_number() OVER w
FROM bowdst_val WHERE security_description_1 LIKE '%CCP%'
WINDOW w AS (PARTITION BY
                as_of_date,
                substring(security_description_1 FROM '(IG|HY|EU|XO)(?:R|VER)?(?:\.| )(?:[0-9]{2})')::index_type,
                substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)(?:\.| )([0-9]{2})')::smallint,
                maturity_date)
) AS ss WHERE row_number=1;

CREATE OR REPLACE function list_cds_marks(
       p_date date,
       strat cds_strat DEFAULT NULL::cds_strat,
       p_fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(security_id varchar(12), security_desc varchar(32), index index_type,
              series smallint,
              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
  sqlquery text;
  and_clause text;
BEGIN
IF strat IS NOT NULL THEN
   and_clause := 'AND strat = ltrim($2::text, ''SER_'')::strategy';
ELSE
   and_clause := '';
END IF;
IF p_fund = 'SERCGMAST' THEN
sqlquery := format('
SELECT l.*, b.globeop_nav, b.globeop_notional FROM list_cds_marks_pre($1, $2, $3) l
JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav,
             sum(endqty) AS globeop_notional
      FROM (SELECT periodenddate as date,
                   endqty,
                   endbooknav,
                   split_part(invid, ''_'', 2) AS security_id,
                   split_part(invid, ''_'', 6)::date AS maturity
            FROM valuation_reports WHERE invid LIKE ''CDS\_%%'' %s) a
      GROUP BY date, a.security_id, a.maturity HAVING date=$1) b
USING (security_id, maturity)', and_clause);
ELSIF p_fund = 'BOWDST' THEN
sqlquery := 'SELECT a.*, bowdst_index.admin_clean_nav, bowdst_index.admin_notional FROM list_cds_marks_pre($1, $2, $3) a LEFT JOIN bowdst_index USING (p_index, p_series, maturity) WHERE as_of_date=$1';
ELSIF p_fund = 'BRINKER' THEN
sqlquery := 'SELECT  *, 0::double precision, 0::double precision FROM list_cds_marks_pre($1, $2, $3)';
END IF;
RETURN QUERY EXECUTE sqlquery USING p_date, strat, p_fund;
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),
       notional float,
       clean_nav float,
       accrued float,
       duration float,
       delta float,
       gamma float,
       theta float,
       corr01 float,
       tranche_factor float,
       upfront float,
       running float,
       corr_attach float,
       corr_detach float,
       index_refprice float,
       index_refspread float,
       index_duration float,
       hy_equiv float,
       theta_amount 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 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, 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, p_fund fund DEFAULT 'SERCGMAST'::fund)
  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 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, portfolio portfolio, indexfactor float) 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 -  coalesce(terminated_amount, 0.)),
       a.option_type, a.strike, a.expiration_date, b.market_value,
       coalesce(endbooknav, base_market_value),
       f.ia, b.date, b.delta, b.gamma, b.vega,
       b.theta, c.index, c.series, c.tenor, d.duration, a.portfolio, c.indexfactor
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.full_globeop_id
LEFT JOIN bowdst_val ON link_ref = a.dealid AND as_of_date=p_date
LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount
           FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid)
LEFT JOIN external_marks_deriv f ON identifier=cpty_id AND f.date=p_date
WHERE a.notional IS DISTINCT FROM terminated_amount
AND a.expiration_date > p_date AND trade_date <= p_date
AND swap_type = 'CD_INDEX_OPTION'
AND (periodenddate IS NULL OR periodenddate <= p_date)
AND a.fund = p_fund
ORDER BY dealid, date DESC, periodenddate DESC;
END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION list_ir_capfloor_positions(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
  RETURNS TABLE(deal_id character varying, strategy swaption_strat, amount double precision,
  cap_or_floor cap_or_floor, strike double precision, floating_rate_index character varying,
  expiration_date date, initial_margin_percentage double precision,
  comments character varying, cp_code character varying, nav double precision ) AS $$
BEGIN
RETURN QUERY
SELECT a.dealid, folder,
       (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.amount -  coalesce(terminated_amount, 0.)),
      a.cap_or_floor, a.strike, a.floating_rate_index, a.expiration_date, a.initial_margin_percentage,
      a.comments, a.cp_code, c.base_nav
FROM capfloors a
LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount
           FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid)
LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv WHERE date=p_date) c ON a.cpty_id = c.identifier
WHERE a.amount IS DISTINCT FROM terminated_amount
AND  a.expiration_date > p_date
AND a.trade_date <= p_date
AND fund=p_fund;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION list_ir_swaption_positions(IN p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
  RETURNS TABLE(deal_id character varying, strategy swaption_strat, notional double precision,
  option_type option_type, strike double precision, security_id varchar, expiration_date date, maturity date,
  initial_margin_percentage double precision, cp_code character varying, nav double precision) AS $$
BEGIN
RETURN QUERY
SELECT a.dealid, folder,
       (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional -  coalesce(terminated_amount, 0.)),
       a.option_type, a.strike/100, a.security_id, a.expiration_date, a.maturity, a.initial_margin_percentage,
       a.cp_code, c.base_nav
FROM swaptions a
LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount
           FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid)
LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv where date = p_date) c on a.cpty_id = c.identifier
WHERE a.notional IS DISTINCT FROM terminated_amount
AND  a.expiration_date > p_date
AND a.trade_date <= p_date
AND a.swap_type = 'SWAPTION'
AND fund=p_fund;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE function list_abscds_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund)
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, 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
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 plpython3u;

CREATE OR REPLACE function days_accrued(p_date date)
RETURNS integer AS $$
from dates import days_accrued
return days_accrued(p_date)
$$ LANGUAGE plpython3u;

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 ON temp.identifier=securities.figi;

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;

CREATE OR REPLACE FUNCTION id_translate(VARIADIC id1 varchar(9)[])
RETURNS TABLE(id2 varchar(12)) AS $$
BEGIN
       RETURN QUERY SELECT figi FROM securities
       JOIN unnest(id1) WITH ORDINALITY AS t(cusip, id)
       USING (cusip) ORDER BY 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_maturity_markit,
 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;
SET enable_nestloop = off;
RETURN QUERY EXECUTE query USING p_date, p_cusip, latest_sim(p_date);
SET enable_nestloop = on;
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,
       figi         varchar(12) NOT NULL,
       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,
       bond_yield     float,
       hy_equiv       float,
       delta_ir       float,
       PRIMARY KEY (date, figi))

CREATE TABLE beta(
        date            date,
        asset_class     beta_type,
        beta            float,
        PRIMARY KEY (date, asset_class))

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', 'BRINKER', 'BOWDST')

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',
'XOEQY', 'XOMEZ', 'XOINX', 'EUEQY', 'EUMEZ', 'EUSNR', 'EUINX',
-- CURVE portfolio
'IGCURVE', 'IGCVECSH', 'ITRXCURVE', 'ITRXCVCSH', 'HYCURVE', 'XCURVE', 'CVECSH',
-- OPTIONS portfolio
'IGOPTDEL', 'IGPAYER', 'IGREC', 'HYOPTDEL', 'HYPAYER', 'HYREC', 'IGCDSCSH', 'HYCDSCSH', 'COCSH'
-- IR portfolio
'IR', 'IRDEVCSH', 'STEEP', 'FLAT', 'DELTAONE', 'DV01',
-- 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_REPO'
'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', 'Payment-US');
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,
       "1st_cpn_date" date,
       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 plpython3u;

CREATE OR REPLACE VIEW tranche_risk_serenitas AS
SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, index, series, maturity, orig_attach,
       orig_detach, tranche_risk.notional,
       admin_notional,
       clean_nav as serenitas_clean_nav,
       admin_clean_nav,
       accrued as serenitas_accrued, admin_accrued,
       base_nav AS cpty_nav,
       duration, delta, gamma, theta, theta_amount, tranche_factor,
       tranche_risk.corr_attach, tranche_risk.corr_detach,
       tranche_risk.upfront, tranche_risk.running,
       index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage
FROM tranche_risk
LEFT JOIN LATERAL
(SELECT full_globeop_id, globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id AND date <= tranche_risk.date ORDER by date desc LIMIT 1) b ON true
LEFT JOIN cds ON (tranche_id=id)
RIGHT JOIN (SELECT invid, periodenddate,
                  sum(endbookunrealincome) AS admin_accrued,
                  sum(endbooknav-endbookunrealincome) AS admin_clean_nav,
                  sum(endqty) AS admin_notional
           FROM valuation_reports GROUP BY invid, periodenddate) a
     ON (invid=b.full_globeop_id AND periodenddate=tranche_risk.date)
LEFT JOIN index_version ON (security_id=redindexcode)
LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date
ORDER BY index, series, orig_attach;

CREATE OR REPLACE VIEW tranche_risk_bowdst AS
SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach,
       orig_detach, tranche_risk.notional,
       admin_notional,
       clean_nav as serenitas_clean_nav,
       admin_clean_nav,
       accrued as serenitas_accrued,
       NULL AS admin_accrued,
       base_nav AS cpty_nav,
       duration, delta, gamma, theta, theta_amount, tranche_factor,
       tranche_risk.corr_attach, tranche_risk.corr_detach,
       tranche_risk.upfront, tranche_risk.running,
       index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage
FROM tranche_risk
LEFT JOIN cds ON (tranche_id=id)
LEFT JOIN (SELECT as_of_date, link_ref, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, base_market_value AS admin_clean_nav FROM bowdst_val WHERE security_description_1 LIKE 'TR%NCH%' AND abs(base_market_value) > 1.0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date
LEFT JOIN index_version ON (security_id=redindexcode)
LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date
WHERE fund='BOWDST'
ORDER BY index, series, orig_attach;

CREATE OR REPLACE VIEW tranche_risk_brinker AS
SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach,
       orig_detach, tranche_risk.notional,
       admin_notional,
       clean_nav as serenitas_clean_nav,
       admin_clean_nav,
       accrued as serenitas_accrued,
       NULL AS admin_accrued,
       base_nav AS cpty_nav,
       duration, delta, gamma, theta, theta_amount, tranche_factor,
       tranche_risk.corr_attach, tranche_risk.corr_detach,
       tranche_risk.upfront, tranche_risk.running,
       index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage
FROM tranche_risk
LEFT JOIN cds ON (tranche_id=id)
LEFT JOIN (SELECT accounting_date, security_id, quantity * (CASE WHEN long_short_indicator = 'S' THEN 1. ELSE -1. END) AS admin_notional, (local_market_value - quantity) * fx_rate AS admin_clean_nav
FROM bbh_val WHERE sub_security_type_code ='CXT' AND interest_rate IS NOT NULL) b ON b.security_id=format('SCCDS%s', tranche_id) AND accounting_date=tranche_risk.date
LEFT JOIN index_version ON (cds.security_id=redindexcode)
LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date
WHERE fund='BRINKER'
ORDER BY index, series, orig_attach;

CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund)
RETURNS TABLE(date date, security_desc varchar(32), index index_type, series smallint, admin_notional float, admin_clean_nav float,
maturity date, serenitas_notional float, tranche_factor float, running float, serenitas_clean_nav float, serenitas_accrued float, initial_margin float, theta float, theta_amount float, duration float, delta float, gamma float, hy_equiv float, upfront float, index_refprice float, index_refspread float, index_duration float, orig_attach smallint, orig_detach smallint) AS $$
DECLARE
    query text;
    fund text;
BEGIN
IF p_fund = 'SERCGMAST' THEN
   fund := 'serenitas';
ELSE
    fund := lower(p_fund::text);
END IF;
query := 'SELECT date, security_desc, index, series, sum(admin_notional),
                 sum(admin_clean_nav), maturity, sum(notional),
                 avg(tranche_factor), avg(running),
                 sum(serenitas_clean_nav),
                 sum(serenitas_accrued),
                 sum(initial_margin_percentage * tranche_factor * abs(notional)/100),
                 avg(theta), sum(theta_amount), avg(duration), avg(delta),
                 avg(gamma), sum(hy_equiv), avg(upfront), avg(index_refprice),
                 avg(index_refspread), avg(index_duration),
                 orig_attach, orig_detach
          FROM tranche_risk_%I
          GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity
	      HAVING date=$1
          ORDER BY index, series';
query := format(query, fund);
RETURN QUERY EXECUTE query USING p_date;
END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE VIEW tranche_risk_master AS
SELECT tranche_risk.date, tranche_id AS trade_id, fund, security_desc, index, series, maturity, orig_attach,
       orig_detach, tranche_risk.notional,
       clean_nav * coalesce(fx, 1.) as serenitas_clean_nav,
       accrued * coalesce(fx, 1.) as serenitas_accrued,
       nav AS cpty_nav,
       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, ia as cpty_ia, indexfactor
FROM tranche_risk
LEFT JOIN cds ON (tranche_id=id)
LEFT JOIN index_version ON (security_id=redindexcode)
LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency)
LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date
ORDER BY date desc, 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 fcm_moneyline(
       date date NOT NULL,
       account text NOT NULL,
       currency currency NOT NULL,
       beginning_balance float NOT NULL,
       cds_initial_coupon float NOT NULL,
       cds_reset_to_par float NOT NULL,
       pai float NOT NULL,
       clearing_fees float NOT NULL,
       transaction_fees float NOT NULL,
       net_dep_withdraw float NOT NULL,
       ending_balance float NOT NULL,
       account_value_market float NOT NULL,
       realized_pnl float NOT NULL,
       current_im float NOT NULL,
       current_excess_deficit 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,
       fund fund NOT NULL,
       PRIMARY KEY (date, strategy, broker, fund)
)

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

CREATE OR REPLACE VIEW swaption_trades AS
SELECT swaptions.id,
       dealid,
       termination_date AS trade_date,
       fee_payment_date AS settle_date,
       termination_amount AS notional,
       security_desc,
       security_id,
       CASE WHEN termination_cp=cp_code THEN
         'Termination'
       ELSE
         'Assignment'
       END AS trade_type,
       folder,
       portfolio,
       fund,
       termination_cp AS cp_code,
       name,
       option_type,
       expiration_date,
       strike,
       NOT buysell AS buysell,
       -termination_fee AS fee
FROM terminations RIGHT JOIN swaptions USING (dealid)
LEFT JOIN counterparties ON termination_cp = code
WHERE termination_date IS NOT NULL
UNION ALL (
SELECT id,
       dealid,
       trade_date,
       settle_date,
       notional,
       security_desc,
       security_id,
       'New' AS trade_type,
       folder,
       portfolio,
       fund,
       cp_code,
       name,
       option_type,
       expiration_date,
       strike,
       buysell,
       notional * price / 100 * (2* buysell::integer -1.) * COALESCE(indexfactor / 100, 1.)
FROM swaptions JOIN counterparties ON cp_code = code
               LEFT JOIN index_version ON security_id = redindexcode) ORDER BY trade_date DESC;

CREATE OR REPLACE VIEW cds_trades AS
SELECT cds.id,
       dealid,
       termination_date AS trade_date,
       termination_amount AS notional,
       security_desc,
       security_id,
       CASE WHEN termination_cp=cp_code THEN
         'Termination'
       ELSE
         'Assignment'
       END AS trade_type,
       folder,
       fund,
       termination_cp AS cp_code,
       name,
       orig_attach,
       orig_detach,
       attach,
       detach,
       CASE WHEN protection = 'Buyer' THEN
         'Seller'::protection
       ELSE
         'Buyer'::protection
       END AS protection,
       termination_fee AS upfront,
       terminations.traded_level
FROM terminations RIGHT JOIN cds USING (dealid)
LEFT JOIN counterparties on termination_cp=code
WHERE termination_date is NOT NULL
UNION ALL (
SELECT id,
       dealid,
       trade_date,
       notional,
       security_desc,
       security_id,
       'New',
       folder,
       fund,
       cp_code,
       name,
       orig_attach,
       orig_detach,
       attach,
       detach,
       protection,
       upfront,
       traded_level
FROM cds JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder;

CREATE TABLE curve_risk(
       date date NOT NULL,
       strategy cds_strat,
       "VaR" float,
       currency currency,
       fund fund,
       PRIMARY KEY (date, strategy, fund))

CREATE OR REPLACE VIEW tranche_pnl AS
WITH temp AS (
   SELECT  date, tranche_id, fund, clean_nav + accrued - lag(clean_nav+accrued, -1, -cds.upfront) over (partition by tranche_id ORDER BY date DESC) AS daily_pnl
   FROM tranche_risk JOIN cds ON tranche_id=id
)
SELECT date, fund, tranche_id, daily_pnl, sum(daily_pnl) OVER (PARTITION BY tranche_id, date_trunc('month', date) ORDER BY date) AS mtd_pnl FROM temp;


create table id_mapping(
       date date not null,
       trade_type text not null,
       serenitas_id int not null,
       globeop_id int not null,
       full_globeop_id text GENERATED ALWAYS AS (trade_type||lpad(globeop_id::text, 6, '0')|| 'K00SCLMA') stored,
       PRIMARY KEY (date, trade_type, serenitas_id)
);

CREATE TABLE tranche_cashflows(
       date date NOT NULL,
       tranche_id integer NOT NULL,
       principal float,
       accrued float,
       currency currency,
       PRIMARY KEY (date, tranche_id)
);

CREATE TABLE beta_crt(
       date date NOT NULL,
       strategy bond_strat NOT NULL ,
       beta_crt float,
       PRIMARY KEY (date, strategy)
);

CREATE OR REPLACE VIEW public.payment_settlements
AS SELECT trades.settle_date,
    trades.fund,
    cps.name,
    trades.cp_code,
    trades.asset_class,
    trades.currency,
    sum(trades.payment_amount)::numeric(10,2) AS payment_amount,
    array_agg(trades.id) AS ids
   FROM ( SELECT bond_trades.id,
            bond_trades.settle_date,
            bond_trades.fund,
            bond_trades.cp_code,
            'bond'::text AS asset_class,
            'USD'::currency AS currency,
                CASE
                    WHEN bond_trades.buysell THEN - bond_trades.net_amount
                    WHEN NOT bond_trades.buysell THEN bond_trades.net_amount
                    ELSE NULL::double precision
                END AS payment_amount
           FROM bond_trades
          WHERE bond_trades.tradeid IS NOT NULL
        UNION
         SELECT cds_trades.id,
            cds_trades.settle_date,
            cds_trades.fund,
            cds_trades.cp_code,
            'tranche'::text AS asset_class,
            cds_trades.currency,
            cds_trades.upfront AS payment_amount
           FROM cds_trades
          WHERE cds_trades.orig_attach IS NOT NULL
        UNION
         SELECT swaptions.id,
            swaptions.settle_date,
            swaptions.fund,
            swaptions.cp_code,
            'swaption'::text AS asset_class,
            swaptions.currency,
                CASE
                    WHEN swaptions.buysell THEN (- swaptions.notional) * swaptions.price / 100::double precision
                    WHEN NOT swaptions.buysell THEN swaptions.notional * swaptions.price / 100::double precision
                    ELSE NULL::double precision
                END AS payment_amount
           FROM swaptions
        UNION
         SELECT spots.id,
            spots.settle_date,
            spots.fund,
            spots.account_code AS cp_code,
            'spot'::text AS asset_class,
            unnest(ARRAY[spots.buy_currency, spots.sell_currency]) AS currency,
            unnest(ARRAY[spots.buy_amount, - spots.sell_amount]) AS amount
           FROM spots) trades
     LEFT JOIN ( SELECT DISTINCT ON (cp_code.cp_code) cp_code.cp_code,
            cp_code.name
           FROM ( SELECT c.code AS cp_code,
                    c.name
                   FROM counterparties c
                UNION
                 SELECT accounts.code AS cp_code,
                    accounts.name
                   FROM accounts) cp_code) cps USING (cp_code)
  GROUP BY trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency
  ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency;



  CREATE TABLE public.equities (
	id serial4 NOT NULL,
	dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQ'::text || id::text))) STORED,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	folder future_strat 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,
	security_desc varchar(32) NOT NULL,
	"currency" currency NOT NULL,
	exchange varchar(3) NOT NULL,
	"fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	account_code text NOT NULL DEFAULT 'IB'::text,
	CONSTRAINT equities_dealid_key UNIQUE (dealid),
	CONSTRAINT equities_pkey PRIMARY KEY (id),
	CONSTRAINT equities_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code)
);

CREATE TABLE public.equity_options (
	id serial4 NOT NULL,
	dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQOPT'::text || id::text))) STORED,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
	folder future_strat 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,
	option_type equity_option_type NOT NULL,
	price float8 NOT NULL,
	commission float8 NULL,
	security_desc varchar(32) NOT NULL,
	"currency" currency NOT NULL,
	exchange varchar(3) NOT NULL,
	"fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	account_code text NOT NULL DEFAULT 'IB'::text,
	CONSTRAINT equity_options_dealid_key UNIQUE (dealid),
	CONSTRAINT equity_options_pkey PRIMARY KEY (id),
	CONSTRAINT equity_options_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code)
);