Plenty of mainstream distros have versions designed with an RPi in mind. They should be designed lightweight for that purpose, but also the default version for rpi is called raspbian, and it tends to have the most support for rpi applications. If you’re not committed to a particular distro for any reason that’s a good place to start. All the software should work regardless.
If you want the whole setup to be headless (no screen), you’ll have to do a lot of work in the command line. If you want a screen to play things on, well then just the regular OS version should be fine.
Anything serving a desktop will be more resource intensive. I’m pretty sure the VNC option should have minimal impact whenever you’re not connected to it.
Also though, no matter what you do, it’s linux so you should accept that you’ll need to spend some time in the command line to get things done. It’s getting better with making things accessible via GUIs but I think it may always have a heavier reliance on the CLI because of the hacker nature of it.