Are you referencing Picard season one, two, or three?
If memory serves, season one ended with Android

Cthulhu driven away but not defeated while season two ended with the slightly-over-aggressive Friendly Borg guarding some sort of dimensional rift. Season three dealt with the meddling of the Borg Queen (
would have been nice if there were some Friendly Borg around to help) to be defeated by the return of the Enterprise-D.
I liked parts of seasons 1 and 2 of Picard, but both had bad endings in my opinion and didn't really hold up overall. Season 3 was uniformly excellent (I know cynics will moan about it being nostalgic, but tough).
I did like Discovery, though it veered between excellent and atrocious. I think if season 1 had belonged to some other science fiction show, and wasn't labelled as Star Trek, we'd all be lauding it. Season 2 had a really strong start and a messy ending, ditto season 3 (though I was okay with the explanation for the Burn, though I never felt the writers made the most of the concept). I liked the story in season 4 the most, as a good example of a first-contact story, and season 5 was disappointing to me.
I've seen the first season of SNW, it's good in places but I didn't like the characterisation of Spock or the actor playing him, and it seems later seasons became gimmicky (I am *not* watching a Star Trek musical). Maybe the character of Spock is just too ingrained with Leonard Nimoy for me.
As for Academy, it doesn't really look like it's for me though I may get around to watching at some point given the good word of mouth it has got. I always try and remember IDIC, and then think that people criticising these shows are missing the point. I've got the original series and TNG and the movies 1-10 (well, maybe not Nemesis) to enjoy, it's okay if I don't like all the new Star Trek as other people do - infinite diversity of Star Trek in infinite combinations. There's something for everyone. As for those people who think Star Trek has 'gone woke', give me a break - it always has been. IDIC is as woke a concept as you can get!
I was born in 1980 and don't normally wish I'd been around in the 60s, but to have been there when Star Trek was originally airing, and being part of that zeitgeist, yeah I can imagine that would have been quite special - like being around for when B5 first aired.
(I'll save my ire for DS9 though – boring characters, boring actors, boring stories, boring set design, boring music... I did a rewatch a few years ago and fell asleep watching Sacrifice of Angels - twice! At that point I decided to stop rewatching if I couldn't get through what many people think is one of its best episodes!)
Sorry for the spiel. Just been thinking about the Star Trek universe recently so it was a bit of a stream of consciouness!