`routes/api.js`

355 lines
const express = require('express');
const fs = require('fs');
const path = require('path');
const { execSync } = require('child_process');
const { pool } = require('../lib/db');
const {
  valueAsset, listAssets, getIndex, listIndices, getFamily, listFamilies, METHODOLOGY_VERSION
} = require('../lib/valuation');
const {
  isValidEmail,
  listHoldings,
  insertHolding,
  patchHolding,
  deleteHolding
} = require('../lib/portfolio');
const { auditMiddleware, recentCalls } = require('../lib/audit');
const { buildSpec } = require('../lib/openapi');

const router = express.Router();

// Mount the immutable audit log on every /api/* call. /api/health is
// skipped inside the middleware to keep monitoring traffic out of the
// table. See lib/audit.js for the full schema + redaction rules.
router.use(auditMiddleware);

// Per-route Cache-Control discipline. Why each rule:
//
//   no-store    : valuations + portfolio + audit (auditable + per-user;
//                 must hit live so audit row writes; never sit in CDN)
//   short cache : list endpoints (sources/assets/indices/openapi) —
//                 minutes-fresh is fine for the catalog
//   no-cache    : health/version (allow conditional GETs but never
//                 serve stale to monitoring)
const NO_STORE  = 'no-store, must-revalidate';
const NO_CACHE  = 'no-cache, must-revalidate';
const PUB_60    = 'public, max-age=60, stale-while-revalidate=300';
const PUB_300   = 'public, max-age=300, stale-while-revalidate=86400';
const PUB_3600  = 'public, max-age=3600, stale-while-revalidate=86400';
function setCache(value) {
  return (req, res, next) => { res.set('Cache-Control', value); next(); };
}

// Resolve build metadata at boot (cheap; doesn't touch the DB).
const PKG_VERSION = (() => {
  try { return require('../package.json').version; } catch { return '0.0.0'; }
})();
const GIT_SHA = (() => {
  try {
    return execSync('git rev-parse --short=12 HEAD', {
      cwd: path.join(__dirname, '..'),
      stdio: ['ignore', 'pipe', 'ignore']
    }).toString().trim();
  } catch { return null; }
})();
const GIT_DIRTY = (() => {
  try {
    const out = execSync('git status --porcelain', {
      cwd: path.join(__dirname, '..'),
      stdio: ['ignore', 'pipe', 'ignore']
    }).toString().trim();
    return out.length > 0;
  } catch { return null; }
})();
const BUILD_TIME = new Date().toISOString();

// Cache the OpenAPI spec at boot — content is build-deterministic, so
// regenerating per-request would just burn CPU.
const OPENAPI_SPEC = buildSpec({ version: PKG_VERSION, gitSha: GIT_SHA });

router.get('/health', setCache(NO_CACHE), async (req, res) => {
  let dbOk = false;
  try {
    await pool.query('SELECT 1');
    dbOk = true;
  } catch (err) {
    console.error('[health] db check failed:', err.message);
  }
  res.json({ ok: true, version: PKG_VERSION, db_ok: dbOk });
});

// /api/version — full build + data introspection. Used by deploy
// scripts, monitoring dashboards, and "is the prod the same as my
// local?" pre-flight checks.
router.get('/version', setCache(NO_CACHE), async (req, res, next) => {
  try {
    const [migs, counts] = await Promise.all([
      pool.query(`SELECT filename, applied_at FROM schema_migrations ORDER BY filename`),
      pool.query(`SELECT
        (SELECT COUNT(*) FROM canonical_assets)::int   AS assets,
        (SELECT COUNT(*) FROM transactions)::int       AS transactions,
        (SELECT COUNT(*) FROM valuation_snapshots)::int AS snapshots,
        (SELECT COUNT(*) FROM indices)::int            AS indices,
        (SELECT COUNT(*) FROM index_points)::int       AS index_points,
        (SELECT COUNT(*) FROM portfolio_holdings)::int AS holdings,
        (SELECT COUNT(*) FROM image_assets)::int       AS images,
        (SELECT MAX(as_of) FROM valuation_snapshots)   AS latest_snapshot_as_of`)
    ]);
    res.json({
      service: 'lifestyle-asset-intel',
      package_version: PKG_VERSION,
      methodology_version: METHODOLOGY_VERSION,
      git: GIT_SHA ? { sha: GIT_SHA, dirty: GIT_DIRTY } : null,
      runtime: {
        node: process.version,
        platform: process.platform,
        arch: process.arch,
        pid: process.pid,
        boot_time: BUILD_TIME,
        uptime_seconds: Math.round(process.uptime())
      },
      schema: {
        migrations_applied: migs.rows.length,
        migrations: migs.rows.map((r) => ({
          filename: r.filename,
          applied_at: r.applied_at
        }))
      },
      data: counts.rows[0]
    });
  } catch (e) { next(e); }
});

// Whitelist of supported /api/sources ?sort= values mapped to safe SQL
// ORDER BY fragments. All sort keys are DB-backed columns (no computed
// values), so SQL-side sort is cleaner than the JS post-sort the other
// list endpoints use. Default mirrors the historical tier+name order.
const SOURCE_SORTS = {
  default:      'tier ASC, name ASC',
  name:         'name ASC',
  slug:         'slug ASC',
  tier:         'tier ASC, name ASC',
  'tier-desc':  'tier DESC, name ASC',
  'weight-desc':'weight DESC, name ASC',
  'weight-asc': 'weight ASC, name ASC',
  kind:         'kind ASC, name ASC',
  enabled:      'enabled DESC, tier ASC, name ASC'
};

router.get('/sources', setCache(PUB_300), async (req, res, next) => {
  try {
    const sort = typeof req.query.sort === 'string' ? req.query.sort.trim() : '';
    const orderBy = SOURCE_SORTS[sort] || SOURCE_SORTS.default;
    const qRaw = typeof req.query.q === 'string' ? req.query.q.trim() : '';
    // Case-insensitive substring filter across the human-meaningful
    // fields. Notes is included because seeded source rows carry the
    // "auction"/"marketplace"/"index" colour in plain English there.
    const params = [];
    let whereSql = '';
    if (qRaw) {
      params.push(`%${qRaw.toLowerCase()}%`);
      whereSql =
        ' WHERE LOWER(slug) LIKE $1 ' +
        '    OR LOWER(name) LIKE $1 ' +
        '    OR LOWER(kind) LIKE $1 ' +
        '    OR LOWER(COALESCE(notes, \'\')) LIKE $1';
    }
    const r = await pool.query(
      `SELECT slug, name, tier, kind, weight, enabled, url, notes
         FROM sources${whereSql}
        ORDER BY ${orderBy}`,
      params
    );
    res.json({
      count: r.rows.length,
      q: qRaw || null,
      sort: sort || null,
      sources: r.rows
    });
  } catch (e) { next(e); }
});

router.get('/assets', setCache(PUB_60), async (req, res, next) => {
  try {
    const q = typeof req.query.q === 'string' ? req.query.q : '';
    const sort = typeof req.query.sort === 'string' ? req.query.sort : '';
    const rows = await listAssets({ q, sort });
    res.json({ count: rows.length, q: q || null, sort: sort || null, assets: rows });
  } catch (e) { next(e); }
});

// Valuations are auditable + must always hit live (audit middleware
// must record every request). NEVER cache.
router.get('/valuation/:slug', setCache(NO_STORE), async (req, res, next) => {
  try {
    const out = await valueAsset(req.params.slug);
    if (!out) return res.status(404).json({ error: 'asset_not_found' });
    res.json(out);
  } catch (e) { next(e); }
});

router.get('/family', setCache(PUB_300), async (req, res, next) => {
  try {
    const q = typeof req.query.q === 'string' ? req.query.q : '';
    const sort = typeof req.query.sort === 'string' ? req.query.sort : '';
    const families = await listFamilies({ q, sort });
    res.json({
      count: families.length,
      q: q || null,
      sort: sort || null,
      families
    });
  } catch (e) { next(e); }
});

router.get('/family/:slug', setCache(PUB_300), async (req, res, next) => {
  try {
    const out = await getFamily(req.params.slug);
    if (!out) return res.status(404).json({ error: 'family_not_found' });
    res.json(out);
  } catch (e) { next(e); }
});

router.get('/index', setCache(PUB_300), async (req, res, next) => {
  try {
    const q = typeof req.query.q === 'string' ? req.query.q : '';
    const sort = typeof req.query.sort === 'string' ? req.query.sort : '';
    const indices = await listIndices({ q, sort });
    res.json({
      count: indices.length,
      q: q || null,
      sort: sort || null,
      indices
    });
  } catch (e) { next(e); }
});

router.get('/index/:slug', setCache(PUB_300), async (req, res, next) => {
  try {
    const out = await getIndex(req.params.slug);
    if (!out) return res.status(404).json({ error: 'index_not_found' });
    res.json(out);
  } catch (e) { next(e); }
});

router.get('/openapi.json', setCache(PUB_3600), (req, res) => {
  res.type('application/json').send(JSON.stringify(OPENAPI_SPEC));
});

// Audit must reflect the live table.
router.get('/audit/recent', setCache(NO_STORE), async (req, res, next) => {
  try {
    const limit = req.query.limit;
    const asset_slug = req.query.asset_slug || null;
    const route = typeof req.query.route === 'string' ? req.query.route.trim() : '';
    const method = typeof req.query.method === 'string' ? req.query.method.trim() : '';
    const statusRaw = typeof req.query.status === 'string' ? req.query.status.trim() : '';
    // Echo back the normalized status: full 3-digit codes as integers,
    // 1-digit class prefixes as the string "2xx"/"4xx"/etc. Anything
    // that doesn't validate echoes back as null (server ignored it).
    let statusEcho = null;
    if (/^[1-5][0-9]{2}$/.test(statusRaw)) statusEcho = parseInt(statusRaw, 10);
    else if (/^[1-5]$/.test(statusRaw)) statusEcho = `${statusRaw}xx`;
    const sinceRaw = typeof req.query.since === 'string' ? req.query.since.trim() : '';
    // Echo back the normalized ISO string if parseable, else null.
    let sinceEcho = null;
    if (sinceRaw) {
      const d = new Date(sinceRaw);
      if (!Number.isNaN(d.getTime())) sinceEcho = d.toISOString();
    }
    const untilRaw = typeof req.query.until === 'string' ? req.query.until.trim() : '';
    let untilEcho = null;
    if (untilRaw) {
      const d = new Date(untilRaw);
      if (!Number.isNaN(d.getTime())) untilEcho = d.toISOString();
    }
    const rows = await recentCalls({
      limit,
      asset_slug,
      route: route || null,
      method: method || null,
      status: statusRaw || null,
      since: sinceRaw || null,
      until: untilRaw || null
    });
    res.json({
      count: rows.length,
      asset_slug: asset_slug || null,
      route: route || null,
      method: method ? method.toUpperCase() : null,
      status: statusEcho,
      since: sinceEcho,
      until: untilEcho,
      calls: rows
    });
  } catch (e) { next(e); }
});

// Portfolio is per-user state — never cache, never put behind a CDN.
router.use('/portfolio', setCache(NO_STORE));

router.get('/portfolio', async (req, res, next) => {
  try {
    const owner = req.query.owner;
    if (!owner) return res.status(400).json({ error: 'owner_query_required' });
    const holdings = await listHoldings(owner);
    res.json({ owner, holdings });
  } catch (e) { next(e); }
});

router.post('/portfolio', async (req, res, next) => {
  try {
    const { owner_email, asset_slug, acquired_at, acquisition_basis, notes } = req.body || {};
    if (!isValidEmail(owner_email)) {
      return res.status(400).json({ error: 'invalid_email' });
    }
    if (!asset_slug || typeof asset_slug !== 'string' || !asset_slug.trim()) {
      return res.status(400).json({ error: 'invalid_asset_slug' });
    }
    try {
      const row = await insertHolding({ owner_email, asset_slug, acquired_at, acquisition_basis, notes });
      return res.status(201).json(row);
    } catch (err) {
      if (err.code === 'invalid_asset_slug') {
        return res.status(400).json({ error: 'invalid_asset_slug' });
      }
      throw err;
    }
  } catch (e) { next(e); }
});

router.patch('/portfolio/:id', async (req, res, next) => {
  try {
    const body = req.body || {};
    const patchable = ['acquired_at', 'acquisition_basis', 'notes'];
    const fields = {};
    for (const k of patchable) {
      if (Object.prototype.hasOwnProperty.call(body, k)) fields[k] = body[k];
    }
    if (!Object.keys(fields).length) {
      return res.status(400).json({ error: 'no_fields_to_update' });
    }
    const out = await patchHolding(req.params.id, fields);
    if (out.error === 'not_found') return res.status(404).json({ error: 'not_found' });
    if (out.error) return res.status(400).json({ error: out.error });
    res.json(out.row);
  } catch (e) {
    // invalid uuid → pg throws 22P02; surface as 404 for clean DELETE-then-404 contract too
    if (e && e.code === '22P02') return res.status(404).json({ error: 'not_found' });
    next(e);
  }
});

router.delete('/portfolio/:id', async (req, res, next) => {
  try {
    const ok = await deleteHolding(req.params.id);
    if (!ok) return res.status(404).json({ error: 'not_found' });
    res.status(204).end();
  } catch (e) {
    if (e && e.code === '22P02') return res.status(404).json({ error: 'not_found' });
    next(e);
  }
});

module.exports = router;