I think the end of the story line ruined it though and the fact that all side stories and characters were all a lot more interesting than any of the "main characters". It's like each and every minor side character excelled in amazing and surprising ways coming out of nowhere, while the main plots main characters were largely kind of dull (with exceptions once in a while).

Maybe they just overdid it with two shows largely running in parallel and then wanting to wrap things up?

That said I think the asymmetric war was always - throughout all of history - a classical topic. Everyone wanted to portray themselves as the rebels fighting an impossible fight. From David and Goliath over the French and American Revolutions, to Putin and Netanyahu trying to frame things as "being surrounded by enemies, fighting on their own". Heck even the Roman Empire tried to internally portray imperialism as just, being attacked by the whole world of wild people. Or look at how the left and the right nowadays portray the government as that big oppressor secretly or openly working with the other side.

There is an interesting... not even a show.. let's say "animation series" called Aeon Flux (not the movie, not the comic show) that tries to go into these perspectives a bit, but it kind of stands out among all the media that is either "that evil oppressive regime" and "those horrible terrorists". Wished there was more like that that didn't feel so try-hard.

Male-female interactions were usually really weird, Bortus relation with his partner is equally anchored in this idea that in male homosexual relations one has to "play the woman part", and i felt overall that the series had much more of an imperialist human-centered worldview, much closer to Stargate than Star Trek (despite not remembering the details).

The ones nerds glaze are the shows normies complain about. IMO because there is tension between what the mainstream wants from trek and what niches like ours want. That's ok. It just requires trying to understand the era of creation as much as the art itself.

The problem with TNG wasn't so much the preachiness, which is still normal for TV. It's that the moral system behind it was actually quite poor. Picard's character was certainly not the paragon this thread is making him out to be - the more you look at Star Trek the worse it morally gets. It's one of those shows that works best when you don't think about the worldbuilding too much. And the Picard character only seems moral and superior on the surface because of the way Stewart acted him, and because his character is so unemotional, not because of what he actually does.

Consider the moral lessons of TNG and Picard.

Picard isn't married, has no family or children, and apparently no desire to change that. He's actually distinctly uncomfortable around children. Instead he married his job, which is all consuming. Right there that renders the character boring, overly intense and unrelatable for many people. When he gets assimilated by the Borg there's no emotional impact because it's not clear who cares about Picard being turned into an alien except his staff, whose only concern is professional duty. In fact when Riker decides to attack the cube with Picard still on board, he barely hesitates, and why would he? Picard keeps everyone around him at great emotional distance at all times. When the writers want to show Picard having an emotional breakdown about his traumatic experience, they have to show him running home to a brother he hardly ever sees or talks to, because that's the only character the writers have who might care about Picard as a person. Sad.

Picard is supposedly a culture maven, but neither him nor his crew ever creates any. They're obsessed with replaying stories and music old even when the show was made like Moby Dick, Beethoven, Shakespeare, detective stories set in the 1940s.

Although the Enterprise has many crappy jobs on it along with many scarce resources (e.g. holodeck time), crew are unpaid. Picard says everyone on the Enterprise is there because Starfleet is prestigious and he seems fine with that idea. This arrangement could never work in reality so he comes across as delusional, and worse, we are left to assume there actually are mechanisms that incentivize or force people to beam down into severe danger, but Picard pretends they don't exist.

By the 1980s Roddenberry was a Maoist and the Federation appears to be some sort of communist dictatorship. We're told it's abolished money and we don't encounter operating businesses or conventional democratic politics. There is notionally some sort of elected leader in the Federation but they don't seem to control anything and Starfleet does whatever it wants. For example, TNG doesn't show Picard being given new orders because the elected government was changed, nor does Picard ever wonder how his actions might affect politics back home.

Picard holds the capitalist Ferengi in contempt and makes dumb diplomatic mistakes, like referring to "civilized societies" when talking to a Ferengi captain despite that the Ferengi fly around in advanced spaceships. This is because Picard views trade as dirty and beneath him.

Although he does occasionally disagree with Starfleet orders, he slavishly follows the Prime Directive even when the outcome would be absurd, like the entire civilization dying. He never even questions this rule despite the writers realizing that it leads to immoral outcomes! Starfleet is ambiguously presented as sometimes a sort of scientific research org and other times a military that in DS9 implements full blown martial law, suggesting the normal military/civilian separation doesn't exist. Picard never questions the structure of Starfleet or Federation society, which he seems to view as automatically superior (Sisko does a lot better in this regard).