/r/wowUI is a nice little place to get some inspiration if you don't want a pre-made suite (like ElvUI, TukUI, etc) and can help you out if you're trying to make stuff on your own.
Curse's big library of add-ons
WowInterface's website, alternative to Curse but not as popular.
WowAce's website. Haven't used them before but still used a bit by others today.
L-UI's website & download. Not a fan, but still one worth mentioning.
gUI - Goldpaw's UI is another UI "suite", some parts may have to be installed separately. I don't use gUI myself, but I definitely like the look of it the most. Not as customizable as ElvUI or LUI.
Other suites listed on WoWInterface, sorted by most downloaded.
Recount and Skada after all this time are still good damage indicator mods, though if you're looking for something more you can always pick up "Details!"
MasterPlan is IMO a must-have for your garrison annoying chores dailies, helps form optimal groups, upgrade your followers easily, etc etc.
If you're someone who sits around in the Auction House all day, Auctioneer or Auctionator might be of use. I prefer Auctionator, but if you want to be like the cool kids at /r/WoWEconomy you could consider using the TradeSkillMaster suite. Much more advanced than the other two add-ons I mentioned, definitely something to use if you have lots of items you're buying or selling.
My personal favorite as of recent, Legion Wardrobe, which aims at trying to prepare you for the new transmog system in Legion. As you may have heard, if you get 25 of each xmoggable item you will get "(name) the Fabulous" as a title.
Out-dated, but still functional comes EasyMail - useful if you have tons of mail you need to collect at a time or just simply making your time at the mailbox a billion-gazillion percent easier.
As I said, if you don't plan on using a "suite" add-on pack like the ones I listed above, then you won't be able to move like any of your UI elements. But wait, MoveAnything lets you do just the thing. Haven't used myself, but the name explains its own function.
Server Hop is good for the time being, though I'm not a fan of realm-hopping myself this may come in handy if you're hunting down a rare, pet, or just some bots to gank. I dunno.
WIM (WoW Instant Messenger) is a good add-on for people who get a ton of whispers from their friends, guildies, or whatever. This'll help organize the people in tabs and has a bunch of options ranging from sound effects on sent/receive, font, you name it.
Another being MogIt, which might go nicely with the Legion Wardrobe add-on I mentioned earlier. Build a transmog set, view some item models, find if you have a certain item or not, and etc.
Lastly, some "fix" add-ons such as StrataFix ("Ever encounter a bug where UI buttons are not clickable or elements are mysteriously missing?"), LibStrataFix, or World Map Zoom Fixer.
CollectMe is for the crazy people who want to farm a whole bunch of mounts, titles, etc. I like using this, because I can see how many raid-dropped/dungeon-dropped mounts I still need to obtain.
SavedInstances is a god-send if you have a lot of characters doing a lot of PVE content. It'll track your raid lock-outs, currency, and I think even cooldowns on your professions and whatnot.
ElvUI AddonSkins which is only needed if you have ElvUI downloaded. This'll skin a majority of the well-known add-ons out there to have the same look-and-feel as ElvUI does - examples include Recount, Server Hop, DBM, and so on. Full list @ the link.
xMerchant, out-dated yet still working fine to this day changes the look of the vendor interface. Instead of a bunch of pages to go through, this'll just make it a long, searchable scroll-y list.
TL;DR - just get a good browse through some of the add-on websites, get some things that suite your needs.
Most of the ones I listed above are in my interface->addons folder right now, and I've been using them for quite awhile. Here is a rough example of what I have right now - using ElvUI, Recount, and MasterPlan.
As for which add-ons I don't like ("which addons are terrible??") - I'm not a fan of the LUI suite, Details! DPS meter, DBM, Postal (compared to EasyMail), Gladius (arena frames), and some of those 'healer' addons like VuhDo or HealBot Continued. Though this add-on isn't really needed nowadays or really used by many, I hated using oQueue before the pre-made group finder was released. The add-on author would update multiple times a week, and add a bunch of useless stuff that cluttered the interface of the add-on. Try to treat some other add-ons that way, if you feel the author(s) is/are just being dumb with their updates perhaps look for an alternative.
Other than that, will edit in or edit out any more I feel should(n't) be in this list. Give /r/wowUI a visit and see what you can come up with! :D
