# Database Optimization - Quick Start Guide

## 1. Apply Indexes (5 minutes)

Run this SQL directly on your PostgreSQL database:

```bash
# Option A: Using psql
psql -U username -d craft2prints -f DATABASE_OPTIMIZATION_MIGRATION.sql

# Option B: Using Prisma
npx prisma db execute --stdin < DATABASE_OPTIMIZATION_MIGRATION.sql
```

**Result:** Queries will be 10-100x faster for large datasets!

---

## 2. Add Pagination to Critical Pages (15 minutes)

### Example: Admin Orders Page

**File:** `src/app/admin/orders/page.tsx`

```typescript
import Link from 'next/link';
import { prisma } from '@/lib/prisma';
import AdminNav from '@/components/AdminNav';

export default async function AdminOrdersPage({ 
  searchParams 
}: { 
  searchParams: { page?: string } 
}) {
  const page = Math.max(1, parseInt(searchParams.page || '1'));
  const pageSize = 50;
  const skip = (page - 1) * pageSize;

  // Fetch orders AND total count in parallel
  const [orders, total] = await Promise.all([
    prisma.order.findMany({
      select: {
        id: true,
        email: true,
        total: true,
        status: true,
        createdAt: true,
        items: {
          select: {
            productName: true,
            quantity: true
          }
        }
      },
      orderBy: { createdAt: 'desc' },
      skip,
      take: pageSize
    }),
    prisma.order.count()
  ]);

  const totalPages = Math.ceil(total / pageSize);

  return (
    <div className="mx-auto max-w-6xl px-6 py-12">
      <AdminNav />
      <h1 className="font-display text-3xl mb-8">Orders</h1>

      {/* Orders List */}
      <div className="divide-y divide-line border-t border-line">
        {orders.map((order) => (
          <div key={order.id} className="py-4 flex justify-between items-center">
            <div>
              <p className="font-medium">{order.id}</p>
              <p className="text-sm text-muted">{order.email}</p>
            </div>
            <div className="text-right">
              <p className="font-mono">£{(order.total / 100).toFixed(2)}</p>
              <p className="text-xs text-muted">{order.status}</p>
            </div>
          </div>
        ))}
      </div>

      {/* Pagination */}
      <div className="mt-8 flex justify-center gap-2">
        {page > 1 && (
          <Link
            href={`/admin/orders?page=${page - 1}`}
            className="px-4 py-2 border border-line hover:border-clay"
          >
            ← Previous
          </Link>
        )}

        <div className="flex items-center gap-1">
          {Array.from({ length: Math.min(5, totalPages) }).map((_, i) => {
            const pageNum = i + 1;
            return (
              <Link
                key={pageNum}
                href={`/admin/orders?page=${pageNum}`}
                className={`px-3 py-2 border ${
                  page === pageNum
                    ? 'border-clay bg-clay text-paper'
                    : 'border-line hover:border-clay'
                }`}
              >
                {pageNum}
              </Link>
            );
          })}
        </div>

        {page < totalPages && (
          <Link
            href={`/admin/orders?page=${page + 1}`}
            className="px-4 py-2 border border-line hover:border-clay"
          >
            Next →
          </Link>
        )}
      </div>

      <p className="text-sm text-muted text-center mt-4">
        Showing {skip + 1}-{Math.min(skip + pageSize, total)} of {total} orders
      </p>
    </div>
  );
}
```

---

## 3. Optimize Existing Queries (10 minutes)

### Admin Products Page

**Before (Slow):**
```typescript
const products = await prisma.product.findMany();
// Fetches: id, slug, name, category, colors (large JSON), colorPhotos (huge!), etc.
// For 1000 products: ~10MB data transferred
```

**After (Fast):**
```typescript
const products = await prisma.product.findMany({
  select: {
    id: true,
    slug: true,
    name: true,
    category: true,
    basePrice: true,
    showOnProducts: true,
    showOnPersonalised: true
    // Skip large JSON fields
  },
  take: 50,
  skip: (page - 1) * 50
});
// For 1000 products: ~100KB data transferred (100x smaller!)
```

### Orders with Items

**Before (N+1 problem):**
```typescript
const orders = await prisma.order.findMany();
// 1 query

orders.forEach(order => {
  const items = await prisma.orderItem.findMany({
    where: { orderId: order.id }
  });
  // N more queries (if 1000 orders, that's 1001 queries!)
});
```

**After (Single query):**
```typescript
const orders = await prisma.order.findMany({
  include: { items: true }  // Join in single query
});
// Just 1 query instead of 1001!
```

---

## 4. Add Caching (20 minutes)

### Install Node Cache

```bash
npm install node-cache
```

### Create Cache Utility

**File:** `lib/cache.ts`

```typescript
import NodeCache from 'node-cache';

// Cache with 10 minute TTL
const cache = new NodeCache({ stdTTL: 600 });

export async function getCachedProducts() {
  // Check cache first
  const cached = cache.get('all_products');
  if (cached) return cached;

  // If not cached, fetch from DB
  const products = await prisma.product.findMany({
    select: { id: true, name: true, slug: true, basePrice: true }
  });

  // Store in cache
  cache.set('all_products', products);
  return products;
}

export async function getCachedCategories() {
  const cached = cache.get('categories');
  if (cached) return cached;

  const categories = await prisma.category.findMany({
    orderBy: { name: 'asc' }
  });

  cache.set('categories', categories);
  return categories;
}

// Clear cache when data changes
export function invalidateCache(key: string) {
  cache.del(key);
}
```

### Use Cache in Pages

```typescript
import { getCachedProducts } from '@/lib/cache';

export default async function ProductsPage() {
  const products = await getCachedProducts(); // Uses cache if available
  // ...
}
```

### Invalidate on Update

```typescript
import { invalidateCache } from '@/lib/cache';

export async function updateProduct(id: string, data: any) {
  await prisma.product.update({ where: { id }, data });
  invalidateCache('all_products'); // Clear cache
}
```

---

## 5. Monitor Performance (10 minutes)

### Add Query Logging

**File:** `lib/prisma.ts`

```typescript
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' }
  ]
});

prisma.$on('query', (e) => {
  if (e.duration > 1000) {
    // Log slow queries (>1 second)
    console.warn(`SLOW QUERY [${e.duration}ms]: ${e.query}`);
  }
});

export default prisma;
```

---

## 6. Performance Checklist

- [ ] Run `DATABASE_OPTIMIZATION_MIGRATION.sql` to add indexes
- [ ] Add pagination to `/admin/orders` page (50 items per page)
- [ ] Add pagination to `/admin/products` page (50 items per page)
- [ ] Add pagination to `/admin/designs` page (50 items per page)
- [ ] Optimize product queries to use `select` instead of fetching all fields
- [ ] Install and configure `node-cache` for frequent queries
- [ ] Cache categories, collections, and products
- [ ] Add query duration logging to identify slow queries
- [ ] Set database connection timeout to 30 seconds
- [ ] Schedule weekly `VACUUM ANALYZE` task

---

## Expected Performance Improvements

| Change | Impact | Effort |
|--------|--------|--------|
| Add indexes | **40-60x faster** | 5 min |
| Pagination (50 items) | **20-30x faster** | 15 min each page |
| Use `select` field | **10-20x less data** | 10 min per query |
| Redis caching | **100-1000x for cached** | 20 min |
| Query logging | **Identify bottlenecks** | 5 min |

**Total effort: ~2 hours**  
**Performance gain: 50-100x for most operations**

---

## Troubleshooting

### Pagination not working?
```typescript
// Make sure to use BOTH skip and take
skip: (page - 1) * pageSize,
take: pageSize
```

### Indexes not helping?
```bash
# Rebuild indexes
REINDEX INDEX index_name;

# Analyze tables
ANALYZE;
```

### Queries still slow?
```typescript
// Add timing logs
const start = Date.now();
const result = await prisma.order.findMany({...});
console.log(`Query took ${Date.now() - start}ms`);
```

---

## Next: Advanced Optimizations

Once these are done, consider:
1. Read replicas for reporting queries
2. Denormalization for frequently joined data
3. Archiving old orders to separate table
4. Full-text search indexes for product search
5. Materialized views for dashboard data