-- Groups

CREATE TABLE user_groups (
  id            BIGSERIAL PRIMARY KEY,
  org_id        BIGINT      NOT NULL REFERENCES orgs (id),
  name          VARCHAR     NOT NULL,
  deleted       BOOLEAN     NOT NULL DEFAULT FALSE,
  total_members INTEGER     NOT NULL DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX user_groups_org_id_name_nondeleted_uniq
  ON user_groups (org_id, lower(name))
  WHERE deleted IS FALSE;

-- Groups members

CREATE TABLE user_groups_members (
  id           BIGSERIAL PRIMARY KEY,
  group_id     BIGINT      NOT NULL REFERENCES user_groups (id),
  user_id      BIGINT      NOT NULL REFERENCES users (id),
  deleted      BOOLEAN     NOT NULL DEFAULT FALSE,
  role_deleted BOOLEAN     NOT NULL DEFAULT FALSE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX user_groups_members_group_id_ix
  ON user_groups_members (group_id);
CREATE UNIQUE INDEX user_groups_members_group_id_user_id_uniq
  ON user_groups_members (group_id, user_id);
