// ❌ THẢM HỌA #1: Fetch ALL rồi filter JS
const allUsers = await db.query("users").collect();
const activeUsers = allUsers.filter(u => u.status === "active");

// ✅ FIX: Filter ở database
const activeUsers = await db
  .query("users")
  .withIndex("by_status", q => q.eq("status", "active"))
  .collect();

// ❌ THẢM HỌA #2: N+1 Problem
const posts = await db.query("posts").take(100);
for (const post of posts) {
  post.author = await db.get(post.authorId); // 100 queries!
}

// ✅ FIX: Batch loading
const posts = await db.query("posts").take(100);
const authorIds = [...new Set(posts.map(p => p.authorId))];
const authors = await Promise.all(authorIds.map(id => db.get(id)));
const authorMap = new Map(authors.map(a => [a._id, a]));
posts.forEach(p => p.author = authorMap.get(p.authorId));

// ❌ THẢM HỌA #3: Fetch ALL để count
const allOrders = await db.query("orders").collect();
const count = allOrders.length;

// ✅ FIX: Dùng aggregation hoặc counter table
const stats = await db.query("orderStats").first();
const count = stats?.totalOrders ?? 0;

// ❌ THẢM HỌA #4: No limit
const results = await db.query("logs").collect(); // Có thể là 1 triệu records!

// ✅ FIX: Luôn có limit
const results = await db.query("logs").order("desc").take(100);

// ⚠️ WARNING: .collect() không có filter
await db.query("tableName").collect()

// ⚠️ WARNING: Loop với database calls
for (const item of items) {
  await db.get(item.relatedId);
}

// ⚠️ WARNING: Filter sau khi fetch
const data = await fetch("/api/items");
const filtered = data.filter(x => x.type === "specific");

// ⚠️ WARNING: Array.find() cho lookups
items.map(i => ({
  ...i,
  related: relatedItems.find(r => r.id === i.relatedId) // O(n²)
}));

// Schema definition với indexes
defineTable({
  userId: v.id("users"),
  status: v.string(),
  type: v.string(),
  createdAt: v.number(),
  amount: v.number(),
})
.index("by_user", ["userId"])              // Foreign key
.index("by_status", ["status"])            // Filter
.index("by_type", ["type"])                // Filter
.index("by_created", ["createdAt"])        // Sort
.index("by_user_status", ["userId", "status"])  // Compound
.index("by_status_created", ["status", "createdAt"]) // Filter + Sort

Selectivity = Unique Values / Total Records

High selectivity (tốt):
- id, email, unique fields → ~1 record
- createdAt, timestamp → phân bố đều

Low selectivity (kém):
- status (active/inactive) → 50% records mỗi value
- type (3-5 values) → 20-33% records mỗi value
- boolean fields → 50% mỗi value

// Khi có nhiều filters, ưu tiên theo selectivity
function chooseIndex(filters) {
  if (filters.id) return "by_id";           // Highest
  if (filters.userId) return "by_user";      // High
  if (filters.createdAt) return "by_created"; // Medium
  if (filters.status) return "by_status";    // Low
  return null; // Full scan - NGUY HIỂM
}

// Rule: Equality filters trước, Range/Sort sau
.index("by_status_created", ["status", "createdAt"])

// ✅ Dùng được
.withIndex("by_status_created", q => q.eq("status", "active"))
.withIndex("by_status_created", q => q.eq("status", "active").gte("createdAt", timestamp))

// ❌ Không tối ưu - chỉ dùng được phần đầu
.withIndex("by_status_created", q => q.gte("createdAt", timestamp)) // Không dùng status!

// Convex
const results = await ctx.db
  .query("items")
  .withIndex("by_created")
  .order("desc")
  .paginate(paginationOpts);

return {
  items: results.page,
  cursor: results.continueCursor,
  hasMore: !results.isDone,
};

// Prisma
const results = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastId },
  orderBy: { createdAt: 'desc' },
});

// ⚠️ Offset pagination - O(offset) complexity
// OK cho small datasets, BAD cho large
const results = await db
  .query("items")
  .order("desc")
  .take(offset + limit)
  .slice(offset);

// Performance degrades: offset=1000 → đọc 1000 records

// ✅ Constants for limits
const LIMITS = {
  DEFAULT_PAGE_SIZE: 20,
  MAX_PAGE_SIZE: 100,
  ADMIN_MAX: 500,
};

// ✅ Validate and cap
const limit = Math.min(requestedLimit || LIMITS.DEFAULT_PAGE_SIZE, LIMITS.MAX_PAGE_SIZE);

// dataloader.ts
import DataLoader from 'dataloader';

const userLoader = new DataLoader(async (ids: string[]) => {
  const users = await db.query("users")
    .filter(q => q.or(...ids.map(id => q.eq(q.field("_id"), id))))
    .collect();
  
  const userMap = new Map(users.map(u => [u._id, u]));
  return ids.map(id => userMap.get(id));
});

// Usage - automatically batches and caches
const user1 = await userLoader.load(id1);
const user2 = await userLoader.load(id2);
// → Only 1 query for both!

async function loadWithRelations(posts: Post[]) {
  // 1. Collect unique IDs
  const authorIds = [...new Set(posts.map(p => p.authorId))];
  const categoryIds = [...new Set(posts.map(p => p.categoryId))];
  
  // 2. Batch load in parallel
  const [authors, categories] = await Promise.all([
    Promise.all(authorIds.map(id => db.get(id))),
    Promise.all(categoryIds.map(id => db.get(id))),
  ]);
  
  // 3. Create lookup maps - O(1) access
  const authorMap = new Map(authors.filter(Boolean).map(a => [a._id, a]));
  const categoryMap = new Map(categories.filter(Boolean).map(c => [c._id, c]));
  
  // 4. Attach relations
  return posts.map(post => ({
    ...post,
    author: authorMap.get(post.authorId),
    category: categoryMap.get(post.categoryId),
  }));
}

// React Query / TanStack Query
const { data } = useQuery({
  queryKey: ['users', filters],
  queryFn: () => fetchUsers(filters),
  staleTime: 5 * 60 * 1000,  // Cache 5 minutes
  cacheTime: 30 * 60 * 1000, // Keep in memory 30 minutes
});

// SWR
const { data } = useSWR(['users', filters], fetcher, {
  revalidateOnFocus: false,
  dedupingInterval: 60000,
});

// ✅ Use indexes
.withIndex("by_field", q => q.eq("field", value))

// ✅ Pagination
.paginate(paginationOpts)

// ✅ Limit results
.take(100)

// ❌ Avoid
.collect() // without filter
.filter() // JS filter after fetch

// ✅ Select only needed fields
const users = await prisma.user.findMany({
  select: { id: true, name: true, email: true },
  where: { status: 'active' },
  take: 20,
});

// ✅ Include relations efficiently
const posts = await prisma.post.findMany({
  include: {
    author: { select: { id: true, name: true } },
  },
  take: 20,
});

// ❌ Avoid
findMany() // without where/take
include: { author: true } // full object when only need name

// ✅ Use compound queries
const q = query(
  collection(db, "posts"),
  where("status", "==", "published"),
  orderBy("createdAt", "desc"),
  limit(20)
);

// ✅ Pagination with cursors
const next = query(q, startAfter(lastDoc));

// ❌ Avoid
getDocs(collection(db, "posts")) // fetches ALL documents

// ✅ Projection - only needed fields
db.users.find(
  { status: "active" },
  { projection: { name: 1, email: 1 } }
).limit(20);

// ✅ Covered queries - index covers all fields
db.users.createIndex({ status: 1, name: 1, email: 1 });

// ✅ Aggregation for complex queries
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$userId", total: { $sum: "$amount" } } },
  { $limit: 100 }
]);

// ❌ Avoid
db.users.find() // no filter, no limit

Convex Dashboard:
- Functions → Sort by "Database Bandwidth"
- Set alert: bandwidth > X GB/day

Firebase:
- Usage & billing → Set budget alerts
- Firestore → Monitor read/write counts

AWS/GCP:
- CloudWatch/Cloud Monitoring alerts
- Budget alerts with thresholds

// Add timing to critical queries
async function profiledQuery(name: string, queryFn: () => Promise<any>) {
  const start = Date.now();
  const result = await queryFn();
  const duration = Date.now() - start;
  
  console.log(`[Query] ${name}: ${duration}ms, ${result.length} results`);
  
  if (duration > 1000) {
    console.warn(`[SLOW QUERY] ${name} took ${duration}ms`);
  }
  
  return result;
}

Estimate formula:
Bandwidth = Records × Average_Record_Size × Requests_per_Day

Example:
- 10,000 records × 1KB × 1000 requests = 10 GB/day
- With proper filtering (100 records): 0.1 GB/day
- Savings: 99%!

□ Search for .collect(), .findAll(), .toArray() without filters
□ Search for database calls inside loops
□ Check for Array.find() used for lookups (should be Map)
□ Verify all queries have limits
□ Check indexes exist for all filtered/sorted fields

□ All queries use indexes
□ All list queries have pagination
□ All queries have reasonable limits
□ No N+1 patterns
□ Batch loading for relations
□ Monitoring/alerts configured

□ Estimate daily bandwidth for each endpoint
□ Calculate cost at 10x, 100x current traffic
□ Set budget alerts before launch
□ Document expected costs in PR