As to what happens to him afterwards, well I'll go with the books.. he breaks up amicably with 7 and he and Janeway hook up. Hopefully now that no one is dead any more they will drop the Chakotay nervous breakdown storyline and everything will work out.. but I think she will always be the alpha partner and he will always be in a carer role who relishes being in orbit. And maybe in the 24th century that's no longer a subject for mockery.
Actually from the female view point right now in romantic literature, the beta male (think accountant, wears glasses, isn't physical) has a lot of fans. I know that there are a lot of fan fic authors that write Chakotay as the "alpha" male type, but I find nothing wrong with the "beta" Chakotay we saw most often on Voyager.