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

import { readWiktionaryDump } from "../services/wiki";
import { getStressedSyllable, getSyllableCount } from "../utils";
import useful from "@/lib/useful_thai.json";
import db from ".";

const SYMBOL_REGEX = new RegExp(/[\W\d]/);

async function handleFile(
  filename: string,
  func: (line: string, idx: number) => void,
) {
  const file = Bun.file(filename);
  const s = file.stream();
  const reader = s.getReader();
  const decoder = new TextDecoder();
  let leftover = "";
  let lineCount = 0;
  while (true) {
    const { value, done } = await reader.read();
    if (done) break;
    const chunk = decoder.decode(value, { stream: true });
    const lines = (leftover + chunk).split("\n");

    // Process each line except the last (which might be incomplete)
    for (const line of lines.slice(0, -1)) {
      lineCount++;
      func(line, lineCount);
    }

    // Save the last incomplete line to process in the next iteration
    leftover = lines[lines.length - 1];
  }

  // Handle any remaining content after reading all chunks
  if (leftover) func(leftover, lineCount + 1);
}

function goodPos(pos: string): boolean {
  const list = [
    "CC",
    "DT",
    "EX",
    "IN",
    "LS",
    "MD",
    "PDT",
    "POS",
    "PRP",
    "PRP$",
    "RP",
    "TO",
    "WDT",
    "WP",
    "WP$",
  ];
  return list.includes(pos);
}
// function englishKaggle() {
//   handleFile("../datasets/words_pos.csv", (line, idx) => {
//     const [_, spelling, pos] = line.split(",");
//     if (!goodPos(pos)) return;
//     const rowid = addWord(db, spelling, "", "en-us", "word", null);
//     const category = poss[pos] || "unknown;";
//     addCat(db, rowid, category);
//   });
// }
// async function englishIPA() {
//   handleFile("ipa/en-us/ipadict.txt", (line, idx) => {
//     const [spelling, ipa] = line.split(/\s+/);
//     if (!spelling || !ipa) return;
//     const hasSymbols = spelling.match(SYMBOL_REGEX);
//     if (hasSymbols) return;
//     const split = spelling.split(" ");
//     const type = split.length > 1 ? "expression" : "word";
//     const subtype = null;
//     addWord(db, spelling, ipa, "en-us", type, subtype);
//   });
// }

async function englishFreq() {
  handleFile(
    "/home/y/code/prosody/hanchu/datasets/unigram_freq.csv",
    (line, idx) => {
      const [spelling, _frequency] = line.split(",");
      db.addFrequency(spelling, idx);
    },
  );
}
async function thaiFreq() {
  const files = [
    "/home/y/code/prosody/prosody/langdata/thai/data/1yin_freq.csv",
    "/home/y/code/prosody/prosody/langdata/thai/data/2yin_freq.csv",
    "/home/y/code/prosody/prosody/langdata/thai/data/3yin_freq.csv",
    "/home/y/code/prosody/prosody/langdata/thai/data/4yin_freq.csv",
    "/home/y/code/prosody/prosody/langdata/thai/data/5yin_freq.csv",
    "/home/y/code/prosody/prosody/langdata/thai/data/6yin_freq.csv",
  ];
  for (let f of files) {
    handleFile(f, (line, idx) => {
      const [spelling, IPA, tone, length, frequency, ...rest] = line.split(",");
      db.addFrequency(spelling, Number(frequency));
    });
  }
}

const thaiTones: Record<string, number> = {
  M: 1,
  L: 2,
  F: 3,
  H: 4,
  R: 5,
};
const thaiTones2: Record<string, number> = {
  "˧": 1,
  "˨˩": 2,
  "˥˩": 3,
  "˦˥": 4,
  "˩˩˦": 5,
};
async function thaiSyllables() {
  handleFile(
    "/home/y/code/prosody/prosody/langdata/thai/data/1yin_freq.csv",
    (line, idx) => {
      const [spelling, IPA, toneS, length, frequency, ...rest] =
        line.split(",");
      const isLong = length === "長";
      const tone = thaiTones[toneS];
      const prosody = JSON.stringify({ isLong, tone, lang: "th" });
      db.upsertWord({
        spelling,
        lang: "th",
        ipa: JSON.stringify([{ ipa: IPA, tags: ["sortug"] }]),
        prosody,
        syllables: 1,
        type: "syllable",
        frequency: Number(frequency),
        confidence: 10,
      });
    },
  );
  handleFile(
    "/home/y/code/prosody/prosody/langdata/thai/data/1yinjie.csv",
    (line, idx) => {
      const [spelling, IPA] = line.split(",");
      const isLong = IPA.includes("ː");
      let tone = 0;
      const toneMarks = Object.keys(thaiTones2);
      for (let tm of toneMarks) {
        if (IPA.includes(tm)) tone = thaiTones2[tm];
      }
      const prosody = JSON.stringify({ isLong, tone, lang: "th" });
      db.upsertWord({
        spelling,
        lang: "th",
        ipa: JSON.stringify([{ ipa: IPA, tags: ["sortug"] }]),
        prosody,
        syllables: 1,
        type: "syllable",
        confidence: 10,
      });
    },
  );
}

//   // Save the last incomplete line to process in the next iteration
// }
// TODO no conjunctions or adpositions in Wordnet!!
// function englishWordnet() {
//   // LEFT JOIN lexes_pronunciations ukpr ON ukpr.wordid = words.wordid AND uspr.variety = 'GB'
//   // LEFT JOIN pronunciations ukp ON ukp.pronunciationid = ukpr.pronunciationid
//   const queryString = `
//     WITH ranked_ipa  AS (
//       SELECT
//         lp.wordid,
//         pr.pronunciation,
//         lp.variety,
//         ROW_NUMBER() OVER (
//           PARTITION BY lp.wordid
//           ORDER BY
//               CASE
//                   WHEN lp.variety = 'US' THEN 1
//                   WHEN lp.variety IS NULL THEN 2
//                   WHEN lp.variety IS 'GB' THEN 3
//                   ELSE 4
//               END
//         ) AS rank
//         FROM lexes_pronunciations lp
//         JOIN pronunciations pr ON pr.pronunciationid = lp.pronunciationid
//     )
//     SELECT words.wordid, word, rp.pronunciation as ipa, domainname
//     FROM words
//     LEFT JOIN ranked_ipa rp ON rp.wordid = words.wordid AND rp.rank = 1
//     LEFT JOIN senses ON senses.wordid = words.wordid
//     LEFT JOIN synsets ON synsets.synsetid = senses.synsetid
//     LEFT JOIN domains ON domains.domainid = synsets.domainid
//     GROUP BY words.wordid
//   `;
//   const query = wndb.query(queryString);
//   const res: Array<{
//     word: string;
//     ipa: string;
//     domainname: string;
//   }> = query.all() as any;
//   console.log("res", res.length);
//   for (const r of res) {
//     console.log(r, "r");
//     // if (r.word === 'abrasive') throw new Error('stop right here');
//     const ok = filterWord(r.word);
//     if (!ok) continue;
//     const split = r.word.split(" ");
//     const type = split.length > 1 ? "expression" : "word";
//     const subtype = null;
//     const wordid = addWord(db, r.word, r.ipa, "en-us", type, subtype);
//     const category = domains[r.domainname] || "unknown;";
//     addCat(db, wordid, category);
//   }
// }
function filterWord(s: string) {
  const hasSymbols = s.match(SYMBOL_REGEX);
  if (hasSymbols) return false;
  else return true;
}

// function checkWordNet(word: string) {
//   const query = wndb.query(`SELECT * FROM words WHERE word = $word`);
//   const res = query.get({ $word: word });
//   return !!res;
// }

// function englishCards() {
//   const lesson_id = addLesson(db, "First Lesson, some easy stuff");
//   const texts = [
//     "I",
//     "friend",
//     "my friend",
//     "you",
//     "your friend",
//     "my friends' friend",
//     "you are my friend",
//     "I am your friend",
//     "your friend is my friend",
//     "my friend is your friend",
//     "he is my friend",
//     "this is mine",
//     "this is yours",
//     "this is my friends'",
//     "no",
//     "you are not my friend",
//     "this is not yours",
//     "your friend is not my friend",
//     "that is mine",
//     "this is mine, that is yours",
//     "he is not your friend",
//     "no, I am not",
//     "that is not me",
//     "that is not mine, that is my friends'",
//   ];
//   for (const text of texts) {
//     addCard(db, lesson_id, text);
//   }
// }
// englishWordnet();
// englishFreq();
// englishCards();
// englishKaggle();

async function fillFromDump() {
  await db.init();
  // const log = Bun.file("./stuff.log");
  // const logWriter = log.writer();
  let count = 0;
  const fields = new Set<string>();
  // let biggest = 0;
  for await (const line of readWiktionaryDump()) {
    try {
      count++;
      // if (count > 80) break;
      // if (line.length > biggest) {
      //   biggest = line.length;
      //   Bun.write("./biggest.log", line, { createPath: true });
      // }
      const j = JSON.parse(line);
      db.addLanguage(j.lang_code, j.lang);
      db.addCat(j.pos);
      // for (let key of Object.keys(j)) {
      //   if (!fields.has(key)) {
      //     fields.add(key);
      //     logWriter.write(`${line}\n`);
      //   }
      // }
      if (j.lang_code === "en" || j.lang_code === "th") {
        console.log("saving", j.word);
        // console.log(j.sounds);
        const related = {
          derived: j.derived,
          antonyms: j.antonyms,
          synonyms: j.synonyms,
          related: j.related,
        };
        let rhyme = "";
        let ipaExample = "";
        let ipa: any[] = [];
        for (let snd of j.sounds || []) {
          if ("ipa" in snd) {
            ipa.push(snd);
            if (!ipaExample) ipaExample = snd.ipa;
          }
          if ("rhymes" in snd) rhyme = snd.rhymes;
        }
        const isWord = j.word.trim().split(" ").length === 1;
        const type: any = isWord ? "word" : "expression";
        const syllables = ipaExample ? getSyllableCount(ipaExample) : 0;
        console.log({ ipaExample, syllables });
        let prosody: any = {};
        if (ipaExample) {
          const stressedSyllable = getStressedSyllable(ipaExample);
          if ("ok" in stressedSyllable)
            prosody.stressedSyllable = stressedSyllable.ok;
        }
        if (rhyme) prosody.rhyme = rhyme;
        try {
          const row = db.addWord({
            spelling: j.word,
            lang: j.lang_code,
            ipa: JSON.stringify(ipa),
            prosody: JSON.stringify(prosody),
            syllables,
            type,
          });
          let parent_id: number | bigint;
          if (row.changes === 1) parent_id = row.lastInsertRowid;
          else {
            const data: any = db.fetchExpressionBySpelling(j.word, j.lang_code);
            parent_id = data.id;
          }
          const senseRow = db.addSense({
            id: count - 1,
            parent_id,
            spelling: j.word,
            etymology: j.etymology_text || "",
            pos: j.pos,
            ipa: JSON.stringify(ipa),
            prosody: JSON.stringify(prosody),
            senses: JSON.stringify(j.senses),
            forms: JSON.stringify(j.forms || []),
            related: JSON.stringify(related),
          });
        } catch (e) {
          console.log("error inserting", e);
        }
      }
      // langset.add(j.lang_code);
      // if (j.lang === "Translingual") continue;
      // if (j.lang_code === "en") en++;
      // if (j.lang_code === "th") thai++;
      // if (j.lang_code === "zh") zh++;

      // if (j.word === "cat") {
      //   console.log(j.word);
      //   console.log(Object.keys(j));
      //   console.log(j);
      //   console.log("senses", j.senses);
      //   console.log("forms", j.forms);
      //   // console.log("ett", j.etymology_templates);
      //   // console.log("derived", j.derived);
      //   // const meaning: Meaning = {etymology: j.etymology_text}
      //   // const wd = { lang: j.lang_code, spelling: j.word, ipa, {} };
      //   break;
      // }
    } catch (e) {
      console.log("error parsing", e);
    }
  }
  console.log("fields", fields);
}

function addDecks() {
  // const lesson_id = db.addLesson({
  //   name: "Thai Syllables",
  //   description: "All the syllables in the Thai language ordered by frequency",
  //   lang: "th",
  // });
  const syllables: any[] = db.fetchExpressionRaw({
    confidence: "10",
    syllables: "1",
    lang: "th",
  });
  for (let expression of syllables) {
    db.addCard({
      lesson_id: 5,
      eid: expression.id,
      text: "Syllable",
      mnote: "from Sortug Development",
    });
  }
}
function adjustFrequency(lang: string) {
  const frequencies: Set<number> = new Set();
  const all: any[] = db.fetchExpressionRaw({ lang });
  for (let row of all) {
    if (row.frequency) frequencies.add(row.frequency);
  }
  const freqArray = Array.from(frequencies).sort((a, b) => b - a);
  console.log(freqArray);
  for (let row of all) {
    if (row.frequency) {
      const f = freqArray.indexOf(row.frequency);
      if (f === -1) throw new Error("wtf" + row.frequency);
      db.updateWord(row.id, { frequency: f + 1 });
    }
  }
}

// -- INSERT INTO lessons(name, description) values('8000 Super Useful Expressions', 'David Martins Facebook list of coloquial Thai expressions');
// --  INSERT INTO lang_lessons(lesson_id, lang) VALUES(1, 'th');
// -- INSERT INTO lessons(name, description) values('Thai Syllables', 'All syllables in Thai phonology');
// -- INSERT INTO lang_lessons(lesson_id, lang) VALUES(2, 'th');
function addThaiUseful() {
  let idx = 0;
  for (const level in useful) {
    db.addCat(level);
    const exps = (useful as any)[level];
    console.log(level, exps.length);
    for (const exp of exps) {
      const split = exp.ipa.split("/").filter((s) => s.trim());
      const ipa = split.map((ip: any) => ({ ipa: ip, tags: [] }));
      try {
        idx++;
        const tx = db.db.transaction(() => {
          const wid = db.addWord({
            spelling: exp.spelling,
            lang: "th",
            type: "expression",
            ipa: JSON.stringify(ipa),
          });
          console.log({ wid });
          db.addWCat(wid, level);
          if (exp.register) {
            db.addCat(exp.register);
            db.addWCat(wid, exp.register);
          }
          const glosses = [exp.english];
          if (exp.note) glosses.push(exp.note);
          db.addSense({
            parent_id: wid,
            spelling: exp.spelling,
            senses: JSON.stringify([{ glosses }]),
          });
          db.addCard({
            text: `Super Useful ${idx}`,
            eid: wid as any,
            lesson_id: 1,
          });
        });
        tx();
      } catch (e) {
        console.log({ exp });
        console.error(`${e}`);
        // break;
      }
    }
  }
}

function addThaiSyllablesLesson() {
  const res = db.db
    .query(
      "SELECT id FROM expressions e WHERE e.type = 'syllable' and e.lang = 'th'",
    )
    .all() as any[];
  for (const row of res) {
    db.addCard({ text: "Syllable", eid: row.id, lesson_id: 2 });
  }
}
// function fixIpa() {
//   const res = db.db.query(`SELECT id, ipa FROM expressions`).all() as any[];
//   for (const row of res) {
//     try {
//       const jon = JSON.parse(row.ipa);
//     } catch (_) {
//       const clean: string = row.ipa.replace("...", "").trim();
//       db.db.query(`UPDATE expressions SET ipa = ? WHERE `).run(JSON.stringify(ipa));
//     }
//   }
// }
addThaiUseful();
// addThaiSyllablesLesson();

// adjustFrequency("th");

// addDecks();
// fillFromDump();
// thaiSyllables();
// thaiFreq();