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

#!/usr/bin/env node

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());
  console.log(`[${now}] ${msg}`);
}

async function analyzeDatabaseQuality() {
  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,
  });

  log('🔍 Analyzing CVE Database Quality...\n');
  log('━'.repeat(70));

  // Total CVEs
  const [total] = await db.query('SELECT COUNT(*) as count FROM cves');
  log(`📊 Total CVEs: ${total[0].count.toLocaleString()}`);

  // CVEs with CVSS v3 scores
  const [v3] = await db.query('SELECT COUNT(*) as count FROM cves WHERE cvss_score_v3 IS NOT NULL');
  const v3Pct = ((v3[0].count / total[0].count) * 100).toFixed(1);
  log(`📈 CVEs with CVSS v3 scores: ${v3[0].count.toLocaleString()} (${v3Pct}%)`);

  // CVEs with CVSS v2 scores
  const [v2] = await db.query('SELECT COUNT(*) as count FROM cves WHERE cvss_score_v2 IS NOT NULL');
  const v2Pct = ((v2[0].count / total[0].count) * 100).toFixed(1);
  log(`📈 CVEs with CVSS v2 scores: ${v2[0].count.toLocaleString()} (${v2Pct}%)`);

  // CVEs with CVSS v4 scores
  const [v4] = await db.query('SELECT COUNT(*) as count FROM cves WHERE cvss_score_v4 IS NOT NULL');
  const v4Pct = ((v4[0].count / total[0].count) * 100).toFixed(1);
  log(`📈 CVEs with CVSS v4 scores: ${v4[0].count.toLocaleString()} (${v4Pct}%)`);

  // CVEs with CWE IDs
  const [cwe] = await db.query('SELECT COUNT(*) as count FROM cves WHERE cwe_ids IS NOT NULL AND cwe_ids != ""');
  const cwePct = ((cwe[0].count / total[0].count) * 100).toFixed(1);
  log(`🏷️  CVEs with CWE IDs: ${cwe[0].count.toLocaleString()} (${cwePct}%)`);

  // CVEs with references
  const [refs] = await db.query('SELECT COUNT(*) as count FROM cves WHERE `references` IS NOT NULL AND `references` != ""');
  const refsPct = ((refs[0].count / total[0].count) * 100).toFixed(1);
  log(`🔗 CVEs with references: ${refs[0].count.toLocaleString()} (${refsPct}%)`);

  // KEV data
  const [kev] = await db.query('SELECT COUNT(*) as count FROM kev_catalog');
  log(`🛡️  Known Exploited Vulnerabilities (KEV): ${kev[0].count.toLocaleString()}`);

  // Microsoft CVEs
  const [msrc] = await db.query('SELECT COUNT(*) as count FROM microsoft_cves');
  log(`🖥️  Microsoft CVEs: ${msrc[0].count.toLocaleString()}`);

  // CPE matches
  const [cpe] = await db.query('SELECT COUNT(*) as count FROM cpe_matches');
  log(`💿 CPE matches (affected software): ${cpe[0].count.toLocaleString()}`);

  log('\n━'.repeat(70));
  log('📅 CVEs by Severity (CVSS v3):');
  log('━'.repeat(70));

  const [severity] = await db.query(`
    SELECT
      severity_v3,
      COUNT(*) as count,
      ROUND(AVG(cvss_score_v3), 1) as avg_score
    FROM cves
    WHERE severity_v3 IS NOT NULL
    GROUP BY severity_v3
    ORDER BY
      CASE severity_v3
        WHEN 'CRITICAL' THEN 1
        WHEN 'HIGH' THEN 2
        WHEN 'MEDIUM' THEN 3
        WHEN 'LOW' THEN 4
        ELSE 5
      END
  `);

  severity.forEach(row => {
    const pct = ((row.count / total[0].count) * 100).toFixed(1);
    const icon = {
      'CRITICAL': '🔴',
      'HIGH': '🟠',
      'MEDIUM': '🟡',
      'LOW': '🟢'
    }[row.severity_v3] || '⚪';
    log(`${icon} ${(row.severity_v3 || 'UNKNOWN').padEnd(10)} ${row.count.toString().padStart(8)} (${pct.padStart(5)}%) - Avg: ${row.avg_score}`);
  });

  log('\n━'.repeat(70));
  log('📅 Recent Activity (Last 30 Days):');
  log('━'.repeat(70));

  const [recent] = await db.query(`
    SELECT COUNT(*) as count
    FROM cves
    WHERE last_modified_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  `);
  log(`🆕 CVEs modified in last 30 days: ${recent[0].count.toLocaleString()}`);

  const [recentPub] = await db.query(`
    SELECT COUNT(*) as count
    FROM cves
    WHERE published_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  `);
  log(`📝 CVEs published in last 30 days: ${recentPub[0].count.toLocaleString()}`);

  log('\n━'.repeat(70));
  log('🎯 Data Quality Score:');
  log('━'.repeat(70));

  const qualityScore = (
    (parseFloat(v3Pct) * 0.3) +  // 30% weight on CVSS v3
    (parseFloat(cwePct) * 0.2) +  // 20% weight on CWE
    (parseFloat(refsPct) * 0.2) + // 20% weight on references
    ((cpe[0].count > 0 ? 100 : 0) * 0.15) + // 15% weight on CPE existence
    ((kev[0].count > 0 ? 100 : 0) * 0.15)   // 15% weight on KEV data
  );

  log(`Overall Quality: ${qualityScore.toFixed(1)}%`);

  if (qualityScore >= 90) log('✅ Excellent - Highly enriched database');
  else if (qualityScore >= 75) log('✅ Good - Well enriched database');
  else if (qualityScore >= 60) log('⚠️  Fair - Some enrichment needed');
  else log('❌ Poor - Significant enrichment needed');

  log('\n━'.repeat(70));
  log('💡 Recommendations:');
  log('━'.repeat(70));

  if (parseFloat(v3Pct) < 80) {
    log('⚠️  Run CVE enrichment to get more CVSS v3 scores');
    log('   Use: POST /api/admin/scripts/fetch-cve (runs fetchCVE_v2.js in enrichment mode)');
  }

  if (parseFloat(cwePct) < 70) {
    log('⚠️  Low CWE coverage - consider running enrichment');
  }

  if (kev[0].count < 1000) {
    log('⚠️  KEV data seems low - run: POST /api/admin/scripts/fetch-kev');
  }

  if (msrc[0].count < 10000) {
    log('⚠️  Microsoft CVE data seems low - run: POST /api/admin/scripts/fetch-msrc');
  }

  if (recent[0].count < 100) {
    log('⚠️  No recent updates detected - run daily sync to stay current');
    log('   Use: POST /api/admin/scripts/fetch-cve');
  }

  await db.end();
  log('\n✅ Analysis complete!\n');
}

analyzeDatabaseQuality().catch(err => {
  console.error('❌ Error:', err.message);
  process.exit(1);
});