where()

// where(column, op, value)
const admins = await User.query()
  .where('role', '=', 'admin')
  .get();

// All OpComparison operators:
// = != < <= > >= LIKE ILIKE

// Chained .where() calls are AND-ed together
const results = await User.query()
  .where('role', '=', 'admin')
  .where('active', '=', true)
  .where('createdAt', '>', '2024-01-01')
  .get();
// WHERE role = 'admin' AND active = 1 AND createdAt > '2024-01-01'

// .orWhere(column, op, value) — OR connector
const results = await User.query()
  .where('role', '=', 'admin')
  .orWhere('role', '=', 'superuser')
  .get();
// WHERE role = 'admin' OR role = 'superuser'

// .whereRaw(sql, params?) — parameterized raw SQL
// params is optional (BUG FIX #3: now correctly accepts 2nd arg)
const results = await User.query()
  .whereRaw('LOWER(email) = ?', ['joe@example.com'])
  .get();

// No params:
const results2 = await Post.query()
  .whereRaw('publishedAt IS NOT NULL')
  .get();

// .whereIn(column, values[])
const users = await User.query()
  .whereIn('role', ['admin', 'superuser', 'moderator'])
  .get();

// .whereNotIn(column, values[])
const others = await User.query()
  .whereNotIn('status', ['banned', 'suspended'])
  .get();

// .whereNull(column)
const unverified = await User.query()
  .whereNull('verifiedAt')
  .get();

// .whereNotNull(column)
const verified = await User.query()
  .whereNotNull('verifiedAt')
  .get();

// .whereBetween(column, min, max)
const recentPosts = await Post.query()
  .whereBetween('createdAt', '2024-01-01', '2024-12-31')
  .get();

const midRange = await Product.query()
  .whereBetween('price', 100, 500)
  .get();

// .ILike(column, pattern) — case-insensitive LIKE
// SQLite: LIKE (already case-insensitive for ASCII)
// Postgres: ILIKE
// MySQL: LIKE (case-insensitive by default collation)

const matches = await User.query()
  .ILike('name', '%joe%')
  .get();
// Finds "Joe", "JOEY", "joe smith", etc.

// Dot-notation: "table.column" after a manual join
// avoids ambiguity on shared column names

const posts = await Post.query()
  .join('users', 'posts.userId', '=', 'users.id')
  .where('users.role', '=', 'admin')  // qualifies to users table
  .get();

// Without a join, bare "column" targets the root table