The primary challenge I face is that there are old entries in the DB like these from the states_meta table:
| metadata_id | entity_id |
|---|---|
| 1568 | sensor.0x5c0272fffe4987ee_battery |
| 1569 | sensor.0x5c0272fffe4987ee_action |
| 1570 | update.0x5c0272fffe4987ee |
| 1571 | sensor.remote_test_2_battery |
| 1572 | sensor.remote_test_2_action |
| 1573 | update.remote_test_2 |
I tried updating the metadata_id in states_meta as well as states but that caused more issues until the devices sensors seem to not report anything anymore. Only removing and repairing the test device brought it back to life.
So the question for me still is, how to sanitize multiple device names and / or the duplicated sensors?
PS: Tampering with the HA DB seems to have a greater chance of messing things up badly. Simply removing the device and having it rejoin the network, followed by a rename of the friendly name, does the trick.
Though, there is still the challenge of merging / harmonizing the long term statistics. This post, at least for Influx DB, seems a good appraoch: