Assume we're at the state where the potassium gates have closed. Inside the membrane should still be more negative relative to its resting potential. This source claims that the sodium-potassium pump brings the membrane potential back up to the resting potential. However, as far as I understand, sodium-potassium pump creates a net negative charge inside the cell by pumping out three Na+ molecules and bringing in two K+ molecules. How would the pump make the membrane potential more positive? Please let me know if I am misunderstanding something.
The sodium potassium pump keeps ion concentrations consistently imbalanced with sodium higher outside and potassium higher inside the cell. The direct impacts it has on voltage are negligible, I'd recommend just ignoring them. If your source specifically says the pump returns the cell to rest after hyperpolarization, this is wrong. A cell could fire several action potentials and return to rest without any pump required, because ion concentrations hardly change during the whole process. You only need the pump in the long term to maintain ion concentrations.
What is important for determining membrane potential is conductances to various ions and the weighted sum of their reversal potentials. Individual reversal potentials can be determined by the Nernst equation; the weighted sum can be determined by the Goldman equation. All the questions you have about what voltage a cell is moving towards are answered by these related equations.
The reason a cell can become hyperpolarized after action potentials is because the weighted sum of ion reversal potentials at rest is less negative than that of potassium alone. If you have extra potassium channels open (voltage gated potassium channels), membrane potential will head closer to potassium reversal.
Nothing special has to happen to return to rest, it's just that at rest the combined conductances of all ions through leak channels and other channels give an expected membrane potential that is not as negative as potassium alone. You can think of this as mostly due to modest resting conductances of sodium and chloride.