1 files changed, 160 insertions, 0 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
new file mode 100644
index 0000000..7c31a18
--- /dev/null
+++ b/app/lib/db.server.ts
@@ -0,0 +1,160 @@
+import Database from "better-sqlite3";
+import path from "path";
+
+let db: Database.Database | null = null;
+
+export function getDb(): Database.Database {
+  if (!db) {
+    const dbPath = process.env.DB_PATH ?? path.resolve("todo.db");
+    db = new Database(dbPath);
+    db.pragma("journal_mode = WAL");
+    db.pragma("foreign_keys = ON");
+    initSchema(db);
+  }
+  return db;
+}
+
+function initSchema(db: Database.Database) {
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS users (
+      id         TEXT PRIMARY KEY,
+      username   TEXT UNIQUE NOT NULL,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+
+    CREATE TABLE IF NOT EXISTS credentials (
+      id         TEXT PRIMARY KEY,
+      user_id    TEXT NOT NULL REFERENCES users(id),
+      public_key TEXT NOT NULL,
+      counter    INTEGER NOT NULL DEFAULT 0,
+      transports TEXT
+    );
+
+    CREATE TABLE IF NOT EXISTS todos (
+      id         TEXT PRIMARY KEY,
+      user_id    TEXT NOT NULL REFERENCES users(id),
+      title      TEXT NOT NULL,
+      done       INTEGER NOT NULL DEFAULT 0,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+    CREATE INDEX IF NOT EXISTS idx_todos_user ON todos(user_id, created_at DESC);
+  `);
+}
+
+/* ── Users ── */
+
+export interface User {
+  id: string;
+  username: string;
+  created_at: string;
+}
+
+export function getUserById(id: string): User | null {
+  return getDb().prepare("SELECT * FROM users WHERE id = ?").get(id) as User | null;
+}
+
+export function getUserByUsername(username: string): User | null {
+  return getDb().prepare("SELECT * FROM users WHERE username = ?").get(username) as User | null;
+}
+
+export function upsertUser(id: string, username: string): User {
+  getDb()
+    .prepare("INSERT OR IGNORE INTO users (id, username) VALUES (?, ?)")
+    .run(id, username);
+  return getUserById(id) ?? getUserByUsername(username)!;
+}
+
+/* ── Credentials ── */
+
+export interface Credential {
+  id: string;
+  user_id: string;
+  public_key: string;
+  counter: number;
+  transports: string | null;
+}
+
+export function hasAnyCredential(): boolean {
+  return (getDb().prepare("SELECT 1 FROM credentials LIMIT 1").get() as unknown) != null;
+}
+
+export function listCredentials(): Credential[] {
+  return getDb().prepare("SELECT * FROM credentials").all() as Credential[];
+}
+
+export function getCredentialById(id: string): Credential | null {
+  return getDb().prepare("SELECT * FROM credentials WHERE id = ?").get(id) as Credential | null;
+}
+
+export function saveCredential(input: {
+  id: string;
+  userId: string;
+  publicKey: Uint8Array;
+  counter: number;
+  transports?: string[];
+}): void {
+  getDb()
+    .prepare(
+      "INSERT INTO credentials (id, user_id, public_key, counter, transports) VALUES (?, ?, ?, ?, ?)"
+    )
+    .run(
+      input.id,
+      input.userId,
+      Buffer.from(input.publicKey).toString("base64url"),
+      input.counter,
+      input.transports ? JSON.stringify(input.transports) : null
+    );
+}
+
+export function updateCredentialCounter(id: string, counter: number): void {
+  getDb().prepare("UPDATE credentials SET counter = ? WHERE id = ?").run(counter, id);
+}
+
+/* ── Todos ── */
+
+export interface Todo {
+  id: string;
+  user_id: string;
+  title: string;
+  done: number;
+  created_at: string;
+}
+
+export function listTodos(userId: string, filter?: string): Todo[] {
+  if (filter === "active") {
+    return getDb()
+      .prepare("SELECT * FROM todos WHERE user_id = ? AND done = 0 ORDER BY created_at DESC")
+      .all(userId) as Todo[];
+  }
+  if (filter === "done") {
+    return getDb()
+      .prepare("SELECT * FROM todos WHERE user_id = ? AND done = 1 ORDER BY created_at DESC")
+      .all(userId) as Todo[];
+  }
+  return getDb()
+    .prepare("SELECT * FROM todos WHERE user_id = ? ORDER BY created_at DESC")
+    .all(userId) as Todo[];
+}
+
+export function createTodo(userId: string, title: string): void {
+  getDb()
+    .prepare("INSERT INTO todos (id, user_id, title) VALUES (?, ?, ?)")
+    .run(crypto.randomUUID(), userId, title);
+}
+
+export function toggleTodo(id: string, userId: string, done: boolean): void {
+  getDb()
+    .prepare("UPDATE todos SET done = ? WHERE id = ? AND user_id = ?")
+    .run(done ? 1 : 0, id, userId);
+}
+
+export function deleteTodo(id: string, userId: string): void {
+  getDb().prepare("DELETE FROM todos WHERE id = ? AND user_id = ?").run(id, userId);
+}
+
+export function countActiveTodos(userId: string): number {
+  const row = getDb()
+    .prepare("SELECT COUNT(*) as n FROM todos WHERE user_id = ? AND done = 0")
+    .get(userId) as { n: number };
+  return row.n;
+}