Most fun and profitable stay at home career?

I think it depends on how hard you work at it. I have done gardening, writing, painting and my sims earned quite nicely. Writing career is nice as you get royalties for all the books you write, and you can take some days off without worrying about income.

I find painting to be very lucrative, especially if you have Nifty Knitting and can use Plopsy to sell the paintings. I had one Sim sell a painting for over 14K Simoleons which basically paid for her entire college education, no loans required. :) Combine it with the Painter Extraordinaire aspiration; with that aspiration's reward trait you can paint any of the emotional paintings regardless of mood, and also write all the emotional books (Romance, Workout Guide, Sad Book, etc.) and carve the emotional wood sculptures.

Most of my Sims are artists and writers. They never have to leave the house to work.

I think painting is more fun than writing, because you see more variety. I do get a kick out of naming books though like making a silly series with my writers.
Do you plan on actually having the job to go with it or just living off of what your sim can do to make money from home?

I always have at least one Sim do the gardening career. I have them work from home when the phone rings. I have one of every base game plant plus two from Jungle Adventures, all Island Living and all Seasons plants. My Sims are getting 8 to over 12k per harvest. Best quality is nice so far. I have duplicates of two plants right now because I am grafting to try and get a cow berry. It's the last seed I need to complete the gardening collection for this family. Just pleased I got everything to work well in this save. I use all season plant mod so the season doesn't mess with the garden.