Philips Hue operates over Zigbee so there isn't vendor lock in. Like you, I've also had exposure to the high end industrial LED emitters, but using those at home is hard. I ended up going with Ikea TRADFRI bulbs, they are much cheaper than Hue (USD 9-13 each) and don't seem to be any worse than the Hue bulbs. Ikea also sells Tradfri buttons (switches to control the bulbs) and motion sensors. And not relevant for this, but also Tradfri controllable power outlets.
I'm using a Sonoff Zigbee 3.0 dongle (available on ebay for $20, uses a TI Zigbee chip) plugged into a small single board computer server. Zigbee2MQTT is FOSS software which interfaces with the Zigbee dongle and manages the Zigbee network. You can pair devices to the network with with its web GUI. Zigbee2MQTT allows you to control the devices over MQTT, a lightweight messaging protocol. I wrote a Python script for this. It connects to MQTT and listens for messages from the buttons (on, off, brightness up/down, CCT up/down). When it gets a message from a button or motion sensor, the script sends a MQTT command to the relevant bulb / group of bulbs.
My setup is all Ikea Tradfri, but I could very easily add in Hue bulbs or other Zigbee devices such as ones from Aqara or Sonoff. I just purchased a Zigbee thermostat but haven't gotten around to setting it up yet. The whole thing works without internet access. Though you could expose the MQTT server to the internet and set up a method to control your devices when away from home.
The main risk is that the industry moves past Zigbee. It's already moving to Thread & Matter, but I've only done a bit of research into that. I think Zigbee will be supported and products will continue to be available for a while. Or a self-hosted Thread software might also end up existing.
The tradfri bulbs don't dim to 0.1% but they get pretty dim. And your control software could do things like selectively turn off certain bulbs in a room as you command it to lower brightnesses. The CCT range is good enough for me. The Tradfri bulbs are specified as >90 CRI. They don't flicker.