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

const filePath = '/Users/baris/Downloads/infosecl_platform (42).sql';
const outputPath = '/Users/baris/infoseclabs/infoseclabs/scripts/fix_unclassified.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 });

console.log(`Generating fixes to ${outputPath}...`);
const outStream = fs.createWriteStream(outputPath);

let processingAlerts = false;

// Keyword Mappings
const MAPPINGS = [
    { keys: ['RAT', 'Ransom', 'Malware', 'Trojan', 'Virus', 'Backdoor'], code: 'MAL' },
    { keys: ['Lateral', 'Network', 'Traffic', 'C2', 'Command and Control'], code: 'NDR' },
    { keys: ['Funds', 'Launder', 'Casino', 'Dark Web', 'Carding', 'Fraud'], code: 'TI' },
    { keys: ['Phishing', 'Email', 'Spam'], code: 'SIEM' },
    { keys: ['Admin', 'Unauthorized', 'Access', 'Login', 'Brute Force'], code: 'EDR' },
    { keys: ['Cloud', 'AWS', 'Azure', 'S3'], code: 'CLOUD' }
];

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 (11), update, playbook, diff, PATH_CODE (15)
    
    if (parts.length >= 16) {
        let pathCode = parts[15];
        
        // Normalize
        let code = pathCode;
        if (code && code.startsWith("'")) {
            code = code.slice(1, -1).trim(); 
            code = code.replace(/\\'/g, "'").replace(/\\\\/g, "\\"); 
        }
        
        if (code === 'NULL' || code === '' || code === undefined || code === 'null') {
            // Unclassified
            const id = parts[0];
            let title = parts[1];
            if (title.startsWith("'")) title = title.slice(1, -1);
            
            // Determine Category
            let bestCode = 'SIEM'; // Default
            
            for (const map of MAPPINGS) {
                for (const key of map.keys) {
                    if (title.toLowerCase().includes(key.toLowerCase())) {
                        bestCode = map.code;
                        break;
                    }
                }
                if (bestCode !== 'SIEM') break;
            }
            
            outStream.write(`UPDATE \`alerts\` SET \`path_code\` = '${bestCode}' WHERE \`id\` = ${id};\n`);
            console.log(`Fixing ID ${id}: ${title.substring(0, 30)}... -> ${bestCode}`);
        }
    }
});

rl.on('close', () => {
    outStream.end();
    console.log("Done.");
});
