How to fix P1016: Your raw query had an incorrect number of parameters in Prisma

// ❌ WRONG: 3 placeholders but only 2 parameters
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE age > $1
  AND status = $2
  AND created_at > $3
`, [18, 'active']; // Missing third parameter!

// ✅ CORRECT: 3 placeholders, 3 parameters
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE age > $1
  AND status = $2
  AND created_at > $3
`, [18, 'active', new Date('2024-01-01')];

// For tagged template syntax, parameters come after the SQL string

// ❌ WRONG: Missing $2, jumps from $1 to $3
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE id = $1
  OR email = $3  // Should be $2!
`, [123, '[email protected]'];

// ✅ CORRECT: Sequential numbering $1, $2
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE id = $1
  OR email = $2
`, [123, '[email protected]'];

// ❌ WRONG: Duplicate $1 placeholder
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE id = $1
  AND parent_id = $1  // Should be $2 if different value
`, [123];

// ✅ CORRECT: Use different placeholders for different values
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE id = $1
  AND parent_id = $2
`, [123, 456];

// PostgreSQL: Uses $1, $2, $3...
const pgResult = await prisma.$queryRaw`
  SELECT * FROM users WHERE id = $1 AND status = $2
`, [123, 'active'];

// MySQL: Uses ? for positional parameters
const mysqlResult = await prisma.$queryRaw(
  `SELECT * FROM users WHERE id = ? AND status = ?`,
  [123, 'active']
);

// SQLite: Also uses ? for positional parameters
const sqliteResult = await prisma.$queryRaw(
  `SELECT * FROM users WHERE id = ? AND status = ?`,
  [123, 'active']
);

// Using the wrong syntax will cause P1016
// ❌ WRONG for MySQL: Using $1 instead of ?
const wrong = await prisma.$queryRaw(
  `SELECT * FROM users WHERE id = $1`,  // Should be ?
  [123]
);

// ❌ PROBLEMATIC: Dynamic SQL with mismatched placeholders
async function searchUsers(filters: any) {
  let sql = 'SELECT * FROM users WHERE 1=1';
  const params: any[] = [];
  let paramCount = 0;

  if (filters.name) {
    sql += ` AND name = $${++paramCount}`;  // $1
    params.push(filters.name);
  }

  if (filters.age) {
    sql += ` AND age > $${++paramCount}`;  // $2
    params.push(filters.age);
  }

  if (filters.status) {
    sql += ` AND status = $${++paramCount}`;  // $3
    params.push(filters.status);
  }

  // ❌ If filters.status is undefined, we have $3 placeholder but only 2 params
  return prisma.$queryRawUnsafe(sql, ...params);
}

// ✅ CORRECT: Build SQL and params together
async function searchUsers(filters: any) {
  const conditions: string[] = [];
  const params: any[] = [];

  if (filters.name) {
    conditions.push(`name = $${params.length + 1}`);
    params.push(filters.name);
  }

  if (filters.age) {
    conditions.push(`age > $${params.length + 1}`);
    params.push(filters.age);
  }

  if (filters.status) {
    conditions.push(`status = $${params.length + 1}`);
    params.push(filters.status);
  }

  const whereClause = conditions.length > 0
    ? `WHERE ${conditions.join(' AND ')}`
    : '';

  const sql = `SELECT * FROM users ${whereClause}`;

  return prisma.$queryRawUnsafe(sql, ...params);
}

// Using $queryRaw with template literals (type-safe for PostgreSQL)
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users
  WHERE age > ${18}
  AND status = ${'active'}
`;

// Using $queryRaw with positional parameters
const users = await prisma.$queryRaw<User[]>(
  `SELECT * FROM users WHERE age > ? AND status = ?`,
  [18, 'active']
);

// Using Prisma.sql helper for complex queries
import { Prisma } from '@prisma/client';

const minAge = 18;
const status = 'active';
const limit = 10;

const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users
  WHERE age > ${minAge}
  AND status = ${status}
  LIMIT ${limit}
`;

// For dynamic WHERE clauses, use Prisma.join
const conditions = [];
if (minAge) conditions.push(Prisma.sql`age > ${minAge}`);
if (status) conditions.push(Prisma.sql`status = ${status}`);

const whereClause = conditions.length > 0
  ? Prisma.sql`WHERE ${Prisma.join(conditions, ' AND ')}`
  : Prisma.empty;

const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users
  ${whereClause}
  LIMIT ${limit}
`;

async function debugRawQuery() {
  try {
    // 1. Start with simplest possible query
    console.log('Test 1: Simple query with one parameter');
    const test1 = await prisma.$queryRaw`
      SELECT * FROM users WHERE id = $1
    `, [1];
    console.log('✓ Test 1 passed');

    // 2. Add second parameter
    console.log('Test 2: Two parameters');
    const test2 = await prisma.$queryRaw`
      SELECT * FROM users WHERE id = $1 AND status = $2
    `, [1, 'active'];
    console.log('✓ Test 2 passed');

    // 3. Test your actual query with hardcoded values
    console.log('Test 3: Your actual query structure');
    const hardcodedQuery = await prisma.$queryRaw`
      SELECT * FROM users
      WHERE age > $1
      AND status = $2
      AND created_at > $3
      ORDER BY $4
      LIMIT $5
    `, [18, 'active', new Date('2024-01-01'), 'name', 10];
    console.log('✓ Test 3 passed - query structure is valid');

    // 4. Now test with your dynamic values
    console.log('Test 4: With your actual parameters');
    const actualParams = [18, 'active', new Date('2024-01-01'), 'name', 10];
    console.log('Parameter count:', actualParams.length);

    const finalResult = await prisma.$queryRaw`
      SELECT * FROM users
      WHERE age > $1
      AND status = $2
      AND created_at > $3
      ORDER BY $4
      LIMIT $5
    `, actualParams;

    console.log('✓ All tests passed!');
    return finalResult;

  } catch (error) {
    console.error('Test failed at step:', error.message);
    // The error will show which test failed and why
    throw error;
  }
}