module.exports = (sequelize, DataTypes) => {
  const PurchaseOrderRateHistory = sequelize.define('PurchaseOrderRateHistory', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    po_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'PurchaseOrder',
        key: 'id'
      }
    },
    item_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'PurchaseOrderItem',
        key: 'id'
      }
    },
    old_rate: {
      type: DataTypes.DECIMAL(18, 2),
      allowNull: false
    },
    new_rate: {
      type: DataTypes.DECIMAL(18, 2),
      allowNull: false
    },
    changed_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    },
    changed_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: DataTypes.NOW
    },
    revision_number: {
      type: DataTypes.INTEGER,
      allowNull: false
    }
  }, {
    tableName: 'purchase_order_rate_history',
    indexes: [
      { fields: ['po_id'] },
      { fields: ['item_id'] },
      { fields: ['changed_by'] },
      { fields: ['changed_at'] },
      { fields: ['revision_number'] }
    ]
  });

  PurchaseOrderRateHistory.associate = function(models) {
    PurchaseOrderRateHistory.belongsTo(models.PurchaseOrder, { foreignKey: 'po_id', as: 'purchaseOrder' });
    PurchaseOrderRateHistory.belongsTo(models.PurchaseOrderItem, { foreignKey: 'item_id', as: 'purchaseOrderItem' });
    PurchaseOrderRateHistory.belongsTo(models.User, { foreignKey: 'changed_by', as: 'changedByUser' });
  };

  return PurchaseOrderRateHistory;
};
