--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;