As you say, Brazilian RW as a tonewood is arguably 'better'. Pretty much all you'll get these days is swirly stumpwood with dubious long-term stability so you can effectively take it out of the buying equation.
Honduran mahogany is in short supply too and will soon be on the CITES register; so much so that Martin now build their guitar necks with 'wings' on the headstock in order to use narrower neck blanks and thus conserve timber. They are also specifying 'select hardwood' in most of their spec for necks below the style 40 guitars. These can be mahogany or Spanish cedar; it's the luck of the draw as to what you get; Martin won't spec the wood used in each individual guitar.
Madagascar Rosewood is probably as close as you'll get to the tone of Braz.

What you say about different woods being more suitable to different styles of playing certainly rings true; a ragtime player, for example, might not want to hear a wash of harmonic overtones which you find in rosewood and might prefer the greater clarity and better note separation of the fundamental notes in maple or mahogany.

Something we haven't mentioned is the huge contribution that the top wood and the way it is braced makes to the tone of the instrument.
Cedar will generally impart a warmer and less bright tonality. Adirondack spruce has a reputation for a bright, hard tone while Sitka spruce falls somewhere in the middle.