Diffuse neutrinos from luminous and dark supernovae: prospects for upcoming detectors at the 𝒪(10) kt scale