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

#!/usr/bin/env node

import axios from 'axios';
import mysql from 'mysql2/promise';

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 getGitHubCVEStats() {
  log('📡 Fetching CVE statistics from GitHub API...');

  try {
    // Get repository tree (cves directory structure)
    const response = await axios.get(
      'https://api.github.com/repos/CVEProject/cvelistV5/git/trees/main?recursive=1',
      {
        headers: {
          'Accept': 'application/vnd.github.v3+json',
          'User-Agent': 'CVE-Verification-Script'
        }
      }
    );

    const tree = response.data.tree;

    // Count .json files in cves/ directory
    const cveFiles = tree.filter(item =>
      item.path.startsWith('cves/') &&
      item.path.endsWith('.json') &&
      item.type === 'blob'
    );

    // Group by year
    const byYear = {};
    cveFiles.forEach(file => {
      const match = file.path.match(/cves\/(\d{4})\//);
      if (match) {
        const year = match[1];
        byYear[year] = (byYear[year] || 0) + 1;
      }
    });

    log(`✅ Found ${cveFiles.length} CVE files in GitHub repository`);

    return {
      total: cveFiles.length,
      byYear,
      lastCommit: response.data.sha
    };
  } catch (err) {
    log(`❌ GitHub API error: ${err.message}`);
    if (err.response?.status === 403) {
      log('⚠️ GitHub API rate limit exceeded. Try again later or clone the repository.');
    }
    throw err;
  }
}

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;
  });

  // Get date range
  const [rangeRows] = await db.query(`
    SELECT
      MIN(published_date) as earliest,
      MAX(published_date) as latest
    FROM cves
  `);

  await db.end();

  log(`✅ Found ${total} CVEs in database`);
  return {
    total,
    byYear,
    earliest: rangeRows[0].earliest,
    latest: rangeRows[0].latest
  };
}

function compareResults(github, db) {
  log('\n📋 Comparison Report:');
  log('━'.repeat(70));
  log(`GitHub Repository: ${github.total.toLocaleString()} CVEs`);
  log(`Your Database:     ${db.total.toLocaleString()} CVEs`);
  log(`Difference:        ${(db.total - github.total).toLocaleString()}`);
  log('━'.repeat(70));

  log(`\nDatabase Date Range:`);
  log(`  Earliest: ${db.earliest ? new Date(db.earliest).toLocaleDateString() : 'N/A'}`);
  log(`  Latest:   ${db.latest ? new Date(db.latest).toLocaleDateString() : 'N/A'}`);

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

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

  log('\n📅 Year-by-Year Breakdown:');
  log('━'.repeat(70));
  log('Year   | GitHub     | Database   | Difference | % Complete');
  log('━'.repeat(70));

  const significantDiffs = [];

  for (const year of sortedYears) {
    const githubCount = github.byYear[year] || 0;
    const dbCount = db.byYear[year] || 0;
    const diff = dbCount - githubCount;
    const pctComplete = githubCount > 0 ? ((dbCount / githubCount) * 100).toFixed(1) : '0.0';

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

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

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

  if (significantDiffs.length > 0) {
    log('\n⚠️ Years with significant differences (>100):');
    significantDiffs.forEach(({ year, githubCount, dbCount, diff }) => {
      if (diff < 0) {
        log(`   ${year}: Missing ${Math.abs(diff)} CVEs (${dbCount}/${githubCount})`);
      } else {
        log(`   ${year}: Extra ${diff} CVEs (database has more than GitHub)`);
      }
    });
  }

  const percentComplete = github.total > 0
    ? ((db.total / github.total) * 100).toFixed(2)
    : '0.00';

  log(`\n📊 Overall Completion: ${percentComplete}%`);

  if (db.total >= github.total) {
    log('🎉 Your database has all CVEs from the official GitHub repository!');
    if (db.total > github.total) {
      log('   (You may have older CVEs or modified entries not in the current repository)');
    }
  } else {
    const missing = github.total - db.total;
    log(`⚠️ Missing ${missing.toLocaleString()} CVEs from GitHub repository`);
    log(`   Run the backfill script to sync missing CVEs.`);
  }
}

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

    // Step 1: Get stats from GitHub API
    const githubStats = await getGitHubCVEStats();

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

    // Step 3: Compare
    compareResults(githubStats, dbStats);

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

main();