As long as you buy natural latex (Dunlop is my choice), then to me it IS a no-brainer over “foam”, and no way would it be “crappy”. I think all “good” latex is better than any foam. Blended Talalay would also be better than foam, to me, unless you’re talking high density foam only in the support layer. Just bear in mine that not everyone who says they’re selling a latex mattress really are - some say that but contain very little latex. You just need to know what’s in the mattress, and I’d think you’d want the latex in a zippered mattress cover. If you learn a few simple things, you’re quite unlikely to end up with crappy latex.
I recently bought a queen Dunlop 9" mattress - a 6" core with a 3" comfort layer, altho I paid outside your $500 range. I don’t know that you can find a real latex mattress in that price range. I had to have a doctor’s prescription to buy my mattress since it has no fire retardant in it - which also made the mattress tax free and tax deductible.
Best deal I can find online is the monthly special at https://www.sleepez.com/latex-mattress-sale.htm - you’d pay about twice what you’d like, but you’d end up with a mattress that would last years.
My father slept on a Dunlop mattress he bought from Sears back in the '60s or '70s for 30-40 years. When he died, the bed was still comfortable. Bonus to that was that he never spent another penny on a mattress after he purchased that one.
You don’t have to drive yourself bonkers learning too much here - I read for a couple of days, found a reputable seller an hour away, went and tried out the mattress. Thought about it for a few days, went back and tried it again, still loved it, bought it and it was delivered free of charge within 48 hours. Start to finish, my mattress buying experience lasted less than a couple of weeks and involved two trips to one mattress store. I’m a happy camper with my new bed, and since I’m already 60 years old, I plan on this being the last mattress I’ll ever need to buy.
You can do this the hard way and buy a crummy mattress every couple of years, and throw good money after bad repeatedly while enduring periodic episodes of pain/discomfort whenever a cheap mattress starts to fall apart (and then you start the process of looking for a mattress all over again), or you can buy a better mattress to start with and avoid some of that stuff. Of course, a lot depends on what you’re able to pay for a mattress - but one of these days hopefully you’ll be able and willing to buy something that will help you sleep better and more pain free for years at a stretch.
You’re not getting the answer you’re demanding here because there is no one perfect answer, no magic “link” that will take you to just what you want at a price you want to pay. All any of us can do is throw out alternatives until you see one you find acceptable.