1 files changed, 111 insertions, 0 deletions
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
new file mode 100755
index 00000000..e7fb604f
--- /dev/null
+++ b/sql/upgrade.sql
@@ -0,0 +1,111 @@
+--CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future');
+--create type fund as enum('SERCGMAST', 'BRINKER', 'BOWDST');
+CREATE TYPE status as ENUM('Pending', 'Processed', 'Submitted', 'Acknowledged');
+
+alter table bonds rename to bonds_old;
+
+create table bonds_temp as (select first_value(id) over w as id, 'NEW'::action, folder, cp_code, trade_date, settle_date, cusip, isin, identifier, description, buysell,
+sum(faceamount) over w as faceamount, faceamount as notional, id as orig_id, price, accrued, asset_class, ticket, sum(principal_payment) over w as principal_payment, sum(accrued_payment) over w as accrued_payment, fund from bonds_old
+window w as (partition by trade_date, identifier, cp_code, price));
+
+
+CREATE TABLE bonds (id integer generated always as identity primary key,
+       dealid varchar(28) generated always as ('SC_'||case when asset_class ='Subprime' then 'SUB' when asset_class='CLO' then 'CLO' when asset_class='CSO' then 'CSO' when asset_class='CRT' then 'CRT' end||id::text) STORED,
+       folder bond_strat NOT NULL,
+       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,
+       current_face float,
+       allocated boolean default true not 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);
+RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER bond_notify AFTER
+INSERT OR UPDATE OF
+    identifier,
+    cusip,
+    isin,
+	faceamount,
+    price,
+    trade_date,
+	settle_date
+ON
+	bonds
+FOR EACH ROW 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();
+      
+
+insert into bonds (id, folder, cp_code, trade_date, settle_date, cusip, isin, identifier, description, buysell, faceamount,price, accrued, asset_class,
+ticket, principal_payment, accrued_payment, current_face) overriding SYSTEM VALUE
+select bonds_temp.id, folder, cp_code, trade_date, settle_date, cusip, isin, identifier, description, buysell, faceamount,
+price, accrued, asset_class, ticket, principal_payment, accrued_payment, (principal_payment/price)*100 from bonds_temp where orig_id=id;
+
+create table allocation_temp as (select id, notional, fund from bonds_temp);
+
+--alter table bonds alter column id restart with 3000;
+
+
+
+CREATE TABLE bond_allocation (
+	id int4 NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+	tradeid int4 NOT NULL REFERENCES bond(id) ON DELETE CASCADE,
+	notional float8 NOT NULL,
+	code text NOT NULL REFERENCES accounts,
+	submitted bool NOT NULL DEFAULT false,
+	UNIQUE (tradeid, code)
+);
+
+
+insert into bond_allocation(tradeid, notional, code) (select id, sum(notional), (case when fund='SERCGMAST' then 'BAC'
+when fund='BRINKER' then 'BBH' when fund='BOWDST' then 'BONY' end)
+from allocation_temp group by id, fund);
+
+
+CREATE TABLE bond_submission(
+	id int4 NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+	allocation_id int4 NOT NULL REFERENCES bond_allocation(id) ON DELETE CASCADE,
+	"action" "action" NOT NULL,
+	submit_date timestamptz NOT NULL DEFAULT now(),
+	status status NOT NULL DEFAULT 'Pending',
+);
+
+update bond_allocation set submitted = true;
+update bonds set allocated = true;