-- M33 follow-up: per-user manual-scan job tracking.
--
-- The Manual Scan UI fans a single click into N parallel scans (one
-- transient systemd unit per user). Without persistent tracking, the
-- table can't surface "who's scanning right now" or "when was this
-- user last scanned" — the agent's only state is the systemd journal
-- which evicts. One row per username, upserted at scan start and
-- finalised by the post-scan-hook ingest.
--
-- Username is PK rather than user_id because (a) the agent operates on
-- /home/<user> regardless of whether the panel knows the user (scan_path
-- mode), and (b) it gives us a single deterministic upsert key. user_id
-- is denormalised when known so the UI can deep-link without a re-join.

CREATE TABLE IF NOT EXISTS malware_user_scans (
  username     VARCHAR(32)  NOT NULL,
  user_id      VARCHAR(26)  NULL,
  session_id   VARCHAR(26)  NOT NULL,
  target_path  VARCHAR(255) NOT NULL,
  state        ENUM('running','done','failed') NOT NULL DEFAULT 'running',
  hits         INT UNSIGNED NOT NULL DEFAULT 0,
  exit_code    INT          NULL,
  started_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at  TIMESTAMP    NULL,
  PRIMARY KEY (username),
  KEY idx_malware_user_scans_state (state),
  KEY idx_malware_user_scans_session (session_id),
  KEY idx_malware_user_scans_path (target_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
