-- ============================================================
-- Migration 001: Vouchers, Broadcast Permissions, Blacklist, System Checks
-- Date: 2024-12-03
-- Author: Cascade AI
-- ============================================================

-- ============================================================
-- A) تحديث جدول super_distributors لصلاحيات البث
-- ============================================================

-- إضافة can_broadcast إذا لم يكن موجوداً
SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'super_distributors' 
    AND column_name = 'can_broadcast'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `super_distributors` ADD COLUMN `can_broadcast` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''هل يمكنه البث؟'' AFTER `status`',
    'SELECT ''Column can_broadcast already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- إضافة broadcast_allowed_types إذا لم يكن موجوداً
SET @column_exists2 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'super_distributors' 
    AND column_name = 'broadcast_allowed_types'
);

SET @sql2 = IF(@column_exists2 = 0,
    'ALTER TABLE `super_distributors` ADD COLUMN `broadcast_allowed_types` TEXT DEFAULT NULL COMMENT ''JSON array: ["text","stickers","photos","videos","documents","audio"]'' AFTER `can_broadcast`',
    'SELECT ''Column broadcast_allowed_types already exists'' AS info'
);
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- تعيين القيمة الافتراضية للموزعين الحاليين
UPDATE `super_distributors` SET `broadcast_allowed_types` = '["text","stickers","photos","videos","documents","audio"]' WHERE `broadcast_allowed_types` IS NULL;

-- ============================================================
-- B) جدول القسائم (Vouchers)
-- ============================================================

CREATE TABLE `vouchers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(32) NOT NULL COMMENT 'كود القسيمة الفريد (مثل: VCH-XXXX-XXXX)',
    `type` ENUM('sd', 'activation_bundle') NOT NULL COMMENT 'sd=اشتراك موزع، activation_bundle=حزمة أكواد',
    
    -- بيانات القسيمة (JSON)
    `payload` JSON NOT NULL COMMENT 'بيانات القسيمة حسب النوع',
    /*
     * payload لـ sd:
     * {
     *   "accounts": [
     *     {"account_id": 1, "duration_days": 30, "limits": {"daily": 10, "weekly": 50, "monthly": 200}}
     *   ]
     * }
     * 
     * payload لـ activation_bundle:
     * {
     *   "account_id": 1,
     *   "codes_count": 10,
     *   "access_type": "timed",
     *   "access_days": 30
     * }
     */
    
    -- الاستخدام
    `max_uses` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'الحد الأقصى للاستخدام',
    `current_uses` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'عدد مرات الاستخدام الحالية',
    
    -- الحالة
    `status` ENUM('unused', 'partial', 'used', 'expired', 'disabled') NOT NULL DEFAULT 'unused',
    `expires_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'تاريخ انتهاء صلاحية القسيمة',
    
    -- الربط بالدفع
    `payment_id` VARCHAR(100) DEFAULT NULL COMMENT 'معرف الدفع الخارجي',
    `payment_provider` VARCHAR(50) DEFAULT NULL COMMENT 'مزود الدفع (stripe, paypal, etc)',
    `payment_amount` DECIMAL(10,2) DEFAULT NULL COMMENT 'مبلغ الدفع',
    `payment_currency` VARCHAR(3) DEFAULT NULL COMMENT 'عملة الدفع',
    `payment_status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT NULL,
    
    -- من أنشأ القسيمة
    `created_by_admin_id` INT UNSIGNED DEFAULT NULL COMMENT 'معرف المسؤول الذي أنشأ القسيمة',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_status` (`status`),
    KEY `idx_type` (`type`),
    KEY `idx_payment_id` (`payment_id`),
    KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='جدول القسائم والباقات';

-- ============================================================
-- C) جدول سجلات استخدام القسائم
-- ============================================================

CREATE TABLE `voucher_redemptions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `voucher_id` INT UNSIGNED NOT NULL,
    `redeemer_telegram_id` BIGINT NOT NULL COMMENT 'Telegram ID للمستفيد',
    `redeemer_type` ENUM('superdist', 'user') NOT NULL COMMENT 'نوع المستفيد',
    `redeemer_id` INT UNSIGNED DEFAULT NULL COMMENT 'ID في جدول super_distributors أو users',
    
    -- نتيجة الاسترداد
    `result` JSON NOT NULL COMMENT 'تفاصيل ما تم إنشاؤه/تفعيله',
    `redeemed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_voucher_id` (`voucher_id`),
    KEY `idx_redeemer_telegram_id` (`redeemer_telegram_id`),
    
    CONSTRAINT `fk_vr_voucher` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سجلات استخدام القسائم';

-- ============================================================
-- D) جدول أكواد القسائم (للحزم التي تولد أكواد ACT)
-- ============================================================

CREATE TABLE `voucher_codes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `voucher_id` INT UNSIGNED NOT NULL,
    `activation_code_id` INT UNSIGNED NOT NULL COMMENT 'معرف كود التفعيل المولد',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_voucher_id` (`voucher_id`),
    KEY `idx_activation_code_id` (`activation_code_id`),
    
    CONSTRAINT `fk_vc_voucher` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_vc_activation` FOREIGN KEY (`activation_code_id`) REFERENCES `activation_codes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='أكواد التفعيل المولدة من القسائم';

-- ============================================================
-- E) جدول الكلمات المحظورة للبث (Blacklist)
-- ============================================================

CREATE TABLE `broadcast_blacklist` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `word` VARCHAR(100) 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,
    `added_by_admin_id` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_word` (`word`),
    KEY `idx_type` (`type`),
    KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='قائمة الكلمات المحظورة للبث';

-- إدراج كلمات محظورة افتراضية
INSERT INTO `broadcast_blacklist` (`word`, `type`, `severity`) VALUES
('porn', 'word', 'block'),
('xxx', 'word', 'block'),
('sex', 'word', 'block'),
('nude', 'word', 'block'),
('adult', 'word', 'block'),
('18+', 'word', 'block'),
('casino', 'word', 'block'),
('gambling', 'word', 'block'),
('t.me/joinchat', 'domain', 'warn'),
('bit.ly', 'domain', 'warn');

-- ============================================================
-- F) جدول البثات المحظورة
-- ============================================================

CREATE TABLE `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(20) NOT NULL COMMENT 'نوع المحتوى (text, photo, etc)',
    `content_snippet` TEXT NOT NULL COMMENT 'مقتطف من المحتوى',
    `matched_words` JSON NOT NULL COMMENT 'الكلمات المطابقة من blacklist',
    
    -- السبب والإجراء
    `reason` VARCHAR(255) NOT NULL,
    `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='سجل البثات المحظورة';

-- ============================================================
-- G) جدول تقارير فحص النظام
-- ============================================================

CREATE TABLE `system_check_reports` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `check_type` ENUM('full', 'quick', 'custom') NOT NULL DEFAULT 'full',
    `triggered_by` ENUM('admin', 'cron', 'api', 'cli') NOT NULL,
    `triggered_by_admin_id` INT UNSIGNED DEFAULT NULL,
    
    -- النتائج
    `total_tests` INT UNSIGNED NOT NULL DEFAULT 0,
    `passed_tests` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_tests` INT UNSIGNED NOT NULL DEFAULT 0,
    `warnings` INT UNSIGNED NOT NULL DEFAULT 0,
    
    -- التفاصيل
    `results` JSON NOT NULL COMMENT 'نتائج كل اختبار بالتفصيل',
    `summary` TEXT NOT NULL COMMENT 'ملخص نصي',
    `recommendations` JSON DEFAULT NULL COMMENT 'توصيات الإصلاح',
    
    -- ملفات التقرير
    `report_file_json` VARCHAR(255) DEFAULT NULL COMMENT 'مسار ملف JSON',
    `report_file_txt` VARCHAR(255) DEFAULT NULL COMMENT 'مسار ملف TXT',
    
    -- الأداء
    `duration_ms` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'مدة التنفيذ بالميلي ثانية',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_check_type` (`check_type`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='تقارير فحص النظام';

-- ============================================================
-- H) جدول إعدادات البث
-- ============================================================

CREATE TABLE `broadcast_settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(50) NOT NULL,
    `setting_value` TEXT NOT NULL,
    `description` VARCHAR(255) DEFAULT 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 INTO `broadcast_settings` (`setting_key`, `setting_value`, `description`) VALUES
('chunk_size', '30', 'عدد المستلمين في كل دفعة'),
('delay_between_chunks_ms', '1000', 'التأخير بين الدفعات (ميلي ثانية)'),
('delay_between_messages_ms', '50', 'التأخير بين الرسائل (ميلي ثانية)'),
('max_retries_on_429', '3', 'الحد الأقصى لإعادة المحاولة عند 429'),
('blacklist_enabled', '1', 'تفعيل فحص الكلمات المحظورة'),
('moderation_enabled', '1', 'تفعيل نظام المراقبة'),
('admin_bypass_blacklist', '0', 'السماح للأدمن بتجاوز القائمة السوداء');

-- ============================================================
-- I) جدول أخطاء البث
-- ============================================================

CREATE TABLE `broadcast_errors` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broadcast_id` VARCHAR(50) NOT NULL COMMENT 'معرف البث',
    `recipient_telegram_id` BIGINT NOT NULL,
    `error_code` INT NOT NULL,
    `error_message` TEXT NOT NULL,
    `retry_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `retry_after` INT UNSIGNED DEFAULT NULL COMMENT 'ثواني الانتظار (لـ 429)',
    `resolved` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_broadcast_id` (`broadcast_id`),
    KEY `idx_error_code` (`error_code`),
    KEY `idx_resolved` (`resolved`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سجل أخطاء البث';

-- ============================================================
-- J) تحديث جدول superdist_accounts لتتبع التصفير (من V3)
-- ============================================================
-- ملاحظة: قد تكون موجودة من migration سابق

-- ALTER TABLE `superdist_accounts`
-- ADD COLUMN IF NOT EXISTS `last_daily_reset` DATE DEFAULT NULL,
-- ADD COLUMN IF NOT EXISTS `last_weekly_reset` CHAR(6) DEFAULT NULL,
-- ADD COLUMN IF NOT EXISTS `last_monthly_reset` CHAR(6) DEFAULT NULL;

-- ============================================================
-- K) جدول سجلات الدفع (Payment Logs)
-- ============================================================

CREATE TABLE `payment_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `provider` VARCHAR(50) NOT NULL COMMENT 'مزود الدفع',
    `payment_id` VARCHAR(100) NOT NULL COMMENT 'معرف الدفع من المزود',
    `event_type` VARCHAR(50) NOT NULL COMMENT 'نوع الحدث (payment.completed, etc)',
    
    -- البيانات
    `amount` DECIMAL(10,2) NOT NULL,
    `currency` VARCHAR(3) NOT NULL,
    `payer_email` VARCHAR(255) DEFAULT NULL,
    `payer_name` VARCHAR(255) DEFAULT NULL,
    
    -- الربط
    `voucher_id` INT UNSIGNED DEFAULT NULL,
    `related_telegram_id` BIGINT DEFAULT NULL,
    
    -- Raw data
    `raw_payload` JSON NOT NULL COMMENT 'البيانات الكاملة من webhook',
    `signature_valid` TINYINT(1) DEFAULT NULL COMMENT 'هل التوقيع صحيح',
    
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_provider` (`provider`),
    KEY `idx_payment_id` (`payment_id`),
    KEY `idx_voucher_id` (`voucher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سجلات الدفع';

-- ============================================================
-- ROLLBACK SCRIPT (احفظه منفصلاً)
-- ============================================================
/*
-- Rollback Migration 001:


ALTER TABLE `super_distributors` 
DROP COLUMN `can_broadcast`,
DROP COLUMN `broadcast_allowed_types`;
*/
