d8d-admin-mobile-starter-public/server/migrations/025_createInspectionTables.ts

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

// 巡检相关表迁移
const createInspectionTables: MigrationLiveDefinition = {
  name: 'create_inspection_tables',
  up: async (api) => {
    await api.schema.createTable('inspection_templates', (table) => {
      table.increments('id').primary();
      table.string('name').notNullable().comment('模板名称');
      table.string('description').nullable().comment('模板描述');
      table.jsonb('device_types').notNullable().comment('适用的设备类型');
      table.jsonb('metrics').notNullable().comment('检查指标配置');
      table.boolean('is_active').defaultTo(true).comment('是否启用');
      table.timestamps(true, true);
      
      table.index('is_active');
    });

    await api.schema.createTable('inspection_tasks', (table) => {
      table.increments('id').primary();
      table.integer('template_id').unsigned().comment('关联的模板ID')
        .references('id').inTable('inspection_templates').onDelete('CASCADE');
      table.string('name').notNullable().comment('任务名称');
      table.string('schedule_type').notNullable().comment('调度类型');
      table.string('cron_expression').nullable().comment('Cron表达式');
      table.integer('created_by').unsigned().comment('创建人ID')
        .references('id').inTable('users').onDelete('SET NULL');
      table.string('status').defaultTo('pending').comment('任务状态');
      table.timestamps(true, true);
      
      table.index('template_id');
      table.index('created_by');
      table.index('status');
    });

    await api.schema.createTable('inspection_results', (table) => {
      table.increments('id').primary();
      table.integer('task_id').unsigned().comment('关联的任务ID')
        .references('id').inTable('inspection_tasks').onDelete('CASCADE');
      table.integer('device_id').unsigned().comment('关联的设备ID')
        .references('id').inTable('device_instances').onDelete('CASCADE');
      table.jsonb('metrics_data').notNullable().comment('指标数据');
      table.string('overall_status').notNullable().comment('整体状态');
      table.timestamps(true, true);
      
      table.index('task_id');
      table.index('device_id');
      table.index('overall_status');
    });

    await api.schema.createTable('inspection_report_receivers', (table) => {
      table.increments('id').primary();
      table.integer('user_id').unsigned().comment('接收人ID')
        .references('id').inTable('users').onDelete('CASCADE');
      table.string('receive_method').notNullable().comment('接收方式');
      table.timestamps(true, true);
      
      table.index('user_id');
    });
  },
  down: async (api) => {
    await api.schema.dropTable('inspection_report_receivers');
    await api.schema.dropTable('inspection_results');
    await api.schema.dropTable('inspection_tasks');
    await api.schema.dropTable('inspection_templates');
  }
};

export default createInspectionTables