-- Per-IP/CIDR drop counters from Linux agent nft/ipset element counters.
CREATE TABLE IF NOT EXISTS agent_ip_block_stats (
  id TEXT PRIMARY KEY NOT NULL,
  agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  ip TEXT NOT NULL,
  packets INTEGER NOT NULL DEFAULT 0,
  last_reported_packets INTEGER NOT NULL DEFAULT 0,
  first_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  last_seen_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_agent_ip_block_stats_agent_ip
  ON agent_ip_block_stats(agent_id, ip);

CREATE INDEX IF NOT EXISTS idx_agent_ip_block_stats_agent_packets
  ON agent_ip_block_stats(agent_id, packets);