module.exports = (sequelize, DataTypes) => {
  const RouteCardOperationMaterialConsumption = sequelize.define(
    'RouteCardOperationMaterialConsumption',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      route_card_operation_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'RouteCardOperation', key: 'id' },
      },
      production_order_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'ProductionOrder', key: 'id' },
      },
      material_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Material', key: 'id' },
      },
      uom_id: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'UOM', key: 'id' },
      },
      consumed_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
      },
      scrap_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
      },
      material_display_snapshot: {
        type: DataTypes.STRING(512),
        allowNull: true,
        comment: 'Material line as shown (e.g. nesting format) when consumption was saved',
      },
      work_order_id: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'WorkOrder', key: 'id' },
        comment: 'Route card work order (denormalized for PO/WO reporting)',
      },
      wip_material_class: {
        type: DataTypes.STRING(20),
        allowNull: true,
        comment: 'RAW_MATERIAL | FASTENER at save',
      },
      created_by: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'User', key: 'id' },
      },
      updated_by: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'User', key: 'id' },
      },
    },
    {
      tableName: 'route_card_operation_material_consumption',
      indexes: [
        { unique: true, fields: ['route_card_operation_id', 'material_id'] },
        { fields: ['production_order_id'] },
        { fields: ['work_order_id'] },
      ],
    }
  );

  RouteCardOperationMaterialConsumption.associate = (models) => {
    RouteCardOperationMaterialConsumption.belongsTo(models.RouteCardOperation, {
      foreignKey: 'route_card_operation_id',
      as: 'routeCardOperation',
    });
    RouteCardOperationMaterialConsumption.belongsTo(models.ProductionOrder, {
      foreignKey: 'production_order_id',
      as: 'productionOrder',
    });
    RouteCardOperationMaterialConsumption.belongsTo(models.Material, { foreignKey: 'material_id', as: 'material' });
    RouteCardOperationMaterialConsumption.belongsTo(models.UOM, { foreignKey: 'uom_id', as: 'uom' });
    RouteCardOperationMaterialConsumption.belongsTo(models.WorkOrder, { foreignKey: 'work_order_id', as: 'workOrder' });
  };

  return RouteCardOperationMaterialConsumption;
};
