Welcome. My name is XXXXX XXXXX would be glad to assist
I have read the information and postings so fairly familiar with what you have going on at the moment.
Your cable for the water heater sounds like an older SEU cable installation as used in the 70's-80's or even earlier.
It has 2 wires black and the ground is just a group of wires that actually wrap around the other 2 when the cable is unstripped.
That is aluminum and probably #8 awg.
If you can read the outside of the cable insulation it will tell what it is.
#8 should be on a 30 amp breaker and not the 40.
Needless to say, that doesn't trip one so lets look at what the problem may be.
If it sparked at the ground screw, that is a bad sign that wires are shorted somewhere.
So, please do this and post back the results.
Remove the entire cable from the water heater wiring compartment.
The separate the 2 blacks and the ground from each other.
The turn on the breaker and see if everything is fine while it is disconnected.
Do not just unwire the heater, remove the cable from the top where it is connected to the heater going into the wiring section.
Will await the info.