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

import Database from "bun:sqlite";
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 { CardResponse, DeckResponse } from "../types/cards";

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 });

    // 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();
    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
          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 as Array<{
      id: number;
      count: number;
      name: string;
      description: string;
      position: number;
    }>;
  }
  fetchSyllables(lang: string, page?: number) {
    const query = this.db.query(
      `
      SELECT * FROM expressions e
      WHERE e.type = 'syllable' AND e.lang = ?
      ORDER BY frequency DESC
      LIMIT ${PAGE_SIZE} ${page ? "OFFSET " + getDBOffset(page, PAGE_SIZE) : ""}
      `,
      // `
      // 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.name = wc.category
    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.name= wc.category
    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);
    const row = query.get(spelling, lang) as DBWord | null;
    if (!row) return row;
    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: WordData = {
      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,
    };
    return expression;
  }
  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.name = wc.category
    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,
    lessonId,
    count,
    page,
    random,
  }: {
    userId: number;
    lessonId: number;
    count?: number;
    page?: number;
    random?: boolean;
  }): Result<DeckResponse> {
    console.time("fetchLesson-total");
    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.time("fetchLesson-query");
    const queryString = `
    SELECT 
      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,
      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,
     (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 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
    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"
    };
    `;
    // 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.timeEnd("fetchLesson-query");

    // console.log("cards", res.length);
    if (res.length === 0) {
      console.timeEnd("fetchLesson-total");
      return { error: "Lesson not found" };
    }

    const row: any = res[0];
    // console.log({ row });

    console.time("fetchLesson-process");
    const lesson = {
      id: lessonId,
      name: row.name,
      description: row.description,
      language: row.llang,
      cardCount: row.total_card_count,
    };

    // Process the cards
    console.time("fetchLesson-json-processing");
    const cards = res.map((row: any) => {
      // JSON parsing is often expensive
      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 = {
        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.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;
    });
    console.timeEnd("fetchLesson-json-processing");

    console.timeEnd("fetchLesson-process");
    console.timeEnd("fetchLesson-total");
    return { ok: { 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 });
  }

  /**
   * 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
  //
  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.lastInsertRowid;
  }
  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 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(`
      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);
      }
    });
    return tx();
  }
  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 OR IGNORE
  INTO word_categories(word_id, category)
  VALUES($wordId, $category)
  `;
    const query = this.db.query(queryString);
    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;
    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 type { DatabaseHandler };
export default db;