

CREATE TABLE client_groups
(
    id bigint PRIMARY KEY NOT NULL,
    project_id bigint NOT NULL references projects(id),
    parent_id bigint references client_groups(id),
    title character varying(64) NOT NULL,
    description character varying(255) NOT NULL,
    total_client_groups integer NOT NULL,
    total_members integer NOT NULL,
    priority integer,
    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
);


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_parent_id_title_not_deleted_uniq
    ON client_groups (project_id, coalesce(parent_id, -1), lower(title))
    WHERE parent_id IS NULL AND deleted = FALSE;

CREATE TABLE IF NOT EXISTS client_group_members
(
    id bigint PRIMARY KEY NOT NULL,
    client_group_id bigint NOT NULL references client_groups(id),
    client_id bigint 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 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);

