module.exports = (sequelize, DataTypes) => {
  const StockReservation = sequelize.define('StockReservation', {
    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'
      }
    },
    material_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Material',
        key: 'id'
      }
    },
    storage_location: {
      type: DataTypes.STRING(10),
      allowNull: true,
      comment: 'Storage location code - null means reservation for any location'
    },
    reserved_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0.000
    },
    issued_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0.000
    },
    available_quantity: {
      type: DataTypes.DECIMAL(18, 3),
      allowNull: false,
      defaultValue: 0.000
    },
    reference_type: {
      type: DataTypes.ENUM('PRODUCTION_ORDER', 'SALES_ORDER', 'MATERIAL_REQUEST', 'MANUAL'),
      allowNull: false,
      defaultValue: 'PRODUCTION_ORDER'
    },
    reference_id: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    reference_number: {
      type: DataTypes.STRING(50),
      allowNull: true
    },
    reservation_date: {
      type: DataTypes.DATEONLY,
      allowNull: false,
      defaultValue: DataTypes.NOW
    },
    required_date: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    status: {
      type: DataTypes.ENUM('ACTIVE', 'PARTIALLY_ISSUED', 'FULLY_ISSUED', 'CANCELLED', 'EXPIRED'),
      allowNull: false,
      defaultValue: 'ACTIVE'
    },
    cancelled_at: {
      type: DataTypes.DATE,
      allowNull: true
    },
    cancelled_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    },
    remarks: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    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: 'stock_reservation',
    underscored: true,
    timestamps: true,
    freezeTableName: true
  });

  StockReservation.associate = function(models) {
    StockReservation.belongsTo(models.Company, {
      foreignKey: 'company_id',
      as: 'company'
    });
    StockReservation.belongsTo(models.Plant, {
      foreignKey: 'plant_id',
      as: 'plant'
    });
    StockReservation.belongsTo(models.Material, {
      foreignKey: 'material_id',
      as: 'material'
    });
    StockReservation.belongsTo(models.User, {
      foreignKey: 'created_by',
      as: 'createdByUser'
    });
    StockReservation.belongsTo(models.User, {
      foreignKey: 'updated_by',
      as: 'updatedByUser'
    });
    StockReservation.belongsTo(models.User, {
      foreignKey: 'cancelled_by',
      as: 'cancelledByUser'
    });
  };

  return StockReservation;
};

