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',
       'CRT_SD',
       'IGNORE'
       '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', 'CASH_BASIS', '*');

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', 'OIS_SWAP', 'TOTAL_RETURN_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 TYPE deal_type AS ENUM ('Swaption', 'CDS', 'TRS', 'CAP');

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 accounts2 (
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
	"name" text NULL,
	custodian text NOT NULL,
	cash_account text NOT NULL,
	"fund" fund NOT NULL,
	"account_type" account_type NOT NULL,
	active bool NOT NULL,
    cp_code varchar(10),
	CONSTRAINT accounts2_cash_account_key UNIQUE (cash_account),
	CONSTRAINT accounts2_pkey PRIMARY KEY (id)
);

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),
       ctm_code text;

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,
       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,
       bbg_ticket_id text NULL REFERENCES bond_tickets(bbg_ticket_id) ON DELETE SET NULL,
      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 TABLE portfolio_folder_mapping(
folder strategy PRIMARY KEY,
portfolio portfolio NOT NULL
valid boolean NOT NULL);

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


CREATE OR REPLACE FUNCTION update_portf2()
RETURNS TRIGGER
AS $$
BEGIN
SELECT pfm.portfolio FROM portfolio_folder_mapping pfm INTO NEW.portfolio where folder::text=NEW.folder::text;
RETURN NEW;
END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER bond_notify BEFORE
INSERT OR UPDATE OF
    identifier,
    cusip,
    isin,
	faceamount,
    price,
    trade_date,
	settle_date,
    accrued,
    asset_class
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', 'Acknowledged', 'Failed',);

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) NOT NULL,
       fund fund NOT NULL DEFAULT 'SERCGMAST',
       lastupdate timestamptz DEFAULT now(),
       action action NOT NULL DEFAULT 'NEW',
       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 DEFAULT 'Following',
       notional float NOT NULL,
       fixed_rate float NOT NULL,
       day_count day_count NOT NULL DEFAULT 'ACT/360',
       frequency smallint NOT NULL DEFAULT 4,
       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,
       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 BEFORE 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 WHEN (current_setting('application_name') != 'autobooker') EXECUTE FUNCTION notify_id();

CREATE OR REPLACE FUNCTION cds_enrich()
RETURNS TRIGGER
AS $$
from serenitas.analytics.api import CreditIndex
trade = TD["new"]
if trade["swap_type"] == "BESPOKE":
    return
if TD["event"] == "INSERT" and all([trade["upfront"], trade["traded_level"]]):
    return
index = CreditIndex(
        redcode=trade["security_id"],
        maturity=trade["maturity"],
        notional=trade["notional"],
        value_date=trade["trade_date"],
)
index.direction = trade["protection"]
if trade["traded_level"]:
    if trade["swap_type"] == "CD_INDEX":
        index.ref = float(trade["traded_level"])
        upfront = -index.pv
    else:
        tranche_factor = (trade["detach"] - trade["attach"]) / (
            trade["orig_detach"] - trade["orig_attach"]
        )
        accrued = index._accrued * trade["fixed_rate"]
        match index.index_type:
            case "HY":
                dirty_price = float(trade["traded_level"]) + accrued
                upfront = (
                    -(100 - dirty_price)
                    * index.notional
                    * tranche_factor
                    * 0.01
                )
            case "EU" | "XO" if trade["orig_attach"] in (6, 12, 35):
                if trade["orig_attach"] == 6:
                    index.recovery = 0.0
                index.spread = float(trade["traded_level"])
                upfront = -index._pv * trade["notional"] * tranche_factor
            case _:
                dirty_protection = float(trade["traded_level"]) - accrued
                upfront = (
                    -dirty_protection
                    * index.notional
                    * tranche_factor
                    * 0.01
                )
        trade["upfront"] = upfront
else:
    index.pv = trade["upfront"]
    trade["traded_level"] = index.ref
return "MODIFY"
$$ LANGUAGE plpython3u;


CREATE OR REPLACE TRIGGER upfront_setter AFTER
INSERT
	OR
UPDATE OF
    security_id,
	maturity,
	trade_date,
	upfront_settle_date,
	traded_level,
	notional,
	protection
ON
	cds FOR EACH ROW WHEN (current_setting('application_name') != 'autobooker') EXECUTE FUNCTION cds_enrich();


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) NOT NULL UNIQUE,
       fund fund NOT NULL DEFAULT 'SERCGMAST',
       lastupdate timestamptz DEFAULT now(),
       action action NOT NULL,
       portfolio portfolio NOT NULL,
       folder swaption_strat NOT NULL,
       cash_account text NOT NULL REFERENCES accounts2(cash_account),
       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 BEFORE INSERT ON swaptions
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();

CREATE TABLE account_counterparty (
	account_id int4 NULL,
	cp_code varchar(12) NULL,
	"trade_type" text NOT NULL,
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
	"name" text NULL,
	CONSTRAINT account_counterparty_account_id_cp_code_trade_type_key UNIQUE (account_id, cp_code, trade_type),
	CONSTRAINT account_counterparty_pkey PRIMARY KEY (id),
	CONSTRAINT account_counterparty_account_id_fkey FOREIGN KEY (account_id) REFERENCES accounts2(id),
	CONSTRAINT account_counterparty_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code) ON UPDATE CASCADE
);


CREATE OR REPLACE FUNCTION update_account()
RETURNS TRIGGER
AS $$
BEGIN
SELECT cash_account INTO STRICT NEW.cash_account FROM accounts2 LEFT JOIN account_counterparty ON accounts2.id=account_id WHERE trade_type=TG_ARGV[0] AND fund=NEW.fund AND account_counterparty.cp_code=NEW.cp_code;
RETURN NEW;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER cash_account BEFORE
INSERT OR UPDATE OF
   fund,
   cp_code
ON
    swaptions
FOR EACH ROW
EXECUTE PROCEDURE update_account('ISDA');

CREATE TRIGGER cash_account BEFORE
INSERT OR UPDATE OF
   fund,
   cp_code
ON
    irs
FOR EACH ROW
EXECUTE PROCEDURE update_account('Cleared Rate');

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 BEFORE 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,
    portfolio portfolio NOT NULL,
	folder future_strat NOT NULL,
	cash_account text NOT NULL REFERENCES accounts2(cash_account),
	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 BEFORE
INSERT ON futures
FOR EACH ROW
EXECUTE PROCEDURE auto_dealid();

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

CREATE TABLE wires (
    id serial NOT NULL,
    fund fund NOT NULL,
    dealid varchar(28) NOT NULL,
    lastupdate timestamp NULL DEFAULT now(),
    "action" action NOT NULL,
    folder cash_strat NOT NULL,
    portfolio portfolio NOT NULL,
    cash_account text NOT NULL REFERENCES accounts2(cash_account),
    amount float8 NOT NULL,
    currency currency NOT NULL,
    trade_date date NOT NULL,
    author author default 'manual',
    CONSTRAINT wires_pkey PRIMARY KEY (id),
);

ALTER table wires OWNER TO dawn_user;

CREATE TRIGGER dealid BEFORE
INSERT ON wires
FOR EACH ROW
EXECUTE PROCEDURE auto_dealid();

CREATE TRIGGER portf2 BEFORE
INSERT OR UPDATE OF
    folder
ON
    wires
FOR EACH ROW
EXECUTE PROCEDURE update_portf2();



CREATE TABLE spots (
	id serial NOT NULL,
	fund fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	dealid varchar(28) NOT NULL,
	lastupdate timestamp NULL DEFAULT now(),
	"action" action NOT NULL,
    portfolio portfolio NOT NULL,
	folder spot_strat NOT NULL,
	cash_account text NOT NULL REFERENCES accounts2(cash_account),
    cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
	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,
    initial_margin_percentage float8,
    cpty_id text,
    globeop_id text,
    bbg_ticket_id text UNIQUE REFERENCES fx_tickets(bbg_ticket_id),
	CONSTRAINT spots_pkey PRIMARY KEY (id),
);

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

CREATE TRIGGER dealid BEFORE
INSERT ON
spots
FOR EACH ROW
EXECUTE PROCEDURE auto_dealid();

CREATE TABLE fx_swaps (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	dealid varchar(28) NOT NULL GENERATED ALWAYS AS ((('FXSWAP_'::text || id::text))) STORED,
	"fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund,
	lastupdate timestamp NULL DEFAULT now(),
	"portfolio" portfolio NOT NULL,
	folder spot_strat NOT NULL,
    cash_account text NOT NULL REFERENCES accounts2(cash_account),
	cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
	trade_date date NOT NULL,
	near_rate float8 NOT NULL,
	near_settle_date date NOT NULL,
	near_buy_currency currency NOT NULL,
	near_buy_amount float8 NOT NULL,
	near_sell_currency currency NOT NULL,
	near_sell_amount float8 NOT NULL,
	far_rate float8 NOT NULL,
	far_settle_date date NOT NULL,
	far_buy_currency currency NULL,
	far_buy_amount float8 NOT NULL,
	far_sell_currency currency NULL,
	far_sell_amount float8 NOT NULL,
	near_cpty_id text NULL,
	far_cpty_id text NULL,
    bbg_ticket_id text UNIQUE REFERENCES fx_tickets(bbg_ticket_id),
    action action not null,
);

CREATE OR REPLACE VIEW forwards AS
     SELECT  UNNEST(ARRAY[dealid || '_near', dealid || '_far']) AS dealid, trade_date, unnest(ARRAY[near_settle_date, far_settle_date]) AS settle_date,
            fund,
            portfolio,
            folder,
            cp_code,
            UNNEST(ARRAY[near_buy_currency, far_buy_currency]) AS buy_currency,
            UNNEST(ARRAY[near_sell_currency, far_sell_currency]) AS sell_currency,
            UNNEST(ARRAY[near_buy_amount, far_buy_amount]) AS buy_amount,
            UNNEST(ARRAY[near_sell_amount, far_sell_amount]) AS sell_amount,
            UNNEST(ARRAY[near_cpty_id, far_cpty_id]) AS cpty_id
     FROM fx_swaps
   UNION
     SELECT  dealid, trade_date, settle_date, fund, portfolio, folder, cp_code, buy_currency, sell_currency, buy_amount, sell_amount, cpty_id FROM spots;

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 OR REPLACE FUNCTION auto_dealid()
    RETURNS TRIGGER AS $$
    DECLARE stub text;
    BEGIN
      IF NEW.dealid is NULL THEN
         IF (TG_TABLE_NAME = 'bonds') THEN
            NEW.dealid := 'SC_' || upper(left(NEW.asset_class::text,3)) || 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;
            NEW.dealid := stub || NEW.id;
         END IF;
      END IF;
      RETURN NEW;
    END;
$$ language plpgsql;

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 NOT NULL,
       price float NOT NULL,
       identifier varchar(12) NOT NULL REFERENCES securities(figi) ON DELETE CASCADE ON UPDATE CASCADE,
       id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       UNIQUE (date, identifier)
);

CREATE UNIQUE INDEX ON marks(date, identifier);

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) NOT NULL REFERENCES securities(figi) ON UPDATE CASCADE,
       date date NOT NULL,
       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 ON a.figi=b.identifier
  LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers
  WHERE date <=p_date ORDER BY identifier, date DESC) c USING ON a.figi=c.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 strategy,
              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, figi varchar(12)) 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 (identifier) securities.identifier, securities.description, notional,
  substring(folder::text FROM ''(?:M_)?(.*)'')::strategy AS folder,
  securities.coupon, start_accrued_date, settle_date, temp.principal_payment,
  temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type, securities.figi
  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 strategy, 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, figi varchar(12)) 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.figi
  FROM list_positions(p_date, p_assetclass, true, p_fund) a
  LEFT JOIN factors_history(p_date) b ON a.figi = b.identifier
  LEFT JOIN list_marks(p_date) c ON a.figi = c.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 isosel_pnl_report AS
SELECT citco_reports.period_end_date,
    citco_reports."quantity(end)",
    citco_reports.bloomberg_code,
    citco_reports.symbol,
    split_part(citco_reports.strategy,'/',1) as Port,
    split_part(citco_reports.strategy,'/',2) as Strat,
    citco_reports."prime_broker/clearing_broker",
    citco_reports.subfund,
    citco_reports.cusip,
    citco_reports.isin,
    citco_reports.base_price,
    citco_reports.base_market_value,
    citco_reports.underlying_currency_code,
    citco_reports.security_description,
    citco_reports.security_asset_name,
    citco_reports.security_asset_class,
    citco_reports.underlying_symbol,
    citco_reports.factor,
    citco_reports.underlying_red,
    citco_reports."mtd_base_unrealized_p&l",
    citco_reports."mtd_base_realized_p&l",
    citco_reports."daily_base_p/l",
    citco_reports."monthly_base_p/l"
   FROM citco_reports
  WHERE citco_reports.subfund = 'ISOSEL_DFLT'::text;

CREATE OR REPLACE VIEW isosel_val_report AS
SELECT citco_reports.period_end_date,
    citco_reports."quantity(end)",
    citco_reports.bloomberg_code,
    citco_reports.symbol,
    split_part(citco_reports.strategy,'/',1) as Port,
    split_part(citco_reports.strategy,'/',2) as Strat,
    citco_reports."prime_broker/clearing_broker",
    citco_reports.subfund,
    citco_reports.cusip,
    citco_reports.isin,
    citco_reports.base_price,
    citco_reports.base_market_value,
    citco_reports.base_bond_accrued_interest,
    citco_reports.end_face_value as factored_notional,
    citco_reports.underlying_currency_code,
    citco_reports.security_description,
    citco_reports.security_asset_name,
    citco_reports.security_asset_class,
    citco_reports.underlying_symbol,
    citco_reports.factor,
    citco_reports.underlying_red,
    citco_reports.base_nav_contribution,
    citco_reports.total_fund_capital
   FROM citco_reports
  WHERE citco_reports.subfund = 'ISOSEL_DFLT'::text;

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 list_cds_positions_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, notional float) AS $$
BEGIN
RETURN QUERY
  WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate,
       cds.currency,
       SUM(cds.notional)
       OVER (PARTITION BY cds.security_id, cds.maturity, 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) * FROM tmp WHERE abs(tmp.notional) > 0.1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trade_upload_baml(p_date date, p_fcm text, p_fund fund DEFAULT 'SERCGMAST::fund')
RETURNS TABLE("Type" text, "CCP ID" text, "B/S Protection" text, "Identifier" text, "Notional" float, "Fixed Rate" integer, "Trade Date" date, "Maturity Date" date, CCY text, "Payment Freq" text)
SELECT 'CDX',
       123456,
       CASE WHEN notional >0 THEN 'BUY' ELSE 'SELL' END,
       tradeid,
       abs(notional),
       fixed_rate * 100,
       '6/27/2022',
       l.maturity,
       currency,
       'QUARTERLY'
FROM
    list_cds_positions_fcm('2022-06-27', 'BAML') l
LEFT JOIN
     index_maturity_markit imm ON redindexcode=security_id AND imm.maturity=l.maturity
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
   IF p_fund = 'ISOSEL' THEN
       and_clause := 'AND split_part(strategy, ''/'', 2) = $2::text';
   ELSE
       and_clause := 'AND strat = ltrim($2::text, ''SER_'')::strategy';
   END IF;
ELSE
   and_clause := '';
END IF;
IF p_fund = 'SERCGMAST' OR p_fund = 'BOWDST' 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,
                   fund,
                   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, fund, a.security_id, a.maturity HAVING date=$1 AND fund=$3) 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)';
ELSIF p_fund = 'ISOSEL' THEN
sqlquery := format('
SELECT l.*, b.dirty_nav, b.position FROM list_cds_marks_pre($1, $2, $3) l
LEFT JOIN index_version ON security_id=redindexcode
LEFT JOIN index_maturity USING (INDEX, series, maturity)
LEFT JOIN (
SELECT ticker_bbg AS bbg_id, -sum(position) AS position, sum("end_mkt_value_[n]"+"bond_accrual_[n]") AS dirty_nav,
sum(bond_accrual) FROM isosel_accrued WHERE instr_type=''Credit Default Swap''
AND ticker_bbg IS NOT NULL AND status in (''Open'', ''P'') %s GROUP BY period_end_date, ticker_bbg HAVING period_end_date=$1 ) b USING (bbg_id)', and_clause);
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,
       ir_dv01 float,
       PRIMARY KEY (date, tranche_id));

CREATE TABLE ir_swaption_risk(
       date date,
       swpt_id integer REFERENCES swaptions(id),
       notional float,
       pv float,
       vol float,
       vol_type text,
       "DV01" float,
       "IRGamma1bp" float,
       vega float,
       PRIMARY KEY (date, swpt_id));

CREATE TABLE ir_swap_risk(
       date date,
       swp_id integer REFERENCES irs(id),
       notional float,
       pv float,
       "DV01" float,
       "IRGamma1bp" float,
       PRIMARY KEY (date, swp_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, hy_equiv 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, end_mkt_value),
       f.ia, b.date, b.delta, b.gamma, b.vega,
       b.theta, b.hy_equiv, 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 NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K'), '')::integer = a.globeop_id
LEFT JOIN bowdst_val ON link_ref = a.dealid AND as_of_date=p_date
LEFT JOIN isosel_accrued ON a.dealid=isosel_accrued.init_fix_id
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,
       theta float,
       hy_equiv 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', 'LIMBO', 'ISOSEL')

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_BASIS',
-- CASH portfolio
'M_CSH_CASH', 'M_CSH_EXP',
-- GFS_HELPER_BUSINESS_UNIT portfolio
'GFS_TRANSFER_HELPER',
-- UNALLOCATED portfolio
'*',
-- 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 Not NULL,
        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),
        invticker varchar(12) NULL,
        pricelist text NULL,
        externalid text NULL,
        PRIMARY KEY(periodenddate, fund, row)
);

CREATE INDEX on valuation_reports (periodenddate);

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

CREATE TABLE pnl_reports(
        date date NOT NULL,
        fund fund NOT NULL,
        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 NOT NULL,
        PRIMARY KEY(date, fund, row)
);

CREATE INDEX on pnl_reports (date);

CREATE TYPE clearing_cp AS ENUM('ICE-CREDIT', 'NOT CLEARED', 'LCH-LTD');
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 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)
FULL 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 (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00SCLMA'), '')::integer IS NOT DISTINCT FROM b.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
WHERE fund='SERCGMAST'
ORDER BY index, series, orig_attach;

CREATE OR REPLACE VIEW ir_swaption_risk_serenitas AS
SELECT ir_swaption_risk.date, swpt_id as trade_id, swaptions.globeop_id, security_desc, maturity,
       admin_notional,
       pv as serenitas_pv,
       admin_pv,
       base_nav AS cpty_nav,
       vol, vol_type, "DV01", "IRGamma1bp", vega
       FROM ir_swaption_risk
LEFT JOIN swaptions ON (swpt_id=id)
FULL JOIN (SELECT invid, periodenddate,
                  sum(endbooknav) AS admin_pv,
                  sum(endqty) AS admin_notional
           FROM valuation_reports GROUP BY invid, periodenddate) a
     ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00SCLMA'), '')::integer=swaptions.globeop_id AND periodenddate=ir_swaption_risk.date)
LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=ir_swaption_risk.date
WHERE fund='SERCGMAST'
ORDER BY ir_swaption_risk.date DESC;

CREATE OR REPLACE VIEW ir_swaption_risk_bowdst AS
SELECT ir_swaption_risk.date, swpt_id as trade_id, swaptions.globeop_id, security_desc, maturity,
       admin_notional,
       pv as serenitas_pv,
       admin_pv,
       base_nav AS cpty_nav,
       vol, vol_type, "DV01", "IRGamma1bp", vega
       FROM ir_swaption_risk
LEFT JOIN swaptions ON (swpt_id=id)
FULL JOIN (SELECT invid, periodenddate,
                  sum(endbooknav) AS admin_pv,
                  sum(endqty) AS admin_notional
           FROM valuation_reports GROUP BY invid, periodenddate) a
     ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00HEEAD'), '')::integer=swaptions.globeop_id AND periodenddate=ir_swaption_risk.date)
LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=ir_swaption_risk.date
WHERE fund='BOWDST'
ORDER BY ir_swaption_risk.date DESC;

-- bony reports version
-- 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_bowdst 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 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)
FULL 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 (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00HEEAD'), '')::integer=b.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
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, fund, broker, strategy, currency)
)

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

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,
       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 payment_settlements
AS SELECT trades.settle_date,
    trades.fund,
    cps.name,
    trades.cp_code,
    trades.asset_class,
    trades.account,
    trades.currency,
    sum(trades.payment_amount)::numeric(10,2) AS payment_amount,
    array_agg(trades.id) AS ids
   FROM ( SELECT bond_trades.id::text AS id,
            bond_trades.settle_date,
            bond_trades.fund,
            bond_trades.cp_code,
            'BOND' AS asset_class,
            'Account'::text AS account,
            '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::text AS id,
            cds_trades.settle_date,
            cds_trades.fund,
            cds_trades.cp_code,
            'TRANCHE' AS asset_class,
            'Account'::text AS account,
            cds_trades.currency,
            cds_trades.upfront AS payment_amount
           FROM cds_trades
          WHERE cds_trades.orig_attach IS NOT NULL
        UNION
         SELECT swaptions.id::text AS id,
            swaptions.settle_date,
            swaptions.fund,
            swaptions.cp_code,
            'SWAPTION' AS asset_class,
            'Bilateral Trade'::text AS account,
            'USD'::currency AS currency,
            - swaptions.fee AS payment_amount
           FROM swaption_trades swaptions
        UNION
         SELECT spots.dealid::text AS dealid,
            spots.settle_date,
            spots.fund,
            spots.cp_code,
            'SPOT' AS asset_class,
            spots.cash_account AS account,
            unnest(ARRAY[spots.buy_currency, spots.sell_currency]) AS currency,
            unnest(ARRAY[spots.buy_amount, - spots.sell_amount]) AS amount
           FROM spots
        UNION
         SELECT fx_swaps.dealid,
            unnest(ARRAY[fx_swaps.near_settle_date, fx_swaps.near_settle_date, fx_swaps.far_settle_date, fx_swaps.far_settle_date]) AS settle_date,
            fx_swaps.fund,
            fx_swaps.cp_code,
            'SPOT' AS asset_class,
            fx_swaps.cash_account AS account,
            unnest(ARRAY[fx_swaps.near_buy_currency, fx_swaps.near_sell_currency, fx_swaps.far_buy_currency, fx_swaps.far_sell_currency]) AS currency,
            unnest(ARRAY[fx_swaps.near_buy_amount, - fx_swaps.near_sell_amount, fx_swaps.far_buy_amount, - fx_swaps.far_sell_amount]) AS amount
           FROM fx_swaps) 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, trades.account
  ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency;



CREATE TABLE 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 accounts(code)
);

CREATE TABLE 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 accounts(code)
);

CREATE TABLE bond_tickets (
	bbg_ticket_id text NOT NULL,
	"Match" text NULL,
	"Side" text NULL,
	"Security" text NULL,
	"Quantity" int8 NULL,
	"Price (Dec)" float8 NULL,
	"CP" text NULL,
	"STP Status" text NULL,
	"Trade Dt" date NULL,
	"SetDt" date NULL,
	"Curncy" text NULL,
	"Principal" float8 NULL,
	"Net" float8 NULL,
	"Acc Int" float8 NULL,
	"Block Status" text NULL,
	"Brkr" text NULL,
	"BrkrName" text NULL,
	"BrkrName.1" text NULL,
	"FIGI" text NULL,
	"Cusip" text NULL,
	"Curr Face" text NULL,
  "Mat Dt" date NULL,
  "C/Clear USI/UTI" text null,
  "Client FCM" text null,
	CONSTRAINT bond_tickets_pk PRIMARY KEY (bbg_ticket_id)
);
CREATE INDEX ix_bond_tickets_index ON bond_tickets USING btree (bbg_ticket_id);

CREATE TABLE cds_tickets (
	bbg_ticket_id text NOT NULL,
	"Match" text NULL,
	"Side" text NULL,
	"Security" text NULL,
	"Quantity" int8 NULL,
	"Price (Dec)" float8 NULL,
	"CP" text NULL,
	"STP Status" text NULL,
	"Trade Dt" text NULL,
	"SetDt" text NULL,
	"Curncy" text NULL,
	"Principal" float8 NULL,
	"Net" float8 NULL,
	"Acc Int" float8 NULL,
	"Account" text NOT NULL,
	"Client FCM" text NOT NULL,
	"Block Status" text NULL,
	"Red Code" text NULL,
	"Cusip" text NULL,
	"Brkr" text NULL,
	"Mat Dt" text NULL,
	"Coupon" int8 NULL,
	CONSTRAINT cds_tickets_pk PRIMARY KEY (bbg_ticket_id, "Account", "Client FCM")
);

CREATE TABLE cds_submission (
	id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
	tradeid int4 NOT NULL,
	"action" action NOT NULL,
	submit_date timestamptz NOT NULL DEFAULT now(),
	"status" status NOT NULL DEFAULT 'Pending'::status,
	CONSTRAINT cds_submission_pkey PRIMARY KEY (id),
	CONSTRAINT cds_submission_allocation_id_fkey FOREIGN KEY (tradeid) REFERENCES cds(id) ON DELETE CASCADE
);


CREATE TABLE mtm_submissions (
	ticketid text NOT NULL,
	executed date NULL,
	dealid text NOT NULL,
	dealtype text NULL,
	cp_code text NULL,
	swaptype text NULL,
	cpty_ref text NULL,
	CONSTRAINT mtm_submissions_pk PRIMARY KEY (ticketid, dealid)
);

CREATE TABLE quantifi_bond_proxy (
	product_name text NOT NULL,
	trade_date date NOT NULL,
	"asset_class" text NOT NULL,
	"fund" "fund" NOT NULL,
	notional float8 NULL,
	terminated bool NULL DEFAULT false,
	CONSTRAINT quantifi_bond_proxy_pk PRIMARY KEY (product_name, trade_date, asset_class, fund)
);


CREATE OR REPLACE FUNCTION list_compressed_quantifi(start_from date)
 RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id varchar(12), notional double precision, fcm text)
 LANGUAGE plpgsql
AS $function$
begin
	return query
	SELECT LEFT(split_part(security_desc, ' ', 2),2) AS "index", imm.tradeid, a.fund, pfm.clean_folder, pfm.portfolio, a.security_id, a.notional, a.fcm
FROM (
SELECT cds.fund, account_code AS fcm, cds.security_id, max(security_desc) AS security_desc, maturity, folder,
SUM(cds.notional * (CASE WHEN protection='Buyer' THEN 1 ELSE -1 END)) AS notional FROM cds
WHERE swap_type='CD_INDEX' AND trade_date<=start_from AND maturity > start_from
GROUP BY cds.security_id, maturity, cds.folder, cds.fund, account_code) a
LEFT JOIN portfolio_folder_mapping pfm ON replace(a.folder::text, 'SER_', '')=pfm.clean_folder
LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active AND abs(a.notional) >1;
end;
$function$
;

CREATE TABLE fx_tickets (
	bbg_ticket_id text NOT NULL,
	"Message Type" text NULL,
	"Deal Type" text NULL,
	"Side" text NULL,
	"Product" text NULL,
	"Trans Type" text NULL,
	"Revision Version" text NULL,
	"Trade ID" text NULL,
	"Batch ID" text NULL,
	"Trader Deal Code" text NULL,
	"Trader UUID" text NULL,
	"Trader Name" text NULL,
	"Counterparty UUID" text NULL,
	"Counterparty Trader Name" text NULL,
	"Date Of Deal" text NULL,
	"Time Of Deal" text NULL,
	"Trade Date" text NULL,
	"Date Confirmed" text NULL,
	"Time Confirmed" text NULL,
	"Counterparty Deal Code" text NULL,
	"Counterparty Name" text NULL,
	"User Identifier 1" text NULL,
	"User Identifier 2" text NULL,
	"User Identifier 3" text NULL,
	"User Identifier 4" text NULL,
	"Currency 1" text NULL,
	"Currency 2" text NULL,
	"Amount Dealt" text NULL,
	"Dealt Currency" text NULL,
	"Counter Amount" text NULL,
	"Counter Currency" text NULL,
	"Forward Points Near" text NULL,
	"Far Amount Dealt" text NULL,
	"Far Currency Dealt" text NULL,
	"Far Counter Amount" text NULL,
	"Far Counter Currency" text NULL,
	"Forward Points Far" text NULL,
	"Spot Basis Rate" text NULL,
	"Deposit Rate" text NULL,
	"Day Count Type" text NULL,
	"New or Roll" text NULL,
	"Volume Of Interest" text NULL,
	"Exchange Rate Period 1" text NULL,
	"Value Date Period 1 Currency 1" text NULL,
	"Tenor Period 1" text NULL,
	"Fixing Date Period 1" text NULL,
	"Fixing Source Period 1" text NULL,
	"Settle Currency" text NULL,
	"Swap Rate" text NULL,
	"Exchange Rate Period 2" text NULL,
	"Value Date Period 2 Currency 1" text NULL,
	"Tenor Period 2" text NULL,
	"Fixing Date Period 2" text NULL,
	"Fixing Source Period 2" text NULL,
	"Split Tenor Currency 1" text NULL,
	"Split Value Date Currency 1" text NULL,
	"Split Tenor Currency 2" text NULL,
	"Split Value Date Currency 2" text NULL,
	"Comment Text" text NULL,
	"Note Name 1" text NULL,
	"Note Text 1" text NULL,
	"Note Name 2" text NULL,
	"Note Text 2" text NULL,
	"Note Name 3" text NULL,
	"Note Text 3" text NULL,
	"Note Name 4" text NULL,
	"Note Text 4" text NULL,
	"Note Name 5" text NULL,
	"Note Text 5" text NULL,
	"Comp Quote 1" text NULL,
	"Comp Deal Code 1" text NULL,
	"Supplementary Cost 1" text NULL,
	"Comp Quote 2" text NULL,
	"Comp Deal Code 2" text NULL,
	"Supplementary Cost 2" text NULL,
	"Comp Quote 3" text NULL,
	"Comp Deal Code 3" text NULL,
	"Supplementary Cost 3" text NULL,
	"Comp Quote 4" text NULL,
	"Comp Deal Code 4" text NULL,
	"Supplementary Cost 4" text NULL,
	"Comp Quote 5" text NULL,
	"Comp Deal Code 5" text NULL,
	"Supplementary Cost 5" text NULL,
	"Buyer Near Trader UUID" text NULL,
	"Buyer Near Deal Code" text NULL,
	"Seller Near Trader UUID" text NULL,
	"Seller Near Deal Code" text NULL,
	"Buyer Far Trader UUID" text NULL,
	"Buyer Far Deal Code" text NULL,
	"Seller Far Trader UUID" text NULL,
	"Seller Far Deal Code" text NULL,
	"Broker Trader UUID" text NULL,
	"Broker Deal Code" text NULL,
	"Broker Name" text NULL,
	"Portfolio" text NULL,
	"ALOC Account 1" text NULL,
	"ALOC Amount 1" text NULL,
	"ALOC Direction 1" text NULL,
	"ALOC Custodian 1" text NULL,
	"ALOC Prime Broker 1" text NULL,
	"ALOC Account 2" text NULL,
	"ALOC Amount 2" text NULL,
	"ALOC Direction 2" text NULL,
	"ALOC Custodian 2" text NULL,
	"ALOC Prime Broker 2" text NULL,
	"ALOC Account 3" text NULL,
	"ALOC Amount 3" text NULL,
	"ALOC Direction 3" text NULL,
	"ALOC Custodian 3" text NULL,
	"ALOC Prime Broker 3" text NULL,
	"ALOC Account 4" text NULL,
	"ALOC Amount 4" text NULL,
	"ALOC Direction 4" text NULL,
	"ALOC Custodian 4" text NULL,
	"ALOC Prime Broker 4" text NULL,
	"ALOC Account 5" text NULL,
	"ALOC Amount 5" text NULL,
	"ALOC Direction 5" text NULL,
	"ALOC Custodian 5" text NULL,
	"ALOC Prime Broker 5" text NULL,
	"ALOC Account 6" text NULL,
	"ALOC Amount 6" text NULL,
	"ALOC Direction 6" text NULL,
	"ALOC Custodian 6" text NULL,
	"ALOC Prime Broker 6" text NULL,
	"ALOC Account 7" text NULL,
	"ALOC Amount 7" text NULL,
	"ALOC Direction 7" text NULL,
	"ALOC Custodian 7" text NULL,
	"ALOC Prime Broker 7" text NULL,
	"ALOC Account 8" text NULL,
	"ALOC Amount 8" text NULL,
	"ALOC Direction 8" text NULL,
	"ALOC Custodian 8" text NULL,
	"ALOC Prime Broker 8" text NULL,
	"ALOC Account 9" text NULL,
	"ALOC Amount 9" text NULL,
	"ALOC Direction 9" text NULL,
	"ALOC Custodian 9" text NULL,
	"ALOC Prime Broker 9" text NULL,
	"ALOC Account 10" text NULL,
	"ALOC Amount 10" text NULL,
	"ALOC Direction 10" text NULL,
	"ALOC Custodian 10" text NULL,
	"ALOC Prime Broker 10" text NULL,
	"Reference Spot Rate" text NULL,
	"Reference Rate Period 1" text NULL,
	"Reference Rate Period 2" text NULL,
	"Pay Currency" text NULL,
	"Pay SWIFT Code" text NULL,
	"Pay Account Number" text NULL,
	"Pay Bank" text NULL,
	"Pay Branch" text NULL,
	"Pay Beneficiary" text NULL,
	"Pay Special Instructions" text NULL,
	"Receiving Currency" text NULL,
	"Receiving SWIFT Code" text NULL,
	"Receiving Account Number" text NULL,
	"Receiving Bank" text NULL,
	"Receiving Branch" text NULL,
	"Receiving Beneficiary" text NULL,
	"Receiving Special Instructions" text NULL,
	"Spot Rate Mid" text NULL,
	"All-in Rate Near Leg Mid" text NULL,
	"Near Leg Forward Point Mid" text NULL,
	"All-in Rate Far Leg Mid" text NULL,
	"Far Leg Forward Point Mid" text NULL,
	"USI Namespace" text NULL,
	"USI ID" text NULL,
	"USI ID Near" text NULL,
	"USI ID Swap" text NULL,
	"Delivery Date" text NULL,
	"Banknote Rate Type" text NULL,
	"Commission" text NULL,
	"Order Type Name" text NULL,
	"Order Strategy Name" text NULL,
	"Execution Venue MIC" text NULL,
	"Market Segment ID" text NULL,
	"Group ID" text NULL,
	"Group Type" text NULL,
	"Trade Method" text NULL,
	"Execution Method" text NULL,
	"Sub Type" text NULL,
	"Supplementary Sub Type" text NULL,
	"External Reference ID" text NULL,
	"Execution Venue LEI" text NULL,
	"ISIN Near" text NULL,
	"ISIN Far" text NULL,
	"ISIN Swap" text NULL,
	"Execution within Firm Algo ID" text NULL,
	"Execution within Firm GPI" text NULL,
	"Execution within Firm Identifier" text NULL,
	"Investment Decision within Firm Algo ID" text NULL,
	"Investment Decision within Firm GPI" text NULL,
	"Investment Decision within Firm Short Code" text NULL,
	"Trading Capacity" text NULL,
	"Package ID" text NULL,
	"Pre-Trade Transparency Type" text NULL,
	"Pre-Trade Transparency Reason" text NULL,
	"Post-Trade Transparency Type" text NULL,
	"Post-Trade Transparency Reason" text NULL,
	"Commodities Derivative Indicator" text NULL,
	"Securities Financing Transaction" text NULL,
	"Maker Algo ID" text NULL,
	"Ord Reg Timestamp" text NULL,
	"Ord Reg Timestamp Type" text NULL,
	"Trd Reg Timestamp" text NULL,
	"Trd Reg Timestamp Type" text NULL,
	"Trade Reporting Indicator" text NULL,
	"Country of Membership" text NULL,
	CONSTRAINT fx_tickets_pk PRIMARY KEY (bbg_ticket_id)
);


CREATE TABLE irs (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dealid text NULL GENERATED ALWAYS AS ('IRS_'::text || id::text) STORED,
  "fund" "fund" NOT NULL DEFAULT 'SERCGMAST'::fund,
  "portfolio" "portfolio" NOT NULL,
  folder "strategy" NOT NULL,
  cash_account text NOT NULL REFERENCES accounts2(cash_account) ON UPDATE CASCADE,
  cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
  trade_date date NOT NULL,
	effective_date date NOT NULL,
	maturity_date date NOT NULL,
	payreceive bool NOT NULL,
	fixed_rate float8 NOT NULL,
	fixed_daycount day_count NOT NULL,
	fixed_payment_freq frequency NOT NULL,
  fixed_bdc bus_day_convention NOT NULL,
	notional float8 NOT NULL,
  float_index cash_rate NOT NULL,
	float_daycount day_count NOT NULL,
	float_payment_freq frequency NOT NULL,
  float_bdc bus_day_convention NOT NULL,
  float_arrears bool NOT NULL,
	"currency" "currency" NOT NULL,
	float_fixing_freq frequency NOT NULL,
	pay_interest_calc_method interest_calc_method NOT NULL,
	clearing_facility clearing_cp NOT NULL DEFAULT 'LCH-LTD'::clearing_cp,
	swap_type swap_type NOT NULL,
	cleared_trade_id text NULL,
  action action not null,
  bbg_ticket_id text null references bond_tickets(bbg_ticket_id) ON UPDATE CASCADE
);

CREATE TYPE "bbg_code_type" AS ENUM (
	'BOND',
	'CDS',
	'FX',
  'IRS');

CREATE TYPE "interest_calc_method" AS ENUM (
	'Compound',
	'Simple');

CREATE TABLE trs (
    id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dealid text NULL GENERATED ALWAYS AS ('TRS'::text || id::text) STORED,
    "fund" "fund" NOT NULL DEFAULT 'SERCGMAST'::fund,
    "portfolio" "portfolio" NOT NULL,
    folder "cds_strat" NOT NULL,
    cash_account text NOT NULL REFERENCES accounts2(cash_account) ON UPDATE CASCADE,
    cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
    trade_date date NOT NULL,
    effective_date date NOT NULL,
    maturity_date date NOT NULL,
    settle_date date NOT NULL,
    funding_index cash_rate NOT NULL DEFAULT 'SOFRRATE'::cash_rate,
    buysell bool NOT NULL,
    underlying_security varchar(32) NOT NULL,
    price float8 NOT NULL,
    accrued float8 NOT NULL,
    funding_freq frequency NOT NULL,
    funding_daycount day_count NOT NULL,
    funding_payment_roll_convention bus_day_convention NOT NULL,
    funding_arrears bool NOT NULL,
    asset_freq frequency NOT NULL,
    asset_daycount day_count NOT NULL,
    asset_payment_roll_convention bus_day_convention NOT NULL,
    initial_margin_percentage float8 NOT NULL,
    notional float8 NOT NULL,
    "currency" "currency" NOT NULL DEFAULT 'USD'::currency,
    interest_calc_method interest_calc_method NOT NULL,
    compound_avg_frequency frequency NOT NULL,
    fixing_frequency frequency NOT NULL,
    cpty_id text NULL,
    action action not null,
    globeop_id int null,
);

CREATE TYPE "instrument_type" AS ENUM (
	'IRS',
	'CDS',
  'BNDO',
  'SWPO',
  'TRS');


CREATE TYPE "id_source" AS ENUM (
	'RED', 'USERID');

CREATE TYPE "serenitas_product" AS ENUM(
  'tranche', 'cdx_swaption', 'ir_swaption', 'irs', 'trs'
)

CREATE TABLE citco_tranche (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	dealid text NULL GENERATED ALWAYS AS ('CDS_'::text || id::text) STORED,
	instrument_type instrument_type NOT NULL DEFAULT 'CDS'::instrument_type,
	underlying_id_source id_source NOT NULL DEFAULT 'RED'::id_source,
	underlying_security_id text NOT NULL,
	birth_date date NULL,
	death_date date NULL,
	active bool NULL DEFAULT true,
	"attach" int8 NOT NULL,
	"detach" int8 NOT NULL,
	"committed" bool NOT NULL DEFAULT false,
	CONSTRAINT citco_tranche_pkey PRIMARY KEY (id),
	CONSTRAINT citco_tranche_un UNIQUE (underlying_security_id, attach, detach)
);

CREATE FUNCTION instrument_cast(inst instrument_type) RETURNS text
LANGUAGE SQL
IMMUTABLE
RETURN instrument_type::text;

CREATE TABLE citco_swaption (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	dealid varchar(28) NULL GENERATED ALWAYS AS (instrument_cast(instrument_type) || '_' || id::text) STORED,
	instrument_type instrument_type NOT NULL,
	underlying_id_source id_source NOT NULL,
	underlying_security_id text NOT NULL,
	birth_date date NOT NULL,
	death_date date NOT NULL,
	active bool NOT NULL DEFAULT true,
	strike float8 NOT NULL,
	expiration date NOT NULL,
	callput bool NULL,
	committed bool NOT NULL DEFAULT false,
	UNIQUE (instrument_type, underlying_security_id, strike, expiration, callput)
);

CREATE TABLE citco_irs (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	dealid text NULL GENERATED ALWAYS AS ('IRS'::text || id::text) STORED,
  instrument_type  "instrument_type" NOT NULL default 'IRS',
	birth_date date NOT NULL,
  death_date date NOT NULL,
  active bool NOT NULL default True,
	payreceive bool NOT NULL,
	fixed_rate float8 NOT NULL,
	fixed_daycount "day_count" NOT NULL,
	fixed_payment_freq "frequency" NOT NULL,
	fixed_bdc "bus_day_convention" NOT NULL,
	notional float8 NOT NULL,
	float_index "cash_rate" NOT NULL,
	float_daycount "day_count" NOT NULL,
	float_payment_freq "frequency" NOT NULL,
	float_bdc "bus_day_convention" NOT NULL,
	float_arrears bool NOT NULL,
	"currency" "currency" NOT NULL,
	float_fixing_freq "frequency" NOT NULL,
	pay_interest_calc_method "interest_calc_method" NOT NULL,
  committed bool NOT NULL default False,
);


CREATE TABLE citco_trs (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	dealid text NULL GENERATED ALWAYS AS ('TRS'::text || id::text) STORED,
  instrument_type  "instrument_type" NOT NULL default 'TRS',
	birth_date date NOT NULL,
  death_date date NOT NULL,
  active bool NOT NULL default True,
	funding_index "cash_rate" NOT NULL DEFAULT 'SOFRRATE'::cash_rate,
	buysell bool NOT NULL,
	underlying_security varchar(32) NOT NULL,
	price numeric(9, 5) NOT NULL,
	accrued float8 NOT NULL,
	funding_freq "frequency" NOT NULL,
	funding_daycount "day_count" NULL,
	funding_payment_roll_convention "bus_day_convention" NOT NULL,
	funding_arrears bool NOT NULL,
	asset_freq "frequency" NOT NULL,
	asset_daycount "day_count" NULL,
	asset_payment_roll_convention "bus_day_convention" NOT NULL,
	initial_margin_percentage float8 NOT NULL,
	notional float8 NOT NULL,
	"currency" "currency" NOT NULL DEFAULT 'USD'::currency,
	"interest_calc_method" "interest_calc_method" NOT NULL,
	compound_avg_frequency "frequency" NOT NULL,
	fixing_frequency "frequency" NOT NULL,
  committed bool NOT NULL default False
);

CREATE TYPE "citco_identifier" AS ENUM (
	'trade', 'instrument', 'failed');


CREATE TABLE citco_submission(
       fname text not null,
       identifier_type citco_identifier not null,
       identifier text not null,
       serenitas_id text not null,
       submit_date timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE citco_submission2(
       id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       identifier_type citco_identifier not null,
       citco_id text not null,
       serenitas_id text not null,
       submit_date timestamptz,
       process_date timestamptz,
       UNIQUE (identifier_type, submit_date, process_date, citco_id)
);

CREATE TABLE citco_submission_status(
       serenitas_id text not null,
       action action not null,
       identifier_type citco_identifier not null,
       identifier text null,
       committed bool not null default False,
       submit_date timestamptz NOT NULL DEFAULT now()
);


CREATE table isda_agreement_dates(
  fund fund not null,
  counterparty text REFERENCES counterparties(code),
  agreement_date date not null,
  CONSTRAINT isda_agreement_dates_key UNIQUE (fund, counterparty, agreement_date),
)

CREATE OR REPLACE VIEW tranche_risk_isosel
AS SELECT tranche_risk.date,
    tranche_risk.tranche_id AS trade_id,
    a.admin_id,
    cds.security_desc,
    index_version.index,
    index_version.series,
    cds.maturity,
    cds.orig_attach,
    cds.orig_detach,
    tranche_risk.notional,
    a.admin_notional,
    tranche_risk.clean_nav AS serenitas_clean_nav,
    a.admin_clean_nav,
    tranche_risk.accrued AS serenitas_accrued,
    a.admin_accrued,
    external_marks_deriv.base_nav AS cpty_nav,
    tranche_risk.duration,
    tranche_risk.delta,
    tranche_risk.gamma,
    tranche_risk.theta,
    tranche_risk.theta_amount,
    tranche_risk.tranche_factor,
    tranche_risk.corr_attach,
    tranche_risk.corr_detach,
    tranche_risk.upfront,
    tranche_risk.running,
    tranche_risk.index_refprice,
    tranche_risk.index_refspread,
    tranche_risk.index_duration,
    tranche_risk.hy_equiv,
    cds.initial_margin_percentage
   FROM tranche_risk
     LEFT JOIN cds ON tranche_risk.tranche_id = cds.id
     FULL JOIN ( SELECT
        COALESCE(init_trader_notes, init_fix_id) AS fix_id,
        init_order_id AS admin_id,
            period_end_date,
            sum(total_acc_int) AS admin_accrued,
            sum(end_mkt_value) AS admin_clean_nav,
            sum(-position) AS admin_notional
           FROM isosel_accrued WHERE liqd_date IS NULL
          GROUP BY COALESCE(init_trader_notes, init_fix_id), init_order_id , period_end_date) a ON a.fix_id= concat('SCCDS', tranche_risk.tranche_id) AND a.period_end_date = tranche_risk.date
     LEFT JOIN index_version ON cds.security_id::text = index_version.redindexcode
     LEFT JOIN external_marks_deriv ON cds.cpty_id = external_marks_deriv.identifier AND external_marks_deriv.date = tranche_risk.date
  WHERE cds.fund = 'ISOSEL'::fund
  ORDER BY index_version.index, index_version.series, cds.orig_attach;


  CREATE TABLE isosel_accrued (
	"status" text NULL,
	tid text NULL,
	tid_fwd_date date NULL,
	fund_abbrev text NULL,
	trader_name text NULL,
	clear_agent text NULL,
	"strategy" text NULL,
	init_exec_broker text NULL,
	liqd_exec_broker text NULL,
	instr_class text NULL,
	instr_type text NULL,
	instrument_description text NULL,
	"l/s" text NULL,
	"position" float8 NULL,
	init_order_id int8 NULL,
	liqd_order_id float8 NULL,
	matching_id int8 NULL,
	init_fix_id text NULL,
	liqd_fix_id text NULL,
	init_date date NULL,
	init_settle_date date NULL,
	liqd_date date NULL,
	liqd_settle_date date NULL,
	init_price float8 NULL,
	liqd_price float8 NULL,
	init_fx float8 NULL,
	liqd_fx float8 NULL,
	"cost_[n]" float8 NULL,
	"avg_cost_[n]" float8 NULL,
	"off-balance_cost_[n]" float8 NULL,
	"off-balance_net_cost_[n]" float8 NULL,
	"cost" float8 NULL,
	avg_cost float8 NULL,
	"off-balance_cost" float8 NULL,
	"off-balance_net_cost" float8 NULL,
	"off-balance_init_net_cost" float8 NULL,
	init_net_cost float8 NULL,
	net_cost float8 NULL,
	"proceeds_[n]" float8 NULL,
	proceeds float8 NULL,
	net_proceeds float8 NULL,
	"init_comms_[n]" float8 NULL,
	"init_mfees_[n]" float8 NULL,
	"init_mtax_[n]" float8 NULL,
	"init_mexp_[n]" float8 NULL,
	"liqd_comms_[n]" float8 NULL,
	"liqd_mfees_[n]" float8 NULL,
	"liqd_mtax_[n]" float8 NULL,
	"liqd_mexp_[n]" float8 NULL,
	init_comms float8 NULL,
	init_mfees float8 NULL,
	init_mtax float8 NULL,
	init_mexp float8 NULL,
	liqd_comms float8 NULL,
	liqd_mfees float8 NULL,
	liqd_mtax float8 NULL,
	liqd_mexp float8 NULL,
	sec_fees float8 NULL,
	start_qty float8 NULL,
	end_qty float8 NULL,
	chg_qty float8 NULL,
	"start_mkt_value_[n]" float8 NULL,
	"start_mkt_value_realized_[n]" float8 NULL,
	"end_mkt_value_[n]" float8 NULL,
	"start_unrealized_[n]" float8 NULL,
	"end_unrealized_[n]" float8 NULL,
	"chg_unreal_[n]" float8 NULL,
	"realized_[n]" float8 NULL,
	"net_realized_[n]" float8 NULL,
	start_mkt_value float8 NULL,
	start_mkt_value_realized float8 NULL,
	end_mkt_value float8 NULL,
	start_unrealized float8 NULL,
	end_unrealized float8 NULL,
	chg_unreal float8 NULL,
	realized float8 NULL,
	net_realized float8 NULL,
	unrealized_fx float8 NULL,
	unrealized_fx_net float8 NULL,
	chg_unreal_fx float8 NULL,
	chg_unreal_fx_net float8 NULL,
	realized_fx float8 NULL,
	realized_fx_net float8 NULL,
	"dividends_[n]" float8 NULL,
	dividends float8 NULL,
	"acq_int_[n]" float8 NULL,
	"start_accrual_[n]" float8 NULL,
	"bond_accrual_[n]" float8 NULL,
	"interest_[n]" float8 NULL,
	"total_int_[n]" float8 NULL,
	acq_int float8 NULL,
	start_accrual float8 NULL,
	bond_accrual float8 NULL,
	interest float8 NULL,
	total_int float8 NULL,
	"p&l" float8 NULL,
	"p&l_[n]" float8 NULL,
	"p&l_total" float8 NULL,
	start_mkt_px float8 NULL,
	end_mkt_px float8 NULL,
	chg_mkt_px float8 NULL,
	instr_ccy text NULL,
	trade_ccy text NULL,
	fund_ccy text NULL,
	price_factor float8 NULL,
	notes text NULL,
	tax_status text NULL,
	days_held float8 NULL,
	account_988 float8 NULL,
	cost_basis text NULL,
	bond_coupon float8 NULL,
	source_ccy text NULL,
	"end_mkt_value_[source_ccy]" float8 NULL,
	fund_ending_notional float8 NULL,
	symbol text NULL,
	exchange text NULL,
	"country" text NULL,
	ticker_cusip text NULL,
	ticker_isin text NULL,
	ticker_sedol text NULL,
	ticker_userid text NULL,
	ticker_u_isin float8 NULL,
	ticker_u_sedol float8 NULL,
	industry float8 NULL,
	u_tid int8 NULL,
	u_name text NULL,
	"posted_init_fees_[n]" float8 NULL,
	posted_init_fees float8 NULL,
	"avg_netpx_[n]" float8 NULL,
	"avg_netliqdpx_[n]" float8 NULL,
	avg_netpx float8 NULL,
	fas_157 text NULL,
	fas_157_moved float8 NULL,
	orig_date date NULL,
	start_date date NULL,
	end_date date NULL,
	lot_commissions float8 NULL,
	lot_sec_fees float8 NULL,
	"fwd_unwind_[n]" float8 NULL,
	fwd_unwind float8 NULL,
	instr_country text NULL,
	"lot_cost_[n]" float8 NULL,
	lot_cost float8 NULL,
	"lot_net_cost_[n]" float8 NULL,
	lot_net_cost float8 NULL,
	"net_proceeds_[n]" float8 NULL,
	zz_int float8 NULL,
	fas157_major_type float8 NULL,
	fas161_trade_hedge float8 NULL,
	fas161_risk float8 NULL,
	lot_init_comms float8 NULL,
	lot_liqd_comms float8 NULL,
	ntv_acq_relief float8 NULL,
	base_acq_relief float8 NULL,
	ntv_end_accrual float8 NULL,
	base_end_accrual float8 NULL,
	last_cpn_date date NULL,
	liqd_orig_date text NULL,
	days_betw_orig_dates float8 NULL,
	"long/short_adjustment" float8 NULL,
	"$p&l_fx" float8 NULL,
	"opt_put/call" text NULL,
	beta float8 NULL,
	option_type_name text NULL,
	instrument_subtype text NULL,
	std_stratery float8 NULL,
	u_mkt_price float8 NULL,
	delta float8 NULL,
	option_strike float8 NULL,
	"total_acc_int_[n]" float8 NULL,
	total_acc_int float8 NULL,
	end_princ_factor float8 NULL,
	end_face_value float8 NULL,
	"acq_int_(fx)" float8 NULL,
	init_ticket_bs text NULL,2
	start_tax_status text NULL,
	active_bond_int float8 NULL,
	end_last_progname text NULL,
	init_corpact_id float8 NULL,
	liqd_corpact_id float8 NULL,
	init_client_order_id text NULL,
	liqd_client_order_id text NULL,
	deal_id float8 NULL,
	apost_tax_status text NULL,
	start_apost_tax_status text NULL,
	ticker_bbg text NULL,
	"order-level_attr_name" float8 NULL,
	init_order_attr_value float8 NULL,
	liqd_order_attr_value float8 NULL,
	ticker_pb_code_1 float8 NULL,
	ticker_pb_code_2 float8 NULL,
	init_trader_notes text NULL,
	liqd_trader_notes text NULL,
	u_symbol text NULL,
	ticker_u_cusip float8 NULL,
	bond_class text NULL,
	bond_maturity text NULL,
	trader_group float8 NULL,
	subfund text NULL,
	"row" int8 NOT NULL,
	period_end_date date NOT NULL,
	knowledge_date timestamp NULL,
  ticker_ocs_code text null,
	CONSTRAINT isosel_accrued_pk PRIMARY KEY (period_end_date, "row")
);


CREATE TABLE custodian_wires(
       date date NOT NULL,
       fund fund NOT NULL,
       entry_date date,
       value_date date,
       pay_date date,
       currency currency,
       amount float8 NOT NULL,
       wire_details text,
       unique_ref text PRIMARY KEY);



CREATE OR REPLACE VIEW trs_trades AS
SELECT trs.id,
       dealid,
       termination_date AS trade_date,
       termination_amount AS notional,
       underlying_desc,
       underlying_security,
       CASE WHEN termination_cp=cp_code THEN
         'Termination'
       ELSE
         'Assignment'
       END AS trade_type,
       folder,
       fund,
       termination_cp AS cp_code,
       name,
       funding_index,
       CASE WHEN buysell THEN
         False
       ELSE
         True
       END AS buysell,
       termination_fee AS upfront,
       terminations.traded_level as price,
       maturity_date,
       fee_payment_date as settle_date
FROM terminations RIGHT JOIN trs USING (dealid)
LEFT JOIN counterparties on termination_cp=code
WHERE termination_date is NOT NULL
UNION ALL (
SELECT id,
       dealid,
       trade_date,
       notional,
       underlying_desc,
       underlying_security,
       'New',
       folder,
       fund,
       cp_code,
       name,
       funding_index,
       buysell,
       accrued as upfront,
       price,
       maturity_date,
       settle_date
FROM trs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder;


CREATE TRIGGER cash_account BEFORE
INSERT OR UPDATE OF
   fund,
   cp_code
ON
    trs
FOR EACH ROW
EXECUTE PROCEDURE update_account('ISDA');

CREATE TYPE author AS ENUM ('auto', 'manual');

CREATE OR REPLACE VIEW forward_trades
AS SELECT a.*, c.name from(SELECT unnest(ARRAY[fx_swaps.dealid::text, fx_swaps.dealid::text]) AS dealid,
    fx_swaps.trade_date,
    unnest(ARRAY[fx_swaps.near_settle_date, fx_swaps.far_settle_date]) AS settle_date,
    fx_swaps.fund,
    fx_swaps.portfolio,
    fx_swaps.folder,
    fx_swaps.cp_code,
    unnest(ARRAY[fx_swaps.near_buy_currency, fx_swaps.far_buy_currency]) AS buy_currency,
    unnest(ARRAY[fx_swaps.near_sell_currency, fx_swaps.far_sell_currency]) AS sell_currency,
    unnest(ARRAY[fx_swaps.near_buy_amount, fx_swaps.far_buy_amount]) AS buy_amount,
    unnest(ARRAY[fx_swaps.near_sell_amount, fx_swaps.far_sell_amount]) AS sell_amount,
    unnest(ARRAY[fx_swaps.near_cpty_id, fx_swaps.far_cpty_id]) AS cpty_id,
    unnest(ARRAY[fx_swaps.near_rate, fx_swaps.far_rate]) AS spot_rate,
    fx_swaps.id as id
   FROM fx_swaps
UNION
 SELECT spots.dealid,
    spots.trade_date,
    spots.settle_date,
    spots.fund,
    spots.portfolio,
    spots.folder,
    spots.cp_code,
    spots.buy_currency,
    spots.sell_currency,
    spots.buy_amount,
    spots.sell_amount,
    spots.cpty_id,
    spots.spot_rate,
    spots.id as id
   FROM spots) a
   LEFT JOIN counterparties c ON cp_code=code ;

CREATE OR REPLACE function list_orphaned_cash(p_date date, p_fund fund)
RETURNS TABLE(fund fund, trade_date date,amount float, portfolio portfolio, cash_account text, currency currency, folder TEXT, custodian text, cp_code varchar(10)) AS $$
BEGIN
  RETURN QUERY
  SELECT a.*, coalesce(pfm.folder::text, a.strat::text), accounts2.custodian, accounts2.cp_code FROM (
  SELECT vr.fund, vr.periodenddate, -sum(vr.endqty) AS amount, vr.port, vr.custacctname AS cash_account, vr.invid::currency AS currency
  FROM valuation_reports vr
  GROUP BY (vr.periodenddate, vr.fund, vr.port, vr.strat, vr.custacctname, vr.invid)
  HAVING periodenddate=p_date AND vr.fund=p_fund AND vr.invid in ('USD', 'EUR') and port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') AND sum(vr.endqty) !=0
  ) a
  LEFT JOIN portfolio_folder_mapping pfm ON a.strat::text = pfm.clean_folder::text
  LEFT JOIN accounts2 USING (cash_account)
  WHERE pfm.active;
END
$$ LANGUAGE plpgsql;


CREATE TABLE cash_balances (
	"date" date NOT NULL,
	"fund" "fund" NULL,
	account_name varchar NULL,
	account_number varchar NOT NULL,
	currency_code "currency" NOT NULL,
	balance float8 NULL,
	CONSTRAINT cash_balances_pkey PRIMARY KEY (date, account_number, currency_code)
);