-- ============================================================
-- Migration 003: إصلاحات نظام البث
-- ============================================================
-- تاريخ: 2025-12-03
-- الوصف: توحيد صلاحيات البث + إضافة حقل الأخطاء
-- ============================================================

-- ============================================================
-- 1. إضافة حقل broadcast_allowed_types إذا لم يكن موجوداً
-- ============================================================

-- التحقق وإضافة الحقل
ALTER TABLE `super_distributors` 
ADD COLUMN IF NOT EXISTS `broadcast_allowed_types` JSON DEFAULT NULL 
COMMENT 'أنواع المحتوى المسموحة للبث (JSON array): ["text","photos","videos","documents","audio","stickers","animation"]';

-- ============================================================
-- 2. تحويل القيم من broadcast_content_type إلى broadcast_allowed_types
-- ============================================================
-- ملاحظة: يتم تشغيل هذا مرة واحدة فقط

UPDATE `super_distributors` 
SET `broadcast_allowed_types` = CASE 
    WHEN `broadcast_content_type` = 'text_only' THEN '["text"]'
    WHEN `broadcast_content_type` = 'text_images' THEN '["text","photos"]'
    WHEN `broadcast_content_type` = 'all' THEN '[]'
    ELSE '[]'
END
WHERE `broadcast_allowed_types` IS NULL;

-- ============================================================
-- 3. إضافة حقل errors لجدول broadcast_logs
-- ============================================================

ALTER TABLE `broadcast_logs` 
ADD COLUMN IF NOT EXISTS `errors` JSON DEFAULT NULL 
COMMENT 'عينة من أخطاء الإرسال (أول 10 أخطاء)';

-- ============================================================
-- 4. إضافة حقل content_caption لجدول broadcast_logs
-- ============================================================

ALTER TABLE `broadcast_logs` 
ADD COLUMN IF NOT EXISTS `content_caption` TEXT DEFAULT NULL 
COMMENT 'وصف الوسائط (caption)';

-- ============================================================
-- 5. التحقق من وجود جدول broadcast_blacklist
-- ============================================================

CREATE TABLE IF NOT EXISTS `broadcast_blacklist` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `word` VARCHAR(255) NOT NULL COMMENT 'الكلمة أو النمط المحظور',
    `type` ENUM('word', 'regex', 'domain') NOT NULL DEFAULT 'word' COMMENT 'نوع المطابقة',
    `severity` ENUM('block', 'warn', 'log') NOT NULL DEFAULT 'block' COMMENT 'درجة الخطورة',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'هل مفعّل؟',
    `added_by_admin_id` INT UNSIGNED DEFAULT NULL COMMENT 'من أضافها',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_word` (`word`),
    KEY `idx_type` (`type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='قائمة الكلمات المحظورة في البث';

-- ============================================================
-- 6. إضافة كلمات محظورة افتراضية (اختياري)
-- ============================================================

INSERT IGNORE INTO `broadcast_blacklist` (`word`, `type`, `severity`) VALUES
('http://', 'word', 'warn'),
('https://t.me/', 'word', 'warn'),
('@', 'word', 'log');

-- ============================================================
-- 7. التحقق من وجود جدول blocked_broadcasts
-- ============================================================

CREATE TABLE IF NOT EXISTS `blocked_broadcasts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broadcaster_type` ENUM('admin', 'superdist') NOT NULL,
    `broadcaster_id` INT UNSIGNED NOT NULL,
    `broadcaster_telegram_id` BIGINT NOT NULL,
    `content_type` VARCHAR(32) NOT NULL,
    `content_snippet` VARCHAR(500) DEFAULT NULL COMMENT 'مقتطف من المحتوى',
    `matched_words` JSON DEFAULT NULL COMMENT 'الكلمات المطابقة',
    `reason` VARCHAR(255) NOT NULL COMMENT 'سبب الحظر',
    `action_taken` ENUM('blocked', 'warned', 'logged') NOT NULL DEFAULT 'blocked',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_broadcaster` (`broadcaster_type`, `broadcaster_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سجل البثات المحظورة';

-- ============================================================
-- 8. إضافة جدول إعدادات البث
-- ============================================================

CREATE TABLE IF NOT EXISTS `broadcast_settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(64) NOT NULL,
    `setting_value` TEXT NOT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='إعدادات نظام البث';

-- إعدادات افتراضية
INSERT IGNORE INTO `broadcast_settings` (`setting_key`, `setting_value`) VALUES
('blacklist_enabled', '1'),
('moderation_enabled', '1'),
('admin_bypass_blacklist', '0'),
('max_message_length', '4096'),
('batch_size', '25'),
('batch_delay_ms', '50');

-- ============================================================
-- Done!
-- ============================================================
