-- Table: trade

CREATE TABLE trade
(
  id bigserial NOT NULL,
  time_executed TIMESTAMP WITH TIME zone NOT NULL,
  time_booked TIMESTAMP WITH TIME zone NOT NULL,
  id_book INTEGER NOT NULL,
  id_book_counter INTEGER NOT NULL,
  id_secdb BIGINT,
  id_secdb_transaction INTEGER,
  id_trader INTEGER NOT NULL,
  id_tradelabel INTEGER NOT NULL,
  id_external text,
  CONSTRAINT cons_trade_primary_key PRIMARY KEY (id),
  CONSTRAINT cons_trade_book FOREIGN KEY (id_book)
      REFERENCES book (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_trade_book_counter FOREIGN KEY (id_book_counter)
      REFERENCES book (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_trade_tl FOREIGN KEY (id_tradelabel)
      REFERENCES tradelabel (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_trade_trader FOREIGN KEY (id_trader)
      REFERENCES trader (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_tl_chk_books CHECK (id_book <> id_book_counter)
)
WITH (
  OIDS=FALSE
);


CREATE INDEX idx_trade_id_book
  ON trade
  USING btree
  (id_book, time_executed, id);

CREATE INDEX idx_trade_id_book_counter
  ON trade
  USING btree
  (id_book_counter, time_executed, id);

CREATE INDEX idx_trade_id_external
  ON trade
  USING btree
  (id_external COLLATE pg_catalog."default");

CREATE INDEX idx_trade_id_secdb
  ON trade
  USING btree
  (id_secdb, id);

CREATE INDEX idx_trade_id_secdb_trx
  ON trade
  USING btree
  (id_secdb_transaction, id);

CREATE INDEX idx_trade_id_tradelabel
  ON trade
  USING btree
  (id_tradelabel, time_executed, id);

CREATE INDEX idx_trade_time_booked
  ON trade
  USING btree
  (time_booked, id);

CREATE INDEX idx_trade_time_executed
  ON trade
  USING btree
  (time_executed, id);
ALTER TABLE trade CLUSTER ON idx_trade_time_executed;


-- Table: position_effect

CREATE TABLE position_effect
(
  id bigserial NOT NULL,
  id_trade BIGINT NOT NULL,
  id_asset INTEGER NOT NULL,
  quantity DOUBLE PRECISION NOT NULL,
  CONSTRAINT cons_pe_primary_key PRIMARY KEY (id_trade, id_asset),
  CONSTRAINT cons_pe_asset FOREIGN KEY (id_asset)
      REFERENCES asset (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_pe_trade FOREIGN KEY (id_trade)
      REFERENCES trade (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cons_pe_unique_id UNIQUE (id)
  WITH (FILLFACTOR=100)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE position_effect
  OWNER TO procuser;
GRANT ALL ON TABLE position_effect TO procuser;
GRANT SELECT ON TABLE position_effect TO procuser_ro;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE position_effect TO procuser_rw;
GRANT ALL ON TABLE position_effect TO standard;


CREATE INDEX idx_pe_id_asset
  ON position_effect
  USING btree
  (id_asset, id_trade);