

CREATE TABLE corporate_chat_inboxes
(
    user_id NUMBER(19) NOT NULL references users(id) PRIMARY KEY,
    total_open_corporate_threads NUMBER(10) NOT NULL,
    total_unread_corporate_messages NUMBER(10) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

CREATE TABLE corporate_chat_inbox_threads
(
    id NUMBER(19) NOT NULL primary key,
    user_id NUMBER(19) NOT NULL references users(id),
    corporate_chat_id NUMBER(19) NOT NULL references corporate_chats(id),
    "left" NUMBER(1) NOT NULL,
    archived NUMBER(1) NOT NULL,
    total_unread_corporate_messages NUMBER(10) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    sorted_at timestamp with time zone NOT NULL
);

CREATE SEQUENCE corporate_chat_inbox_threads_id_seq;

CREATE UNIQUE INDEX corporate_chat_inbox_threads_user_id_corporate_chat_id
    on corporate_chat_inbox_threads(CASE WHEN archived = 0 then user_id WHEN archived = 0 THEN corporate_chat_id END );

