Photos is not LIghtroom. If you have Lightroom I would wonder why you use Photos at all, as LR is a far more capable app than Photos - as it ought to be given that you pay for it. But more importantly, LR and Photos occupy the same place in your workflow - they are both media managers with non-destructive editing. Neither is aware of the other in the workflow, they don't communicate. Using both is like using two address books - which one is the most up-to-date? Which as the latest version of whose details? It's just a recipe for confusion.
when setting up Photo I asked Apple and they told me to untick the "copy items to Photos Library" box
This is called using a referenced library. This is perfectly normal in LR. Don't do this in Photos. Photos does not have the tools to manage a referenced library. If the path to the files changes at all - a folder or file renamed, a folder moved, whatever - you can find yourself reconnecting the files one by one. This is especially risky when the library is on one drive and the files on another volume. Really. No experienced user of Photos recommends this mode of working.
If you don't want to use the Cloud then simply sign out of the iCloud Photo Library, which you can do in the System Settings under your details heading at the top.
By default, in Photos when you import images to the Library they are stored in the Library package. (This is the opposite to referenced mode, and it's called managed mode. Photos manages the files for you.) This is opaque to the user. Photos is designed in such a way that you do all your interactions with the images via this app and this app alone. Again, a bit like managing your addresses and phone numbers: you search, edit, add and so on via the Contacts app, you don't go looking for the individual entries on the HD. Same with Photos. Everything you need to do with your images you do via the App, and everything you need to do you can be done via the app. So, for the Photos users, where the files live on the HD is not really a question. They're in the Library. Back up the library.
If you've been using LR this is a bit of a leap. But that's how it is designed to work, and given the inherent instability of Photos in referenced mode, I strongly urge you to go this way or use some other app. If you're worried about disk space then library can run perfectly well from an external disk formatted Mac OS X Extended, Journaled or apfs.
The 'Show In Finder' command only works with referenced files, not with managed ones. Why do you want to see the files? Directly accessing them for some other purpose is the kind of thing that corrupts library - and you find yourself dealing with the scenario described in my first paragraph above. Want to edit them with a different app? Then, like LR, Photos has an 'edit with' command which will send the image to another editor and so on.
If you want to access or recover those originals you use the File -> Export -> Export Unmodified Original command.
When I ask Apple how the Library works they tell me to just trust the system
You don't have to trust the system. There are alternatives. And the system doesn't work for everybody - and especially if you want to run referenced library, I would simply not trust the system.
But in summary: when you import photographs to Photos in default mode, the original files are copied into the library packed. Thereafter they are accessible via the app only. Within the library package they are stored, renamed and arranged in an opaque fashion for the end user. They can be got out again (via export/sharing etc) and everything you need to do can be done via the app.
Photos is optimised for the family snapper, probably shooting with a phone, who wants as much automation possible, so s/he can easily manage, tidy up and share the images. Other usage scenarios are possible of course, but that's what it works best for. I have yet to see a usage scenario where it makes sense to use Photos if you have Lightroom.