-- Sistema de Chat en Tiempo Real con WhatsApp Business API
-- Soporte completo para conversaciones multicanal, agentes, y automatización

-- Tabla de configuración de WhatsApp Business
CREATE TABLE IF NOT EXISTS whatsapp_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Credenciales API
    business_account_id VARCHAR(255) NOT NULL,
    phone_number_id VARCHAR(255) NOT NULL,
    access_token TEXT NOT NULL,
    webhook_verify_token VARCHAR(255) NOT NULL,
    app_secret VARCHAR(255) NOT NULL,
    
    -- Configuración del negocio
    business_name VARCHAR(255) NOT NULL DEFAULT 'Mayan World Travel Agency',
    business_description TEXT,
    website_url VARCHAR(255),
    business_phone VARCHAR(50),
    business_email VARCHAR(255),
    business_address TEXT,
    
    -- Configuración de mensajes
    welcome_message TEXT DEFAULT 'Bienvenido a Mayan World Travel Agency! 🏛️ ¿En qué podemos ayudarte hoy?',
    business_hours_message TEXT DEFAULT 'Estamos fuera del horario de atención. Te responderemos lo antes posible.',
    auto_reply_enabled BOOLEAN DEFAULT TRUE,
    business_hours_enabled BOOLEAN DEFAULT TRUE,
    
    -- Horarios de atención (JSON)
    business_hours JSON DEFAULT '{"lunes": {"open": "08:00", "close": "18:00", "enabled": true}, "martes": {"open": "08:00", "close": "18:00", "enabled": true}, "miercoles": {"open": "08:00", "close": "18:00", "enabled": true}, "jueves": {"open": "08:00", "close": "18:00", "enabled": true}, "viernes": {"open": "08:00", "close": "18:00", "enabled": true}, "sabado": {"open": "09:00", "close": "15:00", "enabled": true}, "domingo": {"open": "09:00", "close": "15:00", "enabled": false}}',
    
    -- Estado y metadatos
    is_active BOOLEAN DEFAULT TRUE,
    webhook_url VARCHAR(255),
    last_webhook_test TIMESTAMP NULL,
    api_rate_limit INT DEFAULT 1000,
    daily_message_limit INT DEFAULT 10000,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de agentes/operadores de chat
CREATE TABLE IF NOT EXISTS chat_agents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Información del agente
    user_id INT NULL, -- Referencia a tabla usuarios si existe
    agent_name VARCHAR(255) NOT NULL,
    agent_email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(50) NULL,
    avatar_url VARCHAR(500) NULL,
    
    -- Especialización
    specialization ENUM('general', 'tours', 'transport', 'bookings', 'billing', 'technical') DEFAULT 'general',
    languages JSON DEFAULT '["es", "en"]', -- Idiomas que maneja
    experience_level ENUM('junior', 'senior', 'expert', 'supervisor') DEFAULT 'junior',
    
    -- Estado y disponibilidad
    status ENUM('online', 'busy', 'away', 'offline') DEFAULT 'offline',
    is_active BOOLEAN DEFAULT TRUE,
    max_concurrent_chats INT DEFAULT 5,
    current_chat_count INT DEFAULT 0,
    
    -- Configuración de trabajo
    can_transfer_chats BOOLEAN DEFAULT TRUE,
    can_close_chats BOOLEAN DEFAULT TRUE,
    can_access_history BOOLEAN DEFAULT TRUE,
    priority_level INT DEFAULT 5, -- 1-10, mayor número = mayor prioridad
    
    -- Horarios de trabajo
    work_schedule JSON DEFAULT '{"lunes": {"start": "08:00", "end": "18:00", "enabled": true}, "martes": {"start": "08:00", "end": "18:00", "enabled": true}, "miercoles": {"start": "08:00", "end": "18:00", "enabled": true}, "jueves": {"start": "08:00", "end": "18:00", "enabled": true}, "viernes": {"start": "08:00", "end": "18:00", "enabled": true}, "sabado": {"start": "09:00", "end": "15:00", "enabled": false}, "domingo": {"start": "09:00", "end": "15:00", "enabled": false}}',
    
    -- Estadísticas
    total_conversations INT DEFAULT 0,
    total_messages_sent INT DEFAULT 0,
    avg_response_time INT DEFAULT 0, -- segundos
    customer_rating DECIMAL(3,2) DEFAULT 0.00,
    last_activity TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_agent_status (status, is_active),
    INDEX idx_specialization (specialization),
    INDEX idx_priority (priority_level DESC)
);

-- Tabla de conversaciones
CREATE TABLE IF NOT EXISTS chat_conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Identificación
    whatsapp_conversation_id VARCHAR(255) UNIQUE NULL, -- ID de WhatsApp
    customer_phone VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255) NULL,
    customer_email VARCHAR(255) NULL,
    
    -- Contexto del cliente
    customer_language ENUM('es', 'en', 'auto') DEFAULT 'auto',
    customer_timezone VARCHAR(100) DEFAULT 'America/Guatemala',
    customer_country VARCHAR(10) DEFAULT 'GT',
    is_returning_customer BOOLEAN DEFAULT FALSE,
    
    -- Asignación de agente
    assigned_agent_id INT NULL,
    assigned_at TIMESTAMP NULL,
    assignment_method ENUM('auto', 'manual', 'transfer', 'escalation') DEFAULT 'auto',
    
    -- Estado de la conversación
    status ENUM('pending', 'active', 'waiting_agent', 'waiting_customer', 'resolved', 'closed', 'transferred') DEFAULT 'pending',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    category ENUM('inquiry', 'booking', 'support', 'complaint', 'sales', 'information') DEFAULT 'inquiry',
    
    -- Canal y origen
    channel ENUM('whatsapp', 'web', 'facebook', 'instagram') DEFAULT 'whatsapp',
    source_page VARCHAR(255) NULL, -- Página desde donde inició el chat
    referrer VARCHAR(500) NULL,
    user_agent TEXT NULL,
    
    -- Contexto de negocio
    related_product_id INT NULL, -- Producto sobre el que pregunta
    related_booking_id INT NULL, -- Reserva relacionada
    inquiry_type VARCHAR(100) NULL, -- tours, transport, booking, general
    estimated_budget DECIMAL(10,2) NULL,
    travel_dates JSON NULL, -- {'start': '2024-03-15', 'end': '2024-03-20'}
    group_size INT NULL,
    
    -- Métricas
    first_response_time INT NULL, -- segundos hasta primera respuesta
    avg_response_time INT DEFAULT 0,
    total_messages INT DEFAULT 0,
    customer_satisfaction_rating INT NULL, -- 1-5
    customer_feedback TEXT NULL,
    
    -- Automatización
    bot_handled BOOLEAN DEFAULT FALSE,
    auto_responses_sent INT DEFAULT 0,
    escalated_to_human BOOLEAN DEFAULT FALSE,
    escalation_reason TEXT NULL,
    
    -- Metadatos
    tags JSON NULL, -- ['urgent', 'vip', 'technical']
    notes TEXT NULL,
    internal_notes TEXT NULL, -- Solo para agentes
    
    -- Control de tiempo
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    first_response_at TIMESTAMP NULL,
    last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_customer_phone (customer_phone),
    INDEX idx_agent (assigned_agent_id),
    INDEX idx_status_priority (status, priority),
    INDEX idx_channel_status (channel, status),
    INDEX idx_activity (last_activity_at DESC),
    INDEX idx_business_context (related_product_id, related_booking_id),
    
    FOREIGN KEY (assigned_agent_id) REFERENCES chat_agents(id) ON DELETE SET NULL,
    FOREIGN KEY (related_product_id) REFERENCES productos(id) ON DELETE SET NULL,
    FOREIGN KEY (related_booking_id) REFERENCES reservas(id) ON DELETE SET NULL
);

-- Tabla de mensajes
CREATE TABLE IF NOT EXISTS chat_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Relación con conversación
    conversation_id INT NOT NULL,
    
    -- Identificación del mensaje
    whatsapp_message_id VARCHAR(255) UNIQUE NULL, -- ID de WhatsApp
    message_type ENUM('text', 'image', 'document', 'audio', 'video', 'location', 'contact', 'template', 'interactive', 'system') DEFAULT 'text',
    
    -- Remitente
    sender_type ENUM('customer', 'agent', 'system', 'bot') NOT NULL,
    sender_id INT NULL, -- ID del agente si es agente
    sender_name VARCHAR(255) NULL,
    
    -- Contenido del mensaje
    message_content TEXT NOT NULL,
    media_url VARCHAR(500) NULL,
    media_mime_type VARCHAR(100) NULL,
    media_filename VARCHAR(255) NULL,
    media_caption TEXT NULL,
    
    -- Información de WhatsApp
    whatsapp_status ENUM('sent', 'delivered', 'read', 'failed') NULL,
    whatsapp_timestamp BIGINT NULL, -- Timestamp de WhatsApp
    whatsapp_error TEXT NULL,
    
    -- Contenido estructurado (para mensajes interactivos)
    interactive_data JSON NULL,
    template_data JSON NULL,
    location_data JSON NULL, -- {'latitude': 14.6349, 'longitude': -90.5069, 'name': 'Tikal'}
    
    -- Contexto y metadatos
    reply_to_message_id INT NULL, -- Para responder a mensajes específicos
    quoted_message_content TEXT NULL,
    forwarded_from VARCHAR(255) NULL,
    
    -- Procesamiento automático
    processed_by_bot BOOLEAN DEFAULT FALSE,
    bot_confidence DECIMAL(3,2) NULL, -- 0.00-1.00
    detected_intent VARCHAR(100) NULL, -- greeting, booking_inquiry, pricing, etc.
    extracted_entities JSON NULL, -- {'dates': ['2024-03-15'], 'locations': ['Tikal'], 'numbers': [2]}
    
    -- Control de calidad
    flagged_for_review BOOLEAN DEFAULT FALSE,
    flag_reason VARCHAR(255) NULL,
    reviewed_by_agent_id INT NULL,
    review_status ENUM('pending', 'approved', 'rejected') NULL,
    
    -- Timestamps
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at TIMESTAMP NULL,
    read_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_conversation (conversation_id, sent_at DESC),
    INDEX idx_sender (sender_type, sender_id),
    INDEX idx_whatsapp_id (whatsapp_message_id),
    INDEX idx_message_type (message_type),
    INDEX idx_bot_processing (processed_by_bot, detected_intent),
    INDEX idx_review_status (flagged_for_review, review_status),
    
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES chat_agents(id) ON DELETE SET NULL,
    FOREIGN KEY (reply_to_message_id) REFERENCES chat_messages(id) ON DELETE SET NULL,
    FOREIGN KEY (reviewed_by_agent_id) REFERENCES chat_agents(id) ON DELETE SET NULL
);

-- Tabla de plantillas de mensajes
CREATE TABLE IF NOT EXISTS chat_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Identificación
    template_name VARCHAR(255) NOT NULL UNIQUE,
    template_category ENUM('welcome', 'booking', 'pricing', 'tours', 'transport', 'support', 'closing', 'escalation') NOT NULL,
    language ENUM('es', 'en') DEFAULT 'es',
    
    -- Contenido
    template_content TEXT NOT NULL,
    template_variables JSON NULL, -- {'customer_name': 'string', 'tour_name': 'string'}
    
    -- Configuración
    is_active BOOLEAN DEFAULT TRUE,
    is_auto_triggered BOOLEAN DEFAULT FALSE,
    trigger_keywords JSON NULL, -- ['precio', 'cost', 'cuanto cuesta']
    trigger_conditions JSON NULL, -- Condiciones para auto-envío
    
    -- WhatsApp Business Template (para notificaciones)
    whatsapp_template_id VARCHAR(255) NULL,
    whatsapp_template_status ENUM('pending', 'approved', 'rejected', 'paused') NULL,
    
    -- Estadísticas de uso
    times_used INT DEFAULT 0,
    effectiveness_rating DECIMAL(3,2) DEFAULT 0.00,
    last_used_at TIMESTAMP NULL,
    
    -- Metadatos
    created_by_agent_id INT NULL,
    approved_by_agent_id INT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category_language (template_category, language),
    INDEX idx_auto_trigger (is_auto_triggered, is_active),
    INDEX idx_usage (times_used DESC),
    
    FOREIGN KEY (created_by_agent_id) REFERENCES chat_agents(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by_agent_id) REFERENCES chat_agents(id) ON DELETE SET NULL
);

-- Tabla de respuestas automáticas/bot
CREATE TABLE IF NOT EXISTS chat_auto_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Trigger
    trigger_type ENUM('keyword', 'intent', 'time_based', 'no_agent', 'business_hours') NOT NULL,
    trigger_value TEXT NOT NULL, -- keyword, intent name, etc.
    trigger_conditions JSON NULL,
    
    -- Respuesta
    response_type ENUM('text', 'template', 'interactive', 'transfer_to_agent') NOT NULL,
    response_content TEXT NOT NULL,
    response_data JSON NULL, -- Para mensajes interactivos
    
    -- Configuración
    is_active BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 5, -- Mayor número = mayor prioridad
    delay_seconds INT DEFAULT 0, -- Delay antes de enviar
    
    -- Condiciones de activación
    only_first_message BOOLEAN DEFAULT FALSE,
    only_business_hours BOOLEAN DEFAULT FALSE,
    max_uses_per_conversation INT DEFAULT NULL, -- Límite por conversación
    
    -- Contexto
    applies_to_languages JSON DEFAULT '["es", "en"]',
    applies_to_channels JSON DEFAULT '["whatsapp"]',
    
    -- Estadísticas
    times_triggered INT DEFAULT 0,
    success_rate DECIMAL(3,2) DEFAULT 0.00,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_trigger (trigger_type, is_active),
    INDEX idx_priority (priority DESC, is_active)
);

-- Tabla de transferencias de chat
CREATE TABLE IF NOT EXISTS chat_transfers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    conversation_id INT NOT NULL,
    from_agent_id INT NULL,
    to_agent_id INT NOT NULL,
    
    transfer_reason ENUM('workload', 'specialization', 'escalation', 'language', 'manual', 'end_of_shift') NOT NULL,
    transfer_notes TEXT NULL,
    
    -- Estado
    status ENUM('pending', 'accepted', 'rejected', 'auto_assigned') DEFAULT 'pending',
    accepted_at TIMESTAMP NULL,
    rejection_reason TEXT NULL,
    
    -- Contexto adicional
    customer_consent BOOLEAN DEFAULT FALSE,
    transfer_message_sent BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_conversation (conversation_id),
    INDEX idx_agents (from_agent_id, to_agent_id),
    INDEX idx_status (status, created_at),
    
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (from_agent_id) REFERENCES chat_agents(id) ON DELETE SET NULL,
    FOREIGN KEY (to_agent_id) REFERENCES chat_agents(id) ON DELETE CASCADE
);

-- Tabla de estadísticas de chat
CREATE TABLE IF NOT EXISTS chat_statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    date DATE NOT NULL UNIQUE,
    
    -- Conversaciones
    total_conversations INT DEFAULT 0,
    new_conversations INT DEFAULT 0,
    active_conversations INT DEFAULT 0,
    resolved_conversations INT DEFAULT 0,
    abandoned_conversations INT DEFAULT 0,
    
    -- Mensajes
    total_messages INT DEFAULT 0,
    messages_from_customers INT DEFAULT 0,
    messages_from_agents INT DEFAULT 0,
    messages_from_bot INT DEFAULT 0,
    
    -- Tiempos de respuesta
    avg_first_response_time INT DEFAULT 0, -- segundos
    avg_response_time INT DEFAULT 0,
    max_response_time INT DEFAULT 0,
    conversations_within_sla INT DEFAULT 0, -- SLA = 5 minutos
    
    -- Agentes
    active_agents INT DEFAULT 0,
    peak_concurrent_conversations INT DEFAULT 0,
    agent_utilization_rate DECIMAL(5,2) DEFAULT 0.00, -- %
    
    -- Satisfacción
    total_ratings INT DEFAULT 0,
    avg_satisfaction_rating DECIMAL(3,2) DEFAULT 0.00,
    nps_score INT DEFAULT 0, -- Net Promoter Score
    
    -- Conversiones de negocio
    conversations_to_bookings INT DEFAULT 0,
    booking_conversion_rate DECIMAL(5,2) DEFAULT 0.00,
    total_booking_value DECIMAL(12,2) DEFAULT 0.00,
    
    -- WhatsApp específico
    whatsapp_delivery_rate DECIMAL(5,2) DEFAULT 0.00,
    whatsapp_read_rate DECIMAL(5,2) DEFAULT 0.00,
    failed_message_deliveries INT DEFAULT 0,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Vista para conversaciones activas con información de agente
CREATE OR REPLACE VIEW active_conversations AS
SELECT 
    c.id,
    c.whatsapp_conversation_id,
    c.customer_phone,
    c.customer_name,
    c.status,
    c.priority,
    c.category,
    c.channel,
    c.started_at,
    c.last_activity_at,
    
    -- Información del agente
    a.agent_name,
    a.avatar_url as agent_avatar,
    a.status as agent_status,
    
    -- Último mensaje
    lm.message_content as last_message,
    lm.sent_at as last_message_time,
    lm.sender_type as last_sender,
    
    -- Conteos
    (SELECT COUNT(*) FROM chat_messages WHERE conversation_id = c.id) as message_count,
    (SELECT COUNT(*) FROM chat_messages WHERE conversation_id = c.id AND sender_type = 'customer' AND read_at IS NULL) as unread_count,
    
    -- Tiempo desde último mensaje
    TIMESTAMPDIFF(MINUTE, c.last_activity_at, NOW()) as minutes_since_activity,
    
    -- Contexto de negocio
    p.nombre as related_product_name,
    r.codigo_reserva as related_booking_code
    
FROM chat_conversations c
LEFT JOIN chat_agents a ON c.assigned_agent_id = a.id
LEFT JOIN chat_messages lm ON lm.id = (
    SELECT id FROM chat_messages 
    WHERE conversation_id = c.id 
    ORDER BY sent_at DESC LIMIT 1
)
LEFT JOIN productos p ON c.related_product_id = p.id
LEFT JOIN reservas r ON c.related_booking_id = r.id
WHERE c.status IN ('pending', 'active', 'waiting_agent', 'waiting_customer')
ORDER BY c.priority DESC, c.last_activity_at DESC;

-- Triggers para actualizar estadísticas automáticamente
DELIMITER //

CREATE TRIGGER update_conversation_stats_on_message
AFTER INSERT ON chat_messages
FOR EACH ROW
BEGIN
    -- Actualizar contador de mensajes en conversación
    UPDATE chat_conversations 
    SET total_messages = total_messages + 1,
        last_activity_at = NEW.sent_at
    WHERE id = NEW.conversation_id;
    
    -- Si es el primer mensaje del agente, registrar tiempo de primera respuesta
    IF NEW.sender_type = 'agent' AND (
        SELECT first_response_time FROM chat_conversations WHERE id = NEW.conversation_id
    ) IS NULL THEN
        UPDATE chat_conversations 
        SET first_response_time = TIMESTAMPDIFF(SECOND, started_at, NEW.sent_at),
            first_response_at = NEW.sent_at
        WHERE id = NEW.conversation_id;
    END IF;
    
    -- Actualizar estadísticas diarias
    INSERT INTO chat_statistics (date, total_messages, 
        messages_from_customers, messages_from_agents, messages_from_bot)
    VALUES (CURDATE(), 1, 
        CASE WHEN NEW.sender_type = 'customer' THEN 1 ELSE 0 END,
        CASE WHEN NEW.sender_type = 'agent' THEN 1 ELSE 0 END,
        CASE WHEN NEW.sender_type = 'bot' THEN 1 ELSE 0 END
    ) ON DUPLICATE KEY UPDATE 
        total_messages = total_messages + 1,
        messages_from_customers = messages_from_customers + CASE WHEN NEW.sender_type = 'customer' THEN 1 ELSE 0 END,
        messages_from_agents = messages_from_agents + CASE WHEN NEW.sender_type = 'agent' THEN 1 ELSE 0 END,
        messages_from_bot = messages_from_bot + CASE WHEN NEW.sender_type = 'bot' THEN 1 ELSE 0 END,
        updated_at = CURRENT_TIMESTAMP;
END//

CREATE TRIGGER update_agent_activity_on_message
AFTER INSERT ON chat_messages
FOR EACH ROW
BEGIN
    -- Actualizar estadísticas del agente si el mensaje es de un agente
    IF NEW.sender_type = 'agent' AND NEW.sender_id IS NOT NULL THEN
        UPDATE chat_agents 
        SET total_messages_sent = total_messages_sent + 1,
            last_activity = NEW.sent_at
        WHERE id = NEW.sender_id;
    END IF;
END//

CREATE TRIGGER update_stats_on_conversation_close
AFTER UPDATE ON chat_conversations
FOR EACH ROW
BEGIN
    -- Cuando se cierra una conversación
    IF OLD.status != 'closed' AND NEW.status = 'closed' THEN
        INSERT INTO chat_statistics (date, resolved_conversations)
        VALUES (CURDATE(), 1)
        ON DUPLICATE KEY UPDATE 
            resolved_conversations = resolved_conversations + 1,
            updated_at = CURRENT_TIMESTAMP;
    END IF;
    
    -- Cuando se inicia una conversación
    IF OLD.status = 'pending' AND NEW.status = 'active' THEN
        INSERT INTO chat_statistics (date, active_conversations)
        VALUES (CURDATE(), 1)
        ON DUPLICATE KEY UPDATE 
            active_conversations = active_conversations + 1,
            updated_at = CURRENT_TIMESTAMP;
    END IF;
END//

DELIMITER ;

-- Índices adicionales para optimización
CREATE INDEX idx_messages_unread ON chat_messages(conversation_id, sender_type, read_at);
CREATE INDEX idx_conversations_activity ON chat_conversations(status, last_activity_at DESC);
CREATE INDEX idx_agents_workload ON chat_agents(status, current_chat_count, max_concurrent_chats);
CREATE INDEX idx_templates_trigger ON chat_templates(is_auto_triggered, trigger_keywords(255));

-- Insertar configuración inicial
INSERT INTO whatsapp_config (
    business_account_id, phone_number_id, access_token, 
    webhook_verify_token, app_secret, business_name
) VALUES (
    'your_business_account_id', 'your_phone_number_id', 'your_access_token',
    'your_webhook_verify_token', 'your_app_secret', 'Mayan World Travel Agency'
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar agente administrador por defecto
INSERT INTO chat_agents (
    agent_name, agent_email, specialization, experience_level, 
    status, can_transfer_chats, can_close_chats, can_access_history, priority_level
) VALUES (
    'Admin', 'admin@mayanworldtravelagency.com', 'general', 'expert',
    'online', TRUE, TRUE, TRUE, 10
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar plantillas básicas
INSERT INTO chat_templates (template_name, template_category, language, template_content, template_variables) VALUES
('welcome_es', 'welcome', 'es', 'Bienvenido a Mayan World Travel Agency! 🏛️ Soy {agent_name} y estoy aquí para ayudarte con tus planes de viaje por Guatemala, Belice y México. ¿En qué puedo asistirte hoy?', '{"agent_name": "string"}'),
('welcome_en', 'welcome', 'en', 'Welcome to Mayan World Travel Agency! 🏛️ I am {agent_name} and I am here to help you with your travel plans in Guatemala, Belize and Mexico. How can I assist you today?', '{"agent_name": "string"}'),
('pricing_tours', 'pricing', 'es', 'Te puedo ayudar con información sobre precios de nuestros tours. Nuestros tours más populares son:\n\n🏛️ Tikal: desde $85 USD\n🏞️ Semuc Champey: desde $95 USD\n🌅 Yaxha Sunset: desde $75 USD\n\n¿Hay algún destino específico que te interese?', NULL),
('booking_assistance', 'booking', 'es', 'Perfecto! Te ayudo con tu reserva. Para darte la mejor atención necesito algunos datos:\n\n📅 ¿Qué fechas tienes en mente?\n👥 ¿Cuántas personas van a viajar?\n🗺️ ¿Qué tours te interesan?\n\nCon esta información podremos crear la experiencia perfecta para ti!', NULL),
('business_hours_es', 'support', 'es', 'Gracias por contactarnos! 🌙 Actualmente estamos fuera del horario de atención (8:00 AM - 6:00 PM GMT-6). Te responderemos primera hora mañana. Para emergencias puedes llamar al +502 1234-5678.', NULL)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Insertar respuestas automáticas básicas
INSERT INTO chat_auto_responses (trigger_type, trigger_value, response_type, response_content, is_active, priority) VALUES
('keyword', '["hola", "hello", "hi", "buenos dias", "buenas tardes"]', 'template', 'welcome_es', TRUE, 10),
('keyword', '["precio", "cost", "cuanto cuesta", "price"]', 'template', 'pricing_tours', TRUE, 8),
('keyword', '["reservar", "booking", "book", "reserva"]', 'template', 'booking_assistance', TRUE, 8),
('no_agent', 'all', 'text', 'En este momento todos nuestros agentes están ocupados. Te atenderemos en breve. Mientras tanto, puedes visitar nuestro sitio web para ver nuestros tours: mayanworldtravelagency.com', TRUE, 5),
('business_hours', 'outside', 'template', 'business_hours_es', TRUE, 9)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;