A white PC Engine only has RF out, the Core 1 and Core 2 have AV out. All three have slightly different cases, but aside from the video outputs they are all the same. They can all hook up to the CDROM2 or Super CDROM2 and those have AV outputs themselves so...pick the one who's color you like the most, I guess.
A Super CD is a format of CD game that needs either the Super CD card to run, or a system that already has the "Super" built into it, like the Duos or the Super CDROM add-on. The vast majority of PC Engine games are in Super CDROM format.
The Supergrafx is backwards compatible, yes, and can also hook to the Super CDROM add-on, although it needs an adaptor to hook up to the original CDROM2. The Supergrafx is basically a giant-ass PC Engine that can play a half dozen games that regular PC Engine's can't. They games, honestly, aren't that amazing. Therefore the SGX is for hardcore PCE freaks only since its kind of pricy to get into, and there are so many other better games in regular HuCard, CD, or Super CDROM formats.
Shortcut answer: you want a Duo R.