

CREATE TABLE client_groups
(
    id            BIGINT PRIMARY KEY    IDENTITY (1, 1),
    project_id bigint NOT NULL references projects(id),
    parent_id bigint references client_groups(id),
    title nvarchar(64) NOT NULL,
    description nvarchar(255) NOT NULL,
    total_client_groups integer NOT NULL,
    total_members integer NOT NULL,
    priority integer,
    deleted bit NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL,
    deleted_at DATETIMEOFFSET
);

CREATE INDEX client_groups_project_id_ix
    ON client_groups (project_id);

CREATE INDEX client_groups_parent_id_ix
    ON client_groups (parent_id);

CREATE UNIQUE INDEX client_groups_project_id_title_null_parent_id_not_deleted_uniq
    ON client_groups (project_id, title)
    WHERE parent_id IS NULL AND deleted = 0;


CREATE TABLE client_group_members
(
    id BIGINT PRIMARY KEY    IDENTITY (1, 1),
    client_group_id bigint NOT NULL references client_groups(id),
    client_id bigint NOT NULL references clients(id),
    deleted bit NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL,
    deleted_at DATETIMEOFFSET,
    restored_at DATETIMEOFFSET
);

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

CREATE INDEX client_group_members_client_id_ix
    ON client_group_members (client_id);

