First off, Shadow Priest is fine if you have enough Warlocks in late game content. In leveling content Shadow Priest is more than fine. Get Spirit Tap, Get Imp Wands, then do whatever you want tbh until you are level 40 for Shadow Form.
In groups, make sure you take the threat talents for the class. You will consistently pull threat off the tank and decrease anyones want to bring Shadow Priests along otherwise. Dont be afraid to bubble or dispell people as both of these can be done in Shadow Form, and if needed, drop form and heal.
End game expect to be sub par DPS. You wont be as bad as many people assume, but you wont be top tier either. I actually do between 4-500 DPS consistently on my Shadow Priest unless its against mobs with high resistances, but all casters suffer with these generally especially in BWL. Many times ill see Mages or Warlocks between 300-400 DPS in some cases. I chalk this up to Shadow Priests having 10% hit baseline and 40% Shadow scaling as a baseline. You get a lot out of Shadow Damage.
Your classes issue is mana, not that it scales terribly. Be prepared to farm pots and dark runes. This becomes incredibly annoying as the game goes on especially when it feels like it isnt needed because the content flops over so quick, but you will be expected to push your hardest anyways because most guilds are about speed clears at this point.
Overall, I found it to be a bit boring over time. Between your DoT being knocked off constantly in raids causing a mass loss of DPS and mana, between having no gear to roll on beyond the rarist BIS pieces and even those going to Mages and Warlocks before you most of the time. Between having to farm for hours to get buffs for braindead content. Between having to collect world buffs to then afk for most of your sub time and the content just never pushing back it becomes a bit mind numbing over time. Like you are doing things just to do them with no real payoff or purpose. Hell, even in FF14, a game considered casual by many, basic leveling dungeons require far more attention and group coordination than any end game content.
For all of the above ive quit Classic despite being one of the best geared and highest parsing Priests on my server. It simply wasn’t fun anymore. However, if you are dedicated I wish you luck. You may regret it as you get higher, but that is for you to do decide.