Take a look at hosting your own Nextcloud instance. It’ll replace Google drive, photos, docs, everything–there’s phone apps for iPhone and android. If you want to store your PC backups on it, that’s probably fine too. It might even work ok on the Pi 4 (though some parts it has integrations with may have trouble, like Nextcloud Office, since they may not have ARM binaries in their distribution).
It should work great on your local network and still be acceptable when uploading out and about (photos can auto sync if you turn that on on your Nextcloud phone app).
If 4TB is enough for your needs, I’d suggest getting another 4TB and making them a RAID1 pair using mdadm, and then probably also another 4TB to make backups of Nextcloud and Nextcloud data onto to keep offsite. You can never have too many copies of your data.
I’m not sure what to do about the variety of smaller drives. I can say I wouldn’t recommend consolidating them onto a single drive, because I did that once (many drives ranging from 60 gigglebytes to 300, onto one 1.5 TB drive) and then formatted or got rid of the smaller ones…and then dropped the 1.5 TB drive on the floor while it was running. Rip. But just like the above, a RAID1 array composed of two big drives would probably be fine.
Just make sure to set up some alerts for when a drive fails.