ld-sysinfo-server-backend/scripts/verifyCVECount.js

#!/usr/bin/env node

import fs from 'fs';
import { execSync } from 'child_process';
import mysql from 'mysql2/promise';
import path from 'path';

const REPO_URL = 'https://github.com/CVEProject/cvelistV5.git';
const REPO_DIR = './cvelistV5';

function log(msg) {
  const now = new Date().toLocaleString('en-AU', {
    day: '2-digit', month: 'short', year: 'numeric',
    hour: '2-digit', minute: '2-digit', second: '2-digit', hour12: true
  }).replace(/\b(AM|PM)\b/, m => m.toLowerCase());

  const line = `[${now}] ${msg}`;
  console.log(line);
}

async function cloneOrPullRepo() {
  log('📦 Checking CVE repository...');

  if (fs.existsSync(REPO_DIR)) {
    log('🔄 Repository exists, pulling latest changes...');
    try {
      execSync('git pull', { cwd: REPO_DIR, stdio: 'inherit' });
      log('✅ Repository updated');
    } catch (err) {
      log(`⚠️ Git pull failed, trying fresh clone: ${err.message}`);
      execSync(`rm -rf ${REPO_DIR}`);
      execSync(`git clone --depth 1 ${REPO_URL} ${REPO_DIR}`, { stdio: 'inherit' });
    }
  } else {
    log('📥 Cloning CVE repository (this may take a while)...');
    execSync(`git clone --depth 1 ${REPO_URL} ${REPO_DIR}`, { stdio: 'inherit' });
    log('✅ Repository cloned');
  }
}

function countCVEsInRepo() {
  log('🔍 Counting CVEs in repository...');

  const cvesDir = path.join(REPO_DIR, 'cves');
  let totalCount = 0;
  const yearCounts = {};

  if (!fs.existsSync(cvesDir)) {
    log('❌ CVEs directory not found');
    return { total: 0, byYear: {} };
  }

  // Iterate through year directories (e.g., cves/2023/)
  const years = fs.readdirSync(cvesDir).filter(f => /^\d{4}$/.test(f));

  for (const year of years) {
    const yearPath = path.join(cvesDir, year);
    if (!fs.statSync(yearPath).isDirectory()) continue;

    let yearCount = 0;

    // Each year has subdirectories like 0xxx, 1xxx, etc.
    const subdirs = fs.readdirSync(yearPath);

    for (const subdir of subdirs) {
      const subdirPath = path.join(yearPath, subdir);
      if (!fs.statSync(subdirPath).isDirectory()) continue;

      // Count .json files in this subdirectory
      const files = fs.readdirSync(subdirPath).filter(f => f.endsWith('.json'));
      yearCount += files.length;
    }

    totalCount += yearCount;
    yearCounts[year] = yearCount;
  }

  log(`📊 Found ${totalCount} CVE JSON files in repository`);
  return { total: totalCount, byYear: yearCounts };
}

async function countCVEsInDatabase() {
  log('🗄️ Counting CVEs in database...');

  const db = await mysql.createConnection({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  });

  // Total count
  const [totalRows] = await db.query('SELECT COUNT(*) as total FROM cves');
  const total = totalRows[0].total;

  // Count by year
  const [yearRows] = await db.query(`
    SELECT
      YEAR(published_date) as year,
      COUNT(*) as count
    FROM cves
    WHERE published_date IS NOT NULL
    GROUP BY YEAR(published_date)
    ORDER BY year
  `);

  const byYear = {};
  yearRows.forEach(row => {
    byYear[row.year] = row.count;
  });

  await db.end();

  log(`📊 Found ${total} CVEs in database`);
  return { total, byYear };
}

function compareResults(repo, db) {
  log('\n📋 Comparison Report:');
  log('━'.repeat(60));
  log(`Repository Total:  ${repo.total.toLocaleString()}`);
  log(`Database Total:    ${db.total.toLocaleString()}`);
  log(`Difference:        ${(db.total - repo.total).toLocaleString()}`);
  log('━'.repeat(60));

  // Get all years from both sources
  const allYears = new Set([
    ...Object.keys(repo.byYear),
    ...Object.keys(db.byYear)
  ]);

  const sortedYears = Array.from(allYears).sort();

  log('\n📅 Year-by-Year Breakdown:');
  log('━'.repeat(60));
  log('Year   | Repository | Database  | Difference');
  log('━'.repeat(60));

  const missingYears = [];

  for (const year of sortedYears) {
    const repoCount = repo.byYear[year] || 0;
    const dbCount = db.byYear[year] || 0;
    const diff = dbCount - repoCount;

    const diffStr = diff >= 0 ? `+${diff}` : diff.toString();
    log(`${year}  | ${repoCount.toString().padStart(10)} | ${dbCount.toString().padStart(9)} | ${diffStr}`);

    if (Math.abs(diff) > 100) {
      missingYears.push({ year, repoCount, dbCount, diff });
    }
  }

  log('━'.repeat(60));

  if (missingYears.length > 0) {
    log('\n⚠️ Years with significant differences (>100):');
    missingYears.forEach(({ year, repoCount, dbCount, diff }) => {
      log(`   ${year}: ${diff > 0 ? 'Extra' : 'Missing'} ${Math.abs(diff)} CVEs`);
    });
  }

  const percentComplete = ((db.total / repo.total) * 100).toFixed(2);
  log(`\n✅ Database is ${percentComplete}% complete`);

  if (db.total >= repo.total) {
    log('🎉 Your database has all CVEs from the official repository!');
  } else {
    log(`⚠️ Missing ${(repo.total - db.total).toLocaleString()} CVEs`);
  }
}

async function main() {
  try {
    log('🚀 Starting CVE verification...\n');

    // Step 1: Clone or pull repo
    await cloneOrPullRepo();

    // Step 2: Count CVEs in repo
    const repoStats = countCVEsInRepo();

    // Step 3: Count CVEs in database
    const dbStats = await countCVEsInDatabase();

    // Step 4: Compare
    compareResults(repoStats, dbStats);

    log('\n✅ Verification complete!');
  } catch (err) {
    log(`❌ Error: ${err.message}`);
    console.error(err);
    process.exit(1);
  }
}

main();