-- ============================================
-- MIGRATION: Bot Custom Responses System
-- ============================================
-- التاريخ: 2025-10-25
-- الغرض: إنشاء نظام ردود مخصصة لكل بوت
-- Idempotent: آمن للتشغيل مرتين
-- ============================================

-- 1. إنشاء جدول ردود البوتات المخصصة
-- ============================================
CREATE TABLE IF NOT EXISTS bot_message_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bot_id INT NOT NULL,
    template_key VARCHAR(64) NOT NULL,
    template_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_bot_template (bot_id, template_key),
    FOREIGN KEY (bot_id) REFERENCES sub_bots(id) ON DELETE CASCADE,
    INDEX idx_bot_id (bot_id),
    INDEX idx_template_key (template_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='ردود مخصصة لكل بوت - تتجاوز الردود العامة في message_templates';

-- ============================================
-- Verification
-- ============================================
SELECT 
    'bot_message_templates' AS table_name,
    COUNT(*) AS total_templates,
    COUNT(DISTINCT bot_id) AS unique_bots,
    COUNT(DISTINCT template_key) AS unique_keys
FROM bot_message_templates;

SELECT '✅ Migration completed - Bot responses system ready!' AS status;
