CREATE TABLE proposal.proposal
(
  id bigserial NOT NULL,
  reference uuid NOT NULL,
  system_id integer NOT NULL,
  legacy_id integer NOT NULL,
  organisation_id integer,
  legacy_organisation_id integer NOT NULL,
  has_been_anonymised boolean NOT NULL,
  external_source character varying,
  CONSTRAINT pk_proposal PRIMARY KEY (id),
  CONSTRAINT proposal_reference_key UNIQUE (reference),
  CONSTRAINT proposal_system_id_legacy_id_key UNIQUE (system_id, legacy_id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX proposal_has_been_anonymised_system_id_legacy_organisation__idx
  ON proposal.proposal
  USING btree
  (has_been_anonymised, system_id, legacy_organisation_id);

CREATE INDEX proposal_has_been_anonymised_system_id_legacy_organisation_idx1
  ON proposal.proposal
  USING btree
  (has_been_anonymised, system_id, legacy_organisation_id, reference);

--

CREATE TABLE proposal.note
(
  id bigserial NOT NULL,
  reference uuid NOT NULL,
  proposal_reference uuid NOT NULL,
  entry_time timestamp with time zone NOT NULL,
  legacy_originator_id integer,
  type_id integer NOT NULL,
  content text NOT NULL,
  legacy_read_by integer,
  time_read timestamp with time zone,
  "from" character varying(100),
  "to" character varying(100),
  originator_reference uuid,
  read_by_reference uuid,
  CONSTRAINT pk_note PRIMARY KEY (id),
  CONSTRAINT note_proposal_reference_fkey FOREIGN KEY (proposal_reference)
      REFERENCES proposal.proposal (reference) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT note_reference_key UNIQUE (reference)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX note_entry_time_type_id_idx
  ON proposal.note
  USING btree
  (entry_time, type_id)
  WHERE legacy_read_by IS NULL;

CREATE INDEX note_proposal_reference_idx
  ON proposal.note
  USING btree
  (proposal_reference);

CREATE INDEX note_proposal_reference_type_id_entry_time_idx
  ON proposal.note
  USING btree
  (proposal_reference, type_id, entry_time)
  WHERE legacy_read_by IS NULL;

CREATE INDEX note_type_id_entry_time_idx
  ON proposal.note
  USING btree
  (type_id, entry_time)
  WHERE legacy_read_by IS NULL;