-- 004-groups


CREATE TABLE groups (
  id                 NUMBER(19) PRIMARY KEY,
  project_id         NUMBER(19)               NOT NULL REFERENCES projects (id),
  parent_id          NUMBER(19) REFERENCES groups (id),
  title              VARCHAR2(64 CHAR)        NOT NULL,
  description        VARCHAR2(255 CHAR)       NOT NULL,
  admin              NUMBER(1)                NOT NULL,
  deleted            NUMBER(1)                NOT NULL,
  permissions        VARCHAR2(4000 CHAR),

  total_groups       NUMBER(10)               NOT NULL,
  total_members      NUMBER(10)               NOT NULL,
  total_open_tickets NUMBER(10)               NOT NULL,

  created_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at         TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE groups_id_seq;

CREATE INDEX groups_parent_id_ix
  ON groups (parent_id);

CREATE UNIQUE INDEX groups_project_id_title_uniq
  ON groups (
    CASE WHEN deleted = 0
      THEN project_id END,
    CASE WHEN deleted = 0
      THEN coalesce(parent_id, -1) END,
    CASE WHEN deleted = 0
      THEN lower(title) END
  );

-- group-members

CREATE TABLE group_members (
  id          NUMBER(19) PRIMARY KEY,
  group_id    NUMBER(19)               NOT NULL REFERENCES groups (id),
  user_id     NUMBER(19)               NOT NULL REFERENCES users (id),
  deleted     NUMBER(1)                NOT NULL,

  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at  TIMESTAMP WITH TIME ZONE,
  restored_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE group_members_id_seq;

CREATE INDEX group_members_user_id_ix
  ON group_members (user_id);

CREATE UNIQUE INDEX group_members_group_user_uniq
  ON group_members (group_id, user_id);
