import Database from "bun:sqlite";
import { Phoneme, Tone } from "../types/phonetics";
type Str = string | null;
type ItemType = "word" | "syllable" | "idiom";

class DatabaseHandler {
  db: Database;
  constructor() {
    const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/phon.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(
    wordId: number | bigint,
    ipa: string,
    syllables: number,
    tags: Str,
    notes: Str,
  ) {
    const query = this.db
      .query(
        `INSERT OR IGNORE INTO word_phonetics(word_id,ipa, syllables, tag, notes) VALUES(?, ?, ?, ?, ?)`,
      )
      .run(wordId, ipa, syllables, tags, notes);
  }
  addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) {
    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_wrhymes(word_id, wrhyme_id) VALUES(?, ?)
          `,
      )
      .run(wordId, query.id);
  }
  addIdiom(spelling: string, lang: string) {
    const query = this.db.query(
      `INSERT OR IGNORE 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,
    frequency: number | null,
    notes: Str,
  ) {
    const query = this.db.query(
      `INSERT OR IGNORE INTO words(spelling, lang, frequency, notes) VALUES(?, ?, ?, ?)`,
      // `INSERT INTO words(spelling, lang) VALUES(?, ?)`,
    );
    const res = query.run(spelling, lang, frequency, notes);
    const wordId = res.lastInsertRowid;
    return wordId;
  }
  addSyllable(
    wordId: number | bigint,
    sylIdx: number,
    lang: string,
    ipa: string,
    long: boolean,
    text: string,
    onset: Phoneme,
    medial: Phoneme,
    nucleus: Phoneme,
    coda: Phoneme,
    rhyme: Phoneme,
    tone: Tone,
    notes: Str,
  ) {
    const tx = this.db.transaction(() => {
      const onsetId = this.db
        .query(
          `INSERT INTO onsets(ipa, lang, text) VALUES(?, ?, ?)
           ON CONFLICT(ipa, lang, text) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(onset.ipa, lang, onset.spelling) as number;
      const medialId = this.db
        .query(
          `INSERT INTO medials(ipa, lang, text) VALUES(?, ?, ?)
           ON CONFLICT(ipa, lang, text) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(medial.ipa, lang, medial.spelling) as number;
      const nucleusId = this.db
        .query(
          `INSERT INTO nucleus(ipa, lang, text) VALUES(?, ?, ?)
         ON CONFLICT(ipa, lang, text) DO UPDATE SET
         text = excluded.text
         RETURNING rowid
        `,
        )
        .get(nucleus.ipa, lang, nucleus.spelling) as number;
      const codaId = this.db
        .query(
          `INSERT INTO codas(ipa, lang, text) VALUES(?, ?, ?)
           ON CONFLICT(ipa, lang, text) DO UPDATE SET
           text = excluded.text
           RETURNING rowid
          `,
        )
        .get(coda.ipa, lang, coda.spelling) as number;
      const rhymeId = this.db
        .query(
          `INSERT INTO rhymes(ipa, lang, text) VALUES(?, ?, ?)
         ON CONFLICT(ipa, lang, text) DO UPDATE SET
         text = excluded.text
         RETURNING rowid
        `,
        )
        .get(rhyme.ipa, lang, rhyme.spelling) as number;
      const toneId = this.db
        .query(
          `INSERT INTO tones(ipa, lang, name, nums) VALUES(?, ?, ?, ?)
           ON CONFLICT(ipa, lang) DO UPDATE SET
           ipa = excluded.ipa
           RETURNING rowid
          `,
        )
        .get(tone.letters, lang, tone.name, tone.numbers) as number;

      const query = this.db.query(
        `INSERT INTO syllables(lang, ipa, long, text, onset, medial, nucleus, coda, rhyme, tone, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      );
      // TODO need a dual structure here for IPA and orto
      const res = query.run(
        lang,
        ipa,
        long,
        text,
        onsetId,
        medialId,
        nucleusId,
        codaId,
        rhymeId,
        toneId,
        notes,
      );
      const sylId = res.lastInsertRowid;
      const ipaq = this.db.query(`
        INSERT INTO syl_ipa(syl_id, ipa, onset, medial, nucleus, coda, rhyme, notes)
        VALUES(?, ?, ?, ?, ?, ?, ?, ?)`);
      ipaq.run(
        sylId,
        ipa,
        onset.ipa,
        medial.ipa,
        nucleus.ipa,
        coda.ipa,
        rhyme.ipa,
        null,
      );
      //
      const res1 = this.db
        .query(
          `INSERT INTO syllables_words(syl_id, word_id, idx) VALUES(?, ?, ?)`,
        )
        .run(sylId, wordId, sylIdx);
      //
      return sylId;
    });
    const sylId = tx();
  }

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

export default db;