all of them seem to only have female AV inputs and this weird plug has female audio jacks and no video jack which is confusing the hell outta me.
It does have video jack, or actually pins, built-in audio pins and RCA sockets for extra stereo input.
What exactly do I need to plug this into the composite outlets on my tv?
Just plug in the SCART cable to both ends. You can leave the RCA sockets unplugged.
The SCART cable has 21 pins (one is ground) which are used for different video protocols, all of them analogue.
It will trasmit signal in either component, S-Video or RGB, being the latter the best possible, so opt for it if you're given the choice.