F021 - Pricing Plans & Billing

-- Subscription plans
CREATE TABLE analyzer.subscription_plans (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  stripe_product_id VARCHAR(255) UNIQUE,
  stripe_price_id VARCHAR(255) UNIQUE,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(50) UNIQUE NOT NULL, -- 'free', 'professional', 'bundle', 'enterprise'
  price_cents INTEGER NOT NULL,
  billing_period VARCHAR(20), -- 'once', 'monthly', 'yearly'
  features JSONB NOT NULL,
  limits JSONB NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- User subscriptions
CREATE TABLE analyzer.user_subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  plan_id UUID REFERENCES analyzer.subscription_plans(id),
  stripe_subscription_id VARCHAR(255),
  stripe_customer_id VARCHAR(255),
  status VARCHAR(50) NOT NULL, -- 'active', 'canceled', 'past_due', 'expired'
  current_period_start TIMESTAMP,
  current_period_end TIMESTAMP,
  cancel_at_period_end BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Usage tracking
CREATE TABLE analyzer.usage_records (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  resource_type VARCHAR(50) NOT NULL, -- 'analysis', 'word_count', 'consultation'
  resource_id UUID,
  quantity INTEGER NOT NULL DEFAULT 1,
  metadata JSONB DEFAULT '{}',
  recorded_at TIMESTAMP DEFAULT NOW()
);
 
-- One-time purchases
CREATE TABLE analyzer.purchases (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  stripe_payment_intent_id VARCHAR(255) UNIQUE,
  product_type VARCHAR(50) NOT NULL, -- 'analysis', 'consultation'
  amount_cents INTEGER NOT NULL,
  status VARCHAR(50) NOT NULL, -- 'pending', 'completed', 'failed', 'refunded'
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Seed subscription plans
INSERT INTO analyzer.subscription_plans (code, name, price_cents, billing_period, features, limits) VALUES
('free', 'Free Tier', 0, NULL, 
  '{"analyses_per_month": 3, "max_words": 5000, "basic_report": true}',
  '{"analyses_per_month": 3, "max_words_per_analysis": 5000}'
),
('professional', 'Professional', 3900, 'once',
  '{"full_analysis": true, "detailed_report": true, "export_pdf": true, "max_words": 150000}',
  '{"max_words_per_analysis": 150000}'
),
('bundle', 'Author Bundle', 14900, 'monthly',
  '{"analyses_per_month": 5, "priority_processing": true, "revision_tracking": true, "all_features": true}',
  '{"analyses_per_month": 5, "max_words_per_analysis": 150000}'
),
('enterprise', 'Enterprise', 39900, 'monthly',
  '{"unlimited_analyses": true, "api_access": true, "white_label": true, "priority_support": true}',
  '{"analyses_per_month": -1, "max_words_per_analysis": 200000}'
);
 
-- Indexes
CREATE INDEX idx_user_subscriptions_user_id ON analyzer.user_subscriptions(user_id);
CREATE INDEX idx_user_subscriptions_status ON analyzer.user_subscriptions(status);
CREATE INDEX idx_usage_records_user_id ON analyzer.usage_records(user_id, resource_type);

// packages/billing/src/services/billing-service.ts
import Stripe from 'stripe'
import { createClient } from '@mystoryflow/database/server'
 
export class BillingService {
  private stripe: Stripe
  private supabase = getSupabaseBrowserClient()
 
  constructor() {
    this.stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
      apiVersion: '2023-10-16'
    })
  }
 
  async createCheckoutSession(
    userId: string,
    planCode: string,
    successUrl: string,
    cancelUrl: string
  ): Promise<string> {
    // Get plan details
    const { data: plan } = await this.supabase
      .from('analyzer.subscription_plans')
      .select('*')
      .eq('code', planCode)
      .single()
 
    if (!plan) throw new Error('Plan not found')
 
    // Get or create Stripe customer
    const customer = await this.getOrCreateStripeCustomer(userId)
 
    // Create appropriate session based on billing period
    if (plan.billing_period === 'once') {
      return this.createOneTimeCheckout(customer.id, plan, successUrl, cancelUrl, userId)
    } else {
      return this.createSubscriptionCheckout(customer.id, plan, successUrl, cancelUrl, userId)
    }
  }
 
  private async createOneTimeCheckout(
    customerId: string,
    plan: any,
    successUrl: string,
    cancelUrl: string,
    userId: string
  ): Promise<string> {
    const session = await this.stripe.checkout.sessions.create({
      customer: customerId,
      payment_method_types: ['card'],
      line_items: [
        {
          price_data: {
            currency: 'usd',
            product_data: {
              name: plan.name,
              description: 'One-time manuscript analysis'
            },
            unit_amount: plan.price_cents
          },
          quantity: 1
        }
      ],
      mode: 'payment',
      success_url: successUrl,
      cancel_url: cancelUrl,
      metadata: {
        userId,
        planCode: plan.code,
        type: 'one_time'
      }
    })
 
    return session.url!
  }
 
  private async createSubscriptionCheckout(
    customerId: string,
    plan: any,
    successUrl: string,
    cancelUrl: string,
    userId: string
  ): Promise<string> {
    const session = await this.stripe.checkout.sessions.create({
      customer: customerId,
      payment_method_types: ['card'],
      line_items: [
        {
          price: plan.stripe_price_id,
          quantity: 1
        }
      ],
      mode: 'subscription',
      success_url: successUrl,
      cancel_url: cancelUrl,
      subscription_data: {
        metadata: {
          userId,
          planId: plan.id
        }
      }
    })
 
    return session.url!
  }
 
  async handleWebhook(event: Stripe.Event): Promise<void> {
    switch (event.type) {
      case 'checkout.session.completed':
        await this.handleCheckoutComplete(event.data.object as Stripe.Checkout.Session)
        break
      
      case 'customer.subscription.updated':
      case 'customer.subscription.deleted':
        await this.handleSubscriptionUpdate(event.data.object as Stripe.Subscription)
        break
      
      case 'invoice.payment_failed':
        await this.handlePaymentFailed(event.data.object as Stripe.Invoice)
        break
    }
  }
 
  private async handleCheckoutComplete(session: Stripe.Checkout.Session) {
    const userId = session.metadata?.userId
    if (!userId) return
 
    if (session.mode === 'payment') {
      // Handle one-time purchase
      await this.supabase
        .from('analyzer.purchases')
        .insert({
          user_id: userId,
          stripe_payment_intent_id: session.payment_intent as string,
          product_type: 'analysis',
          amount_cents: session.amount_total!,
          status: 'completed',
          metadata: {
            planCode: session.metadata.planCode
          }
        })
 
      // Grant one-time analysis credit
      await this.grantAnalysisCredit(userId)
    } else {
      // Handle subscription
      const subscription = await this.stripe.subscriptions.retrieve(
        session.subscription as string
      )
      
      await this.updateUserSubscription(userId, subscription)
    }
  }
 
  private async updateUserSubscription(userId: string, subscription: Stripe.Subscription) {
    const planId = subscription.metadata.planId
    
    await this.supabase
      .from('analyzer.user_subscriptions')
      .upsert({
        user_id: userId,
        plan_id: planId,
        stripe_subscription_id: subscription.id,
        stripe_customer_id: subscription.customer as string,
        status: subscription.status,
        current_period_start: new Date(subscription.current_period_start * 1000),
        current_period_end: new Date(subscription.current_period_end * 1000),
        cancel_at_period_end: subscription.cancel_at_period_end
      })
  }
 
  async checkUsageLimits(userId: string, resourceType: string): Promise<boolean> {
    // Get user's current plan
    const { data: subscription } = await this.supabase
      .from('analyzer.user_subscriptions')
      .select('*, subscription_plans(*)')
      .eq('user_id', userId)
      .eq('status', 'active')
      .single()
 
    const plan = subscription?.subscription_plans || await this.getFreePlan()
    const limits = plan.limits
 
    // Check if unlimited
    if (limits[`${resourceType}s_per_month`] === -1) return true
 
    // Count usage in current period
    const startDate = subscription?.current_period_start || this.getMonthStart()
    
    const { count } = await this.supabase
      .from('analyzer.usage_records')
      .select('*', { count: 'exact' })
      .eq('user_id', userId)
      .eq('resource_type', resourceType)
      .gte('recorded_at', startDate)
 
    return (count || 0) < limits[`${resourceType}s_per_month`]
  }
 
  async recordUsage(
    userId: string,
    resourceType: string,
    resourceId?: string,
    quantity: number = 1,
    metadata?: any
  ): Promise<void> {
    await this.supabase
      .from('analyzer.usage_records')
      .insert({
        user_id: userId,
        resource_type: resourceType,
        resource_id: resourceId,
        quantity,
        metadata
      })
  }
 
  private async getOrCreateStripeCustomer(userId: string): Promise<Stripe.Customer> {
    // Check if customer exists
    const { data: user } = await this.supabase
      .from('analyzer.users')
      .select('email, stripe_customer_id')
      .eq('id', userId)
      .single()
 
    if (user?.stripe_customer_id) {
      return await this.stripe.customers.retrieve(user.stripe_customer_id) as Stripe.Customer
    }
 
    // Create new customer
    const customer = await this.stripe.customers.create({
      email: user?.email,
      metadata: { userId }
    })
 
    // Save customer ID
    await this.supabase
      .from('analyzer.users')
      .update({ stripe_customer_id: customer.id })
      .eq('id', userId)
 
    return customer
  }
 
  private async getFreePlan() {
    const { data } = await this.supabase
      .from('analyzer.subscription_plans')
      .select('*')
      .eq('code', 'free')
      .single()
    
    return data
  }
 
  private getMonthStart(): Date {
    const now = new Date()
    return new Date(now.getFullYear(), now.getMonth(), 1)
  }
}

// apps/analyzer-app/src/app/pricing/page.tsx
import { Card, Button, Badge } from '@mystoryflow/ui'
import { Check, X } from 'lucide-react'
import { createClient } from '@mystoryflow/database/server'
import { PricingCard } from '@/components/billing/PricingCard'
 
export default async function PricingPage() {
  const supabase = getSupabaseBrowserClient()
  
  // Get subscription plans
  const { data: plans } = await supabase
    .from('analyzer.subscription_plans')
    .select('*')
    .eq('is_active', true)
    .order('price_cents')
 
  const freePlan = plans?.find(p => p.code === 'free')
  const professionalPlan = plans?.find(p => p.code === 'professional')
  const bundlePlan = plans?.find(p => p.code === 'bundle')
  const enterprisePlan = plans?.find(p => p.code === 'enterprise')
 
  return (
    <div className="container py-16">
      <div className="text-center mb-12">
        <h1 className="text-4xl font-bold mb-4">
          Choose Your Analysis Plan
        </h1>
        <p className="text-xl text-muted-foreground max-w-2xl mx-auto">
          Professional manuscript analysis powered by AI. Get detailed feedback
          in minutes, not weeks.
        </p>
      </div>
 
      <div className="grid gap-8 lg:grid-cols-4 mb-16">
        {/* Free Tier */}
        <PricingCard
          plan={freePlan}
          features={[
            '3 analyses per month',
            'Up to 5,000 words',
            'Basic analysis report',
            'Community support'
          ]}
          highlighted={false}
        />
 
        {/* Professional */}
        <PricingCard
          plan={professionalPlan}
          features={[
            'One complete analysis',
            'Up to 150,000 words',
            'Detailed 20-page report',
            'Export to PDF',
            '48-hour turnaround'
          ]}
          highlighted={false}
          badge="Pay Once"
        />
 
        {/* Bundle */}
        <PricingCard
          plan={bundlePlan}
          features={[
            '5 analyses per month',
            'Priority processing',
            'Revision tracking',
            'Progress dashboard',
            'Email support'
          ]}
          highlighted={true}
          badge="Most Popular"
        />
 
        {/* Enterprise */}
        <PricingCard
          plan={enterprisePlan}
          features={[
            'Unlimited analyses',
            'API access',
            'White-label option',
            'Dedicated support',
            'Custom integrations'
          ]}
          highlighted={false}
          badge="For Teams"
        />
      </div>
 
      {/* Feature Comparison */}
      <Card className="p-8">
        <h2 className="text-2xl font-bold mb-6">Compare Features</h2>
        <FeatureComparison plans={[freePlan, professionalPlan, bundlePlan, enterprisePlan]} />
      </Card>
 
      {/* FAQ Section */}
      <div className="mt-16 max-w-3xl mx-auto">
        <h2 className="text-2xl font-bold mb-8 text-center">
          Frequently Asked Questions
        </h2>
        <FAQ />
      </div>
    </div>
  )
}
 
function FeatureComparison({ plans }: { plans: any[] }) {
  const features = [
    { name: 'Manuscript Analysis', free: '✓', professional: '✓', bundle: '✓', enterprise: '✓' },
    { name: 'Word Limit', free: '5,000', professional: '150,000', bundle: '150,000', enterprise: '200,000' },
    { name: 'Analyses per Month', free: '3', professional: '1', bundle: '5', enterprise: 'Unlimited' },
    { name: 'Processing Time', free: '5 min', professional: '5 min', bundle: 'Priority', enterprise: 'Priority' },
    { name: 'Detailed Report', free: '✗', professional: '✓', bundle: '✓', enterprise: '✓' },
    { name: 'PDF Export', free: '✗', professional: '✓', bundle: '✓', enterprise: '✓' },
    { name: 'Revision Tracking', free: '✗', professional: '✗', bundle: '✓', enterprise: '✓' },
    { name: 'Progress Dashboard', free: '✗', professional: '✗', bundle: '✓', enterprise: '✓' },
    { name: 'API Access', free: '✗', professional: '✗', bundle: '✗', enterprise: '✓' },
    { name: 'Support', free: 'Community', professional: 'Email', bundle: 'Priority', enterprise: 'Dedicated' }
  ]
 
  return (
    <div className="overflow-x-auto">
      <table className="w-full">
        <thead>
          <tr className="border-b">
            <th className="text-left py-3 px-4">Feature</th>
            <th className="text-center py-3 px-4">Free</th>
            <th className="text-center py-3 px-4">Professional</th>
            <th className="text-center py-3 px-4">Bundle</th>
            <th className="text-center py-3 px-4">Enterprise</th>
          </tr>
        </thead>
        <tbody>
          {features.map((feature, i) => (
            <tr key={i} className="border-b">
              <td className="py-3 px-4">{feature.name}</td>
              <td className="text-center py-3 px-4">{feature.free}</td>
              <td className="text-center py-3 px-4">{feature.professional}</td>
              <td className="text-center py-3 px-4">{feature.bundle}</td>
              <td className="text-center py-3 px-4">{feature.enterprise}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  )
}

// apps/analyzer-app/src/app/settings/billing/page.tsx
'use client'
 
import { useState } from 'react'
import { Card, Button, Badge } from '@mystoryflow/ui'
import { CreditCard, FileText, Download } from 'lucide-react'
import { useSubscription } from '@/hooks/useSubscription'
import { UsageChart } from '@/components/billing/UsageChart'
import { InvoiceList } from '@/components/billing/InvoiceList'
 
export default function BillingPage() {
  const { subscription, usage, loading } = useSubscription()
  const [managingBilling, setManagingBilling] = useState(false)
 
  const handleManageBilling = async () => {
    setManagingBilling(true)
    try {
      const response = await fetch('/api/billing/portal', {
        method: 'POST'
      })
      const { url } = await response.json()
      window.location.href = url
    } catch (error) {
      console.error('Error:', error)
    } finally {
      setManagingBilling(false)
    }
  }
 
  if (loading) return <div>Loading...</div>
 
  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-bold">Billing & Subscription</h1>
 
      {/* Current Plan */}
      <Card className="p-6">
        <div className="flex items-center justify-between mb-4">
          <h2 className="text-lg font-semibold">Current Plan</h2>
          <Badge variant={subscription?.status === 'active' ? 'success' : 'secondary'}>
            {subscription?.status || 'Free'}
          </Badge>
        </div>
 
        <div className="space-y-4">
          <div>
            <p className="text-2xl font-bold">
              {subscription?.subscription_plans?.name || 'Free Tier'}
            </p>
            <p className="text-muted-foreground">
              {subscription?.subscription_plans?.price_cents 
                ? `$${(subscription.subscription_plans.price_cents / 100).toFixed(2)}/month`
                : 'No charge'
              }
            </p>
          </div>
 
          {subscription?.current_period_end && (
            <p className="text-sm text-muted-foreground">
              {subscription.cancel_at_period_end 
                ? `Cancels on ${new Date(subscription.current_period_end).toLocaleDateString()}`
                : `Renews on ${new Date(subscription.current_period_end).toLocaleDateString()}`
              }
            </p>
          )}
 
          <div className="flex gap-3">
            <Button 
              onClick={handleManageBilling}
              disabled={managingBilling || !subscription}
            >
              <CreditCard className="h-4 w-4 mr-2" />
              Manage Billing
            </Button>
            {!subscription && (
              <Button variant="outline" asChild>
                <a href="/pricing">Upgrade Plan</a>
              </Button>
            )}
          </div>
        </div>
      </Card>
 
      {/* Usage This Month */}
      <Card className="p-6">
        <h2 className="text-lg font-semibold mb-4">Usage This Month</h2>
        <UsageChart usage={usage} limits={subscription?.subscription_plans?.limits} />
      </Card>
 
      {/* Invoices */}
      <Card className="p-6">
        <h2 className="text-lg font-semibold mb-4">Invoice History</h2>
        <InvoiceList customerId={subscription?.stripe_customer_id} />
      </Card>
    </div>
  )
}

// apps/analyzer-app/src/hooks/useSubscription.ts
import { useEffect, useState } from 'react'
import { createClient } from '@mystoryflow/database/client'
 
export function useSubscription() {
  const [subscription, setSubscription] = useState<any>(null)
  const [usage, setUsage] = useState<any>({})
  const [loading, setLoading] = useState(true)
  const supabase = getSupabaseBrowserClient()
 
  useEffect(() => {
    loadSubscriptionData()
  }, [])
 
  const loadSubscriptionData = async () => {
    try {
      // Get current subscription
      const { data: sub } = await supabase
        .from('analyzer.user_subscriptions')
        .select('*, subscription_plans(*)')
        .eq('status', 'active')
        .single()
 
      setSubscription(sub)
 
      // Get usage for current period
      const startDate = sub?.current_period_start || getMonthStart()
      
      const { data: usageData } = await supabase
        .from('analyzer.usage_records')
        .select('resource_type, quantity')
        .gte('recorded_at', startDate)
 
      // Aggregate usage by type
      const aggregated = usageData?.reduce((acc, record) => {
        acc[record.resource_type] = (acc[record.resource_type] || 0) + record.quantity
        return acc
      }, {})
 
      setUsage(aggregated || {})
    } catch (error) {
      console.error('Error loading subscription:', error)
    } finally {
      setLoading(false)
    }
  }
 
  const checkLimit = (resourceType: string): boolean => {
    const plan = subscription?.subscription_plans || getFreePlanDefaults()
    const limit = plan.limits[`${resourceType}s_per_month`]
    
    if (limit === -1) return true // Unlimited
    
    const used = usage[resourceType] || 0
    return used < limit
  }
 
  return {
    subscription,
    usage,
    loading,
    checkLimit,
    refresh: loadSubscriptionData
  }
}
 
function getMonthStart(): string {
  const now = new Date()
  return new Date(now.getFullYear(), now.getMonth(), 1).toISOString()
}
 
function getFreePlanDefaults() {
  return {
    limits: {
      analyses_per_month: 3,
      max_words_per_analysis: 5000
    }
  }
}