The whole point of the peer-review process is to avoid that any sub-standard garbage gets published. That is the reason your "reputable journal" is reputable in the first place.
Here, the review process pointed at a major issue in your work. That's great because that's why your paper gets reviewed in the first place. (I would even argue that whether or not the reviewer actually realized the gravity of the implication of their review is secondary.)
Now, you're currently in the following situation:
- Presumably, you have already invested a lot of work to get to the current point in the research for this paper.
- To conclude this part of your research, you've now realized you need to spend a whole lot of additional work.
Naturally, the question then arises: are you either (1) willing to spend the additional work or (2) completely scratch all the work you've already done?
The option that's missing here is: (3) pretend nothing happened and publish the current paper after addressing the outlined revisions. Clearly, you do not intend to go for that, so the decision is between (1) and (2). In both cases, you will have to tell your co-authors and the journal that the paper will need more additional work than anticipated.
But I don't quite see the problem with that. The journal did their job, so if you honestly inform them about what happened, I can't imagine that it will lead to any bad consequences for you. It is in their interest too, after all, that only high-quality results get published. So, why not thank the reviewers and inform the journal that you will (1) withdraw the paper completely, or (2) resubmit at some point in the future but probably not within the specified time frame for the revisions they asked for?
Also: I would probably discuss your newly gained insights with my co-authors as soon as possible. Not only so they are informed but also because maybe there's a different angle that you're missing and that would put the whole situation into a different light again? One of your co-authors might have some relevant insights that might change how you will move ahead.