CREATE TABLE branch_groups (
  id            BIGINT       PRIMARY KEY,
  project_id    BIGINT       NOT NULL REFERENCES projects(id),
  parent_id     BIGINT       REFERENCES branch_groups(id),
  title         VARCHAR(64)  NOT NULL,
  description   VARCHAR(255) 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 branch_groups_id_seq;

CREATE TABLE branch_group_members (
  id          BIGINT      PRIMARY KEY,
  group_id    BIGINT      NOT NULL REFERENCES branch_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 branch_group_members_id_seq;
