Here is my guess what is going on:
Old Wiring
Not super old like my house. (OK, that's not "super old", just the 1950s. Super old is knob & tube). Just old enough to not have required a neutral in the switch box. Neutrals in the switch box are trivial if the wiring is panel->switch->fixture. But they require an extra wire - and builders don't like to spend on an extra wire if they don't have to - if you are using switch loops.
Two Switches
Why two cables? Because there were two switches in the box at one time. One of them was removed and just wired "on" by connecting black (switched hot) & white (hot). That was probably not the right thing to do (I don't know if it is code compliant or not), but it was the easy thing to do as it makes the switched item "always on". The alternative is to disconnect the switch loop and change the wires around at the fixture so that it is always on.
Switch Loops
A switch loop is a set of wires - commonly 2, but 3 if you include neutral - that extends from a fixture to a switch. The full wiring is:
- Neutral (white) from panel -> fixture neutral
- Hot (typically black) from panel -> hot wire to switch (white in your case)
- Switched hot (typically black) from switch -> fixture hot
With typical 2-wire cables, the only colors are black & white. So you end up with black & white connected together in a couple of places, including at/through the switch.
Normally, white means neutral. But since you don't have a neutral in a normal switch loop, you get to use white as hot. It should be marked but often isn't because it is "obvious". But with the new neutral requirement, which was created for things like your new smart switch, you need a 3-wire cable, so white becomes neutral again (capped if not used), black becomes hot and red becomes switched hot.
Since you have the box open, you should go ahead and mark the white wire going to the switch with black tape. That will make it clear to someone else in the future that it is actually a hot wire.
Sorry, Neutral Not Available
You have two choices:
- Find a smart switch that doesn't require neutral
- Replace the existing 2-wire cable with a 3-wire cable