-- ============================================================
-- Migration 002: Settings System & Process Jobs
-- Date: 2024-12-03
-- Author: Cascade AI
-- ============================================================

-- ============================================================
-- A) جدول الإعدادات المخصصة (Configurable Settings)
-- ============================================================
-- يدعم الإعدادات على مستويات متعددة:
-- global, account, superdist, user, user_account

CREATE TABLE IF NOT EXISTS `system_settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    
    -- المستوى والمعرف
    `level` ENUM('global', 'account', 'superdist', 'user', 'user_account') NOT NULL,
    `level_id` VARCHAR(100) DEFAULT NULL COMMENT 'معرف المستوى (null للـ global)',
    `level_id_secondary` VARCHAR(100) DEFAULT NULL COMMENT 'معرف ثانوي (للـ user_account)',
    
    -- الإعداد
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT NOT NULL,
    `value_type` ENUM('string', 'int', 'float', 'bool', 'json', 'array') NOT NULL DEFAULT 'string',
    
    -- التتبع
    `updated_by` INT UNSIGNED DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_setting` (`level`, `level_id`, `level_id_secondary`, `setting_key`),
    KEY `idx_level` (`level`),
    KEY `idx_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- إعدادات افتراضية
INSERT IGNORE INTO `system_settings` (`level`, `setting_key`, `setting_value`, `value_type`) VALUES
('global', 'activation_default_type', 'lifetime', 'string'),
('global', 'activation_default_days', '30', 'int'),
('global', 'voucher_redeem_allowed', 'true', 'bool'),
('global', 'broadcast_allowed_types', '["text","stickers","photos","videos","documents","audio"]', 'json'),
('global', 'max_codes_per_generation', '100', 'int'),
('global', 'code_expiry_days', '365', 'int');

-- ============================================================
-- B) جدول مهام معالجة الأكواد (Process Jobs)
-- ============================================================

CREATE TABLE IF NOT EXISTS `process_jobs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    
    -- نوع المهمة
    `job_type` VARCHAR(50) NOT NULL DEFAULT 'codes_window',
    `mode` ENUM('since', 'last_n') NOT NULL,
    `mode_value` VARCHAR(100) NOT NULL COMMENT 'تاريخ أو عدد',
    `action` ENUM('expire_unused', 'disable_unused', 'report_only') NOT NULL,
    
    -- النطاق
    `scope_account_id` INT UNSIGNED DEFAULT NULL,
    `scope_superdist_id` INT UNSIGNED DEFAULT NULL,
    `scope_json` JSON DEFAULT NULL COMMENT 'معلومات النطاق الإضافية',
    
    -- التنفيذ
    `initiated_by` INT UNSIGNED NOT NULL COMMENT 'admin telegram_id',
    `status` ENUM('pending', 'running', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `progress_percent` TINYINT UNSIGNED DEFAULT 0,
    
    -- النتائج
    `total_codes` INT UNSIGNED DEFAULT 0,
    `processed_codes` INT UNSIGNED DEFAULT 0,
    `affected_codes` INT UNSIGNED DEFAULT 0,
    `skipped_codes` INT UNSIGNED DEFAULT 0,
    `result_summary_json` JSON DEFAULT NULL,
    
    -- الملفات
    `report_path` VARCHAR(500) DEFAULT NULL,
    `csv_path` VARCHAR(500) DEFAULT NULL,
    
    -- التوقيت
    `started_at` TIMESTAMP NULL DEFAULT NULL,
    `finished_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_initiated_by` (`initiated_by`),
    KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- C) تحديث جدول activation_codes (إضافة حقول ناقصة)
-- ============================================================

-- إضافة access_type إذا لم يكن موجوداً
SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'activation_codes' 
    AND column_name = 'access_type'
);
SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `activation_codes` ADD COLUMN `access_type` ENUM(''lifetime'',''timed'') DEFAULT ''lifetime'' AFTER `status`',
    'SELECT ''Column access_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- إضافة access_days إذا لم يكن موجوداً
SET @column_exists2 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'activation_codes' 
    AND column_name = 'access_days'
);
SET @sql2 = IF(@column_exists2 = 0,
    'ALTER TABLE `activation_codes` ADD COLUMN `access_days` INT UNSIGNED DEFAULT NULL AFTER `access_type`',
    'SELECT ''Column access_days already exists'' AS info'
);
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- إضافة expires_at إذا لم يكن موجوداً
SET @column_exists3 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'activation_codes' 
    AND column_name = 'expires_at'
);
SET @sql3 = IF(@column_exists3 = 0,
    'ALTER TABLE `activation_codes` ADD COLUMN `expires_at` DATETIME DEFAULT NULL AFTER `access_days`',
    'SELECT ''Column expires_at already exists'' AS info'
);
PREPARE stmt3 FROM @sql3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

-- ============================================================
-- D) تحديث جدول user_accounts (إضافة حقول التتبع)
-- ============================================================

-- إضافة activation_code_id إذا لم يكن موجوداً
SET @column_exists4 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'user_accounts' 
    AND column_name = 'activation_code_id'
);
SET @sql4 = IF(@column_exists4 = 0,
    'ALTER TABLE `user_accounts` ADD COLUMN `activation_code_id` INT UNSIGNED DEFAULT NULL AFTER `superdist_id`',
    'SELECT ''Column activation_code_id already exists'' AS info'
);
PREPARE stmt4 FROM @sql4;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;

-- إضافة access_type إذا لم يكن موجوداً
SET @column_exists5 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'user_accounts' 
    AND column_name = 'access_type'
);
SET @sql5 = IF(@column_exists5 = 0,
    'ALTER TABLE `user_accounts` ADD COLUMN `access_type` ENUM(''lifetime'',''timed'') DEFAULT ''lifetime'' AFTER `status`',
    'SELECT ''Column access_type in user_accounts already exists'' AS info'
);
PREPARE stmt5 FROM @sql5;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;

-- إضافة access_expires_at إذا لم يكن موجوداً
SET @column_exists6 = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'user_accounts' 
    AND column_name = 'access_expires_at'
);
SET @sql6 = IF(@column_exists6 = 0,
    'ALTER TABLE `user_accounts` ADD COLUMN `access_expires_at` DATETIME DEFAULT NULL AFTER `access_type`',
    'SELECT ''Column access_expires_at already exists'' AS info'
);
PREPARE stmt6 FROM @sql6;
EXECUTE stmt6;
DEALLOCATE PREPARE stmt6;

-- ============================================================
-- E) جدول سجل معالجة الأكواد التفصيلي
-- ============================================================

CREATE TABLE IF NOT EXISTS `process_job_details` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `job_id` INT UNSIGNED NOT NULL,
    `activation_code_id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `account_slug` VARCHAR(100) DEFAULT NULL,
    `superdist_id` INT UNSIGNED DEFAULT NULL,
    `old_status` VARCHAR(20) DEFAULT NULL,
    `new_status` VARCHAR(20) DEFAULT NULL,
    `action_taken` VARCHAR(50) DEFAULT NULL,
    `reason` VARCHAR(255) DEFAULT NULL,
    `processed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_job_id` (`job_id`),
    KEY `idx_code` (`code`),
    CONSTRAINT `fk_pjd_job` FOREIGN KEY (`job_id`) REFERENCES `process_jobs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- F) فهارس إضافية للأداء
-- ============================================================

-- فهرس على activation_codes للبحث السريع
CREATE INDEX IF NOT EXISTS `idx_ac_status_created` ON `activation_codes`(`status`, `created_at`);
CREATE INDEX IF NOT EXISTS `idx_ac_account_status` ON `activation_codes`(`account_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_ac_superdist_status` ON `activation_codes`(`superdist_id`, `status`);

-- ============================================================
-- تم الانتهاء من Migration 002
-- ============================================================
