module.exports = (sequelize, DataTypes) => {
  const MaterialRequestItem = sequelize.define('MaterialRequestItem', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    request_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'MaterialRequest',
        key: 'id'
      }
    },
    production_order_component_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'ProductionOrderComponent',
        key: 'id'
      }
    },
    line_number: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    material_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Material',
        key: 'id'
      }
    },
    material_number: {
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: 'Line-level material number (e.g. from nesting); description may hold spec text only'
    },
    description: {
      type: DataTypes.STRING(500),
      allowNull: true
    },
    required_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false
    },
    uom_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'UOM',
        key: 'id'
      }
    },
    available_stock: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0
    },
    shortfall_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0
    },
    issued_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0
    },
    pending_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0
    },
    storage_location: {
      type: DataTypes.STRING(10),
      allowNull: true
    },
    required_date: {
      type: DataTypes.DATEONLY,
      allowNull: true,
      comment: 'Date by which the material is required (from Production Order finish_date or Customer PO delivery_date)'
    },
    remarks: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    is_substituted: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      comment: 'Whether this item has been substituted'
    },
    original_material_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'Material',
        key: 'id'
      },
      comment: 'Original material (if substituted)'
    },
    substitution_transaction_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialSubstitutionTransaction',
        key: 'id'
      },
      comment: 'Reference to substitution transaction'
    }
  }, {
    tableName: 'material_request_item',
    indexes: [
      {
        fields: ['request_id']
      },
      {
        fields: ['material_id']
      },
      {
        fields: ['production_order_component_id']
      }
    ]
  });

  MaterialRequestItem.associate = function(models) {
    MaterialRequestItem.belongsTo(models.MaterialRequest, { foreignKey: 'request_id', as: 'request' });
    MaterialRequestItem.belongsTo(models.ProductionOrderComponent, { foreignKey: 'production_order_component_id', as: 'productionOrderComponent' });
    MaterialRequestItem.belongsTo(models.Material, { foreignKey: 'material_id', as: 'material' });
    MaterialRequestItem.belongsTo(models.Material, { foreignKey: 'original_material_id', as: 'originalMaterial' });
    MaterialRequestItem.belongsTo(models.UOM, { foreignKey: 'uom_id', as: 'uom' });
    MaterialRequestItem.belongsTo(models.MaterialSubstitutionTransaction, { foreignKey: 'substitution_transaction_id', as: 'substitutionTransaction' });
  };

  return MaterialRequestItem;
};

