Great character on a great model can be a really exciting experience.
You get the right setup (ie sci-fi/modern/medieval, family/school/trip), interesting character(s) and good story.
On bad or mediocre models (which is the norm sadly), this is more like "meh", but still better than the average text game.
On the good models you basically get the challenging roleplay partner, or better yet, spicy Game Master and a slew of the good characters.
If you're chasing a good, satisfying short story culminating in, ekhm, culmination, you can easily find the character that matches your preferences however they are. It's also possible (albeit challenging) to find amazing model which will then lead you to the climax the way you want.
It can be really satisfying and about as good as a human roleplay partner.
Of course it won't replace human-to-human interaction, but as a someone with profiles on one of the large roleplay sites, much more often than not (95%+) players don't want any interactions beside roleplay, so..... it's pretty much like RP with a bot.
If you want to dip your proverbial finger in the good AI roleplay, try c.ai -- they have by far the best freemium model and allow for the free voice model creation -- if you live in the long distance relationship with someone, that may be a gamechanger for you two.
If you have a reasonably good GPU in your PC, try Backyard.ai for the local models -- now, in late 2024 they're no longer clunky and terrible, some are actually pretty good.
I'm not sure if it's allowed to post the link to the large(st) database of characters, so I won't, but just look up the "tavern character" - tavern is the type of the character card, they usually come in the form of png or json file.
Other than "chat" cards and models, there are also scenario / story mode models, but the most consistent ones are slightly harder to set up and navigate.
And lastly - concerning image generation: other than thousands of the online services you can find free model on civitai and use them with the local instance of Stable Diffusion UI (by automatic1111) or any other ui. There are hundreds of youtube channels covering it in depth.
If you want more hints or answers, ask away.
I find a great fun in playing with my favourite characters.
It takes a bit of time to set it up right, but at the end you get a really great CYOA with the character(s) you want.
Like a text game but better, because 90% exactly like you want it.