-- تحديث جدول alerts لدعم أنواع الإشعارات الجديدة
-- يتم تنفيذ هذا الملف مرة واحدة لتحديث قاعدة البيانات

-- تحديث enum لجدول alerts
ALTER TABLE `alerts` 
MODIFY COLUMN `alert_type` ENUM(
    'connection_lost',
    'low_battery',
    'geofence_enter',
    'geofence_exit',
    'high_speed',
    'gps_disabled',
    'gps_enabled',
    'internet_disconnected',
    'internet_connected',
    'internet_manually_disabled',
    'device_shutdown',
    'permission_denied',
    'permission_granted',
    'custom'
) NOT NULL;

-- تحديث enum لجدول alert_settings
ALTER TABLE `alert_settings` 
MODIFY COLUMN `alert_type` ENUM(
    'connection_lost',
    'low_battery',
    'geofence_enter',
    'geofence_exit',
    'high_speed',
    'gps_disabled',
    'gps_enabled',
    'internet_disconnected',
    'internet_connected',
    'internet_manually_disabled',
    'device_shutdown',
    'permission_denied',
    'permission_granted'
) NOT NULL;

-- إضافة فهارس جديدة لتحسين الأداء (مع التحقق من وجودها أولاً)
-- الفهرس الأول: idx_alert_type_created
SET @dbname = DATABASE();
SET @tablename = "alerts";
SET @indexname = "idx_alert_type_created";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (table_schema = @dbname)
      AND (table_name = @tablename)
      AND (index_name = @indexname)
  ) > 0,
  "SELECT 'Index idx_alert_type_created already exists' AS result",
  CONCAT("CREATE INDEX ", @indexname, " ON ", @tablename, " (`alert_type`, `created_at`)")
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- الفهرس الثاني: idx_device_alert_type
SET @indexname = "idx_device_alert_type";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (table_schema = @dbname)
      AND (table_name = @tablename)
      AND (index_name = @indexname)
  ) > 0,
  "SELECT 'Index idx_device_alert_type already exists' AS result",
  CONCAT("CREATE INDEX ", @indexname, " ON ", @tablename, " (`device_id`, `alert_type`, `created_at`)")
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
