-- Migration 014: Create messaging tables
-- Conversation: message thread
-- ConversationParticipant: user participation (polymorphic: team_profile or customer_profile)
-- Message: individual message
-- MessageReadReceipt: tracks when messages are read

-- Message thread/conversation
CREATE TABLE conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    subject VARCHAR(500),
    conversation_type conversation_type NOT NULL DEFAULT 'DIRECT',

    -- Polymorphic: entity this conversation is about
    entity_type VARCHAR(50),
    entity_id UUID,

    -- Polymorphic: who created the conversation
    created_by_type VARCHAR(20),  -- 'team_profile' or 'customer_profile'
    created_by_id UUID,

    last_message_at TIMESTAMPTZ,
    is_archived BOOLEAN NOT NULL DEFAULT FALSE,
    metadata JSONB NOT NULL DEFAULT '{}'::JSONB
);

-- Conversation participant (polymorphic)
CREATE TABLE conversation_participants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,

    -- Polymorphic: participant type
    participant_type VARCHAR(20) NOT NULL,  -- 'team_profile' or 'customer_profile'
    participant_id UUID NOT NULL,

    last_read_at TIMESTAMPTZ,
    unread_count INTEGER NOT NULL DEFAULT 0 CHECK (unread_count >= 0),
    is_muted BOOLEAN NOT NULL DEFAULT FALSE,
    is_archived BOOLEAN NOT NULL DEFAULT FALSE,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- One entry per participant per conversation
    UNIQUE (conversation_id, participant_type, participant_id)
);

-- Individual message
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,

    -- Polymorphic: author type
    author_type VARCHAR(20) NOT NULL,  -- 'team_profile' or 'customer_profile'
    author_id UUID NOT NULL,

    content TEXT NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    -- For threaded replies
    reply_to_id UUID REFERENCES messages(id) ON DELETE SET NULL,

    -- File attachments (JSONB array of attachment metadata)
    attachments JSONB NOT NULL DEFAULT '[]'::JSONB,

    -- System-generated message (e.g., "User joined conversation")
    is_system_message BOOLEAN NOT NULL DEFAULT FALSE,

    -- Additional metadata (formatting, mentions, etc.)
    metadata JSONB NOT NULL DEFAULT '{}'::JSONB
);

-- Message read receipts (tracks when specific messages are read)
CREATE TABLE message_read_receipts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

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

    -- Polymorphic: reader type
    reader_type VARCHAR(20) NOT NULL,  -- 'team_profile' or 'customer_profile'
    reader_id UUID NOT NULL,

    read_at TIMESTAMPTZ NOT NULL,

    -- One receipt per message per reader
    UNIQUE (message_id, reader_type, reader_id)
);

-- Indexes
CREATE INDEX idx_conversations_type ON conversations(conversation_type);
CREATE INDEX idx_conversations_entity ON conversations(entity_type, entity_id);
CREATE INDEX idx_conversations_last_message ON conversations(last_message_at DESC);
CREATE INDEX idx_conversation_participants_conversation ON conversation_participants(conversation_id);
CREATE INDEX idx_conversation_participants_participant ON conversation_participants(participant_type, participant_id);
CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_author ON messages(author_type, author_id);
CREATE INDEX idx_messages_created ON messages(created_at DESC);
CREATE INDEX idx_message_read_receipts_message ON message_read_receipts(message_id);