Castlevania's always been mainstream, but it's been on the outskirts of mainstream. It was the mainstream game that people who liked GOOD games wouldn't be ashamed to play. A game hipsters could enjoy but that also had mainstream appeal. All the people I knew who were REALLY into games with maybe one exception owned at least ONE CV game.
The classic games were a good barometer of what was going on with the home console market. CV1 was arcade-based while CV2 and 3 made the games longer, more adventure-like. CV4 made full use of the SNES' Mode7 and pushed the console's capabilities as did Bloodlines on the Genesis. Rondo made excellent use of the Turbo Duo's CD drive and boasted graphics that looked like a full on anime cartoon.
The series took a lot of chances too. They'd establish something then obliterate it. The classic games gave way to SoTN and the exploration games, something that wasn't welcomed by some of the CV fans I knew. Making Alucard the main character in SoTN wasn't a popular move with a lot of the people I knew who were fans from the beginning. But SoTN went on to be one of the series' highlights. Some of the chances they took were rather ill advised (Judgment, but that's more a symptom of Konami going from state-of-the-art publisher to me-too company) but they did a lot more chance-taking than series' like Mario, Zelda, or Sonic did.
Having at least one CV game on most systems definitely didn't do them any favors, but years later it means we have more games to enjoy. But CV was all the rage back in the day. You definitely heard more about CV than you did about Metal Gear, at least until MGS came out.
The games stopped being cool probably around Aria. HoD was probably the last "cool" CV game IMO because even though in places it seemed like they were trying to remind people of SoTn, it was weird, jaunty, and in some ways, supposed to make you go O_o. I didn't like that game as much when I first played it, but it's really grown on me over the years. They watered the subsequent games down a good bit, and as much as I like the LoS series (especially the 1st game) they aren't cool in the same way that the Transformers movies aren't cool.
TL;DR Castlevania has always been sort of mainstream, but it also kept its cred for a long time too.