I do think the situation in some areas is really hard- I've recently got a teaching job in Cornwall (which is where I grew up), and finding somewhere to rent has been difficult- it's not even really about the affordability of properties, it's about the availability. In the area I'm looking, a lot of properties are holiday lets or student lets, so finding a one bed flat to rent is pretty tough. I think there's an argument to be made that a certain percentage of housing in an area should be reserved for essential workers to ensure that communities have access to proper services.
Holiday homes/second homes that stand empty for 90% of the year are definitely morally wrong and add nothing to a community- there should definitely be some kind of restriction on these.
Holiday lets with a high level of occupancy are a bit different- but I do think the "tourism" issue is complicated. A lot of the jobs created by tourism are low paid and seasonal- people don't have reliable work year round, and therefore may struggle to find somewhere to live themselves- yet presumably the people who come on holiday want someone providing these services for them?
People who come on holiday usually want shops and restaurants to visit, and maybe activities to do as well- but if there isn't enough of a local market to sustain these businesses over the winter, they can go out of business. This can, over time, make the destination less attractive for holiday makers, and can become a sort of negative cycle. So really, it benefits everyone to have healthy local communities who can support local businesses year round as well. I've previously worked for businesses that rely or partly rely on tourism, and I've seen how difficult it can be to make things work (and no, the answer isn't more tourism, because the business is usually working to full capacity in the summer).
It's all very well to say people should move elsewhere, but if everyone did that you'd have areas without essential services, and without anyone to do the low paid work that the tourism industry relies on.
A lot of people do "just leave"- of my school friends, I'd say over 75% no longer live in the county, a lot of them left for uni and never came back- even though I know a lot of them would love to! But equally, being able to move away in the first place requires a certain amount of money to get yourself started, and having to live away from your support network can be pretty tough. Also, in the longer- medium term, this will have consequences.
I think the answer is maybe to have some kind of balance- so maybe a percentage cap on the number of homes that can be used as second homes/investment properties/ holiday lets in an area, to ensure that there's housing available and community services are able to survive.
FWIW, I don't think just building is the answer either. For a long time, in my home town, developers have been trying to build. They haven't been blocked from doing this as such, but the council have said that existing services (e.g. schools, doctor's surgery) can't cope, and any development would also have to include some provision for these services- and then the developers pull out. In one case, all the developers were asked to do was to fund some improvements to the existing road network to ensure that access wasn't a problem, but they weren't even willing to do that!