I used to live across the street from a smaller NYCHA building. 5 stories, probably 20-40 units (new york city housing authority, pubic housing here).
It seemed perfectly fine. I never went in, but the outside was clean, well lit, and well maintained. My neighbors there were just like my neighbors anywhere else on the block.
There's certainly a lot of bad public housing in the us, but that's not an immutable quality of public housing, that's a policy choice made by politicians.
I'm sure it varies by region, but there many places along Appalachia where housing prices are basically the only buffer keeping fentanyl, meth, and guns at a distance. Yeah, it sucks because these are people in desperate circumstances exacerbated by distorted cost-of-living in the first place, but resolving the root cause doesn't magically undo the metastasis of crime and violence.
Edit: I'm not saying that there aren't smart ways to do this, and converting one single-family lot in SF to a duplex certainly doesn't destroy the neighborhood. I am saying that some people have seen things they don't want in their backyard, and some people haven't.
This is one of the worst aspects of American culture, IMO. Instead of tackling the issues of poverty, we just use the market to segregate the poor and their problems. It ends up creating a fractious, rancorous, and mutually distrustful society.