From a03c92dc82ad527d7da6bbaa3c43000e2e5f0e69 Mon Sep 17 00:00:00 2001
From: polwex <polwex@sortug.com>
Date: Thu, 29 May 2025 16:25:31 +0700
Subject: better better

---
 src/lib/db/index.ts | 106 +++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 105 insertions(+), 1 deletion(-)

(limited to 'src/lib/db')

diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index 5c13f85..2212583 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -3,7 +3,7 @@ import { getDBOffset, wordFactorial } from "../utils";
 import type { AddSense, AddWord, Result, State } from "../types";
 import { DEFAULT_SRS } from "../services/srs";
 import { DBWord, WordData } from "@/zoom/logic/types";
-import { DeckResponse } from "../types/cards";
+import { CardResponse, DeckResponse } from "../types/cards";
 
 const PAGE_SIZE = 100;
 
@@ -506,6 +506,110 @@ class DatabaseHandler {
     `);
     return query.all({ cid, userid });
   }
+
+  /**
+   * Fetch a single card by ID with all related data
+   * @param cardId Card ID
+   * @param userId User ID
+   * @returns Card data with progress and expression information
+   */
+  fetchCardById(cardId: number, userId: number): Result<CardResponse> {
+    console.time("fetchCardById-total");
+
+    // Query to fetch card with expression and progress data
+    const query = this.db.query(`
+      SELECT 
+        cards.id as cid, cards.text, cards.note,
+        up.id as upid,
+        up.repetition_count,
+        up.ease_factor,
+        up.interval,
+        up.next_review_date,
+        up.last_reviewed,
+        up.is_mastered,
+        e.id as eid,
+        e.*,
+        (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
+      JOIN cards_expressions ce ON cards.id = ce.card_id
+      JOIN expressions e ON e.id = ce.expression_id
+      LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
+      LEFT JOIN bookmarks bm ON bm.word_id = e.id AND bm.user_id = ?
+      WHERE cards.id = ?
+    `);
+
+    const result = query.get(userId, userId, cardId);
+
+    if (!result) {
+      console.timeEnd("fetchCardById-total");
+      return { error: "Card not found" };
+    }
+
+    console.time("fetchCardById-json-processing");
+
+    // Process the row into a CardResponse object
+    const row: any = result;
+
+    // Process sense data
+    const sense_array = JSON.parse(row.senses_array);
+    const senses = sense_array.map((s: any) => {
+      const senses = JSON.parse(s.senses);
+      const related = JSON.parse(s.related);
+      const forms = JSON.parse(s.forms);
+      return { ...s, senses, related, forms };
+    });
+
+    // Create expression object
+    const expression = {
+      isBookmarked: row.is_bookmarked > 0,
+      ipa: JSON.parse(row.ipa),
+      prosody: JSON.parse(row.prosody),
+      syllables: row.syllables,
+      frequency: row.frequency,
+      type: row.type,
+      lang: row.lang,
+      spelling: row.spelling,
+      id: row.eid,
+      confidence: row.confidence,
+      senses,
+    };
+
+    // Create progress object (default if not exists)
+    const progress = !row.upid
+      ? DEFAULT_SRS
+      : {
+          repetitionCount: row.repetition_count,
+          easeFactor: row.ease_factor,
+          interval: row.interval,
+          nextReviewDate: row.next_review_date,
+          lastReviewed: row.last_reviewed,
+          isMastered: row.is_mastered,
+        };
+
+    // Create card response
+    const card = {
+      text: row.text,
+      note: row.note,
+      id: row.cid,
+      expression,
+      progress,
+    };
+
+    console.timeEnd("fetchCardById-json-processing");
+    console.timeEnd("fetchCardById-total");
+
+    return { ok: card };
+  }
   //
   // write
   //
-- 
cgit v1.2.3