SaaS Community Building 社区建设：我们在 6 个月内达到 5000 成员的实操手册

// 自动处理社区入驻
export const welcomeNewMember = createServerFn({ method: "POST" }).handler(
  async ({ data, context }: { data: { discordId: string; email: string } }) => {
    // 1. 根据产品等级分配角色
    const tier = await getSubscriptionTier(data.email)
    const role = tier === "pro" ? "Pro Member" : "Community Member"

    // 2. 通过 webhook 发送欢迎消息
    await fetch(env.DISCORD_WEBHOOK, {
      method: "POST",
      body: JSON.stringify({
        content: `Welcome <@${data.discordId}>! 🎉`,
        embeds: [{
          title: "Getting Started",
          description: [
            "1. Read #rules",
            "2. Introduce yourself in #introductions",
            "3. Check out #getting-started for product help",
          ].join("\n"),
        }],
      }),
    })

    // 3. 在数据库中记录
    await context.env.DB.prepare(`
      INSERT INTO community_members
        (user_id, discord_id, role, joined_at)
      VALUES (?, ?, ?, ?)
    `).bind(data.email, data.discordId, role, Date.now()).run()

    return { welcomed: true }
  }
)

// 自动化的每日话题提示
const dailyPrompts = {
  monday: "What did you build this weekend? Share in #show-and-tell!",
  tuesday: "Tip Tuesday: Share one productivity tip that changed your workflow.",
  wednesday: "Feedback Wednesday: What's one thing you'd improve in our product?",
  thursday: "Throwback: Share a project you're proud of.",
  friday: "Free talk Friday! What are your weekend plans?",
)

// 追踪社区参与度并奖励互动行为
export const updateMemberScore = createServerFn({ method: "POST" }).handler(
  async ({ data, context }: { data: {
    userId: string
    action: "message" | "help" | "reaction" | "thread"
  }}) => {
    const points = { message: 1, reaction: 1, help: 5, thread: 3 }

    await context.env.DB.prepare(`
      UPDATE community_members
      SET score = score + ?, last_active = ?
      WHERE user_id = ?
    `).bind(points[data.action], Date.now(), data.userId).run()

    // 检查是否符合角色晋升条件
    const member = await context.env.DB.prepare(`
      SELECT score FROM community_members WHERE user_id = ?
    `).bind(data.userId).first()

    if (member.score >= 100 && member.role === "member") {
      await promoteToHelper(data.userId)
    }
  }
)

export const getCommunityHealth = createServerFn({ method: "GET" }).handler(
  async ({}, { context }) => {
    const [members, engagement, retention] = await Promise.all([
      // 成员增长
      context.env.DB.prepare(`
        SELECT DATE(joined_at) as date, COUNT(*) as new_members
        FROM community_members
        WHERE joined_at > datetime('now', '-30 days')
        GROUP BY DATE(joined_at)
      `).all(),

      // 互动率
      context.env.DB.prepare(`
        SELECT
          COUNT(*) as total_members,
          SUM(CASE WHEN last_active > datetime('now', '-7 days') THEN 1 ELSE 0 END) as active_weekly,
          SUM(CASE WHEN score > 10 THEN 1 ELSE 0 END) as engaged
        FROM community_members
      `).first(),

      // 比较社区成员与非社区成员的留存情况
      context.env.DB.prepare(`
        SELECT
          c.user_id IS NOT NULL as in_community,
          COUNT(*) as users,
          AVG(CASE WHEN s.status = 'active' THEN 1 ELSE 0 END) as retention_rate
        FROM users u
        LEFT JOIN subscriptions s ON s.user_id = u.id
        LEFT JOIN community_members c ON c.user_id = u.email
        WHERE u.created_at > datetime('now', '-90 days')
        GROUP BY in_community
      `).all(),
    ])

    return { members, engagement, retention }
  }
)