1f3833989a
Add per-metric stats and trend endpoints to vclMultiVertical.js. Refactor CCPMetricsPage to use a unified MetricSelector that drives StatsBar, TrendChart, DonutChart, and ForecastBurndownChart for the selected metric only. Remove the separate Per-Metric Forecast Burndown section (now integrated). Fix trend query double-counting when multiple uploads exist per vertical per month. Closes #25
2005 lines
83 KiB
JavaScript
2005 lines
83 KiB
JavaScript
// VCL Multi-Vertical Routes — Cross-organizational compliance reporting
|
||
// Handles multi-file per-vertical xlsx upload, scoped resolution, and executive reporting.
|
||
|
||
const express = require('express');
|
||
const path = require('path');
|
||
const fs = require('fs');
|
||
const { spawn } = require('child_process');
|
||
const pool = require('../db');
|
||
const { requireAuth, requireGroup } = require('../middleware/auth');
|
||
const { parseVerticalFilename, computeVerticalBurndown, isValidDateString, categorizeNonCompliant, deduplicateByHostname, computeAggregatedBurndown, computeMetricForecastBurndown } = require('../helpers/vclHelpers');
|
||
const logAudit = require('../helpers/auditLog');
|
||
|
||
const PARSER_SCRIPT = path.join(__dirname, '../scripts/parse_compliance_xlsx.py');
|
||
const PYTHON_BIN = process.env.PYTHON_BIN || 'python3';
|
||
const TEMP_DIR = path.join(process.cwd(), 'uploads', 'temp');
|
||
const VCL_TARGET_PCT = parseInt(process.env.VCL_TARGET_PCT, 10) || 95;
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Run Python parser, return parsed object
|
||
// ---------------------------------------------------------------------------
|
||
function parseXlsx(filePath) {
|
||
return new Promise((resolve, reject) => {
|
||
const py = spawn(PYTHON_BIN, [PARSER_SCRIPT, filePath]);
|
||
let out = '';
|
||
let err = '';
|
||
py.stdout.on('data', d => { out += d; });
|
||
py.stderr.on('data', d => { err += d; });
|
||
py.on('close', code => {
|
||
if (code !== 0) return reject(new Error(err || `Parser exited with code ${code}`));
|
||
try { resolve(JSON.parse(out)); }
|
||
catch (e) { reject(new Error('Parser returned invalid JSON')); }
|
||
});
|
||
py.on('error', reject);
|
||
});
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Compute scoped diff: only considers items within the same vertical
|
||
// ---------------------------------------------------------------------------
|
||
async function computeScopedDiff(incomingItems, vertical) {
|
||
const { rows: activeRows } = await pool.query(
|
||
`SELECT hostname, metric_id FROM compliance_items WHERE status = 'active' AND vertical = $1`,
|
||
[vertical]
|
||
);
|
||
const activeKeys = new Set(activeRows.map(r => `${r.hostname}|||${r.metric_id}`));
|
||
const newKeys = new Set(incomingItems.map(i => `${i.hostname}|||${i.metric_id}`));
|
||
|
||
let newCount = 0, recurringCount = 0, resolvedCount = 0;
|
||
for (const k of newKeys) { if (activeKeys.has(k)) recurringCount++; else newCount++; }
|
||
for (const k of activeKeys) { if (!newKeys.has(k)) resolvedCount++; }
|
||
|
||
return { newCount, recurringCount, resolvedCount };
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Persist a single vertical's upload with scoped resolution
|
||
// ---------------------------------------------------------------------------
|
||
async function persistMultiVerticalUpload({ items, summary, reportDate, filename, vertical, userId }, client) {
|
||
// Get active items for THIS vertical only
|
||
const { rows: activeRows } = await client.query(
|
||
`SELECT id, hostname, metric_id, seen_count, first_seen_upload_id FROM compliance_items
|
||
WHERE status = 'active' AND vertical = $1`,
|
||
[vertical]
|
||
);
|
||
const activeMap = {};
|
||
activeRows.forEach(r => { activeMap[`${r.hostname}|||${r.metric_id}`] = r; });
|
||
|
||
const newKeys = new Set(items.map(i => `${i.hostname}|||${i.metric_id}`));
|
||
|
||
// 1. Insert the upload record
|
||
const uploadResult = await client.query(
|
||
`INSERT INTO compliance_uploads (filename, report_date, uploaded_by, uploaded_at, vertical, summary_json)
|
||
VALUES ($1, $2, $3, NOW(), $4, $5)
|
||
RETURNING id`,
|
||
[filename, reportDate || null, userId || null, vertical, JSON.stringify(summary)]
|
||
);
|
||
const uploadId = uploadResult.rows[0].id;
|
||
|
||
let newCount = 0, recurringCount = 0, resolvedCount = 0;
|
||
|
||
// 2. Upsert each incoming non-compliant item
|
||
for (const item of items) {
|
||
const key = `${item.hostname}|||${item.metric_id}`;
|
||
const existing = activeMap[key];
|
||
const extraStr = JSON.stringify(item.extra_json || {});
|
||
|
||
if (existing) {
|
||
await client.query(
|
||
`UPDATE compliance_items
|
||
SET upload_id = $1, seen_count = $2, ip_address = $3, device_type = $4, extra_json = $5,
|
||
metric_desc = $6, category = $7, team = $8
|
||
WHERE id = $9`,
|
||
[uploadId, existing.seen_count + 1, item.ip_address, item.device_type, extraStr,
|
||
item.metric_desc, item.category, item.team, existing.id]
|
||
);
|
||
recurringCount++;
|
||
} else {
|
||
await client.query(
|
||
`INSERT INTO compliance_items
|
||
(upload_id, hostname, ip_address, device_type, team, metric_id, metric_desc,
|
||
category, extra_json, status, first_seen_upload_id, seen_count, vertical)
|
||
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'active', $10, 1, $11)`,
|
||
[uploadId, item.hostname, item.ip_address, item.device_type, item.team,
|
||
item.metric_id, item.metric_desc, item.category, extraStr, uploadId, vertical]
|
||
);
|
||
newCount++;
|
||
}
|
||
}
|
||
|
||
// 3. Resolve items NOT present in this upload — SCOPED to this vertical only
|
||
for (const [key, row] of Object.entries(activeMap)) {
|
||
if (!newKeys.has(key)) {
|
||
await client.query(
|
||
`UPDATE compliance_items SET status = 'resolved', resolved_upload_id = $1 WHERE id = $2`,
|
||
[uploadId, row.id]
|
||
);
|
||
resolvedCount++;
|
||
}
|
||
}
|
||
|
||
// 4. Update upload with final counts
|
||
await client.query(
|
||
`UPDATE compliance_uploads SET new_count = $1, resolved_count = $2, recurring_count = $3 WHERE id = $4`,
|
||
[newCount, resolvedCount, recurringCount, uploadId]
|
||
);
|
||
|
||
// 5. Store summary entries in vcl_multi_vertical_summary
|
||
if (summary && summary.entries && summary.entries.length > 0) {
|
||
for (const entry of summary.entries) {
|
||
await client.query(
|
||
`INSERT INTO vcl_multi_vertical_summary
|
||
(upload_id, vertical, metric_id, metric_desc, category, team, priority,
|
||
non_compliant, compliant, total, compliance_pct, target, status)
|
||
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)`,
|
||
[uploadId, vertical, entry.metric_id, entry.description || entry.metric_desc || '',
|
||
entry.category || 'Other', entry.team || '', entry.priority || '',
|
||
entry.non_compliant || 0, entry.compliant || 0, entry.total || 0,
|
||
entry.compliance_pct || 0, entry.target || 0, entry.status || '']
|
||
);
|
||
}
|
||
}
|
||
|
||
// 6. Create/update compliance_snapshots for this vertical
|
||
// Use summary data for accurate totals (compliance_items only has non-compliant devices).
|
||
// IMPORTANT: Only use "ALL:" rollup rows to avoid double-counting. Each Summary sheet
|
||
// has sub-team rows AND a rollup row per metric — the rollup already includes sub-teams.
|
||
// Use the file's report_date month for the snapshot so historical uploads land in the correct bucket.
|
||
const snapshotMonth = reportDate ? reportDate.slice(0, 7) : new Date().toISOString().slice(0, 7);
|
||
let totalDevices = 0, snapshotCompliant = 0, snapshotNonCompliant = 0;
|
||
|
||
if (summary && summary.entries && summary.entries.length > 0) {
|
||
for (const entry of summary.entries) {
|
||
// Only count rollup rows (team starts with "ALL:") to avoid double-counting
|
||
if (entry.team && entry.team.startsWith('ALL:')) {
|
||
totalDevices += entry.total || 0;
|
||
snapshotCompliant += entry.compliant || 0;
|
||
snapshotNonCompliant += entry.non_compliant || 0;
|
||
}
|
||
}
|
||
}
|
||
|
||
const compPct = totalDevices > 0 ? Math.round((snapshotCompliant / totalDevices) * 100 * 100) / 100 : 0;
|
||
|
||
await client.query(
|
||
`INSERT INTO compliance_snapshots (snapshot_month, vertical, total_devices, compliant, non_compliant, compliance_pct)
|
||
VALUES ($1, $2, $3, $4, $5, $6)
|
||
ON CONFLICT (snapshot_month, vertical)
|
||
DO UPDATE SET total_devices = $3, compliant = $4, non_compliant = $5, compliance_pct = $6`,
|
||
[snapshotMonth, vertical, totalDevices, snapshotCompliant, snapshotNonCompliant, compPct]
|
||
);
|
||
|
||
return { uploadId, newCount, recurringCount, resolvedCount };
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Safe temp path check
|
||
// ---------------------------------------------------------------------------
|
||
function isSafeTempPath(filePath) {
|
||
const resolved = path.resolve(filePath);
|
||
return resolved.startsWith(path.resolve(TEMP_DIR) + path.sep) && path.extname(resolved) === '.json';
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Router factory
|
||
// ---------------------------------------------------------------------------
|
||
function createVCLMultiVerticalRouter(upload) {
|
||
const router = express.Router();
|
||
|
||
// All routes require authentication
|
||
router.use(requireAuth());
|
||
|
||
/**
|
||
* POST /preview
|
||
* Accepts multiple xlsx files, parses each, extracts vertical from filename,
|
||
* computes per-vertical scoped diffs, and stores parsed data in temp files.
|
||
*
|
||
* @method POST
|
||
* @route /preview
|
||
* @group Admin, Standard_User
|
||
*
|
||
* @body multipart/form-data
|
||
* - files: File[] — 1–14 xlsx files with naming convention <VERTICAL>_YYYY_MM_DD.xlsx
|
||
*
|
||
* @response 200
|
||
* {
|
||
* files: Array<{
|
||
* filename: string,
|
||
* vertical: string,
|
||
* report_date: string,
|
||
* total_items: number,
|
||
* summary_entries: number,
|
||
* diff: { new_count: number, recurring_count: number, resolved_count: number },
|
||
* tempFile: string
|
||
* }>,
|
||
* unrecognized: Array<{ filename: string, error: string }>
|
||
* }
|
||
* @response 400 { error: string } — upload error or no files provided
|
||
*/
|
||
router.post('/preview', requireGroup('Admin', 'Standard_User'), (req, res) => {
|
||
upload.array('files', 14)(req, res, async (uploadErr) => {
|
||
if (uploadErr) return res.status(400).json({ error: uploadErr.message });
|
||
if (!req.files || req.files.length === 0) return res.status(400).json({ error: 'No files uploaded' });
|
||
|
||
const results = [];
|
||
const unrecognized = [];
|
||
const seenVerticals = new Set();
|
||
|
||
if (!fs.existsSync(TEMP_DIR)) fs.mkdirSync(TEMP_DIR, { recursive: true });
|
||
|
||
for (const file of req.files) {
|
||
const ext = path.extname(file.originalname).toLowerCase();
|
||
if (ext !== '.xlsx') {
|
||
unrecognized.push({ filename: file.originalname, error: 'Not an xlsx file' });
|
||
fs.unlink(file.path, () => {});
|
||
continue;
|
||
}
|
||
|
||
// Extract vertical from filename
|
||
const parsed = parseVerticalFilename(file.originalname);
|
||
if (!parsed) {
|
||
unrecognized.push({ filename: file.originalname, error: 'Filename does not match pattern <VERTICAL>_YYYY_MM_DD.xlsx' });
|
||
fs.unlink(file.path, () => {});
|
||
continue;
|
||
}
|
||
|
||
// Check for duplicate verticals in the same batch
|
||
if (seenVerticals.has(parsed.vertical)) {
|
||
unrecognized.push({ filename: file.originalname, error: `Duplicate vertical "${parsed.vertical}" in batch` });
|
||
fs.unlink(file.path, () => {});
|
||
continue;
|
||
}
|
||
seenVerticals.add(parsed.vertical);
|
||
|
||
try {
|
||
const xlsxData = await parseXlsx(file.path);
|
||
if (xlsxData.error) {
|
||
unrecognized.push({ filename: file.originalname, error: xlsxData.error });
|
||
fs.unlink(file.path, () => {});
|
||
continue;
|
||
}
|
||
|
||
// Compute scoped diff for this vertical
|
||
const diff = await computeScopedDiff(xlsxData.items, parsed.vertical);
|
||
|
||
// Store parsed data in temp file
|
||
const tempFilename = `vcl_multi_${parsed.vertical}_${Date.now()}_${Math.random().toString(36).slice(2)}.json`;
|
||
const tempFilePath = path.join(TEMP_DIR, tempFilename);
|
||
fs.writeFileSync(tempFilePath, JSON.stringify({
|
||
items: xlsxData.items,
|
||
summary: xlsxData.summary,
|
||
report_date: parsed.date,
|
||
vertical: parsed.vertical,
|
||
filename: file.originalname.replace(/[^\w.\-() ]/g, '_'),
|
||
}));
|
||
|
||
results.push({
|
||
filename: file.originalname,
|
||
vertical: parsed.vertical,
|
||
report_date: parsed.date,
|
||
total_items: xlsxData.total || xlsxData.items.length,
|
||
summary_entries: (xlsxData.summary && xlsxData.summary.entries) ? xlsxData.summary.entries.length : 0,
|
||
diff: { new_count: diff.newCount, recurring_count: diff.recurringCount, resolved_count: diff.resolvedCount },
|
||
tempFile: tempFilename,
|
||
});
|
||
} catch (parseErr) {
|
||
unrecognized.push({ filename: file.originalname, error: parseErr.message });
|
||
} finally {
|
||
fs.unlink(file.path, () => {});
|
||
}
|
||
}
|
||
|
||
res.json({ files: results, unrecognized });
|
||
});
|
||
});
|
||
|
||
/**
|
||
* POST /commit
|
||
* Commits all previewed files in a single transaction with vertical-scoped resolution.
|
||
*
|
||
* @method POST
|
||
* @route /commit
|
||
* @group Admin, Standard_User
|
||
*
|
||
* @body application/json
|
||
* {
|
||
* files: Array<{
|
||
* tempFile: string,
|
||
* vertical?: string,
|
||
* report_date?: string,
|
||
* filename?: string
|
||
* }>
|
||
* }
|
||
*
|
||
* @response 200
|
||
* {
|
||
* committed: Array<{
|
||
* vertical: string,
|
||
* upload_id: number,
|
||
* new_count: number,
|
||
* recurring_count: number,
|
||
* resolved_count: number
|
||
* }>,
|
||
* total_new: number,
|
||
* total_resolved: number
|
||
* }
|
||
* @response 400 { error: string } — invalid/missing tempFile or expired preview session
|
||
* @response 500 { error: string } — transaction failure
|
||
*/
|
||
router.post('/commit', requireGroup('Admin', 'Standard_User'), async (req, res) => {
|
||
const { files } = req.body;
|
||
if (!files || !Array.isArray(files) || files.length === 0) {
|
||
return res.status(400).json({ error: 'files array is required' });
|
||
}
|
||
|
||
// Validate all temp files exist before starting transaction
|
||
for (const file of files) {
|
||
const resolvedPath = path.join(TEMP_DIR, path.basename(file.tempFile || ''));
|
||
if (!file.tempFile || !isSafeTempPath(resolvedPath)) {
|
||
return res.status(400).json({ error: `Invalid tempFile path for ${file.vertical || 'unknown'}` });
|
||
}
|
||
if (!fs.existsSync(resolvedPath)) {
|
||
return res.status(400).json({ error: `Preview session expired for ${file.vertical || 'unknown'} — please upload again` });
|
||
}
|
||
// Store resolved path for use in the transaction
|
||
file._resolvedTempFile = resolvedPath;
|
||
}
|
||
|
||
const client = await pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
const committed = [];
|
||
for (const file of files) {
|
||
let parsed;
|
||
try { parsed = JSON.parse(fs.readFileSync(file._resolvedTempFile, 'utf8')); }
|
||
catch { throw new Error(`Could not read preview data for ${file.vertical}`); }
|
||
|
||
const result = await persistMultiVerticalUpload({
|
||
items: parsed.items,
|
||
summary: parsed.summary,
|
||
reportDate: file.report_date || parsed.report_date,
|
||
filename: file.filename || parsed.filename,
|
||
vertical: file.vertical || parsed.vertical,
|
||
userId: req.user?.id || null,
|
||
}, client);
|
||
|
||
committed.push({
|
||
vertical: file.vertical || parsed.vertical,
|
||
upload_id: result.uploadId,
|
||
new_count: result.newCount,
|
||
recurring_count: result.recurringCount,
|
||
resolved_count: result.resolvedCount,
|
||
});
|
||
}
|
||
|
||
await client.query('COMMIT');
|
||
|
||
// Clean up temp files
|
||
for (const file of files) {
|
||
fs.unlink(file._resolvedTempFile, () => {});
|
||
}
|
||
|
||
// Audit log
|
||
logAudit({
|
||
userId: req.user.id,
|
||
username: req.user.username,
|
||
action: 'vcl_multi_vertical_upload',
|
||
entityType: 'compliance_uploads',
|
||
entityId: null,
|
||
details: {
|
||
verticals: committed.map(c => c.vertical),
|
||
total_new: committed.reduce((s, c) => s + c.new_count, 0),
|
||
total_resolved: committed.reduce((s, c) => s + c.resolved_count, 0),
|
||
},
|
||
ipAddress: req.ip,
|
||
});
|
||
|
||
res.json({
|
||
committed,
|
||
total_new: committed.reduce((s, c) => s + c.new_count, 0),
|
||
total_resolved: committed.reduce((s, c) => s + c.resolved_count, 0),
|
||
});
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
// Clean up temp files on failure too
|
||
for (const file of files) {
|
||
if (file._resolvedTempFile) fs.unlink(file._resolvedTempFile, () => {});
|
||
}
|
||
console.error('[VCL Multi] Commit error:', err.message);
|
||
res.status(500).json({ error: 'Failed to commit batch: ' + err.message });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /stats
|
||
* Returns aggregated cross-vertical executive summary statistics.
|
||
*
|
||
* @method GET
|
||
* @route /stats
|
||
*
|
||
* @response 200
|
||
* {
|
||
* stats: {
|
||
* total_devices: number,
|
||
* compliant: number,
|
||
* non_compliant: number,
|
||
* compliance_pct: number,
|
||
* target_pct: number
|
||
* },
|
||
* donut: { blocked: number, in_progress: number },
|
||
* vertical_breakdown: Array<{
|
||
* vertical: string,
|
||
* total_devices: number,
|
||
* compliant: number,
|
||
* non_compliant: number,
|
||
* compliance_pct: number,
|
||
* blockers: number,
|
||
* forecast_burndown: Array<{ month: string, projected_remaining: number }>,
|
||
* last_upload: string|null
|
||
* }>,
|
||
* metric_breakdown: Array<{
|
||
* metric_id: string,
|
||
* category: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* target: number
|
||
* }>,
|
||
* last_upload_date: string|null
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/stats', async (req, res) => {
|
||
try {
|
||
// Use Summary sheet data (vcl_multi_vertical_summary) for accurate totals.
|
||
// The compliance_items table only contains NON-COMPLIANT devices, so counting
|
||
// hostnames there gives 0 compliant. The Summary sheet has the real numbers.
|
||
|
||
// Get the latest upload per vertical to pull summary data from
|
||
const { rows: latestUploads } = await pool.query(`
|
||
SELECT DISTINCT ON (vertical) id, vertical
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY vertical, id DESC
|
||
`);
|
||
|
||
if (latestUploads.length === 0) {
|
||
return res.json({
|
||
stats: { total_devices: 0, compliant: 0, non_compliant: 0, compliance_pct: 0, target_pct: VCL_TARGET_PCT },
|
||
donut: { blocked: { count: 0, pct: 0 }, in_progress: { count: 0, pct: 0 } },
|
||
vertical_breakdown: [],
|
||
last_upload_date: null,
|
||
});
|
||
}
|
||
|
||
const latestUploadIds = latestUploads.map(u => u.id);
|
||
|
||
// Aggregate summary data from the latest upload per vertical.
|
||
// IMPORTANT: Only use "ALL:" rollup rows to avoid double-counting.
|
||
// Each spreadsheet's Summary sheet contains both sub-team rows (ACCESS-OPS,
|
||
// STEAM, etc.) AND a rollup row (ALL: NTS-AEO) per metric. The rollup row
|
||
// already includes all sub-team totals, so summing all rows would double-count.
|
||
const { rows: verticalSummary } = await pool.query(`
|
||
SELECT vertical,
|
||
SUM(total)::int AS total_devices,
|
||
SUM(compliant)::int AS compliant,
|
||
SUM(non_compliant)::int AS non_compliant
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND team LIKE 'ALL:%'
|
||
GROUP BY vertical
|
||
ORDER BY vertical
|
||
`, [latestUploadIds]);
|
||
|
||
// Compute aggregated stats across all verticals
|
||
let aggTotal = 0, aggCompliant = 0, aggNonCompliant = 0;
|
||
for (const v of verticalSummary) {
|
||
aggTotal += v.total_devices;
|
||
aggCompliant += v.compliant;
|
||
aggNonCompliant += v.non_compliant;
|
||
}
|
||
const compliance_pct = aggTotal > 0 ? Math.round((aggCompliant / aggTotal) * 100) : 0;
|
||
|
||
// Donut: blocked vs in-progress (from compliance_items — devices with/without resolution dates)
|
||
const { rows: donutRows } = await pool.query(`
|
||
SELECT hostname, MAX(resolution_date) AS resolution_date
|
||
FROM compliance_items
|
||
WHERE vertical IS NOT NULL AND status = 'active'
|
||
GROUP BY hostname
|
||
`);
|
||
const donutItems = donutRows.map(r => ({ resolution_date: r.resolution_date }));
|
||
const donut = categorizeNonCompliant(donutItems);
|
||
|
||
// Get last upload date per vertical
|
||
const { rows: uploadDates } = await pool.query(`
|
||
SELECT vertical, MAX(report_date) AS last_upload
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
GROUP BY vertical
|
||
`);
|
||
const uploadDateMap = {};
|
||
uploadDates.forEach(r => { uploadDateMap[r.vertical] = r.last_upload; });
|
||
|
||
// Get burndown data per vertical (from compliance_items — actual device records)
|
||
const { rows: burndownRows } = await pool.query(`
|
||
SELECT vertical, hostname, resolution_date
|
||
FROM compliance_items
|
||
WHERE vertical IS NOT NULL AND status = 'active'
|
||
`);
|
||
const burndownByVertical = {};
|
||
for (const row of burndownRows) {
|
||
if (!burndownByVertical[row.vertical]) burndownByVertical[row.vertical] = [];
|
||
burndownByVertical[row.vertical].push(row);
|
||
}
|
||
|
||
// Build per-vertical breakdown using summary data for totals
|
||
const vertical_breakdown = verticalSummary.map(v => {
|
||
const pct = v.total_devices > 0 ? Math.round((v.compliant / v.total_devices) * 100) : 0;
|
||
const items = burndownByVertical[v.vertical] || [];
|
||
const burndown = computeVerticalBurndown(items);
|
||
|
||
return {
|
||
vertical: v.vertical,
|
||
total_devices: v.total_devices,
|
||
compliant: v.compliant,
|
||
non_compliant: v.non_compliant,
|
||
compliance_pct: pct,
|
||
blockers: burndown.blockers,
|
||
forecast_burndown: burndown.monthly,
|
||
last_upload: uploadDateMap[v.vertical] || null,
|
||
};
|
||
});
|
||
|
||
// Cross-vertical metric breakdown (aggregated across all verticals, ALL: rows only)
|
||
const { rows: metricBreakdown } = await pool.query(`
|
||
SELECT metric_id, category,
|
||
SUM(non_compliant)::int AS non_compliant,
|
||
SUM(compliant)::int AS compliant,
|
||
SUM(total)::int AS total,
|
||
ROUND(AVG(target::numeric), 2) AS target
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND team LIKE 'ALL:%'
|
||
GROUP BY metric_id, category
|
||
ORDER BY non_compliant DESC
|
||
`, [latestUploadIds]);
|
||
|
||
res.json({
|
||
stats: {
|
||
total_devices: aggTotal,
|
||
compliant: aggCompliant,
|
||
non_compliant: aggNonCompliant,
|
||
compliance_pct,
|
||
target_pct: VCL_TARGET_PCT,
|
||
},
|
||
donut,
|
||
vertical_breakdown,
|
||
metric_breakdown: metricBreakdown,
|
||
last_upload_date: uploadDates.length > 0 ? uploadDates.reduce((max, r) => r.last_upload > max ? r.last_upload : max, '') : null,
|
||
});
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /stats error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /trend
|
||
* Returns monthly compliance trend data aggregated across all verticals.
|
||
* Includes linear regression forecast when 3+ months of data exist.
|
||
*
|
||
* @method GET
|
||
* @route /trend
|
||
*
|
||
* @response 200
|
||
* {
|
||
* months: Array<{
|
||
* month: string,
|
||
* compliant_count: number|null,
|
||
* compliance_pct: number|null,
|
||
* forecast_pct: number|null,
|
||
* target_pct: number
|
||
* }>
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/trend', async (req, res) => {
|
||
try {
|
||
// Get snapshots for multi-vertical data (vertical IS NOT NULL)
|
||
const { rows: snapshots } = await pool.query(`
|
||
SELECT snapshot_month, SUM(total_devices)::int AS total_devices,
|
||
SUM(compliant)::int AS compliant, SUM(non_compliant)::int AS non_compliant
|
||
FROM compliance_snapshots
|
||
WHERE vertical IS NOT NULL AND vertical != ''
|
||
GROUP BY snapshot_month
|
||
ORDER BY snapshot_month ASC
|
||
`);
|
||
|
||
if (snapshots.length === 0) return res.json({ months: [] });
|
||
|
||
const months = snapshots.map(s => {
|
||
const total = s.total_devices;
|
||
const pct = total > 0 ? Math.round((s.compliant / total) * 100 * 10) / 10 : 0;
|
||
return {
|
||
month: s.snapshot_month,
|
||
compliant_count: s.compliant,
|
||
compliance_pct: pct,
|
||
forecast_pct: null,
|
||
target_pct: VCL_TARGET_PCT,
|
||
};
|
||
});
|
||
|
||
// Compute forecast using linear regression if we have 3+ months
|
||
if (months.length >= 3) {
|
||
const n = months.length;
|
||
let sumX = 0, sumY = 0, sumXY = 0, sumX2 = 0;
|
||
for (let i = 0; i < n; i++) {
|
||
sumX += i;
|
||
sumY += months[i].compliance_pct;
|
||
sumXY += i * months[i].compliance_pct;
|
||
sumX2 += i * i;
|
||
}
|
||
const slope = (n * sumXY - sumX * sumY) / (n * sumX2 - sumX * sumX);
|
||
const intercept = (sumY - slope * sumX) / n;
|
||
|
||
// Project forward 3 months
|
||
for (let i = 0; i < 3; i++) {
|
||
const futureIdx = n + i;
|
||
const forecastPct = Math.min(100, Math.max(0, Math.round((slope * futureIdx + intercept) * 10) / 10));
|
||
|
||
const lastMonth = months[months.length - 1].month;
|
||
const [year, mon] = lastMonth.split('-').map(Number);
|
||
const futureDate = new Date(year, mon - 1 + i + 1, 1);
|
||
const futureMonth = `${futureDate.getFullYear()}-${String(futureDate.getMonth() + 1).padStart(2, '0')}`;
|
||
|
||
months.push({
|
||
month: futureMonth,
|
||
compliant_count: null,
|
||
compliance_pct: null,
|
||
forecast_pct: forecastPct,
|
||
target_pct: VCL_TARGET_PCT,
|
||
});
|
||
}
|
||
|
||
// Add forecast_pct to last actual month as starting point
|
||
if (n > 0) {
|
||
months[n - 1].forecast_pct = months[n - 1].compliance_pct;
|
||
}
|
||
}
|
||
|
||
res.json({ months });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /trend error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /vertical/:code/metrics
|
||
* Returns per-metric breakdown for a specific vertical from the latest upload's summary data.
|
||
* Metrics are the "ALL:" rollup rows; each includes a sub_teams array with per-team numbers.
|
||
*
|
||
* @method GET
|
||
* @route /vertical/:code/metrics
|
||
* @param {string} code — vertical code (e.g., "NTS_AEO", "SDIT_CISO")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* vertical: string,
|
||
* metrics: Array<{
|
||
* metric_id: string,
|
||
* metric_desc: string,
|
||
* category: string,
|
||
* team: string,
|
||
* priority: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number,
|
||
* target: number,
|
||
* status: string,
|
||
* sub_teams: Array<{
|
||
* team: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number
|
||
* }>
|
||
* }>,
|
||
* categories: Array<{
|
||
* category: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number
|
||
* }>,
|
||
* teams: string[]
|
||
* }
|
||
* @response 400 { error: string } — invalid vertical code
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/vertical/:code/metrics', async (req, res) => {
|
||
const vertical = req.params.code;
|
||
if (!vertical || vertical.length > 100) return res.status(400).json({ error: 'Invalid vertical code' });
|
||
|
||
try {
|
||
// Get the latest upload for this vertical
|
||
const { rows: latestUpload } = await pool.query(
|
||
`SELECT id FROM compliance_uploads WHERE vertical = $1 ORDER BY id DESC LIMIT 1`,
|
||
[vertical]
|
||
);
|
||
|
||
if (latestUpload.length === 0) return res.json({ vertical, metrics: [], categories: [], teams: [] });
|
||
|
||
const uploadId = latestUpload[0].id;
|
||
|
||
// Get per-metric summary data (all rows including sub-teams)
|
||
const { rows: allRows } = await pool.query(
|
||
`SELECT metric_id, metric_desc, category, team, priority,
|
||
non_compliant, compliant, total, compliance_pct, target, status
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = $1 AND vertical = $2
|
||
ORDER BY category, metric_id, team`,
|
||
[uploadId, vertical]
|
||
);
|
||
|
||
// Separate into rollup rows (ALL:) and sub-team rows
|
||
// metrics = rollup rows only (one per metric — used for the primary table)
|
||
// Each metric gets a sub_teams array with the team-level breakdown
|
||
const metricMap = {};
|
||
const teamSet = new Set();
|
||
|
||
for (const row of allRows) {
|
||
const isRollup = row.team && row.team.startsWith('ALL:');
|
||
const isOther = row.team === '(Other)';
|
||
|
||
if (isRollup) {
|
||
// Primary metric row
|
||
metricMap[row.metric_id] = {
|
||
metric_id: row.metric_id,
|
||
metric_desc: row.metric_desc,
|
||
category: row.category,
|
||
priority: row.priority,
|
||
non_compliant: row.non_compliant,
|
||
compliant: row.compliant,
|
||
total: row.total,
|
||
compliance_pct: row.compliance_pct,
|
||
target: row.target,
|
||
status: row.status,
|
||
team: row.team,
|
||
sub_teams: [],
|
||
};
|
||
} else if (!isOther) {
|
||
// Sub-team row (skip "(Other)" — it's a catch-all already in the rollup)
|
||
teamSet.add(row.team);
|
||
}
|
||
}
|
||
|
||
// Second pass: attach sub-team rows to their parent metric
|
||
for (const row of allRows) {
|
||
const isRollup = row.team && row.team.startsWith('ALL:');
|
||
const isOther = row.team === '(Other)';
|
||
if (isRollup || isOther) continue;
|
||
|
||
if (metricMap[row.metric_id]) {
|
||
metricMap[row.metric_id].sub_teams.push({
|
||
team: row.team,
|
||
non_compliant: row.non_compliant,
|
||
compliant: row.compliant,
|
||
total: row.total,
|
||
compliance_pct: row.compliance_pct,
|
||
});
|
||
}
|
||
}
|
||
|
||
const metrics = Object.values(metricMap);
|
||
|
||
// Aggregate by category — only use rollup rows to avoid double-counting
|
||
const categoryMap = {};
|
||
for (const m of metrics) {
|
||
const cat = m.category || 'Other';
|
||
if (!categoryMap[cat]) categoryMap[cat] = { category: cat, non_compliant: 0, compliant: 0, total: 0 };
|
||
categoryMap[cat].non_compliant += m.non_compliant;
|
||
categoryMap[cat].compliant += m.compliant;
|
||
categoryMap[cat].total += m.total;
|
||
}
|
||
const categories = Object.values(categoryMap).map(c => ({
|
||
...c,
|
||
compliance_pct: c.total > 0 ? Math.round((c.compliant / c.total) * 100 * 10) / 10 : 0,
|
||
}));
|
||
|
||
// Return distinct team names for the vertical (useful for filtering)
|
||
const teams = [...teamSet].sort();
|
||
|
||
res.json({ vertical, metrics, categories, teams });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /vertical/:code/metrics error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /vertical/:code/metric/:metricId/devices
|
||
* Returns the list of non-compliant devices for a specific vertical + metric.
|
||
* Optionally filters by team when the query parameter is provided.
|
||
*
|
||
* @method GET
|
||
* @route /vertical/:code/metric/:metricId/devices
|
||
* @param {string} code — vertical code (e.g., "NTS_AEO")
|
||
* @param {string} metricId — metric identifier (e.g., "VM-001")
|
||
* @query {string} [team] — optional team name to filter devices (e.g., "STEAM", "ACCESS-ENG")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* vertical: string,
|
||
* metric_id: string,
|
||
* team: string|null,
|
||
* devices: Array<{
|
||
* hostname: string,
|
||
* ip_address: string,
|
||
* device_type: string,
|
||
* team: string,
|
||
* seen_count: number,
|
||
* resolution_date: string|null,
|
||
* remediation_plan: string|null,
|
||
* first_seen: string|null,
|
||
* last_seen: string|null
|
||
* }>
|
||
* }
|
||
* @response 400 { error: string } — invalid vertical code or metric ID
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/vertical/:code/metric/:metricId/devices', async (req, res) => {
|
||
const vertical = req.params.code;
|
||
const metricId = req.params.metricId;
|
||
if (!vertical || vertical.length > 100) return res.status(400).json({ error: 'Invalid vertical code' });
|
||
if (!metricId || metricId.length > 50) return res.status(400).json({ error: 'Invalid metric ID' });
|
||
|
||
try {
|
||
const team = req.query.team || null;
|
||
let query = `SELECT ci.hostname, ci.ip_address, ci.device_type, ci.team, ci.seen_count,
|
||
ci.resolution_date, ci.remediation_plan,
|
||
fu.report_date AS first_seen, lu.report_date AS last_seen
|
||
FROM compliance_items ci
|
||
LEFT JOIN compliance_uploads fu ON ci.first_seen_upload_id = fu.id
|
||
LEFT JOIN compliance_uploads lu ON ci.upload_id = lu.id
|
||
WHERE ci.vertical = $1 AND ci.metric_id = $2 AND ci.status = 'active'`;
|
||
const params = [vertical, metricId];
|
||
|
||
if (team) {
|
||
query += ` AND ci.team = $3`;
|
||
params.push(team);
|
||
}
|
||
|
||
query += ` ORDER BY ci.hostname`;
|
||
|
||
const { rows } = await pool.query(query, params);
|
||
|
||
res.json({ vertical, metric_id: metricId, team: team || null, devices: rows });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /vertical/:code/metric/:metricId/devices error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /vertical/:code/burndown
|
||
* Returns burndown forecast for a specific vertical.
|
||
* Deduplicates devices by hostname and computes monthly projected resolution.
|
||
*
|
||
* @method GET
|
||
* @route /vertical/:code/burndown
|
||
* @param {string} code — vertical code (e.g., "TSI")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* vertical: string,
|
||
* blockers: number,
|
||
* in_progress: number,
|
||
* monthly: Array<{ month: string, projected_remaining: number }>
|
||
* }
|
||
* @response 400 { error: string } — invalid vertical code
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/vertical/:code/burndown', async (req, res) => {
|
||
const vertical = req.params.code;
|
||
if (!vertical || vertical.length > 100) return res.status(400).json({ error: 'Invalid vertical code' });
|
||
|
||
try {
|
||
const { rows } = await pool.query(
|
||
`SELECT hostname, resolution_date
|
||
FROM compliance_items
|
||
WHERE vertical = $1 AND status = 'active'`,
|
||
[vertical]
|
||
);
|
||
|
||
// Deduplicate by hostname (a device may have multiple failing metrics)
|
||
const deviceMap = {};
|
||
for (const row of rows) {
|
||
if (!deviceMap[row.hostname]) {
|
||
deviceMap[row.hostname] = { hostname: row.hostname, resolution_date: row.resolution_date };
|
||
} else if (row.resolution_date && !deviceMap[row.hostname].resolution_date) {
|
||
// If any metric has a resolution date, the device counts as "in progress"
|
||
deviceMap[row.hostname].resolution_date = row.resolution_date;
|
||
}
|
||
}
|
||
const devices = Object.values(deviceMap);
|
||
const burndown = computeVerticalBurndown(devices);
|
||
|
||
res.json({ vertical, ...burndown });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /vertical/:code/burndown error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /uploads
|
||
* Returns upload history for multi-vertical uploads (most recent 100).
|
||
*
|
||
* @method GET
|
||
* @route /uploads
|
||
*
|
||
* @response 200
|
||
* {
|
||
* uploads: Array<{
|
||
* id: number,
|
||
* filename: string,
|
||
* report_date: string|null,
|
||
* uploaded_at: string,
|
||
* vertical: string,
|
||
* new_count: number,
|
||
* resolved_count: number,
|
||
* recurring_count: number
|
||
* }>
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/uploads', async (req, res) => {
|
||
try {
|
||
const { rows } = await pool.query(
|
||
`SELECT id, filename, report_date, uploaded_at, vertical, new_count, resolved_count, recurring_count
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY id DESC
|
||
LIMIT 100`
|
||
);
|
||
res.json({ uploads: rows });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /uploads error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
/**
|
||
* GET /verticals
|
||
* Returns the list of known verticals derived from compliance_items records.
|
||
*
|
||
* @method GET
|
||
* @route /verticals
|
||
*
|
||
* @response 200
|
||
* {
|
||
* verticals: string[]
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/verticals', async (req, res) => {
|
||
try {
|
||
const { rows } = await pool.query(
|
||
`SELECT DISTINCT vertical FROM compliance_items WHERE vertical IS NOT NULL ORDER BY vertical`
|
||
);
|
||
res.json({ verticals: rows.map(r => r.vertical) });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /verticals error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// Data Management — Delete / Rollback
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* DELETE /vertical/:code
|
||
* Deletes all data for a single vertical — items, uploads, summary, and snapshots.
|
||
* Admin only.
|
||
*
|
||
* @method DELETE
|
||
* @route /vertical/:code
|
||
* @group Admin
|
||
* @param {string} code — vertical code to delete (e.g., "NTS_AEO")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* message: string,
|
||
* deleted: { items: number, uploads: number }
|
||
* }
|
||
* @response 400 { error: string } — invalid vertical code
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.delete('/vertical/:code', requireGroup('Admin'), async (req, res) => {
|
||
const vertical = req.params.code;
|
||
if (!vertical || vertical.length > 100) return res.status(400).json({ error: 'Invalid vertical code' });
|
||
|
||
const client = await pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
const { rows: uploadRows } = await client.query(
|
||
`SELECT id FROM compliance_uploads WHERE vertical = $1`, [vertical]
|
||
);
|
||
const uploadIds = uploadRows.map(r => r.id);
|
||
|
||
if (uploadIds.length > 0) {
|
||
await client.query(
|
||
`DELETE FROM vcl_multi_vertical_summary WHERE upload_id = ANY($1)`, [uploadIds]
|
||
);
|
||
}
|
||
|
||
const itemResult = await client.query(
|
||
`DELETE FROM compliance_items WHERE vertical = $1`, [vertical]
|
||
);
|
||
const uploadResult = await client.query(
|
||
`DELETE FROM compliance_uploads WHERE vertical = $1`, [vertical]
|
||
);
|
||
await client.query(
|
||
`DELETE FROM compliance_snapshots WHERE vertical = $1`, [vertical]
|
||
);
|
||
|
||
await client.query('COMMIT');
|
||
|
||
logAudit({
|
||
userId: req.user.id,
|
||
username: req.user.username,
|
||
action: 'vcl_multi_vertical_delete',
|
||
entityType: 'compliance_vertical',
|
||
entityId: vertical,
|
||
details: { items_deleted: itemResult.rowCount, uploads_deleted: uploadResult.rowCount },
|
||
ipAddress: req.ip,
|
||
});
|
||
|
||
res.json({
|
||
message: `Deleted all data for vertical "${vertical}"`,
|
||
deleted: { items: itemResult.rowCount, uploads: uploadResult.rowCount },
|
||
});
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
console.error('[VCL Multi] DELETE /vertical/:code error:', err.message);
|
||
res.status(500).json({ error: 'Failed to delete vertical data' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
/**
|
||
* DELETE /all
|
||
* Deletes ALL multi-vertical data — items, uploads, summary, and snapshots.
|
||
* Admin only. Nuclear reset.
|
||
*
|
||
* @method DELETE
|
||
* @route /all
|
||
* @group Admin
|
||
*
|
||
* @response 200
|
||
* {
|
||
* message: string,
|
||
* deleted: { items: number, uploads: number }
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.delete('/all', requireGroup('Admin'), async (req, res) => {
|
||
const client = await pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
await client.query(`DELETE FROM vcl_multi_vertical_summary`);
|
||
const itemResult = await client.query(`DELETE FROM compliance_items WHERE vertical IS NOT NULL`);
|
||
const uploadResult = await client.query(`DELETE FROM compliance_uploads WHERE vertical IS NOT NULL`);
|
||
await client.query(`DELETE FROM compliance_snapshots WHERE vertical IS NOT NULL AND vertical != ''`);
|
||
|
||
await client.query('COMMIT');
|
||
|
||
logAudit({
|
||
userId: req.user.id,
|
||
username: req.user.username,
|
||
action: 'vcl_multi_vertical_reset',
|
||
entityType: 'compliance_vertical',
|
||
entityId: 'ALL',
|
||
details: { items_deleted: itemResult.rowCount, uploads_deleted: uploadResult.rowCount },
|
||
ipAddress: req.ip,
|
||
});
|
||
|
||
res.json({
|
||
message: 'All multi-vertical data has been deleted',
|
||
deleted: { items: itemResult.rowCount, uploads: uploadResult.rowCount },
|
||
});
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
console.error('[VCL Multi] DELETE /all error:', err.message);
|
||
res.status(500).json({ error: 'Failed to reset data' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
/**
|
||
* DELETE /upload/:uploadId
|
||
* Rolls back a specific upload — deletes items introduced by it, reactivates items it resolved.
|
||
* Admin only. Must be the most recent upload for that vertical.
|
||
*
|
||
* @method DELETE
|
||
* @route /upload/:uploadId
|
||
* @group Admin
|
||
* @param {number} uploadId — numeric ID of the upload to roll back
|
||
*
|
||
* @response 200
|
||
* {
|
||
* message: string,
|
||
* rolled_back: {
|
||
* upload_id: number,
|
||
* vertical: string,
|
||
* filename: string,
|
||
* items_deleted: number,
|
||
* items_reactivated: number
|
||
* }
|
||
* }
|
||
* @response 400 { error: string } — invalid upload ID, not a multi-vertical upload, or not the most recent upload
|
||
* @response 404 { error: string } — upload not found
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.delete('/upload/:uploadId', requireGroup('Admin'), async (req, res) => {
|
||
const uploadId = parseInt(req.params.uploadId, 10);
|
||
if (isNaN(uploadId)) return res.status(400).json({ error: 'Invalid upload ID' });
|
||
|
||
try {
|
||
const { rows: uploadRows } = await pool.query(
|
||
`SELECT id, filename, report_date, vertical, new_count, resolved_count FROM compliance_uploads WHERE id = $1`,
|
||
[uploadId]
|
||
);
|
||
const upload = uploadRows[0];
|
||
if (!upload) return res.status(404).json({ error: 'Upload not found' });
|
||
if (!upload.vertical) return res.status(400).json({ error: 'This upload is not a multi-vertical upload' });
|
||
|
||
const { rows: latestRows } = await pool.query(
|
||
`SELECT id FROM compliance_uploads WHERE vertical = $1 ORDER BY id DESC LIMIT 1`,
|
||
[upload.vertical]
|
||
);
|
||
if (latestRows[0].id !== uploadId) {
|
||
return res.status(400).json({
|
||
error: `Only the most recent upload for "${upload.vertical}" can be rolled back`,
|
||
latest_upload_id: latestRows[0].id,
|
||
});
|
||
}
|
||
|
||
const client = await pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
const deleteNew = await client.query(
|
||
`DELETE FROM compliance_items WHERE first_seen_upload_id = $1 AND upload_id = $1`, [uploadId]
|
||
);
|
||
const reactivate = await client.query(
|
||
`UPDATE compliance_items SET status = 'active', resolved_upload_id = NULL WHERE resolved_upload_id = $1`, [uploadId]
|
||
);
|
||
|
||
const { rows: prevRows } = await pool.query(
|
||
`SELECT id FROM compliance_uploads WHERE vertical = $1 AND id < $2 ORDER BY id DESC LIMIT 1`,
|
||
[upload.vertical, uploadId]
|
||
);
|
||
if (prevRows.length > 0) {
|
||
await client.query(
|
||
`UPDATE compliance_items SET upload_id = $1, seen_count = GREATEST(seen_count - 1, 1)
|
||
WHERE upload_id = $2 AND first_seen_upload_id != $2`,
|
||
[prevRows[0].id, uploadId]
|
||
);
|
||
}
|
||
|
||
await client.query(`DELETE FROM vcl_multi_vertical_summary WHERE upload_id = $1`, [uploadId]);
|
||
await client.query(`DELETE FROM compliance_uploads WHERE id = $1`, [uploadId]);
|
||
|
||
const rollbackMonth = upload.report_date ? upload.report_date.slice(0, 7) : new Date().toISOString().slice(0, 7);
|
||
await client.query(
|
||
`DELETE FROM compliance_snapshots WHERE vertical = $1 AND snapshot_month = $2`,
|
||
[upload.vertical, rollbackMonth]
|
||
);
|
||
|
||
await client.query('COMMIT');
|
||
|
||
logAudit({
|
||
userId: req.user.id,
|
||
username: req.user.username,
|
||
action: 'vcl_multi_upload_rollback',
|
||
entityType: 'compliance_upload',
|
||
entityId: String(uploadId),
|
||
details: {
|
||
vertical: upload.vertical,
|
||
filename: upload.filename,
|
||
items_deleted: deleteNew.rowCount,
|
||
items_reactivated: reactivate.rowCount,
|
||
},
|
||
ipAddress: req.ip,
|
||
});
|
||
|
||
res.json({
|
||
message: `Rolled back upload "${upload.filename}" for ${upload.vertical}`,
|
||
rolled_back: {
|
||
upload_id: uploadId,
|
||
vertical: upload.vertical,
|
||
filename: upload.filename,
|
||
items_deleted: deleteNew.rowCount,
|
||
items_reactivated: reactivate.rowCount,
|
||
},
|
||
});
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
throw err;
|
||
} finally {
|
||
client.release();
|
||
}
|
||
} catch (err) {
|
||
console.error('[VCL Multi] DELETE /upload/:uploadId error:', err.message);
|
||
res.status(500).json({ error: 'Failed to rollback upload: ' + err.message });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metric/:id/stats — Per-metric summary statistics + donut breakdown
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metric/:id/stats
|
||
* Returns summary statistics and donut breakdown for a single metric,
|
||
* aggregated across all verticals using only ALL: rollup rows.
|
||
*
|
||
* @method GET
|
||
* @route /metric/:id/stats
|
||
* @param {string} id — metric identifier (e.g., "2.3.6i")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* metric_id: string,
|
||
* metric_desc: string,
|
||
* category: string,
|
||
* total_devices: number,
|
||
* compliant: number,
|
||
* non_compliant: number,
|
||
* compliance_pct: number,
|
||
* target: number,
|
||
* donut: {
|
||
* blocked: { count: number, pct: number },
|
||
* in_progress: { count: number, pct: number }
|
||
* }
|
||
* }
|
||
* @response 400 { error: string } — metric ID exceeds 50 characters
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/metric/:id/stats', async (req, res) => {
|
||
const metricId = req.params.id;
|
||
if (!metricId || metricId.length > 50) {
|
||
return res.status(400).json({ error: 'Invalid metric ID' });
|
||
}
|
||
|
||
try {
|
||
// Get latest upload ID per vertical (same pattern as existing /stats endpoint)
|
||
const { rows: latestUploads } = await pool.query(`
|
||
SELECT DISTINCT ON (vertical) id, vertical
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY vertical, id DESC
|
||
`);
|
||
|
||
if (latestUploads.length === 0) {
|
||
return res.json({
|
||
metric_id: metricId,
|
||
metric_desc: '',
|
||
category: '',
|
||
total_devices: 0,
|
||
compliant: 0,
|
||
non_compliant: 0,
|
||
compliance_pct: 0,
|
||
target: 0,
|
||
donut: {
|
||
blocked: { count: 0, pct: 0 },
|
||
in_progress: { count: 0, pct: 0 },
|
||
},
|
||
});
|
||
}
|
||
|
||
const latestUploadIds = latestUploads.map(u => u.id);
|
||
|
||
// Query vcl_multi_vertical_summary for this metric, ALL: rollup rows only
|
||
const { rows: summaryRows } = await pool.query(`
|
||
SELECT metric_desc, category, total, compliant, non_compliant, target
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND metric_id = $2 AND team LIKE 'ALL:%'
|
||
`, [latestUploadIds, metricId]);
|
||
|
||
// If metric not found, return zero-filled response (HTTP 200, not 404)
|
||
if (summaryRows.length === 0) {
|
||
return res.json({
|
||
metric_id: metricId,
|
||
metric_desc: '',
|
||
category: '',
|
||
total_devices: 0,
|
||
compliant: 0,
|
||
non_compliant: 0,
|
||
compliance_pct: 0,
|
||
target: 0,
|
||
donut: {
|
||
blocked: { count: 0, pct: 0 },
|
||
in_progress: { count: 0, pct: 0 },
|
||
},
|
||
});
|
||
}
|
||
|
||
// Aggregate across verticals
|
||
let totalDevices = 0, totalCompliant = 0, totalNonCompliant = 0;
|
||
let targetSum = 0, targetCount = 0;
|
||
let metricDesc = '';
|
||
let category = '';
|
||
|
||
for (const row of summaryRows) {
|
||
totalDevices += row.total || 0;
|
||
totalCompliant += row.compliant || 0;
|
||
totalNonCompliant += row.non_compliant || 0;
|
||
targetSum += parseFloat(row.target) || 0;
|
||
targetCount++;
|
||
// Derive metric_desc and category from first non-empty value
|
||
if (!metricDesc && row.metric_desc) metricDesc = row.metric_desc;
|
||
if (!category && row.category) category = row.category;
|
||
}
|
||
|
||
const target = targetCount > 0 ? Math.round((targetSum / targetCount) * 100) / 100 : 0;
|
||
const compliancePct = totalDevices > 0 ? Math.round((totalCompliant / totalDevices) * 100) : 0;
|
||
|
||
// Donut breakdown: query compliance_items for this metric
|
||
const { rows: donutRows } = await pool.query(`
|
||
SELECT hostname, MAX(resolution_date) AS resolution_date
|
||
FROM compliance_items
|
||
WHERE metric_id = $1 AND status = 'active' AND vertical IS NOT NULL
|
||
GROUP BY hostname
|
||
`, [metricId]);
|
||
|
||
const donutItems = donutRows.map(r => ({ resolution_date: r.resolution_date }));
|
||
const donut = categorizeNonCompliant(donutItems);
|
||
|
||
res.json({
|
||
metric_id: metricId,
|
||
metric_desc: metricDesc,
|
||
category: category,
|
||
total_devices: totalDevices,
|
||
compliant: totalCompliant,
|
||
non_compliant: totalNonCompliant,
|
||
compliance_pct: compliancePct,
|
||
target: target,
|
||
donut,
|
||
});
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metric/:id/stats error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metric/:id/verticals — Per-vertical breakdown for a specific metric
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metric/:id/verticals
|
||
* Returns per-vertical breakdown for a specific metric, including sub-team data
|
||
* within each vertical. Uses only the latest upload per vertical.
|
||
*
|
||
* @method GET
|
||
* @route /metric/:id/verticals
|
||
* @param {string} id — metric identifier (e.g., "VM-001")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* metric_id: string,
|
||
* metric_desc: string|null,
|
||
* category: string|null,
|
||
* verticals: Array<{
|
||
* vertical: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number,
|
||
* target: number,
|
||
* sub_teams: Array<{
|
||
* team: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number
|
||
* }>
|
||
* }>
|
||
* }
|
||
* @response 400 { error: string } — metric ID exceeds 50 characters
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/metric/:id/verticals', async (req, res) => {
|
||
const metricId = req.params.id;
|
||
if (!metricId || metricId.length > 50) {
|
||
return res.status(400).json({ error: 'Invalid metric ID' });
|
||
}
|
||
|
||
try {
|
||
// Get latest upload per vertical
|
||
const { rows: latestUploads } = await pool.query(`
|
||
SELECT DISTINCT ON (vertical) id, vertical
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY vertical, id DESC
|
||
`);
|
||
|
||
if (latestUploads.length === 0) {
|
||
return res.json({ metric_id: metricId, metric_desc: null, category: null, verticals: [] });
|
||
}
|
||
|
||
const latestUploadIds = latestUploads.map(u => u.id);
|
||
|
||
// Get all rows for this metric from latest uploads
|
||
const { rows: allRows } = await pool.query(`
|
||
SELECT vertical, metric_desc, category, team,
|
||
non_compliant, compliant, total, compliance_pct, target
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND metric_id = $2
|
||
ORDER BY vertical, team
|
||
`, [latestUploadIds, metricId]);
|
||
|
||
// If no rows found, return empty verticals with metric_id echoed back
|
||
if (allRows.length === 0) {
|
||
return res.json({ metric_id: metricId, metric_desc: null, category: null, verticals: [] });
|
||
}
|
||
|
||
// Extract metric_desc and category from the first row
|
||
const metric_desc = allRows[0].metric_desc || null;
|
||
const category = allRows[0].category || null;
|
||
|
||
// Separate rollup rows (ALL:) from sub-team rows and build per-vertical entries
|
||
const verticalMap = {};
|
||
|
||
for (const row of allRows) {
|
||
const isRollup = row.team && row.team.startsWith('ALL:');
|
||
|
||
if (isRollup) {
|
||
// Primary vertical entry from rollup row
|
||
verticalMap[row.vertical] = {
|
||
vertical: row.vertical,
|
||
non_compliant: row.non_compliant,
|
||
compliant: row.compliant,
|
||
total: row.total,
|
||
compliance_pct: row.compliance_pct,
|
||
target: row.target,
|
||
sub_teams: [],
|
||
};
|
||
}
|
||
}
|
||
|
||
// Second pass: attach sub-team rows to their parent vertical
|
||
for (const row of allRows) {
|
||
const isRollup = row.team && row.team.startsWith('ALL:');
|
||
const isOther = row.team === '(Other)';
|
||
if (isRollup || isOther) continue;
|
||
|
||
if (verticalMap[row.vertical]) {
|
||
verticalMap[row.vertical].sub_teams.push({
|
||
team: row.team,
|
||
non_compliant: row.non_compliant,
|
||
compliant: row.compliant,
|
||
total: row.total,
|
||
compliance_pct: row.compliance_pct,
|
||
});
|
||
}
|
||
}
|
||
|
||
// Sort verticals by non_compliant DESC
|
||
const verticals = Object.values(verticalMap).sort((a, b) => b.non_compliant - a.non_compliant);
|
||
|
||
res.json({ metric_id: metricId, metric_desc, category, verticals });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metric/:id/verticals error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /burndown — Aggregated cross-vertical burndown forecast
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /burndown
|
||
* Returns aggregated burndown forecast across all verticals.
|
||
* Deduplicates devices by hostname (earliest non-null resolution_date).
|
||
*
|
||
* @method GET
|
||
* @route /burndown
|
||
*
|
||
* @response 200
|
||
* {
|
||
* total_non_compliant: number,
|
||
* blockers: number,
|
||
* with_dates: number,
|
||
* monthly_forecast: Object<string, number>,
|
||
* projected_clear_date: string|null,
|
||
* by_vertical: Array<{ vertical: string, total: number, blockers: number, with_dates: number }>
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/burndown', async (req, res) => {
|
||
try {
|
||
const { rows } = await pool.query(
|
||
`SELECT hostname, resolution_date, vertical
|
||
FROM compliance_items
|
||
WHERE vertical IS NOT NULL AND status = 'active'`
|
||
);
|
||
|
||
const devices = deduplicateByHostname(rows);
|
||
const burndown = computeAggregatedBurndown(devices);
|
||
|
||
res.json({
|
||
total_non_compliant: burndown.total,
|
||
blockers: burndown.blockers,
|
||
with_dates: burndown.with_dates,
|
||
monthly_forecast: burndown.monthly,
|
||
projected_clear_date: burndown.projected_clear_date,
|
||
by_vertical: burndown.by_vertical,
|
||
});
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /burndown error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metrics — Metrics aggregated across all verticals
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metrics
|
||
* Returns all metrics aggregated across verticals using only ALL: rollup rows
|
||
* from the latest upload per vertical.
|
||
*
|
||
* @method GET
|
||
* @route /metrics
|
||
*
|
||
* @response 200
|
||
* {
|
||
* metrics: Array<{
|
||
* metric_id: string,
|
||
* metric_desc: string,
|
||
* category: string,
|
||
* non_compliant: number,
|
||
* compliant: number,
|
||
* total: number,
|
||
* compliance_pct: number,
|
||
* target: number
|
||
* }>
|
||
* }
|
||
* @response 500 { error: "Database error" }
|
||
*/
|
||
router.get('/metrics', async (req, res) => {
|
||
try {
|
||
// Get latest upload ID per vertical
|
||
const { rows: latestUploads } = await pool.query(`
|
||
SELECT DISTINCT ON (vertical) id, vertical
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY vertical, id DESC
|
||
`);
|
||
|
||
if (latestUploads.length === 0) {
|
||
return res.json({ metrics: [] });
|
||
}
|
||
|
||
const latestUploadIds = latestUploads.map(u => u.id);
|
||
|
||
// Aggregate metrics across verticals (ALL: rows only)
|
||
const { rows: metrics } = await pool.query(`
|
||
SELECT metric_id,
|
||
MAX(metric_desc) AS metric_desc,
|
||
MAX(category) AS category,
|
||
SUM(non_compliant)::int AS non_compliant,
|
||
SUM(compliant)::int AS compliant,
|
||
SUM(total)::int AS total,
|
||
ROUND(AVG(target::numeric), 4) AS target
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND team LIKE 'ALL:%'
|
||
GROUP BY metric_id
|
||
ORDER BY non_compliant DESC
|
||
`, [latestUploadIds]);
|
||
|
||
// Compute compliance_pct for each metric
|
||
const result = metrics.map(m => ({
|
||
...m,
|
||
target: parseFloat(m.target) || 0,
|
||
compliance_pct: m.total > 0 ? m.compliant / m.total : 0,
|
||
}));
|
||
|
||
res.json({ metrics: result });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metrics error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metrics-list — Distinct metrics with active non-compliant device counts
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metrics-list
|
||
* Returns the list of distinct metrics that have at least one active non-compliant
|
||
* device with a non-null vertical. Used by the MetricSelector component.
|
||
*
|
||
* @method GET
|
||
* @route /metrics-list
|
||
*
|
||
* @response 200
|
||
* Array<{ metric_id: string, device_count: number }>
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/metrics-list', async (req, res) => {
|
||
try {
|
||
const { rows } = await pool.query(`
|
||
SELECT metric_id, COUNT(DISTINCT hostname) AS device_count
|
||
FROM compliance_items
|
||
WHERE status = 'active' AND vertical IS NOT NULL
|
||
GROUP BY metric_id
|
||
ORDER BY metric_id ASC
|
||
`);
|
||
|
||
res.json(rows.map(r => ({ metric_id: r.metric_id, device_count: parseInt(r.device_count, 10) })));
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metrics-list error:', err.message);
|
||
res.status(500).json({ error: 'Failed to fetch metrics list' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metric/:id/trend — Per-metric monthly compliance trend with forecast
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metric/:id/trend
|
||
* Returns monthly compliance history with linear regression forecast for a
|
||
* single metric. Groups by report month from compliance_uploads, aggregating
|
||
* only rollup rows (team LIKE 'ALL:%').
|
||
*
|
||
* @method GET
|
||
* @route /metric/:id/trend
|
||
* @param {string} id — metric identifier (e.g., "2.3.6i")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* months: Array<{
|
||
* month: string,
|
||
* compliant_count: number|null,
|
||
* non_compliant_count: number|null,
|
||
* total_devices: number|null,
|
||
* compliance_pct: number|null,
|
||
* forecast_pct: number|null,
|
||
* target: number
|
||
* }>
|
||
* }
|
||
* @response 400 { error: string } — metric ID exceeds 50 characters
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/metric/:id/trend', async (req, res) => {
|
||
const metricId = req.params.id;
|
||
if (!metricId || metricId.length > 50) {
|
||
return res.status(400).json({ error: 'Invalid metric ID' });
|
||
}
|
||
|
||
try {
|
||
// Get the metric's target value from the latest uploads (same pattern as stats endpoint)
|
||
const { rows: latestUploads } = await pool.query(`
|
||
SELECT DISTINCT ON (vertical) id, vertical
|
||
FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY vertical, id DESC
|
||
`);
|
||
|
||
if (latestUploads.length === 0) {
|
||
return res.json({ months: [] });
|
||
}
|
||
|
||
const latestUploadIds = latestUploads.map(u => u.id);
|
||
|
||
// Get target from the latest uploads for this metric
|
||
const { rows: targetRows } = await pool.query(`
|
||
SELECT ROUND(AVG(target::numeric), 2) AS target
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE upload_id = ANY($1) AND metric_id = $2 AND team LIKE 'ALL:%'
|
||
`, [latestUploadIds, metricId]);
|
||
|
||
const metricTarget = targetRows.length > 0 && targetRows[0].target !== null
|
||
? parseFloat(targetRows[0].target)
|
||
: 0;
|
||
|
||
// Join vcl_multi_vertical_summary with compliance_uploads to group by report month.
|
||
// Use only the latest upload per vertical per month to avoid double-counting
|
||
// when a vertical is re-uploaded multiple times in the same month.
|
||
const { rows: monthlyData } = await pool.query(`
|
||
WITH latest_per_vertical_month AS (
|
||
SELECT DISTINCT ON (cu.vertical, COALESCE(SUBSTRING(cu.report_date FROM 1 FOR 7), TO_CHAR(cu.uploaded_at, 'YYYY-MM')))
|
||
cu.id AS upload_id,
|
||
cu.vertical,
|
||
COALESCE(SUBSTRING(cu.report_date FROM 1 FOR 7), TO_CHAR(cu.uploaded_at, 'YYYY-MM')) AS report_month
|
||
FROM compliance_uploads cu
|
||
WHERE cu.vertical IS NOT NULL
|
||
ORDER BY cu.vertical, COALESCE(SUBSTRING(cu.report_date FROM 1 FOR 7), TO_CHAR(cu.uploaded_at, 'YYYY-MM')), cu.id DESC
|
||
)
|
||
SELECT lvm.report_month,
|
||
SUM(s.compliant)::int AS compliant,
|
||
SUM(s.non_compliant)::int AS non_compliant,
|
||
SUM(s.total)::int AS total
|
||
FROM vcl_multi_vertical_summary s
|
||
JOIN latest_per_vertical_month lvm ON s.upload_id = lvm.upload_id
|
||
WHERE s.metric_id = $1 AND s.team LIKE 'ALL:%'
|
||
GROUP BY lvm.report_month
|
||
ORDER BY lvm.report_month ASC
|
||
`, [metricId]);
|
||
|
||
// If metric not found in any historical data, return empty months
|
||
if (monthlyData.length === 0) {
|
||
return res.json({ months: [] });
|
||
}
|
||
|
||
// Build historical months with compliance_pct
|
||
const months = monthlyData.map(row => {
|
||
const pct = row.total > 0
|
||
? Math.round((row.compliant / row.total) * 100 * 10) / 10
|
||
: 0;
|
||
return {
|
||
month: row.report_month,
|
||
compliant_count: row.compliant,
|
||
non_compliant_count: row.non_compliant,
|
||
total_devices: row.total,
|
||
compliance_pct: pct,
|
||
forecast_pct: null,
|
||
target: metricTarget,
|
||
};
|
||
});
|
||
|
||
// Compute forecast using linear regression if we have 3+ months
|
||
if (months.length >= 3) {
|
||
const n = months.length;
|
||
let sumX = 0, sumY = 0, sumXY = 0, sumX2 = 0;
|
||
for (let i = 0; i < n; i++) {
|
||
sumX += i;
|
||
sumY += months[i].compliance_pct;
|
||
sumXY += i * months[i].compliance_pct;
|
||
sumX2 += i * i;
|
||
}
|
||
const slope = (n * sumXY - sumX * sumY) / (n * sumX2 - sumX * sumX);
|
||
const intercept = (sumY - slope * sumX) / n;
|
||
|
||
// Set forecast_pct on the last historical month as the transition point
|
||
months[n - 1].forecast_pct = months[n - 1].compliance_pct;
|
||
|
||
// Project forward 3 months
|
||
for (let i = 0; i < 3; i++) {
|
||
const futureIdx = n + i;
|
||
const forecastPct = Math.min(100.0, Math.max(0.0,
|
||
Math.round((slope * futureIdx + intercept) * 10) / 10
|
||
));
|
||
|
||
const lastMonth = months[months.length - 1].month;
|
||
const [year, mon] = lastMonth.split('-').map(Number);
|
||
const futureDate = new Date(year, mon - 1 + 1, 1);
|
||
const futureMonth = `${futureDate.getFullYear()}-${String(futureDate.getMonth() + 1).padStart(2, '0')}`;
|
||
|
||
months.push({
|
||
month: futureMonth,
|
||
compliant_count: null,
|
||
non_compliant_count: null,
|
||
total_devices: null,
|
||
compliance_pct: null,
|
||
forecast_pct: forecastPct,
|
||
target: metricTarget,
|
||
});
|
||
}
|
||
}
|
||
|
||
res.json({ months });
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metric/:id/trend error:', err.message);
|
||
res.status(500).json({ error: 'Database error' });
|
||
}
|
||
});
|
||
|
||
// -----------------------------------------------------------------------
|
||
// GET /metric/:metricId/forecast-burndown — Per-metric forecast burndown
|
||
// -----------------------------------------------------------------------
|
||
|
||
/**
|
||
* GET /metric/:metricId/forecast-burndown
|
||
* Returns combined historical + forecast burndown data for a specific metric.
|
||
* Historical data is derived from compliance_snapshots using the ratio method.
|
||
* Forecast data is computed by the computeMetricForecastBurndown helper.
|
||
*
|
||
* @method GET
|
||
* @route /metric/:metricId/forecast-burndown
|
||
* @param {string} metricId — metric identifier (e.g., "2.3.5")
|
||
*
|
||
* @response 200
|
||
* {
|
||
* metric_id: string,
|
||
* historical: Array<{ month: string, total_assets: number, non_compliant: number, compliance_pct: number }>,
|
||
* forecast: Array<{ month: string, total_assets: number, non_compliant: number, compliance_pct: number }>,
|
||
* current_snapshot: { total_assets: number, non_compliant: number, compliant: number, compliance_pct: number, blockers: number, with_dates: number }
|
||
* }
|
||
* @response 500 { error: string }
|
||
*/
|
||
router.get('/metric/:metricId/forecast-burndown', async (req, res) => {
|
||
const metricId = req.params.metricId;
|
||
|
||
try {
|
||
// 1. Query active devices for this metric
|
||
const { rows: activeDevices } = await pool.query(
|
||
`SELECT hostname, resolution_date, vertical
|
||
FROM compliance_items
|
||
WHERE metric_id = $1 AND status = 'active' AND vertical IS NOT NULL`,
|
||
[metricId]
|
||
);
|
||
|
||
// If no active devices, return empty response
|
||
if (activeDevices.length === 0) {
|
||
return res.json({
|
||
metric_id: metricId,
|
||
historical: [],
|
||
forecast: [],
|
||
current_snapshot: {
|
||
total_assets: 0,
|
||
non_compliant: 0,
|
||
compliant: 0,
|
||
compliance_pct: 0,
|
||
blockers: 0,
|
||
with_dates: 0,
|
||
},
|
||
});
|
||
}
|
||
|
||
// 2. Determine the vertical(s) from active devices
|
||
// Group by vertical to handle metrics that span multiple verticals
|
||
const verticalSet = new Set(activeDevices.map(d => d.vertical));
|
||
const vertical = activeDevices[0].vertical; // primary vertical for snapshot lookup
|
||
|
||
// 3. Compute date range for 3 months of historical snapshots
|
||
const now = new Date();
|
||
const currentMonth = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`;
|
||
// 3 months prior to current month
|
||
const threeMonthsAgo = new Date(now.getFullYear(), now.getMonth() - 3, 1);
|
||
const startMonth = `${threeMonthsAgo.getFullYear()}-${String(threeMonthsAgo.getMonth() + 1).padStart(2, '0')}`;
|
||
|
||
// 4. Query historical snapshots for ALL verticals this metric spans
|
||
const verticals = [...verticalSet];
|
||
const { rows: snapshots } = await pool.query(
|
||
`SELECT snapshot_month AS month,
|
||
SUM(total_devices)::int AS total_assets,
|
||
SUM(non_compliant)::int AS non_compliant,
|
||
ROUND((SUM(compliant)::numeric / NULLIF(SUM(total_devices), 0)) * 100, 1) AS compliance_pct
|
||
FROM compliance_snapshots
|
||
WHERE vertical = ANY($1) AND snapshot_month >= $2 AND snapshot_month < $3
|
||
GROUP BY snapshot_month
|
||
ORDER BY snapshot_month ASC`,
|
||
[verticals, startMonth, currentMonth]
|
||
);
|
||
|
||
// 5. Get total non-compliant devices across all verticals this metric spans
|
||
const { rows: verticalNcRows } = await pool.query(
|
||
`SELECT COUNT(DISTINCT hostname) AS total_nc
|
||
FROM compliance_items
|
||
WHERE vertical = ANY($1) AND status = 'active'`,
|
||
[verticals]
|
||
);
|
||
const verticalTotalNc = parseInt(verticalNcRows[0].total_nc, 10) || 0;
|
||
|
||
// Count metric's non-compliant devices (distinct hostnames)
|
||
const metricNcCount = new Set(activeDevices.map(d => d.hostname)).size;
|
||
|
||
// 6. Compute per-metric historical non_compliant using the ratio method (Requirement 7.2)
|
||
// Use the metric's own total (from summary) rather than the vertical's total_devices
|
||
const historicalSnapshots = snapshots.map(snap => {
|
||
const snapshotNc = parseInt(snap.non_compliant, 10) || 0;
|
||
let metricNc;
|
||
if (verticalTotalNc === 0) {
|
||
metricNc = 0;
|
||
} else {
|
||
// Ratio method: vertical_snapshot_nc * (metric_nc / vertical_total_nc)
|
||
metricNc = Math.round(snapshotNc * (metricNcCount / verticalTotalNc));
|
||
}
|
||
|
||
return {
|
||
month: snap.month,
|
||
total_assets: 0, // Will be filled in after we get the metric's totalAssets
|
||
non_compliant: metricNc,
|
||
compliance_pct: 0, // Will be recomputed
|
||
};
|
||
});
|
||
|
||
// 7. Include current month as the most recent historical data point (from live data)
|
||
// Get totalAssets from the per-metric summary (vcl_multi_vertical_summary)
|
||
// This gives us the actual total devices for THIS metric, not the entire vertical
|
||
let totalAssets = 0;
|
||
const { rows: metricSummaryRows } = await pool.query(
|
||
`SELECT SUM(total)::int AS total
|
||
FROM vcl_multi_vertical_summary
|
||
WHERE metric_id = $1 AND team LIKE 'ALL:%'
|
||
AND upload_id IN (
|
||
SELECT id FROM compliance_uploads
|
||
WHERE vertical IS NOT NULL
|
||
ORDER BY id DESC
|
||
LIMIT 20
|
||
)`,
|
||
[metricId]
|
||
);
|
||
|
||
if (metricSummaryRows.length > 0 && metricSummaryRows[0].total) {
|
||
totalAssets = parseInt(metricSummaryRows[0].total, 10) || 0;
|
||
}
|
||
|
||
// Fallback: if no summary data, use non_compliant count as minimum
|
||
if (totalAssets === 0) {
|
||
totalAssets = metricNcCount;
|
||
}
|
||
|
||
// Backfill historical snapshots with the correct per-metric totalAssets and compliance_pct
|
||
for (const snap of historicalSnapshots) {
|
||
snap.total_assets = totalAssets;
|
||
snap.compliance_pct = totalAssets > 0
|
||
? Math.round(((totalAssets - snap.non_compliant) / totalAssets) * 1000) / 10
|
||
: 0;
|
||
}
|
||
|
||
// Current month data point from live data
|
||
const currentMonthNc = metricNcCount;
|
||
const currentMonthCompliancePct = totalAssets > 0
|
||
? Math.round(((totalAssets - currentMonthNc) / totalAssets) * 1000) / 10
|
||
: 0;
|
||
|
||
historicalSnapshots.push({
|
||
month: currentMonth,
|
||
total_assets: totalAssets,
|
||
non_compliant: currentMonthNc,
|
||
compliance_pct: currentMonthCompliancePct,
|
||
});
|
||
|
||
// 8. Prepare currentDevices for the helper — deduplicate by hostname
|
||
// A device may appear in multiple verticals; count it once, keeping the
|
||
// earliest resolution_date (or null if any row has no date)
|
||
const deviceMap = {};
|
||
for (const d of activeDevices) {
|
||
if (!deviceMap[d.hostname]) {
|
||
deviceMap[d.hostname] = { hostname: d.hostname, resolution_date: d.resolution_date || null };
|
||
} else if (d.resolution_date && !deviceMap[d.hostname].resolution_date) {
|
||
// If any row has a resolution_date and current doesn't, use it
|
||
deviceMap[d.hostname].resolution_date = d.resolution_date;
|
||
}
|
||
}
|
||
const currentDevices = Object.values(deviceMap);
|
||
|
||
// 9. Pass data to computeMetricForecastBurndown helper
|
||
const result = computeMetricForecastBurndown(currentDevices, totalAssets, historicalSnapshots);
|
||
|
||
// 10. Return response
|
||
res.json({
|
||
metric_id: metricId,
|
||
historical: result.historical,
|
||
forecast: result.forecast,
|
||
current_snapshot: result.current_snapshot,
|
||
});
|
||
} catch (err) {
|
||
console.error('[VCL Multi] GET /metric/:metricId/forecast-burndown error:', err.message);
|
||
res.status(500).json({ error: 'Failed to compute forecast burndown' });
|
||
}
|
||
});
|
||
|
||
return router;
|
||
}
|
||
|
||
module.exports = { createVCLMultiVerticalRouter };
|