CRUD

// insert(data) — returns EntityWithUpdate<T>
const user = await User.insert({
  email: 'joe@example.com',
  name: 'Joe',
});
console.log(user.id);         // auto-assigned
console.log(user.createdAt);  // set automatically
console.log(user.updatedAt);  // set automatically

// Returned entity has .update(), .delete(), .refresh(), .toJSON()
const updated = await user.update({ name: 'Joseph' });

// insertMany(data[]) — batch insert
// On SQLite, wrapped in a single transaction for speed
const users = await User.insertMany([
  { email: 'alice@example.com', name: 'Alice' },
  { email: 'bob@example.com', name: 'Bob' },
  { email: 'carol@example.com', name: 'Carol' },
]);
console.log(users.length); // 3

// get(filter) — returns EntityWithUpdate<T> | null
const user = await User.get({ email: 'joe@example.com' });
if (!user) {
  throw new Error('User not found');
}
console.log(user.name);

// Multiple filters (AND)
const admin = await User.get({ role: 'admin', active: true });

// getAll(filter?) — returns EntityWithUpdate<T>[]
const allUsers = await User.getAll();
const admins = await User.getAll({ role: 'admin' });

// All returned entities have .update(), .delete(), etc.
for (const user of admins) {
  await user.update({ notifiedAt: new Date().toISOString() });
}

// update(filter, data) — auto-sets updatedAt
const count = await User.update(
  { role: 'user' },
  { role: 'member' }
);
console.log(count); // number of rows updated

// update returns number of affected rows for updateMany
const affected = await User.updateMany(
  { status: 'pending' },
  { status: 'active' }
);

// delete(filter) — non-empty filter required
await User.delete({ id: 42 });

// deleteMany(filter) — returns number deleted
const count = await User.deleteMany({ role: 'guest' });
console.log(`Deleted ${count} guests`);

// upsert(data, conflictKey)
// Postgres: ON CONFLICT (email) DO UPDATE
// MySQL: ON DUPLICATE KEY UPDATE
// SQLite / MongoDB: manual get-then-insert-or-update

const user = await User.upsert(
  { email: 'joe@example.com', name: 'Joe', role: 'admin' },
  'email'   // conflict column
);

// findOrCreate(filter, defaults?) — returns { record, created }
const { record, created } = await User.findOrCreate(
  { email: 'joe@example.com' },
  { name: 'Joe', role: 'user' }  // defaults if creating
);

if (created) {
  console.log('New user:', record.id);
} else {
  console.log('Existing user:', record.id);
}

// exists(filter) — returns boolean
const taken = await User.exists({ email: 'joe@example.com' });
if (taken) {
  throw new Error('Email already registered');
}

// count(filter?) — count matching rows
const total = await User.count();
const admins = await User.count({ role: 'admin' });

// Scalar aggregates on a column
const totalSpend = await Order.sum('amount', { userId: 42 });
const avgRating  = await Review.avg('rating', { productId: 7 });
const earliest   = await Event.min('startDate');
const latest     = await Event.max('endDate');

// truncate() — deletes ALL rows. Use with care.
await TempData.truncate();

// restore(filter) — un-soft-delete rows
// Only available when the model has a @softDelete field

await User.delete({ id: 42 });          // soft-deletes
const user = await User.get({ id: 42 }); // null — filtered out

await User.restore({ id: 42 });
const back = await User.get({ id: 42 }); // found again