-- Table: public.corporate_chat_events

-- chats
CREATE TABLE corporate_chats
(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
    corporate_chat_event_id bigint ,
    initiated bit default 0 not null,
    type NVARCHAR(32),
    [deleted] BIT DEFAULT 0,
    total_messages bigint,
    corporate_chat_message_id bigint,
    title NVARCHAR(256),
    admin_id bigint references users(id),
    changed_at DATETIMEOFFSET,
    created_at DATETIMEOFFSET,
    deleted_at DATETIMEOFFSET
);

CREATE INDEX corporate_chats_admin_id_title on corporate_chats (admin_id, title);

-- actions

CREATE TABLE corporate_chat_actions
(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    type NVARCHAR(32) NOT NULL,
    actor_id bigint references users(id),
    user_id bigint references users(id),
    created_at DATETIMEOFFSET NOT NULL,
    comment NVARCHAR(500)
);

CREATE INDEX corporate_chat_actions_corporate_chat_id_user_id on corporate_chat_actions(corporate_chat_id, user_id);

-- events

CREATE TABLE corporate_chat_events
(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    type NVARCHAR(32) NOT NULL,
    corporate_chat_id bigint,
    corporate_chat_message_id bigint,
    user_id bigint references users(id),
    created_at DATETIMEOFFSET
);

-- messages

CREATE TABLE corporate_chat_messages
(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
    uid NVARCHAR(40) NOT NULL,
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    local_id bigint NOT NULL,
    user_id bigint NOT NULL references users(id),
    type NVARCHAR(32),
    text NVARCHAR(4000) NOT NULL,
    file_id NVARCHAR(255),
    action_id bigint references corporate_chat_actions(id),
    received BIT NOT NULL,
    [read] BIT NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL,
    received_at DATETIMEOFFSET,
    read_at DATETIMEOFFSET,
    reply_to_message_id bigint references corporate_chat_messages(id),
    [deleted] BIT,
    corporate_chat_event_id bigint references corporate_chat_events(id)
);

CREATE INDEX corporate_chat_messages_corporate_chat_id_user_id on corporate_chat_messages(corporate_chat_id, user_id);

-- members

CREATE TABLE corporate_chat_members
(
    id BIGINT IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    user_id bigint NOT NULL references users(id),
    [deleted] BIT NOT NULL,
    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL,
    deleted_at DATETIMEOFFSET,
    is_admin BIT DEFAULT 0
);

CREATE UNIQUE INDEX corporate_chat_members_uniq
    ON corporate_chat_members (user_id, corporate_chat_id) where deleted = 0;




