# Who are the "deaf" DHCP clients, and what do they have in common?
# Read-only: queries dhcp_messages and connected_clients, changes nothing.
#
# A deaf client sent DISCOVER, the rXg sent an OFFER, and no REQUEST followed
# within a minute -- the offer never arrived, or the client ignored it. This
# uses the same definition as the dashboard's Deaf Clients gauges, then puts
# each MAC next to where it is connected now, so the cross-tabs show whether
# the deaf share one AP model, VLAN, signal band or start time.
#
# USAGE -- on the rXg, as root (csh-safe; settings are trailing arguments):
#
#   curl -o /tmp/sdan-deaf-check.rb https://pre-alpha.henryhaller.com/sdan-deaf-check.rb
#   cd /space/rxg/console
#   bin/rails runner /tmp/sdan-deaf-check.rb
#   bin/rails runner /tmp/sdan-deaf-check.rb MINUTES=180 MIN_IGNORED=1
#
#   MINUTES=60      window (default 60)
#   MIN_IGNORED=1   ignored offers needed to count as deaf (default 1; the
#                   dashboard gauges use > 20)
#   LIST=40         deaf MACs listed individually (default 40)
ARGV.each do |arg|
  key, value = arg.split('=', 2)
  next if value.nil? || !key.match?(/\A[A-Z][A-Z0-9_]*\z/)
  ENV[key] ||= value
end

MINUTES     = (ENV['MINUTES'] || 60).to_i
MIN_IGNORED = (ENV['MIN_IGNORED'] || 1).to_i
LIST        = (ENV['LIST'] || 40).to_i
OUT         = "/tmp/sdan-deaf-check-#{Time.current.strftime('%Y%m%d-%H%M%S')}.tsv"
DASHBOARD_HAVING = 20
PLAIN_VLAN_UNITS = 4096 # vlan0-vlan4095 are tag == unit; QinQ units start here

# The VLAN a DHCP message arrived on, from dhcpd's "via vlan3439". vlans.tag
# cannot stand in for it: one auto-increment Vlan record carries every
# per-unit tag under a single base tag. A relay IP, physical port or QinQ unit
# names no comparable tag, so it is shown as logged.
def via_label(via)
  unit = via.to_s[/\Avlan(\d+)\z/, 1]&.to_i
  unit && unit < PLAIN_VLAN_UNITS ? unit : via
end

def norm_mac(m)
  m.to_s.downcase.gsub(/[^0-9a-f]/, '')
end

def mac_variants(macs)
  macs.flat_map do |m|
    n = norm_mac(m)
    next [] if n.length != 12
    colon = n.scan(/../).join(':')
    [m, n, colon, colon.upcase, n.scan(/../).join('-')]
  end.uniq
end

def pct(n, total)
  total.zero? ? '  - ' : format('%3d%%', (100.0 * n / total).round)
end

def deaf_sql(minutes, having)
  "WITH recent AS (SELECT * FROM dhcp_messages WHERE created_at > now() - interval '#{minutes} minutes') " \
  'SELECT mac, count(*) FROM (SELECT *, lag(message_type) OVER (PARTITION BY mac ORDER BY created_at) ' \
  'AS prev_message_type, created_at - lag(created_at) OVER (PARTITION BY mac ORDER BY created_at) AS delta ' \
  "FROM recent) s WHERE message_type != 'REQUEST' AND prev_message_type = 'OFFER' " \
  "AND delta < interval '1 minute' GROUP BY mac HAVING count(*) >= #{having} ORDER BY count(*) DESC"
end

conn = ActiveRecord::Base.connection
deaf = conn.select_rows(deaf_sql(MINUTES, MIN_IGNORED)).to_h { |m, c| [norm_mac(m), c.to_i] }
dash = conn.select_values(deaf_sql(MINUTES, DASHBOARD_HAVING + 1)).size

puts "=== Deaf DHCP clients, last #{MINUTES}m ==="
puts format('  %-44s %6d', "deaf by this run's threshold (>= #{MIN_IGNORED} ignored)", deaf.size)
puts format('  %-44s %6d', "deaf by the dashboard's threshold (> #{DASHBOARD_HAVING})", dash)
if deaf.empty?
  puts '  (none)'
  exit 0
end

msgs = DhcpMessage.where(mac: mac_variants(deaf.keys))
                  .where('created_at > ?', MINUTES.minutes.ago)
                  .order(:created_at)
                  .pluck(:mac, :message_type, :created_at, :via, :access_point_id)
                  .group_by { |m, *| norm_mac(m) }

clients = WirelessClient.where(mac: mac_variants(deaf.keys)).order(updated_at: :desc).to_a
                        .group_by { |c| norm_mac(c.mac) }.transform_values(&:first)
aps = AccessPoint.where(id: (clients.values.map(&:access_point_id) +
                            msgs.values.flatten(1).map(&:last)).compact.uniq).index_by(&:id)

rows = deaf.map do |mac, ignored|
  ev = msgs[mac] || []
  types = ev.map { |e| e[1].to_s.upcase }.tally
  c = clients[mac]
  ap = c && aps[c.access_point_id]
  ap ||= aps[ev.map(&:last).compact.last]
  {
    mac: mac, ignored: ignored, first: ev.first&.at(2), last: ev.last&.at(2),
    discover: types['DISCOVER'].to_i, offer: types['OFFER'].to_i, request: types['REQUEST'].to_i,
    ack: types['ACK'].to_i, nak: types['NAK'].to_i,
    dhcp_vlans: ev.map { |e| via_label(e[3]) }.compact.uniq,
    ap: ap&.name, model: ap&.model.presence || '(unknown)', connected: !c.nil?,
    client_vlan: c&.vlan_tag, rssi: c&.rssi, to_client: c&.bytes_to_client, from_client: c&.bytes_from_client,
    seen: c&.updated_at
  }
end

def tab(title, rows, &key)
  puts title
  rows.group_by(&key).sort_by { |_, g| -g.size }.first(12).each do |k, g|
    puts format('  %-34s %5d  %s', k.to_s[0, 34], g.size, pct(g.size, rows.size))
  end
  puts
end

puts
tab('By AP model (where connected now, else where its DHCP came from):', rows) { |r| r[:model] }
tab('By client VLAN (connected_clients):', rows) { |r| r[:client_vlan] || '(not connected)' }
tab('By signal:', rows) do |r|
  s = r[:rssi]
  next '(unknown)' if s.nil? || s.zero?
  s >= -60 ? 'a. >= -60 dBm' : s >= -70 ? 'b. -60..-70' : s >= -80 ? 'c. -70..-80' : 'd. < -80'
end
tab('Got an ACK in the window as well:', rows) { |r| r[:ack].positive? ? 'yes -- deaf only some of the time' : 'no ACK at all' }
tab('First ignored offer, by 15-minute bucket:', rows.select { |r| r[:first] }) do |r|
  t = r[:first].in_time_zone
  t.change(min: (t.min / 15) * 15).strftime('%H:%M')
end

# Normalise by population: deaf per connected client of each model.
pop = WirelessClient.where('connected_clients.updated_at > ?', MINUTES.minutes.ago).joins(:access_point)
                    .group('access_points.model').count
puts 'Deaf rate by AP model (deaf / clients seen in the window):'
rows.group_by { |r| r[:model] }.sort_by { |_, g| -g.size }.each do |model, g|
  puts format('  %-34s %5d / %-5d %s', model.to_s[0, 34], g.size, pop[model].to_i, pct(g.size, pop[model].to_i))
end
puts

puts "Deaf MACs (top #{LIST} by ignored offers):"
puts format('  %-14s %4s %4s %4s %4s %4s  %-28s %-18s %5s %5s %9s  %s',
            'MAC', 'IGN', 'DISC', 'OFF', 'REQ', 'ACK', 'AP', 'MODEL', 'VLAN', 'RSSI', 'TO_CLIENT', 'DHCP_VLANS')
rows.sort_by { |r| -r[:ignored] }.first(LIST).each do |r|
  puts format('  %-14s %4d %4d %4d %4d %4d  %-28s %-18s %5s %5s %9s  %s',
              r[:mac], r[:ignored], r[:discover], r[:offer], r[:request], r[:ack],
              r[:ap].to_s[0, 28], r[:model].to_s[0, 18], r[:client_vlan], r[:rssi], r[:to_client],
              r[:dhcp_vlans].join(','))
end

File.open(OUT, 'w') do |io|
  keys = rows.first.keys
  io.puts keys.join("\t")
  rows.each { |r| io.puts keys.map { |k| v = r[k]; v.is_a?(Array) ? v.join(',') : v }.join("\t") }
end
puts
puts "Per-MAC detail: #{OUT}"