I think, honestly, that the Fairy-Type is totally consistent in its distribution and design concepts. It is the best counterpart to Dragon-Type that the series could have made, and to understand why I think it's important to look into what exactly the Dragon-Type is too.
In Pokémon, as well as most mythology, dragons are powerful users of natural energy and bodies. This is represented in Pokémon very well, but is often a forgotten fact by the fan-base because when we think of the Dragon-Type, we tend to think of moves like Outrage, Dragon Claw and Dragon Dance. However, if we look at other moves we see nature being represented extensively. For instance, before the physical/special split, Dragon-Type was considered special, and this was consistent with the early moves that the Dragon-Type had. Moves like Twister and Dragon Breath are both wind-based -- Dragon Breath is described as a "mighty gust" and Twister a "vicious tornado" -- which is a natural energy. However, in both instances, there is unnatural energy involved, as Dragon Breath can cause paralysis and Twister can cause flinching. Roar of Time and Spacial Rend are both Dragon-Type, and both time and space are natural phenomenae that the most powerful dragons can harness (they did, after all, create them). The most powerful non-legendary dragons can use Draco Meteor, which is literally dropping comets from space, one of just about the biggest ways anything could hope to use nature against an enemy. Even Dragon Dance is described as "the user vigorously perform[ing] a mystic, powerful dance" which shows that the user's attack and speed are increased through a dance that accumulates energy, not just through them hyping themselves up like Work Up or concentrating harder like Meditate. There is natural energy being used.
All of this being said, perhaps a good way to describe the Dragon-Type is arcane. As humans, we don't fully understand how this draconic energy works. We see that nature is involved, but only ever in destructive ways both to nature and to the target. Tornadoes are natural disasters, after all. Dragon Rage is nothing but attacking with pure rage -- think of a dragon flying over a forest, burning down all of the trees in hope that its target will also be hit. Almost every animation for Dragon-Type moves involves this dark-blue/purple energy which is all that we know about what this energy is. However, purple has often been used to depict dominion over nature because it's insanely rare to actually find in nature, so even the colour choice around the Dragon-Type serves to help this theory.
With that said, before the Fairy-Type I'm not sure this theory would be quite as sound as it is now. But as we were told that Fairy- is to Dragon-Type as Dark- is to Psychic-Type, we know that a dichotomy is occurring. The number one thing that all fae things have in common is that they are
protectorates of nature. Oftentimes this is because they get their own power from nature, but the crucial difference is that fae creatures almost always harness this energy
symbiotically with nature, the complete opposite to dragons, who simply use it. This works to be a clear counterpart to the Dragon-Type's natural energy, because working with nature to first enhance the natural power and
then using it is more powerful than draining nature of its power in order to use attacks. For instance, instead of Twister we have Fairy Wind, which is a more powerful move but also a more pure version of using wind, as there is no added effect (or tampering with nature's original effect). Instead of dropping comets out of the sky, Moonblast is described as "borrowing the power of the moon". Nature's Madness "hits the target with the force of nature", which I see as a counterpart to Dragon Rage due to both causing fixed damage -- but Nature' Madness is usually much more powerful than Dragon Rage, as nature is not also attacked while performing the attack. The Fairy-Type is, above all else, the "nature" type.
The qualms you had with it come from folklore and mythology, and basically explains the modus operandi of fae creatures. As beings themselves, fairies are almost always depicted as extremely weak. For example, on average Fairy-Type Pokémon have low HP and Defense but very high SpDef -- in fact, the highest average SpDef of any type (Dragon-Type has the highest average SpAtk, maintaining the rivalry). Fairy-type Pokémon also on average have much higher SpAtk than Attack, though physically oriented Fairy-Type Pokémon do exist. This means that in order for fairies to properly utilise their powers, they have to trick the target into not attacking them first. Being cute is one way that this is accomplished, as it makes the opponent let their guard down, making the fae attack do more damage. Everything cutesy involving the Fairy-Type is 2-pronged: Sweet Kiss is adorable but causes confusion, Draining Kiss steals damage like more sinister moves like Leech Life, Disarming Voice literally deals "emotional damage", and Charm lowers the opponent's Attack by "making it less wary" (which to me reads like it should lower Defense or Speed but oh well). Applying this modus operandi, seen used by fae creatures in folklore and mythology worldwide, more widely is Pokémon's way of creating
new fae creatures, rather than simply basing all of its Fairy-Type designs off of existing ones.
- The Azurill family is also based on lapis lazuli, a stone with a rich history in mythology and folklore. The connection to the stone is lost in other languages, but ruri is the Japanese name for lapis lazuli, and features in the name of Azumarill, Marill and Azurill (whose Japanese name is literally Ruriri). Due to it resembling a night sky with its deep blue hue with littered gold specs, the stone has long been seen as a symbolism of nature itself, just as the night sky was used as a sign that nature is far more powerful than we are. For instance, the first recorded creator God in history, the Sumerian deity of Inanna, used her lapis lazuli staff to measure the length of a person's life, as life at that time was seen as a natural energy that living beings were able to harness. The Egyptian Book of the Dead tells us that lapis lazuli was used to protect the dead and priests from evil spirits who would wish to tamper with the natural progression from life into death. It's perhaps not the most traditional thing that could be used as fae inspiration, but there is definite connection between beliefs surrounding lapis lazuli and things that other cultures attributed to fae creatures. So I see it as being a way of broadening the scope of of Fairy-Type away from just mediaeval or later folklore, created after fairies had too, to having a Pokémon whose fae energy comes from myths of ancient times.
- It's said that in Britain when belief in the fae was near-universal, even children were warned that if they ever ended up in the fairy realm they must never touch the food or drink, for if they did they would be trapped in the realm forever, or banished to the human realm where the food we have here pales in comparison to the extent that we would rather starve than eat it. Due to this, I've never questioned the Fairy-Type's connection to food in Pokémon. I'd go so far as to say that this is likely even why fairy floss was named as such to begin with, plus it seems somewhat other-worldly anyway. However, to throw another theory out there as to why Milcery and Alcremie in particular are Fairy-Type, we need to look at Alcremie-Gigantamax's dex entry. "It launches swarms of missiles, each made of cream and loaded with 100,000 kilocalories. Get hit by one of these, and your head will swim". In a way, a calorie is the scientific measure of natural energy -- that is, energy that exists naturally, and that sustains the life energy of other beings. Perhaps the fae spirits in Milcery and Alcremie harnessed the nature power of calories, and utilises that in battle. Sounds kind of weird, but I think the theory checks out.
- I think Light of Ruin is particularly interesting because it deals recoil damage. This is the only Fairy-Type attack that has a destructive effect on nature, and the recoil damage demonstrates this. I think it's a cool bit of worldbuilding, and it's a shame it was never released.
I think this is valid until you realise it's Pokémon. There are very few Pokémon that have genuine malice and the majority of them are legendaries, with the entire list I can think of being Mewtwo and Giratina. Even the Dark-Type, which should be Pokémon's "malice" type, are almost always depicted as being misunderstood. Absol doesn't cause disasters but is wrongly labelled as doing so. Persian-Alola tortures its prey, but in exactly the same was as actual cats do, and we don't see that as malicious. The Scraggy line and Zigzagoon-Galar lines are hooligans because of hard knocks, not because they're truly malicious. Even Malamar, who has "mal" in its name, according to the PokéDex is utilised by people for nefarious deeds, rather than performing nefarious deeds themselves. It just doesn't make that much sense to have genuinely evil or malicious Pokémon, because the vast majority of kids aren't genuinely evil or malicious. As a species we generally prefer to beat evil than harness it, and the philosophy of Pokémon designs has been descibed by designers as creatures that we would like to see as being our friends.
tl;dr The Fairy-Type is consistent in its design. It can be described first and foremost as a type that works
symbiotically with nature, and its properties are that of
mischief and
deceit, which is why many Fairy-Type Pokémon appear cute. This serves to form a dichotomy with the Dragon-Type, which works
parasitically with nature and involves things like rage rather than mischief.
