-- ============================================================
-- Estado de entrega de los mensajes salientes del chatbot
-- scripts/chatbot/mysql/03-chatbot-message-delivery.sql
--
-- Ejecutar contra la base MySQL del chatbot (CHATBOT_DATABASE_URL),
-- no contra la base principal de la aplicación.
-- ============================================================

SET @status_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'chatbot_message'
      AND COLUMN_NAME = 'delivery_status'
);

SET @status_sql := IF(
    @status_exists = 0,
    'ALTER TABLE chatbot_message
        ADD COLUMN delivery_status VARCHAR(20) DEFAULT NULL AFTER agent_user_id,
        ADD COLUMN error_category VARCHAR(20) DEFAULT NULL AFTER delivery_status,
        ADD COLUMN error_code INT DEFAULT NULL AFTER error_category,
        ADD COLUMN error_message VARCHAR(500) DEFAULT NULL AFTER error_code,
        ADD INDEX idx_chatbot_message_delivery_status (delivery_status)',
    'SELECT 1'
);

PREPARE status_stmt FROM @status_sql;
EXECUTE status_stmt;
DEALLOCATE PREPARE status_stmt;