7 files changed, 379 insertions, 22 deletions

diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index a767f70..5c13f85 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -21,10 +21,29 @@ class DatabaseHandler {
   constructor() {
     const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/prosody.db";
     const db = new Database(dbPath, { create: true });
+
+    // Performance optimizations for SQLite
     db.exec("PRAGMA journal_mode = WAL"); // Enable Write-Ahead Logging for better performance
     db.exec("PRAGMA foreign_keys = ON");
+    db.exec("PRAGMA cache_size = -8000"); // Increase cache size to 8MB
+    db.exec("PRAGMA temp_store = MEMORY"); // Store temp tables in memory
+    db.exec("PRAGMA synchronous = NORMAL"); // Slightly less safe but faster
+
     this.db = db;
+
+    // Apply performance indexes
+    try {
+      const indexesFile = Bun.file(
+        "/home/y/code/bun/ssr/waku/src/lib/db/indexes.sql",
+      );
+      const indexesSql = indexesFile.text().then((txt) => {
+        db.exec(txt);
+      });
+    } catch (e) {
+      console.error("Failed to apply performance indexes:", e);
+    }
   }
+
   async init() {
     const file = Bun.file("./schema.sql");
     const sql = await file.text();
diff --git a/src/lib/db/indexes.sql b/src/lib/db/indexes.sql
new file mode 100644
index 0000000..ffd2938
--- /dev/null
+++ b/src/lib/db/indexes.sql
@@ -0,0 +1,26 @@
+-- Performance-enhancing indexes for the SRS database
+
+-- User progress indexes
+CREATE INDEX IF NOT EXISTS idx_user_progress_next_review ON user_progress(user_id, next_review_date);
+CREATE INDEX IF NOT EXISTS idx_user_progress_mastered ON user_progress(user_id, is_mastered);
+
+-- Expressions related indexes
+CREATE INDEX IF NOT EXISTS idx_expressions_combined ON expressions(lang, type, frequency);
+CREATE INDEX IF NOT EXISTS idx_expressions_spelling_lang ON expressions(spelling, lang);
+
+-- Cards and lessons indexes
+CREATE INDEX IF NOT EXISTS idx_cards_lessons_lesson ON cards_lessons(lesson_id);
+CREATE INDEX IF NOT EXISTS idx_cards_lessons_card ON cards_lessons(card_id);
+CREATE INDEX IF NOT EXISTS idx_cards_expressions_expression ON cards_expressions(expression_id);
+
+-- Bookmarks index
+CREATE INDEX IF NOT EXISTS idx_bookmarks_user ON bookmarks(user_id);
+
+-- Lessons indexes
+CREATE INDEX IF NOT EXISTS idx_lang_lessons_lang ON lang_lessons(lang);
+
+-- Attempts indexes
+CREATE INDEX IF NOT EXISTS idx_attempts_timestamp ON attempts(timestamp);
+
+-- Combined index for senses parent lookup
+CREATE INDEX IF NOT EXISTS idx_senses_parent_combined ON senses(parent_id, pos);
\ No newline at end of file
diff --git a/src/lib/db/perf.ts b/src/lib/db/perf.ts
new file mode 100644
index 0000000..a5b57c3
--- /dev/null
+++ b/src/lib/db/perf.ts
@@ -0,0 +1,317 @@
+/**
+ * Database performance optimization suggestions
+ */
+
+// Optimized version of fetchLesson that splits the query into multiple smaller queries
+// to reduce the complexity and improve performance
+export function optimizedFetchLesson() {
+  return `
+  // Split into multiple queries for better performance
+  
+  // 1. First get the lesson information (small query)
+  console.time("fetchLesson-lessonInfo");
+  const lessonQuery = this.db.query(\`
+    SELECT 
+      l.name, 
+      l.description, 
+      ll.lang as llang,
+      (SELECT COUNT(DISTINCT card_id) FROM cards_lessons WHERE lesson_id = ?) AS total_card_count
+    FROM lessons l
+    JOIN lang_lessons ll ON l.id = ll.lesson_id
+    WHERE l.id = ?
+    LIMIT 1
+  \`);
+  
+  const lessonInfo = lessonQuery.get(lessonId, lessonId);
+  console.timeEnd("fetchLesson-lessonInfo");
+  
+  if (!lessonInfo) {
+    console.timeEnd("fetchLesson-total");
+    return { error: "Lesson not found" };
+  }
+  
+  // 2. Get the card IDs that need review (faster query)
+  console.time("fetchLesson-cardIds");
+  const cardIdsQuery = this.db.query(\`
+    SELECT DISTINCT cards.id as cid
+    FROM cards_lessons cl
+    JOIN cards ON cards.id = cl.card_id
+    LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
+    WHERE cl.lesson_id = ? AND (up.next_review_date IS NULL OR up.next_review_date < ?)
+    \${random ? "ORDER BY RANDOM()" : "ORDER BY cards.id"}
+    LIMIT ? OFFSET ?
+  \`);
+  
+  const cardIdsResult = cardIdsQuery.all(userId, lessonId, tomorrow.getTime(), size, offset);
+  console.timeEnd("fetchLesson-cardIds");
+  
+  if (cardIdsResult.length === 0) {
+    console.timeEnd("fetchLesson-total");
+    return { error: "No cards due for review" };
+  }
+  
+  // Extract card IDs into a comma-separated list for the IN clause
+  const cardIds = cardIdsResult.map((row: any) => row.cid).join(',');
+  
+  // 3. Get card data for those IDs
+  console.time("fetchLesson-cardData");
+  const cardDataQuery = this.db.query(\`
+    SELECT 
+      cards.text, cards.note, cards.id as cid,
+      up.id as upid,
+      up.repetition_count,
+      up.ease_factor,
+      up.interval,
+      up.next_review_date,
+      up.last_reviewed,
+      up.is_mastered
+    FROM cards
+    LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
+    WHERE cards.id IN (\${cardIds})
+  \`);
+  
+  const cardDataResult = cardDataQuery.all(userId);
+  console.timeEnd("fetchLesson-cardData");
+  
+  // 4. For each card, get the expression data
+  console.time("fetchLesson-expressionData");
+  const cards = [];
+  
+  for (const cardData of cardDataResult) {
+    const expressionQuery = this.db.query(\`
+      SELECT 
+        e.id as eid,
+        e.spelling,
+        e.lang,
+        e.frequency,
+        e.type,
+        e.syllables,
+        e.ipa,
+        e.prosody,
+        e.confidence,
+        (CASE WHEN bm.word_id IS NULL THEN 0 ELSE 1 END) as is_bookmarked,
+        (SELECT
+          json_group_array(json_object(
+            'pos', pos,
+            'senses', s.senses,
+            'forms', forms,
+            'etymology', etymology,
+            'related', related)
+          )
+          FROM senses s WHERE s.parent_id = e.id
+        ) as senses_array
+      FROM cards_expressions ce
+      JOIN expressions e ON e.id = ce.expression_id
+      LEFT JOIN bookmarks bm ON bm.word_id = e.id AND bm.user_id = ?
+      WHERE ce.card_id = ?
+      LIMIT 1
+    \`);
+    
+    const expressionData = expressionQuery.get(userId, cardData.cid);
+    
+    if (expressionData) {
+      // Process expression data
+      let senses = [];
+      try {
+        const sense_array = JSON.parse(expressionData.senses_array || '[]');
+        senses = sense_array.map((s: any) => {
+          try {
+            const sensesData = s.senses ? JSON.parse(s.senses) : [];
+            const relatedData = s.related ? JSON.parse(s.related) : [];
+            const formsData = s.forms ? JSON.parse(s.forms) : [];
+            return { 
+              ...s, 
+              senses: sensesData, 
+              related: relatedData, 
+              forms: formsData 
+            };
+          } catch (e) {
+            return { ...s, senses: [], related: [], forms: [] };
+          }
+        });
+      } catch (e) {
+        // Handle parse error
+        console.error("Error parsing senses data:", e);
+      }
+      
+      const expression = {
+        isBookmarked: expressionData.is_bookmarked > 0,
+        ipa: expressionData.ipa ? JSON.parse(expressionData.ipa) : {},
+        prosody: expressionData.prosody ? JSON.parse(expressionData.prosody) : {},
+        syllables: expressionData.syllables,
+        frequency: expressionData.frequency,
+        type: expressionData.type,
+        lang: expressionData.lang,
+        spelling: expressionData.spelling,
+        id: expressionData.eid,
+        confidence: expressionData.confidence,
+        senses,
+      };
+      
+      const progress = !cardData.upid
+        ? DEFAULT_SRS
+        : {
+            repetitionCount: cardData.repetition_count,
+            easeFactor: cardData.ease_factor,
+            interval: cardData.interval,
+            nextReviewDate: cardData.next_review_date,
+            lastReviewed: cardData.last_reviewed,
+            isMastered: cardData.is_mastered,
+          };
+      
+      const card = {
+        text: cardData.text,
+        note: cardData.note,
+        id: cardData.cid,
+        expression,
+        progress,
+      };
+      
+      cards.push(card);
+    }
+  }
+  console.timeEnd("fetchLesson-expressionData");
+  
+  const lesson = {
+    id: lessonId,
+    name: lessonInfo.name,
+    description: lessonInfo.description,
+    language: lessonInfo.llang,
+    cardCount: lessonInfo.total_card_count,
+  };
+  
+  console.timeEnd("fetchLesson-total");
+  return { ok: { lesson, cards } };
+  `;
+}
+
+// Additional performance optimization ideas
+export const performanceOptimizations = [
+  {
+    area: "Indexing",
+    description: "Add appropriate indexes to speed up common queries",
+    implementation: "See indexes.sql for specific index definitions"
+  },
+  {
+    area: "Query Splitting",
+    description: "Split complex queries into multiple simpler queries",
+    implementation: "Break down large JOIN operations into multiple targeted queries"
+  },
+  {
+    area: "Caching",
+    description: "Implement a caching layer for frequently accessed data",
+    implementation: `
+      // Simple in-memory cache implementation
+      const cache = new Map();
+      const CACHE_TTL = 1000 * 60 * 15; // 15 minutes
+      
+      function getCached(key: string) {
+        const item = cache.get(key);
+        if (!item) return null;
+        
+        if (Date.now() > item.expiry) {
+          cache.delete(key);
+          return null;
+        }
+        
+        return item.value;
+      }
+      
+      function setCache(key: string, value: any) {
+        cache.set(key, {
+          value,
+          expiry: Date.now() + CACHE_TTL
+        });
+      }
+    `
+  },
+  {
+    area: "JSON Processing",
+    description: "Optimize JSON parsing and serialization",
+    implementation: `
+      // Batch JSON parsing operations
+      function safeJsonParse(jsonString: string, fallback: any = {}) {
+        try {
+          return JSON.parse(jsonString || '{}');
+        } catch (e) {
+          console.error("JSON parse error:", e);
+          return fallback;
+        }
+      }
+      
+      // Process multiple JSON fields at once
+      function processJsonFields(row: any, fields: string[]) {
+        const result: Record<string, any> = {};
+        
+        for (const field of fields) {
+          result[field] = safeJsonParse(row[field]);
+        }
+        
+        return result;
+      }
+    `
+  },
+  {
+    area: "SQLite Configuration",
+    description: "Optimize SQLite connection parameters",
+    implementation: `
+      // Performance-focused SQLite configuration
+      db.exec("PRAGMA journal_mode = WAL"); 
+      db.exec("PRAGMA foreign_keys = ON");
+      db.exec("PRAGMA cache_size = -8000"); // 8MB cache
+      db.exec("PRAGMA temp_store = MEMORY"); 
+      db.exec("PRAGMA synchronous = NORMAL"); 
+      db.exec("PRAGMA mmap_size = 30000000000"); // 30GB memory map
+    `
+  },
+  {
+    area: "Query Optimization",
+    description: "Avoid subqueries and complex joins where possible",
+    implementation: "Use targeted queries with specific WHERE clauses rather than complex JOINs"
+  }
+];
+
+// Caching layer for SRS data
+export class SRSCache {
+  private static instance: SRSCache;
+  private cache: Map<string, { data: any, expiry: number }>;
+  private TTL = 15 * 60 * 1000; // 15 minutes
+  
+  private constructor() {
+    this.cache = new Map();
+  }
+  
+  public static getInstance(): SRSCache {
+    if (!SRSCache.instance) {
+      SRSCache.instance = new SRSCache();
+    }
+    return SRSCache.instance;
+  }
+  
+  public get<T>(key: string): T | null {
+    const entry = this.cache.get(key);
+    if (!entry) return null;
+    
+    if (Date.now() > entry.expiry) {
+      this.cache.delete(key);
+      return null;
+    }
+    
+    return entry.data as T;
+  }
+  
+  public set<T>(key: string, data: T, ttl?: number): void {
+    this.cache.set(key, {
+      data,
+      expiry: Date.now() + (ttl || this.TTL)
+    });
+  }
+  
+  public clear(): void {
+    this.cache.clear();
+  }
+  
+  public delete(key: string): boolean {
+    return this.cache.delete(key);
+  }
+}
\ No newline at end of file
diff --git a/src/lib/db/prosodydb.ts b/src/lib/db/prosodydb.ts
index b3b973b..52312bd 100644
--- a/src/lib/db/prosodydb.ts
+++ b/src/lib/db/prosodydb.ts
@@ -49,7 +49,6 @@ class DatabaseHandler {
     }
   }
   addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) {
-    console.log("wordrhyme", notes);
     try {
       const query = this.db
         .query(
diff --git a/src/lib/db/seed.ts b/src/lib/db/seed.ts
index 6c2a9f7..b776782 100644
--- a/src/lib/db/seed.ts
+++ b/src/lib/db/seed.ts
@@ -276,6 +276,7 @@ async function fillFromDump() {
   for await (const line of readWiktionaryDump()) {
     try {
       count++;
+      console.log({ count });
       // if (count > 80) break;
       // if (line.length > biggest) {
       //   biggest = line.length;
@@ -522,7 +523,7 @@ async function redump() {
       count++;
       // if (count > 50) break;
       const j = JSON.parse(line);
-      console.log(Object.keys(j), j.word);
+      // console.log(Object.keys(j), j.word);
       // add language to db
       pdb.addLanguage(j.lang_code, j.lang);
       if (!langs.includes(j.lang_code)) continue;
@@ -534,7 +535,7 @@ async function redump() {
       if (isWord) await handleWord(j);
       else await handleIdiom(j);
     } catch (e) {
-      console.log("error parsing", e);
+      // console.log("error parsing", e);
       // break;
     }
   }
@@ -555,11 +556,11 @@ type SorSyl = {
 async function handleWord(j: any) {
   let ts = Date.now();
   const analyzed = await findLemma(j.word, j.lang_code);
-  console.log(analyzed.segments.length);
+  // console.log(analyzed.segments.length);
   if (analyzed.segments.length !== 1)
     return console.error("wtf bruh", analyzed);
   const seg = analyzed.segments[0];
-  if (!seg) console.log("no seg", analyzed);
+  if (!seg) return console.log("no seg", analyzed);
   const isLemma = analyzed.input === seg.root.lemma;
   if (!isLemma)
     // return console.error("not lemma", {
@@ -608,7 +609,7 @@ async function handleIpa(
       else return `${acc}${item.ipa}`;
     }, "");
     if (wordRhyme) pdb.addWordRhyme(wordId, wordRhyme, j.lang_code, wikiRhyme);
-    else console.log("no rhyme?", hjon);
+    // else console.log("no rhyme?", hjon);
     for (const syl of hjon.syls) {
       // TODO ideally syllables would have spelling not IPA... harsh tho
       pdb.addSyllable(
@@ -628,8 +629,8 @@ async function handleIpa(
     // console.log(Date.now() - ts, "elapsed in db");
     // ts = Date.now();
   } catch (e) {
-    console.error(e);
-    console.error({ snd });
+    // console.error(e);
+    // console.error({ snd });
     // break;
   }
 }
diff --git a/src/pages.gen.ts b/src/pages.gen.ts
index d8c992f..fa7a6fa 100644
--- a/src/pages.gen.ts
+++ b/src/pages.gen.ts
@@ -30,13 +30,13 @@ import type { getConfig as File_Index_getConfig } from './pages/index';
 
 // prettier-ignore
 type Page =
+| { path: '/study/[slug]'; render: 'dynamic' }
 | ({ path: '/zoom' } & GetConfigResponse<typeof File_Zoom_getConfig>)
 | ({ path: '/lang/[slug]' } & GetConfigResponse<typeof File_LangSlug_getConfig>)
 | ({ path: '/lesson/[slug]' } & GetConfigResponse<typeof File_LessonSlug_getConfig>)
 | ({ path: '/login' } & GetConfigResponse<typeof File_Login_getConfig>)
 | ({ path: '/parse' } & GetConfigResponse<typeof File_Parse_getConfig>)
 | ({ path: '/db' } & GetConfigResponse<typeof File_Db_getConfig>)
-| { path: '/study'; render: 'dynamic' }
 | { path: '/test/client-modal'; render: 'dynamic' }
 | { path: '/test/trigger-modal-button'; render: 'dynamic' }
 | { path: '/test'; render: 'dynamic' }
diff --git a/src/pages/study.tsx b/src/pages/study/[slug].tsx
index 68f781e..a5af523 100644
--- a/src/pages/study.tsx
+++ b/src/pages/study/[slug].tsx
@@ -5,15 +5,13 @@ import StudySession from "@/components/Flashcard/StudySession";
 import { Button } from "@/components/ui/button";
 import { Card } from "@/components/ui/card";
 import LessonSelector from "@/components/srs/LessonSelector";
+import type { PageProps } from "waku/router";
 
 // This is a server component that gets the initial data
-export default async function StudyPage({
-  searchParams,
-}: {
-  searchParams: { lessonId?: string };
-}) {
-  const { user } = getContextData() as any;
-  const userId = user?.id;
+export default async function StudyPage(props: PageProps<"/study/[slug]">) {
+  const lessonId = props.slug;
+  const ctx = getContextData() as any;
+  const userId = ctx?.user?.id;
   // const state = getState(null);
 
   // If not logged in, show login required message
@@ -33,10 +31,6 @@ export default async function StudyPage({
     );
   }
 
-  const lessonId = searchParams?.lessonId
-    ? parseInt(searchParams.lessonId, 10)
-    : null;
-
   // If no lesson ID provided, show lesson selector
 
   // Get initial data for the study session
@@ -53,11 +47,11 @@ async function Inner({
   lessonId,
 }: {
   userId: number;
-  lessonId: number | null;
+  lessonId: string;
 }) {
   return (
     <>
-      {lessonId ? (
+      {lessonId && Number(lessonId) ? (
         <StudySessionOuter userId={userId} lessonId={Number(lessonId)} />
       ) : (
         <LessonSelector userId={userId} />
@@ -73,6 +67,7 @@ async function StudySessionOuter({
   lessonId: number;
 }) {
   const initialData = await startStudySession(userId, lessonId, true);
+  console.log({ initialData });
   if ("ok" in initialData)
     return (
       <>