// OPFS 기반 WebAssembly SQLite (sql.js) 초기화 및 동기화
// 가벼운 전체 파일 교체 방식. 향후 Supabase 연동 시 증분 동기화로 확장 가능.

const DB = (() => {
  let SQL, db, ready;
  const OPFS_FILE = 'faults.db';
  const STORE_KEY = 'db-version';

  async function ensureSqlJs() {
    if (SQL) return SQL;
    // sql.js CDN
    const module = await window.initSqlJs({ locateFile: f => `https://sql.js.org/dist/${f}` });
    SQL = module;
    return SQL;
  }

  async function readFromOpfs() {
    if (!('storage' in navigator) || !('getDirectory' in navigator.storage)) return null;
    try {
      const root = await navigator.storage.getDirectory();
      const handle = await root.getFileHandle(OPFS_FILE, { create: false }).catch(() => null);
      if (!handle) return null;
      const file = await handle.getFile();
      return new Uint8Array(await file.arrayBuffer());
    } catch { return null; }
  }

  async function writeToOpfs(bytes) {
    if (!('storage' in navigator) || !('getDirectory' in navigator.storage)) return;
    const root = await navigator.storage.getDirectory();
    const handle = await root.getFileHandle(OPFS_FILE, { create: true });
    const w = await handle.createWritable();
    await w.write(bytes);
    await w.close();
  }

  async function init() {
    if (ready) return ready;
    ready = (async () => {
      await ensureSqlJs();
      // 1) OPFS에서 기존 DB 로드
      const bytes = await readFromOpfs();
      if (bytes) {
        db = new SQL.Database(bytes);
      } else {
        // 없으면 서버에서 부트스트랩 DB 다운로드
        const buf = await fetch('/api/db').then(r => r.arrayBuffer());
        db = new SQL.Database(new Uint8Array(buf));
        await persist();
      }
      return true;
    })();
    return ready;
  }

  async function persist() {
    const data = db.export();
    await writeToOpfs(data);
  }

  async function syncIfNeeded() {
    // 서버 버전 확인 후 다르면 교체
    try {
      const remoteMeta = await fetch('/api/meta').then(r => r.json());
      const localMeta = getLocalMeta();
      if (!localMeta || Number(remoteMeta.version) > Number(localMeta.version)) {
        const buf = await fetch('/api/db').then(r => r.arrayBuffer());
        db = new SQL.Database(new Uint8Array(buf));
        await persist();
        setLocalMeta(remoteMeta);
        return true;
      }
    } catch {}
    return false;
  }

  function getLocalMeta() {
    try { return JSON.parse(localStorage.getItem(STORE_KEY)); } catch { return null; }
  }
  function setLocalMeta(meta) {
    localStorage.setItem(STORE_KEY, JSON.stringify(meta));
  }

  function query(sql, params = []) {
    const stmt = db.prepare(sql);
    stmt.bind(params);
    const rows = [];
    while (stmt.step()) rows.push(stmt.getAsObject());
    stmt.free();
    return rows;
  }

  return { init, syncIfNeeded, query, persist };
})();

window.DB = DB;