CREATE TABLE groups_permissions (
  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,
  options       VARCHAR
);

CREATE SEQUENCE groups_permissions_id_seq;

CREATE TABLE group_permissions_members (
  id          BIGINT      PRIMARY KEY,
  group_id    BIGINT      NOT NULL REFERENCES groups_permissions(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_permissions_members_id_seq;
