Poincaré duality, Hilbert complexes and geometric applications