I've never really explored adventure games outside of The Day of the Tentacle and the Monkey Island series due to their linearity and they never really caught my fancy [I felt that they were mostly aimed for younger gamers].
Ohh you would be surprised but both those games... were made for adults! (though Day of the Tentacle likely was made more family friendly.)
Gabriel Knight
The first one is excellent except cripplingly difficult. While the second is easier but not as well developed.
Skip the third.
Broken sword if not mentioned already is excellent... but only the first one is. It has ONE "Unbeatable game" glitch in the first one, so I suggest saving after each location.
The second might be worth checking out if you liked the first... but it is like a quarter as good as the first (and ALSO features an unwinnable scenario)
---
Phantasmagoria 1 and 2
Ok Phantasmagoria 1 is in my opinion an underrated game (a 6 out of 10) but the sheer amount of effort put into it and the subtle horror is what I like about it. It is one of the FEW horror games that take its sweet time to even attempt to scare you.
Phantasmagoria 2 is dreadful... it is horrible in almost everyway. The only good part of it is that it has the most progressive depiction of homosexual relationships in all videogames (where it tends to be at LEAST partially tongue and cheek in other games). HOWEVER! you will WANT to play it because it is so bad. It is one of the few hilariously bad point and clicks in existence and while some puzzles are overly complex, the story (and the easter eggs... Honestly FIND those easter eggs... they are worth it) is worth it.
And yeah, -1 for Alan Wake. Didn't even get an hour through it, I was so disgusted by it. And I generally even give bad games two hours. But between the bad writing (which is the foundation for the whole damn game) and how uninspired the rest of it is, I wish I could scrub it from my Steam list.
Honestly? That is what made me love the game even more. The sheer amount of "Show don't tell" with Alan Wake is amazing! They build him up as some sort of epic novelist but every single scene of the game contradicts this and makes him look like a terrible pulp fiction writer at best.
I mean I love how the novel papers and what happens on screen are so stark from one another. Alan writing a suspenseful supernatural thriller but what is going on onscreen is basically just "and then I mowed them down with my gun... and did it again"
-----
Tip for Point and click adventure games!
Try to see if they have a unwinnable situation or save after every major transition.