Why does higher aperture, less light, make an image sharper? Or rather make everything in focus? And how does this lead to lenses having a sweet spot in sharpness?
A higher f-number (technically a smaller aperture) contributes to sharpness in two ways. Firstly the depth of field is increased, thus objects which would appear blurry are now rendered sharp. Secondly a smaller aperture reduces aberrations which cause the image to appear soft even at the plane of focus.
In a perfect lens light coming from an object spreads out, passes through the aperture and then is focussed into a dot on the film/sensor plane. However real lenses suffer from aberrations, such as spherical aberration whereby the light passing through the aperture isn't all focussed at the correct distance, light passing through the edges of the aperture might come into focus in front of or behind the sensor, and thus not form a precise dot but a smear. Closing the aperture simply blocks the light from the edges thus it can't have a softening effect on the image.
image by Lookang
If you make the aperture too small then diffraction occurs, whereby light spreads out, again causing a softening of the image. So for every lens there is a crossover point where the increase in sharpness from reducing aberrations is balanced by the decrease in sharpness from diffraction. This is the "sweet spot"
This question is exactly a duplicate of a prior one Why small aperture (less light) has large depth of field?, but that was closed as a duplicate of a more technical response.
A common concept in both this question and the direct duplicate is considering aperture as primarily a control over the amount of light in the exposure — it seems like maybe less light would be bad for image quality.
Aperture is a control of the amount of light, but it works by decreasing the hole through which light passes, which (as explained in the technical answers) has the "side effect" effect of increasing the depth of the image which is in sharp focus. This has nothing to do with the amount of light per se.
The unstated assumption in the answers is that shutter time is increased to make the exposure correct — assume that there's enough light for the image in any case. If the exposure weren't made to be correct, the underexposed result wouldn't be less sharp, but if you've underexposed to the point where the scene is unrecognizable, sharpness is kinda irrelevant. Alternately, if you compensate for the reduced light by boosting ISO sensitivity, the result will inevitably have more noise (because less light is less signal), and while that doesn't degrade sharpness in the same way technically, noise interferes with the perception of sharpness (and noise removal techniques in post processing do have a smoothing/blurring effect).