As you cast the spell, choose whether it creates antipathy or sympathy, and target one creature or object that is Huge or smaller. Then specify a kind of creature, such as red dragons, goblins, or vampires. A creature of the chosen kind makes a Wisdom saving throw when it comes within 120 feet of the target. Your choice of antipathy or sympathy determines what happens to a creature when it fails that save:
Antipathy. The creature has the Frightened condition. The Frightened creature must use its movement on its turns to get as far away as possible from the target, moving by the safest route.
Sympathy. The creature has the Charmed condition. The Charmed creature must use its movement on its turns to get as close as possible to the target, moving by the safest route. If the creature is within 5 feet of the target, the creature can’t willingly move away. If the target damages the Charmed creature, that creature can make a Wisdom saving throw to end the effect, as described below.
Ending the Effect. If the Frightened or Charmed creature ends its turn more than 120 feet away from the target, the creature makes a Wisdom saving throw. On a successful save, the creature is no longer affected by the target. A creature that successfully saves against this effect is immune to it for 1 minute, after which it can be affected again.
* - (a mix of vinegar and honey)
With it now invoking the charmed condition, Sympathy is an incredibly scary spell for a villain to utilize. Truly some horrifying things that someone creative can pull off with it against common-folk. Truly horrifying. Malicious even.
Just a simple example, any enterprising villain could get all the common folk to desperately want to walk towards a gold coin at the center of a spike/lava pit, or directly into a cage to capture them, or whatever else you really want. As you said, a villain with this spell would be horrifying.
See I have this Mythical Medusa ruling over some Yuan-Ti posing as a Sarrukh and my players "loved" the fact that she had that spell precast on herself at all times after they barged into her Lair with explosions and no stealth at all. So imagine the "joy" they had when they entered her audience chambers (boss room), and most of them "willingly" walked up to her to get petrified.
I have a question about this, would the caster be considered immune? For example, if a player wanted to use Sympathy to lure bandits to a spot, would the caster also need to make a save after the spell goes off?
For another example, if a wizard BBEG wanted to use Sympathy to make a chest irresistible and put a pit trap in front of it, would that wizard then be at risk of falling for their own trap?
No, the caster is not automatically immune to the effects. If they were, it would say so in the spell description. However, keep in mind that when you cast the spell, you specify "a kind of creature, such as red dragons, goblins, or vampires" that are affected by it. In many cases it may be possible for the caster to define this in a way that doesn't include themselves.
Kind: how specific can you get with this? Could you say: "Only Bob the human cleric"? Or would you have to say humans as the most specific you can get, or clerics, or ?
I'd say it's up to the DM's discretion, and the spell seems to be designed to be up for interpretation. If you say "humans" does that mean that the halfling/dwarf/elven, etc. population is unaffected? Okay so then I'd say "commoners", but would that also include the guards who are off-duty, and if so, does that mean that if they don their armor and weapons, they'd become immune to the spell?
In my opinion, you should work together with the DM to figure what your exact goal is and what the limitations are on a case by case situation.