d8d-admin-mobile-starter-public/server/migrations/020_createDeviceMonitorDataTable.ts

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

// 设备监控数据表迁移
const createDeviceMonitorDataTable: MigrationLiveDefinition = {
  name: 'create_device_monitor_data_table',
  up: async (api) => {
    await api.schema.createTable('device_monitor_data', (table) => {
      table.increments('id').primary();
      table.integer('device_id').unsigned().comment('关联的设备ID')
        .references('id').inTable('device_instances').onDelete('CASCADE');
      table.string('metric_type').comment('监控指标类型(temperature/humidity/cpu_usage等)');
      table.decimal('metric_value', 10, 2).comment('监控值');
      table.string('unit').comment('单位');
      table.integer('status').defaultTo(DeviceStatus.NORMAL).comment('状态');
      table.timestamp('collect_time').comment('采集时间');
      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('collect_time');
      table.index('status');
      table.index('is_deleted');
    });
  },
  down: async (api) => {
    await api.schema.dropTable('device_monitor_data');
  }
};

export default createDeviceMonitorDataTable