Anharmonic vibrational many-body theory for molecules and extended systems