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

// Config
const INPUT_FILE = '/Users/baris/Downloads/infosecl_platform (41).sql';
const OUTPUT_FILE = '/Users/baris/infoseclabs/infoseclabs/classified_alerts_update.sql';

// Heuristics
const RULES = [
    {
        code: 'CLOUD',
        keywords: ['AWS', 'Azure', 'GCP', 'S3', 'IAM', 'CloudTrail', 'EC2', 'Kubernetes', 'Docker', 'Container', 'Lambda', 'Bucket', 'CloudWatch', 'VPC']
    },
    {
        code: 'MAL',
        keywords: ['Malware', 'Trojan', 'Ransomware', 'Virus', 'Worm', 'Rootkit', 'Backdoor', 'Reverse Engineering', 'Binary', 'Dropper', 'Payload', 'C2', 'Command and Control']
    },
    {
        code: 'TI',
        keywords: ['Threat Intel', 'APT', 'IOC', 'Campaign', 'Threat Actor', 'OSINT', 'YARA', 'Intelligence', 'State-Sponsored']
    },
    {
        code: 'NDR',
        keywords: ['Network', 'Traffic', 'Packet', 'PCAP', 'Suricata', 'Zeek', 'Snort', 'Wireshark', 'TCP', 'UDP', 'Port Scan', 'DNS Exfiltration', 'Beaconing']
    },
    {
        code: 'EDR',
        keywords: ['Endpoint', 'Process', 'PowerShell', 'cmd.exe', 'Mimikatz', 'Registry', 'Service', 'Task Scheduler', 'WMI', 'CrowdStrike', 'SentinelOne', 'Carbon Black', 'Shell']
    },
    {
        code: 'DLP',
        keywords: ['Data Loss', 'DLP', 'Exfiltration', 'PII', 'Sensitive Data', 'Confidential', 'Leak', 'GDPR', 'HIPAA']
    },
    {
        code: 'IR',
        keywords: ['Incident Response', 'Forensic', 'Timeline', 'Artifact', 'Memory Dump', 'Disk Image']
    },
    {
        code: 'SIEM',
        keywords: ['SIEM', 'Splunk', 'Log', 'Event Audit', 'Correlation', 'Login Failure', 'Brute Force', 'Authentication']
    }
];

async function processFile() {
    const fileStream = fs.createReadStream(INPUT_FILE);
    const rl = readline.createInterface({
        input: fileStream,
        crlfDelay: Infinity
    });

    const updates = [];
    let processedCount = 0;
    let classifiedCount = 0;
    const stats = {};
    let inAlertsBlock = false;

    console.log("Processing SQL dump...");

    for await (const line of rl) {
        const trimmed = line.trim();

        // Detect start of Alerts INSERT block
        if (line.includes('INSERT INTO `alerts`')) {
            inAlertsBlock = true;
            console.log("Found INSERT INTO `alerts` block.");
            // If the start line also has values, handle it (though usually it's "VALUES" then newlines)
            if (line.includes('VALUES') && (line.endsWith(';') || line.endsWith('),'))) {
                // Single line or start of multi-line on same line?
                // Let's rely on the heuristic: If it has values, process.
                let content = line.substring(line.indexOf('VALUES') + 6).trim();
                processRows(content, updates, stats);
                if (line.endsWith(';')) inAlertsBlock = false;
            }
            continue;
        }

        if (inAlertsBlock) {
             // Each line is likely: `(1, ...),` or `(1, ...);`
             if (trimmed.startsWith('(')) {
                 processRows(trimmed, updates, stats);
             } else if (trimmed === ');' || trimmed === ';') {
                 inAlertsBlock = false;
                 console.log("End of INSERT INTO `alerts` block.");
             }
             
             if (trimmed.endsWith(';')) inAlertsBlock = false;
        }
    }
    
    // Process Rows Wrapper
    function processRows(content, updates, stats) {
        // Remove trailing comma or semicolon
        let cleanContent = content;
        if (cleanContent.endsWith(',')) cleanContent = cleanContent.slice(0, -1);
        if (cleanContent.endsWith(';')) cleanContent = cleanContent.slice(0, -1);
        
        // Split by "), (" ONLY if multiple on one line (rare but possible)
        // Usually dump format is one tuple per line.
        // Assuming line contains one or more full tuples.
        
        // Simple regex-based split is risky but let's try.
        // Since we are processing line-by-line, `content` is likely just `(1, ...)`
        
        // Parse the row
        const row = cleanContent;
        if (row.startsWith('(')) {
            const inner = row.substring(1, row.length - 1); // remove outer parens
            const cols = parseSQLRow(inner); // Use our parser on the inner content
            
            // 0: id, 1: title, ..., 15: path_code
             if (cols.length > 15) {
                const id = cols[0];
                const title = cols[1];
                const source = cols[3];
                const details = cols[4];
                const alertType = cols[5];
                let currentPath = cols[15];
                
                // Cleanup NULL
                if (currentPath === 'NULL' || currentPath === "'NULL'") currentPath = null;
                if (currentPath && currentPath.startsWith("'")) currentPath = currentPath.slice(1, -1);
                
                processedCount++;

                if (!currentPath || currentPath === 'NULL' || currentPath.trim() === '') {
                    // Classify
                    const newPath = classify(title + ' ' + source + ' ' + details + ' ' + alertType);
                    if (newPath) {
                        updates.push(`UPDATE alerts SET path_code = '${newPath}' WHERE id = ${id};`);
                        classifiedCount++;
                        stats[newPath] = (stats[newPath] || 0) + 1;
                    } else {
                         // Default to SIEM if nothing matches, or leave NULL?
                         // Let's force everything to at least 'SIEM' if it matches basic logs?
                         // Or try 'CORE' (Unassigned but valid)?
                         // If still null, count as unclassified.
                    }
                } else {
                    stats[currentPath] = (stats[currentPath] || 0) + 1;
                }
            }
        }
    }

    // Write output
    fs.writeFileSync(OUTPUT_FILE, updates.join('\n'));
    
    console.log(`\nProcessing Complete.`);
    console.log(`Total Alerts Processed: ${processedCount}`);
    console.log(`Newly Classified: ${classifiedCount}`);
    console.log(`\n--- Distribution After Updates (Projected) ---`);
    console.log(stats);
    console.log(`\nSQL Script generated at: ${OUTPUT_FILE}`);
}

function classify(text) {
    if (!text) return null;
    const t = text.toUpperCase();
    for (const rule of RULES) {
        for (const keyword of rule.keywords) {
            if (t.includes(keyword.toUpperCase())) {
                return rule.code;
            }
        }
    }
    return null; 
}

// Simple SQL Value Parser
function parseSQLRow(rowStr) {
    const result = [];
    let current = '';
    let inQuote = false;
    let escape = false;
    
    for (let i = 0; i < rowStr.length; i++) {
        const char = rowStr[i];
        
        if (escape) {
            current += char;
            escape = false;
            continue;
        }
        
        if (char === '\\') {
            escape = true;
            // current += char; // Do not include backslash in value unless needed? 
            // Actually, keep it simple. If valid SQL string, backslash escapes next char.
            // But we want the raw string content for keyword matching.
            continue;
        }
        
        if (char === "'" && !escape) {
            inQuote = !inQuote;
            continue;
        }
        
        if (char === ',' && !inQuote) {
            result.push(current.trim());
            current = '';
            continue;
        }
        
        current += char;
    }
    result.push(current.trim());
    return result;
}

processFile().catch(console.error);
