Chat and messaging

CREATE TYPE channel_kind AS ENUM ('public', 'private', 'direct');

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL
);

CREATE TABLE workspaces (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    slug text NOT NULL UNIQUE
);

CREATE TABLE channels (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    workspace_id uuid NOT NULL,
    kind channel_kind DEFAULT 'public' NOT NULL,
    name text,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX channels_workspace_id_idx ON channels (workspace_id);

CREATE TABLE channel_members (
    channel_id uuid NOT NULL,
    user_id uuid NOT NULL,
    last_read_at timestamptz,
    PRIMARY KEY (channel_id, user_id)
);
CREATE INDEX channel_members_user_id_idx ON channel_members (user_id);

CREATE TABLE messages (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    channel_id uuid NOT NULL,
    author_id uuid NOT NULL,
    parent_id bigint,
    body text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    edited_at timestamptz
);
CREATE INDEX messages_channel_idx ON messages (channel_id, created_at);
CREATE INDEX messages_author_id_idx ON messages (author_id);
CREATE INDEX messages_parent_id_idx ON messages (parent_id);

CREATE TABLE reactions (
    message_id bigint NOT NULL,
    user_id uuid NOT NULL,
    emoji text NOT NULL,
    PRIMARY KEY (message_id, user_id, emoji)
);
CREATE INDEX reactions_user_id_idx ON reactions (user_id);

CREATE TABLE attachments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    message_id bigint NOT NULL,
    file_name text NOT NULL,
    content_type text NOT NULL,
    size_bytes bigint NOT NULL,
    storage_key text NOT NULL
);
CREATE INDEX attachments_message_id_idx ON attachments (message_id);

ALTER TABLE channels ADD FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE;

ALTER TABLE channel_members ADD FOREIGN KEY (channel_id) REFERENCES channels (id) ON DELETE CASCADE;

ALTER TABLE channel_members ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE messages ADD FOREIGN KEY (channel_id) REFERENCES channels (id) ON DELETE CASCADE;

ALTER TABLE messages ADD FOREIGN KEY (author_id) REFERENCES users (id);

ALTER TABLE messages ADD FOREIGN KEY (parent_id) REFERENCES messages (id) ON DELETE CASCADE;

ALTER TABLE reactions ADD FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE;

ALTER TABLE reactions ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE attachments ADD FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE;