-- ============ Secret key versioning (master-key rotation) ============
-- crypto_keys tracks WHICH key versions exist and which one is the current
-- write ("active") key. The key material itself stays in env/KMS — only the
-- version number lives in the DB, both here and in each encrypted row's
-- key_version column. This lets master-key rotation re-encrypt blobs version by
-- version without a magic ciphertext prefix.
CREATE TABLE crypto_keys (
  version    INT PRIMARY KEY,
  provider   TEXT NOT NULL DEFAULT 'env',
  key_ref    TEXT,
  status     TEXT NOT NULL DEFAULT 'active'
             CHECK (status IN ('active', 'retiring', 'retired')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed the historical single key as version 1, active.
INSERT INTO crypto_keys (version, provider, status) VALUES (1, 'env', 'active');

-- Per-row key_version on every encrypted-blob owner. DEFAULT 1 backfills every
-- existing row to the historical key. The re-encrypt job finds rows on an old
-- version and Decrypt selects the right key by this column.
ALTER TABLE acp_agent_kinds              ADD COLUMN key_version SMALLINT NOT NULL DEFAULT 1;
ALTER TABLE acp_agent_kind_config_files  ADD COLUMN key_version SMALLINT NOT NULL DEFAULT 1;
ALTER TABLE workspace_run_profiles       ADD COLUMN key_version SMALLINT NOT NULL DEFAULT 1;
-- 注意：git 凭据密文在 git_credentials.encrypted_secret，不在 workspaces（后者无密文列）。
ALTER TABLE git_credentials              ADD COLUMN key_version SMALLINT NOT NULL DEFAULT 1;
ALTER TABLE llm_endpoints                ADD COLUMN key_version SMALLINT NOT NULL DEFAULT 1;

-- Partial indexes to make the re-encrypt scan (WHERE key_version < active AND
-- blob IS NOT NULL) cheap. Only index rows that actually carry ciphertext.
CREATE INDEX acp_agent_kinds_keyver_idx ON acp_agent_kinds (key_version)
  WHERE encrypted_env IS NOT NULL OR encrypted_mcp_servers IS NOT NULL;
CREATE INDEX acp_agent_kind_config_files_keyver_idx ON acp_agent_kind_config_files (key_version)
  WHERE encrypted_content IS NOT NULL;
CREATE INDEX workspace_run_profiles_keyver_idx ON workspace_run_profiles (key_version)
  WHERE encrypted_env IS NOT NULL;
CREATE INDEX git_credentials_keyver_idx ON git_credentials (key_version)
  WHERE encrypted_secret IS NOT NULL;
CREATE INDEX llm_endpoints_keyver_idx ON llm_endpoints (key_version)
  WHERE api_key_encrypted IS NOT NULL;

-- Record which sandbox mode a session ran under (audit/forensics). Cheap and
-- recommended by the spec; NULL for sessions that predate this column.
ALTER TABLE acp_sessions ADD COLUMN sandbox_mode TEXT;