erp-core/docs/04-modelado/database-design/DDL-SPEC-messaging.md

DDL Specification: messaging Schema

Identificacion

Campo	Valor
Schema	messaging
Modulo	MGN-017 WhatsApp Business Integration
Version	1.0
Fecha	2025-12-05
Estado	Ready

---

Diagrama ER

erDiagram
    tenants ||--o{ whatsapp_accounts : has
    whatsapp_accounts ||--o{ whatsapp_templates : defines
    whatsapp_accounts ||--o{ whatsapp_conversations : hosts
    whatsapp_conversations ||--o{ whatsapp_messages : contains
    whatsapp_conversations }o--|| contacts : with
    whatsapp_conversations }o--|| users : assigned_to
    tenants ||--o{ chatbot_flows : creates
    chatbot_flows ||--o{ chatbot_nodes : contains
    whatsapp_conversations ||--o{ chatbot_sessions : runs

    whatsapp_accounts {
        uuid id PK
        uuid tenant_id FK
        string phone_number UK
        string display_name
        string waba_id
        string status
        boolean is_active
    }

    whatsapp_templates {
        uuid id PK
        uuid account_id FK
        string name
        string category
        string language
        string status
        jsonb components
    }

    whatsapp_conversations {
        uuid id PK
        uuid account_id FK
        uuid contact_id FK
        uuid assigned_user_id FK
        string wa_conversation_id
        string status
        timestamp window_expires_at
    }

    whatsapp_messages {
        uuid id PK
        uuid conversation_id FK
        string wa_message_id UK
        string direction
        string message_type
        jsonb content
        string status
        timestamp sent_at
    }

    chatbot_flows {
        uuid id PK
        uuid tenant_id FK
        string name
        boolean is_active
        jsonb trigger_config
    }

    chatbot_nodes {
        uuid id PK
        uuid flow_id FK
        string node_type
        jsonb config
        jsonb connections
    }

    chatbot_sessions {
        uuid id PK
        uuid conversation_id FK
        uuid flow_id FK
        uuid current_node_id FK
        jsonb context
        string status
    }

---

Tablas

1. whatsapp_accounts

Cuentas de WhatsApp Business conectadas.

CREATE TABLE messaging.whatsapp_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- WhatsApp Business Account (WABA)
    waba_id VARCHAR(50) NOT NULL,
    phone_number_id VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    verified_name VARCHAR(100),

    -- Calidad y limites
    quality_rating VARCHAR(20) DEFAULT 'unknown',
    messaging_limit VARCHAR(20) DEFAULT 'tier_1',
    -- tier_1: 1K/day, tier_2: 10K/day, tier_3: 100K/day, unlimited

    -- Configuracion
    webhook_url VARCHAR(500),
    webhook_verify_token VARCHAR(100),

    -- Credenciales (encriptadas via integrations schema)
    access_token_encrypted BYTEA,
    token_expires_at TIMESTAMPTZ,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_active BOOLEAN NOT NULL DEFAULT true,
    last_synced_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT uq_whatsapp_accounts UNIQUE (tenant_id, phone_number),
    CONSTRAINT chk_whatsapp_accounts_status CHECK (status IN (
        'pending', 'connected', 'disconnected', 'suspended', 'error'
    )),
    CONSTRAINT chk_whatsapp_accounts_quality CHECK (quality_rating IN (
        'unknown', 'green', 'yellow', 'red'
    )),
    CONSTRAINT chk_whatsapp_accounts_phone CHECK (phone_number ~ '^\+[1-9]\d{6,14}$')
);

-- Indices
CREATE INDEX idx_whatsapp_accounts_tenant ON messaging.whatsapp_accounts(tenant_id);
CREATE INDEX idx_whatsapp_accounts_phone ON messaging.whatsapp_accounts(phone_number);
CREATE INDEX idx_whatsapp_accounts_active ON messaging.whatsapp_accounts(tenant_id, is_active)
    WHERE is_active = true;

-- Trigger para updated_at
CREATE TRIGGER trg_whatsapp_accounts_updated_at
    BEFORE UPDATE ON messaging.whatsapp_accounts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE messaging.whatsapp_accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY whatsapp_accounts_tenant_isolation ON messaging.whatsapp_accounts
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE messaging.whatsapp_accounts IS 'Cuentas de WhatsApp Business conectadas (MGN-017)';
COMMENT ON COLUMN messaging.whatsapp_accounts.waba_id IS 'WhatsApp Business Account ID de Meta';
COMMENT ON COLUMN messaging.whatsapp_accounts.quality_rating IS 'Calidad de la cuenta: green, yellow, red';

---

2. whatsapp_templates

Templates de mensajes aprobados por Meta.

CREATE TABLE messaging.whatsapp_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL REFERENCES messaging.whatsapp_accounts(id) ON DELETE CASCADE,

    -- Identificacion
    external_id VARCHAR(50),  -- ID en Meta
    name VARCHAR(100) NOT NULL,
    language VARCHAR(10) NOT NULL DEFAULT 'es',

    -- Categoria
    category VARCHAR(30) NOT NULL,
    -- MARKETING, UTILITY, AUTHENTICATION

    -- Componentes del template
    components JSONB NOT NULL,
    -- Ejemplo: [
    --   {"type": "HEADER", "format": "TEXT", "text": "Hola {{1}}"},
    --   {"type": "BODY", "text": "Tu pedido #{{1}} está en camino"},
    --   {"type": "FOOTER", "text": "Gracias por tu compra"},
    --   {"type": "BUTTONS", "buttons": [{"type": "QUICK_REPLY", "text": "OK"}]}
    -- ]

    -- Estado de aprobacion
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    rejection_reason VARCHAR(500),

    -- Metadata
    quality_score VARCHAR(20),

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMPTZ,
    approved_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT uq_whatsapp_templates UNIQUE (account_id, name, language),
    CONSTRAINT chk_whatsapp_templates_category CHECK (category IN (
        'MARKETING', 'UTILITY', 'AUTHENTICATION'
    )),
    CONSTRAINT chk_whatsapp_templates_status CHECK (status IN (
        'pending', 'approved', 'rejected', 'disabled', 'paused'
    ))
);

-- Indices
CREATE INDEX idx_whatsapp_templates_account ON messaging.whatsapp_templates(account_id);
CREATE INDEX idx_whatsapp_templates_status ON messaging.whatsapp_templates(status);
CREATE INDEX idx_whatsapp_templates_category ON messaging.whatsapp_templates(category);

-- Trigger para updated_at
CREATE TRIGGER trg_whatsapp_templates_updated_at
    BEFORE UPDATE ON messaging.whatsapp_templates
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE messaging.whatsapp_templates IS 'Templates de mensajes aprobados por Meta';
COMMENT ON COLUMN messaging.whatsapp_templates.components IS 'Estructura del template en formato Meta';

---

3. whatsapp_conversations

Conversaciones con contactos.

CREATE TABLE messaging.whatsapp_conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL REFERENCES messaging.whatsapp_accounts(id) ON DELETE CASCADE,
    contact_id UUID REFERENCES core_catalogs.contacts(id),

    -- WhatsApp IDs
    wa_conversation_id VARCHAR(50),
    contact_wa_id VARCHAR(50) NOT NULL,  -- Phone number o WA ID

    -- Asignacion
    assigned_user_id UUID REFERENCES core_users.users(id),
    assigned_at TIMESTAMPTZ,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    unread_count INT NOT NULL DEFAULT 0,

    -- Ventana de 24 horas
    window_expires_at TIMESTAMPTZ,
    is_window_open BOOLEAN NOT NULL DEFAULT false,

    -- Ultima actividad
    last_message_at TIMESTAMPTZ,
    last_message_preview VARCHAR(200),
    last_message_direction VARCHAR(10),

    -- Chatbot
    chatbot_active BOOLEAN NOT NULL DEFAULT false,
    chatbot_session_id UUID,

    -- Tags y categorias
    tags TEXT[] DEFAULT '{}',

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMPTZ,

    CONSTRAINT uq_whatsapp_conversations UNIQUE (account_id, contact_wa_id),
    CONSTRAINT chk_whatsapp_conversations_status CHECK (status IN (
        'open', 'pending', 'resolved', 'closed', 'spam'
    ))
);

-- Indices
CREATE INDEX idx_whatsapp_conversations_account ON messaging.whatsapp_conversations(account_id);
CREATE INDEX idx_whatsapp_conversations_contact ON messaging.whatsapp_conversations(contact_id);
CREATE INDEX idx_whatsapp_conversations_assigned ON messaging.whatsapp_conversations(assigned_user_id);
CREATE INDEX idx_whatsapp_conversations_status ON messaging.whatsapp_conversations(status);
CREATE INDEX idx_whatsapp_conversations_last_message ON messaging.whatsapp_conversations(last_message_at DESC);
CREATE INDEX idx_whatsapp_conversations_open ON messaging.whatsapp_conversations(account_id, status)
    WHERE status IN ('open', 'pending');

-- Trigger para updated_at
CREATE TRIGGER trg_whatsapp_conversations_updated_at
    BEFORE UPDATE ON messaging.whatsapp_conversations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE messaging.whatsapp_conversations IS 'Conversaciones de WhatsApp con contactos';
COMMENT ON COLUMN messaging.whatsapp_conversations.window_expires_at IS 'Expiracion de ventana de 24h para mensajes libres';

---

4. whatsapp_messages

Mensajes enviados y recibidos.

CREATE TABLE messaging.whatsapp_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES messaging.whatsapp_conversations(id) ON DELETE CASCADE,

    -- WhatsApp IDs
    wa_message_id VARCHAR(100) NOT NULL,
    wa_context_id VARCHAR(100),  -- Mensaje al que responde

    -- Direccion
    direction VARCHAR(10) NOT NULL,  -- inbound, outbound

    -- Tipo y contenido
    message_type VARCHAR(20) NOT NULL,
    -- text, image, document, audio, video, sticker, location, contacts, interactive, template
    content JSONB NOT NULL,
    -- Ejemplo text: {"body": "Hola!"}
    -- Ejemplo image: {"id": "...", "mime_type": "image/jpeg", "caption": "..."}
    -- Ejemplo template: {"name": "order_confirmation", "language": "es", "components": [...]}

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    error_code VARCHAR(50),
    error_message VARCHAR(500),

    -- Timestamps
    sent_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ,
    failed_at TIMESTAMPTZ,

    -- Metadata
    is_from_chatbot BOOLEAN NOT NULL DEFAULT false,
    is_from_ai BOOLEAN NOT NULL DEFAULT false,
    ai_agent_id UUID,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_whatsapp_messages UNIQUE (conversation_id, wa_message_id),
    CONSTRAINT chk_whatsapp_messages_direction CHECK (direction IN ('inbound', 'outbound')),
    CONSTRAINT chk_whatsapp_messages_type CHECK (message_type IN (
        'text', 'image', 'document', 'audio', 'video', 'sticker',
        'location', 'contacts', 'interactive', 'template', 'reaction', 'unknown'
    )),
    CONSTRAINT chk_whatsapp_messages_status CHECK (status IN (
        'pending', 'sent', 'delivered', 'read', 'failed'
    ))
);

-- Indices
CREATE INDEX idx_whatsapp_messages_conversation ON messaging.whatsapp_messages(conversation_id);
CREATE INDEX idx_whatsapp_messages_wa_id ON messaging.whatsapp_messages(wa_message_id);
CREATE INDEX idx_whatsapp_messages_direction ON messaging.whatsapp_messages(direction);
CREATE INDEX idx_whatsapp_messages_date ON messaging.whatsapp_messages(created_at DESC);
CREATE INDEX idx_whatsapp_messages_status ON messaging.whatsapp_messages(status)
    WHERE status IN ('pending', 'failed');

-- Particionamiento por mes (recomendado para alto volumen)
-- CREATE TABLE messaging.whatsapp_messages_y2025m01 PARTITION OF messaging.whatsapp_messages
--     FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Comentarios
COMMENT ON TABLE messaging.whatsapp_messages IS 'Mensajes de WhatsApp enviados y recibidos';
COMMENT ON COLUMN messaging.whatsapp_messages.content IS 'Contenido del mensaje en formato Meta';

---

5. chatbot_flows

Flujos de chatbot.

CREATE TABLE messaging.chatbot_flows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),

    -- Trigger
    trigger_type VARCHAR(30) NOT NULL,
    -- keyword, first_message, menu_option, always
    trigger_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Ejemplo keyword: {"keywords": ["hola", "menu", "ayuda"]}
    -- Ejemplo menu: {"option_id": "support"}

    -- Cuentas donde aplica
    account_ids UUID[],  -- NULL = todas las cuentas del tenant

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT false,
    is_draft BOOLEAN NOT NULL DEFAULT true,

    -- AI Integration (MGN-018)
    use_ai_fallback BOOLEAN NOT NULL DEFAULT false,
    ai_agent_id UUID,  -- FK a ai_agents.agents

    -- Estadisticas
    total_sessions INT NOT NULL DEFAULT 0,
    completed_sessions INT NOT NULL DEFAULT 0,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT uq_chatbot_flows UNIQUE (tenant_id, name),
    CONSTRAINT chk_chatbot_flows_trigger CHECK (trigger_type IN (
        'keyword', 'first_message', 'menu_option', 'always', 'scheduled', 'manual'
    ))
);

-- Indices
CREATE INDEX idx_chatbot_flows_tenant ON messaging.chatbot_flows(tenant_id);
CREATE INDEX idx_chatbot_flows_active ON messaging.chatbot_flows(tenant_id, is_active)
    WHERE is_active = true;
CREATE INDEX idx_chatbot_flows_trigger ON messaging.chatbot_flows(trigger_type);

-- Trigger para updated_at
CREATE TRIGGER trg_chatbot_flows_updated_at
    BEFORE UPDATE ON messaging.chatbot_flows
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE messaging.chatbot_flows ENABLE ROW LEVEL SECURITY;

CREATE POLICY chatbot_flows_tenant_isolation ON messaging.chatbot_flows
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE messaging.chatbot_flows IS 'Flujos de chatbot automatizado';
COMMENT ON COLUMN messaging.chatbot_flows.trigger_type IS 'Cuando se activa: keyword, first_message, menu_option, always';

---

6. chatbot_nodes

Nodos de flujos de chatbot.

CREATE TABLE messaging.chatbot_nodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    flow_id UUID NOT NULL REFERENCES messaging.chatbot_flows(id) ON DELETE CASCADE,

    -- Identificacion
    node_key VARCHAR(50) NOT NULL,  -- ID interno para referencias
    name VARCHAR(100),

    -- Tipo de nodo
    node_type VARCHAR(30) NOT NULL,
    -- send_message, ask_question, menu, condition, action, delay, handoff, ai_response

    -- Configuracion del nodo
    config JSONB NOT NULL,
    -- Ejemplo send_message: {"message": "Hola, bienvenido!", "type": "text"}
    -- Ejemplo ask_question: {"question": "Cual es tu email?", "variable": "email", "validation": "email"}
    -- Ejemplo menu: {"text": "Selecciona:", "options": [{"id": "1", "text": "Soporte"}, ...]}
    -- Ejemplo condition: {"variable": "email", "operator": "contains", "value": "@"}
    -- Ejemplo action: {"type": "create_lead", "params": {"source": "whatsapp"}}

    -- Conexiones a otros nodos
    connections JSONB NOT NULL DEFAULT '[]'::jsonb,
    -- Ejemplo: [{"from_output": "default", "to_node": "node_2"}, {"from_output": "option_1", "to_node": "node_3"}]

    -- Posicion visual (para editor)
    position JSONB DEFAULT '{"x": 0, "y": 0}'::jsonb,

    -- Flags
    is_start BOOLEAN NOT NULL DEFAULT false,
    is_end BOOLEAN NOT NULL DEFAULT false,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_chatbot_nodes UNIQUE (flow_id, node_key),
    CONSTRAINT chk_chatbot_nodes_type CHECK (node_type IN (
        'send_message', 'ask_question', 'menu', 'condition',
        'action', 'delay', 'handoff', 'ai_response', 'webhook'
    ))
);

-- Indices
CREATE INDEX idx_chatbot_nodes_flow ON messaging.chatbot_nodes(flow_id);
CREATE INDEX idx_chatbot_nodes_type ON messaging.chatbot_nodes(node_type);
CREATE INDEX idx_chatbot_nodes_start ON messaging.chatbot_nodes(flow_id, is_start)
    WHERE is_start = true;

-- Trigger para updated_at
CREATE TRIGGER trg_chatbot_nodes_updated_at
    BEFORE UPDATE ON messaging.chatbot_nodes
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE messaging.chatbot_nodes IS 'Nodos de flujos de chatbot';
COMMENT ON COLUMN messaging.chatbot_nodes.node_key IS 'Identificador interno para referencias entre nodos';

---

7. chatbot_sessions

Sesiones activas de chatbot.

CREATE TABLE messaging.chatbot_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES messaging.whatsapp_conversations(id) ON DELETE CASCADE,
    flow_id UUID NOT NULL REFERENCES messaging.chatbot_flows(id),

    -- Nodo actual
    current_node_id UUID REFERENCES messaging.chatbot_nodes(id),
    waiting_for_response BOOLEAN NOT NULL DEFAULT false,

    -- Contexto y variables
    context JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Ejemplo: {"name": "Juan", "email": "juan@email.com", "order_number": "12345"}

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    completed_nodes TEXT[] DEFAULT '{}',

    -- Estadisticas
    messages_processed INT NOT NULL DEFAULT 0,

    started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ,
    timeout_at TIMESTAMPTZ,

    CONSTRAINT chk_chatbot_sessions_status CHECK (status IN (
        'active', 'waiting', 'completed', 'handoff', 'timeout', 'error'
    ))
);

-- Indices
CREATE INDEX idx_chatbot_sessions_conversation ON messaging.chatbot_sessions(conversation_id);
CREATE INDEX idx_chatbot_sessions_flow ON messaging.chatbot_sessions(flow_id);
CREATE INDEX idx_chatbot_sessions_status ON messaging.chatbot_sessions(status);
CREATE INDEX idx_chatbot_sessions_active ON messaging.chatbot_sessions(conversation_id, status)
    WHERE status IN ('active', 'waiting');

-- Comentarios
COMMENT ON TABLE messaging.chatbot_sessions IS 'Sesiones activas de chatbot por conversacion';
COMMENT ON COLUMN messaging.chatbot_sessions.context IS 'Variables recopiladas durante el flujo';

---

8. whatsapp_campaigns

Campanas de marketing masivo.

CREATE TABLE messaging.whatsapp_campaigns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES messaging.whatsapp_accounts(id),

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),

    -- Template a usar
    template_id UUID NOT NULL REFERENCES messaging.whatsapp_templates(id),
    template_params JSONB DEFAULT '{}'::jsonb,
    -- Mapeo de variables: {"1": "contact.name", "2": "custom.promo_code"}

    -- Audiencia
    audience_type VARCHAR(30) NOT NULL,
    -- all_contacts, tagged, segment, custom_list
    audience_config JSONB NOT NULL,
    -- Ejemplo tagged: {"tags": ["vip", "newsletter"]}
    -- Ejemplo segment: {"filter": {"country": "MX", "last_order_days": 30}}

    -- Programacion
    scheduled_at TIMESTAMPTZ,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'draft',

    -- Estadisticas
    total_recipients INT NOT NULL DEFAULT 0,
    sent_count INT NOT NULL DEFAULT 0,
    delivered_count INT NOT NULL DEFAULT 0,
    read_count INT NOT NULL DEFAULT 0,
    failed_count INT NOT NULL DEFAULT 0,
    opt_out_count INT NOT NULL DEFAULT 0,

    -- Costo estimado
    estimated_cost DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'MXN',

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT chk_whatsapp_campaigns_status CHECK (status IN (
        'draft', 'scheduled', 'sending', 'completed', 'paused', 'cancelled'
    )),
    CONSTRAINT chk_whatsapp_campaigns_audience CHECK (audience_type IN (
        'all_contacts', 'tagged', 'segment', 'custom_list'
    ))
);

-- Indices
CREATE INDEX idx_whatsapp_campaigns_tenant ON messaging.whatsapp_campaigns(tenant_id);
CREATE INDEX idx_whatsapp_campaigns_account ON messaging.whatsapp_campaigns(account_id);
CREATE INDEX idx_whatsapp_campaigns_status ON messaging.whatsapp_campaigns(status);
CREATE INDEX idx_whatsapp_campaigns_scheduled ON messaging.whatsapp_campaigns(scheduled_at)
    WHERE status = 'scheduled';

-- RLS
ALTER TABLE messaging.whatsapp_campaigns ENABLE ROW LEVEL SECURITY;

CREATE POLICY whatsapp_campaigns_tenant_isolation ON messaging.whatsapp_campaigns
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE messaging.whatsapp_campaigns IS 'Campanas de marketing masivo por WhatsApp';

---

Funciones de Utilidad

Procesar Mensaje Entrante

CREATE OR REPLACE FUNCTION messaging.process_incoming_message(
    p_account_id UUID,
    p_wa_message_id VARCHAR,
    p_from_number VARCHAR,
    p_message_type VARCHAR,
    p_content JSONB
) RETURNS UUID AS $$
DECLARE
    v_conversation_id UUID;
    v_message_id UUID;
    v_contact_id UUID;
BEGIN
    -- Buscar o crear conversacion
    SELECT id, contact_id INTO v_conversation_id, v_contact_id
    FROM messaging.whatsapp_conversations
    WHERE account_id = p_account_id AND contact_wa_id = p_from_number;

    IF v_conversation_id IS NULL THEN
        -- Buscar contacto existente
        SELECT c.id INTO v_contact_id
        FROM core_catalogs.contacts c
        JOIN messaging.whatsapp_accounts wa ON wa.tenant_id = c.tenant_id
        WHERE wa.id = p_account_id
          AND c.whatsapp_number = p_from_number
          AND c.deleted_at IS NULL
        LIMIT 1;

        -- Crear conversacion
        INSERT INTO messaging.whatsapp_conversations (
            account_id, contact_id, contact_wa_id, status,
            is_window_open, window_expires_at
        ) VALUES (
            p_account_id, v_contact_id, p_from_number, 'open',
            true, NOW() + INTERVAL '24 hours'
        )
        RETURNING id INTO v_conversation_id;
    ELSE
        -- Actualizar ventana
        UPDATE messaging.whatsapp_conversations
        SET is_window_open = true,
            window_expires_at = NOW() + INTERVAL '24 hours',
            updated_at = NOW()
        WHERE id = v_conversation_id;
    END IF;

    -- Insertar mensaje
    INSERT INTO messaging.whatsapp_messages (
        conversation_id, wa_message_id, direction,
        message_type, content, status, sent_at
    ) VALUES (
        v_conversation_id, p_wa_message_id, 'inbound',
        p_message_type, p_content, 'delivered', NOW()
    )
    RETURNING id INTO v_message_id;

    -- Actualizar conversacion
    UPDATE messaging.whatsapp_conversations
    SET last_message_at = NOW(),
        last_message_preview = LEFT(p_content->>'body', 200),
        last_message_direction = 'inbound',
        unread_count = unread_count + 1,
        updated_at = NOW()
    WHERE id = v_conversation_id;

    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

Verificar Ventana de Conversacion

CREATE OR REPLACE FUNCTION messaging.is_window_open(
    p_conversation_id UUID
) RETURNS BOOLEAN AS $$
DECLARE
    v_window_open BOOLEAN;
    v_window_expires TIMESTAMPTZ;
BEGIN
    SELECT is_window_open, window_expires_at
    INTO v_window_open, v_window_expires
    FROM messaging.whatsapp_conversations
    WHERE id = p_conversation_id;

    IF v_window_expires IS NOT NULL AND v_window_expires < NOW() THEN
        -- Ventana expirada, actualizar
        UPDATE messaging.whatsapp_conversations
        SET is_window_open = false
        WHERE id = p_conversation_id;
        RETURN false;
    END IF;

    RETURN COALESCE(v_window_open, false);
END;
$$ LANGUAGE plpgsql;

---

Vistas

Vista: Inbox de Conversaciones

CREATE VIEW messaging.vw_inbox AS
SELECT
    c.id,
    c.account_id,
    wa.phone_number AS account_phone,
    c.contact_id,
    ct.name AS contact_name,
    ct.whatsapp_number AS contact_phone,
    c.assigned_user_id,
    u.full_name AS assigned_user_name,
    c.status,
    c.unread_count,
    c.is_window_open,
    c.window_expires_at,
    c.last_message_at,
    c.last_message_preview,
    c.last_message_direction,
    c.chatbot_active,
    c.tags,
    c.created_at
FROM messaging.whatsapp_conversations c
JOIN messaging.whatsapp_accounts wa ON wa.id = c.account_id
LEFT JOIN core_catalogs.contacts ct ON ct.id = c.contact_id
LEFT JOIN core_users.users u ON u.id = c.assigned_user_id
WHERE c.status NOT IN ('closed', 'spam')
ORDER BY c.last_message_at DESC NULLS LAST;

Vista: Metricas de WhatsApp

CREATE VIEW messaging.vw_whatsapp_metrics AS
SELECT
    wa.tenant_id,
    wa.id AS account_id,
    wa.phone_number,
    wa.display_name,
    wa.quality_rating,
    (SELECT COUNT(*) FROM messaging.whatsapp_conversations c
     WHERE c.account_id = wa.id AND c.created_at > CURRENT_DATE - 30) AS conversations_30d,
    (SELECT COUNT(*) FROM messaging.whatsapp_messages m
     JOIN messaging.whatsapp_conversations c ON c.id = m.conversation_id
     WHERE c.account_id = wa.id AND m.direction = 'outbound' AND m.created_at > CURRENT_DATE - 30) AS messages_sent_30d,
    (SELECT COUNT(*) FROM messaging.whatsapp_messages m
     JOIN messaging.whatsapp_conversations c ON c.id = m.conversation_id
     WHERE c.account_id = wa.id AND m.direction = 'inbound' AND m.created_at > CURRENT_DATE - 30) AS messages_received_30d,
    (SELECT COUNT(*) FROM messaging.whatsapp_conversations c
     WHERE c.account_id = wa.id AND c.status = 'open') AS open_conversations
FROM messaging.whatsapp_accounts wa
WHERE wa.is_active = true;

---

Resumen de Tablas

Tabla	Columnas	Descripcion
whatsapp_accounts	20	Cuentas de WhatsApp Business
whatsapp_templates	15	Templates aprobados por Meta
whatsapp_conversations	20	Conversaciones con contactos
whatsapp_messages	18	Mensajes enviados/recibidos
chatbot_flows	17	Flujos de chatbot
chatbot_nodes	13	Nodos de flujos
chatbot_sessions	12	Sesiones activas
whatsapp_campaigns	21	Campanas de marketing

Total: 8 tablas, 136 columnas

---

Historial

Version	Fecha	Autor	Cambios
1.0	2025-12-05	System	Creacion inicial (MGN-017)