Supabase Project: E-commerce Store with Shopping Cart

-- Products and inventory
create table products (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  slug text unique not null,
  description text,
  price numeric(10,2) not null,
  compare_at_price numeric(10,2),
  cost_per_item numeric(10,2),
  category_id uuid references categories(id),
  sku text unique,
  barcode text,
  track_inventory boolean default true,
  inventory_quantity int default 0,
  low_stock_threshold int default 5,
  allow_backorder boolean default false,
  weight numeric(10,2),
  requires_shipping boolean default true,
  status text default 'active' check (status in ('active', 'draft', 'archived')),
  featured boolean default false,
  meta_title text,
  meta_description text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Product variants (sizes, colors, etc.)
create table product_variants (
  id uuid default gen_random_uuid() primary key,
  product_id uuid references products(id) on delete cascade not null,
  name text not null, -- e.g., "Red / Large"
  sku text unique,
  price numeric(10,2),
  compare_at_price numeric(10,2),
  inventory_quantity int default 0,
  option1 text, -- e.g., "Red"
  option2 text, -- e.g., "Large"
  option3 text,
  weight numeric(10,2),
  image_url text,
  position int default 0,
  created_at timestamp with time zone default now()
);

-- Product images
create table product_images (
  id uuid default gen_random_uuid() primary key,
  product_id uuid references products(id) on delete cascade not null,
  url text not null,
  alt_text text,
  position int default 0,
  created_at timestamp with time zone default now()
);

-- Categories
create table categories (
  id uuid default gen_random_uuid() primary key,
  name text unique not null,
  slug text unique not null,
  description text,
  parent_id uuid references categories(id),
  image_url text,
  position int default 0,
  created_at timestamp with time zone default now()
);

-- Customer profiles
create table customer_profiles (
  id uuid references auth.users on delete cascade primary key,
  email text unique not null,
  first_name text,
  last_name text,
  phone text,
  accepts_marketing boolean default false,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Addresses
create table addresses (
  id uuid default gen_random_uuid() primary key,
  customer_id uuid references customer_profiles(id) on delete cascade not null,
  first_name text not null,
  last_name text not null,
  company text,
  address1 text not null,
  address2 text,
  city text not null,
  province text,
  country text not null,
  postal_code text not null,
  phone text,
  is_default boolean default false,
  created_at timestamp with time zone default now()
);

-- Shopping cart
create table cart_items (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  session_id text, -- For guest carts
  product_id uuid references products(id) on delete cascade not null,
  variant_id uuid references product_variants(id) on delete cascade,
  quantity int not null check (quantity > 0),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  unique(user_id, product_id, variant_id),
  unique(session_id, product_id, variant_id)
);

-- Orders
create table orders (
  id uuid default gen_random_uuid() primary key,
  order_number text unique not null,
  customer_id uuid references customer_profiles(id) on delete set null,
  email text not null,
  
  -- Financial
  subtotal numeric(10,2) not null,
  tax numeric(10,2) default 0,
  shipping numeric(10,2) default 0,
  discount numeric(10,2) default 0,
  total numeric(10,2) not null,
  
  -- Payment
  payment_status text default 'pending' check (payment_status in ('pending', 'paid', 'failed', 'refunded')),
  payment_method text,
  stripe_payment_intent_id text,
  
  -- Fulfillment
  fulfillment_status text default 'unfulfilled' check (fulfillment_status in ('unfulfilled', 'partial', 'fulfilled', 'cancelled')),
  shipping_address jsonb not null,
  billing_address jsonb not null,
  tracking_number text,
  tracking_url text,
  
  -- Metadata
  notes text,
  cancelled_at timestamp with time zone,
  fulfilled_at timestamp with time zone,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Order items
create table order_items (
  id uuid default gen_random_uuid() primary key,
  order_id uuid references orders(id) on delete cascade not null,
  product_id uuid references products(id) on delete set null,
  variant_id uuid references product_variants(id) on delete set null,
  product_name text not null,
  variant_name text,
  sku text,
  quantity int not null,
  price numeric(10,2) not null,
  total numeric(10,2) not null,
  created_at timestamp with time zone default now()
);

-- Inventory transactions
create table inventory_transactions (
  id uuid default gen_random_uuid() primary key,
  product_id uuid references products(id) on delete cascade,
  variant_id uuid references product_variants(id) on delete cascade,
  quantity_change int not null,
  reason text not null,
  reference_id uuid, -- order_id or other reference
  created_at timestamp with time zone default now()
);

-- Indexes
create index idx_products_slug on products(slug);
create index idx_products_category on products(category_id);
create index idx_products_status on products(status);
create index idx_product_variants_product on product_variants(product_id);
create index idx_cart_items_user on cart_items(user_id);
create index idx_cart_items_session on cart_items(session_id);
create index idx_orders_customer on orders(customer_id);
create index idx_orders_created_at on orders(created_at desc);
create index idx_order_items_order on order_items(order_id);

-- Function to check and reserve inventory
create or replace function reserve_inventory(
  p_items jsonb -- [{product_id, variant_id, quantity}]
)
returns boolean as $$
declare
  item jsonb;
  current_stock int;
begin
  for item in select * from jsonb_array_elements(p_items)
  loop
    if (item->>'variant_id')::uuid is not null then
      -- Check variant inventory
      select inventory_quantity into current_stock
      from product_variants
      where id = (item->>'variant_id')::uuid
      for update; -- Lock row
      
      if current_stock < (item->>'quantity')::int then
        raise exception 'Insufficient stock for variant %', item->>'variant_id';
      end if;
      
      -- Reserve inventory
      update product_variants
      set inventory_quantity = inventory_quantity - (item->>'quantity')::int
      where id = (item->>'variant_id')::uuid;
    else
      -- Check product inventory
      select inventory_quantity into current_stock
      from products
      where id = (item->>'product_id')::uuid
        and track_inventory = true
      for update;
      
      if current_stock < (item->>'quantity')::int then
        raise exception 'Insufficient stock for product %', item->>'product_id';
      end if;
      
      -- Reserve inventory
      update products
      set inventory_quantity = inventory_quantity - (item->>'quantity')::int
      where id = (item->>'product_id')::uuid;
    end if;
    
    -- Log transaction
    insert into inventory_transactions (product_id, variant_id, quantity_change, reason)
    values (
      (item->>'product_id')::uuid,
      (item->>'variant_id')::uuid,
      -(item->>'quantity')::int,
      'order_reserved'
    );
  end loop;
  
  return true;
exception when others then
  raise;
end;
$$ language plpgsql security definer;

-- Function to restore inventory (on order cancellation)
create or replace function restore_inventory(p_order_id uuid)
returns void as $$
begin
  -- Restore product inventory
  update products p
  set inventory_quantity = inventory_quantity + oi.quantity
  from order_items oi
  where oi.order_id = p_order_id
    and oi.product_id = p.id
    and oi.variant_id is null
    and p.track_inventory = true;
  
  -- Restore variant inventory
  update product_variants pv
  set inventory_quantity = inventory_quantity + oi.quantity
  from order_items oi
  where oi.order_id = p_order_id
    and oi.variant_id = pv.id;
  
  -- Log transactions
  insert into inventory_transactions (product_id, variant_id, quantity_change, reason, reference_id)
  select
    product_id,
    variant_id,
    quantity,
    'order_cancelled',
    p_order_id
  from order_items
  where order_id = p_order_id;
end;
$$ language plpgsql security definer;

-- Trigger to prevent negative inventory
create or replace function check_inventory_quantity()
returns trigger as $$
begin
  if new.inventory_quantity < 0 and new.track_inventory then
    raise exception 'Inventory quantity cannot be negative';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger check_product_inventory
  before update on products
  for each row
  execute function check_inventory_quantity();

create trigger check_variant_inventory
  before update on product_variants
  for each row
  when (new.inventory_quantity < 0)
  execute function check_inventory_quantity();

// supabase/functions/create-checkout-session/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import Stripe from 'https://esm.sh/[email protected]?target=deno'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!, {
  apiVersion: '2023-10-16',
})

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

serve(async (req) => {
  try {
    const { cartItems, shippingAddress, billingAddress } = await req.json()
    
    // Get authenticated user
    const authHeader = req.headers.get('Authorization')!
    const token = authHeader.replace('Bearer ', '')
    const { data: { user }, error: authError } = await supabase.auth.getUser(token)
    
    if (authError || !user) {
      return new Response(JSON.stringify({ error: 'Unauthorized' }), { status: 401 })
    }

    // Fetch cart items with product details
    const { data: items } = await supabase
      .from('cart_items')
      .select(`
        *,
        product:products(*),
        variant:product_variants(*)
      `)
      .eq('user_id', user.id)

    if (!items || items.length === 0) {
      return new Response(JSON.stringify({ error: 'Cart is empty' }), { status: 400 })
    }

    // Calculate totals
    const subtotal = items.reduce((sum, item) => {
      const price = item.variant?.price || item.product.price
      return sum + (price * item.quantity)
    }, 0)

    const shipping = 10.00 // Simple flat rate
    const tax = subtotal * 0.1 // 10% tax
    const total = subtotal + shipping + tax

    // Create Stripe checkout session
    const session = await stripe.checkout.sessions.create({
      payment_method_types: ['card'],
      line_items: items.map(item => ({
        price_data: {
          currency: 'usd',
          product_data: {
            name: item.product.name + (item.variant ? ` - ${item.variant.name}` : ''),
            images: item.product.image_url ? [item.product.image_url] : [],
          },
          unit_amount: Math.round((item.variant?.price || item.product.price) * 100),
        },
        quantity: item.quantity,
      })),
      shipping_options: [{
        shipping_rate_data: {
          type: 'fixed_amount',
          fixed_amount: { amount: Math.round(shipping * 100), currency: 'usd' },
          display_name: 'Standard Shipping',
        },
      }],
      mode: 'payment',
      success_url: `${Deno.env.get('APP_URL')}/checkout/success?session_id={CHECKOUT_SESSION_ID}`,
      cancel_url: `${Deno.env.get('APP_URL')}/checkout/cancel`,
      client_reference_id: user.id,
      metadata: {
        userId: user.id,
        shippingAddress: JSON.stringify(shippingAddress),
        billingAddress: JSON.stringify(billingAddress),
      },
    })

    return new Response(
      JSON.stringify({ sessionId: session.id, url: session.url }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    console.error('Error creating checkout session:', error)
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500 }
    )
  }
})

// supabase/functions/stripe-webhook/index.ts
// Handle successful payments
serve(async (req) => {
  const signature = req.headers.get('stripe-signature')!
  const body = await req.text()

  let event: Stripe.Event

  try {
    event = stripe.webhooks.constructEvent(
      body,
      signature,
      Deno.env.get('STRIPE_WEBHOOK_SECRET')!
    )
  } catch (err) {
    return new Response(JSON.stringify({ error: 'Invalid signature' }), { status: 400 })
  }

  if (event.type === 'checkout.session.completed') {
    const session = event.data.object as Stripe.Checkout.Session
    
    // Create order
    const orderNumber = `ORD-${Date.now()}`
    const metadata = session.metadata!
    
    const { data: order } = await supabase
      .from('orders')
      .insert({
        order_number: orderNumber,
        customer_id: metadata.userId,
        email: session.customer_details?.email,
        subtotal: (session.amount_subtotal || 0) / 100,
        tax: (session.total_details?.amount_tax || 0) / 100,
        shipping: (session.total_details?.amount_shipping || 0) / 100,
        total: (session.amount_total || 0) / 100,
        payment_status: 'paid',
        payment_method: 'card',
        stripe_payment_intent_id: session.payment_intent,
        shipping_address: JSON.parse(metadata.shippingAddress),
        billing_address: JSON.parse(metadata.billingAddress),
      })
      .select()
      .single()

    // Get cart items
    const { data: cartItems } = await supabase
      .from('cart_items')
      .select('*, product:products(*), variant:product_variants(*)')
      .eq('user_id', metadata.userId)

    // Create order items and reserve inventory
    const orderItems = cartItems!.map(item => ({
      order_id: order.id,
      product_id: item.product_id,
      variant_id: item.variant_id,
      product_name: item.product.name,
      variant_name: item.variant?.name,
      sku: item.variant?.sku || item.product.sku,
      quantity: item.quantity,
      price: item.variant?.price || item.product.price,
      total: (item.variant?.price || item.product.price) * item.quantity,
    }))

    await supabase.from('order_items').insert(orderItems)

    // Reserve inventory
    const inventoryItems = cartItems!.map(item => ({
      product_id: item.product_id,
      variant_id: item.variant_id,
      quantity: item.quantity,
    }))

    await supabase.rpc('reserve_inventory', { p_items: inventoryItems })

    // Clear cart
    await supabase
      .from('cart_items')
      .delete()
      .eq('user_id', metadata.userId)

    console.log('Order created:', orderNumber)
  }

  return new Response(JSON.stringify({ received: true }))
})

// hooks/useCart.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface CartItem {
  id: string
  product_id: string
  variant_id?: string
  quantity: number
  product: any
  variant?: any
}

export function useCart() {
  const [items, setItems] = useState<CartItem[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadCart()

    // Subscribe to cart changes
    const channel = supabase
      .channel('cart-changes')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'cart_items',
        },
        () => loadCart()
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [])

  async function loadCart() {
    const { data: { user } } = await supabase.auth.getUser()
    
    let query = supabase
      .from('cart_items')
      .select(`
        *,
        product:products(*),
        variant:product_variants(*)
      `)

    if (user) {
      query = query.eq('user_id', user.id)
    } else {
      const sessionId = getOrCreateSessionId()
      query = query.eq('session_id', sessionId)
    }

    const { data } = await query
    setItems(data || [])
    setLoading(false)
  }

  async function addToCart(productId: string, variantId?: string, quantity = 1) {
    const { data: { user } } = await supabase.auth.getUser()
    const sessionId = getOrCreateSessionId()

    const { data, error } = await supabase
      .from('cart_items')
      .upsert({
        user_id: user?.id,
        session_id: user ? null : sessionId,
        product_id: productId,
        variant_id: variantId,
        quantity,
      })
      .select()

    if (error) throw error
    return data
  }

  async function updateQuantity(itemId: string, quantity: number) {
    if (quantity <= 0) {
      return removeFromCart(itemId)
    }

    const { error } = await supabase
      .from('cart_items')
      .update({ quantity })
      .eq('id', itemId)

    if (error) throw error
  }

  async function removeFromCart(itemId: string) {
    const { error } = await supabase
      .from('cart_items')
      .delete()
      .eq('id', itemId)

    if (error) throw error
  }

  async function clearCart() {
    const { data: { user } } = await supabase.auth.getUser()
    const sessionId = getOrCreateSessionId()

    const { error } = await supabase
      .from('cart_items')
      .delete()
      .eq(user ? 'user_id' : 'session_id', user?.id || sessionId)

    if (error) throw error
  }

  const subtotal = items.reduce((sum, item) => {
    const price = item.variant?.price || item.product.price
    return sum + (price * item.quantity)
  }, 0)

  return {
    items,
    loading,
    subtotal,
    itemCount: items.reduce((sum, item) => sum + item.quantity, 0),
    addToCart,
    updateQuantity,
    removeFromCart,
    clearCart,
  }
}

function getOrCreateSessionId(): string {
  let sessionId = localStorage.getItem('cart_session_id')
  if (!sessionId) {
    sessionId = crypto.randomUUID()
    localStorage.setItem('cart_session_id', sessionId)
  }
  return sessionId
}

// components/CartDrawer.tsx
export function CartDrawer() {
  const { items, subtotal, itemCount, updateQuantity, removeFromCart } = useCart()

  return (
    <div className="fixed right-0 top-0 h-full w-96 bg-white shadow-lg p-6">
      <h2 className="text-2xl font-bold mb-4">Cart ({itemCount})</h2>
      
      <div className="space-y-4 mb-6">
        {items.map((item) => (
          <div key={item.id} className="flex gap-4">
            <img
              src={item.product.image_url}
              alt={item.product.name}
              className="w-20 h-20 object-cover rounded"
            />
            <div className="flex-1">
              <h3 className="font-medium">{item.product.name}</h3>
              {item.variant && (
                <p className="text-sm text-gray-500">{item.variant.name}</p>
              )}
              <div className="flex items-center gap-2 mt-2">
                <button
                  onClick={() => updateQuantity(item.id, item.quantity - 1)}
                  className="px-2 py-1 border rounded"
                >
                  -
                </button>
                <span>{item.quantity}</span>
                <button
                  onClick={() => updateQuantity(item.id, item.quantity + 1)}
                  className="px-2 py-1 border rounded"
                >
                  +
                </button>
                <button
                  onClick={() => removeFromCart(item.id)}
                  className="ml-auto text-red-500"
                >
                  Remove
                </button>
              </div>
            </div>
            <div className="text-right">
              <p className="font-semibold">
                ${((item.variant?.price || item.product.price) * item.quantity).toFixed(2)}
              </p>
            </div>
          </div>
        ))}
      </div>

      <div className="border-t pt-4">
        <div className="flex justify-between mb-4">
          <span className="font-medium">Subtotal:</span>
          <span className="font-bold">${subtotal.toFixed(2)}</span>
        </div>
        <button className="w-full bg-blue-500 text-white py-3 rounded-lg">
          Proceed to Checkout
        </button>
      </div>
    </div>
  )
}

// app/(admin)/dashboard/orders/page.tsx
import { createServerSupabaseClient } from '@/lib/supabase/server'
import { format } from 'date-fns'

export default async function OrdersPage() {
  const supabase = createServerSupabaseClient()

  const { data: orders } = await supabase
    .from('orders')
    .select(`
      *,
      customer:customer_profiles(*),
      items:order_items(*)
    `)
    .order('created_at', { ascending: false })
    .limit(50)

  return (
    <div className="p-6">
      <h1 className="text-3xl font-bold mb-6">Orders</h1>

      <div className="bg-white rounded-lg shadow">
        <table className="w-full">
          <thead className="border-b">
            <tr>
              <th className="text-left p-4">Order</th>
              <th className="text-left p-4">Date</th>
              <th className="text-left p-4">Customer</th>
              <th className="text-left p-4">Total</th>
              <th className="text-left p-4">Payment</th>
              <th className="text-left p-4">Fulfillment</th>
              <th className="text-left p-4">Actions</th>
            </tr>
          </thead>
          <tbody>
            {orders?.map((order) => (
              <tr key={order.id} className="border-b hover:bg-gray-50">
                <td className="p-4">
                  <a href={`/dashboard/orders/${order.id}`} className="text-blue-600 hover:underline">
                    {order.order_number}
                  </a>
                </td>
                <td className="p-4">
                  {format(new Date(order.created_at), 'MMM d, yyyy')}
                </td>
                <td className="p-4">{order.email}</td>
                <td className="p-4 font-semibold">${order.total.toFixed(2)}</td>
                <td className="p-4">
                  <StatusBadge status={order.payment_status} />
                </td>
                <td className="p-4">
                  <StatusBadge status={order.fulfillment_status} />
                </td>
                <td className="p-4">
                  <button className="text-blue-600 hover:underline">
                    View
                  </button>
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    </div>
  )
}

function StatusBadge({ status }: { status: string }) {
  const colors = {
    paid: 'bg-green-100 text-green-800',
    pending: 'bg-yellow-100 text-yellow-800',
    failed: 'bg-red-100 text-red-800',
    fulfilled: 'bg-blue-100 text-blue-800',
    unfulfilled: 'bg-gray-100 text-gray-800',
  }

  return (
    <span className={`px-2 py-1 rounded-full text-xs font-medium ${colors[status as keyof typeof colors]}`}>
      {status}
    </span>
  )
}