-- ============================================================
-- Migration 004: Batch Access Management System
-- ============================================================
-- الهدف: إضافة نظام إدارة صلاحيات الوصول الدفعي
-- التاريخ: 2025-12-04
-- ============================================================

-- ============================================================
-- 1. تحديث جدول accounts
-- ============================================================

-- إضافة حقول الوصول الافتراضي إذا لم تكن موجودة
ALTER TABLE `accounts`
ADD COLUMN IF NOT EXISTS `default_access_type` ENUM('lifetime', 'timed') NULL DEFAULT NULL 
    COMMENT 'نوع الوصول الافتراضي للزبائن' AFTER `secret_key`,
ADD COLUMN IF NOT EXISTS `default_access_days` INT UNSIGNED NULL DEFAULT NULL 
    COMMENT 'عدد أيام الوصول الافتراضي (إذا كان timed)' AFTER `default_access_type`,
ADD COLUMN IF NOT EXISTS `default_inherit_global` TINYINT(1) NOT NULL DEFAULT 1 
    COMMENT '1=يرث من الإعدادات العامة، 0=يستخدم القيم المحددة' AFTER `default_access_days`;

-- إضافة index للبحث السريع
ALTER TABLE `accounts`
ADD INDEX IF NOT EXISTS `idx_inherit_global` (`default_inherit_global`);

-- ============================================================
-- 2. إنشاء جدول admin_audit للتدقيق
-- ============================================================

CREATE TABLE IF NOT EXISTS `admin_audit` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_id` BIGINT UNSIGNED NOT NULL COMMENT 'معرف المدير الذي قام بالعملية',
    `admin_telegram_id` BIGINT NOT NULL COMMENT 'Telegram ID للمدير',
    `action` VARCHAR(100) NOT NULL COMMENT 'نوع العملية (set_users_access, set_default_account_access, etc)',
    `target_type` VARCHAR(50) NOT NULL COMMENT 'نوع الهدف (accounts, users, settings)',
    `target_ids` TEXT NULL COMMENT 'معرفات الأهداف المتأثرة (JSON array)',
    `old_values` TEXT NULL COMMENT 'القيم القديمة قبل التغيير (JSON)',
    `new_values` TEXT NULL COMMENT 'القيم الجديدة بعد التغيير (JSON)',
    `affected_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'عدد السجلات المتأثرة',
    `is_preview` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=معاينة فقط، 0=تنفيذ فعلي',
    `flags` VARCHAR(255) NULL COMMENT 'flags مستخدمة (force, notify, etc)',
    `status` ENUM('success', 'partial', 'failed') NOT NULL DEFAULT 'success',
    `error_message` TEXT NULL COMMENT 'رسالة الخطأ إن وجدت',
    `execution_time_ms` INT UNSIGNED NULL COMMENT 'وقت التنفيذ بالميلي ثانية',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    INDEX `idx_admin_id` (`admin_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_target_type` (`target_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='سجل تدقيق العمليات الإدارية';

-- ============================================================
-- 3. إنشاء جدول global_settings للإعدادات العامة
-- ============================================================

CREATE TABLE IF NOT EXISTS `global_settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT NULL,
    `setting_type` ENUM('string', 'int', 'bool', 'json') NOT NULL DEFAULT 'string',
    `description` VARCHAR(255) NULL,
    `updated_by` BIGINT UNSIGNED NULL COMMENT 'آخر مدير قام بالتحديث',
    `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 `global_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('default_access_type', 'lifetime', 'string', 'نوع الوصول الافتراضي العام (lifetime أو timed)'),
('default_access_days', '30', 'int', 'عدد أيام الوصول الافتراضي إذا كان timed'),
('batch_update_chunk_size', '500', 'int', 'حجم الدفعة عند التحديث الجماعي'),
('batch_update_notify_users', '0', 'bool', 'إرسال إشعارات للزبائن عند التحديث الجماعي');

-- ============================================================
-- 4. تحديث جدول user_accounts (التأكد من وجود الحقول)
-- ============================================================

-- التأكد من وجود حقول الوصول
ALTER TABLE `user_accounts`
MODIFY COLUMN `access_type` ENUM('lifetime', 'timed') NOT NULL DEFAULT 'lifetime',
MODIFY COLUMN `access_days` INT UNSIGNED NULL DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `access_expires_at` DATETIME NULL DEFAULT NULL 
    COMMENT 'تاريخ انتهاء الوصول (NULL = lifetime)' AFTER `access_days`;

-- إضافة index للبحث السريع
ALTER TABLE `user_accounts`
ADD INDEX IF NOT EXISTS `idx_access_type` (`access_type`),
ADD INDEX IF NOT EXISTS `idx_access_expires` (`access_expires_at`);

-- ============================================================
-- 5. إنشاء جدول batch_operations لتتبع العمليات الكبيرة
-- ============================================================

CREATE TABLE IF NOT EXISTS `batch_operations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `operation_id` VARCHAR(50) NOT NULL UNIQUE COMMENT 'معرف فريد للعملية',
    `admin_id` BIGINT UNSIGNED NOT NULL,
    `operation_type` VARCHAR(100) NOT NULL,
    `total_records` INT UNSIGNED NOT NULL DEFAULT 0,
    `processed_records` INT UNSIGNED NOT NULL DEFAULT 0,
    `success_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('pending', 'running', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `progress_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `started_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `error_log` TEXT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_operation_id` (`operation_id`),
    INDEX `idx_admin_id` (`admin_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='تتبع العمليات الدفعية الكبيرة';

-- ============================================================
-- 6. إنشاء stored procedures مساعدة
-- ============================================================

DELIMITER $$

-- دالة لحساب عدد السجلات المتأثرة (preview)
DROP PROCEDURE IF EXISTS `sp_preview_users_access_change`$$
CREATE PROCEDURE `sp_preview_users_access_change`(
    IN p_account_ids TEXT,
    IN p_new_access_type VARCHAR(20),
    IN p_force TINYINT(1)
)
BEGIN
    DECLARE v_account_list TEXT;
    
    SET v_account_list = REPLACE(REPLACE(p_account_ids, ' ', ''), ',', ',');
    
    -- عرض إحصائيات التغيير
    SELECT 
        COUNT(*) as total_affected,
        SUM(CASE WHEN ua.access_type = 'lifetime' THEN 1 ELSE 0 END) as lifetime_count,
        SUM(CASE WHEN ua.access_type = 'timed' THEN 1 ELSE 0 END) as timed_count,
        COUNT(DISTINCT ua.user_id) as unique_users,
        COUNT(DISTINCT ua.account_id) as unique_accounts
    FROM user_accounts ua
    WHERE FIND_IN_SET(ua.account_id, v_account_list) > 0
        AND (p_force = 1 OR ua.access_type != 'lifetime');
END$$

DELIMITER ;

-- ============================================================
-- 7. Views مساعدة
-- ============================================================

-- عرض الحسابات التي ترث الإعدادات العامة
CREATE OR REPLACE VIEW `v_accounts_inherit_global` AS
SELECT 
    a.id,
    a.slug,
    a.name,
    a.default_inherit_global,
    a.default_access_type,
    a.default_access_days,
    COUNT(DISTINCT ua.user_id) as active_users_count
FROM accounts a
LEFT JOIN user_accounts ua ON a.id = ua.account_id
WHERE a.default_inherit_global = 1
GROUP BY a.id, a.slug, a.name, a.default_inherit_global, a.default_access_type, a.default_access_days;

-- عرض آخر عمليات التدقيق
CREATE OR REPLACE VIEW `v_recent_admin_audits` AS
SELECT 
    aa.id,
    aa.admin_telegram_id,
    aa.action,
    aa.target_type,
    aa.affected_count,
    aa.is_preview,
    aa.status,
    aa.created_at,
    TIMESTAMPDIFF(SECOND, aa.created_at, NOW()) as seconds_ago
FROM admin_audit aa
ORDER BY aa.created_at DESC
LIMIT 100;

-- ============================================================
-- 8. Triggers للتدقيق التلقائي
-- ============================================================

-- Trigger عند تحديث accounts
DROP TRIGGER IF EXISTS `trg_accounts_audit`$$
DELIMITER $$
CREATE TRIGGER `trg_accounts_audit` AFTER UPDATE ON `accounts`
FOR EACH ROW
BEGIN
    IF OLD.default_access_type != NEW.default_access_type 
       OR OLD.default_access_days != NEW.default_access_days 
       OR OLD.default_inherit_global != NEW.default_inherit_global THEN
        
        INSERT INTO admin_audit (
            admin_id, 
            admin_telegram_id, 
            action, 
            target_type, 
            target_ids,
            old_values,
            new_values,
            affected_count
        ) VALUES (
            0, -- سيتم تحديثه من الكود
            0,
            'account_default_changed',
            'accounts',
            JSON_ARRAY(NEW.id),
            JSON_OBJECT(
                'access_type', OLD.default_access_type,
                'access_days', OLD.default_access_days,
                'inherit_global', OLD.default_inherit_global
            ),
            JSON_OBJECT(
                'access_type', NEW.default_access_type,
                'access_days', NEW.default_access_days,
                'inherit_global', NEW.default_inherit_global
            ),
            1
        );
    END IF;
END$$
DELIMITER ;

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

SELECT 'Migration 004 completed successfully!' as status;
