const { sequelize } = require('../models');

async function createTables() {
  try {
    console.log('开始创建设备相关表...');

    // 创建设备表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS devices (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '设备ID',
        device_number VARCHAR(50) NOT NULL UNIQUE COMMENT '设备编号',
        device_name VARCHAR(100) NOT NULL COMMENT '设备名称',
        device_type VARCHAR(50) NOT NULL COMMENT '设备类型',
        device_model VARCHAR(100) COMMENT '设备型号',
        manufacturer VARCHAR(100) COMMENT '制造商',
        installation_location VARCHAR(200) COMMENT '安装位置',
        installation_date DATE COMMENT '安装日期',
        status ENUM('normal', 'warning', 'error', 'offline') DEFAULT 'normal' COMMENT '设备状态',
        farm_id INT COMMENT '养殖场ID',
        barn_id INT COMMENT '栏舍ID',
        created_by INT COMMENT '创建人ID',
        updated_by INT COMMENT '更新人ID',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_device_number (device_number),
        INDEX idx_device_type (device_type),
        INDEX idx_status (status),
        INDEX idx_farm_barn (farm_id, barn_id),
        INDEX idx_created_by (created_by),
        INDEX idx_updated_by (updated_by)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备表';
    `);

    console.log('✅ 设备表创建成功');

    // 创建设备预警表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS device_alerts (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '预警ID',
        device_id INT NOT NULL COMMENT '设备ID',
        alert_type VARCHAR(50) NOT NULL COMMENT '预警类型',
        alert_level ENUM('info', 'warning', 'critical') NOT NULL COMMENT '预警级别',
        alert_title VARCHAR(200) NOT NULL COMMENT '预警标题',
        alert_content TEXT COMMENT '预警内容',
        alert_time TIMESTAMP NOT NULL COMMENT '预警时间',
        status ENUM('pending', 'processing', 'resolved', 'ignored') DEFAULT 'pending' COMMENT '处理状态',
        handler_id INT COMMENT '处理人ID',
        handle_time TIMESTAMP NULL COMMENT '处理时间',
        handle_note TEXT COMMENT '处理备注',
        farm_id INT COMMENT '养殖场ID',
        barn_id INT COMMENT '栏舍ID',
        is_read BOOLEAN DEFAULT FALSE COMMENT '是否已读',
        read_time TIMESTAMP NULL COMMENT '阅读时间',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_device_id (device_id),
        INDEX idx_alert_type (alert_type),
        INDEX idx_alert_level (alert_level),
        INDEX idx_alert_time (alert_time),
        INDEX idx_status (status),
        INDEX idx_farm_barn (farm_id, barn_id),
        INDEX idx_handler_id (handler_id),
        INDEX idx_is_read (is_read),
        FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
        FOREIGN KEY (handler_id) REFERENCES users(id) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备预警表';
    `);

    console.log('✅ 设备预警表创建成功');
    console.log('🎉 所有表创建完成！');

  } catch (error) {
    console.error('❌ 创建表失败:', error);
    throw error;
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  createTables().then(() => {
    console.log('数据库表创建完成');
    process.exit(0);
  }).catch(error => {
    console.error('创建失败:', error);
    process.exit(1);
  });
}

module.exports = createTables;