
CREATE TABLE corporate_chat_inboxes
(
    user_id bigint NOT NULL references users(id) PRIMARY KEY,
    total_open_corporate_threads integer NOT NULL,
    total_unread_corporate_messages integer 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 bigint NOT NULL primary key,
    user_id bigint NOT NULL references users(id),
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    "left" boolean NOT NULL,
    archived boolean NOT NULL,
    total_unread_corporate_messages integer 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(user_id, corporate_chat_id) WHERE archived = FALSE;

