CREATE TABLE ticket_comments (
    id NUMBER(19) PRIMARY KEY,
    ticket_id NUMBER(19) NOT NULL REFERENCES tickets(id),
    chat_id NUMBER(19) NOT NULL REFERENCES chats(id),
    client_id NUMBER(19) REFERENCES clients(id),
    channel_type varchar2(100),
    author_id NUMBER(19) REFERENCES users(id),
    edited_by_user_id NUMBER(19) REFERENCES users(id),
    deleted_by_user_id NUMBER(19) REFERENCES users(id),
    text varchar2(512) not null ,
    deleted number(1) default 0,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    edited_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE ticket_comments_id_seq;

create index ticket_comments_ticket_comments_id_ticket_id_ix
    on ticket_comments(CASE WHEN deleted = 0
                                THEN id
                           END,
                       CASE WHEN deleted = 0
                                THEN ticket_id
                           END);
