import { DatabaseSync } from "node:sqlite";

const database = new DatabaseSync("app.sqlite", { timeout: 5000 });

try {
  database.exec(`
    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY,
      title TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'open'
    ) STRICT
  `);

  const upsert = database.prepare(`
    INSERT INTO tasks (id, title, status)
    VALUES (?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      title = excluded.title,
      status = excluded.status
  `);

  upsert.run(1, "ship SQLite connection", "open");
  upsert.run(2, "write follow-up query", "done");

  const summary = database.prepare(`
    SELECT COUNT(*) AS task_count,
           SUM(status = 'open') AS open_count
    FROM tasks
  `).get();

  const nextTask = database.prepare(`
    SELECT id, title
    FROM tasks
    WHERE status = ?
    ORDER BY id
    LIMIT 1
  `).get("open");

  console.log(`database=${database.location()}`);
  console.log(`tasks=${summary.task_count}`);
  console.log(`open=${summary.open_count}`);
  console.log(`next=${nextTask.id}:${nextTask.title}`);
} finally {
  database.close();
}