chattyness/db/schema/tables/050_chat.sql

-- Chattyness Chat Schema Tables
-- PostgreSQL 18
--
-- Messages, whispers, shouts, and reactions
-- Load via: psql -f schema/tables/050_chat.sql
--
-- NOTE: This table is designed for future partitioning by created_at.
-- When message volume grows, convert to a partitioned table:
--   1. Rename chat.messages to chat.messages_old
--   2. Create new partitioned chat.messages with PARTITION BY RANGE (created_at)
--   3. Create monthly partitions (e.g., chat.messages_2025_01)
--   4. Migrate data from old table
--   5. Consider pg_partman extension for automatic partition management
--   6. Add archive table for long-term storage of old partitions

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Instance Messages (Scene-wide chat)
-- =============================================================================
-- Messages visible to everyone in an instance.
-- =============================================================================

CREATE TABLE chat.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,

    -- Sender (all users including guests - guests have 'guest' tag in auth.users)
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE SET NULL,

    -- Cached sender info (in case account deleted)
    sender_name public.display_name NOT NULL,

    -- Message content
    message_type chat.message_type NOT NULL DEFAULT 'normal',
    content TEXT NOT NULL,

    -- Position when message was sent (for speech bubble placement)
    position public.virtual_point,

    -- Reply threading
    reply_to_id UUID REFERENCES chat.messages(id) ON DELETE SET NULL,

    -- Moderation
    is_deleted BOOLEAN NOT NULL DEFAULT false,
    deleted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMPTZ,

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE chat.messages IS 'Instance messages (design supports future time-based partitioning)';
COMMENT ON COLUMN chat.messages.position IS 'Sender position when message sent (for speech bubbles)';
COMMENT ON COLUMN chat.messages.reply_to_id IS 'ID of message being replied to';

CREATE INDEX idx_chat_messages_instance_time ON chat.messages (instance_id, created_at DESC);
CREATE INDEX idx_chat_messages_user ON chat.messages (user_id, created_at DESC)
    WHERE user_id IS NOT NULL;
CREATE INDEX idx_chat_messages_reply ON chat.messages (reply_to_id)
    WHERE reply_to_id IS NOT NULL;
CREATE INDEX idx_chat_messages_created ON chat.messages (created_at DESC);

-- =============================================================================
-- Shouts (Server-wide broadcasts)
-- =============================================================================
-- Messages broadcast to all users on the server.
-- Typically limited to admins or special events.
-- =============================================================================

CREATE TABLE chat.shouts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE SET NULL,
    sender_name public.display_name NOT NULL,

    content TEXT NOT NULL,

    -- Targeting
    target_realm_id UUID REFERENCES realm.realms(id) ON DELETE CASCADE,  -- NULL = all realms

    -- TTL for display
    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE chat.shouts IS 'Server-wide or realm-wide broadcast messages';
COMMENT ON COLUMN chat.shouts.target_realm_id IS 'NULL = broadcast to all realms';

CREATE INDEX idx_chat_shouts_time ON chat.shouts (created_at DESC);
CREATE INDEX idx_chat_shouts_realm ON chat.shouts (target_realm_id, created_at DESC)
    WHERE target_realm_id IS NOT NULL;

-- =============================================================================
-- Whispers (Private Direct Messages)
-- =============================================================================
-- Private messages between two users, cross-realm capable.
-- =============================================================================

CREATE TABLE chat.whispers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Sender and recipient
    sender_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    recipient_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    content TEXT NOT NULL,

    -- Read status
    read_at TIMESTAMPTZ,

    -- Moderation
    is_deleted_by_sender BOOLEAN NOT NULL DEFAULT false,
    is_deleted_by_recipient BOOLEAN NOT NULL DEFAULT false,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT chk_chat_whispers_not_self CHECK (sender_id != recipient_id)
);

COMMENT ON TABLE chat.whispers IS 'Private direct messages between users';

CREATE INDEX idx_chat_whispers_sender ON chat.whispers (sender_id, created_at DESC);
CREATE INDEX idx_chat_whispers_recipient ON chat.whispers (recipient_id, created_at DESC);
CREATE INDEX idx_chat_whispers_unread ON chat.whispers (recipient_id, read_at)
    WHERE read_at IS NULL;

-- Composite index for conversation view between two users
CREATE INDEX idx_chat_whispers_conversation ON chat.whispers (
    LEAST(sender_id, recipient_id),
    GREATEST(sender_id, recipient_id),
    created_at DESC
);

-- =============================================================================
-- Message Reactions
-- =============================================================================
-- Emoji reactions to channel messages.
-- =============================================================================

CREATE TABLE chat.reactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    message_id UUID NOT NULL REFERENCES chat.messages(id) ON DELETE CASCADE,

    -- Reactor
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- Reaction (emoji or predefined reaction code)
    reaction TEXT NOT NULL CHECK (length(reaction) <= 32),

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- One reaction type per user per message
    CONSTRAINT uq_chat_reactions UNIQUE (message_id, user_id, reaction)
);

COMMENT ON TABLE chat.reactions IS 'Emoji reactions to channel messages';

CREATE INDEX idx_chat_reactions_message ON chat.reactions (message_id);
CREATE INDEX idx_chat_reactions_user ON chat.reactions (user_id);

COMMIT;