I have the Reolink RLC-811A, ticks all those boxes, POE (mine runs on the local UPS services battery with an injector), optical zoom and focus, 4K at least, made of metal.
Standard H246, standard onvif stream that can be used to feed OpenCV and a jetson (as mine does).
Works with VLC, standard linux tools, like motion.
Works with frigate and coral (I have a coral but use it with my own stack).
It has storage (never used it).
It has lights, both white and infra red.
It has motion detection built in that you can access with the onvif interface and some hackery XML.
Also has a good app if you want to access your stream from remote and share it with the chinese government (I use mine locally only).
Edit, the onvif is supported by ispy. (There are two streams, works fine)