@yellowelli this is what the doghouse is for.
Not only a lost night’s sleep for you, the embarrassment of having the coppers tip him out bedraggled on your doorstep, & you are well aware of the risk of his walking down that road barely capable of standing let alone looking after himself. I’d be fucking fuming too.
What next? Well you need some rest & respite so either you go out for the day or you go back to bed to rest. Him, he is now on Daddy duty all day. Wake him at usual time & get the kids in there.
Later today, once he is coherent & the kids are in bed, you discuss what happened. No so much what he did/did not do, because he is unlikely to actually remember much - what happened to him & to you.
Likely he just drank that bit more than he could handle & then some more or his drink was spiked (as in spirits thrown into a pint).
Yes, he did try to get home in the pouring rain along a dangerous stretch of road (why else would the police pick up an adult man unless he was clearly at risk?).
What you need to make clear to him is what the police told you, & how distressed & worried you were, never mind you being pregnant with small kids. Another point to be made is that his friends let him go off completely pissed into the pouring rain at night on his own.
Without being melodramatic, we know that people wending their way home in poor weather have died, partly because alcohol affects how the body regulates temperature. Again, the police deemed him to be at risk.
Once the adult responsibility discussion is done, then you have an eternal open mic to ruthlessly take the piss. ‘Oh remember when the police brought you home so pissed you could not stand…’
Being serious, I expect that once he wakes & is brought up to speed, he’ll be mortified, very apologetic & not be so keen to go on the lash with said mates again.