CREATE TABLE branch_groups
(
    id            NUMBER(19) PRIMARY KEY,
    project_id    NUMBER(19)               NOT NULL REFERENCES projects (id),
    parent_id     NUMBER(19) REFERENCES branch_groups (id),
    title         VARCHAR2(64)             NOT NULL,
    description   VARCHAR2(255)            NOT NULL,

    total_groups  NUMBER(5)                NOT NULL,
    total_members NUMBER(5)                NOT NULL,

    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
);

CREATE SEQUENCE branch_groups_id_seq;

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