import sqlite3 from "better-sqlite3";
import path from "node:path";
import fs from "node:fs/promises";

const mainDB = sqlite3("tmp/yomitan/index.db");
mainDB.exec(/* sql */ `
  CREATE TABLE IF NOT EXISTS dictionaries (
    id              INTEGER PRIMARY KEY,
    title           TEXT    NOT NULL,
    revision        TEXT    NOT NULL,
    author          TEXT,
    [url]           TEXT,
    [description]   TEXT,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    installed_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    lang            TEXT    NOT NULL,      -- custom
    [data]          TEXT    NOT NULL,      -- JSON
    UNIQUE (title, revision)
  );
`);

async function loadFolder(folderpath: string, lang = "ja") {
  const db = sqlite3(path.join(folderpath, "index.db"));

  db.exec(/* sql */ `
    CREATE TABLE IF NOT EXISTS def_tag_sets (id INTEGER PRIMARY KEY, tags TEXT NOT NULL UNIQUE);
    CREATE TABLE IF NOT EXISTS term_tag_sets (id INTEGER PRIMARY KEY, tags TEXT NOT NULL UNIQUE);
    CREATE TABLE IF NOT EXISTS rule_sets (id INTEGER PRIMARY KEY, rules TEXT NOT NULL UNIQUE);
    CREATE TABLE IF NOT EXISTS terms (
      id              INTEGER PRIMARY KEY,
      term            TEXT NOT NULL,
      reading         TEXT NOT NULL,
      def_tags_id     INTEGER,
      rules_id        INTEGER,
      score           INTEGER,
      glossary        TEXT NOT NULL,    -- JSON
      glossary_id     INTEGER, -- NOT NULL REFERENCES glossaries(id),
      [sequence]      INTEGER,
      term_tags_id    INTEGER
    );
    CREATE TABLE IF NOT EXISTS term_meta (
      id              INTEGER PRIMARY KEY,
      term            TEXT NOT NULL,
      mode            TEXT NOT NULL,
      reading         TEXT,
      [data]          TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS tags (
      id              INTEGER PRIMARY KEY,
      [name]          TEXT NOT NULL,
      category        TEXT,
      sort_order      INTEGER NOT NULL DEFAULT 0,
      notes           TEXT,
      score           INTEGER NOT NULL DEFAULT 0,
      UNIQUE ([name])
    );
    CREATE TABLE IF NOT EXISTS kanji (
      id              INTEGER PRIMARY KEY,
      kanji           TEXT NOT NULL,
      onyomi          TEXT,
      kunyomi         TEXT,
      tags            TEXT,
      meanings        TEXT NOT NULL DEFAULT '[]',   -- JSON
      [stats]         TEXT NOT NULL DEFAULT '{}'    -- JSON
    );
  `);

  db.exec(/* sql */ `
    CREATE INDEX IF NOT EXISTS terms_term_reading_score ON terms(term, reading, score DESC);
  `);

  const stmt = (() => {
    let fields = ["title", "revision", "author", "url", "description", "lang"];
    const index = mainDB.prepare(/* sql */ `
      INSERT INTO dictionaries (${fields.map((f) => `[${f}]`).join(",")}, [data])
      VALUES (${fields.map((f) => ":${f}").join(",")}, :data)
    `);

    fields = [
      "term",
      "reading",
      "def_tags",
      "rules",
      "score",
      "glossary",
      "sequence",
      "term_tags",
    ];
    const term = db.prepare(/* sql */ `
      INSERT INTO term (${fields.map((f) => `[${f}]`).join(",")})
      VALUES (${fields.map(() => "?").join(",")})
    `);

    return {
      index,
      term,
    };
  })();

  for (const filename of await fs.readdir(folderpath)) {
    try {
      if (!filename.endsWith(".json")) continue;

      if (filename === "index.json") {
        const obj = JSON.parse(
          await fs.readFile(path.join(folderpath, filename), "utf-8"),
        );

        stmt.index.run({ lang, data: JSON.stringify(obj), ...obj });
      } else if (filename.startsWith("term_bank_")) {
      }
    } catch (e) {
      console.error(filename);
      throw e;
    }
  }
}