How many more moonrocks do we really need? And can't an unmanned craft bring back many more rocks than any manned mission, and much more cheaply?
Pretty much any additional rock would have scientific value, which was your question. Rocks from interesting unsampled locations, like the far side or the south pole have much more scientific value. Potentially we could return them unmanned, but we're still not really at the point where a human with a hammer can be entirely replaced by a robot.
Anything the moon has the Earth has in much greater abundance.
Yes, but a lot of it is inaccessible. We can really only scratch the surface of the Earth and there are often lots of problems doing even that. But the real advantage is, as you put it yourself:
The sheer energy cost of moving anything productive to the moon makes producing all of these things on Earth much more cost appealing.
That's why, if you want to do anything in space, you want to get stuff from space, the moon being the easiest place. Do we want to do stuff in space? Yes we do, certainly for scientific reasons. We are also becoming more and more interested in doing it for industrial reasons. There are lots of processes that might work better in low or zero gravity, some of which have been proven.
I'm not opposed to science, and I'm not opposed to space exploration...but we have not had leaders who have clearly set forth a long-term vision for why we should do this
Our exploitation of space has been scientific, which has had quite good returns, military, the value of which depends on how you value military capability, and prestige, which also depends on your values. The last one gets all the attention and it really shouldn't. The first one is often publicly compromised by the second; the shuttle program, for example.
And the investment is so massive and so cost inefficient,
It's not really. The entire Apollo program, corrected for inflation, cost about 1/3 of a ballroom (Apollo cost realized, ballroom current estimate) or maybe six months of bombing Iran (US cost, not counting Iranian or world cost). The Apollo program, despite being mostly a prestige mission, had lots of spinoff benefits, from electronics to kidney dialysis.