Chrome already has a setting to change a specific website's color-scheme, although it's only working when Developer Tools is visible (then with Cmd+Shift+P "Toggle CSS media feature prefers-color-scheme"), so CSS implementation actually follows respecting UA-level preference over OS-level setting.
But again, Chrome doesn't have a feature that lets users to choose which color-scheme they want to browse the web (so there is no bug, but it's a UX issue because their Appearance settings doesn't make it clear to the users what this setting changes) other than the setting they gave to developers. Honestly I would love to see all browsers have an option for how color-scheme settings should apply to websites.
However, your original comment was saying that prefers-color-scheme is bugged in Chrome is not true. It works as intended: it follows both UA-level and OS-level settings correctly.
The settings in Appearance tab is not a setting for preference when browsing the web. Many apps have a setting to choose if their app should change appearance when OS setting changed (or to override it), they could simply leave out this setting and let Chrome chrome (app ui) always follow the OS setting or always be in default light mode, instead they gave users to choose "how their Chrome browser will look like", not "how every website they visit should look like".