/**
 * 创建iot_cattle表
 * 
 * 问题描述：
 * - 数据库中缺少iot_cattle表
 * - 需要创建该表并正确设置外键约束
 */

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('iot_cattle', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
        comment: '牛只ID'
      },
      org_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '组织ID'
      },
      ear_number: {
        type: Sequelize.BIGINT,
        allowNull: false,
        comment: '耳标号'
      },
      sex: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '性别'
      },
      strain: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '品系'
      },
      varieties: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '品种'
      },
      cate: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '类别'
      },
      birth_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '出生体重'
      },
      birthday: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '出生日期'
      },
      pen_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '栏舍ID'
      },
      into_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '入栏时间'
      },
      parity: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '胎次'
      },
      source: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '来源'
      },
      source_day: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '来源天数'
      },
      source_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '来源体重'
      },
      weight: {
        type: Sequelize.DOUBLE(11, 2),
        allowNull: false,
        comment: '当前体重'
      },
      event: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '事件'
      },
      event_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '事件时间'
      },
      lactation_day: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '泌乳天数'
      },
      semen_num: {
        type: Sequelize.STRING(30),
        allowNull: false,
        comment: '精液编号'
      },
      is_wear: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否佩戴设备'
      },
      batch_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '批次ID'
      },
      imgs: {
        type: Sequelize.STRING(800),
        allowNull: false,
        comment: '图片'
      },
      is_ele_auth: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否电子认证'
      },
      is_qua_auth: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否质量认证'
      },
      is_delete: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '是否删除'
      },
      is_out: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '是否出栏'
      },
      create_uid: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '创建人ID'
      },
      create_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '创建时间'
      },
      algebra: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '代数'
      },
      colour: {
        type: Sequelize.TEXT,
        allowNull: false,
        comment: '毛色'
      },
      info_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '信息体重'
      },
      descent: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '血统'
      },
      is_vaccin: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否接种疫苗'
      },
      is_insemination: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否人工授精'
      },
      is_insure: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否投保'
      },
      is_mortgage: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '是否抵押'
      },
      update_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '更新时间'
      },
      breed_bull_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
        comment: '配种时间'
      },
      level: {
        type: Sequelize.TINYINT,
        allowNull: false,
        comment: '等级'
      },
      six_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '6月龄体重'
      },
      eighteen_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '18月龄体重'
      },
      twelve_day_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '12日龄体重'
      },
      eighteen_day_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '18日龄体重'
      },
      xxiv_day_weight: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
        comment: '24日龄体重'
      },
      semen_breed_imgs: {
        type: Sequelize.STRING(800),
        allowNull: false,
        comment: '精液配种图片'
      },
      sell_status: {
        type: Sequelize.SMALLINT,
        allowNull: false,
        comment: '销售状态'
      },
      weight_calculate_time: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '体重计算时间'
      },
      day_of_birthday: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '出生天数'
      }
    }, {
      charset: 'utf8mb4',
      comment: '物联网牛只表'
    });

    // 添加索引
    await queryInterface.addIndex('iot_cattle', ['org_id']);
    await queryInterface.addIndex('iot_cattle', ['strain']);
    await queryInterface.addIndex('iot_cattle', ['varieties']);
    await queryInterface.addIndex('iot_cattle', ['pen_id']);
    await queryInterface.addIndex('iot_cattle', ['batch_id']);

    console.log('✅ 成功创建iot_cattle表');
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('iot_cattle');
    console.log('✅ 成功删除iot_cattle表');
  }
};