Seeing the title I immediately thought of Indigo Prophecy, not entirely surprised to see that was the game that inspired the thread ^^
Seriously, it's worth playing just to see how far it really goes. Before it goes off the rails it's an OK, decently atmospheric but pretty unremarkable supernatural thriller, but once it starts to derail, oh boy. I'm kind of glad it did, because the trainwreck is so much more interesting than the actual story.
As for other games, despite the fact that I really like (borderline love) the story in all three games, Final Fantasy VII, VIII and IX all suffer from a confusing, needlessly complicated third act. VIII is the one that people complain about the most, and it's definitely the worst offender, but I think I would have enjoyed all three of them more if they didn't feel the need to introduce all those weird, complex concepts.