// app/lib/waitlist-analytics/k-factor.ts
export interface KFactorInput {
  userId: string;
  invitesSent: number;        // 窗口内该用户发送的邀请数
  inviteePaying: number;       // 窗口内转为付费的邀请数
  windowDays: number;         // 通常为30
}

export interface KFactorResult {
  perUser: number;
  cohortK: number;            // 活跃用户均值
  projected: { d30: number; d60: number; d90: number };
}

export function calcKFactor(
  inputs: KFactorInput[]
): KFactorResult {
  const active = inputs.filter((u) => u.invitesSent > 0);
  if (active.length === 0) {
    return { perUser: 0, cohortK: 0, projected: { d30: 1, d60: 1, d90: 1 } };
  }

  const perUserK = active.map(
    (u) => u.inviteePaying / Math.max(u.invitesSent, 1)
  );
  const cohortK =
    perUserK.reduce((acc, k) => acc + k, 0) / perUserK.length;

  const cycleDays = 7; // 8次发布中7次的实测中位周期
  const dailyGrowth = 1 + cohortK;
  const cyclesIn = (d: number) => Math.floor(d / cycleDays);

  return {
    perUser: perUserK.reduce((a, b) => a + b, 0) / perUserK.length,
    cohortK,
    projected: {
      d30: Math.pow(dailyGrowth, cyclesIn(30)),
      d60: Math.pow(dailyGrowth, cyclesIn(60)),
      d90: Math.pow(dailyGrowth, cyclesIn(90)),
    },
  };
}

// 示例：1200名活跃用户，邀请到付费转化率0.46，7天周期
// -> cohortK 0.46，预测d60 ≈ 1.46^8 ≈ 7.7×，60天增长约7.7倍

-- schema/waitlist_events.sql
-- 每个Waitlist事件都附加来源归因，这样我们无需ETL步骤就能读取
-- 11指标表中的任意指标。
CREATE TABLE waitlist_events (
  id INTEGER PRIMARY KEY,
  user_id TEXT NOT NULL,                -- 匿名预注册事件为null
  event TEXT NOT NULL,                  -- 'view_landing','join','verify_email',
                                        -- 'invite_sent','invite_opened',
                                        -- 'invite_converted','position_check',
                                        -- 'return_visit','reward_redeemed',
                                        -- 'launch_invite','launch_paid_d0',
                                        -- 'launch_paid_d1','launch_paid_d7'
  occurred_at INTEGER NOT NULL,         -- unix秒
  source TEXT NOT NULL,                 -- 'twitter','producthunt','hackernews',
                                        -- 'direct','referral:<code>','paid:google',
                                        -- 'paid:meta','email:<list>'
  campaign TEXT,                        -- 可选UTM campaign
  referral_code TEXT,                   -- 事件是否附带推荐码
  referrer_user_id TEXT,                -- 邀请者ID（适用时）
  position INTEGER,                     -- 事件发生时的当前Waitlist排名
  payload TEXT,                         -- JSON: variant, device, country等
  session_id TEXT                       -- 将事件绑定到同一浏览器会话
);
CREATE INDEX idx_events_user ON waitlist_events(user_id, occurred_at);
CREATE INDEX idx_events_source ON waitlist_events(source, event);
CREATE INDEX idx_events_referrer ON waitlist_events(referrer_user_id);
CREATE INDEX idx_events_session ON waitlist_events(session_id);

-- queries/launch_conversion_by_source.sql
-- 第11个指标：按注册时来源归因的D0/D1/D7付费转化
WITH signups AS (
  SELECT user_id, source
  FROM waitlist_events
  WHERE event = 'join'
),
launch_paid AS (
  SELECT
    user_id,
    MAX(CASE WHEN event = 'launch_paid_d0' THEN 1 ELSE 0 END) AS paid_d0,
    MAX(CASE WHEN event = 'launch_paid_d1' THEN 1 ELSE 0 END) AS paid_d1,
    MAX(CASE WHEN event = 'launch_paid_d7' THEN 1 ELSE 0 END) AS paid_d7
  FROM waitlist_events
  WHERE event IN ('launch_paid_d0','launch_paid_d1','launch_paid_d7')
  GROUP BY user_id
)
SELECT
  s.source,
  COUNT(*) AS signups,
  SUM(COALESCE(l.paid_d0, 0)) AS paid_d0,
  SUM(COALESCE(l.paid_d1, 0)) AS paid_d1,
  SUM(COALESCE(l.paid_d7, 0)) AS paid_d7,
  ROUND(100.0 * SUM(COALESCE(l.paid_d7, 0)) / COUNT(*), 2) AS d7_cr_pct
FROM signups s
LEFT JOIN launch_paid l USING (user_id)
GROUP BY s.source
ORDER BY paid_d7 DESC;

-- 8次发布的示例输出，2026年5月：
-- referral:<code>      signups 1,840  d7_cr 22.4%  <- 在这里加倍投入
-- producthunt          signups   920  d7_cr  9.6%
-- twitter              signups   612  d7_cr  6.1%
-- paid:google          signups   410  d7_cr  4.4%  <- CAC紧张，关注回本情况
-- direct               signups   308  d7_cr  3.8%
-- email:<list>         signups   220  d7_cr  2.2%
-- hackernews           signups   140  d7_cr  1.4%  <- 峰值后消失