-- Weekly cohort retention: % of each signup cohort active N weeks later
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', MIN(timestamp)) AS cohort_week
  FROM events
  WHERE event_name = 'signup_completed'
    AND timestamp >= NOW() - INTERVAL '12 weeks'
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('week', timestamp) AS activity_week
  FROM events
  WHERE event_name IN (
    'project_created', 'task_completed', 'message_sent',
    'report_generated', 'file_uploaded', 'comment_added'
  )
  AND timestamp >= NOW() - INTERVAL '12 weeks'
),
retention AS (
  SELECT
    c.cohort_week,
    FLOOR(EXTRACT(EPOCH FROM (a.activity_week - c.cohort_week)) / (7 * 86400))::int AS week_number,
    COUNT(DISTINCT a.user_id) AS active_users,
    (SELECT COUNT(DISTINCT c2.user_id) FROM cohorts c2
     WHERE c2.cohort_week = c.cohort_week) AS cohort_size
  FROM cohorts c
  LEFT JOIN activity a ON c.user_id = a.user_id AND a.activity_week >= c.cohort_week
  GROUP BY c.cohort_week, week_number
)
SELECT
  cohort_week,
  week_number,
  cohort_size,
  active_users,
  ROUND(active_users::numeric / NULLIF(cohort_size, 0) * 100, 1) AS retention_pct
FROM retention
WHERE week_number BETWEEN 0 AND 8
ORDER BY cohort_week, week_number;

// src/app/api/retention/route.ts
import { db } from "@/lib/db";
import { NextRequest, NextResponse } from "next/server";

export async function GET(request: NextRequest) {
  const params = request.nextUrl.searchParams;
  const granularity = params.get("granularity") ?? "week";
  const periods = Math.min(parseInt(params.get("periods") ?? "8"), 12);
  const trunc = granularity === "week" ? "'week'" : "'month'";
  const lookback = granularity === "week" ? `${periods + 4} weeks` : `${periods + 2} months`;
  const periodCalc = granularity === "week"
    ? "FLOOR(EXTRACT(EPOCH FROM (a.ap - c.cp)) / 604800)::int"
    : "EXTRACT(MONTH FROM AGE(a.ap, c.cp))::int";

  const rows = await db.query(`
    WITH cohorts AS (
      SELECT user_id, DATE_TRUNC(${trunc}, MIN(timestamp)) AS cp
      FROM events WHERE event_name = 'signup_completed'
        AND timestamp >= NOW() - INTERVAL '${lookback}'
      GROUP BY user_id
    ),
    activity AS (
      SELECT DISTINCT user_id, DATE_TRUNC(${trunc}, timestamp) AS ap
      FROM events
      WHERE event_name IN ('task_completed','message_sent','report_generated','file_uploaded')
        AND timestamp >= NOW() - INTERVAL '${lookback}'
    ),
    ret AS (
      SELECT c.cp, ${periodCalc} AS pn,
        COUNT(DISTINCT a.user_id) AS active,
        (SELECT COUNT(DISTINCT c2.user_id) FROM cohorts c2 WHERE c2.cp = c.cp) AS size
      FROM cohorts c LEFT JOIN activity a ON c.user_id = a.user_id AND a.ap >= c.cp
      GROUP BY c.cp, pn
    )
    SELECT cp AS cohort_period, pn AS period_number, size AS cohort_size,
      active AS active_users,
      ROUND(active::numeric / NULLIF(size, 0) * 100, 1) AS retention_pct
    FROM ret WHERE pn BETWEEN 0 AND $1 ORDER BY cp, pn
  `, [periods]);

  // Group into heatmap-friendly structure
  const cohorts = new Map<string, { cohort_period: string; cohort_size: number; periods: Record<number, number> }>();
  for (const row of rows) {
    if (!cohorts.has(row.cohort_period)) {
      cohorts.set(row.cohort_period, { cohort_period: row.cohort_period, cohort_size: row.cohort_size, periods: {} });
    }
    cohorts.get(row.cohort_period)!.periods[row.period_number] = row.retention_pct;
  }

  return NextResponse.json({ granularity, total_periods: periods, cohorts: Array.from(cohorts.values()) });
}

// src/lib/retention/churn-score.ts
import { db } from "@/lib/db";

interface ChurnSignal {
  name: string;
  weight: number;
  compute: (userId: string) => Promise<number>; // 0.0 (healthy) to 1.0 (at risk)
}

const signals: ChurnSignal[] = [
  {
    name: "days_since_last_login",
    weight: 30,
    async compute(userId) {
      const r = await db.query<{ days: number }>(`
        SELECT EXTRACT(DAY FROM NOW() - MAX(timestamp))::int AS days
        FROM events WHERE user_id = $1
          AND event_name IN ('page_viewed', 'feature_used', 'session_started')
      `, [userId]);
      return Math.min((r[0]?.days ?? 999) / 14, 1.0);
    },
  },
  {
    name: "weekly_active_days_decline",
    weight: 20,
    async compute(userId) {
      const r = await db.query<{ this_week: number; avg_4w: number }>(`
        WITH weekly AS (
          SELECT DATE_TRUNC('week', timestamp) AS w, COUNT(DISTINCT DATE_TRUNC('day', timestamp)) AS d
          FROM events WHERE user_id = $1 AND timestamp >= NOW() - INTERVAL '5 weeks'
            AND event_name IN ('page_viewed', 'feature_used') GROUP BY w ORDER BY w DESC
        )
        SELECT COALESCE((SELECT d FROM weekly LIMIT 1), 0) AS this_week,
               COALESCE((SELECT AVG(d) FROM weekly OFFSET 1), 0) AS avg_4w
      `, [userId]);
      if (r[0].avg_4w === 0) return r[0].this_week === 0 ? 1.0 : 0.0;
      return Math.max(0, Math.min(1 - r[0].this_week / r[0].avg_4w, 1.0));
    },
  },
  {
    name: "core_actions_last_7d",
    weight: 20,
    async compute(userId) {
      const r = await db.query<{ count: number }>(`
        SELECT COUNT(*) AS count FROM events WHERE user_id = $1
          AND event_name IN ('project_created','task_completed','message_sent','report_generated')
          AND timestamp >= NOW() - INTERVAL '7 days'
      `, [userId]);
      return Math.max(0, 1.0 - (r[0]?.count ?? 0) / 10);
    },
  },
  {
    name: "feature_breadth",
    weight: 15,
    async compute(userId) {
      const r = await db.query<{ recent: number; peak: number }>(`
        SELECT
          (SELECT COUNT(DISTINCT properties->>'feature') FROM events
           WHERE user_id = $1 AND event_name = 'feature_used'
             AND timestamp >= NOW() - INTERVAL '30 days') AS recent,
          (SELECT COUNT(DISTINCT properties->>'feature') FROM events
           WHERE user_id = $1 AND event_name = 'feature_used') AS peak
      `, [userId]);
      return r[0].peak === 0 ? 1.0 : Math.max(0, 1.0 - r[0].recent / r[0].peak);
    },
  },
  {
    name: "has_team_members",
    weight: 15,
    async compute(userId) {
      const r = await db.query<{ ct: number }>(`
        SELECT COUNT(*) AS ct FROM team_memberships
        WHERE team_id IN (SELECT team_id FROM team_memberships WHERE user_id = $1)
          AND user_id != $1
      `, [userId]);
      return r[0].ct > 0 ? 0.0 : 1.0;
    },
  },
];

export async function computeChurnScore(userId: string) {
  const factors: Record<string, { raw: number; weighted: number }> = {};
  let total = 0;
  for (const s of signals) {
    const raw = await s.compute(userId);
    const weighted = Math.round(raw * s.weight);
    factors[s.name] = { raw: Math.round(raw * 100) / 100, weighted };
    total += weighted;
  }
  const score = Math.min(100, total);
  const riskLevel = score >= 60 ? "critical" : score >= 30 ? "at_risk" : "healthy";
  return { userId, score, riskLevel, factors };
}

// src/app/api/cron/churn-scores/route.ts — daily scoring cron
import { NextResponse } from "next/server";
import { db } from "@/lib/db";
import { computeChurnScore } from "@/lib/retention/churn-score";

export async function POST(request: Request) {
  if (request.headers.get("authorization") !== `Bearer ${process.env.CRON_SECRET}`) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const users = await db.query<{ user_id: string }>(`
    SELECT DISTINCT user_id FROM events
    WHERE timestamp >= NOW() - INTERVAL '90 days'
  `);

  let scored = 0, riskChanges = 0;
  for (const { user_id } of users) {
    const prev = await db.query<{ risk_level: string }>(
      `SELECT risk_level FROM user_engagement_scores WHERE user_id = $1`, [user_id]
    );
    const result = await computeChurnScore(user_id);

    await db.query(`
      INSERT INTO user_engagement_scores (user_id, score, risk_level, factors, score_updated_at)
      VALUES ($1, $2, $3, $4, NOW())
      ON CONFLICT (user_id) DO UPDATE SET
        score = $2, risk_level = $3, factors = $4, score_updated_at = NOW()
    `, [user_id, result.score, result.riskLevel, JSON.stringify(result.factors)]);

    if (prev[0] && prev[0].risk_level !== result.riskLevel) {
      riskChanges++;
      if (result.riskLevel !== "healthy") {
        await db.query(`
          INSERT INTO notification_triggers (user_id, trigger_type, channel, payload, scheduled_for, status)
          VALUES ($1, 'inactivity', 'email', $2, NOW() + INTERVAL '1 hour', 'pending')
        `, [user_id, JSON.stringify({ risk_level: result.riskLevel, template: result.riskLevel === "critical" ? "urgent" : "gentle" })]);
      }
    }
    scored++;
  }
  return NextResponse.json({ scored, riskChanges });
}

-- Gamification schema
CREATE TABLE user_streaks (
  user_id           UUID PRIMARY KEY REFERENCES users(id),
  current_streak    INT NOT NULL DEFAULT 0,
  longest_streak    INT NOT NULL DEFAULT 0,
  last_activity_date DATE,
  streak_updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE achievements (
  key               VARCHAR(100) PRIMARY KEY,
  display_name      VARCHAR(255) NOT NULL,
  description       TEXT NOT NULL,
  icon              VARCHAR(100) NOT NULL,
  category          VARCHAR(50) NOT NULL,   -- milestone, streak, social, exploration
  threshold         INT NOT NULL,
  threshold_metric  VARCHAR(100) NOT NULL
);

CREATE TABLE user_achievements (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           UUID NOT NULL REFERENCES users(id),
  achievement_key   VARCHAR(100) NOT NULL REFERENCES achievements(key),
  unlocked_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata          JSONB DEFAULT '{}',
  UNIQUE(user_id, achievement_key)
);

INSERT INTO achievements (key, display_name, description, icon, category, threshold, threshold_metric) VALUES
  ('first_project',   'Getting Started',  'Created your first project',       'rocket', 'milestone', 1,    'projects_created'),
  ('task_master_100', 'Task Champion',     'Completed 100 tasks',              'trophy', 'milestone', 100,  'tasks_completed'),
  ('team_player',     'Team Player',       'Invited your first team member',   'users',  'social',    1,    'invites_sent'),
  ('streak_7',        'On a Roll',         '7-day activity streak',            'flame',  'streak',    7,    'current_streak'),
  ('streak_30',       'Unstoppable',       '30-day activity streak',           'fire',   'streak',    30,   'current_streak'),
  ('explorer',        'Explorer',          'Used 5 different features',        'compass','exploration',5,   'features_used')
ON CONFLICT (key) DO NOTHING;

// src/lib/retention/streaks.ts
import { db } from "@/lib/db";

const GRACE_DAYS = [0, 6]; // Sunday, Saturday don't break B2B streaks

function getEffectiveGap(lastDate: Date, today: Date): number {
  let gap = 0;
  const d = new Date(lastDate);
  d.setDate(d.getDate() + 1);
  while (d < today) {
    if (!GRACE_DAYS.includes(d.getDay())) gap++;
    d.setDate(d.getDate() + 1);
  }
  return gap;
}

export async function updateStreak(userId: string) {
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const todayStr = today.toISOString().split("T")[0];

  const existing = await db.query<{ current_streak: number; longest_streak: number; last_activity_date: string | null }>(
    `SELECT current_streak, longest_streak, last_activity_date FROM user_streaks WHERE user_id = $1`, [userId]
  );

  if (!existing.length) {
    await db.query(`INSERT INTO user_streaks (user_id, current_streak, longest_streak, last_activity_date) VALUES ($1, 1, 1, $2)`, [userId, todayStr]);
    return { currentStreak: 1, longestStreak: 1, broken: false };
  }

  const { current_streak, longest_streak, last_activity_date } = existing[0];
  if (last_activity_date === todayStr) return { currentStreak: current_streak, longestStreak: longest_streak, broken: false };

  const gap = getEffectiveGap(new Date(last_activity_date + "T00:00:00"), today);
  const newStreak = gap <= 1 ? current_streak + 1 : 1;
  const newLongest = Math.max(longest_streak, newStreak);

  await db.query(`UPDATE user_streaks SET current_streak = $2, longest_streak = $3, last_activity_date = $4, streak_updated_at = NOW() WHERE user_id = $1`,
    [userId, newStreak, newLongest, todayStr]);

  return { currentStreak: newStreak, longestStreak: newLongest, broken: gap > 1 && current_streak > 1 };
}

// src/lib/retention/achievements.ts
import { db } from "@/lib/db";

const metricResolvers: Record<string, (uid: string) => Promise<number>> = {
  projects_created: async (uid) => (await db.query<{ c: number }>(`SELECT COUNT(*) AS c FROM events WHERE user_id=$1 AND event_name='project_created'`, [uid]))[0]?.c ?? 0,
  tasks_completed: async (uid) => (await db.query<{ c: number }>(`SELECT COUNT(*) AS c FROM events WHERE user_id=$1 AND event_name='task_completed'`, [uid]))[0]?.c ?? 0,
  invites_sent: async (uid) => (await db.query<{ c: number }>(`SELECT COUNT(*) AS c FROM events WHERE user_id=$1 AND event_name='invite_sent'`, [uid]))[0]?.c ?? 0,
  features_used: async (uid) => (await db.query<{ c: number }>(`SELECT COUNT(DISTINCT properties->>'feature') AS c FROM events WHERE user_id=$1 AND event_name='feature_used'`, [uid]))[0]?.c ?? 0,
  current_streak: async (uid) => (await db.query<{ s: number }>(`SELECT current_streak AS s FROM user_streaks WHERE user_id=$1`, [uid]))[0]?.s ?? 0,
};

export async function checkAchievements(userId: string) {
  const unclaimed = await db.query<{ key: string; display_name: string; description: string; icon: string; threshold: number; threshold_metric: string }>(`
    SELECT a.* FROM achievements a
    WHERE NOT EXISTS (SELECT 1 FROM user_achievements ua WHERE ua.user_id = $1 AND ua.achievement_key = a.key)
  `, [userId]);

  const unlocked: Array<{ key: string; displayName: string; description: string }> = [];
  for (const a of unclaimed) {
    const resolver = metricResolvers[a.threshold_metric];
    if (!resolver) continue;
    if (await resolver(userId) >= a.threshold) {
      await db.query(`INSERT INTO user_achievements (user_id, achievement_key, metadata) VALUES ($1,$2,$3) ON CONFLICT DO NOTHING`,
        [userId, a.key, JSON.stringify({})]);
      unlocked.push({ key: a.key, displayName: a.display_name, description: a.description });
    }
  }
  return unlocked;
}

// src/lib/retention/re-engagement.ts
import { db } from "@/lib/db";

const SEQUENCE = [
  { delayDays: 2, template: "re_engagement_day2", subject: (n: string) => `${n}, here's what you can do next` },
  { delayDays: 5, template: "re_engagement_day5", subject: (n: string) => `${n}, your team has been active` },
  { delayDays: 10, template: "re_engagement_day10", subject: () => `We'd love your feedback` },
];

export async function processReEngagementTriggers() {
  // Find newly at-risk users who haven't been contacted recently
  const atRisk = await db.query<{ user_id: string; email: string; name: string; risk_level: string }>(`
    SELECT ues.user_id, u.email, u.name, ues.risk_level
    FROM user_engagement_scores ues
    JOIN users u ON ues.user_id = u.id
    WHERE ues.risk_level IN ('at_risk', 'critical')
      AND ues.score_updated_at >= NOW() - INTERVAL '24 hours'
      AND NOT EXISTS (
        SELECT 1 FROM notification_triggers nt
        WHERE nt.user_id = ues.user_id AND nt.trigger_type = 'inactivity'
          AND nt.status IN ('pending', 'sent') AND nt.created_at >= NOW() - INTERVAL '14 days'
      )
  `);

  let enqueued = 0;
  for (const user of atRisk) {
    for (const step of SEQUENCE) {
      await db.query(`
        INSERT INTO notification_triggers (user_id, trigger_type, channel, payload, scheduled_for, status)
        VALUES ($1, 'inactivity', 'email', $2, $3, 'pending')
      `, [user.user_id,
          JSON.stringify({ template: step.template, subject: step.subject(user.name), email: user.email }),
          new Date(Date.now() + step.delayDays * 86400000).toISOString()]);
      enqueued++;
    }
  }

  // Cancel pending emails for returned users
  const cancelled = await db.query(`
    UPDATE notification_triggers nt SET status = 'cancelled'
    FROM user_engagement_scores ues
    WHERE nt.user_id = ues.user_id AND ues.risk_level = 'healthy'
      AND nt.trigger_type = 'inactivity' AND nt.status = 'pending'
  `);

  return { enqueued, cancelled: cancelled.length };
}

// src/lib/retention/digest.ts
import { db } from "@/lib/db";
import { render } from "@react-email/render";
import { sendEmail } from "@/lib/email";

export async function generateAndSendDigests() {
  const users = await db.query<{ user_id: string; email: string; name: string }>(`
    SELECT u.id AS user_id, u.email, u.name FROM users u
    JOIN user_preferences up ON u.id = up.user_id
    WHERE up.digest_enabled = true
      AND EXISTS (SELECT 1 FROM events WHERE user_id = u.id AND timestamp >= NOW() - INTERVAL '30 days')
  `);

  let sent = 0;
  for (const user of users) {
    const activities = await db.query<{ event_name: string; count: number }>(`
      SELECT event_name, COUNT(*) AS count FROM events
      WHERE user_id IN (
        SELECT user_id FROM team_memberships
        WHERE team_id IN (SELECT team_id FROM team_memberships WHERE user_id = $1)
      )
      AND event_name IN ('task_completed','comment_added','project_created','milestone_reached')
      AND timestamp >= NOW() - INTERVAL '7 days'
      GROUP BY event_name ORDER BY count DESC
    `, [user.user_id]);

    // Skip if no meaningful activity
    if (activities.reduce((sum, a) => sum + a.count, 0) < 3) continue;

    const streak = await db.query<{ current_streak: number }>(
      `SELECT current_streak FROM user_streaks WHERE user_id = $1`, [user.user_id]
    );

    await sendEmail({
      to: user.email,
      subject: `Your weekly summary — ${activities.reduce((s, a) => s + a.count, 0)} activities`,
      // In production, render a React Email component here
      html: buildDigestHtml(user.name, activities, streak[0]?.current_streak ?? 0),
    });

    await db.query(`
      INSERT INTO notification_triggers (user_id, trigger_type, channel, scheduled_for, sent_at, status)
      VALUES ($1, 'digest', 'email', NOW(), NOW(), 'sent')
    `, [user.user_id]);
    sent++;
  }
  return { sent };
}

function buildDigestHtml(name: string, activities: Array<{ event_name: string; count: number }>, streak: number): string {
  const rows = activities.map(a => `<tr><td>${a.event_name.replace(/_/g, " ")}</td><td>${a.count}</td></tr>`).join("");
  return `
    <h2>Hi ${name}, here's your week</h2>
    <table>${rows}</table>
    ${streak > 0 ? `<p>Current streak: ${streak} days</p>` : ""}
    <a href="${process.env.NEXT_PUBLIC_APP_URL}/dashboard">Open Dashboard</a>
  `;
}

// src/lib/retention/feature-adoption.ts
import { db } from "@/lib/db";

export const FEATURE_REGISTRY = [
  { featureKey: "task_management", displayName: "Task Management", coreEvent: "task_completed", adoptionThreshold: 3, learnUrl: "/features/tasks" },
  { featureKey: "time_tracking", displayName: "Time Tracking", coreEvent: "time_entry_created", adoptionThreshold: 3, learnUrl: "/features/time-tracking" },
  { featureKey: "reporting", displayName: "Reports", coreEvent: "report_generated", adoptionThreshold: 2, learnUrl: "/features/reports" },
  { featureKey: "team_chat", displayName: "Team Chat", coreEvent: "message_sent", adoptionThreshold: 3, learnUrl: "/features/chat" },
  { featureKey: "integrations", displayName: "Integrations", coreEvent: "integration_connected", adoptionThreshold: 1, learnUrl: "/features/integrations" },
  { featureKey: "automations", displayName: "Automations", coreEvent: "automation_created", adoptionThreshold: 2, learnUrl: "/features/automations" },
] as const;

export async function getUserAdoption(userId: string) {
  const rows = await db.query<{ feature_key: string; status: string; usage_count: number; distinct_weeks: number }>(`
    SELECT feature_key, status, usage_count, distinct_weeks_used AS distinct_weeks
    FROM user_feature_adoption WHERE user_id = $1
  `, [userId]);
  const map = new Map(rows.map(r => [r.feature_key, r]));

  return FEATURE_REGISTRY.map(f => ({
    ...f,
    status: (map.get(f.featureKey)?.status as "undiscovered" | "tried" | "adopted") ?? "undiscovered",
    usageCount: map.get(f.featureKey)?.usage_count ?? 0,
  }));
}

// Daily aggregation job
export async function aggregateFeatureAdoption() {
  for (const f of FEATURE_REGISTRY) {
    await db.query(`
      INSERT INTO user_feature_adoption (user_id, feature_key, first_used_at, last_used_at, usage_count, distinct_weeks_used, status)
      SELECT e.user_id, $1, MIN(e.timestamp), MAX(e.timestamp), COUNT(*),
        COUNT(DISTINCT DATE_TRUNC('week', e.timestamp)),
        CASE WHEN COUNT(DISTINCT DATE_TRUNC('week', e.timestamp)) >= $3 THEN 'adopted'
             WHEN COUNT(*) >= 1 THEN 'tried' ELSE 'discovered' END
      FROM events e WHERE e.event_name = $2 GROUP BY e.user_id
      ON CONFLICT (user_id, feature_key) DO UPDATE SET
        last_used_at = EXCLUDED.last_used_at, usage_count = EXCLUDED.usage_count,
        distinct_weeks_used = EXCLUDED.distinct_weeks_used, status = EXCLUDED.status
    `, [f.featureKey, f.coreEvent, f.adoptionThreshold]);
  }
}

// src/components/feature-discovery-prompt.tsx
"use client";
import { useState } from "react";
import Link from "next/link";
import { analytics } from "@/lib/analytics";

export function FeatureDiscoveryPrompt({ feature, context }: {
  feature: { featureKey: string; displayName: string; learnUrl: string };
  context: string;
}) {
  const [dismissed, setDismissed] = useState(false);
  if (dismissed) return null;

  return (
    <div className="flex items-start gap-3 rounded-lg border border-blue-200 bg-blue-50 p-4 dark:border-blue-800 dark:bg-blue-900/20">
      <div className="flex-1">
        <p className="text-sm font-medium text-blue-900 dark:text-blue-100">
          Have you tried {feature.displayName}?
        </p>
        <p className="mt-1 text-sm text-blue-700 dark:text-blue-300">{context}</p>
        <div className="mt-3 flex gap-2">
          <Link href={feature.learnUrl}
            onClick={() => analytics.track("feature_prompt_clicked", { feature: feature.featureKey, source: "discovery_prompt" })}
            className="rounded-md bg-blue-600 px-3 py-1.5 text-sm font-medium text-white hover:bg-blue-700">
            Try it out
          </Link>
          <button onClick={() => { setDismissed(true); analytics.track("feature_prompt_dismissed", { feature: feature.featureKey, source: "discovery_prompt" }); }}
            className="rounded-md px-3 py-1.5 text-sm text-blue-600 hover:bg-blue-100 dark:text-blue-400">
            Maybe later
          </button>
        </div>
      </div>
    </div>
  );
}

-- Misleading: daily retention for a weekly-use product
SELECT COUNT(DISTINCT CASE WHEN active_next_day THEN user_id END)::numeric
  / COUNT(DISTINCT user_id) * 100 AS d1_retention
FROM user_activity GROUP BY DATE_TRUNC('day', first_seen);

// Generic and unhelpful
await sendEmail({ to: user.email, subject: "We miss you!", body: "Come back!" });

await sendEmail({
  to: user.email,
  subject: `${user.name}, your team completed 12 tasks this week`,
  body: `${teammate.name} left 2 comments on your items. [Review](${deepLink})`,
});

-- Bad: rewards passive behavior
INSERT INTO achievements (key, threshold_metric, threshold) VALUES
  ('login_streak_7', 'consecutive_logins', 7),
  ('page_views_100', 'pages_viewed', 100);

-- Good: rewards valuable outcomes
INSERT INTO achievements (key, threshold_metric, threshold) VALUES
  ('task_master_100', 'tasks_completed', 100),
  ('collaborator', 'unique_commenters', 5);

if (lastActivityDate < yesterday) {
  currentStreak = 1; // lost 45-day streak because of a weekend
}

const gap = getEffectiveGap(lastDate, today); // skips grace days
if (gap <= 1) { currentStreak += 1; }
else { currentStreak = 1; }

const digest = await generateDigest(userId);
await sendEmail({ to: user.email, subject: "Weekly summary", html: digest }); // even if empty

const digest = await generateDigest(userId);
if (!digest || digest.totalActivities < 3) return; // skip this week
await sendEmail({ to: user.email, subject: digest.subject, html: digest.html });

const inactiveUsers = await getInactiveUsers(); // no time limit
for (const user of inactiveUsers) await sendReEngagementEmail(user); // forever

if (daysSinceLastActivity > 365) { await unsubscribe(user.id); return; }
if (sentCount >= 3) return; // sequence complete, move to quarterly

WHERE event_name = 'user_logged_in'  -- counts drive-by logins

WHERE event_name IN ('task_completed', 'message_sent', 'report_generated')