Spain's last gasp effort at great power status was during the Pyrenees war of mid 1790's. After initial success, the military commander died (of illness, I believe) and things went south. At that point, Spain was merely a pawn of France. So, this might be a good POD (actually, a little earlier, to allow for military/financial reforms to allow Spain to have positive results in that war. Never mind if it's feasible, or how it gets done. The whole spirit of the thread is a bit implausible, so we just have to go with it. Perhaps Charles IV is born with a bit more mental acuity and Godoy never gains sway, giving way to a more able statesman who doesn't alienate the populace and can actually lead the country into a return to greatness.
If military things break differently in the mid-late 1790's, you could see butterflies of immense proportions. IF Napoleon, who took part in the War of the Pyrenees, is killed or disgraced, the entire world changes. IF he still takes over, but Spain is rendered a path to great power status, you still see some major butterflies. Spain, instead of becoming Nap's lapdog, becomes either an enemy, neutral (but still a potential foe) or a full fledged ally. Hard to see Spain looking to join in for European hegemony, and equally hard to see Nap looking for an equal partner in his conquests. Nap isn't going to leave a strong potential threat on his flank. IMO, invasion of spain comes earlier than OTL, and you may see greater success on the part of some of the early coalitions. French OTL success in Europe was aided immensely by not having to worry at all about Spain. Spain on the rise is not going to be a toady of Nap's continental plan.
If Spain escapes the Napoleonic years still a great power, they will have their entire colonial empire intact and will probably concentrate on developing/using it for further enrichment and all policies will be aimed toward such goals.
A French rise to power is a problem, as they are neighbors. A German or Russian rise to power is not, and Spanish response all depends on whether the rising power is threatening Spanish empire. British empire is a worry. One assumes that with great power status comes a reasonably strong navy, and British power OTL depended greatly on being the sole naval power. There's gonna be conflict.