-- ============================================================
-- نظام سجل الأحداث الشامل (Activity Logs System)
-- Comprehensive Activity Logging System
-- ============================================================

-- 1️⃣ إنشاء جدول سجل الأحداث
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NULL COMMENT 'معرف المستخدم الذي قام بالعملية',
  `user_name` VARCHAR(120) NULL COMMENT 'اسم المستخدم',
  `action_type` ENUM('create','update','delete','login','logout','view','export','import','approve','reject') NOT NULL COMMENT 'نوع العملية',
  `module` VARCHAR(50) NOT NULL COMMENT 'القسم/الموديول',
  `record_id` INT(11) NULL COMMENT 'معرف السجل المتأثر',
  `description` TEXT NOT NULL COMMENT 'وصف العملية بالعربية',
  `old_values` JSON NULL COMMENT 'القيم القديمة قبل التعديل',
  `new_values` JSON NULL COMMENT 'القيم الجديدة بعد التعديل',
  `ip_address` VARCHAR(45) NULL COMMENT 'عنوان IP',
  `user_agent` TEXT NULL COMMENT 'معلومات المتصفح',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'تاريخ ووقت العملية',
  PRIMARY KEY (`id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_action_type` (`action_type`),
  INDEX `idx_module` (`module`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_module_record` (`module`, `record_id`),
  INDEX `idx_user_action` (`user_id`, `action_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='سجل جميع أحداث وعمليات النظام';

-- 2️⃣ إضافة Foreign Key للمستخدم
ALTER TABLE `activity_logs`
ADD CONSTRAINT `fk_activity_user`
FOREIGN KEY (`user_id`) REFERENCES `staff_users` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE;

-- 3️⃣ إنشاء view لعرض السجلات بتفاصيل أكثر
CREATE OR REPLACE VIEW `v_activity_logs_detailed` AS
SELECT 
    al.id,
    al.user_id,
    al.user_name,
    al.action_type,
    CASE al.action_type
        WHEN 'create' THEN 'إضافة'
        WHEN 'update' THEN 'تعديل'
        WHEN 'delete' THEN 'حذف'
        WHEN 'login' THEN 'تسجيل دخول'
        WHEN 'logout' THEN 'تسجيل خروج'
        WHEN 'view' THEN 'عرض'
        WHEN 'export' THEN 'تصدير'
        WHEN 'import' THEN 'استيراد'
        WHEN 'approve' THEN 'موافقة'
        WHEN 'reject' THEN 'رفض'
    END as action_type_ar,
    al.module,
    CASE al.module
        WHEN 'students' THEN 'الطلاب'
        WHEN 'staff' THEN 'المدراء'
        WHEN 'employees' THEN 'الموظفين'
        WHEN 'attendance' THEN 'الحضور'
        WHEN 'invoices' THEN 'الفواتير'
        WHEN 'payments' THEN 'المدفوعات'
        WHEN 'expenses' THEN 'المصروفات'
        WHEN 'salaries' THEN 'الرواتب'
        WHEN 'drivers' THEN 'السائقين'
        WHEN 'transport' THEN 'النقل'
        WHEN 'roles' THEN 'الأدوار'
        WHEN 'settings' THEN 'الإعدادات'
        WHEN 'reports' THEN 'التقارير'
        ELSE al.module
    END as module_ar,
    al.record_id,
    al.description,
    al.old_values,
    al.new_values,
    al.ip_address,
    al.user_agent,
    al.created_at,
    DATE(al.created_at) as activity_date,
    TIME(al.created_at) as activity_time,
    s.email as user_email,
    s.role_id as user_role_id,
    r.label_ar as user_role
FROM 
    activity_logs al
    LEFT JOIN staff_users s ON al.user_id = s.id
    LEFT JOIN roles r ON s.role_id = r.id
ORDER BY 
    al.created_at DESC;

-- 4️⃣ إنشاء stored procedure لتنظيف السجلات القديمة
DELIMITER $$

CREATE PROCEDURE `sp_cleanup_old_logs`(IN days_to_keep INT)
BEGIN
    DELETE FROM activity_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    SELECT ROW_COUNT() as deleted_count;
END$$

DELIMITER ;

-- 5️⃣ إنشاء stored procedure للإحصائيات
DELIMITER $$

CREATE PROCEDURE `sp_activity_statistics`(IN date_from DATE, IN date_to DATE)
BEGIN
    -- إحصائيات العمليات حسب النوع
    SELECT 
        action_type,
        CASE action_type
            WHEN 'create' THEN 'إضافة'
            WHEN 'update' THEN 'تعديل'
            WHEN 'delete' THEN 'حذف'
            WHEN 'login' THEN 'تسجيل دخول'
            WHEN 'logout' THEN 'تسجيل خروج'
            ELSE action_type
        END as action_type_ar,
        COUNT(*) as count
    FROM activity_logs
    WHERE DATE(created_at) BETWEEN date_from AND date_to
    GROUP BY action_type
    ORDER BY count DESC;
    
    -- إحصائيات العمليات حسب القسم
    SELECT 
        module,
        COUNT(*) as count
    FROM activity_logs
    WHERE DATE(created_at) BETWEEN date_from AND date_to
    GROUP BY module
    ORDER BY count DESC;
    
    -- أكثر المستخدمين نشاطاً
    SELECT 
        user_id,
        user_name,
        COUNT(*) as activity_count
    FROM activity_logs
    WHERE DATE(created_at) BETWEEN date_from AND date_to
    GROUP BY user_id, user_name
    ORDER BY activity_count DESC
    LIMIT 10;
END$$

DELIMITER ;

-- 6️⃣ إدراج بيانات تجريبية (اختياري)
INSERT INTO `activity_logs` (`user_id`, `user_name`, `action_type`, `module`, `record_id`, `description`, `ip_address`) VALUES
(3, 'وسام المدير', 'login', 'auth', NULL, 'تسجيل دخول للنظام', '127.0.0.1'),
(3, 'وسام المدير', 'view', 'students', NULL, 'عرض قائمة الطلاب', '127.0.0.1'),
(3, 'وسام المدير', 'create', 'students', 1, 'إضافة طالب جديد: أحمد محمد', '127.0.0.1'),
(3, 'وسام المدير', 'view', 'invoices', NULL, 'عرض قائمة الفواتير', '127.0.0.1');

-- ============================================================
-- ✅ تم إنشاء نظام سجل الأحداث بنجاح
-- ============================================================

-- ملاحظات:
-- 1. يحفظ جميع العمليات في النظام
-- 2. يدعم JSON للقيم القديمة والجديدة
-- 3. Indexes محسّنة للأداء
-- 4. View جاهز للعرض بالعربية
-- 5. Stored procedures للإحصائيات والتنظيف
