path: root/src/lib/db/index.ts

import Database from "bun:sqlite";
import { getDBOffset, wordFactorial } from "../utils";
import type { AddSense, AddWord, State } from "../types";
import { DEFAULT_SRS } from "../services/srs";

const PAGE_SIZE = 100;

export function getState(coki: string | null): State {
  let user = null;
  if (coki) {
    const row: any = db.fetchCookie(coki);
    console.log("user row", row);
    user = row;
  }
  return { user };
}
class DatabaseHandler {
  db: Database;
  constructor() {
    const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/prosody.db";
    const db = new Database(dbPath, { create: true });
    db.exec("PRAGMA journal_mode = WAL"); // Enable Write-Ahead Logging for better performance
    db.exec("PRAGMA foreign_keys = ON");
    this.db = db;
  }
  async init() {
    const file = Bun.file("./schema.sql");
    const sql = await file.text();
    this.db.exec(sql);
  }
  // cokis
  fetchCookie(coki: string) {
    const query = this.db.query(
      `
        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) as {
      id: number;
      name: string;
      expiry: number;
    };
    console.log("cokifetch", { coki, res });
    return res;
  }
  setCookie(coki: string, user: number, expiry: number) {
    const query = this.db.query(`
        INSERT OR REPLACE INTO cookies(user, cookie, expiry)
        VALUES(?, ?, ?)
      `);
    const res = query.run(user, coki, expiry);
    return res.lastInsertRowid;
  }
  // 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 res = query.all(lang);
    console.log(res, "results");
    return res;
  }
  fetchSyllables(lang: string, page?: number) {
    const query = this.db.query(
      `
      SELECT * FROM expressions e
      WHERE e.syllables = 1 AND e.lang = ?
      ORDER BY frequency DESC
      LIMIT ${PAGE_SIZE} ${page ? "OFFSET " + getDBOffset(page, PAGE_SIZE) : ""}
      `,
    );
    const results = query.all(lang);
    console.log({ lang, page }, "results");
    const rpage = (page || 0) + 1;
    return { page: rpage, results };
  }
  fetchInit(userId: number) {
    const query1 = this.db.query(`
    SELECT *
    FROM languages
    `);
    const languages = query1.all();
    const query2 = this.db.query(`
    SELECT *
    FROM categories
    `);
    const categories = query2.all().map((c: any) => c.name);
    const query3 = this.db.query(`
    SELECT
        l.id,
        l.name,
        l.position,
        l.description,
        l.lang,
        COUNT(cl.card_id) as card_count,
        SUM(CASE WHEN up.is_mastered = 1 THEN 1 ELSE 0 END) as completed_cards
    FROM lessons l
    JOIN cards_lessons cl ON cl.lesson_id = l.id
    JOIN user_progress up ON cl.card_id = up.card_id AND up.user_id = ?
    GROUP BY l.id, l.name, l.position, l.description, l.lang
    ORDER BY l.position
    `);
    const decks = query3.all(userId);
    return { languages, categories, decks };
  }
  fetchCats() {
    const query1 = this.db.query(`
    SELECT *
    FROM languages
    `);
    const languages = query1.all();
    const query2 = this.db.query(`
    SELECT *
    FROM categories
    `);
    const categories = query2.all().map((c: any) => c.name);
    return { languages, categories };
  }
  fetchResource(spelling: string) {
    const query = this.db.query(`
    SELECT 
      spelling,
      ipa,
      frequency,
      type,
      subtype,
      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
    WHERE spelling = $spelling
    GROUP BY words.id
    `);
    return query.get({ spelling });
  }
  fetchFrequent(count: number, page: number) {
    const offset = (page - 1) * count;
    const query = this.db.query(`
    SELECT 
      spelling,
      ipa,
      frequency,
      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
    ORDER BY e.frequency DESC
    LIMIT $count
    OFFSET $offset
    `);
    return query.get({ count, offset });
  }

  fetchExpressionRaw(params: Record<string, string>) {
    const paramString = Object.keys(params)
      .map((k) => `${k} = ?`)
      .join(" AND ");
    const queryString = `
    SELECT *
    FROM expressions 
    WHERE ${paramString}
    ORDER BY frequency DESC
    `;
    const query = this.db.query(queryString);
    return query.all(...Object.values(params));
  }
  fetchExpressionBySpelling(spelling: string, lang: string) {
    const queryString = `
    SELECT *
    FROM expressions e
    WHERE e.spelling = ? AND e.lang = ?
    ORDER BY e.frequency DESC
    `;
    const query = this.db.query(queryString);
    return query.get(spelling, lang);
  }
  fetchWordBySpelling(spelling: string, lang: string) {
    const queryString = `
    SELECT *,
     (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 expressions e
    WHERE e.spelling = ? AND e.lang = ?
    ORDER BY e.frequency DESC
    `;
    const query = this.db.query(queryString);
    return query.get(spelling, lang);
  }
  fetchExpressionsByCard(cid: number) {
    const queryString = `
    SELECT 
      e.spelling, e.id as eid, e.ipa
    FROM cards_expressions ce
    JOIN expressions e ON ce.expression_id = e.id
    WHERE ce.card_id = $cid AND e.spelling IS NOT NULL
    ORDER BY e.frequency DESC
    `;
    const query = this.db.query(queryString);
    return query.all({ cid });
  }

  searchExpression(params: ExpressionSearchParams) {
    let queryParam: string[] = [];
    if (params.lang) queryParam.push(`e.lang = '${params.lang}'`);
    if (params.spelling) queryParam.push(`e.spelling = '${params.spelling}'`);
    if (params.pos) queryParam.push(`e.pos = '${params.pos}'`);
    if (params.syllables)
      queryParam.push(
        `e.syllables ${params.syllables.sign} ${params.syllables.num}`,
      );
    if (params.type) queryParam.push(`e.type = ${params.type}`);
    if (params.frequency)
      queryParam.push(
        `e.frequency ${params.frequency.above ? ">" : "<"} ${params.frequency}`,
      );
    const queryString = `
    SELECT * FROM expressions e
    WHERE ${queryParam.join(" AND ")} 
    `;
    console.log({ queryString });
    const query = this.db.query(queryString);
    return query.all(...Object.values(params));
  }
  fetchLessons(count?: number, page?: number) {
    const p = page ? page : 1;
    const size = count ? count : PAGE_SIZE;
    const offset = getDBOffset(p, size);
    // const queryString = `
    //   SELECT
    //     l.id, l.text as ltext, cards.text as ctext, cards.note as cnote, cards.id as cid
    //   FROM cards_lessons cl
    //   JOIN cards ON cards.id = cl.card_id
    //   JOIN lessons l ON l.id = cl.lesson_id
    //   LIMIT $count
    //   OFFSET $offset
    //   `;
    const queryString = `
    SELECT 
        l.id AS lesson_id, 
        l.text AS lesson_text, 
        c.id AS card_id, 
        c.text AS card_text, 
        c.note AS card_note, 
        e.id AS expression_id, 
        e.spelling AS expression_spelling, 
        e.ipa AS expression_ipa, 
        e.type AS expression_type, 
        e.subtype AS expression_subtype,
        GROUP_CONCAT(cat.name, ', ') AS categories
    FROM 
        lessons l
    JOIN 
        cards_lessons cl ON l.id = cl.lesson_id
    JOIN 
        cards c ON c.id = cl.card_id
    JOIN 
        cards_expressions ce ON c.id = ce.card_id
    JOIN 
        expressions e ON e.id = ce.expression_id
    LEFT JOIN 
        word_categories wc ON wc.word_id = e.id
    LEFT JOIN 
        categories cat ON cat.id = wc.category_id
    GROUP BY 
        l.id, c.id, e.id
    ORDER BY 
        l.id ASC, c.id ASC, e.id ASC
    LIMIT ? OFFSET ?;
    `;
    const query = this.db.query(queryString);
    return query.all(size, offset);
  }

  // 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) {
    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, l.lang as llang, 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,
      e.*,
     (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,
     (SELECT COUNT(DISTINCT cl_inner.card_id)
          FROM cards_lessons cl_inner
          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 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 ?;
    `;
    // 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 ?;
    // `;
    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;
    const row: any = res[0];
    // console.log({ row });
    const lesson = {
      id: lessonId,
      name: row.name,
      description: row.description,
      language: row.llang,
      cardCount: row.total_card_count,
    };
    const cards = res.map((row: any) => {
      // TODO parse here...?
      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 };
      });
      const expression = {
        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.id,
        confidence: row.confidence,
        senses,
      };
      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,
          };
      const card = {
        text: row.text,
        note: row.note,
        id: row.cid,
        expression,
        progress,
      };
      return card;
    });
    return { lesson, cards };
  }
  fetchCard(cid: number, userid: number) {
    const query = this.db.query(`
    SELECT 
      l.id, l.text, cards.text, cards.note, u.repetition_count, u.ease_factor, u.interval, u.next_review_date, u.last_reviewed, u.is_mastered
    FROM cards_lessons cl
    JOIN lessons l ON l.id = cl.lesson_id
    JOIN cards ON cards.id = lc.card_id
    JOIN attempts a ON a.card_id = cards.id AND a.user_id = $userid 
    JOIN user_progress u ON u.card_id = cards.id AND u.user_id = $userid 
    WHERE cards.id = $cid
    `);
    return query.all({ cid, userid });
  }
  //
  // write
  //
  addLanguage(code: string, name: string) {
    const query = this.db
      .query(`INSERT OR IGNORE INTO languages(code, name) VALUES(?, ?)`)
      .run(code, name);
  }
  addWord(params: AddWord) {
    const columns = Object.keys(params);

    const queryString = `INSERT INTO
    expressions(${columns.join(", ")})
    VALUES (${columns.map((c) => "?").join(",")})
    `;
    const query = this.db.query(queryString).run(...Object.values(params));
    return query;
  }
  addSense(params: AddSense) {
    const columns = Object.keys(params);

    const queryString = `INSERT INTO senses(${columns.join(", ")}) VALUES (${columns.map((c) => "?").join(",")})`;
    const query = this.db.query(queryString).run(...Object.values(params));
    return query.lastInsertRowid;
  }
  upsertWord(params: AddWord) {
    const columns = Object.keys(params);
    const queryString = `
      INSERT INTO expressions(${columns.join(", ")})
      VALUES (${columns.map((c) => "?").join(",")})
      ON CONFLICT(spelling, lang) DO UPDATE SET
      ${columns.map((c) => `${c}=excluded.${c}`).join(",\n")}
      WHERE excluded.spelling = expressions.spelling
            `;
    console.log("upserting", queryString);
    const query = this.db.query(queryString).run(...Object.values(params));
    return query.lastInsertRowid;
  }
  updateWord(id: number, params: Record<string, any>) {
    const columns = Object.keys(params);
    const queryString = `
      UPDATE expressions SET
      ${columns.map((c) => `${c}= ?`).join(",\n")}
      WHERE expressions.id = ?
            `;
    console.log("upserting", queryString);
    const query = this.db.query(queryString).run(...Object.values(params), id);
    return query.lastInsertRowid;
  }

  addFrequency(spelling: string, frequency: number) {
    const queryString = `
  UPDATE expressions
  SET frequency = ?
  WHERE expressions.spelling = ?
  `;
    const query = this.db.query(queryString);
    const res = query.run(frequency, spelling);
  }
  // addIPA(spelling: string, ipa: string) {
  //   const queryString = `
  // UPDATE expressions
  // SET ipa= $ipa
  // WHERE expressions.spelling = $spelling
  // `;
  //   const query = this.db.query(queryString);
  //   const res = query.run({ spelling, ipa });
  // }
  addLesson(body: {
    name: string;
    description: string | null;
    lang: string | null;
  }) {
    const query = this.db.query(`
    INSERT 
    INTO lessons(name, description, lang) 
    VALUES(?, ?, ?)
  `);
    const res = query.run(body.name, body.description, body.lang);
    return res.lastInsertRowid;
  }
  addCard(params: {
    text: string;
    eid: number;
    mnote?: string;
    lesson_id?: number | bigint;
  }) {
    const { text, mnote, eid, lesson_id } = params;
    const note = mnote ? mnote : null;
    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(`
      INSERT OR IGNORE INTO cards_expressions(card_id, expression_id)
      VALUES(?, ?)
      `);
    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);
    }
  }
  addCardO(lesson_id: number | bigint | null, text: string, mnote?: string) {
    // wtf is this fucntion when did I write this
    const note = mnote ? mnote : null;
    const query = this.db.query(`
    INSERT 
    INTO cards(text, note) 
    VALUES($text, $note)
  `);
    const params = { text, note, spel: text };
    const res = query.run(params);
    const cid = res.lastInsertRowid;
    const wquery = this.db.query(`
    INSERT OR IGNORE
    INTO cards_expressions(card_id, expression_id)
    VALUES($cid, (
      SELECT id FROM expressions e
      WHERE e.spelling LIKE $spelling 
    ))
    `);
    const wtr = this.db.transaction((pairs) => {
      // console.log("adding to ce", { pairs, cid, text });
      for (const pair of pairs) wquery.run(pair);
    });
    const words = text
      .replace(/[^\w\s]/g, "")
      .replace(/\s+/g, " ")
      .trim()
      .split(" ");
    const combinations = wordFactorial(words);
    const richWords = Array.from(combinations).map((spelling) => {
      return { spelling, cid };
    });
    wtr(richWords);
    if (lesson_id) {
      const query = this.db.query(`
      INSERT INTO cards_lessons(card_id, lesson_id)
      VALUES($cid, $lesson_id)
      `);
      query.run({ lesson_id, cid });
    }
  }
  processCard(userId: number, cardId: number, timestamp: number, ok: boolean) {
    const query = this.db
      .query(
        `
      INSERT into attempts(user_id,timestamp, card_id, good) VALUES (?, ?, ?, ?);
      `,
      )
      .run(userId, timestamp, cardId, ok ? 1 : 0);
  }

  addUser(name: string, creds: string) {
    try {
      const query = this.db.query(`
    INSERT OR ABORT
    INTO users(name, creds) 
    VALUES($name, $creds)
  `);
      const q = query.run({ $name: name, $creds: creds });
      return { ok: q.lastInsertRowid };
    } catch (e) {
      return { error: `${e}` };
    }
  }
  async loginUser(name: string, creds: string) {
    const query = this.db.query(`
    SELECT * FROM users
    WHERE name = ?
  `);
    const row = query.get(name) as {
      id: number;
      name: string;
      creds: string;
    } | null;
    if (!row) return { error: "not found" };
    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 = `
  INSERT OR IGNORE
  INTO categories(name)
  VALUES(?)
  `;
    const query = this.db.query(queryString);
    const res = query.run(category);
    return res.lastInsertRowid;
  }
  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
  ))
  `;
    const query = this.db.query(queryString);
    const res = query.run({ wordId, category });
    return res.lastInsertRowid;
  }
  addThaiSyl(params: {
    spelling: string;
    tone: number;
    is_long: number;
    ipa: string;
    frequency?: number;
  }) {
    const columns = Object.keys(params);

    const queryString = `
  INSERT OR IGNORE
  INTO thai_syllables(${columns.join(", ")})
  VALUES(${columns.map((c) => "?").join(", ")})
  `;
    const query = this.db.query(queryString).run(...Object.values(params));
    return query.lastInsertRowid;
  }
}

export const poss: Record<string, string> = {
  CC: "conjunction",
  DT: "determiner",
  IN: "preposition",
  MD: "auxiliar",
  PRP: "nominative", // TODO oi
  PRP$: "gemitive",
  WDT: "determiner",
  WP: "interrogative",
  WP$: "interrogative",
};
export const domains: Record<string, string> = {
  "adj.all": "adjective",
  "adj.pert": "adjective",
  "adj.ppl": "adjective",
  "adv.all": "adverb",
  "noun.Tops": "",
  "noun.act": "abstract",
  "noun.animal": "animate",
  "noun.artifact": "inanimate",
  "noun.attribute": "abstract",
  "noun.body": "inanimate",
  "noun.cognition": "abstract",
  "noun.communication": "abstract",
  "noun.event": "abstract",
  "noun.feeling": "abstract",
  "noun.food": "inanimate",
  "noun.group": "noun",
  "noun.location": "spatial",
  "noun.motive": "abstract",
  "noun.object": "inanimate",
  "noun.person": "animate",
  "noun.phenomenon": "abstract",
  "noun.plant": "noun",
  "noun.possession": "noun",
  "noun.process": "noun",
  "noun.quantity": "uncountable",
  "noun.relation": "noun",
  "noun.shape": "noun",
  "noun.state": "noun",
  "noun.substance": "uncountable",
  "noun.time": "temporal",
  "verb.body": "verb",
  "verb.change": "verb",
  "verb.cognition": "verb",
  "verb.communication": "verb",
  "verb.competition": "verb",
  "verb.consumption": "verb",
  "verb.contact": "verb",
  "verb.creation": "verb",
  "verb.emotion": "mental",
  "verb.motion": "verb",
  "verb.perception": "mental",
  "verb.possession": "verb",
  "verb.social": "verb",
  "verb.stative": "verb",
  "verb.weather": "verb",
};

type ExpressionSearchParams = {
  lang?: string;
  spelling?: string;
  pos?: string;
  syllables?: { num: number; sign: string };
  frequency?: { num: number; above: boolean };
  type?: ExpressionType;
};
type ExpressionType = "syllable" | "word" | "expression";

const db = new DatabaseHandler();
export default db;