module.exports = (sequelize, DataTypes) => {
  const ProductionWipBalance = sequelize.define(
    'ProductionWipBalance',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      company_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Company', key: 'id' },
      },
      plant_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Plant', key: 'id' },
      },
      production_order_id: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'ProductionOrder', key: 'id' },
        comment: 'Optional legacy/context PO; balance is keyed by company + plant + material (shared pool)',
      },
      material_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'Material', key: 'id' },
      },
      uom_id: {
        type: DataTypes.INTEGER,
        allowNull: true,
        references: { model: 'UOM', key: 'id' },
      },
      available_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
        comment: 'Shared Production Storage qty for this material at this plant',
      },
    },
    {
      tableName: 'production_wip_balance',
      indexes: [
        { unique: true, fields: ['company_id', 'plant_id', 'material_id'] },
        { fields: ['production_order_id'] },
        { fields: ['material_id'] },
        { fields: ['plant_id'] },
      ],
    }
  );

  ProductionWipBalance.associate = (models) => {
    ProductionWipBalance.belongsTo(models.Company, { foreignKey: 'company_id', as: 'company' });
    ProductionWipBalance.belongsTo(models.Plant, { foreignKey: 'plant_id', as: 'plant' });
    ProductionWipBalance.belongsTo(models.ProductionOrder, { foreignKey: 'production_order_id', as: 'productionOrder' });
    ProductionWipBalance.belongsTo(models.Material, { foreignKey: 'material_id', as: 'material' });
    ProductionWipBalance.belongsTo(models.UOM, { foreignKey: 'uom_id', as: 'uom' });
  };

  return ProductionWipBalance;
};
