wtf man

7 files changed, 315 insertions, 12 deletions

diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index 9897af8..3d46fd9 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -1,7 +1,7 @@
 import Database from "bun:sqlite";
-import { getDBOffset, wordFactorial } from "@/lib/utils";
-import type { AddSense, AddWord, State } from "@/lib/types";
-import { DEFAULT_SRS } from "@/lib/services/srs";
+import { getDBOffset, wordFactorial } from "../utils";
+import type { AddSense, AddWord, State } from "../types";
+import { DEFAULT_SRS } from "../services/srs";
 
 const PAGE_SIZE = 100;
 
@@ -32,11 +32,17 @@ class DatabaseHandler {
   fetchCookie(coki: string) {
     const query = this.db.query(
       `
-        SELECT * FROM cookies
-        WHERE cookie = ?
+        SELECT u.id, u.name, c.expiry FROM cookies as c
+        JOIN users as u ON u.id = c.user
+        WHERE c.cookie = ?
       `,
     );
-    const res = query.get(coki);
+    const res = query.get(coki) as {
+      id: number;
+      name: string;
+      expiry: number;
+    };
+    console.log("cokifetch", { coki, res });
     return res;
   }
   setCookie(coki: string, user: number, expiry: number) {
@@ -577,14 +583,22 @@ class DatabaseHandler {
       return { error: `${e}` };
     }
   }
-  loginUser(name: string, creds: string) {
+  async loginUser(name: string, creds: string) {
     const query = this.db.query(`
-    SELECT id FROM users
-    WHERE name = ? AND creds = ?
+    SELECT * FROM users
+    WHERE name = ?
   `);
-    const row = query.get(name, creds) as { id: number } | null;
+    const row = query.get(name) as {
+      id: number;
+      name: string;
+      creds: string;
+    } | null;
     if (!row) return { error: "not found" };
-    else return { ok: row.id };
+    else {
+      const ok = await Bun.password.verify(creds, row.creds);
+      if (!ok) return { error: "Wrong password" };
+      else return { ok: row.id };
+    }
   }
   addCat(category: string) {
     const queryString = `
diff --git a/src/lib/db/schema.sql b/src/lib/db/schema.sql
new file mode 100644
index 0000000..1b678c5
--- /dev/null
+++ b/src/lib/db/schema.sql
@@ -0,0 +1,172 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+
+CREATE TABLE IF NOT EXISTS cookies(
+    user INTEGER NOT NULL,
+    cookie TEXT NOT NULL,
+    expiry INTEGER NOT NULL,
+    PRIMARY KEY(user, cookie),
+    FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE
+);
+CREATE TABLE IF NOT EXISTS languages (
+    code TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    native_name TEXT
+);
+-- type is "word" or other
+--
+
+-- an orthographic (and likely phonetic too entity)
+-- lang is 2char code ISO 6393-1 I gues
+CREATE TABLE IF NOT EXISTS expressions(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    spelling TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    frequency INTEGER,
+    type TEXT NOT NULL,
+    syllables INTEGER,
+    ipa    JSONB,
+    prosody JSONB,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (lang) REFERENCES languages(code),
+    CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+CREATE INDEX IF NOT EXISTS idx_words_spelling ON expressions(spelling);
+CREATE INDEX IF NOT EXISTS idx_words_type ON expressions(type);
+CREATE INDEX IF NOT EXISTS idx_words_lang ON expressions(lang);
+-- a semantic entity
+CREATE TABLE IF NOT EXISTS senses(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    parent_id INTEGER NOT NULL,
+    spelling TEXT NOT NULL,
+    pos TEXT,
+    etymology TEXT,
+    ipa    JSONB,
+    prosody JSONB,
+    senses JSONB,
+    forms JSONB,
+    related JSONB,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (parent_id) REFERENCES expressions(id)
+);
+CREATE INDEX IF NOT EXISTS idx_words_pos ON senses(pos);
+CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
+
+
+
+-- Categories table (for noun and verb categories)
+CREATE TABLE IF NOT EXISTS categories (
+    name TEXT PRIMARY KEY
+);
+
+-- Word Categories junction table
+CREATE TABLE IF NOT EXISTS word_categories (
+    word_id INTEGER NOT NULL,
+    category INTEGER NOT NULL,
+    PRIMARY KEY (word_id, category),
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (category) REFERENCES categories(name)
+);
+CREATE INDEX IF NOT EXISTS idx_word_categories_category_id ON word_categories(category);
+
+
+-- Progress
+CREATE TABLE IF NOT EXISTS users(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    creds TEXT NOT NULL,
+    CONSTRAINT name_unique UNIQUE (name)
+);
+CREATE TABLE IF NOT EXISTS user_progress (
+      id INTEGER PRIMARY KEY AUTOINCREMENT,
+      user_id INTEGER NOT NULL,
+      card_id INTEGER NOT NULL,
+      repetition_count INTEGER DEFAULT 0,
+      ease_factor REAL DEFAULT 2.5,
+      interval INTEGER DEFAULT 1,
+      next_review_date INTEGER,
+      last_reviewed INTEGER,
+      is_mastered BOOLEAN DEFAULT FALSE,
+      FOREIGN KEY (user_id) REFERENCES users(id),
+      FOREIGN KEY (card_id) REFERENCES cards(id),
+      CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+);
+-- CREATE TABLE IF NOT EXISTS user_progress (
+--       id INTEGER PRIMARY KEY AUTOINCREMENT,
+--       user_id INTEGER NOT NULL,
+--       card_id INTEGER NOT NULL,
+--       repetition_count INTEGER DEFAULT 0,
+--       ease_factor REAL DEFAULT 2.5,
+--       interval INTEGER DEFAULT 1,
+--       next_review_date DATETIME,
+--       last_reviewed DATETIME,
+--       is_mastered BOOLEAN DEFAULT FALSE,
+--       CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+--       FOREIGN KEY (user_id) REFERENCES users(id),
+--       FOREIGN KEY (card_id) REFERENCES cards(id)
+-- );
+-- Lessons
+CREATE TABLE IF NOT EXISTS lessons(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    position INTEGER NOT NULL DEFAULT 0,
+    description TEXT,
+    lang TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(code)
+);
+CREATE TABLE IF NOT EXISTS cards(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    note TEXT
+);
+CREATE TABLE IF NOT EXISTS cards_expressions(
+  expression_id INTEGER NOT NULL,
+  card_id INTEGER NOT NULL,
+  PRIMARY KEY (card_id, expression_id),
+  FOREIGN KEY (card_id) REFERENCES cards(id),
+  FOREIGN KEY (expression_id) REFERENCES expressions(id)
+);
+CREATE TABLE IF NOT EXISTS cards_lessons(
+  lesson_id INTEGER,
+  card_id INTEGER NOT NULL,
+  PRIMARY KEY (card_id, lesson_id),
+  FOREIGN KEY (card_id) REFERENCES cards(id),
+  FOREIGN KEY (lesson_id) REFERENCES lessons(id)
+);
+
+CREATE TABLE IF NOT EXISTS attempts(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    user_id INTEGER NOT NULL,
+    timestamp INTEGER NOT NULL,
+    card_id INTEGER NOT NULL,
+    good INTEGER NOT NULL, -- 0 or 1
+    FOREIGN KEY (user_id) REFERENCES users(id)
+    FOREIGN KEY (card_id) REFERENCES cards(id)
+);
+
+-- Index to query attempts on a specific card
+CREATE INDEX IF NOT EXISTS idx_attempts_card ON attempts(card_id);
+
+-- Index to query attempts for a specific user
+CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id);
+
+-- (Optional) Index to query attempts by user and resource (useful if you often query by both)
+CREATE INDEX IF NOT EXISTS idx_attempts_user_resource ON attempts(user_id, card_id);
+CREATE INDEX IF NOT EXISTS idx_cards_resources
+ON cards_expressions(expression_id, card_id);
+
+-- CREATE TRIGGER IF NOT EXISTS populate_cards_resources
+-- AFTER INSERT ON cards
+-- FOR EACH ROW
+-- BEGIN
+--     -- Insert matching words into cards_resources
+--     INSERT INTO cards_expressions(card_id, expression_id)
+--     SELECT NEW.id, w.id 
+--     FROM expressions w 
+--     WHERE NEW.text LIKE '%' || w.spelling || '%';
+-- END;
+--
diff --git a/src/lib/server/cookie.ts b/src/lib/server/cookie.ts
new file mode 100644
index 0000000..fadac9d
--- /dev/null
+++ b/src/lib/server/cookie.ts
@@ -0,0 +1,47 @@
+import {
+  getCookie,
+  getSignedCookie,
+  setCookie,
+  setSignedCookie,
+  deleteCookie,
+} from "hono/cookie";
+import cookie from "cookie";
+// console.log("db module path:", "@/lib/db");
+// console.log(
+//   "globalThis.__WAKU_MIDDLEWARE_CONTEXT_STORAGE__:",
+//   globalThis.__WAKU_MIDDLEWARE_CONTEXT_STORAGE__,
+// );
+import db from "../db";
+
+import type { Middleware } from "waku/config";
+
+// XXX we would probably like to extend config.
+
+const cookieMiddleware: Middleware = () => {
+  console.log("cookieMiddleware executed");
+  return async (ctx, next) => {
+    if (ctx.req.url.pathname === "/login") return await next();
+    const cookies = cookie.parse(ctx.req.headers.cookie || "");
+    console.log({ cookies });
+    const coki = cookies.sorlang;
+    if (!coki) {
+      ctx.res.status = 301;
+      ctx.res.headers = {
+        Location: "/login",
+      };
+    }
+    if (coki) {
+      const userRow = db.fetchCookie(coki);
+      if (userRow) ctx.data.user = { id: userRow.id, name: userRow.name };
+      else {
+        ctx.res.status = 301;
+        ctx.res.headers = {
+          Location: "/login",
+        };
+      }
+    }
+    await next();
+  };
+};
+
+export default cookieMiddleware;
diff --git a/src/lib/server/cookiebridge.ts b/src/lib/server/cookiebridge.ts
new file mode 100644
index 0000000..ca4bd44
--- /dev/null
+++ b/src/lib/server/cookiebridge.ts
@@ -0,0 +1,33 @@
+import { getContextData } from "waku/middleware/context";
+import {
+  RequestCookies,
+  ResponseCookies,
+  type ResponseCookie,
+} from "@edge-runtime/cookies";
+import { mergeSetCookies } from "./setcookie";
+
+const cookies = () => {
+  const ctx = getContextData() as {
+    headers: Record<string, string | string[]>;
+    cookies?: ResponseCookie[];
+  };
+  const headerObj = ctx.headers || {};
+  headerObj["set-cookie"] = mergeSetCookies(
+    headerObj["set-cookie"] || [],
+    (ctx.cookies || []) as ResponseCookie[],
+  );
+  const headers = new Headers(headerObj as Record<string, string>);
+  const reqCookies = new RequestCookies(headers);
+  const resCookies = new ResponseCookies(headers);
+
+  const getCookie: ResponseCookies["get"] = (...args) =>
+    resCookies.get(...args) || reqCookies.get(...args);
+  const setCookie: ResponseCookies["set"] = (...args) => {
+    const updated = resCookies.set(...args);
+    ctx.cookies = updated.getAll();
+    return updated;
+  };
+  return { getCookie, setCookie };
+};
+
+export { cookies };
diff --git a/src/lib/server/header.ts b/src/lib/server/header.ts
new file mode 100644
index 0000000..33f8792
--- /dev/null
+++ b/src/lib/server/header.ts
@@ -0,0 +1,12 @@
+// https://github.com/t6adev/waku-auth-middleware-demo/blob/a476ecb3d5caf0c7731a34314450400d4dcc2dac/src/middleware/validateRouting.ts
+
+import type { Middleware } from "waku/config";
+
+const headersMiddleware: Middleware = () => {
+  return async (ctx, next) => {
+    ctx.data.headers = ctx.req.headers;
+    await next();
+  };
+};
+
+export default headersMiddleware;
diff --git a/src/lib/server/setcookie.ts b/src/lib/server/setcookie.ts
new file mode 100644
index 0000000..f64b380
--- /dev/null
+++ b/src/lib/server/setcookie.ts
@@ -0,0 +1,25 @@
+import type { Middleware } from "waku/config";
+import { type ResponseCookie, stringifyCookie } from "@edge-runtime/cookies";
+
+export const mergeSetCookies = (
+  resSetCookies: string | string[],
+  cookiesInContext: ResponseCookie[],
+) => {
+  if (typeof resSetCookies === "string") {
+    resSetCookies = [resSetCookies];
+  }
+  return [...resSetCookies, ...cookiesInContext.map(stringifyCookie)];
+};
+
+const setCookieMiddleware: Middleware = () => {
+  return async (ctx, next) => {
+    await next();
+    ctx.res.headers ||= {};
+    ctx.res.headers["set-cookie"] = mergeSetCookies(
+      ctx.res.headers["set-cookie"] || [],
+      (ctx.data.cookies || []) as ResponseCookie[],
+    );
+  };
+};
+
+export default setCookieMiddleware;
diff --git a/src/lib/utils.ts b/src/lib/utils.ts
index 113c874..d3fdf9c 100644
--- a/src/lib/utils.ts
+++ b/src/lib/utils.ts
@@ -1,6 +1,6 @@
 import { type ClassValue, clsx } from "clsx";
 import { twMerge } from "tailwind-merge";
-import type { Result } from "@/lib/types";
+import type { Result } from "./types";
 
 export function cn(...inputs: ClassValue[]) {
   return twMerge(clsx(inputs));