Stripe Billing Integration

const claim = await claimEventLedger(supabase, event);
if (!claim.shouldProcess) {
  return { statusCode: 200, body: { received: true, duplicate: true } };
}

async function processCheckoutCompleted(event, supabase, stripe) {
  const subscription = await stripe.subscriptions.retrieve(subscriptionId);
  
  // Resolve user identity from Stripe customer or metadata
  const identity = await resolveWebhookIdentity({ event, supabase });
  
  // Upsert subscription record
  await supabase.from('subscriptions').upsert({
    user_id: identity.userId,
    stripe_customer_id: session.customer,
    stripe_subscription_id: subscription.id,
    plan_id: planId,
    tier: tier,
    status: 'active'
  });
}

async function processInvoicePaid(event, supabase, stripe) {
  // Update subscription status and period dates
  await supabase.from('subscriptions').upsert({
    status: 'active',
    current_period_start: new Date(subscription.current_period_start * 1000),
    current_period_end: new Date(subscription.current_period_end * 1000),
    grace_period_end: null
  });
  
  // Create new usage period for quota tracking
  await supabase.from('subscription_usage').upsert({
    user_id: userId,
    period_start: periodStart,
    period_end: periodEnd,
    analyses_count: 0
  });
}

async function processInvoicePaymentFailed(event, supabase, stripe) {
  const gracePeriodEnd = new Date();
  gracePeriodEnd.setDate(gracePeriodEnd.getDate() + 7); // 7-day grace period
  
  await supabase.from('subscriptions').upsert({
    status: 'past_due',
    grace_period_end: gracePeriodEnd.toISOString()
  });
}

async function processSubscriptionUpdated(event, supabase) {
  // Detect downgrades and flag for admin review
  const isDowngrade = tierRank[newTier] < tierRank[currentTier];
  
  const updateData = {
    tier: newTier,
    plan_id: planId,
    billing_interval: interval,
    cancel_at_period_end: Boolean(subscription.cancel_at_period_end)
  };
  
  if (isDowngrade) {
    updateData.downgrade_action_required = {
      previous_tier: currentTier,
      new_tier: newTier,
      flagged_at: new Date().toISOString()
    };
  }
}

async function processSubscriptionDeleted(event, supabase) {
  await supabase.from('subscriptions').upsert({
    status: 'canceled',
    canceled_at: new Date(subscription.canceled_at * 1000),
    stripe_subscription_id: '', // Clear subscription ID
    grace_period_end: null
  });
}

CREATE TABLE subscriptions (
  user_id UUID PRIMARY KEY REFERENCES profiles(user_id),
  stripe_customer_id TEXT NOT NULL,
  stripe_subscription_id TEXT,
  plan_id UUID REFERENCES plan_definitions(id),
  tier TEXT NOT NULL,
  status TEXT NOT NULL,
  billing_interval TEXT,
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN DEFAULT false,
  canceled_at TIMESTAMPTZ,
  grace_period_end TIMESTAMPTZ,
  downgrade_action_required JSONB,
  stripe_last_event_created_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE stripe_webhook_events (
  event_id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  event_created_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL, -- 'processing' | 'processed' | 'failed'
  error_message TEXT,
  processed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE stripe_webhook_unmatched (
  event_id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  payload_excerpt JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);