
CREATE TABLE client_groups
(
    id NUMBER(19) PRIMARY KEY NOT NULL,
    project_id NUMBER(19) NOT NULL references projects(id),
    parent_id NUMBER(19) references client_groups(id),
    title VARCHAR2(64) NOT NULL,
    description VARCHAR2(255) NOT NULL,
    total_client_groups NUMBER(10) NOT NULL,
    total_members NUMBER(10) NOT NULL,
    priority NUMBER(2),
    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 client_groups_id_seq;

CREATE INDEX client_groups_parent_id_ix
    ON client_groups (parent_id);

CREATE UNIQUE INDEX client_groups_project_id_title_uniq
    ON client_groups (
               CASE WHEN deleted = 0
                        THEN project_id END,
               CASE WHEN deleted = 0
                        THEN coalesce(parent_id, -1) END,
               CASE WHEN deleted = 0
                        THEN lower(title) END
        );

CREATE TABLE client_group_members
(
    id NUMBER(19) PRIMARY KEY NOT NULL,
    client_group_id NUMBER(19) NOT NULL references client_groups(id),
    client_id NUMBER(19) NOT NULL references clients(id),
    deleted boolean 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 client_group_members_id_seq;

CREATE INDEX client_group_members_client_id_ix
    ON client_group_members (client_id);

CREATE UNIQUE INDEX client_group_members_group_client_uniq
    ON client_group_members (client_group_id, client_id);

