// ❌ Classic N+1: fetching blog posts and their authors
// This looks innocent. The ORM hides what it is doing.

const posts = await prisma.post.findMany({ take: 100 });
// → Query 1: SELECT * FROM posts LIMIT 100

for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
  // → Query 2: SELECT * FROM users WHERE id = 1
  // → Query 3: SELECT * FROM users WHERE id = 2
  // → Query 4: SELECT * FROM users WHERE id = 3
  // ... 100 more queries
  console.log(`${post.title} by ${author.name}`);
}
// Total: 101 queries. In dev: feels fine (sub-ms each).
// In production with real latency: 101 × 5ms = 505ms for one page load.

Development database:
  SELECT * FROM users WHERE id = 1  →  0.3ms  (localhost, warm cache)
  × 100 queries                      →  30ms total
  → "Feels fast. Ship it."

Production database:
  SELECT * FROM users WHERE id = 1  →  4ms  (network RTT + connection overhead)
  × 100 queries                      →  400ms total
  → "The blog page is slow." (one week after launch)

// Pattern A — N+1 (wrong)
// Prisma emits: SELECT * FROM posts (1 query)
// Then per post: SELECT * FROM users WHERE id = $1 (N queries)
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

// Pattern B — Two queries (correct, but not a JOIN)
// Prisma emits:
//   Query 1: SELECT * FROM posts
//   Query 2: SELECT * FROM users WHERE id IN (1, 2, 3, ...)
// This is what Prisma does when relationLoadStrategy = "query"
const posts = await prisma.post.findMany({
  include: { author: true },  // With relationLoadStrategy: "query" (Prisma 5.x default)
});

// Pattern C — Single JOIN query (most efficient for small relations)
// Prisma emits:
//   SELECT posts.*, users.* FROM posts LEFT JOIN users ON users.id = posts.authorId
// This is what Prisma does when relationLoadStrategy = "join"
const posts = await prisma.post.findMany({
  include: { author: true },
  // With: { prisma.$extends({ ... relationLoadStrategy: "join" }) }
});

// ✅ Prisma 5.x: control the query strategy per-query
const posts = await prisma.post.findMany({
  relationLoadStrategy: 'join',  // Single JOIN query — best for one-to-one, one-to-many
  include: { author: true },
});

const posts = await prisma.post.findMany({
  relationLoadStrategy: 'query',  // Two queries — best for large many-to-many
  include: { tags: true },         // avoids Cartesian explosion on many-to-many
});

// TypeORM example — lazy loading with Promise-based relations
// Entity definition
@Entity()
class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User, { lazy: true })  // lazy relation
  author: Promise<User>;  // Promise signals lazy loading
}

// ❌ This looks like a property access — it fires a SELECT
const post = await postRepository.findOne({ where: { id: 1 } });
// → Query 1: SELECT * FROM posts WHERE id = 1

const author = await post.author;
// → Query 2: SELECT * FROM users WHERE id = <post.authorId>
// ↑ Hidden SQL. Not obvious at the call site. Multiplied by N in a loop.

// ❌ Extremely dangerous: lazy loading inside JSON.stringify (or Express res.json())
// res.json() calls .toJSON() which triggers property access on all lazy relations

app.get('/posts', async (req, res) => {
  const posts = await postRepository.find();
  // ↑ Query 1: SELECT * FROM posts (100 rows)

  res.json(posts);
  // ↑ res.json() serializes each post
  //   Each post.author access → SELECT * FROM users WHERE id = $1
  //   → 100 additional queries — COMPLETELY INVISIBLE in the controller code
});

// ✅ Prisma: one author per post — single JOIN or two queries (no Cartesian explosion)
const posts = await prisma.post.findMany({
  include: {
    author: true,      // one-to-one → safe with JOIN
    comments: true,    // one-to-many → safe with JOIN (bounded by post count)
  },
});
// Generated SQL (join strategy):
// SELECT posts.*, users.*, comments.*
//   FROM posts
//   LEFT JOIN users ON users.id = posts.author_id
//   LEFT JOIN comments ON comments.post_id = posts.id

// ❌ Many-to-many with JOIN: Cartesian explosion
// posts × tags = (100 posts) × (avg 20 tags each) = 2000 rows returned
// Then Prisma deduplicates in application memory → expensive for large result sets
const posts = await prisma.post.findMany({
  relationLoadStrategy: 'join',
  include: { tags: true },  // many-to-many via join table
});
// SQL emits: SELECT posts.*, tags.*
//              FROM posts
//              JOIN _PostToTag ON ...
//              JOIN tags ON ...
// Returns 2000 rows for 100 posts — deduplicated by Prisma, but 2000 rows traversed

// ✅ Many-to-many with two queries: no Cartesian explosion
const posts = await prisma.post.findMany({
  relationLoadStrategy: 'query',  // Query 1: posts, Query 2: tags WHERE post_id IN (...)
  include: { tags: true },
});
// Query 1: SELECT * FROM posts → 100 rows
// Query 2: SELECT * FROM tags WHERE post_id IN (1,2,...,100) → ≤ 2000 rows
// Prisma joins in memory, but only reads 2000 rows (not 2000 × columns of posts)

import DataLoader from 'dataloader';

// ✅ Create a DataLoader per request (not per module — not a singleton)
function createUserLoader() {
  return new DataLoader<number, User>(async (userIds) => {
    // DataLoader coalesces all calls within the same event-loop tick
    // into a single batch call with all requested IDs
    const users = await prisma.user.findMany({
      where: { id: { in: userIds as number[] } },
    });

    // Must return results in the SAME ORDER as userIds
    const userMap = new Map(users.map((u) => [u.id, u]));
    return userIds.map((id) => userMap.get(id) ?? new Error(`User ${id} not found`));
  });
}

// Usage in Express middleware — one loader per request
app.use((req, res, next) => {
  req.loaders = {
    user: createUserLoader(),
  };
  next();
});

// In a resolver or controller — looks like individual lookups, batched automatically
async function getPostWithAuthor(postId: number, loaders: Loaders) {
  const post = await prisma.post.findUnique({ where: { id: postId } });
  const author = await loaders.user.load(post!.authorId);
  // If 100 posts are loaded in parallel, all 100 loaders.user.load() calls
  // are coalesced into ONE: SELECT * FROM users WHERE id IN (...)
  return { ...post, author };
}

Single event-loop tick:
  t=0ms: Request A calls loaders.user.load(1)
  t=0ms: Request B calls loaders.user.load(2)
  t=0ms: Request C calls loaders.user.load(1)  ← duplicate
  t=0ms: DataLoader tick boundary — batch fires
    → SELECT * FROM users WHERE id IN (1, 2)  ← 1 query for 3 calls
    → cache deduplicates id=1 (returned from cache to both A and C)
  t=4ms: all three requests receive their User objects

// TypeORM EntityManager: the Identity Map pattern
const entityManager = dataSource.createEntityManager();

const user1 = await entityManager.findOne(User, { where: { id: 1 } });
const user2 = await entityManager.findOne(User, { where: { id: 1 } });

console.log(user1 === user2);  // true — same object reference
// EntityManager tracks every loaded entity. A second load of the same PK
// returns the cached object — no second SQL query.

// ❌ Long-running workers: the Identity Map grows forever
// A worker that processes 100K events — EntityManager loads 100K User objects
// and retains all of them in memory.
async function processEvents(events: Event[]) {
  const em = dataSource.createEntityManager();
  for (const event of events) {
    await em.findOne(User, { where: { id: event.userId } });
    // em retains every loaded User — memory grows with each iteration
  }
}

// ✅ Clear the Identity Map periodically in long-running workers
async function processEvents(events: Event[]) {
  const em = dataSource.createEntityManager();
  for (let i = 0; i < events.length; i++) {
    await em.findOne(User, { where: { id: events[i].userId } });
    if (i % 1000 === 0) {
      em.clear();  // Release all tracked entities — prevents memory leak
    }
  }
}

-- PostgreSQL: log all queries that take longer than 0ms (every query)
-- Set in postgresql.conf or per session for staging environments
log_min_duration_statement = 0   -- logs every query with its duration
log_statement = 'all'            -- also logs queries with zero duration

-- Pattern to look for in pg_log:
-- Many near-identical queries with different parameter values in rapid succession:
-- 2026-09-06 12:00:00 LOG: duration: 2.1ms  statement: SELECT * FROM users WHERE id = 1
-- 2026-09-06 12:00:00 LOG: duration: 2.3ms  statement: SELECT * FROM users WHERE id = 2
-- 2026-09-06 12:00:00 LOG: duration: 1.9ms  statement: SELECT * FROM users WHERE id = 3
-- ↑ This pattern at high frequency = N+1 in production

// Prisma: enable query logging in development/staging
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
  ],
});

prisma.$on('query', (e) => {
  console.log(`Query: ${e.query}`);
  console.log(`Duration: ${e.duration}ms`);
});

// ✅ Slow query threshold in staging:
// Set log_min_duration_statement = 100 (log queries > 100ms)
// An N+1 generating 100 individual queries of 5ms each won't be caught here
// → Use log_min_duration_statement = 0 for N+1 detection specifically