CREATE SCHEMA dbp AUTHORIZATION {0};
CREATE TABLE dbp.metadata (
	id int2 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	"data" bytea NULL,
	CONSTRAINT metadata_pk PRIMARY KEY (id)
);
CREATE TABLE dbp.people (
	id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	login varchar NOT NULL,
	"password" varchar NULL,
	"data" bytea NULL,
	CONSTRAINT people_pk PRIMARY KEY (id)
);
CREATE TABLE dbp.changesets (
	id int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"data" bytea NOT NULL,
	"identity" uuid NOT NULL,
	CONSTRAINT changesets_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX changesets_identity_idx ON dbp.changesets USING btree (identity);
CREATE TABLE dbp.changeset_data (
	id uuid NOT NULL,
	"data" bytea NOT NULL,
	CONSTRAINT changeset_data_pk PRIMARY KEY (id)
);

CREATE TABLE dbp.counters (
	"name" varchar NOT NULL,
	"data" bytea NOT NULL,
	CONSTRAINT counters_pk PRIMARY KEY ("name")
);
CREATE TABLE dbp.databaseid (
	id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	"data" uuid NOT NULL,
	"version" int,
	"server_version" varchar NULL,
	"session_id" uuid,
	CONSTRAINT databaseid_pk PRIMARY KEY (id)
);
CREATE TABLE dbp.famediator (
	file_id uuid NOT NULL,
	object_id uuid NOT NULL,
	fa_id uuid NOT NULL,
	CONSTRAINT famediator_pk PRIMARY KEY (file_id)
);
CREATE INDEX IF NOT EXISTS ix_famediator_fa_id ON  dbp.famediator (fa_id);

CREATE TABLE dbp.faslist (
	fa_id uuid NOT NULL,
	"name" varchar NOT NULL,
	creation_date timestamptz NULL,
	is_writable bool NULL,
	CONSTRAINT faslist_pk PRIMARY KEY (fa_id)
);

CREATE TABLE dbp.objects (
	id uuid NULL,
	"data" bytea NULL,
	last_change INTEGER NOT NULL DEFAULT 0,
	CONSTRAINT objects_pk PRIMARY KEY (id)
);

CREATE INDEX idx_objects_id_last_change ON dbp.objects(id, last_change);

CREATE TABLE dbp.history_items (
	id uuid NOT NULL,
	object_id uuid NOT NULL,
	reason text NULL,
	creator_id int4 NOT NULL,
	created timestamptz NOT NULL,
	"object" bytea NOT NULL,
	due_to_person int4 NULL,
	CONSTRAINT history_items_pk PRIMARY KEY (id),
	CONSTRAINT history_items_objects_fk FOREIGN KEY (object_id) REFERENCES dbp.objects(id),
	CONSTRAINT history_items_people_fk FOREIGN KEY (creator_id) REFERENCES dbp.people(id)
);
CREATE INDEX history_items_creator_id_idx ON dbp.history_items USING btree (creator_id);
CREATE INDEX history_items_object_id_idx ON dbp.history_items USING btree (object_id);

CREATE TABLE dbp.interitable_access(
  object_id UUID, 
  "access" bytea NOT null,
 CONSTRAINT interitable_access_pk PRIMARY KEY (object_id),
 CONSTRAINT interitable_access_objects_fk FOREIGN KEY (object_id) REFERENCES dbp.objects(id)

 );

 CREATE TABLE dbp.notifications(
  id int NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
  data bytea);

CREATE INDEX notifications_index ON dbp.notifications(id);

CREATE TABLE dbp.organisationunits(
  id int NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
  "data" bytea,
 CONSTRAINT organisationunits_pk PRIMARY KEY (id));

 CREATE TABLE dbp.rules(
  id UUID PRIMARY KEY, 
  description bytea);

CREATE TABLE dbp.rules_changeset_data(
  rule_id UUID, 
  changeset_data_id UUID, 
  PRIMARY KEY(rule_id, changeset_data_id), 
  FOREIGN KEY(rule_id) REFERENCES dbp.rules(id), 
  FOREIGN KEY(changeset_data_id) REFERENCES dbp.changeset_data(id));

  CREATE TABLE dbp.settings(
  "key" varchar PRIMARY KEY, 
  "data" bytea);
  
  CREATE TABLE dbp.chats(
  id uuid PRIMARY KEY, 
  "name" text, 
  description text, 
  creator_id INT NOT null constraint fk_chats_people references dbp.people(id), 
  "type" SMALLINT NOT NULL, 
  last_message_id uuid, 
  creation_date_utc timestamptz);

CREATE TABLE dbp.messages(
  id uuid PRIMARY KEY, 
  "data" bytea, 
  creator_id INT NOT NULL constraint fk_chats_people references dbp.people(id), 
  date_time_server_utc timestamptz, 
  date_time_local_utc timestamptz NOT NULL, 
  chat_id uuid NOT NULL CONSTRAINT fk_messages_chats REFERENCES dbp.chats(Id), 
  related_message_id uuid CONSTRAINT fk_related_messages REFERENCES dbp.messages(id), 
  "type" smallint NOT NULL);

CREATE INDEX indx_messages_related ON dbp.messages(related_message_id);

CREATE INDEX indx_messages_chat
ON dbp.messages(
	 chat_id, 
  "type", 
  creator_id, 
  date_time_server_utc DESC);

alter table dbp.chats add constraint fk_chats_last_message FOREIGN KEY(last_message_id)  REFERENCES dbp.messages(id);

CREATE TABLE dbp.chat_members(
  chat_id uuid NOT NULL CONSTRAINT fk_chat_members_chats REFERENCES dbp.chats(Id), 
  person_id INT NOT NULL constraint fk_chats_people references dbp.people(id), 
  is_admin BOOLEAN NOT NULL DEFAULT false, 
  is_deleted BOOLEAN NOT NULL DEFAULT false, 
  is_notifiable BOOLEAN NOT NULL DEFAULT true, 
  date_updated timestamptz, 
  is_viewer_only boolean NOT NULL DEFAULT true, 
  CONSTRAINT chat_members_pk PRIMARY KEY(chat_id, person_id));

CREATE INDEX idx_chat_members ON dbp.chat_members(chat_id);

CREATE INDEX idx_chat_member_person
ON dbp.chat_members(
  person_id, 
  is_deleted);

CREATE INDEX idx_chat_member_updated ON dbp.chat_members(date_updated);

CREATE TABLE dbp.message_person(
  chat_id uuid NOT NULL CONSTRAINT fk_message_person_chats REFERENCES dbp.chats(id), 
  message_id uuid NOT NULL CONSTRAINT fk_message_person_messages REFERENCES dbp.messages(id), 
  person_id INT NOT NULL constraint fk_chats_people references dbp.people(id), 
  isSent BOOL NOT NULL DEFAULT false, 
  mark_as_unread BOOL NOT NULL DEFAULT false, 
  "date" timestamp with time zone NOT NULL, 
  PRIMARY KEY(message_id, person_id));

CREATE INDEX ix_message_person_chat
ON dbp.message_person(
  chat_id, 
  person_id, 
  mark_as_unread);

CREATE INDEX ix_message_person_person
ON dbp.message_person(
  person_id, 
  isSent, 
  date);

  CREATE TABLE dbp.message_relations(
  object_id uuid NOT NULL,
  chat_id uuid NOT NULL CONSTRAINT fk_relation_chat REFERENCES dbp.chats(id), 
  "type" smallint NOT NULL, 
  message_id uuid CONSTRAINT fk_relation_mesage REFERENCES dbp.messages(id), 
  is_deleted BOOLEAN NOT NULL DEFAULT false, 
  CONSTRAINT pk_message_relations PRIMARY KEY(object_id, chat_id));

CREATE INDEX indx_message_relations_chat
ON dbp.message_relations(
  chat_id, 
  is_deleted);

CREATE INDEX indx_relations_message ON dbp.message_relations(message_id);

CREATE TABLE dbp.third_party_info(
  third_party_info TEXT PRIMARY KEY NOT NULL UNIQUE, 
  messageId UUID NOT NULL REFERENCES dbp.messages(id));


  CREATE TABLE dbp.user_actions(
  id bigint PRIMARY KEY GENERATED always AS IDENTITY, 
  person_id int4 NOT null, 
  ip VARCHAR NOT NULL, 
  server_date_time timestamptz NOT NULL, 
  changeset_id uuid, 
  event_kind int4 NOT NULL, 
  object_id uuid, 
  object_type int4 NOT NULL, 
  object_title VARCHAR, 
  additional_info bytea, 
  error_string VARCHAR, 
  plugin_additional bytea null
);

CREATE INDEX ix_user_actions_person ON dbp.user_actions(person_id);

CREATE INDEX ix_user_actions_date ON dbp.user_actions(server_date_time);

CREATE INDEX ix_user_actions_event_kind ON dbp.user_actions(event_kind);

CREATE INDEX ix_user_actions_id_and_server_date_and_object_type_and_person ON dbp.user_actions(id DESC, server_date_time, object_type, person_id);

CREATE TABLE dbp.subscriptions (
    obj_id uuid NOT NULL CONSTRAINT fk_subscriptions_obj_id REFERENCES dbp.objects(id),
    person_id integer NOT NULL CONSTRAINT fk_subscriptions_person_id REFERENCES dbp.people(id),
    CONSTRAINT subscriptions_pk PRIMARY KEY (obj_id, person_id)
);

CREATE INDEX idx_subscriptions_obj_id ON dbp.subscriptions(obj_id);
CREATE INDEX idx_subscriptions_person_id ON dbp.subscriptions(person_id);

CREATE UNIQUE INDEX people_login_index ON dbp.people(login);

ALTER TABLE dbp.people add column last_change INTEGER;
CREATE INDEX idx_people_last_change ON dbp.people(last_change);
ALTER TABLE dbp.organisationunits add column last_change INTEGER;
CREATE INDEX idx_organisationunits_last_change ON dbp.organisationunits(last_change);

CREATE TABLE dbp.external_tasks (
	id uuid NOT NULL,
	"type" varchar NOT NULL,
	initiator_id int4 NOT null CONSTRAINT fk_external_task_initiator REFERENCES dbp.people(id),
	date_created timestamptz NOT NULL,
	date_started timestamptz NULL,
	date_finished timestamptz NULL,
	"handler" varchar NULL,
	task_data bytea NULL,
    is_canceled bool NOT NULL default FALSE,
	source_id uuid NULL,
	group_id uuid NULL,
    object_id uuid NULL,
    project_id uuid NULL,
    progress varchar NULL,
    additional varchar NULL,

    CONSTRAINT external_tasks_pk PRIMARY KEY (id)
);

CREATE INDEX indx_external_tasks_type_initiator_date ON dbp.external_tasks USING btree (type, initiator_id, date_created);
CREATE INDEX indx_external_tasks_initiator ON dbp.external_tasks USING btree (initiator_id, date_created);
CREATE INDEX indx_external_tasks_group_id ON dbp.external_tasks USING btree (group_id);
CREATE INDEX indx_external_tasks_type ON dbp.external_tasks USING btree (type, date_created);