import Database from "bun:sqlite";
type Str = string | null;
type ItemType = "word" | "syllable" | "idiom";

class DatabaseHandler {
  db: Database;
  constructor() {
    const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/prosodynew.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("./prosodyschema.sql");
    const sql = await file.text();
    this.db.exec(sql);
  }
  // selects
  fetchWords(words: string[]) {
    const query = this.db.query(
      `SELECT id FROM words where spelling IN (${words.map((w) => `'${w}'`).join(", ")})`,
    );
    return query.all() as Array<{ id: number }>;
  }
  // inserts

  addLanguage(code: string, name: string) {
    const query = this.db
      .query(`INSERT OR IGNORE INTO languages(iso6392, english) VALUES(?, ?)`)
      .run(code, name);
  }
  addPronunciation(
    type: ItemType,
    parentId: number | bigint,
    ipa: string,
    syllables: number,
    tags: Str,
    notes: Str,
  ) {
    try {
      const query = this.db
        .query(
          `INSERT INTO pronunciation(type, parent_id,ipa, syllables, tag, notes) VALUES(?, ?, ?, ?, ?, ?)`,
        )
        .run(type, parentId, ipa, syllables, tags, notes);
    } catch (e) {
      // console.error(e);
    }
  }
  addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) {
    try {
      const query = this.db
        .query(
          `INSERT INTO word_rhymes(text, lang, notes) VALUES(?, ?, ?)
                 ON CONFLICT(text,lang) DO UPDATE SET
                 text = excluded.text
                 RETURNING rowid
          `,
        )
        .get(ipa, lang, notes) as { id: number };
      const query2 = this.db
        .query(
          `
            INSERT INTO words_idioms(word_id, idiom_id) VALUES(?, ?)
          `,
        )
        .run(wordId, query.id);
    } catch (e) {
      // console.error(e);
    }
  }
  addIdiom(spelling: string, lang: string) {
    const query = this.db.query(
      `INSERT INTO idioms(spelling, lang) VALUES(?, ?)`,
    );
    const res = query.run(spelling, lang);
    return res;
  }
  findIdiomWords(spelling: string, idId: number | bigint) {
    const split = spelling.split(" ");
    const words = this.fetchWords(split);
    console.log({ words });
    const tx = this.db.transaction(() => {
      for (const w of words) {
        this.db
          .query(
            `
            INSERT INTO words_idioms(word_id, idiom_id) VALUES(?, ?)
          `,
          )
          .run(w.id, idId);
      }
    });
    tx();
  }
  findIdiomsWords() {
    const rows: any = this.db.query(`SELECT id, spelling FROM idioms`);
    for (const row of rows) {
      this.findIdiomWords(row.spelling, row.id);
    }
  }
  addWord(spelling: string, lang: string) {
    const query = this.db.query(
      // `INSERT OR IGNORE INTO words(spelling, lang) VALUES(?, ?)`,
      `INSERT INTO words(spelling, lang) VALUES(?, ?)`,
    );
    const res = query.run(spelling, lang);
    const wordId = res.lastInsertRowid;
    return wordId;
  }
  addSyllable(
    wordId: number | bigint,
    text: string,
    lang: string,
    long: boolean,
    onset: Str,
    medial: Str,
    nucleus: string,
    coda: Str,
    rhyme: string,
    tone: Str,
    notes: Str,
  ) {
    const tx = this.db.transaction(() => {
      const query = this.db.query(
        `INSERT INTO syllables(text, lang, long, onset, medial, nucleus, coda, rhyme, tone, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      );
      const res = query.run(
        text,
        lang,
        long,
        onset,
        medial,
        nucleus,
        coda,
        rhyme,
        tone,
        notes,
      );
      const sylId = res.lastInsertRowid;

      const res1 = this.db
        .query(`INSERT INTO syllables_words(syl_id, word_id) VALUES(?, ?)`)
        .run(sylId, wordId);
      //
      return sylId;
    });
    const sylId = tx();
    let res1: any;
    if (onset) {
      res1 = this.db
        .query(
          `INSERT INTO onsets(text, lang) VALUES(?, ?)
           ON CONFLICT(text, lang) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(onset, lang);
      this.db
        .query(`INSERT INTO onsets_syllables(syl_id, onset_id) VALUES(?, ?)`)
        .run(sylId, res1.id);
    }
    if (medial) {
      res1 = this.db
        .query(
          `INSERT INTO medials(text, lang) VALUES(?, ?)
           ON CONFLICT(text, lang) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(medial, lang);
      this.db
        .query(`INSERT INTO medials_syllables(syl_id, medial_id) VALUES(?, ?)`)
        .run(sylId, res1.id);
    }
    res1 = this.db
      .query(
        `INSERT INTO nucleus(text, lang) VALUES(?, ?)
         ON CONFLICT(text, lang) DO UPDATE SET
         text = excluded.text
         RETURNING rowid
        `,
      )
      .get(nucleus, lang);
    this.db
      .query(`INSERT INTO nucleus_syllables(syl_id, nucleus_id) VALUES(?, ?)`)
      .run(sylId, res1.id);
    if (coda) {
      res1 = this.db
        .query(
          `INSERT INTO codas(text, lang) VALUES(?, ?)
           ON CONFLICT(text, lang) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(coda, lang);
      this.db
        .query(`INSERT INTO codas_syllables(syl_id, coda_id) VALUES(?, ?)`)
        .run(sylId, res1.id);
    }
    res1 = this.db
      .query(
        `INSERT INTO rhymes(text, lang) VALUES(?, ?)
         ON CONFLICT(text, lang) DO UPDATE SET
         text = excluded.text
         RETURNING rowid
        `,
      )
      .get(rhyme, lang);
    this.db
      .query(`INSERT INTO rhymes_syllables(syl_id, rhyme_id) VALUES(?, ?)`)
      .run(sylId, res1.id);
    if (tone) {
      res1 = this.db
        .query(
          `INSERT INTO tones(text, lang) VALUES(?, ?)
           ON CONFLICT(text, lang) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(tone, lang);
      this.db
        .query(`INSERT INTO tones_syllables(syl_id, tone_id) VALUES(?, ?)`)
        .run(sylId, res1.id);
    }
  }

  // reads
}
const db = new DatabaseHandler();

export default db;