From what I can tell, the Borg are a singular collective consciousness spread across multiple bodies: they're defined by their lack of individuality and central consciousness. So then why is there a Borg Queen? Shouldn't a Borg Queen basically be just a propped-up Borg drone?
The reason there was/is a Borg Queen is because in any system there is a regulator/controller. Any program that would make up the Borg would ultimately create several regulating "drones" which is what "Queens" are.
There is this idea of how the Borg were all just a mass of randomly acting drones, but this was never the case. It was always the case that there was a single mind behind the drones and it is best understood that the this hierarchy is natural to both programming and organisms and such would be how the Borg organize themselves as evidenced by designations and such. The main difference between biology and The Borg is that The Borg can move from 1 group to another when needed.
For a little clarification. You have drones and they are linked together to a task or whatever. This group can be considered like organs to the ship. The ships can be seen as individuals which are subject to the queens, like the drones are to the groups and the groups to the ships.
Given that it has been said there are a few Queens in some places or at least shown we can speculate that there is a higher controller/regulator or that the Queen is as she said she is, "The Borg" she is in fact the one voice that is the sum total of all the minds of The Borg in one drone and she only existed in a singular drone for the convenience of working with humans and is not a static being as we are lead to believe.
These easiest way to think of this is that the Queen is the resulting sentience of The Borg in total much like we humans are the resulting sentience of all the cells in our body. We are as much a collective as the queen is. Similarly, our brain is our body's regulator and decision maker for all the cells in our body. The queen is just the brain of The Borg body speaking through 1 drone like our head is the body part that we speak out of.
In-universe, the Borg Queen is something akin to the queen of a bee hive, a kind of figurehead or control point.
Out-of-universe the Borg were originally conceived as you described - a distributed collective intelligence with no hierarchy and this is how they were portrayed in their first few appearances in ST:TNG. However, when creating the story for the First Contact movie the writers decided to create the Borg Queen for dramatic purposes as they needed an antagonist character which couldn't really be fulfilled by the deliberately characterless Borg drones. This arguably introduces a paradox, because in the "Best of Both Worlds" episodes of TNG the Borg turned Picard into "Locutus of Borg" as they needed an intermediary to speak for them, which would not have been necessary had the Borg Queen existed.
Why the Borg Queen is not only for dramatisation.
If the Borg were only the nanites they spread, the hive controlling mechanism could be only in their nanotechnology itself and there would be no need for a separate organic controller (Queen) for a Borg colony/ship. The hive consciousness could be stored only in the transwarp network of nano nodes, and use all the drones as just organised tools for various purposes.
But the Borg fundamentally need organic hosts, being a combination of both organic tissue and electric/mechanical nanotechnology. So there is something important in the organic brain/mind for them, otherwise they would probably be fully mechanical/nano, and a lot more durable than the organic tissue they still mostly are.
So if all Borg units are fundamentally partly organic, the controlling units would also be partly organic, and thus there are queens. Why the queen appeared only later in the series and films could be that the Borg first perceived the federation species just another resource and later found them a real thread, maybe even equal rival - so they needed some diplomacy and introduced first a Locutus and then a Queen to federation.
Maybe the Borg technology is not itself conscious but only a tool to synchronise / combine many organic brains into one, creating a new kind of semi-organic life-form that organises it's units like a larger body just like a bee queen controls it's hive with chemical scents. Mystery is how the Queen is chosen from those assimilated, maybe always a strong-willed female as males are typically worse in multi-tasking. Maybe the Borg Queens are even actually the first Borg species or what's left of them.
In First Contact, the Queen was extremely fascina ted of Data, as data was something very different than Borg; a completely artificial electro-mechanical mind, as the Borg are still organic minds, but very different than individualistic organic minds, because of technology.