CREATE TABLE edna_chatbot_channels (
                               id NUMBER(19) PRIMARY KEY REFERENCES channels(id),
                               uuid VARCHAR2(255),
                               deleted NUMBER(1),

                               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 UNIQUE INDEX edna_chatbot_channels_uuid_ix ON edna_chatbot_channels(uuid);

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

                              url VARCHAR2(255),
                              api_key VARCHAR2(255)
);

CREATE TABLE edna_chatbot_users (
                            id NUMBER(19) PRIMARY KEY NOT NULL,
                            name VARCHAR2(255),
                            phone varchar2(15),
                            email varchar2(225),
                            crm_id varchar2(255),
                            edna_client_id varchar2(255),
                            created_at timestamp with time zone NOT NULL,
                            updated_at timestamp with time zone NOT NULL
);

CREATE SEQUENCE IF NOT EXISTS edna_chatbot_users_id_seq;

CREATE TABLE edna_chatbot_chats (
                            id BIGSERIAL PRIMARY KEY,
                            chat_id NUMBER(19) NOT NULL REFERENCES chats(id),
                            user_id NUMBER(19) NOT NULL REFERENCES edna_chatbot_users(id),

                            created_at timestamp with time zone NOT NULL,
                            updated_at timestamp with time zone NOT NULL
);

alter table clients add edna_chatbot_id number(19);
alter table tickets add edna_session_id varchar2(255);
