-- mcp: squashed from 1400_mcp.sql, 1401_mcp_agents_audience.sql.

CREATE TABLE oauth_clients (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  redirect_uris TEXT NOT NULL,
  -- 'dcr' for RFC 7591 registrations, 'cimd' for Client ID Metadata Documents (client_id is a URL).
  kind TEXT NOT NULL CHECK (kind IN ('dcr', 'cimd')),
  created_at INTEGER NOT NULL,
  last_used_at INTEGER
);

CREATE TABLE oauth_codes (
  code_hash TEXT PRIMARY KEY,
  client_id TEXT NOT NULL,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  redirect_uri TEXT NOT NULL,
  code_challenge TEXT NOT NULL,
  resource TEXT NOT NULL,
  -- JSON: { permissions, allRepos, repoIds, agent }
  grant_json TEXT NOT NULL,
  expires_at INTEGER NOT NULL
);

-- One OAuth grant is one access_tokens row for its whole life: refreshing rotates that row's secret in
-- place. Rotated refresh tokens are kept (used_at set) so replaying one revokes the grant.
CREATE TABLE oauth_refresh_tokens (
  token_hash TEXT PRIMARY KEY,
  client_id TEXT NOT NULL,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  access_token_id TEXT NOT NULL REFERENCES access_tokens(id) ON DELETE CASCADE,
  resource TEXT NOT NULL,
  grant_json TEXT NOT NULL,
  expires_at INTEGER NOT NULL,
  used_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX oauth_refresh_tokens_user ON oauth_refresh_tokens(user_id);

CREATE INDEX oauth_refresh_tokens_access ON oauth_refresh_tokens(access_token_id);

-- Which agent a token belongs to and which human authorized it; drives commit/PR attribution.
-- parent_token_id marks tokens delegated through create_repo_token.
CREATE TABLE mcp_token_agents (
  token_id TEXT PRIMARY KEY REFERENCES access_tokens(id) ON DELETE CASCADE,
  agent_name TEXT NOT NULL,
  client_id TEXT,
  authorized_by TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  parent_token_id TEXT,
  created_at INTEGER NOT NULL,
  agent_id TEXT REFERENCES agents(id) ON DELETE CASCADE,
  kind TEXT NOT NULL DEFAULT 'oauth'
);

CREATE INDEX mcp_token_agents_parent ON mcp_token_agents(parent_token_id);

CREATE INDEX mcp_token_agents_agent ON mcp_token_agents(agent_id);

CREATE TABLE mcp_audit (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  token_id TEXT,
  agent_name TEXT,
  repo_id TEXT,
  tool TEXT NOT NULL,
  arguments TEXT NOT NULL,
  outcome TEXT NOT NULL CHECK (outcome IN ('ok', 'denied', 'error')),
  detail TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX mcp_audit_user ON mcp_audit(user_id, created_at DESC);

CREATE INDEX mcp_audit_repo ON mcp_audit(repo_id, created_at DESC);

-- A named agent (an "app") a user registers; tokens minted for it carry its identity, so commit and
-- pull request attribution comes from the token instead of anything the client claims.
CREATE TABLE agents (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  description TEXT,
  created_at INTEGER NOT NULL,
  UNIQUE (owner_id, name)
);

-- Deleting an agent revokes every token minted for it.
CREATE TRIGGER agents_revoke_tokens BEFORE DELETE ON agents
BEGIN
  DELETE FROM access_tokens WHERE id IN (SELECT token_id FROM mcp_token_agents WHERE agent_id = OLD.id);
END;

-- Usage metering: MCP tool calls per account (the token owner) per UTC day, split by tool.
CREATE TABLE mcp_usage_daily (
  account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  day TEXT NOT NULL,
  tool TEXT NOT NULL,
  calls INTEGER NOT NULL DEFAULT 0,
  errors INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, day, tool)
);