Medical science is an incredible thing, but science, by definition, does not have all the answers.
A small part of me is actually somewhat surprised, that people (both lay, and professional) are surprised.
Though I am not the best placed commentator to champion this view.
I don't find that surprising. You have to do the early phases of drug development in model systems like cell cultures for practical reasons: It makes evidence obtained more reproducible and has less ethical concerns than experimenting on humans immediately. It's like how software components usually get implemented and tested in simplified environments (e.g. unit tests use doubles), and just like with drugs, sometimes we find that software components interact with each other in unexpected ways when combined into a whole.
For the initial approval, we have this very formal process, the FDA wants this kind of data from trials of this size to show this statistical level, etc. All very scientific-looking.
But once it's on the market, random doctors try it out for other conditions. If it seems to work they tell their buddies, tweak the mixtures, share anecdotes. If it becomes widespread, some industry club records this for the purpose of arguing with insurance companies. I am told this off-label use now constitutes the majority of drug prescriptions. All based on (as far as I can tell) no statistically driven testing at all, just stories.
(To be clear, I'm not at all surprised that drugs have "off-target" effects, and may be very useful for things their inventor never imagines. I'm just a bit shocked how pre-modern our system for collecting this knowledge appears to be. But not an expert, and would love to know more.)
If anyone is interested, the history of anabolic steroids is almost comical... https://www.drugabuse.gov/publications/research-reports/ster...
And the period of time over which the change took place, is actually quite remarkable.
I'm not an expert, but I imagine any more advanced system would be extremely problematic from a data protection point of view (think HIPAA etc.). In a study, patients sign multiple pages of legalese to allow the researchers to collect all the required data. You don't have that with regular patients in a GP's office.
More generally, how do we know it isn't mostly garbage? That seems to be the consensus about most pre-20th-C medicine, and those guys weren't idiots, they were just trying things out and sharing ideas...