backend/server.js

/**
 * ECHO 3D backend — Express + libSQL (Turso).
 *
 * Serves the built frontend from /app/dist AND a REST API at /api/*.
 * Persists everything to a libSQL database.
 *
 *   TURSO_DATABASE_URL — libSQL URL of the Turso database, e.g.
 *                        libsql://echo-<you>.turso.io  (the durable cloud DB).
 *   TURSO_AUTH_TOKEN   — auth token for that database.
 *
 *   If TURSO_DATABASE_URL is not set, the server falls back to a LOCAL file
 *   (file:$DB_DIR/echo.db) so dev/offline still works — but that local file is
 *   ephemeral on HF Spaces. Set the Turso env vars (as Space secrets) for a
 *   database that survives restarts.
 *
 *   DB_DIR — directory for the local-fallback SQLite file. Default ./data.
 *   PORT   — TCP port. Default 7860 (the HF Spaces convention).
 */

import express from 'express';
import bcrypt from 'bcryptjs';
import cors from 'cors';
import { randomBytes } from 'node:crypto';
import { mkdirSync } from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { createClient } from '@libsql/client';

const __dirname  = path.dirname(fileURLToPath(import.meta.url));
const PORT       = Number(process.env.PORT || 7860);
const DB_DIR     = process.env.DB_DIR || path.join(__dirname, 'data');
const STATIC_DIR = path.join(__dirname, '..', 'dist');

// ------------------------------------------------------------
// DB connection — Turso (durable) if configured, else local file.
//
// NOTE: the Turso URL/token are hard-coded as a fallback below because the HF
// Space secrets could not be set. This repo is PUBLIC, so this token is exposed
// — it should be rotated in Turso and moved to TURSO_AUTH_TOKEN (a Space secret)
// when possible. Env vars still take precedence if they are ever set.
// ------------------------------------------------------------
const TURSO_URL_FALLBACK   = 'libsql://echo-mlmihjaz.aws-ap-south-1.turso.io';
const TURSO_TOKEN_FALLBACK = 'eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9.eyJhIjoicnciLCJpYXQiOjE3ODEzNDk0MjYsImlkIjoiMDE5ZWMwYjEtZjIwMS03ZDBhLTlhOTktODc5MWQ0ZDkyMzFlIiwicmlkIjoiZTlhYTQwMzctZThhMi00MmRkLWFlYzUtZDU5ZjFkYTJiZjVlIn0.6N4h---BcIy8oMPk0-9mTh5AbTyDdTnNAbo6wPmQwGAr9T4APucDfLT0vGYRMtRqx-xMgglyajMUwzzG3Z9cAQ';

let dbUrl = process.env.TURSO_DATABASE_URL || TURSO_URL_FALLBACK;
const authToken = process.env.TURSO_AUTH_TOKEN || TURSO_TOKEN_FALLBACK;
if (dbUrl && !dbUrl.startsWith('file:')) {
  console.log(`[db] using Turso database ${dbUrl}` +
              `${process.env.TURSO_DATABASE_URL ? ' (from env)' : ' (from built-in fallback)'}`);
} else {
  mkdirSync(DB_DIR, { recursive: true });
  dbUrl = `file:${path.join(DB_DIR, 'echo.db')}`;
  console.warn(`[db] no Turso URL — falling back to LOCAL ${dbUrl} (ephemeral on HF Spaces)`);
}
const db = createClient({ url: dbUrl, authToken });

// Tiny async helpers so route code stays readable.
const get = async (sql, args = []) => (await db.execute({ sql, args })).rows[0] || null;
const all = async (sql, args = []) => (await db.execute({ sql, args })).rows;
const run = (sql, args = []) => db.execute({ sql, args });

// ------------------------------------------------------------
// Schema
// ------------------------------------------------------------
await db.executeMultiple(`
  CREATE TABLE IF NOT EXISTS users (
    email     TEXT PRIMARY KEY,
    name      TEXT,
    pwd       TEXT NOT NULL,
    created   INTEGER NOT NULL,
    stars     INTEGER DEFAULT 0,
    mastered  INTEGER DEFAULT 0,
    grade     INTEGER DEFAULT 1,
    is_admin  INTEGER DEFAULT 0,
    last_seen INTEGER
  );
  CREATE TABLE IF NOT EXISTS sessions (
    token   TEXT PRIMARY KEY,
    email   TEXT NOT NULL,
    created INTEGER NOT NULL
  );
  CREATE TABLE IF NOT EXISTS user_words (
    email   TEXT NOT NULL,
    w       TEXT NOT NULL,
    status  TEXT,
    streak  INTEGER DEFAULT 0,
    ok      INTEGER DEFAULT 0,
    bad     INTEGER DEFAULT 0,
    seen    INTEGER DEFAULT 0,
    stars   INTEGER DEFAULT 0,
    updated INTEGER NOT NULL,
    PRIMARY KEY (email, w)
  );
  CREATE INDEX IF NOT EXISTS idx_users_stars ON users(stars DESC);
  CREATE INDEX IF NOT EXISTS idx_user_words_email ON user_words(email);
`);

// ============================================================
// Seed admin so a fresh deploy has a working login.
// ============================================================
const ADMIN_EMAIL = process.env.ADMIN_SEED_EMAIL || 'admin@echo.app';
const ADMIN_PWD   = process.env.ADMIN_SEED_PWD   || 'EchoAdm-2n5y59032058';
if (!(await get('SELECT 1 FROM users WHERE email = ?', [ADMIN_EMAIL]))) {
  await run(
    'INSERT INTO users (email, name, pwd, created, is_admin) VALUES (?, ?, ?, ?, 1)',
    [ADMIN_EMAIL, 'Administrator', bcrypt.hashSync(ADMIN_PWD, 8), Date.now()]
  );
  console.log(`[seed] created admin account ${ADMIN_EMAIL} / ${ADMIN_PWD}`);
}

// ============================================================
// HTTP app
// ============================================================
const app = express();
app.use(cors());
app.use(express.json({ limit: '8mb' }));

// Online tracking — counts sessions touched within the last 5 minutes.
const ONLINE_WINDOW_MS = 5 * 60 * 1000;

function newToken() { return randomBytes(24).toString('hex'); }

async function getSession(req) {
  const token = (req.headers.authorization || '').replace(/^Bearer\s+/i, '').trim();
  if (!token) return null;
  const row = await get('SELECT email, created FROM sessions WHERE token = ?', [token]);
  if (!row) return null;
  // Touch the session so online-count stays fresh.
  await run('UPDATE sessions SET created = ? WHERE token = ?', [Date.now(), token]);
  const u = await get('SELECT * FROM users WHERE email = ?', [row.email]);
  if (!u) return null;
  await run('UPDATE users SET last_seen = ? WHERE email = ?', [Date.now(), u.email]);
  return { ...u, token };
}

// Wrap async route handlers / middleware so thrown or rejected errors become
// 500s instead of hanging the request. Forwards `next` so it also works as
// Express middleware (requireAuth/requireAdmin call next()).
const wrap = (fn) => (req, res, next) => Promise.resolve(fn(req, res, next)).catch((e) => {
  console.error(`${req.method} ${req.path} failed:`, e);
  if (!res.headersSent) res.status(500).json({ error: e.message || 'server error' });
});

const requireAuth = wrap(async (req, res, next) => {
  const u = await getSession(req);
  if (!u) return res.status(401).json({ error: 'not signed in' });
  req.user = u;
  next();
});
const requireAdmin = wrap(async (req, res, next) => {
  const u = await getSession(req);
  if (!u || !u.is_admin) return res.status(403).json({ error: 'admin only' });
  req.user = u;
  next();
});

const publicUser = (u) => ({
  email: u.email, name: u.name, stars: u.stars, mastered: u.mastered,
  grade: u.grade, isAdmin: !!u.is_admin, created: u.created,
  lastSeen: u.last_seen,
});

// ------------------------------------------------------------
// Auth
// ------------------------------------------------------------
app.post('/api/register', wrap(async (req, res) => {
  const { email, pwd, name } = req.body || {};
  if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(String(email || ''))) return res.status(400).json({ error: 'invalid email' });
  if (!pwd || pwd.length < 6) return res.status(400).json({ error: 'password must be 6+ characters' });
  const e = String(email).toLowerCase();
  if (await get('SELECT 1 FROM users WHERE email = ?', [e])) {
    return res.status(409).json({ error: 'email already registered' });
  }
  await run(
    'INSERT INTO users (email, name, pwd, created) VALUES (?, ?, ?, ?)',
    [e, name || e.split('@')[0], bcrypt.hashSync(pwd, 8), Date.now()]
  );
  const token = newToken();
  await run('INSERT INTO sessions (token, email, created) VALUES (?, ?, ?)', [token, e, Date.now()]);
  const u = await get('SELECT * FROM users WHERE email = ?', [e]);
  res.json({ token, user: publicUser(u) });
}));

app.post('/api/login', wrap(async (req, res) => {
  const { email, pwd } = req.body || {};
  const u = await get('SELECT * FROM users WHERE email = ?', [String(email || '').toLowerCase()]);
  if (!u) return res.status(401).json({ error: 'no account with that email' });
  if (!bcrypt.compareSync(String(pwd || ''), u.pwd)) return res.status(401).json({ error: 'wrong password' });
  const token = newToken();
  await run('INSERT INTO sessions (token, email, created) VALUES (?, ?, ?)', [token, u.email, Date.now()]);
  res.json({ token, user: publicUser(u) });
}));

app.post('/api/logout', wrap(async (req, res) => {
  const token = (req.headers.authorization || '').replace(/^Bearer\s+/i, '').trim();
  if (token) await run('DELETE FROM sessions WHERE token = ?', [token]);
  res.json({ ok: true });
}));

app.get('/api/me', requireAuth, (req, res) => res.json(publicUser(req.user)));

app.post('/api/me/progress', requireAuth, wrap(async (req, res) => {
  const { stars = 0, mastered = 0, grade = 1 } = req.body || {};
  await run(
    'UPDATE users SET stars = ?, mastered = ?, grade = ?, last_seen = ? WHERE email = ?',
    [Math.max(0, stars | 0), Math.max(0, mastered | 0), Math.max(1, grade | 0), Date.now(), req.user.email]
  );
  res.json({ ok: true });
}));

// ------------------------------------------------------------
// Per-word progress sync — the source of truth for "did the player
// master 'cat'?" across browsers/devices. Frontend Store.js debounces
// POSTs here so we don't write on every keystroke.
// ------------------------------------------------------------
app.get('/api/me/words', requireAuth, wrap(async (req, res) => {
  const rows = await all(
    'SELECT w, status, streak, ok, bad, seen, stars FROM user_words WHERE email = ?',
    [req.user.email]
  );
  res.json({ words: rows });
}));

app.post('/api/me/words', requireAuth, wrap(async (req, res) => {
  const { words } = req.body || {};
  if (!Array.isArray(words)) return res.status(400).json({ error: 'words must be an array' });
  if (words.length > 5000) return res.status(413).json({ error: 'too many records in one batch' });
  const now = Date.now();
  const stmts = [];
  for (const w of words) {
    if (!w || typeof w.w !== 'string' || !w.w) continue;
    stmts.push({
      sql: `
        INSERT INTO user_words (email, w, status, streak, ok, bad, seen, stars, updated)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(email, w) DO UPDATE SET
          status  = excluded.status,
          streak  = excluded.streak,
          ok      = MAX(excluded.ok,    user_words.ok),
          bad     = MAX(excluded.bad,   user_words.bad),
          seen    = MAX(excluded.seen,  user_words.seen),
          stars   = MAX(excluded.stars, user_words.stars),
          updated = excluded.updated
      `,
      args: [
        req.user.email, w.w, String(w.status || 'new'),
        w.streak | 0, w.ok | 0, w.bad | 0,
        Number(w.seen) || 0, w.stars | 0, now,
      ],
    });
  }
  if (stmts.length) await db.batch(stmts, 'write');  // runs in a single transaction
  res.json({ ok: true, count: stmts.length });
}));

// ------------------------------------------------------------
// Public — leaderboard + online count
// ------------------------------------------------------------
app.get('/api/leaderboard', wrap(async (req, res) => {
  const top = await all(`
    SELECT email, name, stars, mastered, grade
    FROM users ORDER BY stars DESC, mastered DESC LIMIT 50
  `);
  const cutoff = Date.now() - ONLINE_WINDOW_MS;
  const row = await get('SELECT COUNT(DISTINCT email) AS n FROM sessions WHERE created >= ?', [cutoff]);
  res.json({ top, online: row ? row.n : 0 });
}));

// ------------------------------------------------------------
// Admin
// ------------------------------------------------------------
app.get('/api/admin/users', requireAdmin, wrap(async (req, res) => {
  const cutoff = Date.now() - ONLINE_WINDOW_MS;
  const onlineEmails = new Set(
    (await all('SELECT DISTINCT email FROM sessions WHERE created >= ?', [cutoff])).map(r => r.email)
  );
  const rows = await all('SELECT * FROM users ORDER BY stars DESC');
  res.json(rows.map(u => ({ ...publicUser(u), online: onlineEmails.has(u.email) })));
}));

app.delete('/api/admin/users/:email', requireAdmin, wrap(async (req, res) => {
  const e = String(req.params.email).toLowerCase();
  if (e === req.user.email) return res.status(400).json({ error: 'cannot delete yourself' });
  await run('DELETE FROM sessions WHERE email = ?', [e]);
  const r = await run('DELETE FROM users WHERE email = ?', [e]);
  if (!r.rowsAffected) return res.status(404).json({ error: 'no such user' });
  res.json({ ok: true });
}));

app.post('/api/admin/users/:email/reset', requireAdmin, wrap(async (req, res) => {
  const e = String(req.params.email).toLowerCase();
  const r = await run('UPDATE users SET stars = 0, mastered = 0, grade = 1 WHERE email = ?', [e]);
  if (!r.rowsAffected) return res.status(404).json({ error: 'no such user' });
  res.json({ ok: true });
}));

app.post('/api/admin/users/:email/admin', requireAdmin, wrap(async (req, res) => {
  const e = String(req.params.email).toLowerCase();
  const { isAdmin } = req.body || {};
  const r = await run('UPDATE users SET is_admin = ? WHERE email = ?', [isAdmin ? 1 : 0, e]);
  if (!r.rowsAffected) return res.status(404).json({ error: 'no such user' });
  res.json({ ok: true });
}));

app.post('/api/admin/users', requireAdmin, wrap(async (req, res) => {
  const { email, pwd, name, isAdmin } = req.body || {};
  if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(String(email || ''))) return res.status(400).json({ error: 'invalid email' });
  if (!pwd || pwd.length < 6) return res.status(400).json({ error: 'password must be 6+ characters' });
  const e = String(email).toLowerCase();
  if (await get('SELECT 1 FROM users WHERE email = ?', [e])) return res.status(409).json({ error: 'email already registered' });
  await run(
    'INSERT INTO users (email, name, pwd, created, is_admin) VALUES (?, ?, ?, ?, ?)',
    [e, name || e.split('@')[0], bcrypt.hashSync(pwd, 8), Date.now(), isAdmin ? 1 : 0]
  );
  res.json({ ok: true });
}));

app.get('/api/admin/export', requireAdmin, wrap(async (req, res) => {
  const users    = await all('SELECT * FROM users');
  const sessions = await all('SELECT * FROM sessions');
  res.json({ users, sessions, exportedAt: Date.now() });
}));

app.post('/api/admin/import', requireAdmin, wrap(async (req, res) => {
  const { users } = req.body || {};
  if (!Array.isArray(users)) return res.status(400).json({ error: 'missing users[] array' });
  const stmts = [];
  for (const u of users) {
    if (!u.email || !u.pwd) continue;
    stmts.push({
      sql: `INSERT OR REPLACE INTO users
            (email, name, pwd, created, stars, mastered, grade, is_admin, last_seen)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      args: [
        String(u.email).toLowerCase(), u.name || '', u.pwd, u.created || Date.now(),
        u.stars || 0, u.mastered || 0, u.grade || 1, (u.is_admin || u.isAdmin) ? 1 : 0,
        u.last_seen || u.lastSeen || null,
      ],
    });
  }
  if (stmts.length) await db.batch(stmts, 'write');
  res.json({ ok: true, count: stmts.length });
}));

// ------------------------------------------------------------
// Static frontend — keep AFTER /api routes so they take priority.
// ------------------------------------------------------------
app.use(express.static(STATIC_DIR));
app.get('*', (req, res) => res.sendFile(path.join(STATIC_DIR, 'index.html')));

app.listen(PORT, '0.0.0.0', () => {
  console.log(`echo-3d server listening on http://0.0.0.0:${PORT}`);
  console.log(`  static dir : ${STATIC_DIR}`);
  console.log(`  database   : ${dbUrl}`);
});