It's pretty much a small form PC that you can buy by the board. This is what it looks before you put it in a case (left). Mine looked similar but was an earlier model. The image on the right is when you put it in a case. You can buy many different cases, or even make your own if you know how.
It can be used for many things, like building robots, coding, using it as a PC etc. All you need to do is install an OS on an micro sd card and that's really it. There are guides online how to set it up with emulation station, which works with raspberry pi os. You can connect USB controllers and map the controller depending on game/console etc.
I'm probably not making much sense, but the guide I used was pretty easy to follow, just tedious if anything. I couldn't find the guide I used years ago, but I was able to find this guide that looks to go step by step - https://www.instructables.com/Raspberry-Pi-Emulation-Station-1/
Reading that, they mention an OS called RetroPie, which is needed for Emulation station. - https://retropie.org.uk/
Another thing I used my raspberry pi was for a cheap PC I used for streaming some content on my bigscreen tv. Sadly it died on me a couple months ago, I think it shorted out somehow. And now it won't turn on. :( But the good thing is that they're pretty cheap. The newest one right now, I believe goes for like $60 for just the board, but based on the amount of RAM you want, and you can find kits online for the case/charger/SD card for a decent price as well. I just checked the site, and one retailer is selling the Raspberry Pi 4 for $75 with 8gb, which is probably what I would go with for RAM.
And then for the actual emulation, I used https://emulationstation.org/ which pretty much combines all emulators into one package, so all you'd then need to do is find your roms and that's it. Some of the newer models will work with PS1 and N64. Not sure if it can do later stuff like PS2 and up, but I could be wrong. My Raspberry pi was older, so it didn't work so well with N64 games and later.
And the link to the Raspberry pi site here - https://www.raspberrypi.com/ they will list retailers that sell them. They may be tough to come by lately due to the recent shortage of boards and other PC parts lately.
They make other boards for tech projects, but I only ever used the raspberry pi boards they sell.