24.11-Prompt模板市场

-- Prompt 模板表
CREATE TABLE prompt_templates (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,                       -- NULL 表示系统预置
  workspace_id TEXT,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT,                        -- writing/coding/analysis/chat
  content TEXT NOT NULL,                -- 模板内容
  variables TEXT,                       -- JSON: 变量定义
  model_preferences TEXT,               -- JSON: 推荐模型配置
  is_public INTEGER DEFAULT 0,
  is_system INTEGER DEFAULT 0,
  usage_count INTEGER DEFAULT 0,
  version INTEGER DEFAULT 1,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (workspace_id) REFERENCES workspaces(id)
)
 
-- 模板历史版本表
CREATE TABLE prompt_template_versions (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL,
  version INTEGER NOT NULL,
  content TEXT NOT NULL,
  variables TEXT,
  created_by TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (template_id) REFERENCES prompt_templates(id),
  UNIQUE(template_id, version)
)
 
-- 模板收藏表
CREATE TABLE prompt_template_favorites (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  template_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (template_id) REFERENCES prompt_templates(id),
  UNIQUE(user_id, template_id)
)

// src/types/prompt.ts
export interface PromptTemplate {
  id: string
  tenantId?: string
  workspaceId?: string
  name: string
  description?: string
  category?: string
  content: string
  variables: PromptVariable[]
  modelPreferences?: {
    model?: string
    temperature?: number
    maxTokens?: number
  }
  isPublic: boolean
  isSystem: boolean
  usageCount: number
  version: number
  createdAt: number
  updatedAt: number
}
 
export interface PromptVariable {
  name: string
  description?: string
  type: 'string' | 'number' | 'boolean' | 'list'
  required: boolean
  defaultValue?: any
  options?: string[]    // 当 type 为 list 时
}
 
export interface PromptTemplateVersion {
  id: string
  templateId: string
  version: number
  content: string
  variables: PromptVariable[]
  createdBy: string
  createdAt: number
}

// src/routes/prompts.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { generateId } from '../lib/utils'
 
const app = new Hono()
 
const VariableSchema = z.object({
  name: z.string(),
  description: z.string().optional(),
  type: z.enum(['string', 'number', 'boolean', 'list']),
  required: z.boolean().optional(),
  defaultValue: z.any().optional(),
  options: z.array(z.string()).optional(),
})
 
app.post('/', zValidator('json', z.object({
  name: z.string().min(1).max(100),
  description: z.string().optional(),
  category: z.string().optional(),
  content: z.string(),
  variables: z.array(VariableSchema).optional(),
  modelPreferences: z.object({
    model: z.string().optional(),
    temperature: z.number().optional(),
    maxTokens: z.number().optional(),
  }).optional(),
  workspaceId: z.string().optional(),
  isPublic: z.boolean().optional(),
})), async (c) => {
  const tenantId = c.get('tenantId')
  const userId = c.get('userId')
  const data = c.req.valid('json')
 
  const id = generateId()
  const now = Date.now()
 
  await c.env.DB.prepare(`
    INSERT INTO prompt_templates (id, tenant_id, workspace_id, name, description, category, content, variables, model_preferences, is_public, is_system, version, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 1, ?, ?)
  `).bind(
    id,
    tenantId,
    data.workspaceId || null,
    data.name,
    data.description || null,
    data.category || null,
    data.content,
    JSON.stringify(data.variables || []),
    data.modelPreferences ? JSON.stringify(data.modelPreferences) : null,
    data.isPublic ? 1 : 0,
    now,
    now
  ).run()
 
  // 创建初始版本
  await c.env.DB.prepare(`
    INSERT INTO prompt_template_versions (id, template_id, version, content, variables, created_by, created_at)
    VALUES (?, ?, 1, ?, ?, ?, ?)
  `).bind(
    generateId(),
    id,
    data.content,
    JSON.stringify(data.variables || []),
    userId,
    now
  ).run()
 
  return c.json({ id, name: data.name, version: 1 }, 201)
})

app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
  const workspaceId = c.req.query('workspaceId')
  const category = c.req.query('category')
  const search = c.req.query('search')
 
  let query = `
    SELECT * FROM prompt_templates
    WHERE (tenant_id = ? OR is_public = 1 OR is_system = 1)
  `
  const params: any[] = [tenantId]
 
  if (workspaceId) {
    query += ` AND (workspace_id = ? OR workspace_id IS NULL)`
    params.push(workspaceId)
  }
 
  if (category) {
    query += ` AND category = ?`
    params.push(category)
  }
 
  if (search) {
    query += ` AND (name LIKE ? OR description LIKE ?)`
    params.push(`%${search}%`, `%${search}%`)
  }
 
  query += ` ORDER BY is_system DESC, usage_count DESC, created_at DESC`
 
  const templates = await c.env.DB.prepare(query).bind(...params).all()
 
  return c.json({
    templates: templates.results.map(t => ({
      id: t.id,
      name: t.name,
      description: t.description,
      category: t.category,
      variables: JSON.parse(t.variables || '[]'),
      isPublic: t.is_public === 1,
      isSystem: t.is_system === 1,
      usageCount: t.usage_count,
      version: t.version,
    })),
  })
})

app.put('/:id', zValidator('json', z.object({
  name: z.string().optional(),
  description: z.string().optional(),
  category: z.string().optional(),
  content: z.string().optional(),
  variables: z.array(VariableSchema).optional(),
  modelPreferences: z.object({
    model: z.string().optional(),
    temperature: z.number().optional(),
    maxTokens: z.number().optional(),
  }).optional(),
  isPublic: z.boolean().optional(),
})), async (c) => {
  const templateId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const userId = c.get('userId')
  const updates = c.req.valid('json')
 
  // 验证权限
  const template = await c.env.DB.prepare(`
    SELECT * FROM prompt_templates WHERE id = ? AND tenant_id = ?
  `).bind(templateId, tenantId).first()
 
  if (!template) {
    return c.json({ error: '模板不存在' }, 404)
  }
 
  if (template.is_system === 1) {
    return c.json({ error: '系统模板不能修改' }, 403)
  }
 
  const now = Date.now()
  const newVersion = template.version + 1
 
  // 更新模板
  const setClauses = ['version = ?', 'updated_at = ?']
  const values: any[] = [newVersion, now]
 
  if (updates.name) {
    setClauses.push('name = ?')
    values.push(updates.name)
  }
  if (updates.description !== undefined) {
    setClauses.push('description = ?')
    values.push(updates.description)
  }
  if (updates.category !== undefined) {
    setClauses.push('category = ?')
    values.push(updates.category)
  }
  if (updates.content) {
    setClauses.push('content = ?')
    values.push(updates.content)
  }
  if (updates.variables) {
    setClauses.push('variables = ?')
    values.push(JSON.stringify(updates.variables))
  }
  if (updates.modelPreferences) {
    setClauses.push('model_preferences = ?')
    values.push(JSON.stringify(updates.modelPreferences))
  }
  if (updates.isPublic !== undefined) {
    setClauses.push('is_public = ?')
    values.push(updates.isPublic ? 1 : 0)
  }
 
  values.push(templateId)
 
  await c.env.DB.prepare(`
    UPDATE prompt_templates SET ${setClauses.join(', ')} WHERE id = ?
  `).bind(...values).run()
 
  // 创建新版本
  if (updates.content || updates.variables) {
    await c.env.DB.prepare(`
      INSERT INTO prompt_template_versions (id, template_id, version, content, variables, created_by, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).bind(
      generateId(),
      templateId,
      newVersion,
      updates.content || template.content,
      JSON.stringify(updates.variables || JSON.parse(template.variables || '[]')),
      userId,
      now
    ).run()
  }
 
  return c.json({ success: true, version: newVersion })
})

// src/lib/prompt-renderer.ts
import { PromptTemplate, PromptVariable } from '../types/prompt'
 
export function renderPromptTemplate(
  template: PromptTemplate,
  variables: Record<string, any>
): string {
  let content = template.content
 
  // 验证必需变量
  for (const v of template.variables) {
    if (v.required && !(v.name in variables)) {
      throw new Error(`Missing required variable: ${v.name}`)
    }
  }
 
  // 替换变量
  for (const v of template.variables) {
    const value = variables[v.name] ?? v.defaultValue ?? ''
    const placeholder = new RegExp(`\\{\\{${v.name}\\}\\}`, 'g')
    content = content.replace(placeholder, String(value))
  }
 
  return content
}
 
// 使用示例
const template: PromptTemplate = {
  id: '1',
  name: '翻译助手',
  content: '请将以下{{sourceLanguage}}文本翻译成{{targetLanguage}}：\n\n{{text}}',
  variables: [
    { name: 'sourceLanguage', type: 'string', required: true },
    { name: 'targetLanguage', type: 'list', required: true, options: ['中文', '英文', '日文'] },
    { name: 'text', type: 'string', required: true },
  ],
}
 
const rendered = renderPromptTemplate(template, {
  sourceLanguage: '英文',
  targetLanguage: '中文',
  text: 'Hello, world!',
})
// => 请将以下英文文本翻译成中文：\n\nHello, world!

// src/routes/chat.ts（补充）
app.post('/chat/with-template', zValidator('json', z.object({
  templateId: z.string(),
  variables: z.record(z.any()),
})), async (c) => {
  const tenantId = c.get('tenantId')
  const userId = c.get('userId')
  const { templateId, variables } = c.req.valid('json')
 
  // 获取模板
  const template = await c.env.DB.prepare(`
    SELECT * FROM prompt_templates
    WHERE id = ? AND (tenant_id = ? OR is_public = 1 OR is_system = 1)
  `).bind(templateId, tenantId).first()
 
  if (!template) {
    return c.json({ error: '模板不存在' }, 404)
  }
 
  // 渲染模板
  const content = renderPromptTemplate(
    {
      ...template,
      variables: JSON.parse(template.variables || '[]'),
      isPublic: template.is_public === 1,
      isSystem: template.is_system === 1,
    },
    variables
  )
 
  // 获取模型配置
  const modelPreferences = template.model_preferences
    ? JSON.parse(template.model_preferences)
    : {}
 
  // 调用 LLM
  const modelRouter = new ModelRouter(c.env)
  const { model, config } = await modelRouter.selectModel({
    tenantId,
    requestedModel: modelPreferences.model,
  })
 
  const finalConfig = {
    ...config,
    temperature: modelPreferences.temperature ?? config.temperature,
    maxTokens: modelPreferences.maxTokens ?? config.maxTokens,
  }
 
  const response = await callLLM(
    [{ role: 'user', content }],
    model,
    finalConfig,
    c.env
  )
 
  // 更新使用次数
  await c.env.DB.prepare(`
    UPDATE prompt_templates SET usage_count = usage_count + 1 WHERE id = ?
  `).bind(templateId).run()
 
  return c.json({
    message: response.content,
    usage: response.usage,
    template: {
      id: template.id,
      name: template.name,
    },
  })
})

// 获取模板历史版本
app.get('/:id/versions', async (c) => {
  const templateId = c.req.param('id')
  const tenantId = c.get('tenantId')
 
  // 验证权限
  const template = await c.env.DB.prepare(`
    SELECT * FROM prompt_templates
    WHERE id = ? AND (tenant_id = ? OR is_public = 1 OR is_system = 1)
  `).bind(templateId, tenantId).first()
 
  if (!template) {
    return c.json({ error: '模板不存在' }, 404)
  }
 
  const versions = await c.env.DB.prepare(`
    SELECT v.*, u.email as createdByEmail, u.name as createdByName
    FROM prompt_template_versions v
    JOIN users u ON v.created_by = u.id
    WHERE v.template_id = ?
    ORDER BY v.version DESC
  `).bind(templateId).all()
 
  return c.json({
    versions: versions.results.map(v => ({
      id: v.id,
      version: v.version,
      content: v.content,
      variables: JSON.parse(v.variables || '[]'),
      createdBy: {
        email: v.createdByEmail,
        name: v.createdByName,
      },
      createdAt: v.created_at,
    })),
  })
})
 
// 回滚到指定版本
app.post('/:id/rollback', zValidator('json', z.object({
  version: z.number(),
})), async (c) => {
  const templateId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const { version } = c.req.valid('json')
 
  // 验证权限
  const template = await c.env.DB.prepare(`
    SELECT * FROM prompt_templates WHERE id = ? AND tenant_id = ?
  `).bind(templateId, tenantId).first()
 
  if (!template) {
    return c.json({ error: '模板不存在' }, 404)
  }
 
  // 获取目标版本
  const targetVersion = await c.env.DB.prepare(`
    SELECT * FROM prompt_template_versions
    WHERE template_id = ? AND version = ?
  `).bind(templateId, version).first()
 
  if (!targetVersion) {
    return c.json({ error: '版本不存在' }, 404)
  }
 
  // 创建新版本（内容是目标版本的）
  const newVersion = template.version + 1
  const now = Date.now()
 
  await c.env.DB.batch([
    c.env.DB.prepare(`
      UPDATE prompt_templates
      SET content = ?, variables = ?, version = ?, updated_at = ?
      WHERE id = ?
    `).bind(targetVersion.content, targetVersion.variables, newVersion, now, templateId),
    c.env.DB.prepare(`
      INSERT INTO prompt_template_versions (id, template_id, version, content, variables, created_by, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).bind(
      generateId(),
      templateId,
      newVersion,
      targetVersion.content,
      targetVersion.variables,
      c.get('userId'),
      now
    ),
  ])
 
  return c.json({ success: true, version: newVersion })
})

app.post('/:id/favorite', async (c) => {
  const templateId = c.req.param('id')
  const userId = c.get('userId')
 
  const existing = await c.env.DB.prepare(`
    SELECT id FROM prompt_template_favorites WHERE user_id = ? AND template_id = ?
  `).bind(userId, templateId).first()
 
  if (existing) {
    return c.json({ error: '已收藏' }, 409)
  }
 
  await c.env.DB.prepare(`
    INSERT INTO prompt_template_favorites (id, user_id, template_id, created_at)
    VALUES (?, ?, ?, ?)
  `).bind(generateId(), userId, templateId, Date.now()).run()
 
  return c.json({ success: true })
})
 
app.delete('/:id/favorite', async (c) => {
  const templateId = c.req.param('id')
  const userId = c.get('userId')
 
  await c.env.DB.prepare(`
    DELETE FROM prompt_template_favorites WHERE user_id = ? AND template_id = ?
  `).bind(userId, templateId).run()
 
  return c.json({ success: true })
})
 
app.get('/favorites/list', async (c) => {
  const userId = c.get('userId')
 
  const templates = await c.env.DB.prepare(`
    SELECT t.* FROM prompt_templates t
    JOIN prompt_template_favorites f ON t.id = f.template_id
    WHERE f.user_id = ?
    ORDER BY f.created_at DESC
  `).bind(userId).all()
 
  return c.json({ templates: templates.results })
})

// 初始化系统模板
export async function initializeSystemTemplates(env: Env) {
  const systemTemplates = [
    {
      name: '通用助手',
      description: '通用的 AI 助手模板',
      category: 'chat',
      content: '你是一个友好的 AI 助手。请根据用户的问题提供帮助。',
      variables: [],
    },
    {
      name: '文本翻译',
      description: '将文本翻译成指定语言',
      category: 'writing',
      content: '请将以下{{sourceLanguage}}文本翻译成{{targetLanguage}}：\n\n{{text}}',
      variables: [
        { name: 'sourceLanguage', type: 'string', required: true },
        { name: 'targetLanguage', type: 'list', required: true, options: ['中文', '英文', '日文', '韩文'] },
        { name: 'text', type: 'string', required: true },
      ],
    },
    {
      name: '代码审查',
      description: '审查代码并提供改进建议',
      category: 'coding',
      content: '请审查以下{{language}}代码，指出潜在的问题并提供改进建议：\n\n```{{language}}\n{{code}}\n```',
      variables: [
        { name: 'language', type: 'list', required: true, options: ['JavaScript', 'Python', 'TypeScript', 'Go'] },
        { name: 'code', type: 'string', required: true },
      ],
    },
    {
      name: '文章摘要',
      description: '为长文章生成简洁摘要',
      category: 'writing',
      content: '请为以下文章生成一个{{length}}字的摘要：\n\n{{article}}',
      variables: [
        { name: 'length', type: 'number', required: true, defaultValue: 200 },
        { name: 'article', type: 'string', required: true },
      ],
    },
  ]
 
  const now = Date.now()
 
  for (const template of systemTemplates) {
    const existing = await env.DB.prepare(`
      SELECT id FROM prompt_templates WHERE name = ? AND is_system = 1
    `).bind(template.name).first()
 
    if (!existing) {
      await env.DB.prepare(`
        INSERT INTO prompt_templates (id, name, description, category, content, variables, is_system, version, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, 1, 1, ?, ?)
      `).bind(
        generateId(),
        template.name,
        template.description,
        template.category,
        template.content,
        JSON.stringify(template.variables),
        now,
        now
      ).run()
    }
  }
}