I think most people can agree that the destruction of Romulus was a terrible way to simply give a third-rate villain a motive. Romulus and its people's importance to Trek history, especially the TNG series, cannot be overstated. To Abrams, who knew nothing about Trek, Romulus was just a "Star Trek planet" that could be destroyed to motivate his villain. The Picard series is apparently going to deal with the impact Romulus' destruction has had on Picard's life. This is also pretty contrived: outside of the encounters he had with Romulans over the years, helping to stop Sela from stopping Spock's reunification efforts, and the events of Nemesis, what does Romulus' destruction really mean to Picard? He would be saddened, of course, but outside becoming the Federation ambassador to Romulus, I don't see the point in building on this ill-conceived plot point.
Of course, many have felt over the years that Picard might have very well left Starfleet to become an ambassador, and yes, we saw that in future timelines and the Countdown comics. On screen, however, Picard turned down romantic possibilities, promotions, and civilian research opportunities to stay in command. He took Kirk's advice and when we last saw him in Nemesis, he was looking forward to moving on with new members of the crew after the Enterprise was repaired and refit. If we learn that he eventually did leave the command chair, it seems very contrived to me. Like Kirk, command was his "first, best destiny." If all the horrible things and wonderful things he experienced over the years (assimilation by the Borg, cushy promotion offers, losing the Enterprise-D, a romance with a metamorph, life with a beautiful woman on a planet with a Fountain of Youth, the death of Data and departure of most of his crew) didn't get him out of that chair, I find it hard to believe anything else would.
At the end of the day, I fear we're looking at one of the problems I always had with Star Trek VI: humans in the 23rd and 24th centuries live far longer than they do now. Age 60-80 wouldn't mean what it does now. McCoy was still out and about well over a century old! If we get a series based on the notion that Picard was getting too old to command a ship and decided to become an ambassador or retire, there's a missed opportunity to explore a person's value as they age. Imagine if Picard is the longest-serving captain of not only a ship named Enterprise, but also any Starfleet vessel in its history? It would be intriguing to explore what that means for Picard and humans in the 24th/25th century in general. It's far more interesting than yet another "guy retires but wishes he hadn't (Kirk)" or "guy suffers tragedy and leaves everything behind (terrible development used for Luke Skywalker)" and it would offer lots of opportunities for worldbuilding the 25th century through Picard's POV and experience.
Of course, attitudes can always change over time. Leonard Nimoy resisted many attempts to woo him back to Spock until the Abrams movie gave him a role he found fascinating enough 
Stewart may have simply needed time to feel nostalgic about good ol' Jean Luc, and a character story interesting enough that he couldn't say no 
