m

2 files changed, 59 insertions, 30 deletions

diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index b43edc3..fcfab57 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -44,7 +44,7 @@ class DatabaseHandler {
       name: string;
       expiry: number;
     };
-    console.log("cokifetch", { coki, res });
+    // console.log("cokifetch", { coki, res });
     return res;
   }
   setCookie(coki: string, user: number, expiry: number) {
@@ -329,18 +329,24 @@ class DatabaseHandler {
   }
 
   // SELECT l.id, l.text, cards.text, cards.note FROM cards_lessons cl LEFT JOIN lessons l ON l.id = cl.lesson_id LEFT JOIN cards ON cards.id = cl.card_id ORDER BY l.id ASC LIMIT 20 OFFSET 0;
-  fetchLesson(
-    userId: number,
-    lessonId: number,
-    count?: number,
-    page?: number,
-  ): Result<DeckResponse> {
+  fetchLesson({
+    userId,
+    lessonId,
+    count,
+    page,
+    random,
+  }: {
+    userId: number;
+    lessonId: number;
+    count?: number;
+    page?: number;
+    random?: boolean;
+  }): Result<DeckResponse> {
     const p = page ? page : 1;
     const size = count ? count : PAGE_SIZE;
     const offset = getDBOffset(p, size);
     const tomorrow = new Date();
     tomorrow.setDate(tomorrow.getDate() + 1);
-    console.log(tomorrow.getTime());
     const queryString = `
     SELECT 
       l.name, l.description, ll.lang as llang, cards.text, cards.note, cards.id as cid,
@@ -351,7 +357,9 @@ class DatabaseHandler {
       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,
@@ -371,31 +379,28 @@ class DatabaseHandler {
     JOIN cards ON cards.id = cl.card_id
     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 = ?
-    WHERE l.id = ? AND (up.next_review_date IS NULL OR up.next_review_date < ?)
-    ORDER BY cards.id, e.id
-    LIMIT ? OFFSET ?;
+    LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?1
+    LEFT JOIN bookmarks bm ON bm.word_id = e.id AND bm.user_id = ?1
+    WHERE l.id = ?2 AND (up.next_review_date IS NULL OR up.next_review_date < ?3)
+    ${
+      random
+        ? // ? "AND e.id IN (SELECT id FROM expressions ORDER BY RANDOM() LIMIT ?4 OFFSET ?5)"
+          // "AND e.rowid > (ABS(RANDOM()) % (SELECT max(rowid) FROM expressions)) LIMIT ?4 OFFSET ?5"
+          "ORDER BY RANDOM() LIMIT ?4 OFFSET ?5"
+        : "ORDER BY cards.id, e.id LIMIT ?4 OFFSET ?5"
+    };
     `;
-    // const queryString = `
-    // SELECT
-    //   l.id, l.name, l.description, l.lang, cards.text, cards.note, cards.id as cid,
-    //   spelling, ipa, frequency, e.id as eid,
-    //   GROUP_CONCAT(wc.category, ',') AS category
-    // FROM cards_lessons cl
-    // JOIN lessons l ON l.id = cl.lesson_id
-    // JOIN cards ON cards.id = cl.card_id
-    // JOIN cards_expressions ce ON cards.id = ce.card_id
-    // JOIN expressions e ON e.id = ce.expression_id
-    // JOIN word_categories wc ON wc.word_id = e.id
-    // WHERE l.id = ?
-    // LIMIT ? OFFSET ?;
-    // `;
+    // SELECT * FROM expressions e
+    // WHERE e.rowid > (
+    //   ABS(RANDOM()) % (SELECT max(rowid) FROM expressions)
+    // )
+    // LIMIT 10;
     const query = this.db.query(queryString);
     const res = query.all(userId, lessonId, tomorrow.getTime(), size, offset);
-    console.log(res.length);
+    // console.log("cards", res.length);
     if (res.length === 0) return { error: "Lesson not found" };
     const row: any = res[0];
-    // console.log({ row });
+    console.log({ row });
     const lesson = {
       id: lessonId,
       name: row.name,
@@ -415,6 +420,7 @@ class DatabaseHandler {
         return { ...s, senses, related, forms };
       });
       const expression = {
+        isBookmarked: row.is_bookmarked > 0,
         ipa: JSON.parse(row.ipa),
         prosody: JSON.parse(row.prosody),
         syllables: row.syllables,
@@ -673,6 +679,26 @@ class DatabaseHandler {
     const res = query.run({ wordId, category });
     return res.lastInsertRowid;
   }
+  addBookmark(userId: number, wordId: number | bigint, notes?: string) {
+    const queryString = `
+  INSERT OR IGNORE
+  INTO bookmarks(user_id, word_id, created, notes)
+  VALUES(?, ?, ?, ?)
+  `;
+    const query = this.db.query(queryString);
+    const res = query.run(userId, wordId, Date.now(), notes || null);
+    return res.lastInsertRowid;
+  }
+  delBookmark(userId: number, wordId: number | bigint) {
+    const queryString = `
+  DELETE
+  FROM bookmarks
+  WHERE word_id = ? AND user_id = ?
+  `;
+    const query = this.db.query(queryString);
+    const res = query.run(wordId, userId);
+    return res;
+  }
   addThaiSyl(params: {
     spelling: string;
     tone: number;
diff --git a/src/lib/db/schema.sql b/src/lib/db/schema.sql
index 4506619..129400a 100644
--- a/src/lib/db/schema.sql
+++ b/src/lib/db/schema.sql
@@ -58,10 +58,13 @@ CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
 
 
 CREATE TABLE IF NOT EXISTS bookmarks(
-    word_id INTEGER PRIMARY KEY,
+    word_id INTEGER NOT NULL,
+    user_id INTEGER NOT NULL,
     notes   TEXT,
     created INTEGER NOT NULL,
-    FOREIGN KEY (word_id) REFERENCES expressions(id)
+    PRIMARY KEY (word_id, user_id),
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE INDEX IF NOT EXISTS idx_bookmarks ON bookmarks(word_id);