CWE Analysis: Database Evolution and Quality Improvements¶

import duckdb
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import json
import os
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')
from matplotlib.patches import Patch
import matplotlib.patches as mpatches
from scipy import stats

# Try to use Modin for faster pandas operations
try:
    import modin.pandas as mpd
    USE_MODIN = True
    print("Using Modin for accelerated pandas operations")
except ImportError:
    import pandas as mpd
    USE_MODIN = False
    print("Using standard pandas (Modin not available)")

# Set up high-quality plotting parameters
plt.rcParams['figure.dpi'] = 300
plt.rcParams['savefig.dpi'] = 300
plt.rcParams['savefig.format'] = 'eps'
plt.rcParams['font.size'] = 12
plt.rcParams['axes.titlesize'] = 14
plt.rcParams['axes.labelsize'] = 12
plt.rcParams['xtick.labelsize'] = 10
plt.rcParams['ytick.labelsize'] = 10
plt.rcParams['legend.fontsize'] = 10

# Global analysis period settings
ANALYSIS_END_DATE = "2024-12-31"
ANALYSIS_START_DATE = "1999-01-01"  # Set to None for all data
USE_ALL_DATA = True  # Toggle this to switch between full dataset and filtered

# Create output directory for figures
os.makedirs('figures', exist_ok=True)
os.makedirs('parquet_data', exist_ok=True)
print(f"Analysis Period: {'All available data' if USE_ALL_DATA else f'{ANALYSIS_START_DATE} to {ANALYSIS_END_DATE}'}")

def load_parquet_data():
    """
    Load Parquet files into DuckDB for analysis
    """

    # Create a new connection for analysis
    con = duckdb.connect(':memory:')  # Use in-memory database for faster processing

    # Load all parquet files
    parquet_files = {
        # MySQL tables
        'cve_main': '..\parquet_data\mysql_cve.parquet',
        'cve_main_old': '..\parquet_data\mysql_cvev5_v2.parquet',
        'exploits': '..\parquet_data\mysql_exploit.parquet',
        'exploits_old': '..\parquet_data\mysql_exploit_old.parquet',
        'msrc_patches': '..\parquet_data\mysql_msrc_vuln_unified.parquet',
        'cisco_patches': '..\parquet_data\mysql_cisco_vuln_unified.parquet',
        'redhat_patches': '..\parquet_data\mysql_redhat_vuln_unified.parquet',
        'github_advisories': '..\parquet_data\mysql_github_advisory_unified.parquet',
        'cwe_ref': '..\parquet_data\mysql_cwe.parquet',
        'capec_ref': '..\parquet_data\mysql_capec.parquet',

        # PostgreSQL tables (MoreFixes)
        'morefixes_cve': '..\parquet_data\postgres_cve.parquet',
        'morefixes_fixes': '..\parquet_data\postgres_fixes.parquet',
        'morefixes_commits': '..\parquet_data\postgres_commits.parquet',
        'morefixes_repository': '..\parquet_data\postgres_repository.parquet'
    }

    # Create views for each parquet file
    for table_name, file_path in parquet_files.items():
        if os.path.exists(file_path):
            con.sql(f"CREATE OR REPLACE VIEW {table_name} AS SELECT * FROM '{file_path}'")
            print(f"✓ Loaded {table_name}")
        else:
            print(f"✗ File not found: {file_path}")

    return con

# Load data for analysis
print("Loading Parquet data for analysis...")
analysis_con = load_parquet_data()

# List of all table names I've loaded
table_names = [
    "cve_main", "cve_main_old", "exploits", "msrc_patches", "cisco_patches",
    "redhat_patches", "github_advisories", "cwe_ref", "capec_ref",
    "morefixes_cve", "morefixes_fixes", "morefixes_commits", "morefixes_repository"
]

print("\n--- Schema for all loaded tables ---")

for table_name in table_names:
    print(f"\nSchema for table: {table_name}")
    try:
        # Execute PRAGMA table_info() to get schema
        schema_info = analysis_con.execute(f"PRAGMA table_info('{table_name}');").fetchall()

        if not schema_info:
            print(f"  (Table '{table_name}' not found or is empty)")
            continue

        # Print header
        header = ["cid", "name", "type", "notnull", "pk", "dflt_value"]
        print(f"  {' '.join(f'{col:<15}' for col in header)}")
        print(f"  {'-'*90}")

        # Print rows
        for col_info in schema_info:
            cid, name, col_type, notnull, pk, dflt_value = col_info
            print(f"  {cid:<15} {name:<15} {col_type:<15} {str(notnull):<15} {str(pk):<15} {str(dflt_value):<15}")
    except duckdb.ParserException as e:
        print(f"  Error retrieving schema for {table_name}: {e}")
    except Exception as e:
        print(f"  An unexpected error occurred for {table_name}: {e}")

def analyze_cwe_comparison_enhanced():
    """
    Enhanced CWE analysis comparing old and new datasets with actual content comparison
    """

    print("=== ENHANCED CWE Comparison Analysis: Old vs New Dataset ===\n")

    # 1. CWE Distribution - Old Database
    print("1. Analyzing CWE Distribution in Old Database...")

    old_cwe_distribution_query = """
    WITH old_cwe_split AS (
        SELECT 
            "CVE ID" as cve_id,
            EXTRACT(YEAR FROM "Date Published") as year,
            TRIM(UNNEST(STRING_SPLIT(cwe, ','))) as cwe_id
        FROM cve_main_old 
        WHERE "State" = 'PUBLISHED'
            AND cwe IS NOT NULL 
            AND cwe != ''
            AND cwe NOT LIKE 'NVD-CWE-%'
    ),
    old_cwe_clean AS (
        SELECT 
            cve_id,
            year,
            cwe_id
        FROM old_cwe_split
        WHERE cwe_id IS NOT NULL 
            AND cwe_id != ''
            AND cwe_id NOT LIKE 'NVD-CWE-%'
    )
    SELECT 
        oc.cwe_id,
        cwe_ref.name as cwe_name,
        COUNT(DISTINCT oc.cve_id) as cve_count,
        COUNT(oc.cve_id) as total_instances,
        MIN(oc.year) as first_seen,
        MAX(oc.year) as last_seen
    FROM old_cwe_clean oc
    LEFT JOIN cwe_ref ON oc.cwe_id = cwe_ref.cwe_id
    GROUP BY oc.cwe_id, cwe_ref.name
    ORDER BY cve_count DESC
    LIMIT 20
    """

    # 2. CWE Distribution - New Database
    print("2. Analyzing CWE Distribution in New Database...")

    new_cwe_distribution_query = """
    WITH new_cwe_split AS (
        SELECT 
            cve_id,
            EXTRACT(YEAR FROM date_published) as year,
            TRIM(UNNEST(STRING_SPLIT(cwe_ids, ','))) as cwe_id
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cwe_ids IS NOT NULL 
            AND cwe_ids != ''
            AND cwe_ids NOT LIKE 'NVD-CWE-%'
    ),
    new_cwe_clean AS (
        SELECT 
            cve_id,
            year,
            cwe_id
        FROM new_cwe_split
        WHERE cwe_id IS NOT NULL 
            AND cwe_id != ''
            AND cwe_id NOT LIKE 'NVD-CWE-%'
    )
    SELECT 
        nc.cwe_id,
        cwe_ref.name as cwe_name,
        COUNT(DISTINCT nc.cve_id) as cve_count,
        COUNT(nc.cve_id) as total_instances,
        MIN(nc.year) as first_seen,
        MAX(nc.year) as last_seen
    FROM new_cwe_clean nc
    LEFT JOIN cwe_ref ON nc.cwe_id = cwe_ref.cwe_id
    GROUP BY nc.cwe_id, cwe_ref.name
    ORDER BY cve_count DESC
    LIMIT 20
    """

    old_cwe_dist = analysis_con.sql(old_cwe_distribution_query).df()
    new_cwe_dist = analysis_con.sql(new_cwe_distribution_query).df()

    # 3. Side-by-side comparison
    print("3. Creating Side-by-Side CWE Comparison...")

    # Merge old and new distributions
    comparison_df = pd.merge(
        old_cwe_dist[['cwe_id', 'cwe_name', 'cve_count']], 
        new_cwe_dist[['cwe_id', 'cve_count']], 
        on='cwe_id', 
        how='outer', 
        suffixes=('_old', '_new')
    ).fillna(0)

    comparison_df['cve_difference'] = comparison_df['cve_count_new'] - comparison_df['cve_count_old']
    comparison_df['percentage_change'] = (comparison_df['cve_difference'] / comparison_df['cve_count_old'].replace(0, 1)) * 100
    comparison_df = comparison_df.sort_values('cve_count_new', ascending=False)

    print("\nTop 15 CWEs - Old vs New Comparison:")
    print("="*100)
    print(f"{'CWE ID':<12} {'CWE Name':<35} {'Old CVEs':<10} {'New CVEs':<10} {'Difference':<12} {'% Change':<10}")
    print("="*100)

    for _, row in comparison_df.head(15).iterrows():
        cwe_name = str(row['cwe_name'])[:33] + "..." if len(str(row['cwe_name'])) > 35 else str(row['cwe_name'])
        change_sign = "+" if row['cve_difference'] > 0 else ""
        print(f"{row['cwe_id']:<12} {cwe_name:<35} {int(row['cve_count_old']):<10} {int(row['cve_count_new']):<10} "
              f"{change_sign}{int(row['cve_difference']):<12} {row['percentage_change']:+.1f}%")

    # 4. ENHANCED: Detailed CWE Content Analysis for Overlapping CVEs
    print("\n4. ENHANCED: Analyzing Actual CWE Content Changes for Overlapping CVEs...")

    cwe_content_changes_query = """
    WITH old_cve_cwe AS (
        SELECT 
            "CVE ID" as cve_id,
            COALESCE(cwe, '') as old_cwe_string,
            CASE 
                WHEN cwe IS NULL OR cwe = '' OR cwe NOT LIKE 'NVD-CWE-%' THEN ''
                ELSE cwe
            END as old_cwe_clean
        FROM cve_main_old 
        WHERE "State" = 'PUBLISHED'
    ),
    new_cve_cwe AS (
        SELECT 
            cve_id,
            COALESCE(cwe_ids, '') as new_cwe_string,
            CASE 
                WHEN cwe_ids IS NULL OR cwe_ids = '' OR cwe_ids NOT LIKE 'NVD-CWE-%' THEN ''
                ELSE cwe_ids
            END as new_cwe_clean
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND date_published <= '2024-04-21'
    ),
    cwe_content_comparison AS (
        SELECT 
            COALESCE(o.cve_id, n.cve_id) as cve_id,
            o.old_cwe_clean,
            n.new_cwe_clean,
            CASE 
                WHEN o.old_cwe_clean = '' AND n.new_cwe_clean = '' THEN 'Both Empty'
                WHEN o.old_cwe_clean = '' AND n.new_cwe_clean != '' THEN 'Added CWEs'
                WHEN o.old_cwe_clean != '' AND n.new_cwe_clean = '' THEN 'Removed CWEs'
                WHEN o.old_cwe_clean = n.new_cwe_clean THEN 'Identical'
                ELSE 'Content Changed'
            END as change_type,
            CASE 
                WHEN o.old_cwe_clean = '' THEN 0
                ELSE LENGTH(o.old_cwe_clean) - LENGTH(REPLACE(o.old_cwe_clean, ',', '')) + 1
            END as old_cwe_count,
            CASE 
                WHEN n.new_cwe_clean = '' THEN 0
                ELSE LENGTH(n.new_cwe_clean) - LENGTH(REPLACE(n.new_cwe_clean, ',', '')) + 1
            END as new_cwe_count
        FROM old_cve_cwe o
        FULL OUTER JOIN new_cve_cwe n ON o.cve_id = n.cve_id
    )
    SELECT 
        change_type,
        COUNT(*) as cve_count,
        ROUND(AVG(old_cwe_count), 2) as avg_old_cwe_count,
        ROUND(AVG(new_cwe_count), 2) as avg_new_cwe_count,
        ROUND(AVG(new_cwe_count - old_cwe_count), 2) as avg_cwe_change
    FROM cwe_content_comparison
    GROUP BY change_type
    ORDER BY cve_count DESC
    """

    cwe_content_changes = analysis_con.sql(cwe_content_changes_query).df()

    # 5. Get detailed examples of CWE content changes
    print("\n5. Analyzing Specific Examples of CWE Content Changes...")

    detailed_cwe_changes_query = """
    WITH old_cve_cwe AS (
        SELECT 
            "CVE ID" as cve_id,
            COALESCE(cwe, '') as old_cwe_string
        FROM cve_main_old 
        WHERE "State" = 'PUBLISHED'
    ),
    new_cve_cwe AS (
        SELECT 
            cve_id,
            COALESCE(cwe_ids, '') as new_cwe_string
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND date_published <= '2024-04-21'
    ),
    content_changed_examples AS (
        SELECT 
            COALESCE(o.cve_id, n.cve_id) as cve_id,
            o.old_cwe_string,
            n.new_cwe_string,
            CASE 
                WHEN o.old_cwe_string = '' AND n.new_cwe_string != '' THEN 'Added'
                WHEN o.old_cwe_string != '' AND n.new_cwe_string = '' THEN 'Removed'
                WHEN o.old_cwe_string != n.new_cwe_string AND o.old_cwe_string != '' AND n.new_cwe_string != '' THEN 'Modified'
                ELSE 'No Change'
            END as change_category
        FROM old_cve_cwe o
        FULL OUTER JOIN new_cve_cwe n ON o.cve_id = n.cve_id
        WHERE o.old_cwe_string != n.new_cwe_string
            AND NOT (o.old_cwe_string IN ('NVD-CWE-Other', 'NVD-CWE-noinfo', '') 
                    AND n.new_cwe_string IN ('NVD-CWE-Other', 'NVD-CWE-noinfo', ''))
    )
    SELECT 
        cve_id,
        old_cwe_string,
        new_cwe_string,
        change_category
    FROM content_changed_examples
    WHERE change_category IN ('Added', 'Removed', 'Modified')
    ORDER BY 
        CASE change_category 
            WHEN 'Modified' THEN 1 
            WHEN 'Added' THEN 2 
            WHEN 'Removed' THEN 3 
        END,
        cve_id
    LIMIT 20
    """

    detailed_changes = analysis_con.sql(detailed_cwe_changes_query).df()

    # 6. ENHANCED: Coverage Analysis with both CVSS v2 and v3
    print("\n6. ENHANCED: CWE Coverage Analysis with CVSS v2 and v3...")

    enhanced_coverage_query = """
    WITH old_coverage AS (
        SELECT 
            COUNT(*) as total_cves,
            COUNT(CASE WHEN cwe IS NOT NULL AND cwe != '' AND cwe NOT IN ('NVD-CWE-Other', 'NVD-CWE-noinfo') THEN 1 END) as cves_with_cwe,
            COUNT(CASE WHEN "CVSS 3.0 Base Score" IS NOT NULL AND "CVSS 3.0 Base Score" > 0 THEN 1 END) as cves_with_cvss_v3,
            COUNT(CASE WHEN "CVSS 2.0 Base Score" IS NOT NULL AND "CVSS 2.0 Base Score" > 0 THEN 1 END) as cves_with_cvss_v2,
            COUNT(CASE WHEN ("CVSS 3.0 Base Score" IS NOT NULL AND "CVSS 3.0 Base Score" > 0) 
                          OR ("CVSS 2.0 Base Score" IS NOT NULL AND "CVSS 2.0 Base Score" > 0) THEN 1 END) as cves_with_any_cvss
        FROM cve_main_old 
        WHERE "State" = 'PUBLISHED'
    ),
    new_coverage AS (
        SELECT 
            COUNT(*) as total_cves,
            COUNT(CASE WHEN cwe_ids IS NOT NULL AND cwe_ids != '' AND cwe_ids NOT IN ('NVD-CWE-Other', 'NVD-CWE-noinfo') THEN 1 END) as cves_with_cwe,
            COUNT(CASE WHEN cvss_v3_score IS NOT NULL AND cvss_v3_score > 0 THEN 1 END) as cves_with_cvss_v3,
            COUNT(CASE WHEN cvss_v2_score IS NOT NULL AND cvss_v2_score > 0 THEN 1 END) as cves_with_cvss_v2,
            COUNT(CASE WHEN (cvss_v3_score IS NOT NULL AND cvss_v3_score > 0) 
                          OR (cvss_v2_score IS NOT NULL AND cvss_v2_score > 0) THEN 1 END) as cves_with_any_cvss
        FROM cve_main 
        WHERE state = 'PUBLISHED'
    )
    SELECT 
        'Old Database' as database_name,
        total_cves,
        cves_with_cwe,
        ROUND((cves_with_cwe * 100.0 / total_cves), 2) as cwe_coverage_pct,
        cves_with_cvss_v3,
        ROUND((cves_with_cvss_v3 * 100.0 / total_cves), 2) as cvss_v3_coverage_pct,
        cves_with_cvss_v2,
        ROUND((cves_with_cvss_v2 * 100.0 / total_cves), 2) as cvss_v2_coverage_pct,
        cves_with_any_cvss,
        ROUND((cves_with_any_cvss * 100.0 / total_cves), 2) as any_cvss_coverage_pct
    FROM old_coverage

    UNION ALL

    SELECT 
        'New Database' as database_name,
        total_cves,
        cves_with_cwe,
        ROUND((cves_with_cwe * 100.0 / total_cves), 2) as cwe_coverage_pct,
        cves_with_cvss_v3,
        ROUND((cves_with_cvss_v3 * 100.0 / total_cves), 2) as cvss_v3_coverage_pct,
        cves_with_cvss_v2,
        ROUND((cves_with_cvss_v2 * 100.0 / total_cves), 2) as cvss_v2_coverage_pct,
        cves_with_any_cvss,
        ROUND((cves_with_any_cvss * 100.0 / total_cves), 2) as any_cvss_coverage_pct
    FROM new_coverage
    """

    enhanced_coverage = analysis_con.sql(enhanced_coverage_query).df()

    # Print results
    print("\nEnhanced CWE Content Change Analysis:")
    print("="*80)
    print(f"{'Change Type':<15} {'CVE Count':<12} {'Avg Old CWEs':<12} {'Avg New CWEs':<12} {'Avg Change':<12}")
    print("="*80)

    for _, row in cwe_content_changes.iterrows():
        print(f"{row['change_type']:<15} {row['cve_count']:<12,} {row['avg_old_cwe_count']:<12.1f} "
              f"{row['avg_new_cwe_count']:<12.1f} {row['avg_cwe_change']:<12.1f}")

    print("\nDetailed Examples of CWE Content Changes:")
    print("="*120)
    print(f"{'CVE ID':<15} {'Change Type':<10} {'Old CWEs':<40} {'New CWEs':<40}")
    print("="*120)

    for _, row in detailed_changes.head(15).iterrows():
        old_cwe = str(row['old_cwe_string'])[:38] + "..." if len(str(row['old_cwe_string'])) > 40 else str(row['old_cwe_string'])
        new_cwe = str(row['new_cwe_string'])[:38] + "..." if len(str(row['new_cwe_string'])) > 40 else str(row['new_cwe_string'])
        print(f"{row['cve_id']:<15} {row['change_category']:<10} {old_cwe:<40} {new_cwe:<40}")

    print("\nEnhanced Coverage Comparison (CWE + CVSS v2/v3):")
    print("="*120)
    print(f"{'Database':<15} {'Total CVEs':<10} {'CWE %':<8} {'CVSS v3 %':<10} {'CVSS v2 %':<10} {'Any CVSS %':<10}")
    print("="*120)

    for _, row in enhanced_coverage.iterrows():
        print(f"{row['database_name']:<15} {row['total_cves']:<10,} {row['cwe_coverage_pct']:<8.1f}% "
              f"{row['cvss_v3_coverage_pct']:<10.1f}% {row['cvss_v2_coverage_pct']:<10.1f}% {row['any_cvss_coverage_pct']:<10.1f}%")

    # 8. Print Enhanced Summary Statistics
    print("\n8. Enhanced Summary Statistics:")
    print("="*80)
    if len(enhanced_coverage) == 2:
        old_coverage = enhanced_coverage[enhanced_coverage['database_name'] == 'Old Database'].iloc[0]
        new_coverage = enhanced_coverage[enhanced_coverage['database_name'] == 'New Database'].iloc[0]

        cve_diff = new_coverage['total_cves'] - old_coverage['total_cves']
        cwe_coverage_diff = new_coverage['cwe_coverage_pct'] - old_coverage['cwe_coverage_pct']
        cvss_v3_diff = new_coverage['cvss_v3_coverage_pct'] - old_coverage['cvss_v3_coverage_pct']
        cvss_v2_diff = new_coverage['cvss_v2_coverage_pct'] - old_coverage['cvss_v2_coverage_pct']

        print(f"Database Growth: {cve_diff:+,} CVEs")
        print(f"CWE Coverage Change: {cwe_coverage_diff:+.1f} percentage points")
        print(f"CVSS v3 Coverage Change: {cvss_v3_diff:+.1f} percentage points")
        print(f"CVSS v2 Coverage Change: {cvss_v2_diff:+.1f} percentage points")

        # Content change analysis summary
        if not cwe_content_changes.empty:
            total_compared = cwe_content_changes['cve_count'].sum()
            print(f"\nCWE Content Changes for Overlapping CVEs ({total_compared:,} total):")
            for _, row in cwe_content_changes.iterrows():
                percentage = (row['cve_count'] / total_compared) * 100
                print(f"  {row['change_type']}: {row['cve_count']:,} CVEs ({percentage:.1f}%)")

        # Top gainers and losers
        gainers = comparison_df[comparison_df['cve_difference'] > 0].head(5)
        losers = comparison_df[comparison_df['cve_difference'] < 0].head(5)

        print(f"\nTop 5 CWE Gainers:")
        for _, row in gainers.iterrows():
            cwe_name = str(row['cwe_name'])[:30] + "..." if len(str(row['cwe_name'])) > 30 else str(row['cwe_name'])
            print(f"  {row['cwe_id']} ({cwe_name}): +{int(row['cve_difference'])} CVEs ({row['percentage_change']:+.1f}%)")

        print(f"\nTop 5 CWE Losers:")
        for _, row in losers.iterrows():
            cwe_name = str(row['cwe_name'])[:30] + "..." if len(str(row['cwe_name'])) > 30 else str(row['cwe_name'])
            print(f"  {row['cwe_id']} ({cwe_name}): {int(row['cve_difference'])} CVEs ({row['percentage_change']:+.1f}%)")

    # 9. Create Temporal Evolution Visualization
    print("\n9. Creating Temporal Evolution Analysis...")

    # Get yearly data for both databases
    yearly_comparison_query = """
    WITH old_yearly AS (
        SELECT 
            EXTRACT(YEAR FROM "Date Published") as year,
            COUNT(*) as total_cves,
            COUNT(CASE WHEN cwe IS NOT NULL AND cwe != '' AND cwe NOT IN ('NVD-CWE-Other', 'NVD-CWE-noinfo') THEN 1 END) as cves_with_cwe
        FROM cve_main_old 
        WHERE "State" = 'PUBLISHED'
            AND "Date Published" IS NOT NULL
            AND EXTRACT(YEAR FROM "Date Published") >= 2010
            AND EXTRACT(YEAR FROM "Date Published") <= 2024
        GROUP BY EXTRACT(YEAR FROM "Date Published")
    ),
    new_yearly AS (
        SELECT 
            EXTRACT(YEAR FROM date_published) as year,
            COUNT(*) as total_cves,
            COUNT(CASE WHEN cwe_ids IS NOT NULL AND cwe_ids != '' AND cwe_ids NOT IN ('NVD-CWE-Other', 'NVD-CWE-noinfo') THEN 1 END) as cves_with_cwe
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND date_published IS NOT NULL
            AND EXTRACT(YEAR FROM date_published) >= 2010
            AND EXTRACT(YEAR FROM date_published) <= 2024
        GROUP BY EXTRACT(YEAR FROM date_published)
    )
    SELECT 
        COALESCE(o.year, n.year) as year,
        COALESCE(o.total_cves, 0) as old_total_cves,
        COALESCE(o.cves_with_cwe, 0) as old_cves_with_cwe,
        COALESCE(n.total_cves, 0) as new_total_cves,
        COALESCE(n.cves_with_cwe, 0) as new_cves_with_cwe,
        ROUND(COALESCE(o.cves_with_cwe, 0) * 100.0 / NULLIF(COALESCE(o.total_cves, 0), 0), 2) as old_coverage_pct,
        ROUND(COALESCE(n.cves_with_cwe, 0) * 100.0 / NULLIF(COALESCE(n.total_cves, 0), 0), 2) as new_coverage_pct
    FROM old_yearly o
    FULL OUTER JOIN new_yearly n ON o.year = n.year
    ORDER BY year
    """

    yearly_data = analysis_con.sql(yearly_comparison_query).df()

    if not yearly_data.empty:
        fig_temporal, (ax1, ax2) = plt.subplots(2, 1, figsize=(16, 12))

        years = yearly_data['year'].tolist()

        # Plot 1: CVE Counts Evolution
        ax1.plot(years, yearly_data['old_total_cves'], marker='o', linewidth=3, markersize=8,
                label='Old Database - Total CVEs', color='#FF6B6B', linestyle='-')
        ax1.plot(years, yearly_data['old_cves_with_cwe'], marker='o', linewidth=2, markersize=6,
                label='Old Database - CVEs with CWE', color='#FF6B6B', linestyle='--', alpha=0.7)
        ax1.plot(years, yearly_data['new_total_cves'], marker='s', linewidth=3, markersize=8,
                label='New Database - Total CVEs', color='#4ECDC4', linestyle='-')
        ax1.plot(years, yearly_data['new_cves_with_cwe'], marker='s', linewidth=2, markersize=6,
                label='New Database - CVEs with CWE', color='#4ECDC4', linestyle='--', alpha=0.7)

        ax1.set_xlabel('Year', fontsize=12)
        ax1.set_ylabel('Number of CVEs', fontsize=12)
        ax1.set_title('CVE Count Evolution: Old vs New Database', fontsize=14, fontweight='bold')
        ax1.legend()
        ax1.grid(True, alpha=0.3)

        # Plot 2: Coverage Percentage Evolution
        ax2.plot(years, yearly_data['old_coverage_pct'], marker='o', linewidth=3, markersize=8,
                label='Old Database CWE Coverage', color='#FF6B6B')
        ax2.plot(years, yearly_data['new_coverage_pct'], marker='s', linewidth=3, markersize=8,
                label='New Database CWE Coverage', color='#4ECDC4')

        ax2.set_xlabel('Year', fontsize=12)
        ax2.set_ylabel('CWE Coverage Percentage (%)', fontsize=12)
        ax2.set_title('CWE Coverage Evolution: Old vs New Database', fontsize=14, fontweight='bold')
        ax2.legend()
        ax2.grid(True, alpha=0.3)
        ax2.set_ylim(0, 100)

        plt.tight_layout()
        plt.savefig('cwe_temporal_evolution.png', dpi=300, bbox_inches='tight')
    plt.show()

    return {
        'old_distribution': old_cwe_dist,
        'new_distribution': new_cwe_dist,
        'comparison': comparison_df,
        'content_changes': cwe_content_changes,
        'detailed_changes': detailed_changes,
        'enhanced_coverage': enhanced_coverage
    }

def analyze_cwe_cpe_relationships_enhanced():
    """
    Enhanced CWE-CPE relationship analysis with proper CPE type extraction and statistical measures
    """

    print("\n\n=== ENHANCED CWE-CPE Relationship Analysis ===\n")

    # 1. ENHANCED: CWE-CPE Co-occurrence with proper CPE type extraction
    print("1. ENHANCED: Analyzing CWE-CPE Co-occurrence with CPE Types (h/o/a)...")

    enhanced_cooccurrence_query = """
    WITH cve_cwe_cpe AS (
        SELECT 
            cve_id,
            cwe_ids,
            cpes,
            CASE 
                WHEN cpes IS NULL OR cpes = '' THEN 0
                ELSE LENGTH(cpes) - LENGTH(REPLACE(cpes, ',', '')) + 1
            END as cpe_count,
            -- FIXED: Proper CWE counting (exclude NVD-CWE but count real CWEs)
            CASE 
                WHEN cwe_ids IS NULL OR cwe_ids = '' OR cwe_ids LIKE 'NVD-CWE-%' THEN 0
                ELSE LENGTH(cwe_ids) - LENGTH(REPLACE(cwe_ids, ',', '')) + 1
            END as cwe_count
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cwe_ids IS NOT NULL 
            AND cwe_ids != ''
            AND cwe_ids NOT LIKE 'NVD-CWE-%'  -- Filter out NVD-CWE entries
            AND cpes IS NOT NULL
            AND cpes != ''
    ),
    cpe_types_extracted AS (
        SELECT 
            cve_id,
            cwe_count,
            cpe_count,
            cpes,
            -- FIXED: DuckDB-compatible CPE type counting
            (LENGTH(cpes) - LENGTH(REPLACE(REPLACE(cpes, 'cpe:/h:', ''), 'cpe:2.3:h:', ''))) / 8 as hardware_count,
            (LENGTH(cpes) - LENGTH(REPLACE(REPLACE(cpes, 'cpe:/o:', ''), 'cpe:2.3:o:', ''))) / 8 as os_count,
            (LENGTH(cpes) - LENGTH(REPLACE(REPLACE(cpes, 'cpe:/a:', ''), 'cpe:2.3:a:', ''))) / 8 as app_count
        FROM cve_cwe_cpe
    ),
    cpe_types_labeled AS (
        SELECT *,
            CASE 
                WHEN hardware_count > 0 AND os_count = 0 AND app_count = 0 THEN 'Hardware'
                WHEN os_count > 0 AND hardware_count = 0 AND app_count = 0 THEN 'Operating System'
                WHEN app_count > 0 AND hardware_count = 0 AND os_count = 0 THEN 'Application'
                ELSE 'Mixed/Unknown'
            END AS primary_cpe_type
        FROM cpe_types_extracted
    ),
    cwe_cpe_categories AS (
        SELECT 
            cve_id,
            CASE 
                WHEN cwe_count = 1 THEN '1 CWE'
                WHEN cwe_count = 2 THEN '2 CWEs'
                WHEN cwe_count = 3 THEN '3 CWEs'
                WHEN cwe_count BETWEEN 4 AND 5 THEN '4-5 CWEs'
                WHEN cwe_count >= 6 THEN '6+ CWEs'
                ELSE '0 CWEs'  -- This shouldn't happen given our filtering
            END as cwe_category,
            CASE 
                WHEN cpe_count = 1 THEN '1 CPE'
                WHEN cpe_count BETWEEN 2 AND 5 THEN '2-5 CPEs'
                WHEN cpe_count BETWEEN 6 AND 10 THEN '6-10 CPEs'
                WHEN cpe_count BETWEEN 11 AND 50 THEN '11-50 CPEs'
                WHEN cpe_count BETWEEN 51 AND 100 THEN '51-100 CPEs'
                ELSE '100+ CPEs'
            END as cpe_category,
            primary_cpe_type,
            cwe_count,
            cpe_count,
            hardware_count,
            os_count,
            app_count
        FROM cpe_types_labeled
        WHERE cwe_count > 0  -- Ensure we only include CVEs with actual CWEs
    )
    SELECT 
        cwe_category,
        cpe_category,
        primary_cpe_type,
        COUNT(*) as cve_count,
        ROUND(AVG(cwe_count), 2) as avg_cwe_count,
        ROUND(AVG(cpe_count), 2) as avg_cpe_count,
        ROUND(AVG(hardware_count), 2) as avg_hardware_cpes,
        ROUND(AVG(os_count), 2) as avg_os_cpes,
        ROUND(AVG(app_count), 2) as avg_app_cpes
    FROM cwe_cpe_categories
    GROUP BY cwe_category, cpe_category, primary_cpe_type
    ORDER BY cve_count DESC
    LIMIT 30
    """

    enhanced_cooccurrence = analysis_con.sql(enhanced_cooccurrence_query).df()

    # 2. ENHANCED: Severity Analysis with Median, Mode, and both CVSS versions
    print("\n2. ENHANCED: Severity Analysis with Statistical Measures (CVSS v2 & v3)...")

    enhanced_severity_query = """
    WITH cve_metrics AS (
        SELECT 
            cve_id,
            -- CVSS v3 analysis
            cvss_v3_score,
            CASE 
                WHEN cvss_v3_score >= 9.0 THEN 'Critical (9.0-10.0)'
                WHEN cvss_v3_score >= 7.0 THEN 'High (7.0-8.9)'
                WHEN cvss_v3_score >= 4.0 THEN 'Medium (4.0-6.9)'
                WHEN cvss_v3_score > 0 THEN 'Low (0.1-3.9)'
                ELSE 'No CVSS v3'
            END as cvss_v3_category,
            -- CVSS v2 analysis
            cvss_v2_score,
            CASE 
                WHEN cvss_v2_score >= 7.0 THEN 'High (7.0-10.0)'
                WHEN cvss_v2_score >= 4.0 THEN 'Medium (4.0-6.9)'
                WHEN cvss_v2_score > 0 THEN 'Low (0.1-3.9)'
                ELSE 'No CVSS v2'
            END as cvss_v2_category,
            -- Best available score
            COALESCE(cvss_v3_score, cvss_v2_score) as best_cvss_score,
            CASE 
                WHEN COALESCE(cvss_v3_score, cvss_v2_score) >= 9.0 THEN 'Critical (9.0-10.0)'
                WHEN COALESCE(cvss_v3_score, cvss_v2_score) >= 7.0 THEN 'High (7.0-8.9)' 
                WHEN COALESCE(cvss_v3_score, cvss_v2_score) >= 4.0 THEN 'Medium (4.0-6.9)'
                WHEN COALESCE(cvss_v3_score, cvss_v2_score) > 0 THEN 'Low (0.1-3.9)'
                ELSE 'No Score'
            END as best_severity_category,
            CASE 
                WHEN cpes IS NULL OR cpes = '' THEN 0
                ELSE LENGTH(cpes) - LENGTH(REPLACE(cpes, ',', '')) + 1
            END as cpe_count,
            CASE 
                WHEN cwe_ids IS NULL OR cwe_ids = '' OR cwe_ids IN ('NVD-CWE-Other', 'NVD-CWE-noinfo') THEN 0
                ELSE LENGTH(cwe_ids) - LENGTH(REPLACE(cwe_ids, ',', '')) + 1
            END as cwe_count
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cwe_ids IS NOT NULL 
            AND cwe_ids != ''
            AND cwe_ids NOT LIKE 'NVD-CWE-%'
    )
    SELECT 
        best_severity_category,
        COUNT(*) as cve_count,
        -- CWE statistics
        ROUND(AVG(cwe_count), 2) as avg_cwe_count,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cwe_count) as median_cwe_count,
        MODE() WITHIN GROUP (ORDER BY cwe_count) as mode_cwe_count,
        MAX(cwe_count) as max_cwe_count,
        -- CPE statistics  
        ROUND(AVG(cpe_count), 2) as avg_cpe_count,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cpe_count) as median_cpe_count,
        MODE() WITHIN GROUP (ORDER BY cpe_count) as mode_cpe_count,
        MAX(cpe_count) as max_cpe_count,
        -- CVSS score statistics
        ROUND(AVG(best_cvss_score), 2) as avg_cvss_score,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY best_cvss_score) as median_cvss_score
    FROM cve_metrics
    WHERE best_cvss_score IS NOT NULL
    GROUP BY best_severity_category
    ORDER BY 
        CASE best_severity_category
            WHEN 'Critical (9.0-10.0)' THEN 1
            WHEN 'High (7.0-8.9)' THEN 2
            WHEN 'Medium (4.0-6.9)' THEN 3
            WHEN 'Low (0.1-3.9)' THEN 4
            ELSE 5
        END
    """

    enhanced_severity = analysis_con.sql(enhanced_severity_query).df()

    # 3. ENHANCED: CWE-CPE Type Relationship Analysis
    print("\n3. ENHANCED: CWE-CPE Type Relationship Analysis...")

    cwe_cpe_type_query = """
        WITH cwe_expanded AS (
        SELECT 
            cve_id,
            TRIM(UNNEST(STRING_SPLIT(cwe_ids, ','))) as cwe_id
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cwe_ids IS NOT NULL 
            AND cwe_ids != ''
            AND cwe_ids NOT LIKE 'NVD-CWE-%'
            AND cpes IS NOT NULL
            AND cpes != ''
    ),
    cpe_type_analysis AS (
        SELECT 
            cve_id,
            cpes,

            -- Count matches via REGEXP_REPLACE + LENGTH trick
            LENGTH(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x')) 
                - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x'), 'x', '')) AS hardware_count,

            LENGTH(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x')) 
                - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x'), 'x', '')) AS os_count,

            LENGTH(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x')) 
                - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x'), 'x', '')) AS app_count,

            -- Determine primary type
            CASE 
                WHEN 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x'), 'x', ''))) 
                    > 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x'), 'x', '')))
                AND 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/h:|cpe:2\\.3:h:', 'x'), 'x', '')))
                    > 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x'), 'x', '')))
                THEN 'Hardware'

                WHEN 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/o:|cpe:2\\.3:o:', 'x'), 'x', '')))
                    > 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x'), 'x', '')))
                THEN 'Operating System'

                WHEN 
                    (LENGTH(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x')) 
                    - LENGTH(REPLACE(REGEXP_REPLACE(cpes, 'cpe:/a:|cpe:2\\.3:a:', 'x'), 'x', '')))
                    > 0
                THEN 'Application'

                ELSE 'Mixed/Unknown'
            END as primary_cpe_type

        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cpes IS NOT NULL
            AND cpes != ''
    ),
    cwe_cpe_type_combinations AS (
        SELECT 
            ce.cwe_id,
            cta.primary_cpe_type,
            COUNT(DISTINCT ce.cve_id) as cve_count,
            ROUND(AVG(cta.hardware_count), 2) as avg_hardware_cpes,
            ROUND(AVG(cta.os_count), 2) as avg_os_cpes,
            ROUND(AVG(cta.app_count), 2) as avg_app_cpes,
            cwe_ref.name as cwe_name
        FROM cwe_expanded ce
        INNER JOIN cpe_type_analysis cta ON ce.cve_id = cta.cve_id
        LEFT JOIN cwe_ref ON ce.cwe_id = cwe_ref.cwe_id
        GROUP BY ce.cwe_id, cta.primary_cpe_type, cwe_ref.name
        HAVING COUNT(DISTINCT ce.cve_id) >= 5
    )
    SELECT 
        cwe_id,
        cwe_name,
        primary_cpe_type,
        cve_count,
        avg_hardware_cpes,
        avg_os_cpes,
        avg_app_cpes
    FROM cwe_cpe_type_combinations
    ORDER BY cve_count DESC
    LIMIT 25;
    """

    cwe_cpe_type_data = analysis_con.sql(cwe_cpe_type_query).df()

    # 4. ENHANCED: Top CWE-Vendor Combinations with CPE version support
    print("\n4. ENHANCED: Top CWE-Vendor Combinations (CPE v2.2 & v2.3 support)...")

    enhanced_cwe_vendor_query = """
    WITH cwe_expanded AS (
        SELECT 
            cve_id,
            TRIM(UNNEST(STRING_SPLIT(cwe_ids, ','))) as cwe_id
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cwe_ids IS NOT NULL 
            AND cwe_ids != ''
            AND cwe_ids NOT LIKE 'NVD-CWE-%'
            AND cpes IS NOT NULL
            AND cpes != ''
    ),
    cpe_expanded AS (
        SELECT 
            cve_id,
            TRIM(UNNEST(STRING_SPLIT(cpes, ','))) as cpe_entry
        FROM cve_main 
        WHERE state = 'PUBLISHED'
            AND cpes IS NOT NULL
            AND cpes != ''
    ),
    cpe_vendors AS (
        SELECT 
            cve_id,
            cpe_entry,
            -- Handle both CPE v2.2 (cpe:/) and v2.3 (cpe:2.3:) formats
            CASE 
                WHEN cpe_entry LIKE 'cpe:/%' THEN SPLIT_PART(SUBSTR(cpe_entry, 6), ':', 2)
                WHEN cpe_entry LIKE 'cpe:2.3:%' THEN SPLIT_PART(cpe_entry, ':', 4)
                ELSE NULL
            END as vendor,
            -- Extract CPE type
            CASE 
                WHEN cpe_entry LIKE 'cpe:/h:%' OR cpe_entry LIKE 'cpe:2.3:h:%' THEN 'Hardware'
                WHEN cpe_entry LIKE 'cpe:/o:%' OR cpe_entry LIKE 'cpe:2.3:o:%' THEN 'Operating System'
                WHEN cpe_entry LIKE 'cpe:/a:%' OR cpe_entry LIKE 'cpe:2.3:a:%' THEN 'Application'
                ELSE 'Unknown'
            END as cpe_type
        FROM cpe_expanded
        WHERE cpe_entry LIKE 'cpe:%'
    ),
    cwe_vendor_combinations AS (
        SELECT 
            ce.cwe_id,
            cv.vendor,
            cv.cpe_type,
            COUNT(DISTINCT ce.cve_id) as cve_count,
            COUNT(cv.cpe_entry) as total_cpe_instances,
            cwe_ref.name as cwe_name
        FROM cwe_expanded ce
        INNER JOIN cpe_vendors cv ON ce.cve_id = cv.cve_id
        LEFT JOIN cwe_ref ON ce.cwe_id = cwe_ref.cwe_id
        WHERE cv.vendor IS NOT NULL
            AND cv.vendor != ''
            AND cv.vendor != '*'
            AND LENGTH(cv.vendor) > 1
        GROUP BY ce.cwe_id, cv.vendor, cv.cpe_type, cwe_ref.name
        HAVING COUNT(DISTINCT ce.cve_id) >= 10
    )
    SELECT 
        cwe_id,
        cwe_name,
        vendor,
        cpe_type,
        cve_count,
        total_cpe_instances
    FROM cwe_vendor_combinations
    ORDER BY cve_count DESC
    LIMIT 25
    """

    enhanced_cwe_vendor = analysis_con.sql(enhanced_cwe_vendor_query).df()

    # 5. Create comprehensive visualizations
    print("\n5. Creating Enhanced Visualizations...")

    import matplotlib.pyplot as plt
    import seaborn as sns
    import numpy as np

    # Create multiple figure layouts
    fig1, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, figsize=(20, 16))

    # Plot 1: Enhanced Severity Analysis with Statistical Measures
    if not enhanced_severity.empty:
        severities = enhanced_severity['best_severity_category'].tolist()
        avg_cpes = enhanced_severity['avg_cpe_count'].tolist()
        median_cpes = enhanced_severity['median_cpe_count'].tolist()
        avg_cwes = enhanced_severity['avg_cwe_count'].tolist()
        median_cwes = enhanced_severity['median_cwe_count'].tolist()

        x = np.arange(len(severities))
        width = 0.2

        bars1 = ax1.bar(x - width*1.5, avg_cpes, width, label='Avg CPEs', alpha=0.8, color='lightcoral')
        bars2 = ax1.bar(x - width/2, median_cpes, width, label='Median CPEs', alpha=0.8, color='darkred')
        bars3 = ax1.bar(x + width/2, avg_cwes, width, label='Avg CWEs', alpha=0.8, color='skyblue')
        bars4 = ax1.bar(x + width*1.5, median_cwes, width, label='Median CWEs', alpha=0.8, color='navy')

        ax1.set_xlabel('CVSS Severity Category', fontsize=12)
        ax1.set_ylabel('Count', fontsize=12)
        ax1.set_title('Enhanced Severity Analysis\n(Average vs Median CWE/CPE Counts)', fontsize=14, fontweight='bold')
        ax1.set_xticks(x)
        ax1.set_xticklabels(severities, rotation=45, ha='right')
        ax1.legend()
        ax1.grid(axis='y', alpha=0.3)

    # Plot 2: CPE Type Distribution
    if not enhanced_cooccurrence.empty:
        cpe_type_summary = enhanced_cooccurrence.groupby('primary_cpe_type')['cve_count'].sum().sort_values(ascending=False)

        colors = plt.cm.Set3(np.linspace(0, 1, len(cpe_type_summary)))
        wedges, texts, autotexts = ax2.pie(cpe_type_summary.values, 
                                          labels=cpe_type_summary.index,
                                          colors=colors, autopct='%1.1f%%', startangle=90)

        ax2.set_title('CVE Distribution by CPE Type\n(Hardware/OS/Application)', fontsize=14, fontweight='bold')

        for autotext in autotexts:
            autotext.set_color('white')
            autotext.set_fontweight('bold')

    # Plot 3: Top CWE-Vendor-CPE Type Combinations
    if not enhanced_cwe_vendor.empty:
        top_combinations = enhanced_cwe_vendor.head(15)

        # Create labels combining CWE, vendor, and type
        labels = [f"{row['cwe_id']}\n{row['vendor'].title()[:10]}\n({row['cpe_type'][:3]})" 
                 for _, row in top_combinations.iterrows()]

        # Color by CPE type
        colors = []
        for _, row in top_combinations.iterrows():
            if row['cpe_type'] == 'Hardware':
                colors.append('#FF6B6B')
            elif row['cpe_type'] == 'Operating System':
                colors.append('#4ECDC4') 
            elif row['cpe_type'] == 'Application':
                colors.append('#45B7D1')
            else:
                colors.append('#FFA07A')

        bars = ax3.barh(range(len(top_combinations)), top_combinations['cve_count'], color=colors, alpha=0.8)

        ax3.set_yticks(range(len(top_combinations)))
        ax3.set_yticklabels(labels, fontsize=9)
        ax3.set_xlabel('Number of CVEs', fontsize=12)
        ax3.set_title('Top 15 CWE-Vendor-CPE Type Combinations', fontsize=14, fontweight='bold')
        ax3.grid(axis='x', alpha=0.3)
        ax3.invert_yaxis()

        # Add value labels
        for i, bar in enumerate(bars):
            width = bar.get_width()
            ax3.text(width + max(top_combinations['cve_count']) * 0.01, 
                    bar.get_y() + bar.get_height()/2,
                    f'{int(width):,}', ha='left', va='center', fontsize=9)

    # Plot 4: CWE-CPE Type Analysis
    if not cwe_cpe_type_data.empty:
        # Group by CWE and show distribution across CPE types
        cwe_type_pivot = cwe_cpe_type_data.pivot_table(
            index='cwe_id', columns='primary_cpe_type', values='cve_count', 
            aggfunc='sum', fill_value=0
        )

        # Select top CWEs by total count
        cwe_totals = cwe_type_pivot.sum(axis=1).sort_values(ascending=False)
        top_cwes_by_type = cwe_totals.head(12)

        # Plot stacked bar chart
        cwe_type_subset = cwe_type_pivot.loc[top_cwes_by_type.index]

        cwe_type_subset.plot(kind='bar', stacked=True, ax=ax4, 
                           colormap='Set2', alpha=0.8)

        ax4.set_title('Top 12 CWEs by CPE Type Distribution', fontsize=14, fontweight='bold')
        ax4.set_xlabel('CWE ID', fontsize=12)
        ax4.set_ylabel('Number of CVEs', fontsize=12)
        ax4.legend(title='CPE Type', bbox_to_anchor=(1.05, 1), loc='upper left')
        ax4.tick_params(axis='x', rotation=45)
        ax4.grid(axis='y', alpha=0.3)

    plt.tight_layout()
    plt.savefig('enhanced_cwe_cpe_analysis.png', dpi=300, bbox_inches='tight')
    plt.show()

    # Create second figure for detailed statistical analysis
    fig2, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))

    # Detailed severity statistics comparison
    if not enhanced_severity.empty:
        severities = enhanced_severity['best_severity_category'].tolist()

        # Plot 1: CWE Statistics (Average, Median, Mode)
        avg_cwes = enhanced_severity['avg_cwe_count'].tolist()
        median_cwes = enhanced_severity['median_cwe_count'].tolist() 
        mode_cwes = enhanced_severity['mode_cwe_count'].tolist()

        x = np.arange(len(severities))
        width = 0.25

        ax1.bar(x - width, avg_cwes, width, label='Average', alpha=0.8, color='lightcoral')
        ax1.bar(x, median_cwes, width, label='Median', alpha=0.8, color='skyblue')
        ax1.bar(x + width, mode_cwes, width, label='Mode', alpha=0.8, color='lightgreen')

        ax1.set_xlabel('CVSS Severity Category', fontsize=12)
        ax1.set_ylabel('CWE Count', fontsize=12)
        ax1.set_title('CWE Count Statistics by Severity\n(Average vs Median vs Mode)', fontsize=14, fontweight='bold')
        ax1.set_xticks(x)
        ax1.set_xticklabels(severities, rotation=45, ha='right')
        ax1.legend()
        ax1.grid(axis='y', alpha=0.3)

        # Plot 2: CPE Statistics (Average, Median, Mode)
        avg_cpes = enhanced_severity['avg_cpe_count'].tolist()
        median_cpes = enhanced_severity['median_cpe_count'].tolist()
        mode_cpes = enhanced_severity['mode_cpe_count'].tolist()

        ax2.bar(x - width, avg_cpes, width, label='Average', alpha=0.8, color='lightcoral')
        ax2.bar(x, median_cpes, width, label='Median', alpha=0.8, color='skyblue')  
        ax2.bar(x + width, mode_cpes, width, label='Mode', alpha=0.8, color='lightgreen')

        ax2.set_xlabel('CVSS Severity Category', fontsize=12)
        ax2.set_ylabel('CPE Count', fontsize=12)
        ax2.set_title('CPE Count Statistics by Severity\n(Average vs Median vs Mode)', fontsize=14, fontweight='bold')
        ax2.set_xticks(x)
        ax2.set_xticklabels(severities, rotation=45, ha='right')
        ax2.legend()
        ax2.grid(axis='y', alpha=0.3)

    plt.tight_layout()
    plt.savefig('enhanced_severity_statistics.png', dpi=300, bbox_inches='tight')
    plt.show()

    # Print comprehensive results
    print("\nEnhanced CWE-CPE Co-occurrence Analysis:")
    print("="*120)
    print(f"{'CWE Cat':<8} {'CPE Cat':<12} {'CPE Type':<15} {'CVEs':<6} {'Avg CWEs':<8} {'Avg CPEs':<8} {'HW':<6} {'OS':<6} {'App':<6}")
    print("="*120)

    for _, row in enhanced_cooccurrence.head(20).iterrows():
        print(f"{row['cwe_category']:<8} {row['cpe_category']:<12} {row['primary_cpe_type']:<15} "
              f"{row['cve_count']:<6} {row['avg_cwe_count']:<8.1f} {row['avg_cpe_count']:<8.1f} "
              f"{row['avg_hardware_cpes']:<6.1f} {row['avg_os_cpes']:<6.1f} {row['avg_app_cpes']:<6.1f}")

    print("\nEnhanced Severity Analysis with Statistical Measures:")
    print("="*140)
    print(f"{'Severity':<20} {'CVEs':<8} {'Avg CWEs':<8} {'Med CWEs':<8} {'Mode CWEs':<9} {'Avg CPEs':<8} {'Med CPEs':<8} {'Mode CPEs':<9} {'Avg CVSS':<9}")
    print("="*140)

    for _, row in enhanced_severity.iterrows():
        print(f"{row['best_severity_category']:<20} {row['cve_count']:<8,} {row['avg_cwe_count']:<8.1f} "
              f"{row['median_cwe_count']:<8.1f} {row['mode_cwe_count']:<9.0f} {row['avg_cpe_count']:<8.1f} "
              f"{row['median_cpe_count']:<8.1f} {row['mode_cpe_count']:<9.0f} {row['avg_cvss_score']:<9.1f}")

    print("\nEnhanced CWE-Vendor-CPE Type Analysis:")
    print("="*120)
    print(f"{'CWE ID':<12} {'CWE Name':<35} {'Vendor':<15} {'CPE Type':<15} {'CVEs':<8} {'CPE Inst.':<10}")
    print("="*120)

    for _, row in enhanced_cwe_vendor.head(20).iterrows():
        cwe_name = str(row['cwe_name'])[:33] + "..." if len(str(row['cwe_name'])) > 35 else str(row['cwe_name'])
        vendor_name = str(row['vendor'])[:13] + "..." if len(str(row['vendor'])) > 15 else str(row['vendor'])
        print(f"{row['cwe_id']:<12} {cwe_name:<35} {vendor_name.title():<15} {row['cpe_type']:<15} "
              f"{row['cve_count']:<8,} {row['total_cpe_instances']:<10,}")

    print("\nCWE-CPE Type Distribution Analysis:")
    print("="*100)
    print(f"{'CWE ID':<12} {'CWE Name':<35} {'CPE Type':<15} {'CVEs':<8} {'Avg HW':<8} {'Avg OS':<8} {'Avg App':<8}")
    print("="*100)

    for _, row in cwe_cpe_type_data.head(15).iterrows():
        cwe_name = str(row['cwe_name'])[:33] + "..." if len(str(row['cwe_name'])) > 35 else str(row['cwe_name'])
        print(f"{row['cwe_id']:<12} {cwe_name:<35} {row['primary_cpe_type']:<15} "
              f"{row['cve_count']:<8,} {row['avg_hardware_cpes']:<8.1f} {row['avg_os_cpes']:<8.1f} {row['avg_app_cpes']:<8.1f}")

    return {
        'enhanced_cooccurrence': enhanced_cooccurrence,
        'enhanced_severity': enhanced_severity,
        'cwe_cpe_type_data': cwe_cpe_type_data,
        'enhanced_cwe_vendor': enhanced_cwe_vendor
    }

# Execute both enhanced analyses
print("Starting Enhanced CWE Analysis...")
cwe_comparison_results = analyze_cwe_comparison_enhanced()
cwe_cpe_relationship_results = analyze_cwe_cpe_relationships_enhanced()

print("\n\n=== ENHANCED ANALYSIS COMPLETE ===")
print("Generated visualizations:")
print("1. enhanced_cwe_cpe_analysis.png - Enhanced CWE-CPE relationship analysis")
print("2. enhanced_severity_statistics.png - Statistical measures for severity analysis")
print("\nKey Enhancements:")
print("✅ Actual CWE content comparison (not just counts)")
print("✅ Support for both CPE v2.2 (cpe:/) and v2.3 (cpe:2.3:) formats")
print("✅ CPE types extracted from actual CPE strings (h/o/a)")
print("✅ Statistical measures: Average, Median, Mode for CWE/CPE counts")
print("✅ Both CVSS v2 and v3 analysis")
print("✅ Enhanced vendor-CPE type analysis")
print("\nDetailed results have been displayed above for review and interpretation.")