For that range, you're probably looking at an entry-level DSLR. Personally, I wouldn't recommend that anyone pay more than $300 (if that) for any point-n-shoot camera. The most affordable DSLR is the Pentax K110D which is just under $400 including kit lens. They have a rebate on the K100D (which adds image stabilization) that ends in, oh, 15 minutes or so, which brings it to $409 after rebate (Beach Camera just lowered their price $10), but there'll probably be another rebate starting soon.
Other options including the Nikon D40, Canon Rebel XT, maybe an Olympus E500... the Sony A100 is probably going to be slightly over your budget.
However, any of these are going to have a fairly short zoom lens as the "kit" lens. You can add a lens with longer reach, but it will add to the total cost, of course. You can also buy the DSLR with no lens and add a single lens that has a very long zoom range, but generally as the zoom range increases, the optical quality decreases.
If zoom is more important than low-light, then you might be able to get buy with a point-n-shoot, but they all have issues in low-light. The Fujis with the SuperCCD HR (not the "plain" SuperCCD) are head and shoulders about the rest in low-light ability.
Oh, and for things like the castle at night, your best friend is a tripod, this will allow most any camera to get great night shots of static objects. As for SpectroMagic, that's very difficult and if you want satisfactory shots without a flash, you're pretty much doomed with a point-n-shoot, and even with a DSLR, you'll want a lens that works especially well in low-light, more so than the kit lens.