Setting `--libsubdir` without `$libname` leads to failure with auto-generated modules (Paths*)

**Describe the bug**
Setting `--libsubdir` without using `$libname` in combination with autogenerated Paths_* files leads to the generated files being copied to the same absolute path, therefore overwriting each other. The actual error doesn't occur until a dependent package wants to access the overwritten file.
Leaving it away entirely does not produce the bug, as the default is `$abi/$libname`.
More details in the top comment below.

**To Reproduce**
Example project is https://github.com/haskell/happy.
```
$ cd lib/
$ runhaskell Setup configure --libsubdir=""
$ runhaskell Setup build
$ runhaskell Setup copy --destdir="/tmp"
$ tree /tmp/usr/lib
$ cd ..
$ runhaskell Setup configure --libsubdir=""
$ runhaskell Setup build
```
For GHC 9.4+, the latter run fails at the build step, as `happy` cannot access the overwritten `Paths_happy_lib` file.
In earlier versions (GHC 9.2 and before), only a warning is emitted. This has been tightened to an error in GHC 9.4 and later.

**Expected behavior**
Cabal should warn about duplicate modules (the `Paths_happy_lib` module). Ideally, it wouldn't even produce two different modules, instead relying on the same Paths_* module.

**System information**
OS: Arch Linux
Kernel: Linux 6.12.7-arch1-1
GHC: 9.2.8, 9.4.8, 9.6.6, (9.8.2)
Cabal: 3.6, 3.8, 3.10
happy: 2.0.2, 2.1.3

**Additional context**
Related issues:
https://github.com/haskell/happy/issues/328
~~https://github.com/haskell/cabal/issues/8733
`hackage-cli` also uses `autogen-modules`.~~

Arch package: [build instructions](https://gitlab.archlinux.org/archlinux/packaging/packages/haskell-happy-lib/-/blob/25ce4245d2672b11e0d5db362b19de4d093667fa/PKGBUILD)

**Log (haskell-happy-lib with extended debug logs)**
[with `--libsubdir=\$compiler/site-local/\$pkgid`](https://github.com/user-attachments/files/18256512/haskell-happy-lib-2.0.2-1-x86_64-package.log)

[without --libsubdir](https://github.com/user-attachments/files/18256522/haskell-happy-lib-2.0.2-1-x86_64-package.log)