I've always seen ranged vs melee in Civ to be about how your projectile moves. Shoot in an arc? Ranged. Shoot in a line? Melee. Apparently the really old games (maybe this changed in Civ3?) didn't really have this distinction.
[...] When an archer unit was being charged, something had gone seriously wrong because they were not supposed to be frontline units.
Not my exact expertise, but I think both of these are based on common misconceptions about how archers functioned on battlefield, at least, their "standard" use in pitched battles. Exceptions of course exist.
Firstly all ballistic trajectories are curved, for arrows and bullets likewise. The functional difference is whether the person aiming and shooting could see the target. For mortars and howitzers the answer is often no, but for archers, slingers, and muskets the answer is almost always yes. The nominal range of longbows with flight arrows might be very long, but the useful range of war arrows for most bows was <100m, with the ability to cause disabling injuries through any kind of armour / shield at a rate that actually mattered on the battlefield increasing rapidly as range reduced. Most casualties caused by archery were probably usually at around the 50m mark. The Total War image of a unit of archers standing behind a unit /cohort of legionaries and shooting over them is mostly fantasy - if only because units themselves are kind of a fantasy. Archers might be incorporated into a formation of spearman and shoot over the first few ranks Assyrian-style, but even that would not be completely blind. Such a hybrid unit is not in Civ however (outside of a few UUs like Immortals IIRC).
All that to say, large groups of archers avoided melee combat not by having melee infantry in front of them but by a combination of: having swords and being OK at melee fighting if need be; running away from heavier, specialised melee infantry; having fortifications or field works to prevent enemy charges (or break up the formations of such charges to weaken them such that archers could defeat them in melee); and adjacent melee specialised troops supporting them to dissuade the enemy from breaking their own formation by exploiting the weak point where the archers are.
Musketry was used in much the same way. Formations like Tercios or Dutch linear "maniples" didn't have muskets shooting over pikes, but sufficiently close to pikes to dissuade enemy cavalry from charging ahead into them. The level of drill and the amount of doctrine that went into these formations was greater than with medieval archers, but the fundamental idea of using direct firepower from the frontline is broadly the same. Earlier firearms, like arquebus in the later stages of the 100 Years War or the Franco-Italian wars, was used (IIRC) in loose skirmishing formations more like medieval archers. On the other hand bayonet armed line infantry of the 18th-19th century were, essentially, deployed and fought like they had 100m long spears (albeit in skinnier formations). The skirmishing role that peltasts, archers, and arquebus had once fulfilled was replaced by light infantry fighting in looser formation deployed in front of between the main lines. All that was brushing over a *LOT* of detail, regional variation, and temporal evolution of course.
Fundamentally, it's not the ranged attack of archers in Civ5/6 that departs from history, but their ability to shoot over friendly units. Although the turn based nature of Civ and the way distance and length scale combine in an inconsistent way makes it foolish to even attempt to compare it to history.
Edit:
interesting blog by a real expert on the (un)importance of range