-- ai endpoints: bring-your-own AI at account and repo scope (repo overrides account), with custom endpoints.
-- kind: 'provider' (a provider key routed through this instance's AI Gateway), 'openai' (any OpenAI-compatible
-- base URL) or 'cf-gateway' (the owner's own Cloudflare AI Gateway, by account and gateway id).

CREATE TABLE ai_endpoints (
  scope TEXT NOT NULL CHECK (scope IN ('account', 'repo')),
  owner_id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('provider', 'openai', 'cf-gateway')),
  provider TEXT,
  model TEXT NOT NULL,
  base_url TEXT,
  cf_account_id TEXT,
  cf_gateway_id TEXT,
  key_ciphertext TEXT NOT NULL,
  key_iv TEXT NOT NULL,
  -- 0 for keys copied from account_ai_keys (generic seal); re-sealed to their scope and owner on first use.
  scoped_seal INTEGER NOT NULL DEFAULT 1,
  updated_by TEXT,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (scope, owner_id)
);

INSERT INTO ai_endpoints (scope, owner_id, kind, provider, model, key_ciphertext, key_iv, scoped_seal, updated_by, updated_at)
  SELECT 'account', account_id, 'provider', provider, model, key_ciphertext, key_iv, 0, updated_by, updated_at
  FROM account_ai_keys;

DROP TABLE account_ai_keys;

CREATE TRIGGER ai_endpoints_repo_deleted AFTER DELETE ON repos
BEGIN
  DELETE FROM ai_endpoints WHERE scope = 'repo' AND owner_id = OLD.id;
END;

CREATE TRIGGER ai_endpoints_account_deleted AFTER DELETE ON accounts
BEGIN
  DELETE FROM ai_endpoints WHERE scope = 'account' AND owner_id = OLD.id;
END;