migrations/0034_anonymous_chat.sql

-- Create chat_messages table
CREATE TABLE IF NOT EXISTS chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id VARCHAR NOT NULL REFERENCES orders(transaction_id) ON DELETE CASCADE,
    sender_type VARCHAR NOT NULL, -- 'BUYER', 'MERCHANT', 'SYSTEM'
    content TEXT NOT NULL,
    message_type VARCHAR NOT NULL DEFAULT 'TEXT', -- 'TEXT', 'REFUND_OFFER', 'REFUND_ACCEPTED', 'COUPON_OFFER', 'COUPON_CLAIMED', 'AI_MEDIATOR_REQUEST', 'AI_MEDIATOR_RESPONSE'
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Index for transaction_id
CREATE INDEX IF NOT EXISTS idx_chat_messages_transaction_id ON chat_messages(transaction_id);