// app/lib/content-calendar/generator.ts
export interface ClusterSlot {
  cluster: 'comparison' | 'how-to' | 'teardown' | 'tradeoff';
  topic: string;
  primaryKeyword: string;
  intent: 'commercial' | 'informational' | 'investigative' | 'decision';
}

export interface CalendarInput {
  startDate: string;            // ISO 日期，例如 '2026-04-06'
  weeks: number;                // 通常为 12
  postsPerWeek: number;         // 独立创始人用 1，2 人团队用 2
  clusters: ClusterSlot[];      // 4 主题簇比例下的 12 个坑位
}

export interface CalendarEntry {
  week: number;
  publishDate: string;
  cluster: ClusterSlot['cluster'];
  topic: string;
  primaryKeyword: string;
  intent: ClusterSlot['intent'];
  wordTarget: number;
}

export function generateCalendar(input: CalendarInput): CalendarEntry[] {
  const start = new Date(input.startDate);
  const slotsPerWeek = input.postsPerWeek;
  const totalSlots = input.weeks * slotsPerWeek;
  const entries: CalendarEntry[] = [];

  for (let i = 0; i < totalSlots; i++) {
    const slot = input.clusters[i % input.clusters.length];
    const weekOffset = Math.floor(i / slotsPerWeek);
    const weekDayOffset = (i % slotsPerWeek) * 4; // 周一、周五节奏
    const publish = new Date(start);
    publish.setDate(start.getDate() + weekOffset * 7 + weekDayOffset);

    entries.push({
      week: weekOffset + 1,
      publishDate: publish.toISOString().slice(0, 10),
      cluster: slot.cluster,
      topic: slot.topic,
      primaryKeyword: slot.primaryKeyword,
      intent: slot.intent,
      wordTarget: slot.cluster === 'teardown' ? 2400 : 1900,
    });
  }

  return entries;
}

// 示例：每周 1 篇，4 主题簇轮换的 12 周日历
const calendar = generateCalendar({
  startDate: '2026-04-06',
  weeks: 12,
  postsPerWeek: 1,
  clusters: [
    { cluster: 'comparison', topic: 'TanStack Ship vs ShipFast', primaryKeyword: 'tanstack ship vs shipfast', intent: 'commercial' },
    { cluster: 'comparison', topic: 'TanStack Ship vs Makerkit', primaryKeyword: 'tanstack ship vs makerkit', intent: 'commercial' },
    { cluster: 'comparison', topic: 'TanStack Ship vs Next.js Boilerplate', primaryKeyword: 'next js boilerplate', intent: 'commercial' },
    { cluster: 'comparison', topic: 'TanStack Ship vs create-t3-app', primaryKeyword: 'tanstack ship vs create t3 app', intent: 'commercial' },
    { cluster: 'how-to',     topic: 'Deploy TanStack Start on Cloudflare', primaryKeyword: 'deploy tanstack start cloudflare', intent: 'informational' },
    { cluster: 'how-to',     topic: 'Add Stripe to TanStack Start', primaryKeyword: 'stripe tanstack start', intent: 'informational' },
    { cluster: 'how-to',     topic: 'Add i18n to TanStack Start', primaryKeyword: 'tanstack start i18n', intent: 'informational' },
    { cluster: 'how-to',     topic: 'Migrate from Next.js to TanStack Start', primaryKeyword: 'next js to tanstack start', intent: 'informational' },
    { cluster: 'teardown',   topic: 'A teardown of ShipFast auth', primaryKeyword: 'shipfast auth review', intent: 'investigative' },
    { cluster: 'teardown',   topic: 'A teardown of Makerkit billing', primaryKeyword: 'makerkit billing review', intent: 'investigative' },
    { cluster: 'tradeoff',   topic: 'When NOT to use a SaaS boilerplate', primaryKeyword: 'when not to use saas boilerplate', intent: 'decision' },
    { cluster: 'tradeoff',   topic: 'Solo founder vs 2-person team cadence', primaryKeyword: 'solo founder content cadence', intent: 'decision' },
  ],
});
// -> 12 条记录，日期分别为 4 月 6 日、4 月 10 日、4 月 17 日、4 月 24 日、5 月 1 日、5 月 8 日……

-- queries/content_calendar_retrofit.sql
-- 读取哪些日历坑位产生了自然流量和辅助转化。
WITH calendar AS (
  SELECT
    slug,
    publish_date,
    cluster,
    primary_keyword
  FROM content_calendar
  WHERE publish_date BETWEEN '2026-04-01' AND '2026-06-30'
),
gsc AS (
  SELECT
    page,
    query,
    clicks,
    impressions,
    position
  FROM gsc_export
  WHERE page LIKE 'https://tanstackship.com/blog/%'
    AND date BETWEEN '2026-06-01' AND '2026-06-30'
),
conv AS (
  SELECT
    page,
    COUNT(*) AS signups
    FROM events
    WHERE event = 'pricing_view'
      AND utm_content LIKE 'blog/%'
      AND occurred_at BETWEEN '2026-04-01' AND '2026-06-30'
    GROUP BY page
)
SELECT
  c.cluster,
  COUNT(DISTINCT c.slug) AS posts,
  ROUND(AVG(g.position), 1) AS avg_position,
  SUM(g.clicks) AS total_clicks,
  SUM(COALESCE(conv.signups, 0)) AS assisted_signups,
  ROUND(100.0 * SUM(COALESCE(conv.signups, 0)) / NULLIF(SUM(g.clicks), 0), 2) AS signup_rate_pct
FROM calendar c
LEFT JOIN gsc g ON g.page LIKE '%' || c.slug
LEFT JOIN conv ON conv.page LIKE '%' || c.slug
GROUP BY c.cluster
ORDER BY assisted_signups DESC;

-- 本次发布的 Q2 2026 实际数据：
-- 对比        篇数 4   平均排名 4.2  点击 11,840  注册 312  转化率 2.63%
-- 教程        篇数 4   平均排名 7.8  点击  6,210  注册  98  转化率 1.58%
-- 拆解        篇数 2   平均排名 9.1  点击  2,140  注册  44  转化率 2.06%
-- 取舍        篇数 2   平均排名 6.4  点击  3,520  注册  72  转化率 2.05%
-- 合计        篇数 12  点击 23,710          注册 526