// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Base model with common fields
model Base {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // For soft deletes
  deletedAt DateTime?
  
  @@map("base")
  @@index([deletedAt])
}

// Your actual models extend this pattern
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  
  posts     Post[]
  profile   Profile?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
  
  @@index([email])
  @@index([deletedAt])
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

// Explicit relation names prevent ambiguity
model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  
  authorId   String
  author     User     @relation(fields: [authorId], references: [id], name: "author_posts")
  
  comments   Comment[]
  categories Category[]
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  
  postId    String
  post      Post     @relation(fields: [postId], references: [id], name: "post_comments")
  
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], name: "author_comments")
  
  createdAt DateTime @default(now())
}

# 1. Make schema changes
# 2. Create migration (review it!)
npx prisma migrate dev --name add_user_profile

# 3. Apply to dev database
npx prisma migrate deploy

# 4. Generate client
npx prisma generate

# 5. Test thoroughly
npm run test:db

# 6. Commit migration files
git add prisma/migrations
git commit -m "Add user profile table"

# Step 1: Create migration without applying
npx prisma migrate dev --create-only --name add_user_settings

# Step 2: Review the SQL
cat prisma/migrations/*/migration.sql

# Step 3: Apply in production
npx prisma migrate deploy

# Or use the shadow database approach for large tables:
# 1. Create new table with _new suffix
# 2. Backfill data in batches
# 3. Rename tables in a transaction
# 4. Update foreign keys

const users = await prisma.user.findMany()
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id }
  })
  // This makes a separate query for each user!
}

const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true  // Single query with JOIN
  }
})

// Only load what you need
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    profile: {
      select: {
        name: true,
        avatar: true
      }
    },
    posts: {
      take: 5,
      orderBy: { createdAt: 'desc' },
      select: {
        title: true,
        published: true
      }
    }
  }
})

// Cursor-based pagination (best for infinite scroll)
async function getPaginatedPosts(cursor?: string, take = 10) {
  return await prisma.post.findMany({
    take,
    skip: cursor ? 1 : 0,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
    include: { author: true }
  })
}

// Offset pagination (simple, but less efficient for large datasets)
async function getPage(page: number, pageSize = 20) {
  return await prisma.post.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' }
  })
}

// When Prisma can't do it efficiently
const result = await prisma.$queryRaw`
  SELECT 
    u.id,
    u.email,
    COUNT(p.id) as post_count,
    AVG(CASE WHEN p.published THEN 1 ELSE 0 END) as publish_rate
  FROM users u
  LEFT JOIN posts p ON u.id = p.author_id
  GROUP BY u.id
  HAVING COUNT(p.id) > 5
  ORDER BY post_count DESC
`;

// With parameters (safe from SQL injection)
const minPosts = 5
const recentUsers = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE created_at > NOW() - INTERVAL '7 days'
  AND id IN (
    SELECT author_id FROM posts
    GROUP BY author_id
    HAVING COUNT(*) > ${minPosts}
  )
`;

// Simple transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]' }
  })
  
  await tx.profile.create({
    data: { userId: user.id, name: 'New User' }
  })
  
  await tx.welcomeEmail.create({
    data: { userId: user.id, sent: false }
  })
  
  return user
})

// Interactive transaction with conditionals
await prisma.$transaction(async (tx) => {
  const product = await tx.product.findUnique({
    where: { id: productId }
  })
  
  if (product.stock < quantity) {
    throw new Error('Insufficient stock')
  }
  
  await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } }
  })
  
  await tx.order.create({
    data: {
      productId,
      quantity,
      userId
    }
  })
})

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  console.log('🌱 Starting database seed...')
  
  // Clear existing data (development only)
  if (process.env.NODE_ENV === 'development') {
    await prisma.$transaction([
      prisma.comment.deleteMany(),
      prisma.post.deleteMany(),
      prisma.profile.deleteMany(),
      prisma.user.deleteMany(),
    ])
    console.log('✓ Cleared existing data')
  }
  
  // Create admin user
  const admin = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Admin User',
      role: 'ADMIN',
      profile: {
        create: {
          bio: 'System administrator',
          avatar: 'https://example.com/admin.jpg'
        }
      }
    }
  })
  console.log('✓ Created admin user')
  
  // Batch create test users
  const users = await Promise.all(
    Array.from({ length: 10 }).map((_, i) =>
      prisma.user.create({
        data: {
          email: `user${i + 1}@example.com`,
          name: `Test User ${i + 1}`,
          profile: {
            create: {
              bio: `Bio for user ${i + 1}`
            }
          }
        }
      })
    )
  )
  console.log(`✓ Created ${users.length} test users`)
  
  // Create posts with relationships
  for (const user of users) {
    await prisma.post.createMany({
      data: Array.from({ length: 3 }).map((_, i) => ({
        title: `Post ${i + 1} by ${user.name}`,
        content: `This is the content for post ${i + 1}`,
        published: i % 2 === 0,
        authorId: user.id
      }))
    })
  }
  console.log('✓ Created posts')
  
  console.log('✅ Seed completed successfully')
}

main()
  .catch(e => {
    console.error('❌ Seed failed:', e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

// package.json
{
  "prisma": {
    "seed": "ts-node --compiler-options {"module":"CommonJS"} prisma/seed.ts"
  }
}

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'info', 'warn', 'error']
    : ['error']
})

// Log slow queries
if (process.env.NODE_ENV === 'development') {
  prisma.$on('query' as never, (e: any) => {
    if (e.duration > 100) {
      console.warn(`Slow query (${e.duration}ms): ${e.query}`)
    }
  })
}

export default prisma

// Optimal pool size = (2 * CPU cores) + 1
// For a 4-core server: 9 connections

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=9'
    }
  }
})

// Or in your connection string:
// DATABASE_URL="postgresql://user:pass@localhost:5432/db?connection_limit=20"

// Bad: New connection pool every time
export async function getUsers() {
  const prisma = new PrismaClient()
  return await prisma.user.findMany()
}

// Good: Single instance
import { PrismaClient } from '@prisma/client'

const globalForPrisma = global as unknown as { prisma: PrismaClient }

export const prisma = globalForPrisma.prisma || new PrismaClient()

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

model Order {
  id         String   @id
  userId     String
  status     OrderStatus
  createdAt  DateTime
  
  @@index([userId])           // For finding user's orders
  @@index([status])           // For filtering by status
  @@index([createdAt])        // For time-based queries
  @@index([userId, status])   // Composite for common filters
}

await prisma.log.deleteMany({
  where: { createdAt: { lt: oneYearAgo } }
})

async function batchDeleteOldLogs(batchSize = 1000) {
  let deletedCount = 0
  
  while (true) {
    const result = await prisma.log.deleteMany({
      where: { createdAt: { lt: oneYearAgo } },
      take: batchSize
    })
    
    if (result.count === 0) break
    deletedCount += result.count
    console.log(`Deleted ${deletedCount} logs...`)
    
    // Small delay to reduce database load
    await new Promise(resolve => setTimeout(resolve, 100))
  }
  
  return deletedCount
}