CREATE TABLE chat_bots (
    id                   NUMBER(19) PRIMARY KEY,
    project_id           NUMBER(19)               NOT NULL REFERENCES projects (id),
    chat_bot_platform_id NUMBER(19)               NOT NULL REFERENCES chat_bot_platforms (id),
    user_id              NUMBER(19)               NOT NULL REFERENCES users (id),
    bot_id               VARCHAR2(255 CHAR)       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 chat_bots_id_seq;

CREATE UNIQUE INDEX chat_bots_uniq ON chat_bots(
    CASE WHEN deleted = 0
        THEN project_id
    END,
    CASE WHEN deleted = 0
        THEN user_id
    END
);
