What does each of these categories mean?

[Retail Uniform], [Witch], [Batuu], and [Vampire Style] are exclusive to things introduced in the Get to Work, Realm of Magic, Journey to Batuu, and Vampires expansion packs, respectively. You don't have to worry about them unless you have a specific expansion pack feature in mind for your custom content, although it is nice to have!

I suspect that [Retail Uniform] and [Career] are similar in function, so I'll discuss them in more detail later. But in short, "retail" refers to anything related to owning your own retail business, a gameplay feature introduced in Get to Work. So a [Retail Uniform] is something that employees at retail businesses can spawn with.

All of Realm of Magic's internal files refer to Spellcasters as "witch," so [Witch] refers to outfits available for the Spellcaster townies that the game spawns (kind of like what the "[Alien]" tag is for aliens in Get to Work). Likewise, "Batuu" refers to the world that comes with Journey to Batuu (called... wait for it... Batuu!), so [Batuu] refers to outfits available for the NPCs that spawn on Batuu. I suspect that there are additional restrictions preventing [Batuu] outfits from appearing on townies spawned outside of Batuu, but don't quote me on that.

[ Color Palette ], [Style], and [Vampire Style] are used to help narrow down outfit choices for players in CAS. Notice on the sidebar in CAS that there's dropdown menus labeled "Color" and "Style," with options like "Red" and "Preppy"-- those filter content so that only outfits with at least one of those tags are shown to the player. You don't have to be super-specific about the color-- just make your best guess based off of what you did. Same with the [Style] tag. I don't own Vampires, but I imagine [Vampire Styles] work the same way. These tags have no bearing on randomization, to my knowledge.

The [Career] tag refers to available outfits for the game's jobs. For example, if you have a base game job as a Fast Food Employee, you will always wear either the male or female Fast Food Employee uniform. My observation is that the Sims 4 is fairly inconsistent about how it uses the [Career] tags, though. Some careers don't pull from a list of outfits that are marked as [Career]-- this is most noticeable with the Style Influencer career, as the outfit your Sim wears to that job is completely randomized with no regard to any tags (not even Batuu! Obligatory hall of shame from Reddit: Hondo-chic, Alien head, Work bikini). Overall I would avoid this tag unless you're making cosmetic changes to an EA uniform that already has it. [Retail Uniform] is much more specific than [Career], since it applies only to Get to Work's retail ownership careers. It's therefore less error-prone, but I'd still stay away from it just to be on the safe side.

[Situation] means outfits for contexts where your normal Sims' outfits don't suffice but there isn't a context-specific tag for it. For example, like you said, tourists will pull outfits from content that is labeled [Situation]. It's meant to give NPCs some variety in their personal appearances, and I suspect it's most helpful to developers looking for files to compile a list of outfit options for small-scale events than anything else. Although the results aren't quite as out-there as the Fashion Influencer career outfits (mostly because the Batuu stuff and costumes aren't tagged [Situation]), this process is still randomized so it results in some... erm... interesting... outfit choices for Sims.