Motion sensors to turn on an outlet that powers a water pump

That is what I use and for me they work well.

The motion sensor does not turn on anything. When used with node-red, the motion events are picked up by node-red (usually via zigbee2MQTT) and then you can do anything you like, such as controlling your pump. Node red should be able to control any zigbee smart socket, but check the zigbee2MQTT website to ensure compatibility. I have a SmartThings mains socket with power meter GP-WOU019BBDWG, but there are many to choose from.

My personal choice is not to use Home Assistant, but to do it all in node-red, which is what most here do.