AgentHub/server/routes/reports.js

/**
 * @file Express router for scheduled reports (`server/lib/reports.js`): CRUD of
 * report definitions, `POST /:id/run` to run one now, the run log, and the
 * download of a run's file — whose path is built from the report and run ids
 * only, never from the request. Every write is same-origin only.
 * @author Nguyễn Ngọc Trí Vĩ <vinnt@smartgift.vn>
 */

const fs = require("node:fs");
const { Router } = require("express");
const { db } = require("../db");
const { sameOriginGuard } = require("../lib/same-origin");
const reports = require("../lib/reports");

const router = Router();

const ID_RE = /^\d+$/;
const bad = (res, message) => res.status(400).json({ error: { code: "INVALID_INPUT", message } });
const notFound = (res) =>
  res.status(404).json({ error: { code: "NOT_FOUND", message: "Report not found" } });

const getRow = (id) =>
  ID_RE.test(String(id))
    ? db.prepare("SELECT * FROM report_definitions WHERE id = ?").get(Number(id))
    : undefined;

const lastRun = db.prepare(
  "SELECT * FROM report_runs WHERE report_id = ? ORDER BY id DESC LIMIT 1"
);

// The slot a definition is due at now counts as claimed, so creating, editing
// or re-enabling a report never runs it for an hour already past.
const claimedSlot = (v) => new Date(reports.lastSlot(v, Date.now())).toISOString();

// GET /api/reports — every definition with its latest run.
router.get("/", (_req, res) => {
  const rows = db.prepare("SELECT * FROM report_definitions ORDER BY id").all();
  res.json({
    reports: rows.map((r) => {
      const run = lastRun.get(r.id);
      return { ...reports.view(r), last_run: run ? reports.viewRun(run) : null };
    }),
  });
});

// POST /api/reports — create.
router.post("/", sameOriginGuard, (req, res) => {
  const { value, error } = reports.validate(req.body || {});
  if (error) return bad(res, error);
  const info = db
    .prepare(
      `INSERT INTO report_definitions
        (name, window_days, frequency, hour, weekday, tz_offset, formats, enabled, last_slot)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
    )
    .run(
      value.name,
      value.window_days,
      value.frequency,
      value.hour,
      value.weekday,
      value.tz_offset,
      JSON.stringify(value.formats),
      value.enabled ? 1 : 0,
      claimedSlot(value)
    );
  res.status(201).json({ report: reports.view(getRow(info.lastInsertRowid)) });
});

// PUT /api/reports/:id — update; omitted fields keep their value.
router.put("/:id", sameOriginGuard, (req, res) => {
  const row = getRow(req.params.id);
  if (!row) return notFound(res);
  const { value, error } = reports.validate({ ...reports.view(row), ...(req.body || {}) });
  if (error) return bad(res, error);
  db.prepare(
    `UPDATE report_definitions SET name = ?, window_days = ?, frequency = ?, hour = ?,
       weekday = ?, tz_offset = ?, formats = ?, enabled = ?, last_slot = ? WHERE id = ?`
  ).run(
    value.name,
    value.window_days,
    value.frequency,
    value.hour,
    value.weekday,
    value.tz_offset,
    JSON.stringify(value.formats),
    value.enabled ? 1 : 0,
    claimedSlot(value),
    row.id
  );
  res.json({ report: reports.view(getRow(row.id)) });
});

// DELETE /api/reports/:id — the definition, its runs and their files.
router.delete("/:id", sameOriginGuard, (req, res) => {
  const row = getRow(req.params.id);
  if (!row) return notFound(res);
  db.prepare("DELETE FROM report_definitions WHERE id = ?").run(row.id);
  fs.rmSync(reports.reportDir(row.id), { recursive: true, force: true });
  res.json({ ok: true });
});

// POST /api/reports/:id/run — run now (also when disabled).
router.post("/:id/run", sameOriginGuard, (req, res) => {
  const row = getRow(req.params.id);
  if (!row) return notFound(res);
  res.json({ run: reports.runReport(reports.view(row), "manual") });
});

// GET /api/reports/:id/runs — the run log, newest first (last 100).
router.get("/:id/runs", (req, res) => {
  const row = getRow(req.params.id);
  if (!row) return notFound(res);
  const runs = db
    .prepare("SELECT * FROM report_runs WHERE report_id = ? ORDER BY id DESC LIMIT 100")
    .all(row.id);
  res.json({ runs: runs.map(reports.viewRun) });
});

// GET /api/reports/:id/runs/:runId/:format — download one file of a run.
router.get("/:id/runs/:runId/:format", (req, res) => {
  const { id, runId, format } = req.params;
  if (!ID_RE.test(id) || !ID_RE.test(runId)) return notFound(res);
  const file = reports.runFile(id, runId, format);
  if (!file) {
    return res.status(404).json({ error: { code: "NOT_FOUND", message: "Report file not found" } });
  }
  res.download(file, `report-${id}-${runId}.${format}`);
});

module.exports = router;