24.13-后台管理系统

-- 管理员表
CREATE TABLE admins (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL,                 -- super_admin/admin/operator
  is_active INTEGER DEFAULT 1,
  last_login_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
)
 
-- 管理员操作日志
CREATE TABLE admin_audit_logs (
  id TEXT PRIMARY KEY,
  admin_id TEXT NOT NULL,
  action TEXT NOT NULL,               -- create/update/delete
  resource_type TEXT NOT NULL,        -- tenant/user/order/config
  resource_id TEXT,
  details TEXT,                       -- JSON: 变更详情
  ip_address TEXT,
  user_agent TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (admin_id) REFERENCES admins(id)
)

// src/routes/admin/auth.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { sign } from 'hono/jwt'
import { verifyPassword } from '../../lib/auth'
 
const app = new Hono()
 
app.post('/login', zValidator('json', z.object({
  email: z.string().email(),
  password: z.string(),
})), async (c) => {
  const { email, password } = c.req.valid('json')
 
  // 查找管理员
  const admin = await c.env.DB.prepare(`
    SELECT * FROM admins WHERE email = ? AND is_active = 1
  `).bind(email).first()
 
  if (!admin) {
    return c.json({ error: '邮箱或密码错误' }, 401)
  }
 
  // 验证密码
  const valid = await verifyPassword(password, admin.password_hash)
  if (!valid) {
    return c.json({ error: '邮箱或密码错误' }, 401)
  }
 
  // 生成 JWT
  const token = await sign(
    {
      adminId: admin.id,
      role: admin.role,
      exp: Math.floor(Date.now() / 1000) + 60 * 60 * 8,  // 8 小时
    },
    c.env.JWT_SECRET
  )
 
  // 更新登录时间
  await c.env.DB.prepare(`
    UPDATE admins SET last_login_at = ? WHERE id = ?
  `).bind(Date.now(), admin.id).run()
 
  return c.json({
    token,
    admin: {
      id: admin.id,
      email: admin.email,
      name: admin.name,
      role: admin.role,
    },
  })
})

// src/middleware/admin-auth.ts
import { Context, Next } from 'hono'
import { verify } from 'hono/jwt'
 
export async function adminAuthMiddleware(c: Context, next: Next) {
  const authHeader = c.req.header('Authorization')
 
  if (!authHeader?.startsWith('Bearer ')) {
    return c.json({ error: 'Unauthorized' }, 401)
  }
 
  const token = authHeader.slice(7)
 
  try {
    const payload = await verify(token, c.env.JWT_SECRET)
    const adminId = payload.adminId as string
    const role = payload.role as string
 
    // 验证管理员是否存在且活跃
    const admin = await c.env.DB.prepare(`
      SELECT id, role FROM admins WHERE id = ? AND is_active = 1
    `).bind(adminId).first()
 
    if (!admin) {
      return c.json({ error: 'Admin not found' }, 401)
    }
 
    c.set('adminId', adminId)
    c.set('adminRole', role)
 
    await next()
  } catch (error) {
    return c.json({ error: 'Invalid token' }, 401)
  }
}
 
export function requireAdminRole(...roles: string[]) {
  return async (c: Context, next: Next) => {
    const role = c.get('adminRole')
 
    if (!roles.includes(role)) {
      return c.json({ error: 'Forbidden' }, 403)
    }
 
    await next()
  }
}

// src/routes/admin/dashboard.ts
import { Hono } from 'hono'
import { adminAuthMiddleware } from '../../middleware/admin-auth'
 
const app = new Hono()
app.use('*', adminAuthMiddleware)
 
// 概览统计
app.get('/overview', async (c) => {
  const now = Date.now()
  const today = new Date()
  today.setHours(0, 0, 0, 0)
  const todayStart = today.getTime()
 
  const monthStart = new Date(today.getFullYear(), today.getMonth(), 1).getTime()
 
  // 租户统计
  const tenantStats = await c.env.DB.prepare(`
    SELECT
      COUNT(*) as total,
      SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
      SUM(CASE WHEN created_at > ? THEN 1 ELSE 0 END) as newToday
    FROM tenants
  `).bind(todayStart).first()
 
  // 收入统计
  const revenueStats = await c.env.DB.prepare(`
    SELECT
      COALESCE(SUM(amount_paid), 0) as monthRevenue,
      COALESCE(SUM(CASE WHEN status = 'paid' THEN amount_paid ELSE 0 END), 0) as totalRevenue
    FROM invoices
    WHERE created_at > ?
  `).bind(monthStart).first()
 
  // 用量统计
  const usageStats = await c.env.DB.prepare(`
    SELECT
      COALESCE(SUM(total_tokens), 0) as tokensToday,
      COUNT(DISTINCT tenant_id) as activeTenantsToday
    FROM usage_daily
    WHERE date = date('now')
  `).first()
 
  // 活跃用户（今日有调用）
  const activeUsers = await c.env.DB.prepare(`
    SELECT COUNT(DISTINCT user_id) as count
    FROM usage_records
    WHERE created_at > ?
  `).bind(todayStart).first()
 
  return c.json({
    tenants: {
      total: tenantStats.total,
      active: tenantStats.active,
      newToday: tenantStats.newToday,
    },
    revenue: {
      month: revenueStats.monthRevenue,
      total: revenueStats.totalRevenue,
    },
    usage: {
      tokensToday: usageStats.tokensToday,
      activeTenantsToday: usageStats.activeTenantsToday,
      activeUsersToday: activeUsers.count,
    },
  })
})
 
// 租户增长趋势
app.get('/tenant-growth', async (c) => {
  const days = parseInt(c.req.query('days') || '30')
 
  const trend = await c.env.DB.prepare(`
    SELECT
      DATE(created_at / 1000, 'unixepoch') as date,
      COUNT(*) as count
    FROM tenants
    WHERE created_at > ?
    GROUP BY DATE(created_at / 1000, 'unixepoch')
    ORDER BY date ASC
  `).bind(Date.now() - days * 24 * 60 * 60 * 1000).all()
 
  return c.json({ trend: trend.results })
})
 
// 收入趋势
app.get('/revenue-trend', async (c) => {
  const days = parseInt(c.req.query('days') || '30')
 
  const trend = await c.env.DB.prepare(`
    SELECT
      DATE(created_at / 1000, 'unixepoch') as date,
      COALESCE(SUM(amount_paid), 0) as revenue
    FROM invoices
    WHERE status = 'paid' AND created_at > ?
    GROUP BY DATE(created_at / 1000, 'unixepoch')
    ORDER BY date ASC
  `).bind(Date.now() - days * 24 * 60 * 60 * 1000).all()
 
  return c.json({ trend: trend.results })
})

// src/routes/admin/tenants.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { adminAuthMiddleware, requireAdminRole } from '../../middleware/admin-auth'
 
const app = new Hono()
app.use('*', adminAuthMiddleware)
 
// 获取租户列表
app.get('/', async (c) => {
  const page = parseInt(c.req.query('page') || '1')
  const pageSize = parseInt(c.req.query('pageSize') || '20')
  const search = c.req.query('search')
  const status = c.req.query('status')
  const offset = (page - 1) * pageSize
 
  let query = `
    SELECT t.*,
           (SELECT COUNT(*) FROM users WHERE tenant_id = t.id) as userCount,
           (SELECT COALESCE(SUM(total_tokens), 0) FROM usage_monthly WHERE tenant_id = t.id AND month = strftime('%Y-%m', 'now')) as monthTokens
    FROM tenants t
    WHERE 1=1
  `
  const params: any[] = []
 
  if (search) {
    query += ` AND (t.name LIKE ? OR t.slug LIKE ?)`
    params.push(`%${search}%`, `%${search}%`)
  }
 
  if (status) {
    query += ` AND t.status = ?`
    params.push(status)
  }
 
  query += ` ORDER BY t.created_at DESC LIMIT ? OFFSET ?`
  params.push(pageSize, offset)
 
  const tenants = await c.env.DB.prepare(query).bind(...params).all()
 
  // 总数
  let countQuery = `SELECT COUNT(*) as count FROM tenants t WHERE 1=1`
  const countParams: any[] = []
 
  if (search) {
    countQuery += ` AND (t.name LIKE ? OR t.slug LIKE ?)`
    countParams.push(`%${search}%`, `%${search}%`)
  }
 
  if (status) {
    countQuery += ` AND t.status = ?`
    countParams.push(status)
  }
 
  const total = await c.env.DB.prepare(countQuery).bind(...countParams).first()
 
  return c.json({
    tenants: tenants.results.map(t => ({
      id: t.id,
      name: t.name,
      slug: t.slug,
      plan: t.plan,
      status: t.status,
      userCount: t.userCount,
      monthTokens: t.monthTokens,
      createdAt: t.created_at,
    })),
    pagination: {
      page,
      pageSize,
      total: total.count,
    },
  })
})
 
// 获取租户详情
app.get('/:id', async (c) => {
  const tenantId = c.req.param('id')
 
  const tenant = await c.env.DB.prepare(`
    SELECT * FROM tenants WHERE id = ?
  `).bind(tenantId).first()
 
  if (!tenant) {
    return c.json({ error: 'Tenant not found' }, 404)
  }
 
  // 用户列表
  const users = await c.env.DB.prepare(`
    SELECT id, email, name, role, status, last_login_at, created_at
    FROM users WHERE tenant_id = ?
    ORDER BY created_at DESC
  `).bind(tenantId).all()
 
  // 订阅信息
  const subscription = await c.env.DB.prepare(`
    SELECT * FROM subscriptions WHERE tenant_id = ? AND status IN ('active', 'trialing', 'past_due')
  `).bind(tenantId).first()
 
  // 本月用量
  const usage = await c.env.DB.prepare(`
    SELECT SUM(total_tokens) as tokens, SUM(request_count) as requests
    FROM usage_monthly
    WHERE tenant_id = ? AND month = strftime('%Y-%m', 'now')
  `).bind(tenantId).first()
 
  return c.json({
    tenant,
    users: users.results,
    subscription,
    usage: {
      tokens: usage?.tokens || 0,
      requests: usage?.requests || 0,
    },
  })
})
 
// 调整租户套餐
app.put('/:id/plan', requireAdminRole('super_admin', 'admin'), zValidator('json', z.object({
  plan: z.enum(['free', 'pro', 'enterprise']),
  reason: z.string(),
})), async (c) => {
  const tenantId = c.req.param('id')
  const adminId = c.get('adminId')
  const { plan, reason } = c.req.valid('json')
 
  const oldTenant = await c.env.DB.prepare(`
    SELECT plan FROM tenants WHERE id = ?
  `).bind(tenantId).first()
 
  // 更新套餐
  await c.env.DB.prepare(`
    UPDATE tenants SET plan = ?, updated_at = ? WHERE id = ?
  `).bind(plan, Date.now(), tenantId).run()
 
  // 清除配置缓存
  await c.env.TENANT_CONFIG.delete(`config:${tenantId}`)
 
  // 记录操作日志
  await logAdminAction({
    adminId,
    action: 'update',
    resourceType: 'tenant',
    resourceId: tenantId,
    details: {
      field: 'plan',
      oldValue: oldTenant.plan,
      newValue: plan,
      reason,
    },
    env: c.env,
  })
 
  return c.json({ success: true })
})
 
// 暂停/恢复租户
app.put('/:id/status', requireAdminRole('super_admin', 'admin'), zValidator('json', z.object({
  status: z.enum(['active', 'suspended']),
  reason: z.string(),
})), async (c) => {
  const tenantId = c.req.param('id')
  const adminId = c.get('adminId')
  const { status, reason } = c.req.valid('json')
 
  const oldTenant = await c.env.DB.prepare(`
    SELECT status FROM tenants WHERE id = ?
  `).bind(tenantId).first()
 
  await c.env.DB.prepare(`
    UPDATE tenants SET status = ?, updated_at = ? WHERE id = ?
  `).bind(status, Date.now(), tenantId).run()
 
  await c.env.TENANT_CONFIG.delete(`config:${tenantId}`)
 
  await logAdminAction({
    adminId,
    action: 'update',
    resourceType: 'tenant',
    resourceId: tenantId,
    details: {
      field: 'status',
      oldValue: oldTenant.status,
      newValue: status,
      reason,
    },
    env: c.env,
  })
 
  return c.json({ success: true })
})

// src/lib/admin-logger.ts
export async function logAdminAction(params: {
  adminId: string
  action: string
  resourceType: string
  resourceId?: string
  details?: Record<string, any>
  env: Env
}) {
  const { adminId, action, resourceType, resourceId, details, env } = params
 
  await env.DB.prepare(`
    INSERT INTO admin_audit_logs (id, admin_id, action, resource_type, resource_id, details, ip_address, user_agent, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).bind(
    crypto.randomUUID(),
    adminId,
    action,
    resourceType,
    resourceId || null,
    details ? JSON.stringify(details) : null,
    null,  // 从上下文获取
    null,
    Date.now()
  ).run()
}
 
// 查询审计日志
app.get('/audit-logs', requireAdminRole('super_admin'), async (c) => {
  const adminId = c.req.query('adminId')
  const resourceType = c.req.query('resourceType')
  const page = parseInt(c.req.query('page') || '1')
  const pageSize = parseInt(c.req.query('pageSize') || '50')
  const offset = (page - 1) * pageSize
 
  let query = `
    SELECT l.*, a.name as adminName, a.email as adminEmail
    FROM admin_audit_logs l
    JOIN admins a ON l.admin_id = a.id
    WHERE 1=1
  `
  const params: any[] = []
 
  if (adminId) {
    query += ` AND l.admin_id = ?`
    params.push(adminId)
  }
 
  if (resourceType) {
    query += ` AND l.resource_type = ?`
    params.push(resourceType)
  }
 
  query += ` ORDER BY l.created_at DESC LIMIT ? OFFSET ?`
  params.push(pageSize, offset)
 
  const logs = await c.env.DB.prepare(query).bind(...params).all()
 
  return c.json({
    logs: logs.results.map(l => ({
      id: l.id,
      admin: {
        id: l.admin_id,
        name: l.adminName,
        email: l.adminEmail,
      },
      action: l.action,
      resourceType: l.resource_type,
      resourceId: l.resource_id,
      details: l.details ? JSON.parse(l.details) : null,
      createdAt: l.created_at,
    })),
  })
})

// src/routes/admin/config.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { adminAuthMiddleware, requireAdminRole } from '../../middleware/admin-auth'
 
const app = new Hono()
app.use('*', adminAuthMiddleware)
app.use('*', requireAdminRole('super_admin'))
 
// 获取系统配置
app.get('/', async (c) => {
  const config = await c.env.SYSTEM_CONFIG.get('app_config', 'json')
 
  return c.json({
    config: config || {
      maintenanceMode: false,
      registrationEnabled: true,
      defaultPlan: 'free',
      maxUploadSize: 50 * 1024 * 1024,
    },
  })
})
 
// 更新系统配置
app.put('/', zValidator('json', z.object({
  maintenanceMode: z.boolean().optional(),
  registrationEnabled: z.boolean().optional(),
  defaultPlan: z.enum(['free', 'pro']).optional(),
  maxUploadSize: z.number().optional(),
})), async (c) => {
  const adminId = c.get('adminId')
  const updates = c.req.valid('json')
 
  const current = await c.env.SYSTEM_CONFIG.get('app_config', 'json') || {}
  const updated = { ...current, ...updates }
 
  await c.env.SYSTEM_CONFIG.put('app_config', JSON.stringify(updated))
 
  await logAdminAction({
    adminId,
    action: 'update',
    resourceType: 'config',
    details: { changes: updates },
    env: c.env,
  })
 
  return c.json({ success: true })
})