You’ll never perfectly solve the “no pissing people off” issue because in competitive games you necessarily have people benefit at the expense of others and in cooperative games you’ll fall into the trap of backseat-driver players telling you what to do on your turn.

That being said, here are some of my favorites I’d like to suggest:

Cooperative:

• Time Stories (kinda like a time-travel themed mystery-solving role-playing game where the pre-built deck is your DM. 1-4 players. You can buy more decks, each with a different setting and story.).
• Pandemic (Stop COVID a deadly disease from killing off the planet. Work together to limit the spread and find the cure before it’s too late) (1-4 players)

Competitive:

• The Settlers of Catan (claim resources and land strategically to build the most prosperous kingdom) (2-4 players but there are expansions and spinoffs so this could be like 1-6 players)
• 7 Wonders (draft cards to build the most prosperous kingdom) (3-8 players IIRC)

In-Between:

• Betrayal in the House on the Hill (explore a haunted house until you find a dark secret that turns one of you into a villain the rest have to fight) (3-6 players)