-- Table: public.corporate_chat_events

-- chats
CREATE TABLE corporate_chats
(
    id NUMBER(19) NOT NULL PRIMARY KEY ,
    corporate_chat_event_id NUMBER(19) ,
    initiated NUMBER(1) default 0 not null,
    type VARCHAR2(32),
    deleted NUMBER(1) DEFAULT 0,
    total_messages NUMBER(19),
    corporate_chat_message_id NUMBER(19),
    title VARCHAR2(256),
    admin_id NUMBER(19) references users (id),
    changed_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL,
    deleted_at timestamp with time zone
);

CREATE SEQUENCE corporate_chats_id_seq;

CREATE INDEX corporate_chats_admin_id_title on corporate_chats (CASE WHEN COALESCE(deleted,0)=0 then admin_id WHEN COALESCE(deleted, 0) = 0 then title end);

-- actions

CREATE TABLE corporate_chat_actions
(
    id NUMBER(19) NOT NULL PRIMARY KEY ,
    corporate_chat_id NUMBER(19) NOT NULL references corporate_chats(id),
    type VARCHAR2(32) NOT NULL,
    actor_id NUMBER(19) references users (id),
    user_id NUMBER(19) references users (id),
    created_at timestamp with time zone NOT NULL,
    "comment" VARCHAR2(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 NUMBER(19) NOT NULL primary key,
    type VARCHAR2(32) NOT NULL,
    corporate_chat_id NUMBER(19),
    corporate_chat_message_id NUMBER(19),
    user_id NUMBER(19) references users (id),
    created_at timestamp with time zone NOT NULL
);

CREATE sequence corporate_chat_events_id_seq;

-- messages

CREATE TABLE corporate_chat_messages (
    id NUMBER(19) NOT NULL PRIMARY KEY,
    "uid" VARCHAR2(40) NOT NULL,
    corporate_chat_id NUMBER(19) NOT NULL references corporate_chats (id),
    local_id NUMBER(19) NOT NULL,
    user_id NUMBER(19) NOT NULL references users (id),
    type VARCHAR2(32),
    text VARCHAR2(4000) NOT NULL,
    file_id VARCHAR2(255),
    action_id NUMBER(19) references corporate_chat_actions (id),
    received NUMBER(1) NOT NULL,
    read NUMBER(1) 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 NUMBER(19) references corporate_chat_messages (id),
    deleted NUMBER(1),
    corporate_chat_event_id NUMBER(19) 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(CASE WHEN COALESCE(deleted,0) = 0 THEN user_id WHEN COALESCE(deleted,0) = 0 then corporate_chat_id end);


-- members

CREATE TABLE corporate_chat_members
(
    id NUMBER(19) NOT NULL PRIMARY KEY ,
    corporate_chat_id NUMBER(19) NOT NULL references corporate_chats (id),
    user_id NUMBER(19) NOT NULL references users (id),
    deleted NUMBER(1) 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 NUMBER(1) DEFAULT 0
);

CREATE SEQUENCE corporate_chat_members_id_seq;

CREATE UNIQUE INDEX corporate_chat_members_uniq ON corporate_chat_members (CASE WHEN deleted = 0 then user_id WHEN deleted = 0 then corporate_chat_id end);





