CREATE TABLE edna_chatbot_channels (
                               id BIGINT PRIMARY KEY REFERENCES channels(id),
                               uuid NVARCHAR(255),
                               deleted BIT,

                               created_at DATETIMEOFFSET NOT NULL,
                               updated_at DATETIMEOFFSET NOT NULL,
                               deleted_at DATETIMEOFFSET,
                               restored_at DATETIMEOFFSET
);

CREATE UNIQUE INDEX edna_chatbot_channels_uuid_ix ON edna_chatbot_channels(uuid);

CREATE TABLE edna_chatbot_configs (
                              channel_id BIGINT PRIMARY KEY REFERENCES edna_chatbot_channels(id),

                              url NVARCHAR(255),
                              api_key NVARCHAR(255)
);

CREATE TABLE edna_chatbot_users (
                            id BIGINT IDENTITY (1,1) PRIMARY KEY,
                            name VARCHAR(255),
                            phone nvarchar(15),
                            email nvarchar(225),
                            crm_id nvarchar(255),
                            edna_client_id nvarchar(255),
                            created_at DATETIMEOFFSET NOT NULL,
                            updated_at DATETIMEOFFSET NOT NULL
);

CREATE TABLE edna_chatbot_chats (
                            id BIGINT IDENTITY (1,1) PRIMARY KEY,
                            chat_id BIGINT NOT NULL REFERENCES chats(id),
                            user_id BIGINT NOT NULL REFERENCES edna_chatbot_users(id),

                            created_at DATETIMEOFFSET NOT NULL,
                            updated_at DATETIMEOFFSET NOT NULL
);

alter table clients add edna_chatbot_id bigint;
alter table tickets add edna_session_id nvarchar(255);
