Now you're talking my language.
There are tons of great NES games out there. Unfortunately right now, obscure + good = $$$$$$$$$$$$$. There are games like Little Sampson, which is obscure and fantastic, going for $1000+ just for the cartridge alone. But not to discourage you, there are still tons of great games for the NES. Some of my favorites:
The Mega Man games: There are 6 for the NES. All of them are great. The 1st one and the 5th one are really pricey. The 2nd and 3rd are my favorites, and those are the most common; a little more than your average game but well worth it. In fact, the 2nd is probably my all time favorite NES game!
Legacy of the Wizard: Well, you mentioned it. It's a good game! It's actually part of a huge series over in Japan called the Dragon Slayer series, and the first in the series is considered to be the 1st Action RPG ever (think Zelda).
Tecmo SUPER Bowl: Everyone knows Tecmo Bowl, Bo Jackson and all that. Forget that game and get the sequel: Tecmo Super Bowl. It's the best football game of all time. Easy to play, all the players and teams from the 1990 season, customizable rosters and playbooks, etc. It rules.
Super Dodge Ball: One of the best 2 player games on the system. It's also part of a larger Japanese series, called the Kunio series about a teenager in a rough town and rival gangs.That leads me to another game...
River City Ransom: It's a side scrolling beat-em-up like Double Dragon, but better. Features the same "Kunio" guys from Super Dodge Ball and a bunch of other games. And the enemies say funny crap when they die!
P.O.W: Prisoner of War: Another side scrolling beat-em-up, but this time, you have to punch and kick your way out of a prison camp. It's like an 8-bit 80's action movie!
Iron Tank: from the same people that brought you P.O.W. comes this one. You command a little tank, and you have to infiltrate and destroy the not-Nazis! (They are definitely Nazis.)
Jackal: This one always flies under the radar. You have a Jeep, and it's your mission to rescue all the hostages, drop them off at the helipad, and then destroy the enemy! Tons of fun, especially with two players.
DuckTales: A Disney game that runs on the Mega Man engine. All sorts of nostalgic fun in this one.
Rad Racer: Remember Outrun in the arcades? This is as close as you get on the NES. Also, it was made buy SquareSoft, the Final Fantasy people, so you know it's good. And it had real cars!
Rolling Thunder: It's basically a James Bond-inspired side scrolling shooter. Plays really well, and challenging.
Rescue: The Embassy Mission: You command a special forces team that has to bust into a high-rise, rescue hostages, and kill all the terrorists. Lots of varied gameplay, and tons of fun.
I can go on and on....