erp-core-database/ddl/15-whatsapp.sql

-- =============================================================
-- ARCHIVO: 15-whatsapp.sql
-- DESCRIPCION: Integracion WhatsApp Business API, mensajes, templates
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-10
-- EPIC: SAAS-WHATSAPP (EPIC-SAAS-008)
-- HISTORIAS: US-090, US-091, US-092
-- =============================================================

-- =====================
-- SCHEMA: whatsapp
-- =====================
CREATE SCHEMA IF NOT EXISTS whatsapp;

-- =====================
-- TABLA: whatsapp.accounts
-- Cuentas de WhatsApp Business configuradas
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Identificación de la cuenta
    name VARCHAR(200) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    phone_number_id VARCHAR(50) NOT NULL, -- ID en Meta
    business_account_id VARCHAR(50) NOT NULL, -- WABA ID

    -- Configuración de API
    access_token TEXT, -- Encriptado
    webhook_verify_token VARCHAR(255),
    webhook_secret VARCHAR(255),

    -- Perfil de negocio
    business_name VARCHAR(200),
    business_description TEXT,
    business_category VARCHAR(100),
    business_website TEXT,
    profile_picture_url TEXT,

    -- Configuración
    default_language VARCHAR(10) DEFAULT 'es_MX',
    auto_reply_enabled BOOLEAN DEFAULT FALSE,
    auto_reply_message TEXT,
    business_hours JSONB DEFAULT '{}',
    -- {monday: {start: "09:00", end: "18:00"}, ...}

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, active, suspended, disconnected
    verified_at TIMESTAMPTZ,

    -- Límites
    daily_message_limit INTEGER DEFAULT 1000,
    messages_sent_today INTEGER DEFAULT 0,
    last_limit_reset TIMESTAMPTZ,

    -- Estadísticas
    total_messages_sent BIGINT DEFAULT 0,
    total_messages_received BIGINT DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),

    UNIQUE(tenant_id, phone_number)
);

-- =====================
-- TABLA: whatsapp.templates
-- Templates aprobados por Meta
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Identificación
    name VARCHAR(512) NOT NULL, -- Nombre en Meta (snake_case)
    display_name VARCHAR(200) NOT NULL, -- Nombre legible
    description TEXT,

    -- Categoría (requerida por Meta)
    category VARCHAR(30) NOT NULL, -- MARKETING, UTILITY, AUTHENTICATION

    -- Idioma
    language VARCHAR(10) NOT NULL DEFAULT 'es_MX',

    -- Componentes del template
    header_type VARCHAR(20), -- TEXT, IMAGE, VIDEO, DOCUMENT
    header_text TEXT,
    header_media_url TEXT,

    body_text TEXT NOT NULL,
    body_variables TEXT[] DEFAULT '{}', -- {{1}}, {{2}}, etc.

    footer_text VARCHAR(60),

    -- Botones
    buttons JSONB DEFAULT '[]',
    -- [{type: "QUICK_REPLY", text: "Sí"}, {type: "URL", text: "Ver más", url: "..."}]

    -- Estado en Meta
    meta_template_id VARCHAR(50),
    meta_status VARCHAR(20) DEFAULT 'PENDING',
    -- PENDING, APPROVED, REJECTED, PAUSED, DISABLED
    rejection_reason TEXT,

    -- Uso
    is_active BOOLEAN DEFAULT TRUE,
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMPTZ,

    -- Versionamiento
    version INTEGER DEFAULT 1,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMPTZ,
    approved_at TIMESTAMPTZ,

    UNIQUE(account_id, name, language)
);

-- =====================
-- TABLA: whatsapp.contacts
-- Contactos de WhatsApp
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,

    -- Número de teléfono
    phone_number VARCHAR(20) NOT NULL,
    wa_id VARCHAR(50), -- WhatsApp ID

    -- Perfil (obtenido de WhatsApp)
    profile_name VARCHAR(200),
    profile_picture_url TEXT,

    -- Asociación con entidades
    customer_id UUID,
    user_id UUID REFERENCES auth.users(id),

    -- Estado de conversación
    conversation_status VARCHAR(20) DEFAULT 'active',
    -- active, waiting, resolved, blocked
    last_message_at TIMESTAMPTZ,
    last_message_direction VARCHAR(10), -- inbound, outbound

    -- Ventana de 24 horas
    conversation_window_expires_at TIMESTAMPTZ,
    can_send_template_only BOOLEAN DEFAULT TRUE,

    -- Opt-in/Opt-out
    opted_in BOOLEAN DEFAULT FALSE,
    opted_in_at TIMESTAMPTZ,
    opted_out BOOLEAN DEFAULT FALSE,
    opted_out_at TIMESTAMPTZ,

    -- Tags y categorización
    tags TEXT[] DEFAULT '{}',
    notes TEXT,

    -- Estadísticas
    total_messages_sent INTEGER DEFAULT 0,
    total_messages_received INTEGER DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(account_id, phone_number)
);

-- =====================
-- TABLA: whatsapp.conversations
-- Conversaciones (hilos de chat)
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,
    contact_id UUID NOT NULL REFERENCES whatsapp.contacts(id) ON DELETE CASCADE,

    -- Estado
    status VARCHAR(20) DEFAULT 'open', -- open, pending, resolved, closed
    priority VARCHAR(20) DEFAULT 'normal', -- low, normal, high, urgent

    -- Asignación
    assigned_to UUID REFERENCES auth.users(id),
    assigned_at TIMESTAMPTZ,
    team_id UUID,

    -- Categorización
    category VARCHAR(50),
    tags TEXT[] DEFAULT '{}',

    -- Contexto
    context_type VARCHAR(50), -- support, sales, order, general
    context_id UUID,

    -- Tiempos
    first_response_at TIMESTAMPTZ,
    resolved_at TIMESTAMPTZ,

    -- Estadísticas
    message_count INTEGER DEFAULT 0,
    unread_count INTEGER DEFAULT 0,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- TABLA: whatsapp.messages
-- Mensajes enviados y recibidos
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,
    contact_id UUID NOT NULL REFERENCES whatsapp.contacts(id) ON DELETE CASCADE,
    conversation_id UUID REFERENCES whatsapp.conversations(id),

    -- Identificadores de Meta
    wa_message_id VARCHAR(100), -- ID del mensaje en WhatsApp
    wa_conversation_id VARCHAR(100), -- ID de conversación en Meta

    -- Dirección
    direction VARCHAR(10) NOT NULL, -- inbound, outbound

    -- Tipo de mensaje
    message_type VARCHAR(20) NOT NULL,
    -- text, image, video, audio, document, sticker, location, contacts, interactive, template, reaction

    -- Contenido
    content TEXT,
    caption TEXT,

    -- Media
    media_id VARCHAR(100),
    media_url TEXT,
    media_mime_type VARCHAR(100),
    media_sha256 VARCHAR(64),
    media_size_bytes INTEGER,

    -- Template (si aplica)
    template_id UUID REFERENCES whatsapp.templates(id),
    template_name VARCHAR(512),
    template_variables JSONB DEFAULT '[]',

    -- Interactivo (si aplica)
    interactive_type VARCHAR(30), -- button, list, product, product_list
    interactive_data JSONB DEFAULT '{}',

    -- Contexto (respuesta a otro mensaje)
    context_message_id VARCHAR(100),
    quoted_message_id UUID REFERENCES whatsapp.messages(id),

    -- Estado del mensaje
    status VARCHAR(20) DEFAULT 'pending',
    -- pending, sent, delivered, read, failed
    status_updated_at TIMESTAMPTZ,

    -- Error (si falló)
    error_code VARCHAR(20),
    error_message TEXT,

    -- Costos (para mensajes de template)
    is_billable BOOLEAN DEFAULT FALSE,
    cost_category VARCHAR(30), -- utility, authentication, marketing

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ
);

-- =====================
-- TABLA: whatsapp.message_status_updates
-- Actualizaciones de estado de mensajes
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.message_status_updates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id UUID NOT NULL REFERENCES whatsapp.messages(id) ON DELETE CASCADE,

    -- Estado
    status VARCHAR(20) NOT NULL,
    previous_status VARCHAR(20),

    -- Error (si aplica)
    error_code VARCHAR(20),
    error_title VARCHAR(200),
    error_message TEXT,

    -- Timestamp de Meta
    meta_timestamp TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- =====================
-- TABLA: whatsapp.quick_replies
-- Respuestas rápidas predefinidas
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.quick_replies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,

    -- Identificación
    shortcut VARCHAR(50) NOT NULL, -- /gracias, /horario
    title VARCHAR(200) NOT NULL,
    category VARCHAR(50),

    -- Contenido
    message_type VARCHAR(20) DEFAULT 'text',
    content TEXT NOT NULL,
    media_url TEXT,

    -- Uso
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMPTZ,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),

    UNIQUE(tenant_id, shortcut)
);

-- =====================
-- TABLA: whatsapp.automations
-- Automatizaciones de WhatsApp
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.automations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,

    -- Identificación
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Trigger
    trigger_type VARCHAR(30) NOT NULL,
    -- keyword, first_message, after_hours, no_response, webhook
    trigger_config JSONB NOT NULL DEFAULT '{}',
    -- keyword: {keywords: ["hola", "info"]}
    -- after_hours: {message: "..."}
    -- no_response: {delay_minutes: 30}

    -- Acción
    action_type VARCHAR(30) NOT NULL,
    -- send_message, send_template, assign_agent, add_tag, create_ticket
    action_config JSONB NOT NULL DEFAULT '{}',

    -- Condiciones adicionales
    conditions JSONB DEFAULT '[]',

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    priority INTEGER DEFAULT 0,

    -- Estadísticas
    trigger_count INTEGER DEFAULT 0,
    last_triggered_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id)
);

-- =====================
-- TABLA: whatsapp.broadcasts
-- Envíos masivos de mensajes
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.broadcasts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES whatsapp.accounts(id) ON DELETE CASCADE,

    -- Identificación
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Template a usar
    template_id UUID NOT NULL REFERENCES whatsapp.templates(id),

    -- Audiencia
    audience_type VARCHAR(30) NOT NULL, -- all, segment, custom, file
    audience_filter JSONB DEFAULT '{}',
    recipient_count INTEGER DEFAULT 0,

    -- Programación
    scheduled_at TIMESTAMPTZ,
    timezone VARCHAR(50) DEFAULT 'America/Mexico_City',

    -- Estado
    status VARCHAR(20) DEFAULT 'draft',
    -- draft, scheduled, sending, completed, cancelled, failed

    -- Progreso
    sent_count INTEGER DEFAULT 0,
    delivered_count INTEGER DEFAULT 0,
    read_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,
    reply_count INTEGER DEFAULT 0,

    -- Tiempos
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Costos estimados
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id)
);

-- =====================
-- TABLA: whatsapp.broadcast_recipients
-- Destinatarios de broadcasts
-- =====================
CREATE TABLE IF NOT EXISTS whatsapp.broadcast_recipients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    broadcast_id UUID NOT NULL REFERENCES whatsapp.broadcasts(id) ON DELETE CASCADE,
    contact_id UUID NOT NULL REFERENCES whatsapp.contacts(id) ON DELETE CASCADE,

    -- Variables del template
    template_variables JSONB DEFAULT '[]',

    -- Estado
    status VARCHAR(20) DEFAULT 'pending',
    -- pending, sent, delivered, read, failed

    -- Mensaje enviado
    message_id UUID REFERENCES whatsapp.messages(id),

    -- Error
    error_code VARCHAR(20),
    error_message TEXT,

    -- Tiempos
    sent_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(broadcast_id, contact_id)
);

-- =====================
-- INDICES
-- =====================

-- Indices para accounts
CREATE INDEX IF NOT EXISTS idx_wa_accounts_tenant ON whatsapp.accounts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_accounts_phone ON whatsapp.accounts(phone_number);
CREATE INDEX IF NOT EXISTS idx_wa_accounts_status ON whatsapp.accounts(status);

-- Indices para templates
CREATE INDEX IF NOT EXISTS idx_wa_templates_account ON whatsapp.templates(account_id);
CREATE INDEX IF NOT EXISTS idx_wa_templates_tenant ON whatsapp.templates(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_templates_status ON whatsapp.templates(meta_status);
CREATE INDEX IF NOT EXISTS idx_wa_templates_category ON whatsapp.templates(category);

-- Indices para contacts
CREATE INDEX IF NOT EXISTS idx_wa_contacts_tenant ON whatsapp.contacts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_account ON whatsapp.contacts(account_id);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_phone ON whatsapp.contacts(phone_number);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_tags ON whatsapp.contacts USING GIN(tags);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_opted_in ON whatsapp.contacts(opted_in) WHERE opted_in = TRUE;

-- Indices para conversations
CREATE INDEX IF NOT EXISTS idx_wa_convos_tenant ON whatsapp.conversations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_convos_contact ON whatsapp.conversations(contact_id);
CREATE INDEX IF NOT EXISTS idx_wa_convos_status ON whatsapp.conversations(status);
CREATE INDEX IF NOT EXISTS idx_wa_convos_assigned ON whatsapp.conversations(assigned_to);
CREATE INDEX IF NOT EXISTS idx_wa_convos_created ON whatsapp.conversations(created_at DESC);

-- Indices para messages
CREATE INDEX IF NOT EXISTS idx_wa_messages_tenant ON whatsapp.messages(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_messages_account ON whatsapp.messages(account_id);
CREATE INDEX IF NOT EXISTS idx_wa_messages_contact ON whatsapp.messages(contact_id);
CREATE INDEX IF NOT EXISTS idx_wa_messages_conversation ON whatsapp.messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_wa_messages_wa_id ON whatsapp.messages(wa_message_id);
CREATE INDEX IF NOT EXISTS idx_wa_messages_status ON whatsapp.messages(status);
CREATE INDEX IF NOT EXISTS idx_wa_messages_created ON whatsapp.messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_wa_messages_direction ON whatsapp.messages(direction);

-- Indices para status updates
CREATE INDEX IF NOT EXISTS idx_wa_status_message ON whatsapp.message_status_updates(message_id);

-- Indices para quick_replies
CREATE INDEX IF NOT EXISTS idx_wa_quick_replies_tenant ON whatsapp.quick_replies(tenant_id);
CREATE INDEX IF NOT EXISTS idx_wa_quick_replies_shortcut ON whatsapp.quick_replies(shortcut);

-- Indices para automations
CREATE INDEX IF NOT EXISTS idx_wa_automations_account ON whatsapp.automations(account_id);
CREATE INDEX IF NOT EXISTS idx_wa_automations_active ON whatsapp.automations(is_active) WHERE is_active = TRUE;

-- Indices para broadcasts
CREATE INDEX IF NOT EXISTS idx_wa_broadcasts_account ON whatsapp.broadcasts(account_id);
CREATE INDEX IF NOT EXISTS idx_wa_broadcasts_status ON whatsapp.broadcasts(status);
CREATE INDEX IF NOT EXISTS idx_wa_broadcasts_scheduled ON whatsapp.broadcasts(scheduled_at)
    WHERE status = 'scheduled';

-- Indices para broadcast_recipients
CREATE INDEX IF NOT EXISTS idx_wa_bcast_recip_broadcast ON whatsapp.broadcast_recipients(broadcast_id);
CREATE INDEX IF NOT EXISTS idx_wa_bcast_recip_status ON whatsapp.broadcast_recipients(status);

-- =====================
-- RLS POLICIES
-- =====================

ALTER TABLE whatsapp.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_accounts ON whatsapp.accounts
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_templates ON whatsapp.templates
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.contacts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_contacts ON whatsapp.contacts
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.conversations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_convos ON whatsapp.conversations
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_messages ON whatsapp.messages
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.quick_replies ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_quick ON whatsapp.quick_replies
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.automations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_auto ON whatsapp.automations
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

ALTER TABLE whatsapp.broadcasts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_wa_broadcasts ON whatsapp.broadcasts
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- =====================
-- FUNCIONES
-- =====================

-- Función para obtener o crear contacto
CREATE OR REPLACE FUNCTION whatsapp.get_or_create_contact(
    p_account_id UUID,
    p_phone_number VARCHAR(20),
    p_profile_name VARCHAR(200) DEFAULT NULL,
    p_wa_id VARCHAR(50) DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_contact_id UUID;
    v_tenant_id UUID;
BEGIN
    -- Obtener tenant de la cuenta
    SELECT tenant_id INTO v_tenant_id
    FROM whatsapp.accounts WHERE id = p_account_id;

    -- Buscar contacto existente
    SELECT id INTO v_contact_id
    FROM whatsapp.contacts
    WHERE account_id = p_account_id AND phone_number = p_phone_number;

    IF FOUND THEN
        -- Actualizar perfil si hay nuevos datos
        IF p_profile_name IS NOT NULL OR p_wa_id IS NOT NULL THEN
            UPDATE whatsapp.contacts
            SET
                profile_name = COALESCE(p_profile_name, profile_name),
                wa_id = COALESCE(p_wa_id, wa_id),
                updated_at = CURRENT_TIMESTAMP
            WHERE id = v_contact_id;
        END IF;
        RETURN v_contact_id;
    END IF;

    -- Crear nuevo contacto
    INSERT INTO whatsapp.contacts (
        tenant_id, account_id, phone_number, profile_name, wa_id
    ) VALUES (
        v_tenant_id, p_account_id, p_phone_number, p_profile_name, p_wa_id
    ) RETURNING id INTO v_contact_id;

    RETURN v_contact_id;
END;
$$ LANGUAGE plpgsql;

-- Función para crear o obtener conversación activa
CREATE OR REPLACE FUNCTION whatsapp.get_active_conversation(
    p_contact_id UUID
)
RETURNS UUID AS $$
DECLARE
    v_conversation_id UUID;
    v_contact RECORD;
BEGIN
    -- Obtener datos del contacto
    SELECT * INTO v_contact FROM whatsapp.contacts WHERE id = p_contact_id;

    -- Buscar conversación abierta
    SELECT id INTO v_conversation_id
    FROM whatsapp.conversations
    WHERE contact_id = p_contact_id
    AND status IN ('open', 'pending')
    ORDER BY created_at DESC
    LIMIT 1;

    IF FOUND THEN
        RETURN v_conversation_id;
    END IF;

    -- Crear nueva conversación
    INSERT INTO whatsapp.conversations (
        tenant_id, account_id, contact_id
    ) VALUES (
        v_contact.tenant_id, v_contact.account_id, p_contact_id
    ) RETURNING id INTO v_conversation_id;

    RETURN v_conversation_id;
END;
$$ LANGUAGE plpgsql;

-- Función para registrar mensaje entrante
CREATE OR REPLACE FUNCTION whatsapp.receive_message(
    p_account_id UUID,
    p_phone_number VARCHAR(20),
    p_wa_message_id VARCHAR(100),
    p_message_type VARCHAR(20),
    p_content TEXT,
    p_media_id VARCHAR(100) DEFAULT NULL,
    p_media_url TEXT DEFAULT NULL,
    p_context_message_id VARCHAR(100) DEFAULT NULL,
    p_profile_name VARCHAR(200) DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_message_id UUID;
    v_contact_id UUID;
    v_conversation_id UUID;
    v_tenant_id UUID;
BEGIN
    -- Obtener tenant
    SELECT tenant_id INTO v_tenant_id
    FROM whatsapp.accounts WHERE id = p_account_id;

    -- Obtener o crear contacto
    v_contact_id := whatsapp.get_or_create_contact(
        p_account_id, p_phone_number, p_profile_name
    );

    -- Actualizar ventana de conversación
    UPDATE whatsapp.contacts
    SET
        conversation_window_expires_at = CURRENT_TIMESTAMP + INTERVAL '24 hours',
        can_send_template_only = FALSE,
        last_message_at = CURRENT_TIMESTAMP,
        last_message_direction = 'inbound',
        total_messages_received = total_messages_received + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = v_contact_id;

    -- Obtener conversación activa
    v_conversation_id := whatsapp.get_active_conversation(v_contact_id);

    -- Crear mensaje
    INSERT INTO whatsapp.messages (
        tenant_id, account_id, contact_id, conversation_id,
        wa_message_id, direction, message_type, content,
        media_id, media_url, context_message_id, status
    ) VALUES (
        v_tenant_id, p_account_id, v_contact_id, v_conversation_id,
        p_wa_message_id, 'inbound', p_message_type, p_content,
        p_media_id, p_media_url, p_context_message_id, 'received'
    ) RETURNING id INTO v_message_id;

    -- Actualizar conversación
    UPDATE whatsapp.conversations
    SET
        message_count = message_count + 1,
        unread_count = unread_count + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = v_conversation_id;

    -- Actualizar estadísticas de la cuenta
    UPDATE whatsapp.accounts
    SET
        total_messages_received = total_messages_received + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_account_id;

    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

-- Función para enviar mensaje de texto
CREATE OR REPLACE FUNCTION whatsapp.send_text_message(
    p_account_id UUID,
    p_contact_id UUID,
    p_content TEXT,
    p_sent_by UUID DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_message_id UUID;
    v_conversation_id UUID;
    v_contact RECORD;
    v_tenant_id UUID;
BEGIN
    -- Obtener contacto
    SELECT * INTO v_contact FROM whatsapp.contacts WHERE id = p_contact_id;
    v_tenant_id := v_contact.tenant_id;

    -- Verificar ventana de conversación
    IF v_contact.can_send_template_only AND
       (v_contact.conversation_window_expires_at IS NULL OR
        v_contact.conversation_window_expires_at < CURRENT_TIMESTAMP) THEN
        RAISE EXCEPTION 'Cannot send text message outside conversation window. Use template.';
    END IF;

    -- Obtener conversación
    v_conversation_id := whatsapp.get_active_conversation(p_contact_id);

    -- Crear mensaje
    INSERT INTO whatsapp.messages (
        tenant_id, account_id, contact_id, conversation_id,
        direction, message_type, content, status
    ) VALUES (
        v_tenant_id, p_account_id, p_contact_id, v_conversation_id,
        'outbound', 'text', p_content, 'pending'
    ) RETURNING id INTO v_message_id;

    -- Actualizar contacto
    UPDATE whatsapp.contacts
    SET
        last_message_at = CURRENT_TIMESTAMP,
        last_message_direction = 'outbound',
        total_messages_sent = total_messages_sent + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_contact_id;

    -- Actualizar conversación
    UPDATE whatsapp.conversations
    SET
        message_count = message_count + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = v_conversation_id;

    -- Actualizar cuenta
    UPDATE whatsapp.accounts
    SET
        total_messages_sent = total_messages_sent + 1,
        messages_sent_today = messages_sent_today + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_account_id;

    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

-- Función para enviar template
CREATE OR REPLACE FUNCTION whatsapp.send_template_message(
    p_account_id UUID,
    p_contact_id UUID,
    p_template_id UUID,
    p_variables JSONB DEFAULT '[]'
)
RETURNS UUID AS $$
DECLARE
    v_message_id UUID;
    v_conversation_id UUID;
    v_template RECORD;
    v_contact RECORD;
    v_tenant_id UUID;
BEGIN
    -- Obtener template
    SELECT * INTO v_template FROM whatsapp.templates WHERE id = p_template_id;
    IF NOT FOUND OR v_template.meta_status != 'APPROVED' THEN
        RAISE EXCEPTION 'Template not found or not approved';
    END IF;

    -- Obtener contacto
    SELECT * INTO v_contact FROM whatsapp.contacts WHERE id = p_contact_id;
    v_tenant_id := v_contact.tenant_id;

    -- Obtener conversación
    v_conversation_id := whatsapp.get_active_conversation(p_contact_id);

    -- Crear mensaje
    INSERT INTO whatsapp.messages (
        tenant_id, account_id, contact_id, conversation_id,
        direction, message_type, content,
        template_id, template_name, template_variables,
        is_billable, cost_category, status
    ) VALUES (
        v_tenant_id, p_account_id, p_contact_id, v_conversation_id,
        'outbound', 'template', v_template.body_text,
        p_template_id, v_template.name, p_variables,
        TRUE, v_template.category, 'pending'
    ) RETURNING id INTO v_message_id;

    -- Actualizar uso del template
    UPDATE whatsapp.templates
    SET usage_count = usage_count + 1, last_used_at = CURRENT_TIMESTAMP
    WHERE id = p_template_id;

    -- Actualizar contacto
    UPDATE whatsapp.contacts
    SET
        last_message_at = CURRENT_TIMESTAMP,
        last_message_direction = 'outbound',
        total_messages_sent = total_messages_sent + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_contact_id;

    -- Actualizar cuenta
    UPDATE whatsapp.accounts
    SET
        total_messages_sent = total_messages_sent + 1,
        messages_sent_today = messages_sent_today + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_account_id;

    RETURN v_message_id;
END;
$$ LANGUAGE plpgsql;

-- Función para actualizar estado de mensaje
CREATE OR REPLACE FUNCTION whatsapp.update_message_status(
    p_wa_message_id VARCHAR(100),
    p_status VARCHAR(20),
    p_meta_timestamp TIMESTAMPTZ DEFAULT NULL,
    p_error_code VARCHAR(20) DEFAULT NULL,
    p_error_message TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    v_message RECORD;
BEGIN
    SELECT * INTO v_message
    FROM whatsapp.messages
    WHERE wa_message_id = p_wa_message_id;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    -- Registrar actualización de estado
    INSERT INTO whatsapp.message_status_updates (
        message_id, status, previous_status, error_code, error_message, meta_timestamp
    ) VALUES (
        v_message.id, p_status, v_message.status, p_error_code, p_error_message, p_meta_timestamp
    );

    -- Actualizar mensaje
    UPDATE whatsapp.messages
    SET
        status = p_status,
        status_updated_at = CURRENT_TIMESTAMP,
        sent_at = CASE WHEN p_status = 'sent' AND sent_at IS NULL THEN COALESCE(p_meta_timestamp, CURRENT_TIMESTAMP) ELSE sent_at END,
        delivered_at = CASE WHEN p_status = 'delivered' THEN COALESCE(p_meta_timestamp, CURRENT_TIMESTAMP) ELSE delivered_at END,
        read_at = CASE WHEN p_status = 'read' THEN COALESCE(p_meta_timestamp, CURRENT_TIMESTAMP) ELSE read_at END,
        error_code = COALESCE(p_error_code, error_code),
        error_message = COALESCE(p_error_message, error_message)
    WHERE id = v_message.id;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- Función para obtener estadísticas de cuenta
CREATE OR REPLACE FUNCTION whatsapp.get_account_stats(
    p_account_id UUID,
    p_days INTEGER DEFAULT 30
)
RETURNS TABLE (
    total_sent BIGINT,
    total_received BIGINT,
    total_delivered BIGINT,
    total_read BIGINT,
    total_failed BIGINT,
    delivery_rate DECIMAL,
    read_rate DECIMAL,
    active_contacts BIGINT,
    messages_by_day JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*) FILTER (WHERE m.direction = 'outbound')::BIGINT as total_sent,
        COUNT(*) FILTER (WHERE m.direction = 'inbound')::BIGINT as total_received,
        COUNT(*) FILTER (WHERE m.status = 'delivered')::BIGINT as total_delivered,
        COUNT(*) FILTER (WHERE m.status = 'read')::BIGINT as total_read,
        COUNT(*) FILTER (WHERE m.status = 'failed')::BIGINT as total_failed,
        ROUND(
            COUNT(*) FILTER (WHERE m.status IN ('delivered', 'read'))::DECIMAL /
            NULLIF(COUNT(*) FILTER (WHERE m.direction = 'outbound'), 0) * 100, 2
        ) as delivery_rate,
        ROUND(
            COUNT(*) FILTER (WHERE m.status = 'read')::DECIMAL /
            NULLIF(COUNT(*) FILTER (WHERE m.status IN ('delivered', 'read')), 0) * 100, 2
        ) as read_rate,
        COUNT(DISTINCT m.contact_id)::BIGINT as active_contacts,
        jsonb_object_agg(
            DATE(m.created_at)::TEXT,
            day_count
        ) as messages_by_day
    FROM whatsapp.messages m
    LEFT JOIN (
        SELECT DATE(created_at) as day, COUNT(*) as day_count
        FROM whatsapp.messages
        WHERE account_id = p_account_id
        AND created_at > CURRENT_TIMESTAMP - (p_days || ' days')::INTERVAL
        GROUP BY DATE(created_at)
    ) days ON TRUE
    WHERE m.account_id = p_account_id
    AND m.created_at > CURRENT_TIMESTAMP - (p_days || ' days')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Función para resetear límite diario
CREATE OR REPLACE FUNCTION whatsapp.reset_daily_limits()
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE whatsapp.accounts
    SET
        messages_sent_today = 0,
        last_limit_reset = CURRENT_TIMESTAMP
    WHERE DATE(last_limit_reset) < CURRENT_DATE
    OR last_limit_reset IS NULL;

    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- =====================
-- TRIGGERS
-- =====================

CREATE OR REPLACE FUNCTION whatsapp.update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_wa_accounts_updated_at
    BEFORE UPDATE ON whatsapp.accounts
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_templates_updated_at
    BEFORE UPDATE ON whatsapp.templates
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_contacts_updated_at
    BEFORE UPDATE ON whatsapp.contacts
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_convos_updated_at
    BEFORE UPDATE ON whatsapp.conversations
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_quick_updated_at
    BEFORE UPDATE ON whatsapp.quick_replies
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_auto_updated_at
    BEFORE UPDATE ON whatsapp.automations
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

CREATE TRIGGER trg_wa_bcast_updated_at
    BEFORE UPDATE ON whatsapp.broadcasts
    FOR EACH ROW EXECUTE FUNCTION whatsapp.update_timestamp();

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE whatsapp.accounts IS 'Cuentas de WhatsApp Business configuradas por tenant';
COMMENT ON TABLE whatsapp.templates IS 'Templates de mensaje aprobados por Meta';
COMMENT ON TABLE whatsapp.contacts IS 'Contactos de WhatsApp con historial de interacción';
COMMENT ON TABLE whatsapp.conversations IS 'Conversaciones (hilos de chat) con contactos';
COMMENT ON TABLE whatsapp.messages IS 'Mensajes enviados y recibidos';
COMMENT ON TABLE whatsapp.message_status_updates IS 'Historial de cambios de estado de mensajes';
COMMENT ON TABLE whatsapp.quick_replies IS 'Respuestas rápidas predefinidas';
COMMENT ON TABLE whatsapp.automations IS 'Reglas de automatización de respuestas';
COMMENT ON TABLE whatsapp.broadcasts IS 'Envíos masivos de mensajes';
COMMENT ON TABLE whatsapp.broadcast_recipients IS 'Destinatarios de broadcasts';

COMMENT ON FUNCTION whatsapp.receive_message IS 'Registra un mensaje entrante de WhatsApp';
COMMENT ON FUNCTION whatsapp.send_text_message IS 'Envía un mensaje de texto';
COMMENT ON FUNCTION whatsapp.send_template_message IS 'Envía un mensaje de template';
COMMENT ON FUNCTION whatsapp.update_message_status IS 'Actualiza el estado de un mensaje desde webhook';