1 files changed, 60 insertions, 32 deletions

diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index a710a1e..b43edc3 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -1,8 +1,9 @@
 import Database from "bun:sqlite";
 import { getDBOffset, wordFactorial } from "../utils";
-import type { AddSense, AddWord, State } from "../types";
+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";
 
 const PAGE_SIZE = 100;
 
@@ -57,16 +58,35 @@ class DatabaseHandler {
   // read
   //
   fetchLanguage(lang: string, page?: number) {
-    const query = this.db.query(
-      `
-      SELECT * FROM lessons
-      JOIN languages l ON l.code = lessons.lang
-      WHERE lessons.lang= ?
-      `,
-    );
+    const query = this.db.query(`
+    SELECT
+          l.id,
+          l.name,
+          l.position,
+          l.description,
+          COUNT(cl.card_id) AS count
+      FROM
+          lessons l
+      INNER JOIN
+          lang_lessons ll ON l.id = ll.lesson_id
+      LEFT JOIN
+          cards_lessons cl ON l.id = cl.lesson_id
+      WHERE
+          ll.lang = ?
+      GROUP BY
+          l.id, l.name, l.position, l.description
+      ORDER BY
+          l.position, l.name;      
+      `);
     const res = query.all(lang);
     console.log(res, "results");
-    return res;
+    return res as Array<{
+      id: number;
+      count: number;
+      name: string;
+      description: string;
+      position: number;
+    }>;
   }
   fetchSyllables(lang: string, page?: number) {
     const query = this.db.query(
@@ -135,7 +155,7 @@ class DatabaseHandler {
       GROUP_CONCAT(c.name, ',') AS category,
     FROM expressions
     JOIN word_categories wc ON wc.word_id = words.id
-    JOIN categories c ON c.id = wc.category_id
+    JOIN categories c ON c.name = wc.category
     WHERE spelling = $spelling
     GROUP BY words.id
     `);
@@ -151,7 +171,7 @@ class DatabaseHandler {
       GROUP_CONCAT(c.name, ',') AS category
     FROM expressions e
     JOIN word_categories wc ON wc.word_id = e.id
-    JOIN categories c ON c.id = wc.category_id
+    JOIN categories c ON c.name= wc.category
     ORDER BY e.frequency DESC
     LIMIT $count
     OFFSET $offset
@@ -297,7 +317,7 @@ class DatabaseHandler {
     LEFT JOIN 
         word_categories wc ON wc.word_id = e.id
     LEFT JOIN 
-        categories cat ON cat.id = wc.category_id
+        categories cat ON cat.name = wc.category
     GROUP BY 
         l.id, c.id, e.id
     ORDER BY 
@@ -309,7 +329,12 @@ 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) {
+  fetchLesson(
+    userId: number,
+    lessonId: number,
+    count?: number,
+    page?: number,
+  ): Result<DeckResponse> {
     const p = page ? page : 1;
     const size = count ? count : PAGE_SIZE;
     const offset = getDBOffset(p, size);
@@ -318,7 +343,7 @@ class DatabaseHandler {
     console.log(tomorrow.getTime());
     const queryString = `
     SELECT 
-      l.name, l.description, l.lang as llang, cards.text, cards.note, cards.id as cid,
+      l.name, l.description, ll.lang as llang, cards.text, cards.note, cards.id as cid,
       up.id as upid,
       up.repetition_count,
       up.ease_factor,
@@ -342,6 +367,7 @@ class DatabaseHandler {
           WHERE cl_inner.lesson_id = l.id) AS total_card_count 
     FROM cards_lessons cl
     JOIN lessons l ON l.id = cl.lesson_id
+    JOIN lang_lessons ll ON l.id = ll.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
@@ -367,7 +393,7 @@ class DatabaseHandler {
     const query = this.db.query(queryString);
     const res = query.all(userId, lessonId, tomorrow.getTime(), size, offset);
     console.log(res.length);
-    if (res.length === 0) return null;
+    if (res.length === 0) return { error: "Lesson not found" };
     const row: any = res[0];
     // console.log({ row });
     const lesson = {
@@ -377,8 +403,10 @@ class DatabaseHandler {
       language: row.llang,
       cardCount: row.total_card_count,
     };
+    // TODO IPA, prosody, senses... should we unify the format on the wikisource standard?
     const cards = res.map((row: any) => {
       // TODO parse here...?
+      // console.log({ row });
       const sense_array = JSON.parse(row.senses_array);
       const senses = sense_array.map((s: any) => {
         const senses = JSON.parse(s.senses);
@@ -417,7 +445,7 @@ class DatabaseHandler {
       };
       return card;
     });
-    return { lesson, cards };
+    return { ok: { lesson, cards } };
   }
   fetchCard(cid: number, userid: number) {
     const query = this.db.query(`
@@ -448,7 +476,7 @@ class DatabaseHandler {
     VALUES (${columns.map((c) => "?").join(",")})
     `;
     const query = this.db.query(queryString).run(...Object.values(params));
-    return query;
+    return query.lastInsertRowid;
   }
   addSense(params: AddSense) {
     const columns = Object.keys(params);
@@ -521,25 +549,28 @@ class DatabaseHandler {
   }) {
     const { text, mnote, eid, lesson_id } = params;
     const note = mnote ? mnote : null;
-    const query = this.db.query(`
+    const tx = this.db.transaction(() => {
+      const query = this.db.query(`
     INSERT 
     INTO cards(text, note) 
     VALUES(?, ?)
   `);
-    const res = query.run(text, note);
-    const cid = res.lastInsertRowid;
-    const query2 = this.db.query(`
+      const res = query.run(text, note);
+      const cid = res.lastInsertRowid;
+      const query2 = this.db.query(`
       INSERT OR IGNORE INTO cards_expressions(card_id, expression_id)
       VALUES(?, ?)
       `);
-    query2.run(cid, eid);
-    if (lesson_id) {
-      const query = this.db.query(`
+      query2.run(cid, eid);
+      if (lesson_id) {
+        const query = this.db.query(`
       INSERT INTO cards_lessons(card_id, lesson_id)
       VALUES(?, ?)
       `);
-      query.run(cid, lesson_id);
-    }
+        query.run(cid, lesson_id);
+      }
+    });
+    return tx();
   }
   addCardO(lesson_id: number | bigint | null, text: string, mnote?: string) {
     // wtf is this fucntion when did I write this
@@ -634,12 +665,9 @@ class DatabaseHandler {
   }
   addWCat(wordId: number | bigint, category: string) {
     const queryString = `
-  INSERT
-  INTO word_categories(word_id, category_id)
-  VALUES($wordId, (
-      SELECT id FROM categories
-      WHERE name = $category
-  ))
+  INSERT OR IGNORE
+  INTO word_categories(word_id, category)
+  VALUES($wordId, $category)
   `;
     const query = this.db.query(queryString);
     const res = query.run({ wordId, category });