-- Baselines and replay watermark outlive raw sample retention and UI resets.
CREATE TABLE agent_telemetry_state (
  agent_id TEXT PRIMARY KEY REFERENCES agents(id) ON DELETE CASCADE,
  sequence INTEGER NOT NULL,
  report_id TEXT NOT NULL,
  boot_id TEXT NOT NULL,
  traffic_epoch TEXT NOT NULL,
  observed_at TEXT NOT NULL,
  received_at TEXT NOT NULL,
  dropped_baseline INTEGER,
  accepted_baseline INTEGER
);

ALTER TABLE agents ADD COLUMN telemetry_version INTEGER;
ALTER TABLE agents ADD COLUMN last_telemetry_at TEXT;
ALTER TABLE agents ADD COLUMN applied_hash TEXT;
ALTER TABLE agents ADD COLUMN applied_revision INTEGER;
ALTER TABLE agents ADD COLUMN desired_hash TEXT;
ALTER TABLE agents ADD COLUMN desired_hash_generation INTEGER;
ALTER TABLE agents ADD COLUMN last_policy_delivered_at TEXT;
ALTER TABLE agents ADD COLUMN capabilities_json TEXT NOT NULL DEFAULT '[]';
ALTER TABLE agents ADD COLUMN quality_json TEXT NOT NULL DEFAULT '{"partial":true,"truncated":false,"unsupported":["telemetry_v2"],"lost_reports":0}';
ALTER TABLE agents ADD COLUMN apply_error_json TEXT;
ALTER TABLE agents ADD COLUMN detail_counts_json TEXT;

ALTER TABLE agent_stats_samples ADD COLUMN telemetry_version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE agent_stats_samples ADD COLUMN report_id TEXT;
ALTER TABLE agent_stats_samples ADD COLUMN sequence INTEGER;
ALTER TABLE agent_stats_samples ADD COLUMN observed_at TEXT;
ALTER TABLE agent_stats_samples ADD COLUMN interval_ms INTEGER;
ALTER TABLE agent_stats_samples ADD COLUMN delta_dropped INTEGER;
ALTER TABLE agent_stats_samples ADD COLUMN delta_accepted INTEGER;
ALTER TABLE agent_stats_samples ADD COLUMN quality_json TEXT;
ALTER TABLE agent_stats_samples ADD COLUMN detail_counts_json TEXT;
CREATE UNIQUE INDEX idx_stats_agent_report ON agent_stats_samples(agent_id, report_id);

ALTER TABLE agent_ip_block_stats ADD COLUMN counter_epoch TEXT;
ALTER TABLE agent_port_block_stats ADD COLUMN counter_epoch TEXT;

CREATE INDEX idx_ip_stats_last_seen ON agent_ip_block_stats(last_seen_at);
CREATE INDEX idx_port_stats_last_seen ON agent_port_block_stats(last_seen_at);