I found armor/weapon maintenance in the Witcher games pretty annoying. There were kits everywhere, so they weren't hard to get ahold of; there was no challenge to equipment maintenance that might have made it interesting. Using them is trivial, so that's not interesting. The worst thing that happens is a sword might go red and be a little less useful in the middle of a fight, that's barely interesting. But you had to lug the kits around, so they took up a little space in your inventory. That's also not interesting. It's all just extra work for no payoff. It doesn't create interesting decisions. It'd be a touch different if neglecting your gear meant you were at risk of it irreparably breaking (and there weren't 100 other swords out there to replace the broken one).
In a D&D game, my main reason for playing (and I totally recognize that different people gravitate towards it for different reasons; that's totally valid - this little rant is about what I'm looking for) is to engage with an interesting story and make decisions about my role in that story. Combat can be a part of that story, but it's a small part. For me, any systems in the game should serve engagement with the story or be removed.
In BG3, almost everything related to the inventory is superfluous - it's extra work for minimal payoff. Looting a thousand containers and bodies is boring. Carrying a thousand little bits of rope and silverware and bones and candles and other junk back to any merchant in the game to sell them is boring (not to mention, why would any of these merchants buy this junk from you?!). Looting every single weapon from every enemy you defeat is boring (and extremely unreasonable). Keeping track of basic consumables when there is no scarcity of those items is boring. Crafting systems where you're just collecting ingredients and following a recipe and there's no creativity involved is boring (I don't see this ever working well in a video game, but it's great on tabletop).
The combat part of a D&D game is primarily interesting because of how powerful and skilled your character is - it's not about all the junk you find along the way. I say we dramatically downsize the inventory: say you have whatever you're wearing/wielding and then you can carry 5 things. Might be a spare weapon. Might be a couple of potions. That weird puzzle box or some spooky tome that you found. That one +1 arrow that you found sitting on a shelf in a shop. Abstract away all of the mundane stuff and just focus on a few really precious things that you might find out in the world. That leaves a lot more room for your character to be awesome.
I play games ( mostly RPGs ) for story, exploration and combat.
Game mechanics should be there to support the story and create interesting decissions, not to add annoying and boring stuff because it is realistic.
Regarding ammo, things would be different in a setting where ranged weapons and magic are rare and powerful. To give an example with todays technology, when the streets are full with gangsters armed with knives it makes sense to spend lots of efford to get a gun and a few bullits.
I do not play survival games, so my best example is system shock 2. Limited inventory, few resources, degrading weapons and respawning enemies made the player feel vulnerable and paranoid which adds a lot to the atmoshere, unlike many other RPG or action games where the player is supposed to feel powerful.