import initSqlJs from 'sql.js';
import path from 'path';
import fs from 'fs';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dataDir = path.join(__dirname, '..', 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });
const dbPath = process.env.DATABASE_PATH || path.join(dataDir, 'configurateur.db');
const schemaPath = path.join(__dirname, 'schema.sql');

let db = null;

/** Création idempotente — bases anciennes sans ces tables (avant migration 014). */
const HARDWARE_CATALOG_DDL = `
CREATE TABLE IF NOT EXISTS blum_hardware (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,
  variant         TEXT,
  price_chf       REAL NOT NULL,
  specs_json      TEXT,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS handle_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  style           TEXT,
  length_mm       INTEGER,
  price_chf       REAL NOT NULL,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS hinge_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,
  brand           TEXT DEFAULT 'Blum',
  price_chf       REAL NOT NULL,
  active          INTEGER DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_blum_hardware_type ON blum_hardware(type);
CREATE INDEX IF NOT EXISTS idx_handle_variants_style ON handle_variants(style);
CREATE INDEX IF NOT EXISTS idx_hinge_variants_type ON hinge_variants(type);
`;

function toSqlitePlaceholders(sql) {
  return sql.replace(/\$(\d+)/g, '?');
}

function save() {
  if (db && dbPath) {
    const data = db.export();
    fs.writeFileSync(dbPath, Buffer.from(data));
  }
}

async function getDb() {
  if (db) return db;
  const SQL = await initSqlJs({
    locateFile: (file) => path.join(__dirname, '..', 'node_modules', 'sql.js', 'dist', file)
  });
  let buffer;
  if (fs.existsSync(dbPath)) {
    buffer = fs.readFileSync(dbPath);
  }
  db = new SQL.Database(buffer || null);
  try {
    // Ensure full schema exists for legacy/partial DB files.
    if (fs.existsSync(schemaPath)) {
      const schemaSql = fs.readFileSync(schemaPath, 'utf8');
      db.exec(schemaSql);
    }
    db.exec(HARDWARE_CATALOG_DDL);
    const data = db.export();
    fs.writeFileSync(dbPath, Buffer.from(data));
  } catch (e) {
    console.error('[db] ensureHardwareCatalogTables:', e.message);
  }
  return db;
}

export const dbReady = getDb();

export const query = async (sql, params = []) => {
  const database = await getDb();
  const sqliteSql = toSqlitePlaceholders(sql);
  const stmt = database.prepare(sqliteSql);
  stmt.bind(Array.isArray(params) ? params : []);
  const rows = [];
  while (stmt.step()) rows.push(stmt.getAsObject());
  stmt.free();
  return { rows };
};

export const run = async (sql, params = []) => {
  const database = await getDb();
  const sqliteSql = toSqlitePlaceholders(sql);
  database.run(sqliteSql, Array.isArray(params) ? params : []);
  save();
  return {};
};

const pool = {
  query: (sql, params) => query(sql, params)
};

export default pool;