Good mysteries involve providing just enough hints so that the player can make the fun leaps of logic and experience "Aha!" moments. People don't want to play the job of a real detective (collect endless witness statements, construct extremely detailed timelines) they want to be the hero in a detective story, making clever deductions.
LLMs might be able to generate characters and dialogue but they're going to struggle to come up with completely new ideas and mystery mechanics like Her Story
It's really interesting but it's also of course really easy to break out of sensibility, and hard to properly gamify. Like you said people don't actually want the tedious part of detective work, they want the fun parts that make them feel like Sherlock.
The game Shadows of Doubt is a great example of how the information flow of detective work is really difficult to balance, I think they did a great job but if you run out of really strong clues, clues that link things together, the game becomes a brute force exercise in talking to everyone and asking the same questions.
You underestimate how many people would be really into what you described. Lots of wildly popular games are work in a fun-looking package. Factorio is just programming. Football Manager, and every other sports manager game for that matter, is spreadsheet after spreadsheet with some cool graphics.
There is one tech demo which has you piecing together what happened at an accident by talking with the NPCs.
There's also a Skyrim mod that replaces all the NPCs with LLM actors.
I think the turning point for that technology will be locally run LLMs that can be very finely tuned and trained on your world data.