michangarrito/database/schemas/11-messaging.sql

-- =============================================================================
-- MICHANGARRITO - 11 MESSAGING
-- =============================================================================
-- WhatsApp y notificaciones
-- =============================================================================

-- Conversaciones
CREATE TABLE IF NOT EXISTS messaging.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES public.tenants(id),

    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(100),

    conversation_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',

    last_message_at TIMESTAMPTZ,
    last_message_preview TEXT,
    unread_count INTEGER DEFAULT 0,

    wa_conversation_id VARCHAR(100),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_conversations_tenant ON messaging.conversations(tenant_id);
CREATE INDEX idx_conversations_phone ON messaging.conversations(phone_number);

CREATE TRIGGER update_conversations_updated_at
    BEFORE UPDATE ON messaging.conversations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Mensajes
CREATE TABLE IF NOT EXISTS messaging.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES messaging.conversations(id) ON DELETE CASCADE,

    direction VARCHAR(10) NOT NULL,

    message_type VARCHAR(20) NOT NULL,
    content TEXT,
    media_url TEXT,
    media_mime_type VARCHAR(50),

    processed_by_llm BOOLEAN DEFAULT false,
    llm_response_id UUID,
    tokens_used INTEGER,

    wa_message_id VARCHAR(100),
    wa_status VARCHAR(20),
    wa_timestamp TIMESTAMPTZ,

    error_code VARCHAR(20),
    error_message TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messaging.messages(conversation_id);
CREATE INDEX idx_messages_wa ON messaging.messages(wa_message_id);

-- Notificaciones
CREATE TABLE IF NOT EXISTS messaging.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(id),

    notification_type VARCHAR(50) NOT NULL,
    channels TEXT[] NOT NULL,

    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    data JSONB,

    push_sent BOOLEAN DEFAULT false,
    push_sent_at TIMESTAMPTZ,
    whatsapp_sent BOOLEAN DEFAULT false,
    whatsapp_sent_at TIMESTAMPTZ,

    read_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notifications_tenant ON messaging.notifications(tenant_id);
CREATE INDEX idx_notifications_user ON messaging.notifications(user_id);