-- Table: public.corporate_chat_events

-- chats
CREATE TABLE corporate_chats
(
    id bigint NOT NULL PRIMARY KEY ,
    corporate_chat_event_id bigint ,
    initiated boolean not null default false,
    type character varying(32),
    deleted boolean DEFAULT false,
    total_messages bigint,
    corporate_chat_message_id bigint,
    title character varying(256),
    admin_id bigint references users(id),
    changed_at timestamp with time zone NOT NULL DEFAULT now(),
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    deleted_at timestamp with time zone
);

CREATE SEQUENCE corporate_chats_id_seq;

CREATE INDEX corporate_chats_admin_id_title on corporate_chats (admin_id, title) WHERE COALESCE(deleted, false) = false;

-- actions

CREATE TABLE corporate_chat_actions
(
    id bigint NOT NULL PRIMARY KEY ,
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    type character varying(32) NOT NULL,
    actor_id bigint references users(id),
    user_id bigint references users(id),
    created_at timestamp with time zone NOT NULL,
    comment character varying(500)
);

CREATE SEQUENCE corporate_chat_actions_id_seq;

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 NOT NULL primary key,
    type character varying(32) NOT NULL,
    corporate_chat_id bigint,
    corporate_chat_message_id bigint,
    user_id bigint references users(id),
    created_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE sequence corporate_chat_events_id_seq;

-- messages

CREATE TABLE corporate_chat_messages
(
    id bigint NOT NULL PRIMARY KEY ,
    uid character varying(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 character varying(32),
    text character varying NOT NULL,
    file_id character varying(255),
    action_id bigint references corporate_chat_actions(id),
    received boolean NOT NULL,
    read boolean NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    received_at timestamp with time zone,
    read_at timestamp with time zone,
    reply_to_message_id bigint references corporate_chat_messages(id),
    deleted boolean,
    corporate_chat_event_id bigint references corporate_chat_events(id)
);

CREATE SEQUENCE corporate_chat_messages_id_seq;

CREATE INDEX corporate_chat_messages_corporate_chat_id_user_id on corporate_chat_messages(corporate_chat_id, user_id) WHERE COALESCE(deleted, false) = false;

-- members

CREATE TABLE corporate_chat_members
(
    id bigint NOT NULL PRIMARY KEY ,
    corporate_chat_id bigint NOT NULL references corporate_chats(id),
    user_id bigint NOT NULL references users(id),
    deleted boolean NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    deleted_at timestamp with time zone,
    is_admin boolean DEFAULT false
);

CREATE SEQUENCE corporate_chat_members_id_seq;

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



