Counter tables for build orders and stats

build_order_wins_by_champion_role.ex

# Version 1 - Counter table
# Ideal for when we update the games/wins columns a lot more often than we read them. 
# Writes on counter fields is more performant than writing on integer fields since it does not do a 
# read-before-write. The drawback is that counter columns can not be used as part of the primary key
# so rows inside a partition can not be order by `games` and `wins` columns.
# Also, we can not use TTLs on counter tables. We will need to delete them when a new patch comes out.

table :build_order_wins_by_champion_role, keyspace: LeagueStats.LeagueStatsScyllaKeyspace do
  field :patch, :text, validators: [presence: true]
  field :region, :text, validators: [presence: true]
  field :queue, :text, validators: [presence: true]
  field :champion_id, :int, validators: [presence: true]
  field :tier, :text
  field :role, :text
  field :build_type, :text  # (items, spells, etc…)
  field :build_order, :text
  field :opponent_champion_id, :int, validators: [presence: true] # when no opponent we use 0 and query using 0 when populating non-matchups
  field :games, :counter
  field :wins, :counter
  partition_key([:patch, :region, :queue, :tier, :champion_id, :role, :opponent_champion_id])
  cluster_columns([:build_type, :build_order])
end

champion_role_stat_totals.ex

table :champion_role_stat_totals, keyspace: LeagueStats.LeagueStatsScyllaKeyspace do
  field :patch, :text, validators: [presence: true]
  field :region, :text, validators: [presence: true]
  field :queue, :text, validators: [presence: true]
  field :champion_id, :int, validators: [presence: true]
  field :tier, :text
  field :role, :text
  
  field :games, :counter
  field :wins, :counter
  ... (all other stat fields will be counter columns)
  
  partition_key([:patch, :region, :queue, :tier, :champion_id])
  cluster_columns([:role]) # just in case we want to compare stats between champion's roles
end

table :matchup_champion_role_stat_totals, keyspace: LeagueStats.LeagueStatsScyllaKeyspace do
  field :patch, :text, validators: [presence: true]
  field :region, :text, validators: [presence: true]
  field :queue, :text, validators: [presence: true]
  field :champion_id, :int, validators: [presence: true]
  field :tier, :text
  field :role, :text
  field :opponent_champion_id, :int # if no opponent then should it even be stored in this table?
  
  field :games, :counter
  field :wins, :counter
  ... (all other stat fields will be counter columns)
  
  partition_key([:patch, :region, :queue, :tier, :champion_id, :role])
  cluster_columns([:opponent_champion_id])
end


table :duo_champion_role_stat_totals, keyspace: LeagueStats.LeagueStatsScyllaKeyspace do
  field :patch, :text, validators: [presence: true]
  field :region, :text, validators: [presence: true]
  field :queue, :text, validators: [presence: true]
  field :champion_id, :int, validators: [presence: true]
  field :tier, :text
  field :role, :text
  field :duo_champion_id, :int
  field :duo_role, :text      
  
  field :games, :counter
  field :wins, :counter
  ... (all other stat fields will be counter columns)
  
  partition_key([:patch, :region, :queue, :tier, :champion_id, :role])
  cluster_columns([:duo_champion_id, :duo_role])
end