// src/lib/plans/definitions.ts
export interface PlanEntitlements {
  maxProjects: number;
  maxSeats: number;
  maxStorageMb: number;
  apiRateLimit: number; // requests per hour
  features: string[];
}

export const plans: Record<string, PlanEntitlements> = {
  free: {
    maxProjects: 3,
    maxSeats: 1,
    maxStorageMb: 500,
    apiRateLimit: 100,
    features: ["basic_analytics", "email_support"],
  },
  pro: {
    maxProjects: 25,
    maxSeats: 10,
    maxStorageMb: 10_000,
    apiRateLimit: 5_000,
    features: [
      "basic_analytics",
      "advanced_analytics",
      "custom_domains",
      "priority_support",
      "api_access",
    ],
  },
  business: {
    maxProjects: -1, // unlimited
    maxSeats: 50,
    maxStorageMb: 100_000,
    apiRateLimit: 50_000,
    features: [
      "basic_analytics",
      "advanced_analytics",
      "custom_domains",
      "priority_support",
      "api_access",
      "sso",
      "audit_log",
      "custom_roles",
    ],
  },
};

// src/lib/entitlements/index.ts
import { db } from "@/lib/db";
import { plans, type PlanEntitlements } from "@/lib/plans/definitions";

export type EntitlementVerdict =
  | { allowed: true; softLimit?: false }
  | { allowed: true; softLimit: true; message: string; percentUsed: number }
  | {
      allowed: false;
      reason: "limit_reached" | "feature_unavailable";
      limit?: number;
      current?: number;
      requiredPlan: string;
    };

type UsageMetric = "projects" | "seats" | "storage_mb" | "api_calls";

async function getOrgUsage(
  orgId: string,
  metric: UsageMetric
): Promise<number> {
  const row = await db.usage.findFirst({
    where: { orgId, metric },
    orderBy: { periodStart: "desc" },
  });
  return row?.value ?? 0;
}

async function getOrgPlan(orgId: string): Promise<string> {
  const sub = await db.subscription.findFirst({
    where: { orgId, status: { in: ["active", "trialing"] } },
  });
  return sub?.planId ?? "free";
}

export async function checkEntitlement(
  orgId: string,
  resource: UsageMetric
): Promise<EntitlementVerdict> {
  const [planId, currentUsage] = await Promise.all([
    getOrgPlan(orgId),
    getOrgUsage(orgId, resource),
  ]);

  const entitlements = plans[planId];
  if (!entitlements) {
    return { allowed: false, reason: "limit_reached", requiredPlan: "pro" };
  }

  const limitMap: Record<UsageMetric, number> = {
    projects: entitlements.maxProjects,
    seats: entitlements.maxSeats,
    storage_mb: entitlements.maxStorageMb,
    api_calls: entitlements.apiRateLimit,
  };

  const limit = limitMap[resource];

  // Unlimited
  if (limit === -1) {
    return { allowed: true };
  }

  // Hard limit reached
  if (currentUsage >= limit) {
    const nextPlan =
      planId === "free" ? "pro" : planId === "pro" ? "business" : "enterprise";
    return {
      allowed: false,
      reason: "limit_reached",
      limit,
      current: currentUsage,
      requiredPlan: nextPlan,
    };
  }

  // Soft limit warning at 80%
  const percentUsed = (currentUsage / limit) * 100;
  if (percentUsed >= 80) {
    return {
      allowed: true,
      softLimit: true,
      message: `You've used ${currentUsage} of ${limit} ${resource}. Consider upgrading for more capacity.`,
      percentUsed,
    };
  }

  return { allowed: true };
}

export async function checkFeature(
  orgId: string,
  feature: string
): Promise<EntitlementVerdict> {
  const planId = await getOrgPlan(orgId);
  const entitlements = plans[planId];

  if (entitlements?.features.includes(feature)) {
    return { allowed: true };
  }

  // Find the lowest plan that includes this feature
  const requiredPlan =
    Object.entries(plans).find(([_, p]) =>
      p.features.includes(feature)
    )?.[0] ?? "business";

  return {
    allowed: false,
    reason: "feature_unavailable",
    requiredPlan,
  };
}

// src/lib/entitlements/middleware.ts — wraps server actions with plan checks
import { checkEntitlement } from "@/lib/entitlements";
import { getOrgId } from "@/lib/auth";

export async function withEntitlement(
  resource: "projects" | "seats" | "storage_mb" | "api_calls",
  action: () => Promise<unknown>
) {
  const orgId = await getOrgId();
  const verdict = await checkEntitlement(orgId, resource);
  if (!verdict.allowed) {
    return { error: "upgrade_required", ...verdict };
  }
  return action();
}

// Usage in a server action — src/app/actions/create-project.ts
"use server";
import { withEntitlement } from "@/lib/entitlements/middleware";
import { db } from "@/lib/db";
import { trackServerEvent } from "@/lib/analytics/server";
import { getOrgId, getUserId } from "@/lib/auth";

export async function createProject(formData: FormData) {
  return withEntitlement("projects", async () => {
    const [orgId, userId] = await Promise.all([getOrgId(), getUserId()]);
    const name = formData.get("name") as string;

    const project = await db.project.create({
      data: { name, orgId, createdBy: userId },
    });
    await db.usage.upsert({
      where: { orgId_metric: { orgId, metric: "projects" } },
      update: { value: { increment: 1 } },
      create: { orgId, metric: "projects", value: 1 },
    });
    trackServerEvent(userId, "project_created", { projectId: project.id, orgId });
    return { success: true, project };
  });
}

// src/lib/pql/scoring-config.ts
export interface ScoringSignal {
  event: string;
  points: number;
  maxPerPeriod: number; // cap repeated events
  description: string;
}

export const scoringSignals: ScoringSignal[] = [
  {
    event: "invite_sent",
    points: 15,
    maxPerPeriod: 3,
    description: "Invited a team member",
  },
  {
    event: "integration_connected",
    points: 20,
    maxPerPeriod: 5,
    description: "Connected an integration",
  },
  {
    event: "usage_limit_reached",
    points: 25,
    maxPerPeriod: 1,
    description: "Hit a usage limit",
  },
  {
    event: "advanced_feature_used",
    points: 10,
    maxPerPeriod: 3,
    description: "Used an advanced feature",
  },
  {
    event: "project_created",
    points: 5,
    maxPerPeriod: 5,
    description: "Created a project",
  },
  {
    event: "pricing_page_viewed",
    points: 3,
    maxPerPeriod: 1,
    description: "Viewed pricing page",
  },
  {
    event: "signup_completed",
    points: 5,
    maxPerPeriod: 1,
    description: "Completed signup",
  },
];

export const PQL_THRESHOLD = 50;

// src/lib/pql/score-calculator.ts
import { db } from "@/lib/db";
import { scoringSignals, PQL_THRESHOLD } from "./scoring-config";

interface PQLResult {
  orgId: string;
  score: number;
  signals: Array<{
    event: string;
    count: number;
    points: number;
    description: string;
  }>;
  qualified: boolean;
}

export async function calculatePQLScore(orgId: string): Promise<PQLResult> {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  // Get all qualifying events for this org in the scoring window
  const eventCounts = await db.$queryRaw<
    Array<{ event_name: string; event_count: number }>
  >`
    SELECT
      e.event_name,
      COUNT(*)::int AS event_count
    FROM events e
    INNER JOIN users u ON e.user_id = u.id
    WHERE u.org_id = ${orgId}
      AND e.event_name IN (${scoringSignals.map((s) => s.event).join(",")})
      AND e.timestamp >= ${thirtyDaysAgo}
    GROUP BY e.event_name
  `;

  const eventMap = new Map(
    eventCounts.map((r) => [r.event_name, r.event_count])
  );

  let totalScore = 0;
  const signals: PQLResult["signals"] = [];

  for (const signal of scoringSignals) {
    const rawCount = eventMap.get(signal.event) ?? 0;
    const cappedCount = Math.min(rawCount, signal.maxPerPeriod);
    const points = cappedCount * signal.points;

    if (points > 0) {
      totalScore += points;
      signals.push({
        event: signal.event,
        count: cappedCount,
        points,
        description: signal.description,
      });
    }
  }

  // Check for consecutive daily activity bonus
  const activeDays = await db.$queryRaw<Array<{ active_days: number }>>`
    SELECT COUNT(DISTINCT DATE(e.timestamp))::int AS active_days
    FROM events e
    INNER JOIN users u ON e.user_id = u.id
    WHERE u.org_id = ${orgId}
      AND e.timestamp >= ${thirtyDaysAgo}
  `;

  if (activeDays[0]?.active_days >= 7) {
    totalScore += 15;
    signals.push({
      event: "weekly_active",
      count: activeDays[0].active_days,
      points: 15,
      description: "Active 7+ days in last 30 days",
    });
  }

  const qualified = totalScore >= PQL_THRESHOLD;

  // Persist the score
  await db.pqlScore.upsert({
    where: { orgId },
    update: {
      score: totalScore,
      signals: JSON.stringify(signals),
      qualifiedAt: qualified ? new Date() : null,
      updatedAt: new Date(),
    },
    create: {
      orgId,
      score: totalScore,
      signals: JSON.stringify(signals),
      qualifiedAt: qualified ? new Date() : null,
    },
  });

  return { orgId, score: totalScore, signals, qualified };
}

// src/lib/pql/crm-sync.ts
import { db } from "@/lib/db";
import { PQL_THRESHOLD } from "./scoring-config";

async function pushToHubSpot(
  email: string,
  properties: Record<string, string | number>
): Promise<void> {
  const res = await fetch("https://api.hubapi.com/crm/v3/objects/contacts", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${process.env.HUBSPOT_API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ properties: { email, ...properties } }),
  });
  // If contact already exists, update instead
  if (res.status === 409) {
    await fetch(
      `https://api.hubapi.com/crm/v3/objects/contacts/${email}?idProperty=email`,
      {
        method: "PATCH",
        headers: {
          Authorization: `Bearer ${process.env.HUBSPOT_API_KEY}`,
          "Content-Type": "application/json",
        },
        body: JSON.stringify({ properties }),
      }
    );
  } else if (!res.ok) {
    throw new Error(`HubSpot sync failed: ${res.status}`);
  }
}

export async function syncPQLsToHubSpot() {
  const unsynced = await db.pqlScore.findMany({
    where: { score: { gte: PQL_THRESHOLD }, syncedToCrm: false },
    include: {
      org: {
        include: {
          owner: { select: { email: true, name: true } },
          _count: { select: { members: true } },
        },
      },
    },
  });

  let synced = 0;
  let errors = 0;

  for (const pql of unsynced) {
    try {
      const signals = JSON.parse(pql.signals as string);
      const topSignals = signals
        .sort((a: { points: number }, b: { points: number }) => b.points - a.points)
        .slice(0, 3)
        .map((s: { description: string }) => s.description)
        .join("; ");

      await pushToHubSpot(pql.org.owner.email, {
        pql_score: pql.score,
        pql_top_signals: topSignals,
        company: pql.org.name,
        team_size: pql.org._count.members,
        product_plan: "free",
        pql_qualified_date: pql.qualifiedAt?.toISOString() ?? "",
        lifecyclestage: "salesqualifiedlead",
      });

      await db.pqlScore.update({
        where: { orgId: pql.orgId },
        data: { syncedToCrm: true },
      });
      synced++;
    } catch (err) {
      console.error(`Failed to sync PQL for org ${pql.orgId}:`, err);
      errors++;
    }
  }
  return { synced, errors };
}

// src/lib/upgrade-triggers/registry.ts
import type { PlanEntitlements } from "@/lib/plans/definitions";

interface UsageSnapshot {
  projects: number;
  seats: number;
  storageMb: number;
  apiCalls: number;
}

export interface UpgradeTrigger {
  id: string;
  condition: (usage: UsageSnapshot, plan: PlanEntitlements) => boolean;
  promptType: "modal" | "banner" | "inline";
  severity: "hard" | "soft";
  cooldownDays: number;
  title: string;
  message: string;
  ctaText: string;
  recommendedPlan: string;
}

export const upgradeTriggers: UpgradeTrigger[] = [
  {
    id: "project_limit_hard",
    condition: (usage, plan) =>
      plan.maxProjects !== -1 && usage.projects >= plan.maxProjects,
    promptType: "modal",
    severity: "hard",
    cooldownDays: 1,
    title: "Project limit reached",
    message:
      "You've used all {limit} projects on your current plan. Upgrade to create unlimited projects.",
    ctaText: "Upgrade to Pro",
    recommendedPlan: "pro",
  },
  {
    id: "project_limit_soft",
    condition: (usage, plan) =>
      plan.maxProjects !== -1 &&
      usage.projects >= Math.floor(plan.maxProjects * 0.8) &&
      usage.projects < plan.maxProjects,
    promptType: "banner",
    severity: "soft",
    cooldownDays: 7,
    title: "Running low on projects",
    message:
      "You've used {current} of {limit} projects. Upgrade for more capacity.",
    ctaText: "View plans",
    recommendedPlan: "pro",
  },
  {
    id: "seat_limit_hard",
    condition: (usage, plan) => usage.seats >= plan.maxSeats,
    promptType: "modal",
    severity: "hard",
    cooldownDays: 1,
    title: "Seat limit reached",
    message:
      "Your team has filled all {limit} seats. Upgrade to add more members.",
    ctaText: "Add more seats",
    recommendedPlan: "pro",
  },
  {
    id: "api_rate_soft",
    condition: (usage, plan) => usage.apiCalls >= plan.apiRateLimit * 0.9,
    promptType: "banner",
    severity: "soft",
    cooldownDays: 3,
    title: "Approaching API limit",
    message:
      "You've used {current} of {limit} API calls this period. Upgrade to avoid rate limiting.",
    ctaText: "Increase limits",
    recommendedPlan: "pro",
  },
];

// src/lib/upgrade-triggers/evaluator.ts
import { db } from "@/lib/db";
import { upgradeTriggers, type UpgradeTrigger } from "./registry";
import { plans } from "@/lib/plans/definitions";

interface ActiveTrigger extends UpgradeTrigger {
  interpolatedMessage: string;
}

export async function getActiveUpgradeTrigger(
  userId: string,
  orgId: string
): Promise<ActiveTrigger | null> {
  const sub = await db.subscription.findFirst({
    where: { orgId, status: { in: ["active", "trialing"] } },
  });
  const planId = sub?.planId ?? "free";
  const plan = plans[planId];
  if (!plan) return null;

  const usageRows = await db.usage.findMany({ where: { orgId } });
  const usage = {
    projects: usageRows.find((r) => r.metric === "projects")?.value ?? 0,
    seats: usageRows.find((r) => r.metric === "seats")?.value ?? 0,
    storageMb: usageRows.find((r) => r.metric === "storage_mb")?.value ?? 0,
    apiCalls: usageRows.find((r) => r.metric === "api_calls")?.value ?? 0,
  };

  // Map trigger IDs to the usage metric they reference
  const impressions = await db.promptImpression.findMany({ where: { userId } });
  const lastShownMap = new Map(impressions.map((i) => [i.triggerId, i.shownAt]));

  // Evaluate in priority order: hard limits first
  const sorted = [...upgradeTriggers].sort((a, b) =>
    a.severity === "hard" && b.severity !== "hard" ? -1 :
    b.severity === "hard" && a.severity !== "hard" ? 1 : 0
  );

  for (const trigger of sorted) {
    if (!trigger.condition(usage, plan)) continue;

    // Enforce cooldown
    const lastShown = lastShownMap.get(trigger.id);
    if (lastShown) {
      const cooldownMs = trigger.cooldownDays * 24 * 60 * 60 * 1000;
      if (Date.now() - lastShown.getTime() < cooldownMs) continue;
    }

    const metricKey = trigger.id.startsWith("api") ? "api_calls"
      : trigger.id.startsWith("seat") ? "seats" : "projects";
    const current = usage[metricKey as keyof typeof usage];
    const limitMap = { projects: plan.maxProjects, seats: plan.maxSeats,
      storage_mb: plan.maxStorageMb, api_calls: plan.apiRateLimit };

    return {
      ...trigger,
      interpolatedMessage: trigger.message
        .replace("{current}", String(current))
        .replace("{limit}", String(limitMap[metricKey as keyof typeof limitMap])),
    };
  }
  return null;
}

// src/components/upgrade-prompt-provider.tsx
"use client";
import { useEffect, useState } from "react";
import { analytics } from "@/lib/analytics";

interface TriggerData {
  id: string;
  promptType: "modal" | "banner" | "inline";
  severity: "hard" | "soft";
  title: string;
  interpolatedMessage: string;
  ctaText: string;
  recommendedPlan: string;
}

export function UpgradePromptProvider({ children }: { children: React.ReactNode }) {
  const [trigger, setTrigger] = useState<TriggerData | null>(null);

  useEffect(() => {
    fetch("/api/upgrade-triggers/check").then(async (res) => {
      if (!res.ok) return;
      const { trigger } = await res.json();
      if (trigger) {
        setTrigger(trigger);
        analytics.track("upgrade_prompt_shown", {
          trigger_id: trigger.id, severity: trigger.severity, source: window.location.pathname,
        });
      }
    });
  }, []);

  async function handleAction(action: "dismissed" | "clicked_upgrade") {
    if (!trigger) return;
    await fetch("/api/upgrade-triggers/impression", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ triggerId: trigger.id, action }),
    });
    analytics.track("upgrade_prompt_acted", { trigger_id: trigger.id, action });
    if (action === "clicked_upgrade") {
      window.location.href = `/billing/upgrade?plan=${trigger.recommendedPlan}`;
    }
    setTrigger(null);
  }

  return (
    <>
      {children}
      {trigger?.promptType === "banner" && (
        <div className="fixed bottom-0 left-0 right-0 bg-amber-50 border-t border-amber-200 p-4 flex items-center justify-between z-50">
          <div>
            <p className="font-medium text-amber-900">{trigger.title}</p>
            <p className="text-sm text-amber-700">{trigger.interpolatedMessage}</p>
          </div>
          <div className="flex gap-2">
            <button onClick={() => handleAction("dismissed")} className="px-3 py-1.5 text-sm text-amber-700">Maybe later</button>
            <button onClick={() => handleAction("clicked_upgrade")} className="px-4 py-1.5 text-sm bg-amber-600 text-white rounded-md">{trigger.ctaText}</button>
          </div>
        </div>
      )}
      {trigger?.promptType === "modal" && (
        <div className="fixed inset-0 bg-black/50 flex items-center justify-center z-50">
          <div className="bg-white rounded-lg p-6 max-w-md mx-4">
            <h3 className="text-lg font-semibold">{trigger.title}</h3>
            <p className="mt-2 text-gray-600">{trigger.interpolatedMessage}</p>
            <div className="mt-6 flex gap-3 justify-end">
              <button onClick={() => handleAction("dismissed")} className="px-4 py-2 text-sm text-gray-600">Maybe later</button>
              <button onClick={() => handleAction("clicked_upgrade")} className="px-4 py-2 text-sm bg-blue-600 text-white rounded-md">{trigger.ctaText}</button>
            </div>
          </div>
        </div>
      )}
    </>
  );
}

-- Time-to-value by signup cohort (weekly)
WITH signups AS (
  SELECT user_id, MIN(timestamp) AS signup_at,
    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
),
activations AS (
  SELECT e.user_id, MIN(e.timestamp) AS activated_at
  FROM events e
  INNER JOIN signups s ON e.user_id = s.user_id
  WHERE e.event_name = 'onboarding_completed'
    AND e.timestamp > s.signup_at
    AND e.timestamp <= s.signup_at + INTERVAL '30 days'
  GROUP BY e.user_id
),
ttv AS (
  SELECT s.cohort_week, s.user_id,
    EXTRACT(EPOCH FROM (a.activated_at - s.signup_at)) / 3600.0 AS hours_to_value
  FROM signups s INNER JOIN activations a ON s.user_id = a.user_id
)
SELECT cohort_week,
  COUNT(*) AS activated_users,
  (SELECT COUNT(*) FROM signups s2 WHERE s2.cohort_week = ttv.cohort_week) AS total_signups,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours_to_value)::numeric, 1) AS median_ttv_hours,
  ROUND(COUNT(*) FILTER (WHERE hours_to_value <= 1)::numeric / NULLIF(COUNT(*), 0) * 100, 1) AS pct_within_1hr,
  ROUND(COUNT(*) FILTER (WHERE hours_to_value <= 24)::numeric / NULLIF(COUNT(*), 0) * 100, 1) AS pct_within_24hr
FROM ttv GROUP BY cohort_week ORDER BY cohort_week DESC;

-- PQL conversion funnel: qualified leads → upgrade → expansion
WITH pql_qualified AS (
  SELECT org_id, score, qualified_at,
    DATE_TRUNC('week', qualified_at) AS qualified_week
  FROM pql_scores
  WHERE qualified_at IS NOT NULL AND qualified_at >= NOW() - INTERVAL '12 weeks'
),
upgraded AS (
  SELECT DISTINCT s.org_id, MIN(s.created_at) AS upgraded_at
  FROM subscriptions s INNER JOIN pql_qualified pq ON s.org_id = pq.org_id
  WHERE s.plan_id != 'free' AND s.created_at > pq.qualified_at
  GROUP BY s.org_id
),
expanded AS (
  SELECT DISTINCT e.properties->>'org_id' AS org_id
  FROM events e INNER JOIN upgraded u ON (e.properties->>'org_id') = u.org_id
  WHERE e.event_name IN ('plan_upgraded', 'seat_added') AND e.timestamp > u.upgraded_at
)
SELECT pq.qualified_week,
  COUNT(DISTINCT pq.org_id) AS total_pqls,
  COUNT(DISTINCT u.org_id) AS converted,
  ROUND(COUNT(DISTINCT u.org_id)::numeric / NULLIF(COUNT(DISTINCT pq.org_id), 0) * 100, 1) AS pql_conversion_pct,
  COUNT(DISTINCT ex.org_id) AS expanded,
  ROUND(COUNT(DISTINCT ex.org_id)::numeric / NULLIF(COUNT(DISTINCT u.org_id), 0) * 100, 1) AS expansion_rate_pct
FROM pql_qualified pq
LEFT JOIN upgraded u ON pq.org_id = u.org_id
LEFT JOIN expanded ex ON pq.org_id = ex.org_id
GROUP BY pq.qualified_week ORDER BY pq.qualified_week DESC;

-- Net revenue retention (NRR) by month
WITH monthly_mrr AS (
  SELECT DATE_TRUNC('month', period_start) AS month, org_id,
    SUM(amount_cents) / 100.0 AS mrr
  FROM subscription_periods WHERE status = 'active'
  GROUP BY DATE_TRUNC('month', period_start), org_id
),
mrr_changes AS (
  SELECT curr.month,
    SUM(CASE WHEN prev.org_id IS NULL THEN curr.mrr ELSE 0 END) AS new_mrr,
    SUM(CASE WHEN prev.org_id IS NOT NULL AND curr.mrr > prev.mrr THEN curr.mrr - prev.mrr ELSE 0 END) AS expansion_mrr,
    SUM(CASE WHEN prev.org_id IS NOT NULL AND curr.mrr < prev.mrr THEN prev.mrr - curr.mrr ELSE 0 END) AS contraction_mrr,
    SUM(CASE WHEN curr_next.org_id IS NULL AND curr.mrr > 0 THEN curr.mrr ELSE 0 END) AS churned_mrr
  FROM monthly_mrr curr
  LEFT JOIN monthly_mrr prev ON curr.org_id = prev.org_id AND prev.month = curr.month - INTERVAL '1 month'
  LEFT JOIN monthly_mrr curr_next ON curr.org_id = curr_next.org_id AND curr_next.month = curr.month + INTERVAL '1 month'
  GROUP BY curr.month
)
SELECT month, ROUND(new_mrr, 2) AS new_mrr, ROUND(expansion_mrr, 2) AS expansion_mrr,
  ROUND(contraction_mrr, 2) AS contraction_mrr, ROUND(churned_mrr, 2) AS churned_mrr
FROM mrr_changes WHERE month >= NOW() - INTERVAL '12 months' ORDER BY month DESC;

-- Expansion MRR breakdown by type
SELECT DATE_TRUNC('month', e.timestamp) AS month,
  CASE WHEN e.event_name = 'seat_added' THEN 'seat_expansion'
       WHEN e.event_name = 'plan_upgraded' THEN 'plan_upgrade'
       WHEN e.event_name = 'usage_overage_charged' THEN 'usage_overage'
  END AS expansion_type,
  COUNT(DISTINCT e.properties->>'org_id') AS orgs,
  SUM((e.properties->>'amount_cents')::int) / 100.0 AS expansion_revenue
FROM events e
WHERE e.event_name IN ('seat_added', 'plan_upgraded', 'usage_overage_charged')
  AND e.timestamp >= NOW() - INTERVAL '6 months'
GROUP BY DATE_TRUNC('month', e.timestamp), expansion_type
ORDER BY month DESC, expansion_revenue DESC;

// src/lib/routing/rules.ts
export type BuyingRoute = "self-serve" | "sales-assist" | "enterprise";

interface RoutingSignals {
  seatCount: number;
  pqlScore: number;
  sameEmailDomainUsers: number;
  requestedSso: boolean;
  requestedCustomPricing: boolean;
  estimatedAcv: number; // annual contract value in dollars
}

interface RoutingRule {
  route: BuyingRoute;
  priority: number; // lower = higher priority
  condition: (signals: RoutingSignals) => boolean;
}

const routingRules: RoutingRule[] = [
  {
    route: "enterprise",
    priority: 1,
    condition: (s) =>
      s.seatCount > 50 ||
      s.requestedSso ||
      s.requestedCustomPricing ||
      s.sameEmailDomainUsers > 10 ||
      s.estimatedAcv > 50_000,
  },
  {
    route: "sales-assist",
    priority: 2,
    condition: (s) =>
      (s.seatCount >= 5 && s.seatCount <= 50) ||
      (s.pqlScore > 75 && s.estimatedAcv > 5_000) ||
      s.sameEmailDomainUsers > 5,
  },
  {
    route: "self-serve",
    priority: 3,
    condition: () => true, // default fallback
  },
];

export function evaluateRoute(signals: RoutingSignals): BuyingRoute {
  const sorted = [...routingRules].sort((a, b) => a.priority - b.priority);
  for (const rule of sorted) {
    if (rule.condition(signals)) {
      return rule.route;
    }
  }
  return "self-serve";
}

// src/lib/routing/evaluate.ts
import { db } from "@/lib/db";
import { evaluateRoute, type BuyingRoute } from "./rules";
import { trackServerEvent } from "@/lib/analytics/server";

export async function getBuyingRoute(orgId: string) {
  const org = await db.org.findUniqueOrThrow({
    where: { id: orgId },
    include: { members: true, owner: true, subscription: true, pqlScore: true },
  });

  const ownerDomain = org.owner.email.split("@")[1];
  const personalDomains = ["gmail.com", "yahoo.com", "hotmail.com", "outlook.com"];
  const sameEmailDomainUsers = personalDomains.includes(ownerDomain) ? 0
    : await db.user.count({ where: { email: { endsWith: `@${ownerDomain}` } } });

  const enterpriseRequests = await db.featureRequest.count({
    where: { orgId, feature: { in: ["sso", "saml", "custom_pricing", "sla"] } },
  });

  const seatCount = org.members.length;
  const planPrices: Record<string, number> = { free: 0, pro: 29, business: 99 };
  const estimatedAcv = (planPrices[org.subscription?.planId ?? "free"] ?? 29) * seatCount * 12;

  const signals = {
    seatCount, pqlScore: org.pqlScore?.score ?? 0, sameEmailDomainUsers,
    requestedSso: enterpriseRequests > 0, requestedCustomPricing: false, estimatedAcv,
  };
  const route = evaluateRoute(signals);

  trackServerEvent(org.owner.id, "buying_route_assigned", {
    orgId, route, seatCount, pqlScore: signals.pqlScore, estimatedAcv,
  });

  return { route, signals };
}

// src/app/api/upgrade/route.ts
import { NextResponse } from "next/server";
import { getBuyingRoute } from "@/lib/routing/evaluate";
import { getOrgId } from "@/lib/auth";

export async function GET() {
  const orgId = await getOrgId();
  const { route, signals } = await getBuyingRoute(orgId);

  return NextResponse.json({ route, signals });
}

// src/app/billing/upgrade/page.tsx
import { getBuyingRoute } from "@/lib/routing/evaluate";
import { getOrgId } from "@/lib/auth";
import { SelfServeCheckout } from "@/components/billing/self-serve-checkout";
import { SalesAssistCheckout } from "@/components/billing/sales-assist-checkout";
import { EnterpriseContact } from "@/components/billing/enterprise-contact";

export default async function UpgradePage() {
  const orgId = await getOrgId();
  const { route, signals } = await getBuyingRoute(orgId);

  switch (route) {
    case "self-serve":
      return <SelfServeCheckout />;
    case "sales-assist":
      return (
        <SalesAssistCheckout
          showSalesCtaAfterSeconds={10}
          context={{
            seatCount: signals.seatCount,
            estimatedAcv: signals.estimatedAcv,
          }}
        />
      );
    case "enterprise":
      return (
        <EnterpriseContact
          prefilledSeats={signals.seatCount}
          pqlScore={signals.pqlScore}
        />
      );
  }
}

// User signs up, immediately hits a wall
export async function createProject(data: ProjectData) {
  const plan = await getUserPlan(data.userId);
  if (plan === "free") {
    throw new Error("Projects require a paid plan");
  }
  // Free users can never do anything useful
}

export async function createProject(data: ProjectData) {
  const verdict = await checkEntitlement(data.orgId, "projects");
  if (!verdict.allowed) {
    return {
      error: "upgrade_required",
      limit: verdict.limit,       // e.g., 3
      current: verdict.current,   // e.g., 3
      requiredPlan: verdict.requiredPlan,
    };
  }
  return db.project.create({ data });
}

// Scattered across 30+ files
if (user.plan === "free") {
  return <UpgradePrompt />;
}
if (user.plan !== "enterprise") {
  throw new Error("SSO requires Enterprise");
}

const verdict = await checkFeature(orgId, "sso");
if (!verdict.allowed) {
  return <UpgradePrompt requiredPlan={verdict.requiredPlan} />;
}

// Fires on every action — user sees this constantly
if (usage.projects >= plan.maxProjects * 0.8) {
  showUpgradeModal(); // no tracking, no cooldown
}

const trigger = await getActiveUpgradeTrigger(userId, orgId);
// Returns null if all matching triggers are within cooldown
if (trigger) {
  await recordImpression(userId, trigger.id);
  showUpgradePrompt(trigger);
}

// Score only goes up, never down
async function updatePQLScore(orgId: string, event: string) {
  const points = scoringMap[event] ?? 0;
  await db.pqlScore.update({
    where: { orgId },
    data: { score: { increment: points } },
  });
}

const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
const events = await db.event.findMany({
  where: { orgId, timestamp: { gte: thirtyDaysAgo } },
});
// Recalculate from scratch using only recent events with caps

-- All revenue in one bucket — useless for PLG analysis
SELECT SUM(amount_cents) / 100 AS total_mrr
FROM subscriptions
WHERE status = 'active';

SELECT
  expansion_type,
  SUM(delta_cents) / 100.0 AS expansion_mrr
FROM subscription_changes
WHERE change_type = 'expansion'
  AND changed_at >= DATE_TRUNC('month', NOW())
GROUP BY expansion_type;

// Client-side only — trivially bypassed
function CreateProjectButton() {
  const { plan, projectCount } = useOrg();
  if (projectCount >= PLAN_LIMITS[plan].maxProjects) {
    return <UpgradePrompt />;
  }
  return <button onClick={createProject}>New Project</button>;
}
// API route has no limit check — anyone can POST

// Server action — the actual enforcement
export async function createProject(data: FormData) {
  const verdict = await checkEntitlement(orgId, "projects");
  if (!verdict.allowed) {
    return { error: "upgrade_required", ...verdict };
  }
  return db.project.create({ data: { name: data.get("name") } });
}