d8d-admin-mobile-starter-public/server/migrations/016_createDeviceInstancesTable.ts

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

// 定义设备表迁移
const createDeviceInstancesTable: MigrationLiveDefinition = {
  name: "create_device_instances_table",
  up: async (api) => {
    await api.schema.createTable('device_instances', (table) => {
      table.integer('id').unsigned().primary(); // 确保与zichan_info表的id字段类型一致
      table.foreign('id').references('id').inTable('zichan_info').onDelete('CASCADE');
      table.integer('type_id').unsigned().notNullable().comment('设备类型ID'); // 确保与device_types表的id字段类型一致
      table.foreign('type_id').references('id').inTable('device_types');
      table.enum('protocol', Object.values(DeviceProtocolType)).notNullable().comment('通信协议'); // 通信协议
      table.string('address').notNullable().comment('通信地址'); // 通信地址
      table.integer('collect_interval').defaultTo(60).comment('采集间隔(秒)'); // 采集间隔(秒)
      table.timestamp('last_collect_time').comment('最后采集时间'); // 最后采集时间
      table.text('remark').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('id'); // 确保id列有索引
      table.index('type_id');
      table.index('protocol');
      table.index('is_enabled');
      table.index('is_deleted');
    });
  },
  down: async (api) => {
    await api.schema.dropTable('device_instances');
  }
};

export default createDeviceInstancesTable