That would be a welcome addition, and I agree that following the system setting is the most natural way to do it.
At the moment I also switch manually, which is easy to forget in the evening, and then a light theme feels too bright. A "Follow system" option in Settings > Appearance would solve that without adding another daily task.
It would work even better if Opera let us choose which theme to use for light mode and which for dark mode, rather than forcing one theme to do both jobs. Some themes look good in one mode and not the other, so pairing a lighter theme with a darker one would keep the switch looking right.
A simple schedule as a fallback would also help people whose system does not change automatically, but following the system should be the first step.