const express = require('express');
const path = require('path');
const fs = require('fs');
const initSqlJs = require('sql.js');

const app = express();
const PORT = process.env.PORT || 3000;
const DB_PATH = path.join(__dirname, 'eviktools.db');

let db = null;

async function initDb() {
  const SQL = await initSqlJs();
  if (fs.existsSync(DB_PATH)) {
    const buf = fs.readFileSync(DB_PATH);
    db = new SQL.Database(buf);
  } else {
    db = new SQL.Database();
  }

  db.run(`
    CREATE TABLE IF NOT EXISTS tools (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      slug TEXT UNIQUE NOT NULL,
      description TEXT,
      icon TEXT,
      enabled INTEGER DEFAULT 1,
      sort_order INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS calculation_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tool_slug TEXT NOT NULL,
      inputs TEXT,
      result TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_history_tool ON calculation_history(tool_slug);
  `);

  const count = db.exec('SELECT COUNT(*) as count FROM tools')[0]?.values[0][0] || 0;
  if (count === 0) {
    const insert = db.prepare('INSERT INTO tools (name, slug, description, icon, sort_order) VALUES (?, ?, ?, ?, ?)');
    [
      ['Slope Calculator', 'slope', 'Calculate rise over run', '📐', 1],
      ['Unit Converter', 'converter', 'Convert distance units', '↔️', 2],
      ['Volt & Cables', 'volt-cables', 'Voltage drop and cable sizing', '⚡', 3],
      ['Volume Calculator', 'volume', 'Calculate volume by shape', '📦', 4]
    ].forEach(([name, slug, desc, icon, order]) => {
      insert.run([name, slug, desc, icon, order]);
    });
    insert.free();
  }
}

function saveDb() {
  if (db) {
    const data = db.export();
    const buf = Buffer.from(data);
    fs.writeFileSync(DB_PATH, buf);
  }
}

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

app.get('/api/tools', (req, res) => {
  const rows = db.exec('SELECT * FROM tools WHERE enabled = 1 ORDER BY sort_order');
  const cols = rows[0]?.columns || [];
  const values = rows[0]?.values || [];
  const tools = values.map(v => Object.fromEntries(cols.map((c, i) => [c, v[i]])));
  res.json(tools);
});

app.post('/api/history', (req, res) => {
  const { tool_slug, inputs, result } = req.body;
  if (!tool_slug || !result) return res.status(400).json({ error: 'Missing fields' });
  db.run('INSERT INTO calculation_history (tool_slug, inputs, result) VALUES (?, ?, ?)',
    [tool_slug, JSON.stringify(inputs || {}), String(result)]);
  saveDb();
  res.json({ ok: true });
});

app.post('/api/tools', (req, res) => {
  const { name, slug, description, icon } = req.body;
  if (!name || !slug) return res.status(400).json({ error: 'Name and slug required' });
  try {
    db.run(
      'INSERT INTO tools (name, slug, description, icon, sort_order) VALUES (?, ?, ?, ?, (SELECT COALESCE(MAX(sort_order), 0) + 1 FROM tools))',
      [name, slug, description || '', icon || '🔧']);
    saveDb();
    res.json({ ok: true });
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

initDb().then(() => {
  app.listen(PORT, () => {
    console.log(`EvikTools running at http://localhost:${PORT}`);
  });
});

process.on('exit', () => { saveDb(); });
