const fs = require('fs');
const readline = require('readline');

const filePath = '/Users/baris/Downloads/infosecl_platform (42).sql';

if (!fs.existsSync(filePath)) {
    console.error(`File not found: ${filePath}`);
    process.exit(1);
}

const fileStream = fs.createReadStream(filePath);
const rl = readline.createInterface({ input: fileStream, crlfDelay: Infinity });

const stats = { total: 0, classified: 0, unclassified: 0, byPath: {} };
let processingAlerts = false;

rl.on('line', (line) => {
    line = line.trim();
    
    // Check for INSERT statement
    if (line.toUpperCase().startsWith('INSERT INTO `')) {
        if (line.toUpperCase().startsWith('INSERT INTO `ALERTS`')) {
            processingAlerts = true;
        } else {
            processingAlerts = false;
        }
    }
    
    // If not processing alerts table, skip
    if (!processingAlerts) return;

    if (!line.startsWith('(')) return;
    
    // Ensure it looks like a value row
    if (!line.match(/\)(,|;)$/)) return;

    // Remove starting '(' and ending '),' or ');'
    let content = line.substring(1).replace(/\);?$/, '').replace(/\),$/, '');
    
    const parts = [];
    let current = '';
    let inQuote = false;
    
    for (let i = 0; i < content.length; i++) {
        const char = content[i];
        
        // Handle Escape Character
        if (char === '\\') {
            current += char;
            if (i + 1 < content.length) {
                current += content[i+1];
                i++; // Skip next character
            }
            continue;
        }
        
        if (char === "'") {
             inQuote = !inQuote;
             current += char;
        } else if (char === ',' && !inQuote) {
            parts.push(current.trim());
            current = '';
        } else {
            current += char;
        }
    }
    parts.push(current.trim());
    
    // Structure: id, title, severity, source, details, alert_type, mitre, real, status, ass, raw, create, update, playbook, diff, PATH_CODE (16)
    
    if (parts.length >= 16) {
        let pathCode = parts[15];
        
        // Normalize
        let code = pathCode;
        if (code && code.startsWith("'")) {
            code = code.slice(1, -1); // Remove quotes
            code = code.replace(/\\'/g, "'").replace(/\\\\/g, "\\"); 
        }
        
        stats.total++;
        if (code === 'NULL' || code === '' || code === undefined || code === 'null') {
            stats.unclassified++;
        } else {
            stats.classified++;
            stats.byPath[code] = (stats.byPath[code] || 0) + 1;
        }
    }
});

rl.on('close', () => {
    console.log(JSON.stringify(stats, null, 2));
});
