"Why do you always drink water?"
Translation:Proč vždy piješ vodu?
It's a bit strange; why do you drink water always and not just sometimes? It would work well if the sentence continued: "Proč piješ vodu vždy, když jíš párek?" (Why do you always drink water when you eat a sausage?) Otherwise, the person asking this question is more likely to be interested in knowing why it's water that you drink always, not why it's always that you drink water. So.. I would say, marginally acceptable :) I can't say it's incorrect, it's just hard to imagine actually saying it.
In "On tady spí zřídka" the "rarely" is the most important piece of information you're telling someone.
As for general placements of adverbs... they like being second in the sentence when they're not too important, they want to be first if that's the topic (as for here..., regarding today...), or last when they're stressed and they're the focus of what you're telling someone. So generally the same rules as for everything else.
No worries, I get the same brain melts when I try to wrap my head around the word order in Hungarian. It's also flexible, but works very differently than in Czech. Couple that with their 30-ish cases, two whole separate systems of conjugation, vowel harmony, and the brain just wants to crawl out of the ears sometimes :D