CREATE TABLE bespokes (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	bookingentity_name text NULL,
	productdescription text NULL,
	maturity text NULL,
	trancheattachment float8 NULL,
	tranchedetachment float8 NULL,
	pv float8 NULL,
	riskyduration float8 NULL,
	apcorrelation float8 NULL,
	dpcorrelation float8 NULL,
	breakevenpremium float8 NULL,
	basecorrelationleveldelta float8 NULL,
	CONSTRAINT qf_bespokes_pk PRIMARY KEY (date, "row")
);

CREATE TABLE bondbetas (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	et_duration float8 NULL,
	et_beta1 float8 NULL,
	et_beta2 float8 NULL,
	CONSTRAINT qf_bondbetas_pk PRIMARY KEY (date, "row")
);

CREATE TABLE bonds (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	bookingentity_name text NULL,
	cusip text NULL,
	"strategy" text NULL,
	quantity int8 NULL,
	effectivenotional float8 NULL,
	bondcoupon float8 NULL,
	producttype text NULL,
	pv float8 NULL,
	price float8 NULL,
	accrued float8 NULL,
	duration float8 NULL,
	beta1 float8 NULL,
	beta2 float8 NULL,
	CONSTRAINT qf_bonds_pk PRIMARY KEY (date, "row")
);

CREATE TABLE cdx (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	productdescription text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	creditindexred text NULL,
	subfamily text NULL,
	currentnotional float8 NULL,
	riskyduration float8 NULL,
	maturity text NULL,
	beta float8 NULL,
	fxratequote float8 NULL,
	cs01 float8 NULL,
	cs10 float8 NULL,
	cs50 float8 NULL,
	CONSTRAINT qf_cdx_pk PRIMARY KEY (date, "row")
);

CREATE TABLE cdxoption (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	productdescription text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	currentnotional int8 NULL,
	cdxoptiontype text NULL,
	cdxoptionstrike float8 NULL,
	expiration text NULL,
	delta float8 NULL,
	cs01 float8 NULL,
	vega float8 NULL,
	pv float8 NULL,
	priceinbps float8 NULL,
	subfamily text NULL,
	tradetag_markit1 text NULL,
	beta float8 NULL,
	fxratequote int8 NULL,
	CONSTRAINT qf_cdxoption_pk PRIMARY KEY (date, "row")
);

CREATE TABLE cdxqf (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	productname text NULL,
	productdescription text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	creditindexred text NULL,
	subfamily text NULL,
	currentnotional float8 NULL,
	riskyduration float8 NULL,
	maturity text NULL,
	beta float8 NULL,
	fxratequote float8 NULL,
	cs01 float8 NULL,
	cs10 float8 NULL,
	cs50 float8 NULL,
	tradedate text NULL,
	substrategy text NULL,
	broker_longname text NULL,
	CONSTRAINT qf_cdxqf_pk PRIMARY KEY (date, "row")
);

CREATE TABLE cdxtranche (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	productdescription text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	currentnotional int8 NULL,
	effectivenotional float8 NULL,
	delta float8 NULL,
	cs01 float8 NULL,
	pvbaseccy float8 NULL,
	pvlocalccy float8 NULL,
	accrued float8 NULL,
	cleanprice float8 NULL,
	tradetag_markit1 text NULL,
	subfamily text NULL,
	fxratequote float8 NULL,
	beta float8 NULL,
	CONSTRAINT qf_cdxtranche_pk PRIMARY KEY (date, "row")
);

CREATE TABLE fxforwards (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	bookingentity_name text NULL,
	buycurrency text NULL,
	buyamount float8 NULL,
	sellcurrency text NULL,
	sellamount float8 NULL,
	pv int8 NULL,
	counterparty_longname text NULL,
	CONSTRAINT qf_fxforwards_pk PRIMARY KEY (date, "row")
);

CREATE TABLE hvardata (
	"date" date NOT NULL,
	"row" text NOT NULL,
	tradeid text NULL,
	shiftdate text NULL,
	bookingentity_longname text NULL,
	"trade simulated loss" text NULL,
	substrategy text NULL,
	"strategy" text NULL,
	CONSTRAINT qf_hvardata_pk PRIMARY KEY (date, "row")
);

CREATE TABLE hvardatacdxoptions (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	shiftdate text NULL,
	bookingentity_longname text NULL,
	tradesimulatedloss float8 NULL,
	substrategy text NULL,
	"strategy" text NULL,
	CONSTRAINT qf_hvardatacdxoptions_pk PRIMARY KEY (date, "row")
);

CREATE TABLE indicatives (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	productname text NULL,
	"strategy" text NULL,
	productdescription text NULL,
	quantity int8 NULL,
	et_beta1 float8 NULL,
	riskyduration float8 NULL,
	pv float8 NULL,
	impliedquotedspr float8 NULL,
	subfamily text NULL,
	CONSTRAINT qf_indicatives_pk PRIMARY KEY (date, "row")
);

CREATE TABLE jtd (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	referenceentity_longname text NULL,
	bookingentity_name text NULL,
	jtddelta float8 NULL,
	jtddelta_0 float8 NULL,
	jtddelta_100 float8 NULL,
	cdsspreadquote5y float8 NULL,
	CONSTRAINT qf_jtd_pk PRIMARY KEY (date, "row")
);

CREATE TABLE pnlexplainbytrade (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	substrategy text NULL,
	producttype text NULL,
	pnlusd float8 NULL,
	newbusiness float8 NULL,
	termination_effect int8 NULL,
	basecorrelationhigherorder float8 NULL,
	basecorrelationdelta float8 NULL,
	parspreadsdelta float8 NULL,
	parspreadsgamma float8 NULL,
	parspreadshigherorder float8 NULL,
	indexbasisdelta float8 NULL,
	indexbasishigherorder float8 NULL,
	creditcorrelationcrosseffect float8 NULL,
	creditindexvolcrosseffect float8 NULL,
	recoveryratedelta float8 NULL,
	recoveryhigherorder float8 NULL,
	creditdefaulteffect int8 NULL,
	volatilitydelta float8 NULL,
	volatilityhigherorder float8 NULL,
	accrualinterest float8 NULL,
	timeeffecttotal float8 NULL,
	irdelta float8 NULL,
	irgamma float8 NULL,
	irhigherorder float8 NULL,
	equityvega int8 NULL,
	equityspotdelta int8 NULL,
	equityspotgamma int8 NULL,
	equitymodeleffectinho float8 NULL,
	equityspothighorder int8 NULL,
	modelbasistotal float8 NULL,
	mtmfxdelta float8 NULL,
	mtmfxhighorder int8 NULL,
	cashfxeffect float8 NULL,
	nonactivetrade_cashrevaleffect float8 NULL,
	mtmpladjfxreval float8 NULL,
	unexplainedpl int8 NULL,
	totalexplainedpl float8 NULL,
	residualeffects float8 NULL,
	absresidual float8 NULL,
	basecorrelationhigherorder1 text NULL,
	CONSTRAINT qf_pnlexplainbytrade_pk PRIMARY KEY (date, "row")
);

CREATE TABLE proxies (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	bookingentity_name text NULL,
	productdescription text NULL,
	"strategy" text NULL,
	substrategy text NULL,
	quantity int8 NULL,
	pv float8 NULL,
	riskyduration float8 NULL,
	cs01 float8 NULL,
	CONSTRAINT qf_proxies_pk PRIMARY KEY (date, "row")
);

CREATE TABLE ratefutures (
	"date" date NOT NULL,
	"row" text NOT NULL,
	bookingentity_name text NULL,
	productdescription text NULL,
	maturity text NULL,
	quantity text NULL,
	futurescontracttype text NULL,
	price text NULL,
	pv text NULL,
	CONSTRAINT qf_ratefutures_pk PRIMARY KEY (date, "row")
);

CREATE TABLE sensitivities (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	bookingentity_name text NULL,
	"strategy" text NULL,
	substrategy text NULL,
	producttype text NULL,
	subfamily text NULL,
	cs01 float8 NULL,
	cs10 float8 NULL,
	cs20 float8 NULL,
	cs30 float8 NULL,
	cs40 float8 NULL,
	cs50 float8 NULL,
	cs60 float8 NULL,
	cs70 float8 NULL,
	cs80 float8 NULL,
	cs90 float8 NULL,
	cs100 float8 NULL,
	beta float8 NULL,
	currentnotional float8 NULL,
	cs300 float8 NULL,
	cs1000 float8 NULL,
	CONSTRAINT qf_sensitivities_pk PRIMARY KEY (date, "row")
);

CREATE TABLE swaptions (
	"date" date NOT NULL,
	"row" int8 NOT NULL,
	tradeid text NULL,
	productdescription text NULL,
	bookingentity_name text NULL,
	quantity int8 NULL,
	producttype text NULL,
	pv float8 NULL,
	expiration text NULL,
	dv01 float8 NULL,
	CONSTRAINT qf_swaptions_pk PRIMARY KEY (date, "row")
);

 CREATE SERVER dawndb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'dawndb');
 CREATE USER MAPPING FOR quantifi_user SERVER dawndb_server OPTIONS (user 'dawn_user', password  '****');

CREATE OR REPLACE
FUNCTION list_unterminated_tranche(p_start_from date) RETURNS TABLE (date date, productname TEXT, tradeid TEXT) AS $$
BEGIN
	RETURN query
	SELECT ct.date, a.productname, ct.tradeid
FROM(
	SELECT c.productname, sum(currentnotional)
FROM cdxtrancheut c
WHERE c.date = p_start_from
GROUP BY
		c.productname
HAVING
		sum(currentnotional)= 0) a
LEFT JOIN cdxtrancheut ct
	USING (productname);

END;

$$ LANGUAGE plpgsql;


CREATE TABLE quantifi_submission(
       id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	   uploadtime timestamptz,
	   filename text,
	   errors int,
	   warnings int,
	   successes int,
	   total int,
       UNIQUE (uploadtime, filename)
);