const db = require('../db');
const badgeService = require('../services/badgeService');
const { generatePlaybook } = require('../utils/playbookGenerator'); // Import Generator
const xpService = require('../utils/xpService'); // Import XP Service for Streaks
const { advanceOperationProgress } = require('./operationController');
const assignmentService = require('../services/assignmentService');
const { broadcastPushNotification } = require('../utils/notificationService');
// const { io } = require('../server'); // Circular dependency removal. Use req.io instead.

/**
 * Auto-detect specialization path based on alert content
 * Analyzes source, title, details, and raw_log to determine the best path
 */
function detectPathFromContent(alertData) {
    const searchText = [
        alertData.title || '',
        alertData.source || '',
        alertData.details || '',
        alertData.alert_type || '',
        typeof alertData.raw_log === 'object' ? JSON.stringify(alertData.raw_log) : (alertData.raw_log || '')
    ].join(' ').toLowerCase();

    // Path detection keywords
    const pathKeywords = {
        EDR: ['endpoint', 'process', 'execution', 'powershell', 'cmd.exe', 'registry', 'dll', 'crowdstrike', 'sentinelone', 'carbon black', 'memory', 'injection', 'lsass', 'mimikatz', 'hash', 'executable', 'service', 'exe'],
        NDR: ['network', 'traffic', 'packet', 'port', 'connection', 'tcp', 'udp', 'dns', 'c2', 'beaconing', 'lateral', 'scan', 'zeek', 'suricata', 'snort', 'darktrace', 'extrahop', 'netflow'],
        SIEM: ['log', 'event', 'correlation', 'splunk', 'elastic', 'sentinel', 'wazuh', 'syslog', 'authentication', 'login', 'failed', 'audit', 'windows event'],
        TI: ['ioc', 'indicator', 'threat', 'intel', 'apt', 'campaign', 'attribution', 'ttps', 'mitre', 'malware family', 'actor', 'adversary'],
        IR: ['incident', 'breach', 'compromise', 'ransomware', 'containment', 'eradication', 'recovery', 'forensic', 'timeline', 'root cause'],
        MAL: ['malware', 'virus', 'trojan', 'worm', 'backdoor', 'dropper', 'payload', 'obfuscated', 'packed', 'sandbox', 'dynamic analysis', 'static analysis', 'reverse', 'disassembly'],
        DLP: ['data', 'exfiltration', 'leak', 'sensitive', 'pii', 'credit card', 'ssn', 'confidential', 'classification', 'dlp', 'upload', 'usb'],
        CLOUD: ['aws', 'azure', 'gcp', 'cloud', 's3', 'ec2', 'lambda', 'iam', 'kubernetes', 'container', 'docker', 'k8s', 'terraform', 'cloudtrail']
    };

    // Score each path
    let maxScore = 0;
    let bestPath = 'CORE'; // Default to CORE if no strong match

    for (const [path, keywords] of Object.entries(pathKeywords)) {
        let score = 0;
        for (const keyword of keywords) {
            if (searchText.includes(keyword)) {
                score++;
            }
        }
        if (score > maxScore) {
            maxScore = score;
            bestPath = path;
        }
    }

    // Only return detected path if we have at least 2 keyword matches
    return maxScore >= 2 ? bestPath : 'CORE';
}


const getAlerts = async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 10;
        const offset = (page - 1) * limit;
        const isGenerated = req.query.generated === 'true';

        let whereClause = `(a.source != 'Academy' OR a.source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)
            AND a.organization_id IS NULL`;
        
        if (isGenerated) {
            whereClause += ` AND a.is_ai_generated = 1`;
        }

        // Filter out future-dated alerts (scheduled alerts)
        whereClause += ` AND (a.created_at <= NOW() OR a.created_at IS NULL)`;

        // Filter by Specialization Path (if user is logged in)
        if (req.user && req.user.id) {
            try {
                const pathResult = await db.query('SELECT primary_path, secondary_path FROM users WHERE id = ?', [req.user.id]);
                const userPaths = pathResult.rows[0];
                
                if (userPaths && userPaths.primary_path) {
                    let pathCondition = `(a.path_code IS NULL OR a.path_code = 'CORE' OR a.path_code = '${userPaths.primary_path}'`;
                    if (userPaths.secondary_path) {
                        pathCondition += ` OR a.path_code = '${userPaths.secondary_path}'`;
                    }
                    pathCondition += ')';
                    whereClause += ` AND ${pathCondition}`;
                }
            } catch (err) {
                console.error('Error fetching user paths for alert filtering:', err);
                // Continue without path filtering on error
            }
        }

        // Get total count of alerts (excluding Academy and operation-linked alerts)
        const countResult = await db.query(`
            SELECT COUNT(*) as total FROM alerts a
            WHERE ${whereClause}
        `);
        const total = parseInt(countResult.rows[0].total);

        // Fetch paginated alerts with investigation info
        const result = await db.query(`
            SELECT a.*, 
                   (SELECT COUNT(*) FROM investigations i WHERE i.alert_id = a.id) as investigation_count,
                   i.id as investigation_id,
                   i.is_reported,
                   i.status as investigation_status,
                   i.grade,
                   i.user_id as assigned_to_id,
                   FALSE as is_operation_alert
            FROM alerts a 
            LEFT JOIN investigations i ON a.id = i.alert_id AND i.user_id = ?
            WHERE ${whereClause}
            ORDER BY a.created_at DESC
            LIMIT ${limit} OFFSET ${offset}
        `, [req.user ? req.user.id : 0]);

        console.log(`[getAlerts] User ${req.user?.id || 0} requested alerts (generated=${isGenerated}). Total: ${total}. Results: ${result.rows.length}`);

        res.json({
            alerts: result.rows,
            total,
            page,
            limit,
            totalPages: Math.ceil(total / limit)
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getMyAlerts = async (req, res) => {
    try {
        const userId = req.user.id;
        const result = await db.query(`
            SELECT a.*, 
                   i.id as investigation_id,
                   i.status as investigation_status, 
                   i.grade, 
                   i.feedback,
                   i.is_reported,
                   i.report_reason
            FROM alerts a
            JOIN investigations i ON a.id = i.alert_id
            WHERE i.user_id = ?
            AND (a.created_at <= NOW() OR a.created_at IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)
            ORDER BY i.updated_at DESC
        `, [userId]);
        res.json(result.rows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

// New: Get Closed / Graded alerts for the logged-in user
const getClosedAlerts = async (req, res) => {
    try {
        const userId = req.user.id;
        const isAdmin = req.user.role === 'admin' || req.user.role === 'alert-admin';

        let query = `
            SELECT a.*, 
                   i.id as investigation_id,
                   i.status as investigation_status, 
                   i.grade, 
                   i.feedback,
                   i.is_reported,
                   i.report_reason,
                   i.executive_summary,
                   i.updated_at as closed_at,
                   u.name as investigator_name
            FROM alerts a
            JOIN investigations i ON a.id = i.alert_id
            JOIN users u ON i.user_id = u.id
            WHERE i.status IN ('graded', 'closed', 'completed')
        `;

        const params = [];
        if (!isAdmin) {
            query += ' AND i.user_id = ?';
            params.push(userId);
        }
 
        query += ' AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)';

        query += ' ORDER BY i.updated_at DESC';

        const result = await db.query(query, params);
        res.json(result.rows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getAllAssignedAlerts = async (req, res) => {
    try {
        const result = await db.query(`
            SELECT a.*, 
                   i.id as investigation_id,
                   i.status as investigation_status,
                   i.user_id as assigned_to_id,
                   u.name as investigator_name,
                   i.created_at as assigned_at
            FROM alerts a
            JOIN investigations i ON a.id = i.alert_id
            JOIN users u ON i.user_id = u.id
            WHERE i.status = 'investigating'
            ORDER BY i.created_at DESC
        `);
        res.json(result.rows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getAlertById = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = req.user ? req.user.id : 0;
        
        const result = await db.query(`
            SELECT a.*, 
                   i.id as investigation_id,
                   i.user_id as investigation_user_id, 
                   u.name as user_name,
                   u.email as user_email,
                   i.status as investigation_status,
                   i.grade,
                   i.feedback,
                   i.executive_summary,
                   i.is_reported,
                   i.report_reason
            FROM alerts a 
            LEFT JOIN investigations i ON a.id = i.alert_id AND i.user_id = ?
            LEFT JOIN users u ON i.user_id = u.id
            WHERE a.id = ?
            AND (a.created_at <= NOW() OR a.created_at IS NULL)
        `, [userId, id]);

        console.log('Query Result Row Count:', result.rows.length);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Alert not found' });
        }

        const alert = result.rows[0];

        // --- SECURITY CHECK: Organization Isolation ---
        if (alert.organization_id) {
            // If alert belongs to an org, user MUST be a member
            if (!req.user || !req.user.id) {
                return res.status(403).json({ message: 'Access denied: Authentication required for private alerts' });
            }
            
            // Check membership
            const memberCheck = await db.query( 
                'SELECT 1 FROM organization_members WHERE organization_id = ? AND user_id = ?', 
                [alert.organization_id, req.user.id]
            );
            
            if (memberCheck.rows.length === 0) {
                // Not a member? Access Denied.
                // We return 404 to avoid leaking existence, or 403 if we don't care. 
                // 403 is clearer for legit users who are just in wrong org.
                return res.status(403).json({ message: 'Access denied: You are not a member of the organization that owns this alert.' });
            }
        }
        
        // --- MULTI-USER ISOLATION FIX ---
        // Prioritize user-specific investigation status/assignment over global 'alerts' table columns.
        // If an investigation exists for this user, use its status and mark as assigned to them.
        if (alert.investigation_status) {
            alert.status = alert.investigation_status;
            alert.assigned_to = alert.investigation_user_id;
        } else {
            // If no investigation exists, validation logic determines if it appears 'New' or 'Assigned' (legacy)
            // But for Missions, it should always look 'New' if the user hasn't started it yet.
            // We will refine this after checking if it's an operation alert below.
        }

        // Parse JSON fields if needed
        try {
            if (alert.executive_summary && typeof alert.executive_summary === 'string') {
                alert.executive_summary = JSON.parse(alert.executive_summary);
            }
        } catch (e) {
            console.error("Error parsing executive_summary", e);
        }

        try {
            if (alert.playbook_solution && typeof alert.playbook_solution === 'string') {
                alert.playbook_solution = JSON.parse(alert.playbook_solution);
            }
        } catch (e) {
            console.error("Error parsing playbook_solution", e);
        }

        // Check if this alert is part of an operation (for return URL handling)
        const opCheck = await db.query(`
            SELECT oa.operation_id, oa.sequence_order, o.title as operation_title,
                   (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = oa.operation_id) as total_alerts
            FROM operation_alerts oa
            JOIN operations o ON oa.operation_id = o.id
            WHERE oa.alert_id = ?
        `, [id]);

        if (opCheck.rows.length > 0) {
            alert.is_operation_alert = true;
            alert.operation_id = opCheck.rows[0].operation_id;
            alert.operation_title = opCheck.rows[0].operation_title;
            alert.operation_sequence = opCheck.rows[0].sequence_order;
            alert.operation_total_alerts = opCheck.rows[0].total_alerts;
            // Force playbook mode for operation alerts
            alert.force_playbook_mode = true;

            // --- MISSION ISOLATION ---
            // If it's a mission alert and the user has NO investigation data (investigation_status is null),
            // FORCE the status to 'New' and assigned_to to NULL (or user if we want to auto-assign in UI).
            // This ignores the global 'Closed' status set by other users.
            if (!alert.investigation_status) {
                alert.status = 'New';
                alert.assigned_to = null; // Visually unassigned until they interact/start
            }
        }

        res.json(alert);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const createAlert = async (req, res) => {
    const { title, severity, source, description, source_ip, destination_ip, file_hash, dns_request, alert_type, mitre_technique, real_world_example, raw_log } = req.body;

    if (!title || !severity) {
        return res.status(400).json({ message: 'Please provide title and severity' });
    }

    try {
        let fullDetails = description || '';
        if (source_ip) fullDetails += `\n\nSource IP: ${source_ip}`;
        if (destination_ip) fullDetails += `\nDestination IP: ${destination_ip}`;
        if (file_hash) fullDetails += `\nFile Hash: ${file_hash}`;
        if (dns_request) fullDetails += `\nDNS/URL: ${dns_request}`;

        // Auto-detect Path Code if not provided
        let pathCode = req.body.path_code || null;
        if (!pathCode) {
            pathCode = detectPathFromContent({
                title,
                source,
                details: fullDetails,
                alert_type,
                raw_log
            });
        }

        // Create alert
        // Note: Added path_code to INSERT
        const result = await db.query(
            'INSERT INTO alerts (title, severity, source, details, alert_type, mitre_technique, real_world_example, raw_log, playbook_solution, sector_code, path_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [
                title,
                severity,
                source || 'Manual',
                fullDetails,
                alert_type || null,
                mitre_technique || null,
                real_world_example || null,
                raw_log || null,
                req.body.playbook_solution ? JSON.stringify(req.body.playbook_solution) : JSON.stringify(generatePlaybook(alert_type || 'Generic', severity, title, raw_log, fullDetails)),
                req.body.sector_code || null,
                pathCode
            ]
        );

        // Fetch the newly created alert
        const newAlertResult = await db.query('SELECT * FROM alerts WHERE id = ?', [result.insertId]);
        const newAlert = newAlertResult.rows[0];

        if (req.io) {
            req.io.emit('new-alert', newAlert);
        }

        // Send Push Notification for new manual alert (respect sendPush toggle)
        if (req.body.sendPush !== false) {
            broadcastPushNotification(
                'New Incident Detected 🚨',
                newAlert.title,
                { screen: 'AlertDetails', alertId: newAlert.id }
            ).catch(e => console.error('Push Broadcast Error:', e));
        }

        res.status(201).json(newAlert);
    } catch (error) {
        console.error("Create Alert Error:", error);
        res.status(500).json({ message: `Server error creating alert: ${error.message || error.sqlMessage || 'Unknown error'}` });
    }
};



const generateAlerts = async (req, res) => {
    const { count } = req.body;
    const numAlerts = parseInt(count) || 2;

    let settings = {};
    try {
        // Fetch AI settings for provider and key
        const settingsResult = await db.query(
            "SELECT `key`, value FROM system_settings WHERE category = 'ai_integration'"
        );
        settingsResult.rows.forEach(row => {
            try {
                settings[row.key] = JSON.parse(row.value);
            } catch (e) {
                settings[row.key] = row.value;
            }
        });
    } catch (dbError) {
        console.error("Error fetching settings, using defaults:", dbError);
        // Continue with empty settings or defaults if DB error occurs
    }

    // Strip quotes if they exist (common issue with some values)
    if (settings.ai_api_key && typeof settings.ai_api_key === 'string') {
        settings.ai_api_key = settings.ai_api_key.replace(/^"|"$/g, '').trim();
    }

    try {
        const { generateAlertContent } = require('../utils/aiService');
        // Pass the new structured parameters - use v2 format for consistent playbooks
        const generatedAlerts = await generateAlertContent(
            numAlerts,
            settings.ai_api_key,
            settings.ai_provider || 'openai',
            {
                severityCounts: req.body.severityCounts,
                verdictDistribution: req.body.verdictDistribution,
                includeRawLogs: req.body.includeRawLogs,
                sources: req.body.sources,
                strategy: req.body.strategy,
                difficulty: req.body.difficulty,
                sector_code: req.body.sector_code, // Pass sector code for industry-specific context
                campaign_pack: req.body.campaign_pack, // Pass campaign pack selection
                scenario_context: req.body.scenario_context, // Custom scenario prompt
                useV2: true // Enable v2 playbook format with AI-generated consistent playbooks
            }
        );

        const createdAlerts = [];
        const distributionHours = parseFloat(req.body.distributionHours) || 0;

        for (const alertData of generatedAlerts) {
            try {
                // Calculate created_at
                let createdAt = new Date();
                if (distributionHours > 0) {
                    // Random delay between 0 and distributionHours (DISTRIBUTED OVER THE PAST)
                    const randomDelayMs = Math.random() * distributionHours * 60 * 60 * 1000;
                    createdAt = new Date(createdAt.getTime() - randomDelayMs);
                }

                // Determine path_code (auto-detect or manual)
                let pathCode = req.body.path_code || null;
                const minLevel = parseInt(req.body.min_level) || 1;
                
                if (req.body.autoAssignPath || pathCode === 'auto') {
                    // AI auto-detect based on alert content
                    pathCode = detectPathFromContent(alertData);
                }

                const finalSectorCode = (req.body.sector_code === 'auto' || !req.body.sector_code) && alertData.sector_code ? alertData.sector_code : (req.body.sector_code || null);

                const result = await db.query(
                    'INSERT INTO alerts (title, severity, status, source, details, alert_type, mitre_technique, real_world_example, raw_log, playbook_solution, difficulty, created_at, is_ai_generated, min_level, path_code, sector_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?)',
                    [
                        alertData.title || 'Untitled Alert',
                        alertData.severity || 'Medium',
                        'New',
                        alertData.source,
                        alertData.details,
                        alertData.alert_type,
                        alertData.mitre_technique,
                        alertData.real_world_example || 0,
                        typeof alertData.raw_log === 'object' ? JSON.stringify(alertData.raw_log) : (alertData.raw_log || null),
                        alertData.playbook_solution ? JSON.stringify(alertData.playbook_solution) : JSON.stringify(generatePlaybook(alertData.alert_type, alertData.severity, alertData.title, alertData.raw_log, alertData.details)),
                        req.body.difficulty || 'intermediate', // Store difficulty level
                        createdAt,
                        minLevel,
                        pathCode,
                        finalSectorCode
                    ]
                );

                // Fetch the simplified object (not full SELECT *) to add to list
                createdAlerts.push({
                    id: Number(result.insertId),
                    ...alertData,
                    difficulty: req.body.difficulty || 'intermediate', // Include difficulty in response
                    min_level: minLevel,
                    path_code: pathCode,
                    sector_code: finalSectorCode,
                    created_at: createdAt
                });
            } catch (err) {
                console.error('Error inserting individual alert:', err);
            }
        }

        if (req.io) {
            req.io.emit('notification', { message: `${createdAlerts.length} new alerts created!` });
        }

        // Broadcast notification for the latest generated alert if any (respect sendPush toggle)
        if (createdAlerts.length > 0 && req.body.sendPush !== false) {
            const firstAlert = createdAlerts[0];
            broadcastPushNotification(
                'New Simulation Phase Injected 🛰️',
                `${createdAlerts.length} new missions have been deployed to your dashboard.`,
                { screen: 'AlertDetails', alertId: firstAlert.id }
            ).catch(e => console.error('Push Broadcast Error:', e));
        }

        res.status(201).json(createdAlerts);
    } catch (error) {
        console.error("Generate Alerts Error:", error);
        res.status(500).json({ message: error.message || 'Server error generating alerts' });
    }
};






const deleteAlert = async (req, res) => {
    const { id } = req.params;
    
    try {
        // Delete associated investigations first
        await db.query('DELETE FROM investigations WHERE alert_id = ?', [id]);
        
        // Then delete the alert
        const result = await db.query('DELETE FROM alerts WHERE id = ?', [id]);

        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Alert not found' });
        }

        res.json({ message: 'Alert deleted successfully' });
    } catch (error) {
        console.error('Error deleting alert:', error);
        res.status(500).json({ message: 'Server error deleting alert' });
    }
};

const deleteAllAlerts = async (req, res) => {
    try {
        await db.query('DELETE FROM investigations');
        await db.query('DELETE FROM alerts');
        res.json({ message: 'All alerts and investigations deleted successfully' });
    } catch (error) {
        console.error('Error deleting all alerts:', error);
        res.status(500).json({ message: 'Server error deleting all alerts' });
    }
};

const updateAlert = async (req, res) => {
    const { id } = req.params;
    const { title, severity, status, source, details, raw_log, min_level, path_code, sector_code } = req.body;
    try {
        const result = await db.query(
            'UPDATE alerts SET title=?, severity=?, status=?, source=?, details=?, raw_log=?, min_level=?, path_code=?, sector_code=? WHERE id=?',
            [
                title ?? null,
                severity ?? null,
                status ?? null,
                source ?? null,
                details ?? null,
                typeof raw_log === 'object' ? JSON.stringify(raw_log) : (raw_log ?? null),
                min_level ?? 1,
                path_code ?? null,
                sector_code ?? null,
                id
            ]
        );
        if (result.rowCount === 0) {
            return res.status(404).json({ message: 'Alert not found' });
        }
        const updated = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
        res.json(updated.rows[0]);
    } catch (error) {
        console.error('Update Alert Error:', error);
        res.status(500).json({ message: `Server error updating alert: ${error.message}` });
    }
};

const { sendAlertAssignedEmail, sendInvestigationGradedEmail, sendLimitReachedEmail, sendReportNotificationEmail } = require('../utils/emailService');

// ... (existing code)

const assignAlert = async (req, res) => {
    const rawId = req.params.id;
    const id = parseInt(rawId); // Force Integer

    let { userId } = req.body;

    // Default to current user if not specified (Assign to Me)
    if (!userId) {
        userId = req.user.id;
    }

    if (isNaN(id)) {
        return res.status(400).json({ message: `Invalid Alert ID: ${rawId}` });
    }

    console.log(`[assignAlert] Attempting to assign Alert ID: ${id} (Raw: ${rawId}) to User ID: ${userId}`);

    try {
        // Check if alert exists and is unassigned
        const alertResult = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
        if (alertResult.rows.length === 0) {
            console.warn(`[assignAlert] Alert ID ${id} not found in DB`);
            return res.status(404).json({ message: 'Alert not found' });
        }
        const alert = alertResult.rows[0];
        const status = alert.status ? alert.status.toLowerCase() : '';

        // Note: We used to block assignment if status was not 'new' or 'investigating'.
        // However, in a multi-user environment, one user's 'resolved' status shouldn't block 
        // another user (especially Pro users) from assigning and playing the mission themselves.
        // User-specific assignment is already gated by the 'investigations' table check below.

        // If the alert is 'new', change its status to 'investigating'
        if (status === 'new') {
            await db.query("UPDATE alerts SET status = 'investigating' WHERE id = ?", [id]);
        }

        // Check if already assigned to this user
        const existingInvestigation = await db.query('SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?', [id, userId]);
        if (existingInvestigation.rows.length > 0) {
            return res.status(400).json({ message: 'You have already assigned this alert to yourself.' });
        }

        // Check user's plan and usage
        const userResult = await db.query('SELECT * FROM users WHERE id = ?', [userId]);
        const user = userResult.rows[0];

        // Check limit using shared usage service
        const usageService = require('../utils/usageService');
        const usageCheck = await usageService.enforceFreeTierLimit(userId, user);
        
        if (!usageCheck.allowed) {
            return res.status(403).json({
                code: 'LIMIT_REACHED',
                message: usageCheck.message
            });
        }

        // Assign alert
        await db.query(
            'UPDATE alerts SET status = ?, assigned_to = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
            ['investigating', userId, id]
        );
        const updatedAlertResult = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
        const updatedAlert = updatedAlertResult.rows[0];

        // Create investigation record
        const delayMinutes = Math.floor(Math.random() * (10 - 5 + 1) + 5);
        const scheduledTime = new Date(Date.now() + delayMinutes * 60000);

        console.log(`[assignAlert] Inserting investigation for Alert ${id}, User ${userId}`);
        
        await db.query(
            'INSERT INTO investigations (alert_id, user_id, status, ai_evaluation_scheduled_at, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())',
            [id, userId, 'investigating', scheduledTime]
        );

        // Notify via Socket.IO
        if (req.io) {
            req.io.emit('alert-updated', updatedAlert);
            req.io.emit('investigation-started', { alertId: id, userId });
        }

        // Send email notification (check preferences)
        try {
            // Check email preferences
            let prefs = user.email_preferences || {};
            if (typeof prefs === 'string') {
                try { prefs = JSON.parse(prefs); } catch (e) { prefs = {}; }
            }
            if (prefs.alert_assigned !== false) {
                await sendAlertAssignedEmail(user.email, user.name, alert.title, userId);
            } else {
                console.log(`Skipping alert assignment email for ${user.email} (opted out)`);
            }
        } catch (emailErr) {
            console.error('Failed to send assignment email:', emailErr);
        }

        res.json(updatedAlert);
    } catch (error) {
        console.error('Error assigning alert:', error);
        res.status(500).json({ 
            message: `Server error: ${error.message}`, 
            error: error.toString(),
            debug: { alertId: id, userId: userId, rawId: rawId }
        });
    }
};

// Duplicate getMyAlerts removed.


const submitSummary = async (req, res) => {
    const { id } = req.params; // Alert ID
    const userId = req.user.id;
    const { summary } = req.body; // JSON object: { verdict, analysis, steps, conclusion }

    try {
        // Check if investigation exists
        const result = await db.query('SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?', [id, userId]);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Investigation not found' });
        }

        const investigation = result.rows[0];

        // 1. Enforce 5-minute Edit Window
        // If status is 'graded', definitely locked.
        // If status is 'submitted', check time.
        if (investigation.status === 'graded') {
            return res.status(403).json({ message: 'Investigation is already graded and cannot be edited.' });
        }

        if (investigation.status === 'submitted') {
            const lastUpdate = new Date(investigation.updated_at);
            const now = new Date();
            const diffMinutes = (now - lastUpdate) / 1000 / 60;

            if (diffMinutes > 5) {
                return res.status(403).json({ message: 'Edit window expired (5 minutes after submission). Your report is locked for grading.' });
            }
        }

        // 2. Update Investigation (Save Draft/Submission)
        // We set status to 'submitted' so the auto-grader picks it up after 5 minutes of inactivity.
        await db.query(
            `UPDATE investigations 
             SET executive_summary = ?, 
                 status = 'submitted', 
                 updated_at = NOW(),
                 ai_evaluation_scheduled_at = DATE_ADD(NOW(), INTERVAL FLOOR(5 + (RAND() * 5)) MINUTE)
             WHERE alert_id = ? AND user_id = ?`,
            [
                JSON.stringify(summary),
                id,
                userId
            ]
        );

        const updatedResult = await db.query('SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?', [id, userId]);
        res.json(updatedResult.rows[0]);

    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};



const completeInvestigation = async (req, res) => {
    const { id } = req.params; // Alert ID
    const userId = req.user.id;
    const { score, verdict, executive_summary, artifacts, analysis_answers } = req.body;

    // Validation: Require verdict and summary for submission
    if (!verdict || !executive_summary) {
        if (score === undefined) {
            console.log("Missing verdict/summary and no score provided.");
        }
    }

    try {
        const connection = await db.pool.getConnection();
        await connection.beginTransaction();

        try {
            // 1. Verify Investigation Ownership
            const [invRows] = await connection.query(
                'SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?',
                [id, userId]
            );

            if (invRows.length === 0) {
                await connection.rollback();
                return res.status(404).json({ message: 'Investigation not found or not assigned to you.' });
            }
            const investigation = invRows[0];

            // 2. Update Investigation with summary
            const summaryObj = {
                verdict,
                executive_summary,
                artifacts,
                analysis_answers,
                submitted_at: new Date().toISOString()
            };

            // Set status to 'submitted' initially. 
            // The AI service will update it to 'graded' when complete.
            await connection.query(
                `UPDATE investigations 
                 SET executive_summary = ?, 
                     status = 'submitted', 
                     updated_at = NOW()
                 WHERE id = ?`,
                [JSON.stringify(summaryObj), investigation.id]
            );

            await connection.commit();
            connection.release();

            // Award streak bonus for completing an investigation
            const streakResult = await xpService.updateStreak(userId);
            console.log(`[CompleteInvestigation] Streak update for user ${userId}:`, streakResult);

            // Check and award badges
            // Pass investigation.id to allow checking time-based and performance badges
            const newBadges = await xpService.checkAndAwardBadges(userId, streakResult.newStreakDays || 0, investigation.id);
            if (newBadges.length > 0) {
                console.log(`[CompleteInvestigation] New badges for user ${userId}:`, newBadges);
            }

            setTimeout(async () => {
                const { gradeInvestigation } = require('../utils/gradingService');
                
                // Fetch fresh records to be safe
                const invResult = await db.query('SELECT * FROM investigations WHERE id = ?', [investigation.id]);
                const alertResult = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
                
                if (invResult.rows.length > 0 && alertResult.rows.length > 0) {
                     await gradeInvestigation(invResult.rows[0], alertResult.rows[0]);
                }
            }, gradingDelay);

            // Return the updated alert immediately (grading happens in background)
            const result = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
            const updatedAlert = result.rows[0];

            if (req.io) {
                req.io.emit('alert-updated', updatedAlert);
            }

            res.json({ ...updatedAlert, message: 'Investigation submitted! Grading in progress...' });

        } catch (err) {
            await connection.rollback();
            connection.release();
            console.error(err);
            res.status(500).json({ message: 'Server error during completion.' });
        }

    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

/**
 * Complete Investigation V2 - For new guided playbook format
 * Uses objective grading instead of AI analysis
 */
const completeInvestigationV2 = async (req, res) => {
    const { id } = req.params; // Alert ID
    const userId = req.user.id;
    const { checkedArtifacts, selectedActions, verdict, conclusion } = req.body;

    // Validation
    if (!verdict) {
        return res.status(400).json({ message: 'Please provide a verdict.' });
    }

    let connection;
    try {
        connection = await db.pool.getConnection();
        await connection.beginTransaction();

        // 1. Verify Investigation Ownership
        const [invRows] = await connection.query(
            'SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?',
            [id, userId]
        );

        if (invRows.length === 0) {
            await connection.rollback();
            return res.status(404).json({ message: 'Investigation not found or not assigned to you.' });
        }
        const investigation = invRows[0];

        // 2. Get alert with playbook_solution
        const [alertRows] = await connection.query('SELECT * FROM alerts WHERE id = ?', [id]);
        if (alertRows.length === 0) {
            await connection.rollback();
            return res.status(404).json({ message: 'Alert not found.' });
        }
        const alert = alertRows[0];

        // 3. Grade using objective grading service
        const { gradeV2Investigation } = require('../utils/playbookGrader');

        const playbook = typeof alert.playbook_solution === 'string'
            ? JSON.parse(alert.playbook_solution)
            : alert.playbook_solution;

        if (!playbook) {
            await connection.rollback();
            console.error(`[completeInvestigationV2] Missing playbook for alert ${id}`);
            return res.status(500).json({ message: 'Playbook solution missing for this alert.' });
        }

        // Enrich artifacts with metadata if they are just IDs
        const enrichedArtifacts = (checkedArtifacts || []).map(artId => {
            const found = playbook.artifacts?.find(pa => pa.id === artId);
            if (found) {
                return {
                    id: found.id,
                    type: found.type,
                    value: found.value,
                    is_critical: found.is_critical
                };
            }
            return artId; // Fallback
        });

        const submission = {
            checkedArtifacts: enrichedArtifacts,
            selectedActions: selectedActions || [],
            verdict,
            conclusion: conclusion || ''
        };

        let gradingResult = gradeV2Investigation(submission, playbook);

        // False Positive Reality Check Overrides
        if (alert.is_false_positive) {
            if (verdict === 'true_positive') {
                // Critical Failure: Acted on a benign event
                gradingResult.grade -= 50; // Heavy penalty
                if (gradingResult.grade < 0) gradingResult.grade = 0;
                
                gradingResult.feedback = "🚨 CRITICAL FAILURE: You identified a normal, benign business activity as a malicious attack. Isolating hosts or blocking IPs on False Positives causes severe business disruption.";
                
                if (gradingResult.breakdown) {
                    gradingResult.breakdown.verdict_score = 0;
                }
            } else if (verdict === 'false_positive') {
                // They correctly identified it as benign. Did they take destructive action anyway?
                const destructiveActions = ['isolate_host', 'block_ip', 'reset_credentials', 'block_domain', 'block_hash', 'suspend_user'];
                const tookDestructive = selectedActions?.some(a => destructiveActions.includes(a));
                
                if (tookDestructive) {
                    gradingResult.grade -= 30;
                    if (gradingResult.grade < 0) gradingResult.grade = 0;
                    gradingResult.feedback = "⚠️ WARNING: You correctly identified this as a False Positive, but you still executed destructive incident response actions (like isolating a host). Do not disrupt business operations for benign events.";
                } else {
                    gradingResult.feedback = "✅ EXCELLENT: You correctly identified this as a benign False Positive (Alert Fatigue) and safely closed the ticket without disrupting the business.";
                    gradingResult.grade = Math.max(gradingResult.grade, 90); // Floor at 90 for finding an FP safely
                }
            }
        }

        // 4. Store submission and grade
        const summaryObj = {
            playbook_version: 2,
            checkedArtifacts,
            selectedActions,
            verdict,
            conclusion,
            submitted_at: new Date().toISOString()
        };

        await connection.query(
            `UPDATE investigations 
             SET executive_summary = ?, 
                 status = 'graded', 
                 grade = ?,
                 feedback = ?,
                 ai_summary = ?,
                 updated_at = NOW()
             WHERE id = ?`,
            [
                JSON.stringify(summaryObj),
                gradingResult.grade,
                gradingResult.feedback,
                JSON.stringify(gradingResult.ai_summary || gradingResult.breakdown),
                investigation.id
            ]
        );

        // 5. Update alert status
        await connection.query(
            `UPDATE alerts SET status = 'Closed' WHERE id = ?`,
            [id]
        );

        await connection.commit();

        // 6. Get updated alert to return
        const result = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
        const updatedAlert = result.rows[0];

        // Get updated investigation for grade info
        const invResult = await db.query('SELECT grade, feedback FROM investigations WHERE id = ?', [investigation.id]);
        const gradedInv = invResult.rows[0];

        // 7. Advance operation progress if this alert is part of an operation
        const opAdvancement = await advanceOperationProgress(userId, id, gradedInv?.grade);

        // 7b. Mark assignment as completed (remove from Daily Queue)
        try {
            await assignmentService.markAssignmentCompleted(userId, id);
        } catch (assignError) {
            console.error('[completeInvestigationV2] Failed to mark assignment completed:', assignError);
            // Non-fatal
        }

        if (req.io) {
            req.io.emit('alert-updated', updatedAlert);
        }

        // Determine if user can retry (failed investigation with grade < 60)
        const canRetry = gradedInv?.grade !== null && gradedInv?.grade < 60;

        res.json({
            ...updatedAlert,
            grade: gradedInv?.grade,
            feedback: gradedInv?.feedback,
            message: canRetry 
                ? 'Investigation graded. You can try again to improve your score!' 
                : 'Investigation submitted and graded!',
            grading_breakdown: gradingResult.breakdown,
            next_alert_id: opAdvancement?.nextAlertId || null,
            can_retry: canRetry,
            isOperationComplete: opAdvancement?.isOperationComplete || false,
            operationId: opAdvancement?.operationId || null
        });

    } catch (err) {
        if (connection) await connection.rollback();
        console.error('[completeInvestigationV2] Error:', err);
        res.status(500).json({ message: 'Server error during completion.' });
    } finally {
        if (connection) connection.release();
    }
};

const unassignAlert = async (req, res) => {
    const { id } = req.params; // Alert ID
    const userId = req.user.id;

    try {
        // Check if investigation exists and matches user
        const result = await db.query('SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?', [id, userId]);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Investigation not found or not assigned to you' });
        }

        const investigation = result.rows[0];

        // Only allow unassign if status is 'investigating' (not submitted or graded)
        if (investigation.status !== 'investigating') {
            return res.status(400).json({ message: 'Cannot unassign: Investigation is already submitted or graded.' });
        }

        // Delete investigation
        await db.query('DELETE FROM investigations WHERE id = ?', [investigation.id]);

        // Update alert status back to 'New' (or check if other investigations exist? No, alerts are 1-1 assignment usually? No, DB schema allows multiple?
        // Wait, schema has `assigned_to` on alerts table for single assignment.
        // `investigations` table tracks history?
        // `assignAlert` sets `assigned_to` on `alerts`.
        // So we must clear `assigned_to` on `alerts`.

        await db.query('UPDATE alerts SET assigned_to = NULL, status = "New" WHERE id = ?', [id]);

        // Decrement user usage logic? (Optional, user asked "let user to unassign")
        // If we limit usage, unassigning might free up a slot?
        // Depending on policy. For now, let's just unassign.

        if (req.io) {
            const updatedAlertResult = await db.query('SELECT * FROM alerts WHERE id = ?', [id]);
            req.io.emit('alert-updated', updatedAlertResult.rows[0]);
        }

        res.json({ message: 'Alert unassigned successfully' });

    } catch (error) {
        console.error('Error unassigning alert:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

const processPendingEvaluations = async () => {
    try {
        const { runAutoGrader } = require('../services/autoGrader');
        await runAutoGrader();
    } catch (error) {
        console.error("[AutoGrader] Error processing pending evaluations:", error);
    }
};

const getInvestigationsForAdmin = async (req, res) => {
    try {
        const result = await db.query(`
            SELECT i.*, i.status as investigation_status, a.title as alert_title, u.name as user_name, u.email as user_email
            FROM investigations i
            JOIN alerts a ON i.alert_id = a.id
            JOIN users u ON i.user_id = u.id
            WHERE i.status = 'submitted'
            ORDER BY i.updated_at ASC
        `);

        // Parse executive_summary for each item
        const parsedRows = result.rows.map(row => ({
            ...row,
            executive_summary: typeof row.executive_summary === 'string' ? JSON.parse(row.executive_summary) : row.executive_summary
        }));

        res.json(parsedRows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

// Duplicate getClosedAlerts removed.




const gradeInvestigation = async (req, res) => {
    const { id } = req.params; // Investigation ID
    const { grade, feedback } = req.body;

    console.log(`[gradeInvestigation] Attempting to grade investigation ${id} with grade: ${grade}, feedback: ${feedback}`);

    if (grade === undefined || grade === null || grade < 0 || grade > 100) {
        console.log('[gradeInvestigation] Invalid grade');
        return res.status(400).json({ message: 'Please provide a valid grade (0-100)' });
    }

    try {
        await db.query(
            'UPDATE investigations SET grade = ?, feedback = ?, status = ?, is_reported = 0, updated_at = NOW() WHERE id = ?',
            [grade, feedback, 'graded', id]
        );

        const result = await db.query('SELECT * FROM investigations WHERE id = ?', [id]);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Investigation not found' });
        }

        const investigation = result.rows[0];

        // Check for activity badges
        if (grade !== undefined) { // Check only if graded
             await badgeService.checkActivityBadges(investigation.user_id);
             await badgeService.checkVolumeBadges(investigation.user_id);
             await badgeService.checkPerformanceBadges(investigation.user_id, investigation);
        }

        // Fetch user and alert details for email
        const details = await db.query(`
            SELECT u.email, u.name, a.title, u.id as userId
            FROM investigations i
            JOIN users u ON i.user_id = u.id
            JOIN alerts a ON i.alert_id = a.id
            WHERE i.id = ?
        `, [id]);

        if (details.rows.length > 0) {
            const { email, name, title } = details.rows[0];
            try {
                // Check email preferences
                const prefQuery = await db.query('SELECT email_preferences FROM users WHERE email = ?', [email]);
                let prefs = prefQuery.rows[0]?.email_preferences || {};
                if (typeof prefs === 'string') {
                    try { prefs = JSON.parse(prefs); } catch (e) { prefs = {}; }
                }
                if (prefs.investigation_graded !== false) {
                    await sendInvestigationGradedEmail(email, name, title, grade, feedback, userId);
                } else {
                    console.log(`Skipping graded email for ${email} (opted out)`);
                }
            } catch (emailErr) {
                console.error('Failed to send grading email:', emailErr);
            }
        }

        res.json(investigation);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const addNote = async (req, res) => {
    // For now, I'll keep it as is but it might not be used in the new flow.
    const { id } = req.params;
    const { note } = req.body;
    const { name, role } = req.user;

    if (!note) return res.status(400).json({ message: 'Please provide a note' });

    try {
        const noteObj = {
            author: name,
            role: role,
            content: note,
            timestamp: new Date()
        };

        await db.query(
            "UPDATE alerts SET notes = JSON_ARRAY_APPEND(COALESCE(notes, JSON_ARRAY()), '$', CAST(? AS JSON)) WHERE id = ?",
            [JSON.stringify(noteObj), id]
        );

        res.json({ message: 'Note added successfully', note: noteObj });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getArchivedAlerts = async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 10;
        const offset = (page - 1) * limit;

        // Archive rule: Only show alerts explicitly marked as archived (is_archived = 1)
        // Auto-archive rule (30 days) has been removed as per user request.
        
        const archiveQuery = `
            SELECT a.*, 
                   (SELECT COUNT(*) FROM investigations i WHERE i.alert_id = a.id) as investigation_count,
                   (SELECT MAX(i.updated_at) FROM investigations i WHERE i.alert_id = a.id) as last_activity
            FROM alerts a 
            WHERE a.is_archived = 1 OR a.status = 'Archived'
        `;

        // Count archived alerts
        const countResult = await db.query(`SELECT COUNT(*) as total FROM (${archiveQuery}) as archived`);
        const total = parseInt(countResult.rows[0].total);

        // Fetch paginated archived alerts
        const result = await db.query(`
            ${archiveQuery}
            ORDER BY a.created_at DESC
            LIMIT ${limit} OFFSET ${offset}
        `);

        res.json({
            alerts: result.rows,
            total,
            page,
            limit,
            totalPages: Math.ceil(total / limit)
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const resubmitAlert = async (req, res) => {
    const { id } = req.params;
    try {
        // Bump created_at to NOW() so it appears in the main feed again (within last 7 days)
        await db.query('UPDATE alerts SET created_at = NOW(), status = ? WHERE id = ?', ['New', id]);
        res.json({ message: 'Alert resubmitted to active feed successfully.' });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getLeaderboard = async (req, res) => {
    console.log('[DEBUG] getLeaderboard endpoint hit!');
    try {
        const { timeframe } = req.query;
        let query = `
            SELECT 
                u.id, 
                u.name, 
                COALESCE(u.username, SUBSTRING_INDEX(u.email, '@', 1)) as username, 
                u.profile_picture,
                COALESCE(ux.streak_days, 0) as streak,
                COALESCE(ux.total_xp, 0) as total_score,
                COALESCE(ux.total_xp, 0) as lifetime_xp,
                COALESCE(ux.current_level, 1) as level
            FROM users u
            LEFT JOIN user_xp ux ON u.id = ux.user_id
            WHERE ux.total_xp > 0 AND u.role != 'admin' AND u.username != 'admin'
            ORDER BY ux.total_xp DESC
            LIMIT 50
        `;

        if (timeframe === 'month') {
            query = `
                SELECT 
                    u.id, 
                    u.name, 
                    COALESCE(u.username, SUBSTRING_INDEX(u.email, '@', 1)) as username, 
                    u.profile_picture,
                    COALESCE(ux.streak_days, 0) as streak,
                    (SELECT COALESCE(SUM(grade), 0) FROM investigations WHERE user_id = u.id AND status = 'graded' AND updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as total_score,
                    COALESCE(ux.total_xp, 0) as lifetime_xp,
                    COALESCE(ux.current_level, 1) as level
                FROM users u
                LEFT JOIN user_xp ux ON u.id = ux.user_id
                WHERE u.role != 'admin' AND u.username != 'admin' AND (SELECT COALESCE(SUM(grade), 0) FROM investigations WHERE user_id = u.id AND status = 'graded' AND updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) > 0
                ORDER BY total_score DESC
                LIMIT 50
            `;
        } else if (timeframe === 'week') {
            query = `
                SELECT 
                    u.id, 
                    u.name, 
                    COALESCE(u.username, SUBSTRING_INDEX(u.email, '@', 1)) as username, 
                    u.profile_picture,
                    COALESCE(ux.streak_days, 0) as streak,
                    (SELECT COALESCE(SUM(grade), 0) FROM investigations WHERE user_id = u.id AND status = 'graded' AND updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as total_score,
                    COALESCE(ux.total_xp, 0) as lifetime_xp,
                    COALESCE(ux.current_level, 1) as level
                FROM users u
                LEFT JOIN user_xp ux ON u.id = ux.user_id
                WHERE u.role != 'admin' AND u.username != 'admin' AND (SELECT COALESCE(SUM(grade), 0) FROM investigations WHERE user_id = u.id AND status = 'graded' AND updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) > 0
                ORDER BY total_score DESC
                LIMIT 50
            `;
        }

        const result = await db.query(query);
            
        // Add dynamic titles based on calculated level
        // Level 1-20: Novice, 21-49: Tier 1, 50-99: Tier 2, 100-199: Senior, 200+: Manager
        const formattedRows = result.rows.map(row => {
            const level = row.level || 1;
            let title = 'Novice Analyst';
            if (level >= 200) title = 'SOC Manager';
            else if (level >= 100) title = 'Senior Analyst';
            else if (level >= 50) title = 'SOC Tier 2';
            else if (level >= 21) title = 'SOC Tier 1';
            
            return {
                ...row,
                title
            };
        });
            
        res.json(formattedRows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error', error: error.toString() });
    }
};

const resetLeaderboard = async (req, res) => {
    try {
        if (req.user.role !== 'admin') return res.status(403).json({ message: 'Not authorized' });
        
        // Fix: Set status to 'investigating' so auto-grader doesn't pick it up immediately.
        // Also clear ai_evaluation_scheduled_at just in case.
        await db.query(`
            UPDATE investigations 
            SET grade = NULL, 
                status = 'investigating', 
                ai_evaluation_scheduled_at = NULL,
                feedback = NULL
            WHERE status = 'graded'
        `);
        
        res.json({ message: 'Leaderboard reset successfully' });
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};

// Duplicate module.exports removed.


const reportInvestigation = async (req, res) => {
    try {
        const { id } = req.params;
        const { reason } = req.body;
        console.log(`Reporting investigation ${id}: ${reason}`);

        // Fetch User and Alert details for the notification
        const details = await db.query(`
            SELECT u.name as user_name, u.email as user_email, a.id as alert_id, a.title as alert_title, a.severity
            FROM investigations i
            JOIN users u ON i.user_id = u.id
            JOIN alerts a ON i.alert_id = a.id
            WHERE i.id = ?
        `, [id]);

        await db.query('UPDATE investigations SET is_reported = ?, report_reason = ? WHERE id = ?', [1, reason, id]);

        // Send Notification Email to Support
        if (details.rows.length > 0) {
            const row = details.rows[0];
            try {
                await sendReportNotificationEmail(
                    { name: row.user_name, email: row.user_email },
                    { id: row.alert_id, title: row.alert_title, severity: row.severity },
                    reason
                );
            } catch (emailError) {
                console.error('Failed to send support notification email:', emailError);
                // Don't fail the request if email fails
            }
        }

        res.json({ message: 'Investigation reported successfully' });
    } catch (error) {
        console.error('Error reporting investigation:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

const getReportedInvestigations = async (req, res) => {
    try {
        const result = await db.query(`
            SELECT i.*, 
                   i.status as investigation_status,
                   u.name as user_name, 
                   u.email as user_email,
                   a.title as alert_title, 
                   a.severity
            FROM investigations i 
            JOIN users u ON i.user_id = u.id 
            JOIN alerts a ON i.alert_id = a.id 
            WHERE i.is_reported = 1
            ORDER BY i.updated_at DESC
        `);

        // Parse executive_summary
        const parsedRows = result.rows.map(row => ({
            ...row,
            executive_summary: typeof row.executive_summary === 'string' ? JSON.parse(row.executive_summary) : row.executive_summary
        }));

        res.json(parsedRows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ message: 'Server error' });
    }
};


const resetInvestigation = async (req, res) => {
    try {
        const { id } = req.params;
        await db.query('UPDATE investigations SET grade = NULL, feedback = NULL, status = ?, is_reported = 0 WHERE id = ?', ['investigating', id]);
        res.json({ message: 'Investigation reset successfully.' });
    } catch (error) {
        console.error('Error resetting investigation:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

const resetMyInvestigation = async (req, res) => {
    const alertId = req.params.id;
    const userId = req.user.id;
    const FAIL_THRESHOLD = 60; // Grade below this allows retry

    try {
        // 1. Get the investigation for this user
        const invRes = await db.query(
            'SELECT * FROM investigations WHERE alert_id = ? AND user_id = ?', 
            [alertId, userId]
        );
        
        if (invRes.rows.length === 0) {
            return res.status(404).json({ message: 'Investigation not found.' });
        }
        
        const investigation = invRes.rows[0];
        
        // 2. Check if retry is allowed (grade must exist and be below threshold)
        if (investigation.grade === null || investigation.grade >= FAIL_THRESHOLD) {
            return res.status(403).json({ 
                message: `Only failed investigations (below ${FAIL_THRESHOLD}%) can be retried.` 
            });
        }
        
        // 3. Reset the investigation
        await db.query(`
            UPDATE investigations 
            SET status = 'investigating', 
                grade = NULL, 
                feedback = NULL,
                executive_summary = NULL,
                ai_evaluation_scheduled_at = NULL,
                updated_at = NOW()
            WHERE alert_id = ? AND user_id = ?
        `, [alertId, userId]);

        // 4. Update the alert status so the UI shows the investigation wizard
        await db.query(
            `UPDATE alerts SET status = 'Investigating', assigned_to = ? WHERE id = ?`, 
            [userId, alertId]
        );
        
        console.log(`[resetMyInvestigation] User ${userId} retrying alert ${alertId} (previous grade: ${investigation.grade})`);
        
        res.json({ 
            message: 'Investigation reset. Good luck on your next attempt!',
            previous_grade: investigation.grade
        });
                
    } catch (error) {
        console.error('Error resetting investigation:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

// Dashboard Stats for SOC Command Center
const getDashboardStats = async (req, res) => {
    try {
        // 1. Severity Counts
        const severityResult = await db.query(`
            SELECT 
                LOWER(severity) as severity,
                COUNT(*) as count
            FROM alerts
            WHERE (created_at <= NOW() OR created_at IS NULL)
            AND (source != 'Academy' OR source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = alerts.id)
            GROUP BY LOWER(severity)
        `);
        
        const severityCounts = { critical: 0, high: 0, medium: 0, low: 0, info: 0 };
        severityResult.rows.forEach(row => {
            if (severityCounts.hasOwnProperty(row.severity)) {
                severityCounts[row.severity] = parseInt(row.count);
            }
        });

        // 2. Investigation Status Counts (Personalized)
        // Open: Global 'new' alerts (Available to pick up)
        // Active: 'investigating' alerts assigned to THIS user
        // Closed: 'graded' alerts assigned to THIS user
        
        // Count Open (Global)
        const openResult = await db.query(`
            SELECT COUNT(*) as count 
            FROM alerts a
            WHERE (a.status = 'new' OR a.status IS NULL)
            AND (a.created_at <= NOW() OR a.created_at IS NULL)
            AND (a.source != 'Academy' OR a.source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)
            AND NOT EXISTS (SELECT 1 FROM investigations i WHERE i.alert_id = a.id)
        `);
        const openCount = parseInt(openResult.rows[0].count);

        const userId = Number(req.user.id);
        const myStatsResult = await db.query(`
            SELECT 
                COALESCE(status, 'investigating') as status,
                COUNT(*) as count
            FROM investigations
            WHERE user_id = ?
            GROUP BY status
        `, [userId]);

        const statusCounts = { open: openCount, investigating: 0, graded: 0 };
        myStatsResult.rows.forEach(row => {
            const s = (row.status || '').toLowerCase();
            if (['investigating', 'submitted', 'pending', 'new', 'in progress'].includes(s)) {
                statusCounts.investigating += parseInt(row.count);
            } else if (['graded', 'closed', 'completed'].includes(s)) {
                statusCounts.graded += parseInt(row.count);
            }
        });

        // 3. Category/Source Breakdown - Group by title keywords for campaign widgets
        const categoryResult = await db.query(`
            SELECT 
                LOWER(COALESCE(source, 'unknown')) as category,
                COUNT(*) as count
            FROM alerts
            WHERE (created_at <= NOW() OR created_at IS NULL)
            AND (source != 'Academy' OR source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = alerts.id)
            GROUP BY LOWER(COALESCE(source, 'unknown'))
            ORDER BY count DESC
            LIMIT 6
        `);

        // Campaign-specific counts based on title keywords
        const campaignResult = await db.query(`
            SELECT 
                CASE 
                    WHEN LOWER(title) LIKE '%phishing%' OR LOWER(title) LIKE '%email%' OR LOWER(title) LIKE '%spam%' OR LOWER(title) LIKE '%spear%' THEN 'phishing'
                    WHEN LOWER(title) LIKE '%ransomware%' OR LOWER(title) LIKE '%malware%' OR LOWER(title) LIKE '%trojan%' OR LOWER(title) LIKE '%virus%' OR LOWER(title) LIKE '%powershell%' THEN 'malware'
                    WHEN LOWER(title) LIKE '%brute%' OR LOWER(title) LIKE '%network%' OR LOWER(title) LIKE '%scan%' OR LOWER(title) LIKE '%ddos%' OR LOWER(title) LIKE '%port%' OR LOWER(title) LIKE '%traffic%' OR LOWER(title) LIKE '%connection%' THEN 'network'
                    WHEN LOWER(title) LIKE '%data%' OR LOWER(title) LIKE '%exfil%' OR LOWER(title) LIKE '%leak%' OR LOWER(title) LIKE '%dlp%' OR LOWER(title) LIKE '%unauthorized%' OR LOWER(title) LIKE '%transfer%' THEN 'data_exfiltration'
                    ELSE 'other'
                END as campaign,
                COUNT(*) as count
            FROM alerts
            WHERE (created_at <= NOW() OR created_at IS NULL)
            AND (source != 'Academy' OR source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = alerts.id)
            GROUP BY campaign
        `);
        
        const campaigns = { phishing: 0, malware: 0, network: 0, data_exfiltration: 0 };
        campaignResult.rows.forEach(row => {
            if (campaigns.hasOwnProperty(row.campaign)) {
                campaigns[row.campaign] = parseInt(row.count);
            }
        });

        // 4. Latest Threats (6 most recent active alerts)
        const latestResult = await db.query(`
            SELECT a.id, a.title, a.severity, a.source, a.difficulty, a.created_at,
                   i.status as investigation_status
            FROM alerts a
            LEFT JOIN investigations i ON a.id = i.alert_id AND i.user_id = ?
            WHERE (a.created_at <= NOW() OR a.created_at IS NULL)
            AND (a.source != 'Academy' OR a.source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)
            ORDER BY a.created_at DESC
            LIMIT 6
        `, [req.user?.id || 0]);

        // 5. Top Affected Sources/Assets
        const assetsResult = await db.query(`
            SELECT 
                COALESCE(source, 'Unknown') as asset,
                COUNT(*) as alert_count,
                SUM(CASE WHEN LOWER(severity) = 'critical' THEN 1 ELSE 0 END) as critical_count,
                SUM(CASE WHEN LOWER(severity) = 'high' THEN 1 ELSE 0 END) as high_count,
                SUM(CASE WHEN LOWER(severity) = 'medium' THEN 1 ELSE 0 END) as medium_count
            FROM alerts
            WHERE (created_at <= NOW() OR created_at IS NULL)
            AND (source != 'Academy' OR source IS NULL)
            AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = alerts.id)
            GROUP BY COALESCE(source, 'Unknown')
            ORDER BY alert_count DESC
            LIMIT 5
        `);

        res.json({
            severity: severityCounts,
            status: statusCounts,
            categories: categoryResult.rows,
            campaigns: campaigns,
            latestThreats: latestResult.rows,
            affectedAssets: assetsResult.rows,
            totalAlerts: Object.values(severityCounts).reduce((a, b) => a + b, 0),
            ctfCount: 60 // Real count from ctfScenarios.js
        });
    } catch (error) {
        console.error('Error fetching dashboard stats:', error);
        res.status(500).json({ message: 'Server error fetching dashboard stats' });
    }
};

/**
 * Bulk tag alerts with min_level and path_code
 * For admins to quickly categorize multiple alerts
 */
const bulkTagAlerts = async (req, res) => {
    const { alertIds, min_level, path_code } = req.body;

    if (!alertIds || !Array.isArray(alertIds) || alertIds.length === 0) {
        return res.status(400).json({ message: 'Please provide an array of alert IDs' });
    }

    try {
        // Build update query parts based on what's provided
        const updates = [];
        const params = [];

        if (min_level !== undefined && min_level !== null) {
            updates.push('min_level = ?');
            params.push(min_level);
        }

        if (path_code !== undefined) {
            updates.push('path_code = ?');
            params.push(path_code);
        }

        if (updates.length === 0) {
            return res.status(400).json({ message: 'Please provide min_level or path_code to update' });
        }

        // Build IN clause for alert IDs
        const placeholders = alertIds.map(() => '?').join(',');
        params.push(...alertIds);

        const query = `UPDATE alerts SET ${updates.join(', ')} WHERE id IN (${placeholders})`;
        const result = await db.query(query, params);

        res.json({ 
            message: `Successfully tagged ${alertIds.length} alerts`,
            updated: alertIds.length,
            min_level: min_level !== undefined ? min_level : 'unchanged',
            path_code: path_code !== undefined ? path_code : 'unchanged'
        });
    } catch (error) {
        console.error('Bulk tag error:', error);
        res.status(500).json({ message: `Server error: ${error.message}` });
    }
};

/**
 * Get path/level stats for admin dashboard
 */
const getPathStats = async (req, res) => {
    try {
        const whereClause = `(source != 'Academy' OR source IS NULL) 
            AND NOT EXISTS (SELECT 1 FROM operation_alerts WHERE operation_alerts.alert_id = alerts.id)`;

        // 1. Total Active Alerts
        const totalResult = await db.query(`SELECT COUNT(*) as total FROM alerts WHERE ${whereClause}`);
        
        // 2. Stats by Path
        const pathResult = await db.query(`
            SELECT 
                COALESCE(path_code, 'CORE') as path_code,
                COUNT(*) as count
            FROM alerts
            WHERE ${whereClause}
            GROUP BY path_code
            ORDER BY count DESC
        `);

        // 3. Stats by Difficulty/Level
        const levelResult = await db.query(`
            SELECT 
                COALESCE(difficulty, 'Intermediate') as difficulty,
                COUNT(*) as count
            FROM alerts
            WHERE ${whereClause}
            GROUP BY difficulty
            ORDER BY count DESC
        `);

        res.json({
            total: parseInt(totalResult.rows[0].total),
            pathStats: pathResult.rows,
            difficultyStats: levelResult.rows
        });
    } catch (error) {
        console.error('Get path stats error:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

/**
 * Get distinct sources for dynamic filtering
 */
const getDistinctSources = async (req, res) => {
    try {
        const result = await db.query("SELECT DISTINCT source FROM alerts WHERE source IS NOT NULL AND source != '' ORDER BY source ASC");
        const sources = result.rows.map(row => row.source);
        res.json(sources);
    } catch (error) {
        console.error('Error fetching distinct sources:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

// Public stats for homepage
const getPublicStats = async (req, res) => {
    try {
        // Count total alerts
        const alertsResult = await db.query('SELECT COUNT(*) as count FROM alerts');
        const alertCount = parseInt(alertsResult.rows[0].count);

        // Count operations (missions)
        // Since 'active' status might not exist or be different, just count all for now or check schema
        const opsResult = await db.query('SELECT COUNT(*) as count FROM operations');
        const missionCount = parseInt(opsResult.rows[0].count);

        // Get recent activity (optional)
        const recentResult = await db.query('SELECT title, created_at FROM alerts ORDER BY created_at DESC LIMIT 3');
        
        res.json({
            alerts: alertCount,
            missions: missionCount,
            captures: 60, // Real count from ctfScenarios.js
            recent: recentResult.rows
        });
    } catch (error) {
        console.error('Error fetching public stats:', error);
        res.status(500).json({ message: 'Server error' });
    }
};

module.exports = {
    getAlerts,
    getMyAlerts,
    getClosedAlerts,
    getAlertById,
    createAlert,
    generateAlerts,
    assignAlert,
    unassignAlert,
    addNote,
    submitSummary,
    completeInvestigation,
    completeInvestigationV2,
    reportInvestigation,
    gradeInvestigation,
    resetInvestigation,
    resetMyInvestigation,
    getInvestigationsForAdmin,
    getReportedInvestigations,
    deleteAlert,
    getArchivedAlerts,
    resubmitAlert,
    updateAlert,
    getLeaderboard,
    resetLeaderboard,
    getAllAssignedAlerts,
    deleteAllAlerts,
    getDashboardStats,
    bulkTagAlerts,
    bulkTagAlerts,
    getPathStats,
    getDistinctSources,
    getPublicStats
};
