There’s a lot to address here as you’re talking about hardware and possibly multiple levels of software.
Yes, you can do this with raspberry pi or any SBC or mini PC. Even an old desktop PC if space isn’t an issue.
In terms of photo management software, I really like Photoprism. Immich seems to be popular as well.
In order to get your photos synced to multiple computers over the internet (a good idea for resilience), you could look at syncthing. Alternatively, you could have one central server and one or two backups in different locations using borg backup or similar. In my experience, backups are easier to manage and make it easier to recover from data loss than replicating the current state of your data in multiple places. You can do both, though.
It’s a very worthwhile project, but may be pretty difficult unless you are already comfortable with server technology or are enthusiastic about learning.
Like many GUIs it makes it so you don’t have to remember and type a bunch of commands to carry out basic tasks. I especially find it convenient for checking logs. But no unique functionality compared to CLI. So it’s a matter of preference.