Yes closed cell spray foam is the way to go no doubt. It allows you to get a higher R-value out of less amount of insulation taking up a wall cavity. This does provide a complete envelope to prevent air leakage into and out of a building. Which you are right, when foams first came out, caused huge issues with air tightness creating condensation and mold growth. In fact I've heard horror stories of brand new houses being demolished for that reason. In the residential space, most houses now are built with foam in the walls and cellulose or other breathable insulation in the attic space with a ridge vent on the roof. But if you do foam a whole house or commercial building most codes nowadays require you to have an a air handler unit or a similar HRV(Heat Recovery Ventilator). This takes the moisture out of the heat, recirculates into the building envelope, and brings fresh air in. On the topic of the fire ratings, yes the foams we use contain a fire retardant. I've seen it lit on fire and it does not combust. But I have no idea what the health implications may be during the case of a fire. I guess my main concern with my building burning to the ground would be making sure people are alive much less the health implications during the event. I would be very careful what advice you take as far as "architectural". I've found so many local codes lately that just seem to be ridiculous. 4 layers of drywall, 2 layers of exterior sheathing, just overall unimaginable requirements for codes to require. And I would have to agree with @DavidFaulkner you've gotta look at it from an investor POV above all others. I can't imagine putting all of these things into this property would make it anywhere close to profitable.
