

CREATE TABLE corporate_chat_inboxes
(
    id  BIGINT IDENTITY (1,1) PRIMARY KEY,
    user_id bigint NOT NULL references users(id),
    total_open_corporate_threads integer NOT NULL,
    total_unread_corporate_messages integer NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL
);

CREATE TABLE corporate_chat_inbox_threads
(
    id BIGINT IDENTITY (1,1) PRIMARY KEY,
    user_id bigint NOT NULL references users(id),
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    "left" BIT NOT NULL,
    archived BIT NOT NULL,
    total_unread_corporate_messages integer NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL,
    sorted_at DATETIMEOFFSET NOT NULL
);

CREATE UNIQUE INDEX corporate_chat_inbox_threads_user_id_corporate_chat_id
    on corporate_chat_inbox_threads(user_id, corporate_chat_id) WHERE archived = 0;

