Optimal Spectral Transitions in High-Dimensional Multi-Index Models