--- groups


CREATE TABLE groups (
  id            BIGINT PRIMARY KEY,
  project_id    BIGINT       NOT NULL REFERENCES projects (id),
  parent_id     BIGINT REFERENCES groups (id),
  title         VARCHAR(64)  NOT NULL,
  description   VARCHAR(255) NOT NULL,
  admin         BOOLEAN      NOT NULL,
  permissions   VARCHAR      NOT NULL,

  total_groups  INTEGER      NOT NULL,
  total_members INTEGER      NOT NULL,

  deleted       BOOLEAN      NOT NULL,
  created_at    TIMESTAMPTZ  NOT NULL,
  updated_at    TIMESTAMPTZ  NOT NULL,
  deleted_at    TIMESTAMPTZ
);

CREATE SEQUENCE groups_id_seq;

CREATE INDEX groups_parent_id_ix
  ON groups (parent_id);

CREATE UNIQUE INDEX groups_project_id_parent_id_title_not_deleted_uniq
  ON groups (project_id, coalesce(parent_id, -1), lower(title))
  WHERE parent_id IS NULL AND deleted = FALSE;

-- group-members

CREATE TABLE group_members (
  id          BIGINT PRIMARY KEY,
  group_id    BIGINT      NOT NULL REFERENCES groups (id),
  user_id     BIGINT      NOT NULL REFERENCES users (id),
  deleted     BOOLEAN     NOT NULL,

  created_at  TIMESTAMPTZ NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL,
  deleted_at  TIMESTAMPTZ,
  restored_at TIMESTAMPTZ
);

CREATE SEQUENCE group_members_id_seq;

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

CREATE INDEX group_members_user_id_ix
  ON group_members (user_id);
