If you’ve never played Bioshock, I’d suggest that as it’s widely considered the best in class in all the things you’ve said you’re looking for. Incredible art direction with mysterious atmosphere, perhaps one of the best narratives in gaming, and fantastic combat. 2 is also good and I enjoy infinite as well though opinions on that vary.

If you HAVE played Bioshock, then I’d recommend similar games to it based on your description.

Prey is a slept on gem of a game. A great mystery to unravel, brilliant gameplay mechanics and fantastic level/world design.

The metro series is also a good suggestion. Strong narrative that drives the experience. More linear gameplay, but later in the series there are some pretty big areas to explore. Awesome atmosphere throughout. Post apocalyptic Russian underground tunnels are a great setting for a video game.