I bought Red Faction expecting it to be the sort of badly aged, nostalgia-only game that so many old FPS games are, but I was curious to see how the destruction system had evolved.
It's actually playable though, the closest game to it in style is Half Life 1. Although it wisely sticks to a simple story well suited to the sparse storytelling style.
It's no more than mildly enjoyable, but the pacing is generally well done with undemanding optional stealth sections and vehicle sections mixed in with the shooting-stuff-on-foot bits.
I found the difficulty to be well judged too, although it's built around accurate semi-automatic weapons so anyone not used to games dependant on mouse accuracy may struggle.
The save system is unforgiving so you'll want to use save slots and quicksaves frequently. Especially towards the end of the game where the rail-cannon wielding soldiers are difficult to identify before they fire.
The destruction system in the single player campaign consists of corners being blown off walls when enemies throw or shoot explosives at you, and maybe the occasional obvious shortcut through a wall. There's no building destruction at all.
It's not a game I'd recommend for someone just looking for something to play, but if you've got interest in this game or era then there's some enjoyment to be had.