The music industry insider wrote the Top 5 hits “Eve of Destruction” and “Secret Agent Man,” and was the subject of a song penned by a legendary tunesmith