Electrical – Double neutral wiring

I replaced the light switch itself and the only thing in the box were the 2 wires connected to the old switch and the ground wire

Since there is only 2 wires in the switch box, that means the switch is at the end of the line. So to fix this light, you just need to wire it like this diagram:

To figure out which cable coming in to the light box is from the breaker and which is from the switch, hook the light directly to one cable and ignore the other cable. Turn the breaker on. If the light comes on, then that cable is coming from the breaker.

Let me guess, the room with that outlet, most likely a bedroom, doesn't have a ceiling light fixture. That or a ceiling light was added later on. And one of those outlets never worked.

The setup you described is typical of bedrooms in many homes where the primary light source is a plug in lamp. Since the lamp only needs one outlet, the other outlet can be left hot at all times instead of switched. So the electrician runs a three conductor cable from the light switch: white is neutral, black is constant hot, and red is the switched lamp socket. This way you can plug in an alarm clock or phone charger and not have it controlled by the switch.

My guess is the electrician was inexperienced and broke off both tabs without realizing they only needed to break off the hot tab. Probably a new guy tasked with doing the grunt work of wiring the receptacles and fixtures. No one ever bothered to check both sockets and the rest is history.