`src/db.js`

91 lines
/**
 * DB layer for inline-editor. Uses local-socket psql by default — same pattern
 * Sister Parish viewer uses — so we avoid needing the dw_admin TCP password.
 *
 * If DATABASE_URL is set and contains a working password, switch to pg client.
 */
const { execSync, spawnSync } = require('child_process');

function psql(sql, opts = {}) {
  const res = spawnSync('psql', ['dw_unified','-At','-q'], {
    input: sql,
    encoding: 'utf8',
    timeout: opts.timeout || 5000
  });
  if (res.status !== 0) throw new Error(`psql failed: ${res.stderr}`);
  return res.stdout.trim();
}

function esc(s) {
  if (s == null) return 'NULL';
  return "'" + String(s).replace(/'/g, "''") + "'";
}

function getOverridesFor(site, path) {
  const sql = `SELECT json_object_agg(selector, json_build_object('kind', kind, 'value', value)) FROM page_overrides WHERE site=${esc(site)} AND path=${esc(path)};`;
  const raw = psql(sql);
  if (!raw || raw === '') return {};
  try { return JSON.parse(raw) || {}; } catch { return {}; }
}

function saveOverride({ site, path, selector, kind, value, editorEmail, editorIp, editorUa, isRevert = false }) {
  // 1) get old value (for log)
  const oldSql = `SELECT value FROM page_overrides WHERE site=${esc(site)} AND path=${esc(path)} AND selector=${esc(selector)};`;
  const oldValue = psql(oldSql) || null;

  // 2) upsert override
  const upsertSql = `
    INSERT INTO page_overrides (site, path, selector, kind, value, updated_at, updated_by)
    VALUES (${esc(site)}, ${esc(path)}, ${esc(selector)}, ${esc(kind)}, ${esc(value)}, NOW(), ${esc(editorEmail)})
    ON CONFLICT (site, path, selector) DO UPDATE
      SET kind=EXCLUDED.kind, value=EXCLUDED.value, updated_at=NOW(), updated_by=EXCLUDED.updated_by;
  `;
  psql(upsertSql);

  // 3) append to log
  const logSql = `
    INSERT INTO page_edit_log (site, path, selector, kind, old_value, new_value, editor_email, editor_ip, editor_ua, is_revert)
    VALUES (${esc(site)}, ${esc(path)}, ${esc(selector)}, ${esc(kind)}, ${oldValue ? esc(oldValue) : 'NULL'}, ${esc(value)}, ${esc(editorEmail)}, ${editorIp ? esc(editorIp) : 'NULL'}, ${editorUa ? esc(editorUa) : 'NULL'}, ${isRevert ? 'TRUE' : 'FALSE'});
  `;
  psql(logSql);
  return { oldValue };
}

function getLogFor(site, path, limit = 50) {
  const sql = `SELECT json_agg(t) FROM (SELECT id, selector, kind, old_value, new_value, editor_email, is_revert, ts FROM page_edit_log WHERE site=${esc(site)} AND path=${esc(path)} ORDER BY ts DESC LIMIT ${limit | 0}) t;`;
  const raw = psql(sql);
  try { return JSON.parse(raw) || []; } catch { return []; }
}

function recordUpload({ site, url, absPath, mimeType, bytes, uploadedBy, uploaderIp }) {
  const sql = `
    INSERT INTO page_uploads (site, url, abs_path, mime_type, bytes, uploaded_by, uploader_ip)
    VALUES (${esc(site)}, ${esc(url)}, ${esc(absPath)}, ${esc(mimeType)}, ${bytes | 0}, ${esc(uploadedBy)}, ${uploaderIp ? esc(uploaderIp) : 'NULL'});
  `;
  psql(sql);
}

function rateLimitCheck(editorEmail, perMinute = 10) {
  // Insert + count last 60s. If count > perMinute, block.
  const sql = `
    INSERT INTO page_edit_ratelimit (editor_email) VALUES (${esc(editorEmail)});
    DELETE FROM page_edit_ratelimit WHERE ts < NOW() - INTERVAL '5 minutes';
    SELECT COUNT(*) FROM page_edit_ratelimit WHERE editor_email=${esc(editorEmail)} AND ts > NOW() - INTERVAL '1 minute';
  `;
  const out = psql(sql);
  const n = parseInt(out, 10);
  return { count: n, blocked: n > perMinute };
}

function deleteOverride({ site, path, selector, editorEmail, editorIp, editorUa, kind }) {
  const oldSql = `SELECT value FROM page_overrides WHERE site=${esc(site)} AND path=${esc(path)} AND selector=${esc(selector)};`;
  const oldValue = psql(oldSql) || null;
  psql(`DELETE FROM page_overrides WHERE site=${esc(site)} AND path=${esc(path)} AND selector=${esc(selector)};`);
  psql(`
    INSERT INTO page_edit_log (site, path, selector, kind, old_value, new_value, editor_email, editor_ip, editor_ua, is_revert)
    VALUES (${esc(site)}, ${esc(path)}, ${esc(selector)}, ${esc(kind || 'text')}, ${oldValue ? esc(oldValue) : 'NULL'}, '', ${esc(editorEmail)}, ${editorIp ? esc(editorIp) : 'NULL'}, ${editorUa ? esc(editorUa) : 'NULL'}, TRUE);
  `);
}

module.exports = { psql, esc, getOverridesFor, saveOverride, deleteOverride, getLogFor, recordUpload, rateLimitCheck };