Piracy: ꜱᴀɪʟ ᴛʜᴇ ʜɪɢʜ ꜱᴇᴀꜱ

I already did some searching and found that games can be played offline fine (most of them, some exceptions are there like Multiplayer and Mortal Kombat)

Yes, most games will work offline just fine even with a multiplayer mode. (You just won't be able to access multiplayer.) I believe you can still play Mortal Kombat 11 offline but it locks you out of a lot of content IIRC.

There is something like the paid Nintendo Online Account. I am not planning on having a paid account. How much of the system depends on the account?

The account creation is completely free. You can even make a local account but you won't be able to play games online or use the eshop without making a Nintendo account. The only thing you "need" to pay for is a $20 annual Nintendo Switch Online subscription to play your games online. (Note that all games don't require the Nintendo Switch Online subscription, but most do. This includes all of Nintendo's first party IP's.)

Can I have progression in a game (let’s say: one of the Zelda franchise) and will my Wife and Kids all have their own progression, without having to pay for X accounts?

All of your games will have separate saves with different profiles. Note that all of the accounts you make don't need a Nintendo Account, so you can make a Nintendo Account for yourself (which is free) if you want the features it comes with, and local accounts for your wife and kids, and any game they play will have different saves that won't conflict with your saves. The only exception to this that I know of is Animal Crossing New Horizons. You can have different accounts and players on one Switch but you are limited to only one island per console.

People who own a Switch, let’s take this to extremes, do you feel like in 20 years from now you can still do the same things on your hardware as you can do now? (No multiplayer is fine)

Personally, I'm not sure how long the Switch's hardware will last. If the durability and longevity of Nintendo's other consoles are anything to go by, I'd say it has a fair shot of lasting a while; with the exception of maybe a battery replacement and/or new thermal paste. I have both the OG model and an OLED model, and I can say for certain that the OLED model runs cooler and quieter than my OG model. Even if the Switch's hardware fails I will always have my games and saves backed up on my PC so I can always play my games through an emulator on more powerful hardware.

Edit: I completely forgot to mention joycons. I think these will be the first things that get replaced with any amount of long-term use. I've already gone through 4 pairs on my OG switch because of joycon drift. My OLED is holding up fine though (thankfully), but I think the cheaper option would be to just replace the joycon's thumb-sticks with hall-sense sticks, and they should (in theory) last quite a bit longer.