yunrui_asset/backend/routes/devices.js

const logger = require('../utils/logger').module('DevicesRoute');
const express = require('express');
const router = express.Router();
const { Op } = require('sequelize');
const { sequelize, dbDialect } = require('../db');
const fs = require('fs');
const path = require('path');
const csv = require('csv-parser');
const { createObjectCsvWriter } = require('csv-writer');
const iconv = require('iconv-lite');
const { Room, Rack, Device, DeviceField, Ticket, DevicePort, Cable, NetworkCard, InventoryRecord } = require('../models');
const {
  logDeviceOperation,
  generateDeviceDescription,
  buildDeviceMetadata,
} = require('../utils/operationLogger');
const { validateBody, validateQuery } = require('../middleware/validation');
const {
  batchDeviceIdsSchema,
  batchStatusSchema,
  batchMoveSchema,
  queryDeviceSchema,
} = require('../validation/deviceSchema');
const { createDeviceSchema } = require('../validation/dynamicDeviceSchema');

const PREVIEW_COUNT = 20;

async function checkPositionAvailable(
  rackId,
  position,
  height,
  excludeDeviceId = null,
  transaction = null
) {
  const pos = parseInt(position, 10);
  if (!pos || pos <= 0) {
    return { available: true, reason: null };
  }

  const deviceHeight = parseInt(height, 10) || 1;
  const startU = pos;
  const endU = pos + deviceHeight - 1;

  const queryOptions = {
    where: {
      rackId: rackId,
      position: { [Op.ne]: null },
    },
    attributes: ['deviceId', 'position', 'height'],
  };

  if (transaction) {
    queryOptions.transaction = transaction;
    // 使用悲观锁防止竞态条件
    queryOptions.lock = transaction.LOCK ? transaction.LOCK.UPDATE : 'UPDATE';
  }

  const existingDevices = await Device.findAll(queryOptions);

  for (const device of existingDevices) {
    if (excludeDeviceId && device.deviceId === excludeDeviceId) {
      continue;
    }

    const existStart = device.position;
    const existEnd = device.position + (device.height || 1) - 1;

    if (!(endU < existStart || startU > existEnd)) {
      return {
        available: false,
        reason: `U位冲突：机柜中已有设备 ${device.deviceId} 占用 U${existStart}${existEnd !== existStart ? '-' + existEnd : ''}，与当前位置范围 U${startU}-U${endU} 冲突`,
      };
    }
  }

  return { available: true, reason: null };
}

async function checkBatchPositions(rackId, devices, excludeDeviceIds = [], transaction = null) {
  const conflicts = [];
  const sortedDevices = [...devices].sort((a, b) => a.position - b.position);

  for (let i = 0; i < sortedDevices.length; i++) {
    const device = sortedDevices[i];
    if (!device.position || device.position <= 0) {
      continue;
    }

    const startU = device.position;
    const endU = device.position + (device.height || 1) - 1;

    for (let j = i + 1; j < sortedDevices.length; j++) {
      const other = sortedDevices[j];
      if (!other.position || other.position <= 0) {
        continue;
      }

      const otherStart = other.position;
      const otherEnd = other.position + (other.height || 1) - 1;

      if (!(endU < otherStart || startU > otherEnd)) {
        conflicts.push(
          `导入数据内部冲突：设备 ${device.deviceId || '新设备'}(U${startU}-U${endU}) 与 设备 ${other.deviceId || '新设备'}(U${otherStart}-U${otherEnd}) U位重叠`
        );
      }
    }
  }

  const queryOptions = {
    where: {
      rackId: rackId,
      position: { [Op.ne]: null },
    },
    attributes: ['deviceId', 'position', 'height'],
  };

  if (transaction) {
    queryOptions.transaction = transaction;
    // 使用悲观锁防止竞态条件
    queryOptions.lock = transaction.LOCK ? transaction.LOCK.UPDATE : 'UPDATE';
  }

  const existingDevices = await Device.findAll(queryOptions);

  for (const existing of existingDevices) {
    if (excludeDeviceIds.includes(existing.deviceId)) {
      continue;
    }

    const existStart = existing.position;
    const existEnd = existing.position + (existing.height || 1) - 1;

    for (const device of devices) {
      if (!device.position || device.position <= 0) {
        continue;
      }

      const startU = device.position;
      const endU = device.position + (device.height || 1) - 1;

      if (!(endU < existStart || startU > existEnd)) {
        conflicts.push(
          `与已有设备冲突：机柜中已有设备 ${existing.deviceId} 占用 U${existStart}${existEnd !== existStart ? '-' + existEnd : ''}，与导入设备 ${device.deviceId || '新设备'}(U${startU}-U${endU}) 冲突`
        );
      }
    }
  }

  return conflicts;
}

router.post('/import-preview', async (req, res) => {
  try {
    if (!req.files || !req.files.csvFile) {
      return res.status(400).json({ error: '请上传CSV文件' });
    }

    const csvFile = req.files.csvFile;
    const stats = { total: 0, valid: 0, invalid: 0, errors: [] };

    if (!fs.existsSync(path.join(__dirname, '../temp'))) {
      fs.mkdirSync(path.join(__dirname, '../temp'), { recursive: true });
    }

    const filePath = path.join(__dirname, '../temp', `preview_${Date.now()}_${csvFile.name}`);
    await csvFile.mv(filePath);

    const results = [];
    const stream = fs.createReadStream(filePath).pipe(iconv.decodeStream('gbk')).pipe(csv());

    await new Promise((resolve, reject) => {
      stream
        .on('data', data => results.push(data))
        .on('end', resolve)
        .on('error', reject);
    });

    fs.unlinkSync(filePath);

    const [rooms, racks, deviceFields] = await Promise.all([
      Room.findAll(),
      Rack.findAll({ include: [{ model: Room }] }),
      DeviceField.findAll({ order: [['order', 'ASC']] }),
    ]);

    const roomNameToIdMap = new Map(rooms.map(room => [room.name, room.roomId]));
    const rackLocationMap = new Map(
      racks.map(rack => [`${rack.Room?.name || ''}_${rack.name}`, rack.rackId])
    );
    const fieldMapping = {};
    const fieldNameToDisplayName = {};
    deviceFields.forEach(field => {
      fieldMapping[field.displayName] = field;
      fieldNameToDisplayName[field.fieldName] = field.displayName;
    });

    const extractFieldName = fieldNameWithFormat => {
      const match = fieldNameWithFormat.match(/^(.+?)(\(必填\)|\(可选\)|\([a-zA-Z0-9\-\/]+\))$/);
      return match ? match[1].trim() : fieldNameWithFormat;
    };

    const validTypes = ['server', 'switch', 'router', 'storage', 'other'];
    const validStatuses = ['running', 'maintenance', 'offline', 'fault'];
    const baseFieldNames = [
      'deviceId',
      'name',
      'type',
      'model',
      'serialNumber',
      'rackId',
      'position',
      'height',
      'powerConsumption',
      'ipAddress',
      'status',
      'purchaseDate',
      'warrantyExpiry',
      'description',
    ];

    const previewData = [];
    const allDeviceIds = new Set();
    const allSerialNumbers = new Set();
    const rackDevicesMap = new Map();

    stats.total = results.length;

    for (let i = 0; i < results.length; i++) {
      const row = results[i];
      const rowNum = i + 2;
      const rowErrors = [];
      const parsedRow = { _rowNum: rowNum };

      try {
        const fieldValueMap = {};
        Object.entries(row).forEach(([displayName, value]) => {
          const originalFieldName = extractFieldName(displayName);
          fieldValueMap[originalFieldName] = value;
          fieldValueMap[displayName] = value;
        });

        const getFieldValue = fieldName => {
          const displayName = fieldNameToDisplayName[fieldName];
          return displayName ? fieldValueMap[displayName] : undefined;
        };

        const trulyRequiredFields = [];
        deviceFields.forEach(field => {
          if (field.fieldName === 'deviceId') {
            return;
          }
          if (field.fieldName === 'rackId') {
            if (field.required) {
              trulyRequiredFields.push('所在机房名称', '所在机柜名称');
            }
            return;
          }
          if (field.required) {
            trulyRequiredFields.push(field.displayName);
          }
        });

        const missingFields = trulyRequiredFields.filter(fieldName => {
          const value = fieldValueMap[fieldName];
          return !value || (typeof value === 'string' && value.trim() === '');
        });

        if (missingFields.length > 0) {
          rowErrors.push(`缺少必填字段：${missingFields.join('、')}`);
        }

        const deviceType = getFieldValue('type');
        if (deviceType && !validTypes.includes(deviceType)) {
          rowErrors.push(`设备类型无效：${deviceType}`);
        }

        const deviceId = getFieldValue('deviceId');
        if (deviceId && deviceId.trim() !== '') {
          if (allDeviceIds.has(deviceId)) {
            rowErrors.push(`设备ID重复：${deviceId}`);
          }
          allDeviceIds.add(deviceId);
        }

        const serialNumber = getFieldValue('serialNumber');
        if (!serialNumber || serialNumber.trim() === '') {
          rowErrors.push('序列号不能为空');
        } else if (allSerialNumbers.has(serialNumber)) {
          rowErrors.push(`序列号重复：${serialNumber}`);
        } else {
          allSerialNumbers.add(serialNumber);
        }

        const roomName = fieldValueMap['所在机房名称'];
        const rackName = fieldValueMap['所在机柜名称'];
        if (!roomName?.trim()) {
          rowErrors.push('所在机房名称不能为空');
        } else if (!roomNameToIdMap.get(roomName.trim())) {
          rowErrors.push(`机房不存在：${roomName}`);
        }

        if (!rackName?.trim()) {
          rowErrors.push('所在机柜名称不能为空');
        }

        const status = getFieldValue('status');
        if (status && !validStatuses.includes(status)) {
          rowErrors.push(`状态值无效：${status}`);
        }

        const position = getFieldValue('position');
        const height = getFieldValue('height');
        const powerConsumption = getFieldValue('powerConsumption');

        if (position !== undefined && position !== '' && isNaN(Number(position))) {
          rowErrors.push(`位置必须是数字：${position}`);
        }
        if (height !== undefined && height !== '' && isNaN(Number(height))) {
          rowErrors.push(`高度必须是数字：${height}`);
        }
        if (
          powerConsumption !== undefined &&
          powerConsumption !== '' &&
          isNaN(Number(powerConsumption))
        ) {
          rowErrors.push(`功率必须是数字：${powerConsumption}`);
        }

        const purchaseDateValue = getFieldValue('purchaseDate');
        const warrantyExpiryValue = getFieldValue('warrantyExpiry');
        const purchaseDate = purchaseDateValue ? new Date(purchaseDateValue) : null;
        const warrantyExpiry = warrantyExpiryValue ? new Date(warrantyExpiryValue) : null;

        if (purchaseDateValue && isNaN(purchaseDate.getTime())) {
          rowErrors.push(`购买日期格式无效：${purchaseDateValue}`);
        }
        if (warrantyExpiryValue && isNaN(warrantyExpiry.getTime())) {
          rowErrors.push(`保修日期格式无效：${warrantyExpiryValue}`);
        }
        if (purchaseDate && warrantyExpiry && warrantyExpiry <= purchaseDate) {
          rowErrors.push('保修日期必须晚于购买日期');
        }

        Object.keys(row).forEach(displayName => {
          const originalDisplayName = extractFieldName(displayName);
          const fieldConfig = fieldMapping[originalDisplayName];
          if (fieldConfig && !baseFieldNames.includes(fieldConfig.fieldName)) {
            parsedRow[fieldConfig.fieldName] = row[displayName];
          }
        });

        parsedRow.name = getFieldValue('name') || '';
        parsedRow.type = deviceType || '';
        parsedRow.model = getFieldValue('model') || '';
        parsedRow.serialNumber = serialNumber || '';
        parsedRow.roomName = roomName || '';
        parsedRow.rackName = rackName || '';
        parsedRow.status = status || '';
        parsedRow.position = position || '';
        parsedRow.height = height || '';
        parsedRow.powerConsumption = powerConsumption || '';
        parsedRow.ipAddress = getFieldValue('ipAddress') || '';
        parsedRow.description = getFieldValue('description') || '';

        if (rowErrors.length === 0) {
          stats.valid++;
          parsedRow._hasError = false;

          const rackId = rackLocationMap.get(`${roomName?.trim()}_${rackName?.trim()}`);
          if (rackId && position) {
            const posNum = parseInt(position);
            const heightNum = parseInt(height) || 1;
            if (!rackDevicesMap.has(rackId)) {
              rackDevicesMap.set(rackId, []);
            }
            rackDevicesMap.get(rackId).push({
              rowNum,
              deviceId: getFieldValue('deviceId') || null,
              position: posNum,
              height: heightNum,
            });
          }
        } else {
          stats.invalid++;
          parsedRow._hasError = true;
          parsedRow._errors = rowErrors;
          stats.errors.push({ row: rowNum, errors: rowErrors });
        }

        if (previewData.length < PREVIEW_COUNT) {
          previewData.push(parsedRow);
        }
      } catch (error) {
        stats.invalid++;
        const errorMsg = error.message || '未知错误';
        stats.errors.push({ row: rowNum, errors: [errorMsg] });
        if (previewData.length < PREVIEW_COUNT) {
          previewData.push({
            _rowNum: rowNum,
            _hasError: true,
            _errors: [errorMsg],
            name: row['设备名称'] || '',
            type: row['设备类型'] || '',
            serialNumber: row['序列号'] || '',
            roomName: row['所在机房名称'] || '',
            rackName: row['所在机柜名称'] || '',
            status: row['状态'] || '',
          });
        }
      }
    }

    const positionConflictRows = new Set();

    for (const [rackId, devices] of rackDevicesMap) {
      const existingDevices = await Device.findAll({
        where: { rackId, position: { [Op.ne]: null } },
        attributes: ['deviceId', 'position', 'height'],
      });

      for (const newDevice of devices) {
        const startU = newDevice.position;
        const endU = newDevice.position + newDevice.height - 1;
        let hasConflict = false;

        for (const existing of existingDevices) {
          const existStart = existing.position;
          const existEnd = existing.position + (existing.height || 1) - 1;

          if (!(endU < existStart || startU > existEnd)) {
            const conflictMsg = `U位冲突：机柜中已有设备 ${existing.deviceId} 占用 U${existStart}${existEnd !== existStart ? '-' + existEnd : ''}，与第 ${newDevice.rowNum} 行设备(导入)占用 U${startU}${endU !== startU ? '-' + endU : ''} 冲突`;
            stats.errors.push({ row: newDevice.rowNum, errors: [conflictMsg] });
            hasConflict = true;
            break;
          }
        }

        if (!hasConflict) {
          for (const otherDevice of devices) {
            if (otherDevice === newDevice) {
              continue;
            }

            const otherStart = otherDevice.position;
            const otherEnd = otherDevice.position + otherDevice.height - 1;

            if (!(endU < otherStart || startU > otherEnd)) {
              const conflictMsg = `U位冲突：第 ${newDevice.rowNum} 行设备与第 ${otherDevice.rowNum} 行设备在 U位上重叠 (U${startU}-U${endU} vs U${otherStart}-U${otherEnd})`;
              stats.errors.push({ row: newDevice.rowNum, errors: [conflictMsg] });
              hasConflict = true;
              break;
            }
          }
        }

        if (hasConflict) {
          positionConflictRows.add(newDevice.rowNum);
        }
      }
    }

    if (positionConflictRows.size > 0) {
      stats.invalid += positionConflictRows.size;
      stats.valid -= positionConflictRows.size;

      for (const rowNum of positionConflictRows) {
        const previewItem = previewData.find(item => item._rowNum === rowNum);
        if (previewItem) {
          previewItem._hasError = true;
          const existingErrors = previewItem._errors || [];
          const positionErrors = stats.errors.filter(e => e.row === rowNum).flatMap(e => e.errors);
          previewItem._errors = [...existingErrors, ...positionErrors];
        }
      }
    }

    const fieldList = deviceFields
      .filter(field => field.visible && field.fieldName !== 'deviceId')
      .map(field => ({
        fieldName: field.fieldName,
        displayName: field.displayName,
        fieldType: field.fieldType,
        required: field.required,
      }));

    res.json({
      success: true,
      data: {
        preview: previewData,
        total: stats.total,
        previewCount: PREVIEW_COUNT,
        statistics: {
          total: stats.total,
          valid: stats.valid,
          invalid: stats.invalid,
        },
        errors: stats.errors.slice(0, 50),
        fieldList,
      },
    });
  } catch (error) {
    logger.error('预览设备数据失败', { error: error.message, stack: error.stack });
    res.status(500).json({
      error: error.message || '预览过程中发生未知错误',
    });
  }
});

router.get('/', validateQuery(queryDeviceSchema), async (req, res) => {
  try {
    const { keyword, status, type, rackId, roomId, isIdle } = req.query;
    const page = Math.max(1, parseInt(req.query.page, 10) || 1);
    const pageSize = Math.min(100, Math.max(1, parseInt(req.query.pageSize, 10) || 10));
    const offset = (page - 1) * pageSize;

    // 构建查询条件
    const where = {};

    // 关键词搜索（所有文本字段 + 自定义字段）
    if (keyword) {
      logger.info('搜索关键词', { data: keyword });
      logger.info('数据库类型', { data: dbDialect });

      // 转义关键词中的特殊字符，防止SQL注入
      // 转义单引号（SQL注入）和 LIKE 通配符（%、_）
      const escapedKeyword = keyword
        .replace(/\\/g, '\\\\')
        .replace(/'/g, "''")
        .replace(/%/g, '\\%')
        .replace(/_/g, '\\_');

      // 基础字段搜索条件（只包含文本类型字段）
      const searchConditions = [
        { deviceId: { [Op.like]: `%${escapedKeyword}%` } },
        { name: { [Op.like]: `%${escapedKeyword}%` } },
        { type: { [Op.like]: `%${escapedKeyword}%` } },
        { model: { [Op.like]: `%${escapedKeyword}%` } },
        { serialNumber: { [Op.like]: `%${escapedKeyword}%` } },
        { ipAddress: { [Op.like]: `%${escapedKeyword}%` } },
        { description: { [Op.like]: `%${escapedKeyword}%` } },
      ];

      // 动态获取文本类型的自定义字段
      const customFields = await DeviceField.findAll({
        where: {
          isSystem: false,
          fieldType: { [Op.in]: ['string', 'textarea'] },
        },
      });

      logger.info('找到的自定义字段', { data: customFields.map(f => f.fieldName) });

      // 构建自定义字段搜索条件（使用原始SQL，兼容SQLite和MySQL）
      if (customFields.length > 0) {
        // 使用原始SQL查询JSON字段
        const jsonConditions = customFields.map(field => {
          // 转义 fieldName 中的特殊字符，防止通过字段名注入
          const safeFieldName = field.fieldName
            .replace(/\\/g, '\\\\')
            .replace(/"/g, '\\"')
            .replace(/'/g, "''");

          // 使用 sequelize.literal 构建原始SQL条件
          if (dbDialect === 'mysql') {
            return sequelize.literal(
              `JSON_UNQUOTE(JSON_EXTRACT(customFields, '$."${safeFieldName}"')) LIKE '%${escapedKeyword}%' ESCAPE '\\\\'`
            );
          } else {
            // SQLite: json_extract 返回 JSON 格式值，需要转换为文本进行比较
            // 使用 ->> 操作符或 CAST 获取纯文本值
            return sequelize.literal(
              `CAST(json_extract(customFields, '$.${safeFieldName}') AS TEXT) LIKE '%${escapedKeyword}%' ESCAPE '\\'`
            );
          }
        });
        searchConditions.push(...jsonConditions);
      }

      // 合并所有搜索条件
      where[Op.or] = searchConditions;
      logger.info('搜索条件数量', { data: searchConditions.length });
    }

    // 状态筛选
    if (status && status !== 'all') {
      where.status = status;
    }

    // 分类筛选
    if (type && type !== 'all') {
      where.type = type;
    }

    // 机柜筛选（用于机柜可视化功能）
    if (rackId && rackId !== 'all') {
      where.rackId = rackId;
    }

    // 机房筛选 - 通过机柜关联查询
    // 注意：使用 include 中嵌套 where 而不是 $Rack.roomId$ 语法

    // 调试日志
    if (process.env.NODE_ENV === 'development') {
      logger.info('=== 设备查询调试 ===');
      logger.info('接收参数', { roomId, rackId });
      logger.info('查询条件 - where', { data: JSON.stringify(where) });
      logger.info('机房筛选条件 - Rack.where', { data: roomId && roomId !== 'all' ? { roomId: roomId } : undefined });
    }

    // 空闲设备筛选
    if (isIdle !== undefined && isIdle !== '') {
      where.isIdle = isIdle === 'true' || isIdle === true;
    }

    // 执行查询 - 优化：使用 JOIN 避免 N+1 查询问题
    const { count, rows } = await Device.findAndCountAll({
      where,
      include: [
        {
          model: Rack,
          where: roomId && roomId !== 'all' ? { roomId: roomId } : undefined,
          include: [{ model: Room }],
          separate: false, // 强制使用 JOIN 而不是单独查询
        },
      ],
      offset,
      limit: parseInt(pageSize),
      distinct: true, // 避免 count 不准确
      subQuery: false, // 避免子查询导致的性能问题
    });

    res.json({
      total: count,
      devices: rows,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
    });
  } catch (error) {
    logger.error('搜索设备失败', { error: error.message, stack: error.stack });
    logger.error('错误详情', { error: error.message });
    if (error.sql) {
      logger.error('SQL', { error: error.sql });
    }
    res.status(500).json({ error: error.message, sql: error.sql });
  }
});

const MAX_EXPORT_SIZE = 50000;

router.get('/all', async (req, res) => {
  try {
    const { keyword, status, type, rackId, roomId } = req.query;

    const where = {};

    if (keyword) {
      const escapedKeyword = keyword.replace(/'/g, "''");
      where[Op.or] = [
        { deviceId: { [Op.like]: `%${escapedKeyword}%` } },
        { name: { [Op.like]: `%${escapedKeyword}%` } },
        { type: { [Op.like]: `%${escapedKeyword}%` } },
        { model: { [Op.like]: `%${escapedKeyword}%` } },
        { serialNumber: { [Op.like]: `%${escapedKeyword}%` } },
        { ipAddress: { [Op.like]: `%${escapedKeyword}%` } },
      ];
    }

    if (status && status !== 'all') {
      where.status = status;
    }

    if (type && type !== 'all') {
      where.type = type;
    }

    if (rackId && rackId !== 'all') {
      where.rackId = rackId;
    }

    // 注意：使用 include 中嵌套 where 而不是 $Rack.roomId$ 语法

    const devices = await Device.findAll({
      where,
      include: [
        {
          model: Rack,
          where: roomId && roomId !== 'all' ? { roomId: roomId } : undefined,
          include: [{ model: Room }],
          separate: false,
        },
      ],
      limit: MAX_EXPORT_SIZE,
      order: [['createdAt', 'DESC']],
      distinct: true,
      subQuery: false,
    });

    res.json({
      devices,
      total: devices.length,
    });
  } catch (error) {
    logger.error('获取设备列表失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: error.message });
  }
});

// 生成设备ID的辅助函数（使用 MAX 聚合查询避免竞态条件）
async function generateDeviceId() {
  const result = await Device.findOne({
    where: {
      deviceId: {
        [Op.like]: 'DEV%',
      },
    },
    attributes: [
      [
        sequelize.fn(
          'MAX',
          sequelize.literal(
            dbDialect === 'mysql'
              ? 'CAST(SUBSTR(deviceId, 4) AS SIGNED)'
              : 'CAST(SUBSTR(deviceId, 4) AS INTEGER)'
          )
        ),
        'maxNum',
      ],
    ],
    raw: true,
  });

  const maxNumber = result && result.maxNum ? parseInt(result.maxNum, 10) : 0;
  const newNumber = maxNumber + 1;
  return `DEV${String(newNumber).padStart(3, '0')}`;
}

// 创建设备
router.post('/', validateBody(() => createDeviceSchema(false)), async (req, res) => {
  try {
    const deviceData = { ...req.body };

    if (!deviceData.deviceId || deviceData.deviceId.trim() === '') {
      deviceData.deviceId = await generateDeviceId();
    }

    if (deviceData.rackId && deviceData.position) {
      const positionCheck = await checkPositionAvailable(
        deviceData.rackId,
        deviceData.position,
        deviceData.height
      );
      if (!positionCheck.available) {
        return res.status(400).json({ error: positionCheck.reason });
      }
    }

    const device = await Device.create(deviceData);

    const rack = await Rack.findByPk(deviceData.rackId);
    if (rack) {
      await rack.update({
        currentPower: rack.currentPower + deviceData.powerConsumption,
      });
    }

    const createDetails = [
      `设备名称: ${device.name}`,
      `设备编号: ${device.deviceId}`,
      `设备类型: ${device.type}`,
      `所属机柜: ${rack ? rack.name : '未分配'}`,
      `安装位置: U${device.position}`,
      `功耗: ${device.powerConsumption}W`,
    ].join('；');

    await logDeviceOperation(
      'create',
      generateDeviceDescription('创建设备', {
        ...device.toJSON(),
        rackName: rack?.name,
      }),
      {
        targetId: device.deviceId,
        targetName: device.name,
        afterState: device.toJSON(),
        req,
        metadata: buildDeviceMetadata({ ...device.toJSON(), rackName: rack?.name }),
      }
    );

    res.status(201).json(device);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// 设备导入模板下载
router.get('/import-template', async (req, res) => {
  try {
    // 查询设备字段配置
    const deviceFields = await DeviceField.findAll({
      order: [['order', 'ASC']],
    });

    // 根据字段配置动态生成CSV标题（排除设备ID，由系统自动生成）
    // 将rackId字段替换为机房名称+机柜名称，以便唯一定位
    // 注意：导入模板包含所有字段（不论visible状态），确保数据完整性
    const headers = [];
    deviceFields
      .filter(field => field.fieldName !== 'deviceId')
      .forEach(field => {
        // 如果是机柜字段，拆分为机房名称和机柜名称两列
        if (field.fieldName === 'rackId') {
          headers.push({
            id: '所在机房名称',
            title: '所在机房名称',
          });
          headers.push({
            id: '所在机柜名称',
            title: '所在机柜名称',
          });
          return;
        }

        // 直接使用displayName作为列名，不添加任何后缀
        headers.push({ id: field.displayName, title: field.displayName });
      });

    // 准备示例数据（根据字段配置生成，排除设备ID）
    // 注意：包含所有字段（不论visible状态）
    const exampleData = {};
    deviceFields
      .filter(field => field.fieldName !== 'deviceId')
      .forEach(field => {
        // 如果是机柜字段，拆分为机房名称和机柜名称
        if (field.fieldName === 'rackId') {
          exampleData['所在机房名称'] = '测试机房';
          exampleData['所在机柜名称'] = 'A01';
          return;
        }

        switch (field.fieldName) {
          case 'name':
            exampleData[field.displayName] = '测试服务器001';
            break;
          case 'type':
            exampleData[field.displayName] = 'server';
            break;
          case 'model':
            exampleData[field.displayName] = 'DELL R740';
            break;
          case 'serialNumber':
            exampleData[field.displayName] = 'SN1234567890';
            break;
          case 'position':
            exampleData[field.displayName] = '1';
            break;
          case 'height':
            exampleData[field.displayName] = '2';
            break;
          case 'powerConsumption':
            exampleData[field.displayName] = '500';
            break;
          case 'status':
            exampleData[field.displayName] = 'running';
            break;
          case 'purchaseDate':
            exampleData[field.displayName] = '2023-01-01';
            break;
          case 'warrantyExpiry':
            exampleData[field.displayName] = '2026-12-31';
            break;
          case 'ipAddress':
            exampleData[field.displayName] = '192.168.1.100';
            break;
          case 'description':
            exampleData[field.displayName] = '测试用服务器';
            break;
          default:
            // 自定义字段根据类型生成示例值
            if (field.fieldType === 'number') {
              exampleData[field.displayName] = '100';
            } else if (field.fieldType === 'boolean') {
              exampleData[field.displayName] = 'true';
            } else if (field.fieldType === 'date') {
              exampleData[field.displayName] = '2023-01-01';
            } else if (field.fieldType === 'select' && field.options && field.options.length > 0) {
              exampleData[field.displayName] = field.options[0].value;
            } else {
              exampleData[field.displayName] = `示例${field.displayName}`;
            }
        }
      });

    const templateData = [exampleData];

    // 确保temp目录存在
    const tempDir = path.join(__dirname, '../temp');
    if (!fs.existsSync(tempDir)) {
      fs.mkdirSync(tempDir, { recursive: true });
    }

    const csvWriter = createObjectCsvWriter({
      path: path.join(tempDir, 'import_template.csv'),
      header: headers,
    });

    // 写入CSV文件
    await csvWriter.writeRecords(templateData);

    // 读取文件并转换为GBK编码
    const csvContent = fs.readFileSync(path.join(tempDir, 'import_template.csv'), 'utf8');
    const gbkContent = iconv.encode(csvContent, 'gbk');

    // 设置响应头
    res.setHeader('Content-Type', 'text/csv; charset=gbk');
    res.setHeader(
      'Content-Disposition',
      `attachment; filename*=UTF-8''${encodeURIComponent('设备导入模板.csv')}`
    );

    // 发送CSV数据
    res.send(gbkContent);

    // 删除临时文件
    fs.unlinkSync(path.join(tempDir, 'import_template.csv'));
  } catch (error) {
    logger.error('生成导入模板失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: '生成导入模板失败' });
  }
});

// 导出设备数据为CSV
router.get('/export', async (req, res) => {
  try {
    const { deviceIds } = req.query;

    // 查询条件
    const where = {};
    if (deviceIds) {
      // 支持多个设备ID，使用数组格式
      const ids = Array.isArray(deviceIds) ? deviceIds : [deviceIds];
      where.deviceId = { [Op.in]: ids };
    }

    // 查询设备字段配置
    const deviceFields = await DeviceField.findAll({
      order: [['order', 'ASC']],
    });

    // 查询设备数据
    const devices = await Device.findAll({
      where,
      include: [
        {
          model: Rack,
          include: [{ model: Room }],
        },
      ],
    });

    // 如果没有找到设备
    if (devices.length === 0) {
      return res.status(404).json({ error: '未找到指定的设备' });
    }

    // 创建字段名到displayName的映射
    const fieldNameToDisplayName = {};
    deviceFields.forEach(field => {
      fieldNameToDisplayName[field.fieldName] = field.displayName;
    });

    // 准备CSV数据 - 根据字段配置动态生成，与导入模板保持一致
    const csvData = devices.map(device => {
      const deviceData = {};

      // 根据字段配置生成数据（排除deviceId）
      deviceFields
        .filter(field => field.visible && field.fieldName !== 'deviceId')
        .forEach(field => {
          // rackId字段拆分为机房名称和机柜名称
          if (field.fieldName === 'rackId') {
            deviceData['所在机房名称'] = device.Rack?.Room?.name || '';
            deviceData['所在机柜名称'] = device.Rack?.name || '';
            return;
          }

          // 其他字段使用displayName作为列名
          const value = device[field.fieldName];

          // 日期格式处理
          if (field.fieldType === 'date' && value) {
            deviceData[field.displayName] = new Date(value).toLocaleDateString();
          } else {
            deviceData[field.displayName] = value !== undefined && value !== null ? value : '';
          }
        });

      // 添加自定义字段
      if (device.customFields) {
        Object.entries(device.customFields).forEach(([fieldName, value]) => {
          const field = deviceFields.find(f => f.fieldName === fieldName);
          if (field && field.visible) {
            deviceData[field.displayName] = value;
          }
        });
      }

      return deviceData;
    });

    // 设置CSV标题 - 与导入模板保持一致
    const headers = [];
    deviceFields
      .filter(field => field.visible && field.fieldName !== 'deviceId')
      .forEach(field => {
        // rackId拆分为两个列
        if (field.fieldName === 'rackId') {
          headers.push({ id: '所在机房名称', title: '所在机房名称' });
          headers.push({ id: '所在机柜名称', title: '所在机柜名称' });
          return;
        }

        headers.push({ id: field.displayName, title: field.displayName });
      });

    const csvWriter = createObjectCsvWriter({
      path: path.join(__dirname, '../temp/devices.csv'),
      header: headers,
      encoding: 'utf8',
    });

    // 确保temp目录存在
    if (!fs.existsSync(path.join(__dirname, '../temp'))) {
      fs.mkdirSync(path.join(__dirname, '../temp'));
    }

    // 写入CSV文件
    await csvWriter.writeRecords(csvData);

    // 读取文件并转换为GBK编码
    const csvContent = fs.readFileSync(path.join(__dirname, '../temp/devices.csv'), 'utf8');
    const gbkContent = iconv.encode(csvContent, 'gbk');

    // 设置响应头
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', 'attachment; filename=devices.csv');

    // 发送CSV数据
    res.send(gbkContent);

    // 删除临时文件
    fs.unlinkSync(path.join(__dirname, '../temp/devices.csv'));
  } catch (error) {
    logger.error('导出设备数据失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: '导出设备数据失败' });
  }
});

// 导入设备数据从CSV - 优化版：使用事务+批量插入
router.post('/import', async (req, res) => {
  const t = await sequelize.transaction();

  try {
    if (!req.files || !req.files.csvFile) {
      await t.rollback();
      return res.status(400).json({ error: '请上传CSV文件' });
    }

    const csvFile = req.files.csvFile;
    const stats = { total: 0, success: 0, failed: 0, errors: [] };

    // 确保temp目录存在
    if (!fs.existsSync(path.join(__dirname, '../temp'))) {
      fs.mkdirSync(path.join(__dirname, '../temp'));
    }

    // 保存上传的文件（使用 path.basename 防止路径遍历）
    const safeFileName = path.basename(csvFile.name);
    if (!safeFileName || safeFileName.startsWith('.') || safeFileName !== csvFile.name) {
      return res.status(400).json({ error: '文件名不合法' });
    }
    const filePath = path.join(__dirname, '../temp', safeFileName);
    await csvFile.mv(filePath);

    // 读取并解析CSV文件（GBK编码）
    const results = [];
    const stream = fs.createReadStream(filePath).pipe(iconv.decodeStream('gbk')).pipe(csv());

    await new Promise((resolve, reject) => {
      stream
        .on('data', data => {
          stats.total++;
          results.push(data);
        })
        .on('end', resolve)
        .on('error', reject);
    });

    // 【优化1】批量查询所有必要数据（单次查询）
    const [rooms, racks, deviceFields, maxDeviceResult] = await Promise.all([
      Room.findAll({ transaction: t }),
      Rack.findAll({ include: [{ model: Room }], transaction: t }),
      DeviceField.findAll({ transaction: t }),
      // 查询最大设备ID序号
      Device.findOne({
        attributes: [
          [
        sequelize.fn(
          'MAX',
          dbDialect === 'mysql'
            ? sequelize.cast(sequelize.fn('SUBSTR', sequelize.col('deviceId'), 4), 'SIGNED')
            : sequelize.cast(sequelize.fn('SUBSTR', sequelize.col('deviceId'), 4), 'INTEGER')
        ),
        'maxNum',
      ],
        ],
        where: { deviceId: { [Op.like]: 'DEV%' } },
        transaction: t,
      }),
    ]);

    // 创建查找映射
    const roomNameToIdMap = new Map(rooms.map(room => [room.name, room.roomId]));
    const rackLocationMap = new Map(
      racks.map(rack => [`${rack.Room?.name || ''}_${rack.name}`, rack.rackId])
    );
    const fieldMapping = {};
    const fieldNameToDisplayName = {};
    deviceFields.forEach(field => {
      fieldMapping[field.displayName] = field;
      fieldNameToDisplayName[field.fieldName] = field.displayName;
    });

    // 设备ID生成器
    let maxDeviceNum = maxDeviceResult?.get('maxNum') || 0;
    const generateDeviceId = () => {
      maxDeviceNum++;
      return `DEV${String(maxDeviceNum).padStart(3, '0')}`;
    };

    // 辅助函数：提取字段名
    const extractFieldName = fieldNameWithFormat => {
      const match = fieldNameWithFormat.match(/^(.+?)(\(必填\)|\(可选\)|\([a-zA-Z0-9\-\/]+\))$/);
      return match ? match[1].trim() : fieldNameWithFormat;
    };

    // 【优化2】收集所有需要验证的唯一键
    const allDeviceIds = new Set();
    const allSerialNumbers = new Set();
    const validTypes = ['server', 'switch', 'router', 'storage', 'other'];
    const validStatuses = ['running', 'maintenance', 'offline', 'fault'];
    const baseFieldNames = [
      'deviceId',
      'name',
      'type',
      'model',
      'serialNumber',
      'rackId',
      'position',
      'height',
      'powerConsumption',
      'ipAddress',
      'status',
      'purchaseDate',
      'warrantyExpiry',
      'description',
    ];

    // 第一遍：验证和收集数据
    const validDevices = [];

    for (let i = 0; i < results.length; i++) {
      const row = results[i];
      const rowNum = i + 2;

      try {
        // 解析字段值
        const fieldValueMap = {};
        Object.entries(row).forEach(([displayName, value]) => {
          const originalFieldName = extractFieldName(displayName);
          fieldValueMap[originalFieldName] = value;
          fieldValueMap[displayName] = value;
        });

        const getFieldValue = fieldName => {
          const displayName = fieldNameToDisplayName[fieldName];
          return displayName ? fieldValueMap[displayName] : undefined;
        };

        // 验证必填字段
        const trulyRequiredFields = [];
        deviceFields.forEach(field => {
          if (field.fieldName === 'deviceId') {
            return;
          }
          if (field.fieldName === 'rackId') {
            if (field.required) {
              trulyRequiredFields.push('所在机房名称', '所在机柜名称');
            }
            return;
          }
          if (field.required) {
            trulyRequiredFields.push(field.displayName);
          }
        });

        const missingFields = trulyRequiredFields.filter(fieldName => {
          const value = fieldValueMap[fieldName];
          return !value || (typeof value === 'string' && value.trim() === '');
        });

        if (missingFields.length > 0) {
          throw new Error(`缺少必填字段：${missingFields.join('、')}`);
        }

        // 验证设备类型
        const deviceType = getFieldValue('type');
        if (!validTypes.includes(deviceType)) {
          throw new Error(`设备类型无效：${deviceType}`);
        }

        // 处理设备ID
        let deviceId = getFieldValue('deviceId');
        if (!deviceId || deviceId.trim() === '') {
          deviceId = generateDeviceId();
        } else if (allDeviceIds.has(deviceId)) {
          throw new Error(`设备ID重复：${deviceId}`);
        }
        allDeviceIds.add(deviceId);

        // 验证序列号
        const serialNumber = getFieldValue('serialNumber');
        if (!serialNumber) {
          throw new Error('序列号不能为空');
        }
        if (allSerialNumbers.has(serialNumber)) {
          throw new Error(`序列号重复：${serialNumber}`);
        }
        allSerialNumbers.add(serialNumber);

        // 验证机房和机柜
        const roomName = fieldValueMap['所在机房名称'];
        const rackName = fieldValueMap['所在机柜名称'];
        if (!roomName?.trim()) {
          throw new Error('所在机房名称不能为空');
        }
        if (!rackName?.trim()) {
          throw new Error('所在机柜名称不能为空');
        }

        const roomId = roomNameToIdMap.get(roomName.trim());
        if (!roomId) {
          throw new Error(`机房不存在：${roomName}`);
        }

        const locationKey = `${roomName.trim()}_${rackName.trim()}`;
        let rackId = rackLocationMap.get(locationKey);

        // 机柜不存在则自动创建
        if (!rackId) {
          const maxRackResult = await Rack.findOne({
            attributes: [
              [
                sequelize.fn(
                  'MAX',
                  dbDialect === 'mysql'
                    ? sequelize.cast(sequelize.fn('SUBSTR', sequelize.col('rackId'), 5), 'SIGNED')
                    : sequelize.cast(sequelize.fn('SUBSTR', sequelize.col('rackId'), 5), 'INTEGER')
                ),
                'maxNum',
              ],
            ],
            where: { rackId: { [Op.like]: 'RACK%' } },
            transaction: t,
          });
          let maxRackNum = maxRackResult?.get('maxNum') || 0;
          maxRackNum++;

          const newRack = await Rack.create(
            {
              rackId: `RACK${String(maxRackNum).padStart(3, '0')}`,
              name: rackName.trim(),
              height: 42,
              maxPower: 10000,
              currentPower: 0,
              status: 'active',
              roomId: roomId,
            },
            { transaction: t }
          );

          rackId = newRack.rackId;
          rackLocationMap.set(locationKey, rackId);
        }

        // 验证状态
        const status = getFieldValue('status');
        if (!validStatuses.includes(status)) {
          throw new Error(`状态值无效：${status}`);
        }

        // 验证数字字段
        const position = getFieldValue('position');
        const height = getFieldValue('height');
        const powerConsumption = getFieldValue('powerConsumption');

        if (position !== undefined && isNaN(Number(position))) {
          throw new Error(`位置必须是数字：${position}`);
        }
        if (height !== undefined && isNaN(Number(height))) {
          throw new Error(`高度必须是数字：${height}`);
        }
        if (powerConsumption !== undefined && isNaN(Number(powerConsumption))) {
          throw new Error(`功率必须是数字：${powerConsumption}`);
        }

        // 验证日期
        const purchaseDateValue = getFieldValue('purchaseDate');
        const warrantyExpiryValue = getFieldValue('warrantyExpiry');
        const purchaseDate = purchaseDateValue ? new Date(purchaseDateValue) : null;
        const warrantyExpiry = warrantyExpiryValue ? new Date(warrantyExpiryValue) : null;

        if (purchaseDateValue && isNaN(purchaseDate.getTime())) {
          throw new Error(`购买日期格式无效：${purchaseDateValue}`);
        }
        if (warrantyExpiryValue && isNaN(warrantyExpiry.getTime())) {
          throw new Error(`保修日期格式无效：${warrantyExpiryValue}`);
        }
        if (purchaseDate && warrantyExpiry && warrantyExpiry <= purchaseDate) {
          throw new Error(`保修日期必须晚于购买日期`);
        }

        // 处理自定义字段
        const customFields = {};
        Object.entries(row).forEach(([displayName, value]) => {
          const originalDisplayName = extractFieldName(displayName);
          const fieldConfig = fieldMapping[originalDisplayName];

          if (fieldConfig && !baseFieldNames.includes(fieldConfig.fieldName)) {
            let processedValue = value;
            if (fieldConfig.fieldType === 'number') {
              processedValue = value ? parseFloat(value) : null;
            } else if (fieldConfig.fieldType === 'boolean') {
              processedValue = value
                ? value.toLowerCase() === 'true' || value === '1' || value.toLowerCase() === '是'
                : false;
            } else if (fieldConfig.fieldType === 'date') {
              processedValue = value ? new Date(value) : null;
            }
            customFields[fieldConfig.fieldName] = processedValue;
          }
        });

        // 收集有效设备数据
        validDevices.push({
          deviceId,
          name: getFieldValue('name'),
          type: deviceType,
          model: getFieldValue('model'),
          serialNumber,
          rackId,
          position: position ? parseInt(position) : 0,
          height: height ? parseInt(height) : 1,
          powerConsumption: powerConsumption ? parseFloat(powerConsumption) : 0,
          ipAddress: getFieldValue('ipAddress') || '',
          status,
          purchaseDate,
          warrantyExpiry,
          description: getFieldValue('description') || '',
          customFields: Object.keys(customFields).length > 0 ? customFields : null,
        });
      } catch (error) {
        stats.failed++;
        stats.errors.push({ row: rowNum, error: error.message, data: row });
      }
    }

    if (validDevices.length > 0) {
      const rackDevicesMap = new Map();
      for (const device of validDevices) {
        if (device.rackId && device.position > 0) {
          if (!rackDevicesMap.has(device.rackId)) {
            rackDevicesMap.set(device.rackId, []);
          }
          rackDevicesMap.get(device.rackId).push(device);
        }
      }

      for (const [rackId, devices] of rackDevicesMap) {
        const existingDevices = await Device.findAll({
          where: { rackId, position: { [Op.ne]: null } },
          attributes: ['deviceId', 'position', 'height'],
          transaction: t,
        });

        for (const newDevice of devices) {
          const startU = newDevice.position;
          const endU = newDevice.position + newDevice.height - 1;

          let hasConflict = false;

          for (const existing of existingDevices) {
            const existStart = existing.position;
            const existEnd = existing.position + (existing.height || 1) - 1;

            if (!(endU < existStart || startU > existEnd)) {
              stats.failed++;
              stats.errors.push({
                row: 0,
                error: `U位冲突：机柜中已有设备 ${existing.deviceId} 占用 U${existStart}${existEnd !== existStart ? '-' + existEnd : ''}，与导入设备 ${newDevice.deviceId}(U${startU}-U${endU}) 冲突`,
                data: { deviceId: newDevice.deviceId },
              });
              hasConflict = true;
              break;
            }
          }

          if (!hasConflict) {
            for (const otherDevice of devices) {
              if (otherDevice === newDevice) {
                continue;
              }

              const otherStart = otherDevice.position;
              const otherEnd = otherDevice.position + otherDevice.height - 1;

              if (!(endU < otherStart || startU > otherEnd)) {
                stats.failed++;
                stats.errors.push({
                  row: 0,
                  error: `U位冲突：导入数据内部冲突，设备 ${newDevice.deviceId}(U${startU}-U${endU}) 与设备 ${otherDevice.deviceId}(U${otherStart}-U${otherEnd}) U位重叠`,
                  data: { deviceId: newDevice.deviceId },
                });
                hasConflict = true;
                break;
              }
            }
          }

          if (hasConflict) {
            const idx = validDevices.indexOf(newDevice);
            if (idx > -1) {
              validDevices.splice(idx, 1);
            }
          }
        }
      }
    }

    // 【优化3】批量查询已存在的设备ID和序列号（单次查询）
    if (validDevices.length > 0) {
      const existingDevices = await Device.findAll({
        where: {
          [Op.or]: [
            { deviceId: validDevices.map(d => d.deviceId) },
            { serialNumber: validDevices.map(d => d.serialNumber) },
          ],
        },
        attributes: ['deviceId', 'serialNumber'],
        transaction: t,
      });

      const existingDeviceIds = new Set(existingDevices.map(d => d.deviceId));
      const existingSerialNumbers = new Set(existingDevices.map(d => d.serialNumber));

      // 过滤掉已存在的设备
      const newDevices = validDevices.filter(device => {
        if (existingDeviceIds.has(device.deviceId)) {
          stats.failed++;
          stats.errors.push({ row: 0, error: `设备ID已存在：${device.deviceId}`, data: {} });
          return false;
        }
        if (existingSerialNumbers.has(device.serialNumber)) {
          stats.failed++;
          stats.errors.push({ row: 0, error: `序列号已存在：${device.serialNumber}`, data: {} });
          return false;
        }
        return true;
      });

      // 【优化4】批量创建设备
      if (newDevices.length > 0) {
        await Device.bulkCreate(newDevices, { transaction: t });
        stats.success = newDevices.length;

        // 【优化5】批量更新机柜功率
        const rackPowerMap = new Map();
        newDevices.forEach(device => {
          const current = rackPowerMap.get(device.rackId) || 0;
          rackPowerMap.set(device.rackId, current + device.powerConsumption);
        });

        for (const [rackId, powerToAdd] of rackPowerMap) {
          // 确保功率值为合法数字，防止SQL注入
          const safePower = Number(powerToAdd) || 0;
          await Rack.update(
            { currentPower: sequelize.literal(`currentPower + ${safePower}`) },
            { where: { rackId }, transaction: t }
          );
        }
      }
    }

    // 提交事务
    await t.commit();

    fs.unlinkSync(filePath);
    res.json({ statistics: stats });
  } catch (error) {
    await t.rollback();
    logger.error('导入设备数据失败', { error: error.message, stack: error.stack });

    // 清理临时文件
    try {
      if (filePath && fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }
    } catch (fileErr) {
      logger.error('删除临时文件失败', { error: fileErr });
    }

    res.status(500).json({
      errors: [{ row: 0, error: error.message || '导入过程中发生未知错误' }],
    });
  }
});

// 批量上线设备
router.put('/batch-online', validateBody(batchDeviceIdsSchema), async (req, res) => {
  try {
    const { deviceIds } = req.body;

    if (!deviceIds || !Array.isArray(deviceIds) || deviceIds.length === 0) {
      return res.status(400).json({ error: '请提供有效的设备ID列表' });
    }

    // 更新设备状态为运行中
    const [affectedCount] = await Device.update(
      { status: 'running' },
      { where: { deviceId: { [Op.in]: deviceIds } } }
    );

    res.json({
      message: `批量上线成功，已更新 ${affectedCount} 个设备`,
      affectedCount,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 批量下线设备
router.put('/batch-offline', validateBody(batchDeviceIdsSchema), async (req, res) => {
  try {
    const { deviceIds } = req.body;

    if (!deviceIds || !Array.isArray(deviceIds) || deviceIds.length === 0) {
      return res.status(400).json({ error: '请提供有效的设备ID列表' });
    }

    // 更新设备状态为离线
    const [affectedCount] = await Device.update(
      { status: 'offline' },
      { where: { deviceId: { [Op.in]: deviceIds } } }
    );

    res.json({
      message: `批量下线成功，已更新 ${affectedCount} 个设备`,
      affectedCount,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 批量变更设备状态
router.put('/batch-status', async (req, res) => {
  try {
    const { deviceIds, status } = req.body;

    if (!deviceIds || !Array.isArray(deviceIds) || deviceIds.length === 0) {
      return res.status(400).json({ error: '请提供有效的设备ID列表' });
    }

    // 检查数据库中是否存在这些设备
    const existingDevices = await Device.findAll({
      where: { deviceId: { [Op.in]: deviceIds } },
      attributes: ['deviceId'],
    });

    // 检查是否有不存在的设备
    const existingIds = existingDevices.map(d => d.deviceId);
    const missingIds = deviceIds.filter(id => !existingIds.includes(id));

    if (missingIds.length > 0) {
      return res.status(404).json({ error: `设备不存在: ${missingIds.join(', ')}` });
    }

    const validStatus = ['running', 'maintenance', 'offline', 'fault'];
    if (!validStatus.includes(status)) {
      return res.status(400).json({
        error: `状态值无效，有效值为：${validStatus.join('、')}`,
      });
    }

    // 状态映射
    const statusText = {
      running: '运行中',
      maintenance: '维护中',
      offline: '离线',
      fault: '故障',
    };

    const beforeDevices = await Device.findAll({
      where: { deviceId: { [Op.in]: deviceIds } },
      include: [{ model: Rack, attributes: ['name'] }],
    });

    const deviceDetails = beforeDevices.map(d => {
      const data = d.toJSON();
      return {
        deviceId: d.deviceId,
        name: d.name,
        type: d.type,
        model: d.model,
        serialNumber: d.serialNumber,
        ipAddress: d.ipAddress,
        rackName: data.Rack?.name || null,
        position: d.position,
        status: d.status,
      };
    });

    const deviceNames = deviceDetails.map(d => d.name);
    const deviceSummary = deviceDetails
      .map(d => `${d.name}(编号:${d.deviceId}${d.rackName ? `,机柜:${d.rackName}` : ''})`)
      .join('、');

    // 执行批量状态更新
    const [affectedCount] = await Device.update(
      { status },
      { where: { deviceId: { [Op.in]: deviceIds } } }
    );

    const statusChangeDesc = `批量变更${affectedCount}台设备状态：${deviceSummary} → ${statusText[status]}`;

    await logDeviceOperation('status_change', statusChangeDesc, {
      targetId: deviceIds.join(','),
      targetName: `${affectedCount}台设备`,
      beforeState: deviceDetails.map(d => ({
        deviceId: d.deviceId,
        name: d.name,
        status: d.status,
      })),
      afterState: deviceDetails.map(d => ({ deviceId: d.deviceId, name: d.name, status })),
      req,
      metadata: {
        status,
        statusText: statusText[status],
        count: affectedCount,
        devices: deviceDetails,
      },
    });

    res.json({
      message: `批量状态变更成功，已将 ${affectedCount} 个设备状态变更为"${statusText[status]}"`,
      affectedCount,
      newStatus: status,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 批量移动设备
router.put('/batch-move', async (req, res) => {
  const transaction = await sequelize.transaction();
  try {
    const { deviceIds, targetRackId, startPosition } = req.body;

    if (!deviceIds || !Array.isArray(deviceIds) || deviceIds.length === 0) {
      await transaction.rollback();
      return res.status(400).json({ error: '请提供有效的设备ID列表' });
    }

    if (!targetRackId) {
      await transaction.rollback();
      return res.status(400).json({ error: '请提供目标机柜ID' });
    }

    const targetRack = await Rack.findByPk(targetRackId, { transaction });
    if (!targetRack) {
      await transaction.rollback();
      return res.status(404).json({ error: '目标机柜不存在' });
    }

    const devicesToMove = await Device.findAll({
      where: { deviceId: { [Op.in]: deviceIds } },
      attributes: [
        'deviceId',
        'name',
        'type',
        'model',
        'serialNumber',
        'ipAddress',
        'rackId',
        'position',
        'height',
        'powerConsumption',
      ],
      transaction,
    });

    const deviceDetails = devicesToMove.map(d => d.toJSON());

    const beforeMoveState = deviceDetails.map(d => ({
      deviceId: d.deviceId,
      name: d.name,
      type: d.type,
      rackId: d.rackId,
      position: d.position,
      powerConsumption: d.powerConsumption,
    }));

    const deviceSummary = deviceDetails
      .map(
        d =>
          `${d.name}(编号:${d.deviceId}${d.type ? `,类型:${d.type}` : ''}${d.ipAddress ? `,IP:${d.ipAddress}` : ''})`
      )
      .join('、');

    const sourceRackPowerChanges = new Map();
    devicesToMove.forEach(device => {
      if (device.rackId) {
        const currentChange = sourceRackPowerChanges.get(device.rackId) || 0;
        sourceRackPowerChanges.set(device.rackId, currentChange - (device.powerConsumption || 0));
      }
    });

    const deviceHeightMap = new Map(devicesToMove.map(d => [d.deviceId, d.height || 1]));

    if (startPosition) {
      const devicesToCheck = [];
      for (let i = 0; i < deviceIds.length; i++) {
        const deviceId = deviceIds[i];
        const height = deviceHeightMap.get(deviceId) || 1;
        devicesToCheck.push({
          deviceId,
          position: startPosition + i,
          height,
        });
      }

      const existingDevices = await Device.findAll({
        where: {
          rackId: targetRackId,
          position: { [Op.ne]: null },
        },
        attributes: ['deviceId', 'position', 'height'],
        transaction,
      });

      for (const newDevice of devicesToCheck) {
        const startU = newDevice.position;
        const endU = newDevice.position + newDevice.height - 1;

        for (const existing of existingDevices) {
          if (devicesToCheck.some(d => d.deviceId === existing.deviceId)) {
            continue;
          }

          const existStart = existing.position;
          const existEnd = existing.position + (existing.height || 1) - 1;

          if (!(endU < existStart || startU > existEnd)) {
            return res.status(400).json({
              error: `U位冲突：机柜中已有设备 ${existing.deviceId} 占用 U${existStart}${existEnd !== existStart ? '-' + existEnd : ''}，与移动设备 ${newDevice.deviceId}(U${startU}-U${endU}) 冲突`,
            });
          }
        }

        for (const other of devicesToCheck) {
          if (other === newDevice) {
            continue;
          }

          const otherStart = other.position;
          const otherEnd = other.position + other.height - 1;

          if (!(endU < otherStart || startU > otherEnd)) {
            return res.status(400).json({
              error: `U位冲突：移动设备 ${newDevice.deviceId}(U${startU}-U${endU}) 与设备 ${other.deviceId}(U${otherStart}-U${otherEnd}) U位重叠`,
            });
          }
        }
      }
    }

    let movedCount = 0;
    const targetRackPowerChange = { rackId: targetRackId, change: 0 };

    for (let i = 0; i < deviceIds.length; i++) {
      const deviceId = deviceIds[i];
      const position = startPosition ? startPosition + i : undefined;

      const updateData = { rackId: targetRackId };
      if (position) {
        updateData.position = position;
      }

      const [updated] = await Device.update(updateData, {
        where: { deviceId },
        transaction,
      });

      if (updated) {
        movedCount++;
        const device = devicesToMove.find(d => d.deviceId === deviceId);
        if (device) {
          targetRackPowerChange.change += device.powerConsumption || 0;
        }
      }
    }

    for (const [rackId, powerChange] of sourceRackPowerChanges) {
      if (rackId !== targetRackId) {
        const safePower = Number(powerChange) || 0;
        await Rack.update(
          { currentPower: sequelize.literal(`currentPower + ${safePower}`) },
          { where: { rackId }, transaction }
        );
      }
    }

    if (sourceRackPowerChanges.has(targetRackId)) {
      targetRackPowerChange.change += sourceRackPowerChanges.get(targetRackId);
    }

    if (targetRackPowerChange.change !== 0) {
      const safeTargetPower = Number(targetRackPowerChange.change) || 0;
      await Rack.update(
        { currentPower: sequelize.literal(`currentPower + ${safeTargetPower}`) },
        { where: { rackId: targetRackId }, transaction }
      );
    }

    const moveDesc = startPosition
      ? `批量移动${movedCount}台设备到机柜【${targetRack.name}】：${deviceSummary} → U${startPosition}起`
      : `批量移动${movedCount}台设备到机柜【${targetRack.name}】：${deviceSummary}`;

    await logDeviceOperation('move', moveDesc, {
      targetId: deviceIds.join(','),
      targetName: `${movedCount}台设备`,
      beforeState: beforeMoveState,
      afterState: { targetRackId, targetRackName: targetRack.name, startPosition },
      req,
      metadata: {
        count: movedCount,
        targetRackId,
        targetRackName: targetRack.name,
        startPosition,
        devices: deviceDetails,
      },
    });

    await transaction.commit();

    res.json({
      message: `批量移动成功，已将 ${movedCount} 个设备移动到机柜 ${targetRackId}`,
      movedCount,
    });
  } catch (error) {
    await transaction.rollback();
    res.status(500).json({ error: error.message });
  }
});

// 增强导出设备数据（支持所有字段和自定义字段）
// 改为 POST 请求，支持按筛选条件导出全部设备，避免 URL 长度限制
router.post('/enhanced-export', async (req, res) => {
  try {
    const { deviceIds, format = 'csv', filters } = req.body;

    // 从数据库读取所有字段配置（不过滤 visible，以导出所有信息）
    const allFields = await DeviceField.findAll({
      order: [['order', 'ASC']],
    });

    // 构建字段映射表
    const fieldMap = {};
    const fieldLabels = {};
    allFields.forEach(field => {
      fieldMap[field.fieldName] = field;
      fieldLabels[field.fieldName] = field.displayName;
    });

    // 构建查询条件
    const where = {};

    // 按指定 deviceIds 导出（选择的行 / 当前页）
    if (deviceIds && Array.isArray(deviceIds) && deviceIds.length > 0) {
      where.deviceId = { [Op.in]: deviceIds };
    } else if (filters) {
      // 按筛选条件导出全部设备（复用列表查询的筛选逻辑）
      const { keyword, status, type, rackId, roomId } = filters;

      if (keyword) {
        const escapedKeyword = keyword
          .replace(/\\/g, '\\\\')
          .replace(/'/g, "''")
          .replace(/%/g, '\\%')
          .replace(/_/g, '\\_');

        const searchConditions = [
          { deviceId: { [Op.like]: `%${escapedKeyword}%` } },
          { name: { [Op.like]: `%${escapedKeyword}%` } },
          { type: { [Op.like]: `%${escapedKeyword}%` } },
          { model: { [Op.like]: `%${escapedKeyword}%` } },
          { serialNumber: { [Op.like]: `%${escapedKeyword}%` } },
          { ipAddress: { [Op.like]: `%${escapedKeyword}%` } },
          { description: { [Op.like]: `%${escapedKeyword}%` } },
        ];

        // 动态获取文本类型的自定义字段
        const customFields = await DeviceField.findAll({
          where: {
            isSystem: false,
            fieldType: { [Op.in]: ['string', 'textarea'] },
          },
        });

        if (customFields.length > 0) {
          const jsonConditions = customFields.map(field => {
            const safeFieldName = field.fieldName
              .replace(/\\/g, '\\\\')
              .replace(/"/g, '\\"')
              .replace(/'/g, "''");

            if (dbDialect === 'mysql') {
              return sequelize.literal(
                `JSON_UNQUOTE(JSON_EXTRACT(customFields, '$."${safeFieldName}"')) LIKE '%${escapedKeyword}%' ESCAPE '\\\\'`
              );
            } else {
              return sequelize.literal(
                `CAST(json_extract(customFields, '$.${safeFieldName}') AS TEXT) LIKE '%${escapedKeyword}%' ESCAPE '\\'`
              );
            }
          });
          searchConditions.push(...jsonConditions);
        }

        where[Op.or] = searchConditions;
      }

      if (status && status !== 'all') {
        where.status = status;
      }
      if (type && type !== 'all') {
        where.type = type;
      }
      if (rackId && rackId !== 'all') {
        where.rackId = rackId;
      }
    }

    // 查询设备数据（不分页，导出全部匹配结果）
    const includeConfig = [
      {
        model: Rack,
        include: [{ model: Room }],
      },
    ];

    // 如果按筛选条件导出且有机房筛选，添加 Rack 的 where 条件
    if (filters && filters.roomId && filters.roomId !== 'all') {
      includeConfig[0].where = { roomId: filters.roomId };
    }

    const devices = await Device.findAll({
      where,
      include: includeConfig,
      distinct: true,
    });

    if (devices.length === 0) {
      return res.status(404).json({ error: '未找到指定的设备' });
    }

    // 状态和类型映射（补充 idle 状态）
    const statusMap = {
      running: '运行中',
      maintenance: '维护中',
      offline: '离线',
      fault: '故障',
      idle: '空闲',
    };
    const typeMap = {
      server: '服务器',
      switch: '交换机',
      router: '路由器',
      storage: '存储设备',
      other: '其他设备',
    };

    // 准备导出数据 - 遍历所有设备
    const exportData = devices.map(device => {
      const data = {};

      // 首先处理关联字段（机房）- 如果 DeviceField 中没有配置 roomName，也导出机房信息
      const hasRoomField = allFields.some(f => f.fieldName === 'roomName');
      if (!hasRoomField) {
        data['所在机房'] = device.Rack?.Room?.name || '';
      }

      // 遍历所有字段配置动态获取值
      allFields.forEach(field => {
        const fieldName = field.fieldName;
        const label = field.displayName;

        // 首先检查 device 表的直字段
        if (device[fieldName] !== undefined && device[fieldName] !== null) {
          if (fieldName === 'rackId') {
            data[label] = device.Rack?.name || '';
          } else if (fieldName === 'roomName') {
            data[label] = device.Rack?.Room?.name || '';
          } else if (fieldName === 'status') {
            data[label] = statusMap[device.status] || device.status || '';
          } else if (fieldName === 'type') {
            data[label] = typeMap[device.type] || device.type || '';
          } else if (fieldName === 'purchaseDate' || fieldName === 'warrantyExpiry') {
            data[label] = device[fieldName]
              ? new Date(device[fieldName]).toLocaleDateString('zh-CN')
              : '';
          } else {
            data[label] = device[fieldName];
          }
        } else if (
          device.customFields &&
          typeof device.customFields === 'object' &&
          device.customFields[fieldName] !== undefined
        ) {
          data[label] = device.customFields[fieldName];
        } else {
          // 设备表中没有该字段且 customFields 中也没有，设为空字符串
          data[label] = '';
        }
      });

      // 展开 customFields 中额外的自定义字段（不在 DeviceField 配置中的）
      if (device.customFields && typeof device.customFields === 'object') {
        Object.entries(device.customFields).forEach(([key, value]) => {
          if (!fieldMap[key]) {
            data[key] = value;
          }
        });
      }

      return data;
    });

    // CSV 导出
    if (format === 'csv') {
      // 构建完整的 header 列表（基于所有字段配置 + customFields 中的额外字段）
      const headerSet = new Set();

      // 添加机房字段（如果存在）
      if (!allFields.some(f => f.fieldName === 'roomName')) {
        headerSet.add('所在机房');
      }

      // 添加所有 DeviceField 配置的字段
      allFields.forEach(field => {
        headerSet.add(field.displayName);
      });

      // 收集所有设备 customFields 中的额外字段键
      devices.forEach(device => {
        if (device.customFields && typeof device.customFields === 'object') {
          Object.keys(device.customFields).forEach(key => {
            if (!fieldMap[key]) {
              headerSet.add(key);
            }
          });
        }
      });

      const headers = Array.from(headerSet).map(key => ({ id: key, title: key }));

      if (headers.length === 0) {
        return res.status(400).json({ error: '没有可导出的字段' });
      }

      // 使用唯一文件名避免并发导出冲突
      const exportFileName = `enhanced_export_${Date.now()}_${Math.random().toString(36).slice(2, 8)}.csv`;
      const exportFilePath = path.join(__dirname, '../temp', exportFileName);

      const csvWriter = createObjectCsvWriter({
        path: exportFilePath,
        header: headers,
        encoding: 'utf8',
      });

      if (!fs.existsSync(path.join(__dirname, '../temp'))) {
        fs.mkdirSync(path.join(__dirname, '../temp'));
      }

      await csvWriter.writeRecords(exportData);

      const csvContent = fs.readFileSync(exportFilePath, 'utf8');
      const gbkContent = iconv.encode(csvContent, 'gbk');

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', 'attachment; filename=devices.csv');
      res.send(gbkContent);

      // 清理临时文件
      try {
        fs.unlinkSync(exportFilePath);
      } catch (cleanupErr) {
        logger.warn('清理导出临时文件失败', { error: cleanupErr.message });
      }
    } else {
      // JSON 导出
      res.setHeader('Content-Type', 'application/json');
      res.setHeader('Content-Disposition', 'attachment; filename=devices.json');
      res.json({
        exportTime: new Date().toISOString(),
        totalCount: devices.length,
        fields: Object.values(fieldLabels),
        devices: exportData,
      });
    }
  } catch (error) {
    logger.error('增强导出失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: '增强导出失败' });
  }
});

// 检查U位是否可用
router.get('/check-position/:rackId', async (req, res) => {
  try {
    const { rackId } = req.params;
    const { position, height, excludeDeviceId } = req.query;

    if (!position) {
      return res.status(400).json({ error: '请提供位置参数' });
    }

    const result = await checkPositionAvailable(
      rackId,
      parseInt(position),
      parseInt(height) || 1,
      excludeDeviceId || null
    );

    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 获取单个设备
router.get('/:deviceId', async (req, res) => {
  try {
    const device = await Device.findByPk(req.params.deviceId, {
      include: [
        {
          model: Rack,
          include: [{ model: Room }],
        },
      ],
    });
    if (!device) {
      return res.status(404).json({ error: '设备不存在' });
    }
    res.json(device);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 将设备标记为空闲
router.put('/:deviceId/to-idle', async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { idleReason } = req.body;
    const { deviceId } = req.params;

    const device = await Device.findByPk(deviceId, { transaction: t });
    if (!device) {
      await t.rollback();
      return res.status(404).json({ error: '设备不存在' });
    }

    if (device.isIdle) {
      await t.rollback();
      return res.status(400).json({ error: '设备已经标记为空闲设备' });
    }

    await device.update(
      {
        isIdle: true,
        status: 'idle',
        idleDate: new Date(),
        idleReason: idleReason || `从设备管理转入`,
      },
      { transaction: t }
    );

    if (device.rackId) {
      const rack = await Rack.findByPk(device.rackId, { transaction: t });
      if (rack) {
        await rack.update(
          {
            currentPower: Math.max(0, rack.currentPower - (device.powerConsumption || 0)),
          },
          { transaction: t }
        );
      }
    }

    await t.commit();

    const deviceData = {
      ...device.toJSON(),
      rackName: device.rack?.name,
    };
    await logDeviceOperation('to_idle', generateDeviceDescription('转入空闲设备', deviceData), {
      targetId: device.deviceId,
      targetName: device.name,
      beforeState: { ...device.toJSON(), isIdle: false },
      afterState: { ...device.toJSON(), isIdle: true },
      req,
      metadata: buildDeviceMetadata(deviceData, { idleReason, type: 'device_to_idle' }),
    });

    res.json({
      message: '设备已转入空闲设备',
      device: device.toJSON(),
    });
  } catch (error) {
    await t.rollback();
    logger.error('设备转入空闲设备失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: error.message });
  }
});

// 获取设备的工单列表
router.get('/:deviceId/tickets', async (req, res) => {
  try {
    const { deviceId } = req.params;
    const { status, page = 1, pageSize = 10 } = req.query;
    const offset = (page - 1) * pageSize;

    // 检查设备是否存在
    const device = await Device.findByPk(deviceId);
    if (!device) {
      return res.status(404).json({ error: '设备不存在' });
    }

    // 构建查询条件
    const where = { deviceId };
    if (status && status !== 'all') {
      where.status = status;
    }

    // 查询工单列表
    const { count, rows: tickets } = await Ticket.findAndCountAll({
      where,
      order: [['createdAt', 'DESC']],
      offset: parseInt(offset),
      limit: parseInt(pageSize),
    });

    res.json({
      data: tickets,
      total: count,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 更新设备
router.put('/:deviceId', validateBody(() => createDeviceSchema(true)), async (req, res) => {
  try {
    const oldDevice = await Device.findByPk(req.params.deviceId);
    if (!oldDevice) {
      return res.status(404).json({ error: '设备不存在' });
    }

    const beforeState = oldDevice.toJSON();
    const changedFields = {};

    const newRackId = req.body.rackId !== undefined ? req.body.rackId : oldDevice.rackId;
    const newPosition = req.body.position !== undefined ? req.body.position : oldDevice.position;
    const newHeight = req.body.height !== undefined ? req.body.height : oldDevice.height;

    if (
      (req.body.rackId !== undefined ||
        req.body.position !== undefined ||
        req.body.height !== undefined) &&
      newRackId &&
      newPosition
    ) {
      const positionCheck = await checkPositionAvailable(
        newRackId,
        newPosition,
        newHeight,
        req.params.deviceId
      );
      if (!positionCheck.available) {
        return res.status(400).json({ error: positionCheck.reason });
      }
    }

    const [updated] = await Device.update(req.body, {
      where: { deviceId: req.params.deviceId },
    });

    if (updated) {
      const oldRackId = oldDevice.rackId;
      const oldPower = oldDevice.powerConsumption || 0;
      const newPower =
        req.body.powerConsumption !== undefined ? req.body.powerConsumption : oldPower;

      if (oldRackId === newRackId) {
        const rack = await Rack.findByPk(oldRackId);
        if (rack) {
          const powerDiff = newPower - oldPower;
          await rack.update({
            currentPower: rack.currentPower + powerDiff,
          });
        }
      } else {
        if (oldRackId) {
          const oldRack = await Rack.findByPk(oldRackId);
          if (oldRack) {
            await oldRack.update({
              currentPower: Math.max(0, oldRack.currentPower - oldPower),
            });
          }
        }
        if (newRackId) {
          const newRack = await Rack.findByPk(newRackId);
          if (newRack) {
            await newRack.update({
              currentPower: newRack.currentPower + newPower,
            });
          }
        }
      }

      const updatedDevice = await Device.findByPk(req.params.deviceId, {
        include: [
          {
            model: Rack,
            include: [{ model: Room }],
          },
        ],
      });

      const afterState = updatedDevice.toJSON();
      for (const key of Object.keys(req.body)) {
        if (JSON.stringify(beforeState[key]) !== JSON.stringify(afterState[key])) {
          changedFields[key] = { from: beforeState[key], to: afterState[key] };
        }
      }

      const deviceData = {
        ...updatedDevice.toJSON(),
        rackName: updatedDevice.Rack?.name,
        roomName: updatedDevice.Rack?.Room?.name,
      };
      delete deviceData.Rack;

      const changeDetails = Object.entries(changedFields)
        .map(([field, values]) => {
          const fieldNames = {
            name: '名称',
            deviceId: '设备编号',
            type: '类型',
            model: '型号',
            manufacturer: '制造商',
            serialNumber: '序列号',
            status: '状态',
            position: '安装位置(U)',
            height: '占用高度(U)',
            powerConsumption: '功耗(W)',
            ipAddress: 'IP地址',
            macAddress: 'MAC地址',
            managementIp: '管理IP',
          };
          const displayName = fieldNames[field] || field;
          return `${displayName}: ${values.from ?? '空'} → ${values.to ?? '空'}`;
        })
        .join('；');

      const operationDesc =
        generateDeviceDescription('更新设备', deviceData, {
          includePosition: false,
        }) + (changeDetails ? `，变更内容：${changeDetails}` : '');

      await logDeviceOperation('update', operationDesc, {
        targetId: updatedDevice.deviceId,
        targetName: updatedDevice.name,
        beforeState,
        afterState: deviceData,
        req,
        metadata: buildDeviceMetadata(deviceData, { changedFields }),
      });

      res.json(updatedDevice);
    } else {
      res.status(404).json({ error: '设备不存在' });
    }
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// 批量删除设备
router.post('/batch-delete', validateBody(batchDeviceIdsSchema), async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { deviceIds } = req.body;

    if (!deviceIds || !Array.isArray(deviceIds) || deviceIds.length === 0) {
      await t.rollback();
      return res.status(400).json({ error: '请提供有效的设备 ID 列表' });
    }

    const devices = await Device.findAll({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 1. 删除相关端口 (必须在网卡之前删除，因为端口依赖网卡)
    await DevicePort.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 2. 删除相关网卡
    await NetworkCard.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 3. 删除相关接线
    await Cable.destroy({
      where: {
        [Op.or]: [
          { sourceDeviceId: { [Op.in]: deviceIds } },
          { targetDeviceId: { [Op.in]: deviceIds } },
        ],
      },
      transaction: t,
    });

    // 4. 解除工单关联
    await Ticket.update(
      { deviceId: null },
      { where: { deviceId: { [Op.in]: deviceIds } }, transaction: t }
    );

    // 5. 删除盘点记录
    await InventoryRecord.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 6. 更新机柜功率
    for (const device of devices) {
      if (device.rackId) {
        const rack = await Rack.findByPk(device.rackId, { transaction: t });
        if (rack) {
          await rack.update(
            {
              currentPower: Math.max(0, rack.currentPower - device.powerConsumption),
            },
            { transaction: t }
          );
        }
      }
    }

    // 7. 删除设备
    const deletedCount = await Device.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    await t.commit();

    const deviceDetails = devices.map(d => d.toJSON());
    const deviceSummary = deviceDetails
      .map(
        d =>
          `${d.name}(编号:${d.deviceId}${d.type ? `,类型:${d.type}` : ''}${d.serialNumber ? `,序列号:${d.serialNumber}` : ''})`
      )
      .join('、');

    await logDeviceOperation('batch_delete', `批量删除${deletedCount}台设备：${deviceSummary}`, {
      targetId: deviceIds.join(','),
      targetName: `${deletedCount}台设备`,
      beforeState: deviceDetails,
      req,
      metadata: { count: deletedCount, devices: deviceDetails },
    });

    res.json({
      message: `批量删除成功，已删除 ${deletedCount} 个设备`,
      deletedCount,
    });
  } catch (error) {
    await t.rollback();
    logger.error('批量删除设备错误', { error: error.message, stack: error.stack });
    res.status(500).json({ error: error.message, stack: error.stack });
  }
});

// 删除所有设备（一键清空）
router.delete('/delete-all', async (req, res) => {
  const t = await sequelize.transaction();
  try {
    // 获取所有设备
    const allDevices = await Device.findAll({ transaction: t });

    if (allDevices.length === 0) {
      await t.rollback();
      return res.json({ message: '没有设备需要删除', deletedCount: 0 });
    }

    const deviceIds = allDevices.map(d => d.deviceId);

    // 1. 删除相关端口
    await DevicePort.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 2. 删除相关网卡
    await NetworkCard.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 3. 删除相关接线
    await Cable.destroy({
      where: {
        [Op.or]: [
          { sourceDeviceId: { [Op.in]: deviceIds } },
          { targetDeviceId: { [Op.in]: deviceIds } },
        ],
      },
      transaction: t,
    });

    // 4. 解除工单关联
    await Ticket.update(
      { deviceId: null },
      { where: { deviceId: { [Op.in]: deviceIds } }, transaction: t }
    );

    // 5. 删除盘点记录
    await InventoryRecord.destroy({
      where: { deviceId: { [Op.in]: deviceIds } },
      transaction: t,
    });

    // 6. 更新机柜功率
    for (const device of allDevices) {
      if (device.rackId) {
        const rack = await Rack.findByPk(device.rackId, { transaction: t });
        if (rack) {
          await rack.update(
            {
              currentPower: Math.max(0, rack.currentPower - device.powerConsumption),
            },
            { transaction: t }
          );
        }
      }
    }

    // 7. 删除所有设备
    const deletedCount = await Device.destroy({
      where: {},
      transaction: t,
    });

    await t.commit();

    res.json({
      message: `成功删除所有设备，共删除 ${deletedCount} 个设备`,
      deletedCount,
    });
  } catch (error) {
    await t.rollback();
    logger.error('删除所有设备错误', { error: error.message, stack: error.stack });
    res.status(500).json({ error: error.message, stack: error.stack });
  }
});

// 删除设备
router.delete('/:deviceId', async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { deviceId } = req.params;

    // 获取设备信息以更新功率
    const device = await Device.findByPk(deviceId, { transaction: t });
    if (!device) {
      await t.rollback();
      return res.status(404).json({ error: '设备不存在' });
    }

    const deviceName = device.name;
    const beforeState = device.toJSON();

    // 1. 删除相关端口 (必须在网卡之前删除，因为端口依赖网卡)
    const deletedPorts = await DevicePort.destroy({
      where: { deviceId: deviceId },
      transaction: t,
    });

    // 2. 删除相关网卡
    const deletedNetworkCards = await NetworkCard.destroy({
      where: { deviceId: deviceId },
      transaction: t,
    });

    // 3. 删除相关接线
    // 必须在删除设备之前删除，否则可能触发外键约束错误
    const deletedCables = await Cable.destroy({
      where: {
        [Op.or]: [{ sourceDeviceId: deviceId }, { targetDeviceId: deviceId }],
      },
      transaction: t,
    });

    // 4. 解除工单关联 (Unlink Tickets)
    await Ticket.update({ deviceId: null }, { where: { deviceId: deviceId }, transaction: t });

    // 5. 删除盘点记录
    await InventoryRecord.destroy({
      where: { deviceId: deviceId },
      transaction: t,
    });

    // 6. 更新机柜功率 (必须在删除设备之前)
    if (device.rackId) {
      try {
        const rack = await Rack.findByPk(device.rackId, { transaction: t });
        if (rack) {
          await rack.update(
            {
              currentPower: Math.max(0, rack.currentPower - device.powerConsumption),
            },
            { transaction: t }
          );
        }
      } catch (err) {
        logger.error('更新机柜功率失败', { err: err.message, stack: err.stack });
        throw err; // 重新抛出错误，触发事务回滚
      }
    }

    // 7. 删除设备 (Delete Device)
    await Device.destroy({
      where: { deviceId: deviceId },
      transaction: t,
    });

    // 提交事务
    await t.commit();

    if (deletedCables > 0) {
      logger.info('已删除 ${deletedCables} 条相关接线');
    }

    await logDeviceOperation(
      'delete',
      `删除设备【${deviceName}】（编号:${deviceId}，类型:${device.type}，型号:${device.model || '无'}，序列号:${device.serialNumber || '无'}，IP:${device.ipAddress || '无'}），关联删除：${deletedCables}条接线、${deletedPorts}个端口、${deletedNetworkCards}张网卡`,
      {
        targetId: deviceId,
        targetName: deviceName,
        beforeState,
        req,
        metadata: buildDeviceMetadata(device.toJSON(), {
          deletedCables,
          deletedPorts,
          deletedNetworkCards,
        }),
      }
    );

    res.status(200).json({
      message: '删除成功',
      deviceId: deviceId,
      deletedCablesCount: deletedCables,
      deletedPortsCount: deletedPorts,
    });
  } catch (error) {
    await t.rollback();
    logger.error('删除设备失败', { error: error.message, stack: error.stack });
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;