What exactly is the point of a maritime patrol aircraft that can’t engage surface warships (at least not effectively)?

I understand hunting subs, but aside from that is maybe ISR the only other mission the CP-140 is used for? Seems like a waste of a great airframe to me….

I could not agree more. The Aurora was frankly an odd beast in a number of ways, most interestingly combining the P-3 airframe and the avionics of the S-3 Viking. Mark 46’s were all it ever carried from what I can gather. To your point about ISR, the Aurora’s are being upgraded with limites ISR capabilities as part of an on again/off again upgrade program.