I really feel for you OP as our son was in some ways similar. He is very bright, high achiever through primary and into secondary until Y9 where he just lost interest in school. He wasn't very sociable though and not at all sporty. He used to just stay in his rim after school, usually online gaming.
He did tolerably well at GCSEs - 5 As an A*, 3 Bs, 2 Cs, 1D and so went into 6th form where he failed the first year very badly. The school let him restart Y12 with different subjects and that seemed to go well, he did really well in AS exams. However, things then spiralled down. He stopped going to school, didn't leave the house, his sleep pattern was all over the place. Since Y10 his behaviour/attitude towards us was bad but it got seriously appalling by Y13. He was depressed, angry, verbally abusive and physically aggressive (not to us directly, but breaking stuff, damaging walls, doors etc). It was hell. He also kept telling us he was going to leave home as soon as he could and quite frankly we felt that would be a good idea as his behaviour was having a serious negative impact on me, dh and ds2.
Like with your son, he was perfect with other people. Neighbours commented on how polite and chatty he always was - it was indeed like they were talking about another person.
In the end, he didn't take any A levels as he had completely disengaged with school and with us by that point. I was in despair as I couldn't see any sort of future for him.
He then got a job - and transformed almost overnight into a lovely young man. He blossomed. It was the absolute making of him. I couldn't believe the difference. That was 2 years ago. Since then, he has developed a social life and goes out regularly with friends. He works hard and 18 months ago got a job with a big organisation where he has a bit of responsibility. He earns £23k which isn't bad for a 20 year old (19 when he started there). There's also a career progression path so he will have other opportunities. He moved out of home a few weeks ago to live independently nearby and we really miss him - something I never thought we'd say 3 years ago!
For my ds, and maybe for yours, the school system for whatever reason, was not right.. Being bright is not the same as being academic. I can see that as ds2 is probably equally as bright but also has a real thirst for knowledge, and really enjoys learning. He is off to uni in September.
Another friend of mine had similar issues with her son who has now just turned 19. He was different to mine in that he was very sociable, out all the time, had a girlfriend from the age of 14, and is incredibly sporty, especially football, so very like your ds. He was also bright but not engaged with school. He dropped out half way through 6th form and them went and got an apprecticeship where he is doing brilliantly. He us working really hard and has totally grown up.
If I were you I'd concentrate on trying to support your son to get 5 good GCSES including English and Maths and agree to him.looking for an apprenticeship if and when he has achieved that. I'd pick your battles - my biggest regret is that ds1 and I spent effectively 3 years having non stop arguments over everything to do with school/ studying. I should have stepped back and let him fail at times but it's really hard to do so! Maybe you and your dh could sit down with your son and agree jointly what to do for Y11 with a view to doing an apprenticeship next year.
And my other bit of advice would be to try to make sure you and dh work as a team otherwise you will just resent each other. My dh has the patience of a saint (unlike me!) and I couldn't have got through those 3 years of hell with ds1 without him. It did put a big strain on us as a couple but we got through it together.
Good luck. 