Just an FYI, the 100% bonus depreciation expired at the end of 2022 and is now phasing out at 20% per year, with the benefit fully sunsets after the end of 2026 calendar year. 

All,It is my understanding that I can not use a 10% second home loan and put it in an LLC to reap the tax benefits if I STR the property most of the time.

If they choose to live in a smaller unit and rent out their 3-bedroom apartment instead, the househacker’s tenant will be paying about 45% of their mortgage payment.While this still leaves househackers short of cash flowing during the initial years, there are significant benefits to consider.

Rel estate investing is a long term game.

An additional benefit of a detailed engineering-based Cost Segregation Study is that it can increase potential insurance premium savings as well as provide support for the property tax appeals process.

I love the area however, I have done a lot of research and am having a hard time making the numbers work out in terms on COC return.

Groups like "Short Term Rental Long Term Wealth" are awesome.

Is it worth holding the property for long term appreciation ? 

You can do it yourself but are almost certainly missing out on tax benefits.

I am lost on which avenue to take from the options of house hacking, buy and hold (short-term rental), buy and hold (mid-term rental), buy and hold (long-term rental), BRRRR, flipping, etc.