The first three Metal Gear Solid games have fascinating stories. They’re not the most logically sound stories and they have a lot of weird elements that make them hard to follow. At the micro scale they’re not the most comprehensive. On the macro scale they’re not hard to get into at all. When the story hooks you it really hooks you.
I was a child when I completed the first two MHS games. I never finished MGS3 because the disk got smashed to pieces. I do remember liking it a lot though. MGS2 is my favorite of the bunch. It was a good mix of old characters and new that carried the story in a satisfying way.
The absolute best story in video games(IMO) is the Mass Effect trilogy. For the longest time those games set the bar for video game story telling to me. Every character was so easy to get invested in. I cared for all of them and deciding who completed the journey or not was a series of deeply personal tough choices that carried across three games. Did the ending suck? Absolutely. Do not let that last 0.1% mar what is easily %99.9 one of the best space scifi stories in any medium.