d8d-admin-mobile-starter-public/server/migrations/021_createDeviceAlertRulesTable.ts

import type { MigrationLiveDefinition } from '@d8d-appcontainer/types'
import { DeleteStatus, EnableStatus} from "../../client/share/types.ts";

// 设备告警规则表迁移
const createDeviceAlertRulesTable: MigrationLiveDefinition = {
  name: 'create_device_alert_rules_table',
  up: async (api) => {
    await api.schema.createTable('device_alert_rules', (table) => {
      table.increments('id').primary();
      table.integer('device_id').unsigned().comment('关联的设备ID')
        .references('id').inTable('device_instances').onDelete('CASCADE');
      table.string('metric_type').comment('监控指标类型');
      table.decimal('min_value', 10, 2).nullable().comment('最小阈值');
      table.decimal('max_value', 10, 2).nullable().comment('最大阈值');
      table.integer('duration_seconds').defaultTo(60).comment('持续时间(秒)');
      table.integer('alert_level').comment('告警等级');
      table.string('alert_message').nullable().comment('告警消息模板');
      table.integer('is_enabled').defaultTo(EnableStatus.ENABLED).comment('是否启用 (0否 1是)');
      table.integer('is_deleted').defaultTo(DeleteStatus.NOT_DELETED).comment('是否删除 (0否 1是)');
      table.timestamps(true, true);
      
      // 添加索引
      table.index('device_id');
      table.index('metric_type');
      table.index('alert_level');
      table.index('is_enabled');
      table.index('is_deleted');
      
      // 为同一设备的相同指标类型添加唯一约束，指定更短的名称
      // table.unique(['device_id', 'metric_type', 'is_deleted'], 'uk_dev_alert_rules');
    });
  },
  down: async (api) => {
    await api.schema.dropTable('device_alert_rules');
  }
};

export default createDeviceAlertRulesTable