import { DatabaseSync } from 'node:sqlite';
import { mo } from '../../src/index.ts';

export const openDb = mo(import.meta, (filename: string): DatabaseSync => {
  const db = new DatabaseSync(filename);
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL
    )
  `);
  return db;
});

export const insertUser = mo(import.meta, (db: DatabaseSync, name: string, email: string): number => {
  const stmt = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
  const result = stmt.run(name, email);
  return Number(result.lastInsertRowid);
});

export const getUser = mo(
  import.meta,
  (db: DatabaseSync, id: number): { id: number; name: string; email: string } | undefined => {
    const stmt = db.prepare('SELECT id, name, email FROM users WHERE id = ?');
    return stmt.get(id) as { id: number; name: string; email: string } | undefined;
  },
);

export const listUsers = mo(import.meta, (db: DatabaseSync): { id: number; name: string; email: string }[] => {
  const stmt = db.prepare('SELECT id, name, email FROM users');
  return stmt.all() as { id: number; name: string; email: string }[];
});

export const countUsers = mo(import.meta, (db: DatabaseSync): number => {
  const stmt = db.prepare('SELECT COUNT(*) as count FROM users');
  const row = stmt.get() as { count: number };
  return row.count;
});