Subscription System

-- Products table
CREATE TABLE products (
  id TEXT PRIMARY KEY,
  active BOOLEAN,
  name TEXT,
  description TEXT,
  image TEXT,
  metadata JSONB
);

-- Prices table
CREATE TABLE prices (
  id TEXT PRIMARY KEY,
  product_id TEXT REFERENCES products(id),
  active BOOLEAN,
  description TEXT,
  unit_amount INTEGER,
  currency TEXT,
  type TEXT,
  interval TEXT,
  interval_count INTEGER,
  trial_period_days INTEGER,
  metadata JSONB
);

-- Subscriptions table
CREATE TABLE subscriptions (
  id TEXT PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  status TEXT,
  metadata JSONB,
  price_id TEXT REFERENCES prices(id),
  quantity INTEGER,
  cancel_at_period_end BOOLEAN,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  ended_at TIMESTAMP WITH TIME ZONE,
  cancel_at TIMESTAMP WITH TIME ZONE,
  canceled_at TIMESTAMP WITH TIME ZONE,
  trial_start TIMESTAMP WITH TIME ZONE,
  trial_end TIMESTAMP WITH TIME ZONE
);

// Service for managing Stripe interactions
import Stripe from 'stripe';
import { createClient } from '@supabase/supabase-js';
import { Database } from '../types_db';

export const stripe = new Stripe(process.env.STRIPE_SECRET_KEY as string, {
  apiVersion: '2022-11-15',
  typescript: true
});

export const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL as string,
  process.env.SUPABASE_SERVICE_ROLE_KEY as string
);

// Example: Create a checkout session
export async function createCheckoutSession(
  userId: string,
  priceId: string,
  redirectTo: string
) {
  try {
    const customer = await getOrCreateCustomer(userId);
    
    const session = await stripe.checkout.sessions.create({
      payment_method_types: ['card'],
      billing_address_collection: 'required',
      customer: customer.id,
      line_items: [
        {
          price: priceId,
          quantity: 1
        }
      ],
      mode: 'subscription',
      allow_promotion_codes: true,
      subscription_data: {
        trial_from_plan: true,
        metadata: {}
      },
      success_url: `${redirectTo}/dashboard?session_id={CHECKOUT_SESSION_ID}`,
      cancel_url: `${redirectTo}/pricing`
    });
    
    return { sessionId: session.id };
  } catch (error) {
    console.log(error);
    throw error;
  }
}

// Helper: Get or create a Stripe customer for a user
async function getOrCreateCustomer(userId: string) {
  const { data: customerData } = await supabase
    .from('customers')
    .select('stripe_customer_id')
    .eq('id', userId)
    .single();
  
  if (customerData?.stripe_customer_id) {
    return { id: customerData.stripe_customer_id };
  }
  
  // Retrieve user data from Supabase
  const { data: userData } = await supabase
    .from('users')
    .select('email')
    .eq('id', userId)
    .single();
  
  // Create a new customer in Stripe
  const customer = await stripe.customers.create({
    email: userData?.email,
    metadata: {
      supabaseUUID: userId
    }
  });
  
  // Store the customer ID in Supabase
  await supabase
    .from('customers')
    .insert([{ id: userId, stripe_customer_id: customer.id }]);
  
  return customer;
}

export async function processStripeWebhook(event: Stripe.Event) {
  switch (event.type) {
    case 'customer.subscription.created':
    case 'customer.subscription.updated':
      const subscription = event.data.object as Stripe.Subscription;
      await updateSubscriptionInDatabase(subscription);
      break;
    case 'customer.subscription.deleted':
      const deletedSubscription = event.data.object as Stripe.Subscription;
      await markSubscriptionAsDeleted(deletedSubscription);
      break;
    case 'invoice.paid':
      const invoice = event.data.object as Stripe.Invoice;
      await handleSuccessfulPayment(invoice);
      break;
    case 'invoice.payment_failed':
      const failedInvoice = event.data.object as Stripe.Invoice;
      await handleFailedPayment(failedInvoice);
      break;
    default:
      console.log(`Unhandled event type: ${event.type}`);
  }
}

export async function createPortalLink(customerId: string, returnUrl: string) {
  try {
    const portalSession = await stripe.billingPortal.sessions.create({
      customer: customerId,
      return_url: returnUrl
    });
    
    return { url: portalSession.url };
  } catch (error) {
    console.log(error);
    throw error;
  }
}

// Custom hook for subscription status
import { useEffect, useState } from 'react';
import { useUser } from '@/utils/useUser';

export const useSubscriptionStatus = () => {
  const { user } = useUser();
  const [subscription, setSubscription] = useState<any>(null);
  const [loading, setLoading] = useState<boolean>(true);
  
  useEffect(() => {
    if (!user) {
      setLoading(false);
      return;
    }
    
    async function fetchSubscription() {
      try {
        const res = await fetch('/api/subscription-status');
        const data = await res.json();
        setSubscription(data);
      } catch (error) {
        console.error('Error fetching subscription:', error);
      } finally {
        setLoading(false);
      }
    }
    
    fetchSubscription();
  }, [user]);
  
  return {
    subscription,
    loading,
    isPro: subscription?.plan === 'pro',
    isBusiness: subscription?.plan === 'business',
    isActive: subscription?.status === 'active' || subscription?.status === 'trialing'
  };
};

# Stripe API configuration
NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY=pk_test_xxxxxxxxxxxxxxxxxxxxxxxxxx
STRIPE_SECRET_KEY=sk_test_xxxxxxxxxxxxxxxxxxxxxxxxxx
STRIPE_WEBHOOK_SECRET=whsec_xxxxxxxxxxxxxxxxxxxxxxxxxx

# Product and price IDs
NEXT_PUBLIC_STRIPE_PRO_PRICE_ID=price_xxxxxxxxxxxxxxxxxx
NEXT_PUBLIC_STRIPE_BUSINESS_PRICE_ID=price_xxxxxxxxxxxxxxxxxx