24.14-商业化指标

// src/lib/metrics/revenue.ts
export async function calculateRevenueMetrics(env: Env, month: string) {
  // MRR
  const mrr = await env.DB.prepare(`
    SELECT COALESCE(SUM(
      CASE
        WHEN plan = 'pro' THEN 2900
        WHEN plan = 'enterprise' THEN 9900
        ELSE 0
      END
    ), 0) as mrr
    FROM tenants
    WHERE status = 'active' AND plan != 'free'
  `).first()
 
  // 付费用户数
  const paidUsers = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM tenants
    WHERE status = 'active' AND plan != 'free'
  `).first()
 
  // ARPU
  const arpu = paidUsers.count > 0 ? mrr.mrr / paidUsers.count : 0
 
  // 上月 MRR（计算增长率）
  const lastMonth = getPreviousMonth(month)
  const lastMrr = await getMrrForMonth(env, lastMonth)
 
  const growth = lastMrr > 0 ? (mrr.mrr - lastMrr) / lastMrr : 0
 
  return {
    mrr: mrr.mrr,
    arr: mrr.mrr * 12,
    arpu,
    paidUsers: paidUsers.count,
    growth,
  }
}

// src/lib/metrics/customers.ts
export async function calculateCustomerMetrics(env: Env, month: string) {
  const monthStart = getMonthStart(month)
  const monthEnd = getMonthEnd(month)
 
  // 新客数
  const newCustomers = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM tenants
    WHERE created_at >= ? AND created_at < ?
  `).bind(monthStart, monthEnd).first()
 
  // 流失客户数
  const churnedCustomers = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM subscriptions
    WHERE status = 'canceled'
    AND updated_at >= ? AND updated_at < ?
  `).bind(monthStart, monthEnd).first()
 
  // 月初活跃客户数
  const activeAtStart = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM tenants
    WHERE created_at < ? AND status = 'active'
  `).bind(monthStart).first()
 
  // 流失率
  const churnRate = activeAtStart.count > 0
    ? churnedCustomers.count / activeAtStart.count
    : 0
 
  return {
    newCustomers: newCustomers.count,
    churnedCustomers: churnedCustomers.count,
    churnRate,
    activeCustomers: activeAtStart.count + newCustomers.count - churnedCustomers.count,
  }
}

// src/lib/metrics/usage.ts
export async function calculateUsageMetrics(env: Env, date: string) {
  // DAU
  const dau = await env.DB.prepare(`
    SELECT COUNT(DISTINCT user_id) as count
    FROM usage_records
    WHERE DATE(created_at / 1000, 'unixepoch') = ?
  `).bind(date).first()
 
  // MAU
  const month = date.substring(0, 7)
  const mau = await env.DB.prepare(`
    SELECT COUNT(DISTINCT user_id) as count
    FROM usage_records
    WHERE strftime('%Y-%m', created_at / 1000, 'unixepoch') = ?
  `).bind(month).first()
 
  // 日均调用次数
  const dailyCalls = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM usage_records
    WHERE DATE(created_at / 1000, 'unixepoch') = ?
  `).bind(date).first()
 
  const callsPerUser = dau.count > 0 ? dailyCalls.count / dau.count : 0
 
  // 功能渗透率
  const featureUsage = await env.DB.prepare(`
    SELECT
      feature,
      COUNT(DISTINCT user_id) as users
    FROM usage_records
    WHERE DATE(created_at / 1000, 'unixepoch') = ?
    GROUP BY feature
  `).bind(date).all()
 
  const penetration = featureUsage.results.map(f => ({
    feature: f.feature,
    users: f.users,
    rate: dau.count > 0 ? f.users / dau.count : 0,
  }))
 
  return {
    dau: dau.count,
    mau: mau.count,
    callsPerUser,
    featurePenetration: penetration,
  }
}

// src/cron/metrics.ts
export async function calculateDailyMetrics(env: Env) {
  const today = new Date()
  const dateStr = `${today.getFullYear()}-${String(today.getMonth() + 1).padStart(2, '0')}-${String(today.getDate()).padStart(2, '0')}`
  const month = dateStr.substring(0, 7)
 
  // 计算各项指标
  const revenue = await calculateRevenueMetrics(env, month)
  const customers = await calculateCustomerMetrics(env, month)
  const usage = await calculateUsageMetrics(env, dateStr)
 
  // 存储到 KV（用于快速查询）
  await env.METRICS_KV.put(`metrics:${dateStr}`, JSON.stringify({
    date: dateStr,
    revenue,
    customers,
    usage,
  }))
 
  console.log(`[Metrics] Daily metrics calculated for ${dateStr}`, {
    mrr: revenue.mrr,
    dau: usage.dau,
    churnRate: customers.churnRate,
  })
}
 
// 计算月度指标
export async function calculateMonthlyMetrics(env: Env) {
  const now = new Date()
  const lastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1)
  const month = `${lastMonth.getFullYear()}-${String(lastMonth.getMonth() + 1).padStart(2, '0')}`
 
  const revenue = await calculateRevenueMetrics(env, month)
  const customers = await calculateCustomerMetrics(env, month)
 
  // 计算 LTV
  const avgLifespan = await calculateAverageLifespan(env)
  const ltv = revenue.arpu * avgLifespan
 
  // 存储
  await env.METRICS_KV.put(`metrics:${month}:monthly`, JSON.stringify({
    month,
    revenue,
    customers,
    ltv,
  }))
 
  console.log(`[Metrics] Monthly metrics calculated for ${month}`, {
    mrr: revenue.mrr,
    arr: revenue.arr,
    ltv,
    churnRate: customers.churnRate,
  })
}
 
async function calculateAverageLifespan(env: Env): Promise<number> {
  // 计算已流失客户的平均生命周期（月）
  const result = await env.DB.prepare(`
    SELECT AVG(
      (updated_at - created_at) / (1000.0 * 60 * 60 * 24 * 30)
    ) as avgLifespan
    FROM tenants
    WHERE status = 'cancelled'
  `).first()
 
  return result?.avgLifespan || 12  // 默认 12 个月
}

// src/routes/admin/metrics.ts
import { Hono } from 'hono'
import { adminAuthMiddleware } from '../../middleware/admin-auth'
 
const app = new Hono()
app.use('*', adminAuthMiddleware)
 
// 获取今日指标
app.get('/today', async (c) => {
  const today = new Date()
  const dateStr = `${today.getFullYear()}-${String(today.getMonth() + 1).padStart(2, '0')}-${String(today.getDate()).padStart(2, '0')}`
 
  const metrics = await c.env.METRICS_KV.get(`metrics:${dateStr}`, 'json')
 
  if (!metrics) {
    // 实时计算
    const revenue = await calculateRevenueMetrics(c.env, dateStr.substring(0, 7))
    const customers = await calculateCustomerMetrics(c.env, dateStr.substring(0, 7))
    const usage = await calculateUsageMetrics(c.env, dateStr)
 
    return c.json({ date: dateStr, revenue, customers, usage })
  }
 
  return c.json(metrics)
})
 
// 获取月度指标
app.get('/monthly', async (c) => {
  const month = c.req.query('month') || getCurrentMonth()
 
  const metrics = await c.env.METRICS_KV.get(`metrics:${month}:monthly`, 'json')
 
  if (!metrics) {
    return c.json({ error: 'Metrics not found' }, 404)
  }
 
  return c.json(metrics)
})
 
// 获取趋势数据
app.get('/trend', async (c) => {
  const metric = c.req.query('metric') || 'mrr'
  const days = parseInt(c.req.query('days') || '90')
 
  const trend = []
  const now = new Date()
 
  for (let i = days - 1; i >= 0; i--) {
    const date = new Date(now.getTime() - i * 24 * 60 * 60 * 1000)
    const dateStr = `${date.getFullYear()}-${String(date.getMonth() + 1).padStart(2, '0')}-${String(date.getDate()).padStart(2, '0')}`
 
    const metrics = await c.env.METRICS_KV.get(`metrics:${dateStr}`, 'json')
    if (metrics) {
      trend.push({
        date: dateStr,
        value: getMetricValue(metrics, metric),
      })
    }
  }
 
  return c.json({ metric, trend })
})
 
function getMetricValue(metrics: any, metric: string): number {
  switch (metric) {
    case 'mrr': return metrics.revenue.mrr
    case 'arr': return metrics.revenue.arr
    case 'dau': return metrics.usage.dau
    case 'mau': return metrics.usage.mau
    case 'churnRate': return metrics.customers.churnRate
    default: return 0
  }
}

// src/lib/metrics/alerts.ts
export async function checkMetricAlerts(env: Env) {
  const today = new Date()
  const dateStr = `${today.getFullYear()}-${String(today.getMonth() + 1).padStart(2, '0')}-${String(today.getDate()).padStart(2, '0')}`
 
  const metrics = await env.METRICS_KV.get(`metrics:${dateStr}`, 'json')
  if (!metrics) return
 
  const alerts = []
 
  // 流失率告警
  if (metrics.customers.churnRate > 0.05) {
    alerts.push({
      type: 'high_churn',
      severity: 'warning',
      message: `流失率过高：${(metrics.customers.churnRate * 100).toFixed(1)}%`,
      value: metrics.customers.churnRate,
    })
  }
 
  // MRR 下降告警
  if (metrics.revenue.growth < -0.05) {
    alerts.push({
      type: 'mrr_decline',
      severity: 'critical',
      message: `MRR 下降：${(metrics.revenue.growth * 100).toFixed(1)}%`,
      value: metrics.revenue.growth,
    })
  }
 
  // DAU 下降告警
  const yesterdayMetrics = await env.METRICS_KV.get(
    `metrics:${getYesterday(dateStr)}`,
    'json'
  )
  if (yesterdayMetrics && metrics.usage.dau < yesterdayMetrics.usage.dau * 0.7) {
    alerts.push({
      type: 'dau_drop',
      severity: 'warning',
      message: `DAU 下降超过 30%`,
      value: metrics.usage.dau,
    })
  }
 
  // 发送告警
  if (alerts.length > 0) {
    await sendAlertNotification(alerts, env)
  }
 
  return alerts
}
 
async function sendAlertNotification(alerts: any[], env: Env) {
  const message = alerts.map(a => `[${a.severity.toUpperCase()}] ${a.message}`).join('\n')
 
  // 发送邮件
  await sendEmail({
    to: env.ALERT_EMAIL,
    subject: '指标告警',
    body: message,
  })
 
  // 发送 Slack
  await fetch(env.SLACK_WEBHOOK_URL, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      text: `🚨 指标告警\n${message}`,
    }),
  })
 
  console.error('[Alerts]', alerts)
}

// MRR 拆解
export async function analyzeMRR(env: Env, month: string) {
  const monthStart = getMonthStart(month)
  const monthEnd = getMonthEnd(month)
 
  // 新客 MRR
  const newMrr = await env.DB.prepare(`
    SELECT COALESCE(SUM(
      CASE
        WHEN plan = 'pro' THEN 2900
        WHEN plan = 'enterprise' THEN 9900
        ELSE 0
      END
    ), 0) as mrr
    FROM tenants
    WHERE created_at >= ? AND created_at < ?
    AND plan != 'free'
  `).bind(monthStart, monthEnd).first()
 
  // 流失 MRR
  const churnedMrr = await env.DB.prepare(`
    SELECT COALESCE(SUM(
      CASE
        WHEN (SELECT plan FROM tenants WHERE id = tenant_id) = 'pro' THEN 2900
        WHEN (SELECT plan FROM tenants WHERE id = tenant_id) = 'enterprise' THEN 9900
        ELSE 0
      END
    ), 0) as mrr
    FROM subscriptions
    WHERE status = 'canceled'
    AND updated_at >= ? AND updated_at < ?
  `).bind(monthStart, monthEnd).first()
 
  return {
    newMrr: newMrr.mrr,
    churnedMrr: churnedMrr.mrr,
    netNewMrr: newMrr.mrr - churnedMrr.mrr,
  }
}

// 客户健康度评分
export async function calculateHealthScore(tenantId: string, env: Env): Promise<number> {
  let score = 100
 
  // 最近活跃度（30天内有调用 +20）
  const lastUsage = await env.DB.prepare(`
    SELECT MAX(created_at) as lastUsed
    FROM usage_records
    WHERE tenant_id = ?
  `).bind(tenantId).first()
 
  if (!lastUsage?.lastUsed || Date.now() - lastUsage.lastUsed > 30 * 24 * 60 * 60 * 1000) {
    score -= 30
  }
 
  // 使用深度（使用 3+ 功能 +20）
  const featureCount = await env.DB.prepare(`
    SELECT COUNT(DISTINCT feature) as count
    FROM usage_records
    WHERE tenant_id = ? AND created_at > ?
  `).bind(tenantId, Date.now() - 30 * 24 * 60 * 60 * 1000).first()
 
  if (featureCount.count < 3) {
    score -= 20
  }
 
  // 支付状态（无逾期 +20）
  const hasOverdue = await env.DB.prepare(`
    SELECT id FROM invoices
    WHERE tenant_id = ? AND status = 'open' AND due_at < ?
  `).bind(tenantId, Date.now()).first()
 
  if (hasOverdue) {
    score -= 20
  }
 
  // 团队规模（3+ 用户 +20）
  const userCount = await env.DB.prepare(`
    SELECT COUNT(*) as count
    FROM users
    WHERE tenant_id = ? AND status = 'active'
  `).bind(tenantId).first()
 
  if (userCount.count < 3) {
    score -= 10
  }
 
  return Math.max(0, score)
}