The future Hall of Famer is heading to the Giants after an injury-compromised 2024 season