-- Per-agent L4 port ACL (desired state) + host firewall snapshot (observed).

CREATE TABLE IF NOT EXISTS agent_port_rules (
  id TEXT PRIMARY KEY NOT NULL,
  agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  action TEXT NOT NULL,
  protocol TEXT NOT NULL,
  port_start INTEGER NOT NULL,
  port_end INTEGER NOT NULL,
  src_kind TEXT NOT NULL,
  src_cidr TEXT,
  list_id TEXT REFERENCES ip_lists(id) ON DELETE SET NULL,
  enabled INTEGER NOT NULL DEFAULT 1,
  comment TEXT,
  priority INTEGER NOT NULL DEFAULT 100,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_agent_port_rules_agent_priority
  ON agent_port_rules(agent_id, priority);

CREATE INDEX IF NOT EXISTS idx_agent_port_rules_agent_enabled
  ON agent_port_rules(agent_id, enabled);

CREATE TABLE IF NOT EXISTS agent_host_firewall_snapshots (
  agent_id TEXT PRIMARY KEY NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  collected_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  payload_json TEXT NOT NULL,
  raw_digest TEXT
);