// Operations Controller - Narrative Campaigns
const db = require('../db');

// Helper to get AI API Key from env or DB
const getAIKey = async () => {
    if (process.env.OPENAI_API_KEY) return process.env.OPENAI_API_KEY;
    try {
        const res = await db.query("SELECT value FROM system_settings WHERE `key` = 'ai_api_key'");
        const rows = res.rows || res;
        if (rows && rows.length > 0) {
            return rows[0].value.replace(/^"|"$/g, '');
        }
    } catch (e) { console.error('Error fetching AI key:', e); }
    return null;
};

// Helper: Resolve ID or Slug to Operation ID
const resolveOperationId = async (identifier) => {
    // If it's a number, return it (assuming it's an ID)
    if (!isNaN(identifier)) return parseInt(identifier);
    
    // Attempt lookup by slug
    const res = await db.query('SELECT id FROM operations WHERE slug = ?', [identifier]);
    if (res.rows.length === 0) return null;
    return res.rows[0].id;
};

/**
 * List all active operations with user progress
 */
const listOperations = async (req, res) => {
    try {
        const userId = Number(req.user.id);

        // Get user's current level
        const userLevelResult = await db.query(
            'SELECT current_level FROM user_xp WHERE user_id = ?',
            [userId]
        );
        const userLevel = userLevelResult.rows[0]?.current_level || 1;

        // Get all active operations
        const operationsResult = await db.query(`
            SELECT 
                o.*,
                (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = o.id) as total_alerts,
                uop.current_alert_sequence,
                uop.started_at,
                uop.completed_at
            FROM operations o
            LEFT JOIN user_operation_progress uop ON o.id = uop.operation_id AND uop.user_id = ?
            WHERE o.is_active = 1
            ORDER BY o.created_at DESC
        `, [userId]);

        const operations = operationsResult.rows.map(op => {
            const minLevel = op.min_level || 1;
            const isLocked = userLevel < minLevel;
            
            return {
                ...op,
                min_level: minLevel,
                is_locked: isLocked,
                user_level: userLevel,
                status: op.completed_at ? 'completed' : (op.started_at ? 'in_progress' : 'not_started'),
                progress_percent: op.completed_at 
                    ? 100 
                    : (op.total_alerts > 0 
                        ? (op.current_alert_sequence 
                            ? Math.min(Math.round(((op.current_alert_sequence - 1) / op.total_alerts) * 100), 99)
                            : 0)
                        : 0)
            };
        });

        res.json({ operations, user_level: userLevel });
    } catch (error) {
        console.error('Error listing operations:', error);
        res.status(500).json({ message: 'Failed to list operations' });
    }
};

/**
 * List all active operations (public view)
 */
const listPublicOperations = async (req, res) => {
    try {
        // Get all active operations
        const operationsResult = await db.query(`
            SELECT 
                o.*,
                (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = o.id) as total_alerts
            FROM operations o
            WHERE o.is_active = 1
            ORDER BY o.created_at DESC
        `);

        res.json({ operations: operationsResult.rows });
    } catch (error) {
        console.error('Error listing public operations:', error);
        res.status(500).json({ message: 'Failed to list operations' });
    }
};

/**
 * Get public operation details (no auth required) - for SEO pages
 */
const getPublicOperationDetails = async (req, res) => {
    try {
        const identifier = req.params.id;
        const id = await resolveOperationId(identifier);

        if (!id) {
            return res.status(404).json({ message: 'Operation not found' });
        }

        // Get operation
        const opResult = await db.query('SELECT * FROM operations WHERE id = ? AND is_active = 1', [id]);
        if (opResult.rows.length === 0) {
            return res.status(404).json({ message: 'Operation not found' });
        }
        const operation = opResult.rows[0];

        // Get alert count and basic info (no spoilers)
        const alertsResult = await db.query(`
            SELECT 
                oa.sequence_order,
                a.severity,
                a.alert_type
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            WHERE oa.operation_id = ?
            ORDER BY oa.sequence_order ASC
        `, [id]);

        // Calculate stats
        const missionStats = {
            total_missions: alertsResult.rows.length,
            severity_breakdown: {},
            alert_types: []
        };

        alertsResult.rows.forEach(alert => {
            const sev = alert.severity || 'medium';
            missionStats.severity_breakdown[sev] = (missionStats.severity_breakdown[sev] || 0) + 1;
            if (alert.alert_type && !missionStats.alert_types.includes(alert.alert_type)) {
                missionStats.alert_types.push(alert.alert_type);
            }
        });

        // Get participant count
        const participantsResult = await db.query(
            'SELECT COUNT(*) as count FROM user_operation_progress WHERE operation_id = ?',
            [id]
        );
        const completedResult = await db.query(
            'SELECT COUNT(*) as count FROM user_operation_progress WHERE operation_id = ? AND completed_at IS NOT NULL',
            [id]
        );

        res.json({
            operation: {
                id: operation.id,
                title: operation.title,
                apt_group: operation.apt_group,
                description: operation.description,
                story_intro: operation.story_intro,
                difficulty_level: operation.difficulty_level,
                is_premium: operation.is_premium,
                image_url: operation.image_url,
                tags: operation.tags,
                estimated_time_minutes: operation.time_limit_hours ? operation.time_limit_hours * 60 : 90
            },
            stats: {
                ...missionStats,
                participants: participantsResult.rows[0]?.count || 0,
                completions: completedResult.rows[0]?.count || 0
            }
        });
    } catch (error) {
        console.error('Error getting public operation details:', error);
        res.status(500).json({ message: 'Failed to get operation details' });
    }
};

/**
 * Get guest access to operation alert (first 2 alerts only for free ops)
 */
const getGuestOperationAlert = async (req, res) => {
    try {
        const { id: identifier, seq } = req.params;
        const id = await resolveOperationId(identifier);
        
        if (!id) return res.status(404).json({ message: 'Operation not found' });

        const sequence = parseInt(seq);

        // Only allow sequences 1 and 2 for guests
        if (sequence > 3) {
            return res.status(403).json({ 
                message: 'Registration required to continue',
                requiresAuth: true,
                completedAlerts: 3
            });
        }

        // Get operation (must be free and active)
        // Get operation
        const opResult = await db.query('SELECT * FROM operations WHERE id = ?', [id]);
        
        if (opResult.rows.length === 0) {
            return res.status(404).json({ message: 'Operation not found' });
        }
        const operation = opResult.rows[0];

        // Check permissions
        const isAdmin = req.user && req.user.role === 'admin';
        const isPremiumUser = req.user && req.user.is_premium;

        // Check active status (Admin bypass)
        if (!operation.is_active && !isAdmin) {
             return res.status(404).json({ message: 'Operation not found' });
        }

        // Check premium status (Admin or Premium User bypass)
        if (operation.is_premium && !isAdmin && !isPremiumUser) {
            return res.status(403).json({ message: 'Operation is premium. Please upgrade to access.' });
        }

        // Get the alert at this sequence
        const alertResult = await db.query(`
            SELECT 
                oa.sequence_order,
                oa.intel_report_title,
                oa.intel_report_content,
                a.*
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            WHERE oa.operation_id = ? AND oa.sequence_order = ?
        `, [id, sequence]);

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

        const alert = alertResult.rows[0];

        // Get total alerts count
        const totalResult = await db.query(
            'SELECT COUNT(*) as count FROM operation_alerts WHERE operation_id = ?',
            [id]
        );

        res.json({
            operation: {
                id: operation.id,
                title: operation.title,
                apt_group: operation.apt_group,
                total_alerts: totalResult.rows[0]?.count || 0
            },
            alert: {
                id: alert.id,
                title: alert.title,
                severity: alert.severity,
                source: alert.source,
                details: alert.details,
                raw_log: alert.raw_log,
                alert_type: alert.alert_type,
                mitre_technique: alert.mitre_technique,
                playbook_solution: alert.playbook_solution,
                sequence_order: alert.sequence_order
            },
            guestMode: true,
            currentSequence: sequence,
            maxGuestSequence: 3
        });
    } catch (error) {
        console.error('Error getting guest operation alert:', error);
        res.status(500).json({ message: 'Failed to get alert' });
    }
};

/**
 * Grade guest investigation (no DB save, just returns result)
 */
const gradeGuestInvestigation = async (req, res) => {
    try {
        const { id, seq } = req.params;
        const { answers } = req.body;
        const sequence = parseInt(seq);

        // Only allow sequences 1 and 2 for guests
        if (sequence > 3) {
            return res.status(403).json({ 
                message: 'Registration required to continue',
                requiresAuth: true
            });
        }

        // Get the alert
        const alertResult = await db.query(`
            SELECT a.*, oa.sequence_order
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            WHERE oa.operation_id = ? AND oa.sequence_order = ?
        `, [id, sequence]);

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

        const alert = alertResult.rows[0];
        
        // Parse playbook solution for grading
        let solution = {};
        try {
            let parsed = typeof alert.playbook_solution === 'string' 
                ? JSON.parse(alert.playbook_solution) 
                : alert.playbook_solution || {};
            // Handle double-encoded JSON
            if (typeof parsed === 'string') {
                parsed = JSON.parse(parsed);
            }
            solution = parsed;
        } catch (e) {
            console.error('Error parsing playbook solution:', e);
        }

        console.log('[Guest Grading] Answers:', JSON.stringify(answers));
        console.log('[Guest Grading] Solution verdict:', solution.verdict || solution.correct_verdict);

        // Grading logic - simplified for guest experience
        let grade = 0;
        let feedback = [];

        // 1. Check verdict (50 points) - most important
        const expectedVerdict = (solution.verdict || solution.correct_verdict || 'true_positive').toLowerCase().replace(/\s+/g, '_');
        const userVerdict = (answers?.verdict || '').toLowerCase().replace(/\s+/g, '_');
        
        // Flexible matching for verdicts
        const verdictMatches = 
            userVerdict === expectedVerdict ||
            (userVerdict.includes('true') && expectedVerdict.includes('true')) ||
            (userVerdict.includes('false') && expectedVerdict.includes('false')) ||
            (userVerdict.includes('benign') && expectedVerdict.includes('benign'));
        
        if (verdictMatches) {
            grade += 50;
            feedback.push({ field: 'verdict', correct: true, message: 'Correct verdict!' });
        } else {
            feedback.push({ field: 'verdict', correct: false, expected: expectedVerdict, got: userVerdict });
        }

        // 2. Check actions (30 points) - give full credit if any actions selected
        const userActions = answers?.actions_taken || [];
        if (userActions.length > 0) {
            grade += 30;
            feedback.push({ field: 'actions', correct: true, count: userActions.length });
        } else {
            feedback.push({ field: 'actions', correct: false, message: 'No containment actions selected' });
        }

        // 3. Check artifacts (20 points) - give full credit if any artifacts analyzed
        const userArtifacts = answers?.artifacts_checked || [];
        if (userArtifacts.length > 0) {
            grade += 20;
            feedback.push({ field: 'artifacts', correct: true, count: userArtifacts.length });
        } else {
            feedback.push({ field: 'artifacts', correct: false, message: 'No artifacts analyzed' });
        }

        // Round grade
        grade = Math.round(grade);
        const passed = grade >= 60;

        console.log('[Guest Grading] Final grade:', grade, 'Passed:', passed);

        // Check if this was the 2nd alert
        const nextSequence = sequence + 1;
        const hasNextAlert = await db.query(
            'SELECT 1 FROM operation_alerts WHERE operation_id = ? AND sequence_order = ?',
            [id, nextSequence]
        );

        res.json({
            grade,
            passed,
            feedback,
            guestMode: true,
            completedSequence: sequence,
            nextSequence: hasNextAlert.rows.length > 0 ? nextSequence : null,
            requiresAuth: nextSequence > 3,
            message: passed 
                ? (nextSequence > 3 
                    ? 'Great work! Register to continue your investigation.' 
                    : 'Mission complete! Proceed to the next target.')
                : 'Review your analysis and try again.'
        });
    } catch (error) {
        console.error('Error grading guest investigation:', error);
        res.status(500).json({ message: 'Failed to grade investigation' });
    }
};




/**
 * Get operation details with alerts and user progress
 */
const getOperationDetails = async (req, res) => {
    try {
        const identifier = req.params.id;
        const userId = Number(req.user.id);
        const id = await resolveOperationId(identifier);

        if (!id) {
            console.log(`Operation not found for identifier: ${identifier}`);
            return res.status(404).json({ message: `Operation not found (ID: ${identifier})` });
        }

        // Get operation
        const opResult = await db.query('SELECT * FROM operations WHERE id = ?', [id]);
        if (opResult.rows.length === 0) {
            console.log(`Operation not found in DB for ID: ${id}`);
            return res.status(404).json({ message: 'Operation not found' });
        }
        const operation = opResult.rows[0];

        // Get user progress - ORDER BY max sequence just in case of any duplicate data issues
        const progressResult = await db.query(
            'SELECT * FROM user_operation_progress WHERE user_id = ? AND operation_id = ? ORDER BY current_alert_sequence DESC LIMIT 1',
            [userId, id]
        );
        const progress = progressResult.rows[0] || null;

        // Get alerts in order
        const alertsResult = await db.query(`
            SELECT 
                oa.sequence_order,
                oa.intel_report_title,
                oa.intel_report_content,
                a.id as alert_id,
                a.title as alert_title,
                a.severity,
                a.alert_type,
                i.status as investigation_status,
                i.grade,
                i.feedback
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            LEFT JOIN investigations i ON a.id = i.alert_id AND i.user_id = ?
            WHERE oa.operation_id = ?
            ORDER BY oa.sequence_order ASC
        `, [userId, id]);

        // Mark which alerts are accessible (current or completed)
        const currentSeq = progress?.current_alert_sequence || 1;
        // Detect mobile platform for progression threshold
        const isMobile = req.headers['user-agent']?.includes('Expo') || req.headers['x-platform'] === 'mobile';
        const progressThreshold = isMobile ? 30 : 60;
        const completionThreshold = 60; // Standard for "Analysis Complete" status

        const alerts = alertsResult.rows.map(alert => ({
            ...alert,
            // Accessible if it's the current sequence OR already in progress/completed
            is_accessible: alert.sequence_order <= currentSeq,
            // Completed ONLY if grade is high enough (completionThreshold)
            is_completed: (alert.investigation_status === 'graded' || alert.investigation_status === 'completed') && 
                          (alert.grade !== null && alert.grade >= completionThreshold),
            // Intel unlocked if they passed the progression threshold (30%)
            intel_unlocked: (alert.investigation_status === 'graded' || alert.investigation_status === 'completed') && 
                            (alert.grade !== null && alert.grade >= progressThreshold)
        }));

        res.json({
            operation,
            progress,
            alerts,
            status: progress?.completed_at ? 'completed' : (progress?.started_at ? 'in_progress' : 'not_started')
        });
    } catch (error) {
        console.error('Error getting operation details:', error);
        res.status(500).json({ message: 'Failed to get operation details' });
    }
};

/**
 * Start an operation - enroll user and assign first alert
 */
const startOperation = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = Number(req.user.id);
        
        // Check free tier limit
        const usageService = require('../utils/usageService');
        const usageCheck = await usageService.enforceFreeTierLimit(userId);
        if (!usageCheck.allowed) {
            return res.status(403).json({ 
                message: usageCheck.message
            });
        }

        const opId = await resolveOperationId(id);
        if (!opId) {
            return res.status(404).json({ message: 'Operation not found' });
        }

        // Get operation details including min_level
        const opResult = await db.query(
            'SELECT min_level, difficulty_level, title FROM operations WHERE id = ?',
            [opId]
        );
        const operation = opResult.rows[0];
        const requiredLevel = operation?.min_level || 1;

        // Get user's current level from user_xp table
        const userLevelResult = await db.query(
            'SELECT current_level FROM user_xp WHERE user_id = ?',
            [userId]
        );
        const userLevel = userLevelResult.rows[0]?.current_level || 1;

        // Check if user meets level requirement
        if (userLevel < requiredLevel) {
            return res.status(403).json({ 
                message: `Level ${requiredLevel} required to start this mission. You are Level ${userLevel}.`,
                required_level: requiredLevel,
                user_level: userLevel
            });
        }

        // Check if already started
        const existingProgress = await db.query(
            'SELECT * FROM user_operation_progress WHERE user_id = ? AND operation_id = ?',
            [userId, opId]
        );
        if (existingProgress.rows.length > 0) {
            return res.status(400).json({ message: 'Operation already started' });
        }

        // Get first alert
        const firstAlertResult = await db.query(`
            SELECT oa.alert_id FROM operation_alerts oa
            WHERE oa.operation_id = ? AND oa.sequence_order = 1
        `, [opId]);

        if (firstAlertResult.rows.length === 0) {
            return res.status(400).json({ message: 'Operation has no alerts configured' });
        }

        const firstAlertId = firstAlertResult.rows[0].alert_id;

        // Create progress record
        await db.query(
            'INSERT INTO user_operation_progress (user_id, operation_id, current_alert_sequence) VALUES (?, ?, 1)',
            [userId, opId]
        );

        // Assign first alert to user -> REMOVED: Do not update global alerts table!
        // This causes issues where one user taking a mission marks alerts as 'assigned' for everyone else.
        // Alert state is tracked via 'investigations' table created below.
        /* 
        await db.query(
            'UPDATE alerts SET assigned_to = ? WHERE id = ? AND assigned_to IS NULL',
            [userId, firstAlertId]
        );
        */

        // Create investigation if not exists
        const existingInvestigation = await db.query(
            'SELECT id FROM investigations WHERE user_id = ? AND alert_id = ?',
            [userId, firstAlertId]
        );
        if (existingInvestigation.rows.length === 0) {
            await db.query(
                'INSERT INTO investigations (user_id, alert_id, status) VALUES (?, ?, ?)',
                [userId, firstAlertId, 'investigating']
            );
        }

        res.json({ 
            message: 'Operation started successfully',
            first_alert_id: firstAlertId
        });
    } catch (error) {
        console.error('Error starting operation:', error);
        res.status(500).json({ message: 'Failed to start operation' });
    }
};

/**
 * Get unlocked intel reports for an operation
 */
const getIntelReports = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = Number(req.user.id);

        // Get user progress
        const progressResult = await db.query(
            'SELECT current_alert_sequence FROM user_operation_progress WHERE user_id = ? AND operation_id = ?',
            [userId, id]
        );

        if (progressResult.rows.length === 0) {
            return res.json({ intel_reports: [] });
        }

        const currentSeq = progressResult.rows[0].current_alert_sequence;

        // Get intel reports for completed alerts (sequence < current)
        const intelResult = await db.query(`
            SELECT 
                oa.sequence_order,
                oa.intel_report_title,
                oa.intel_report_content
            FROM operation_alerts oa
            WHERE oa.operation_id = ? AND oa.sequence_order < ?
            ORDER BY oa.sequence_order ASC
        `, [id, currentSeq]);

        res.json({ intel_reports: intelResult.rows });
    } catch (error) {
        console.error('Error getting intel reports:', error);
        res.status(500).json({ message: 'Failed to get intel reports' });
    }
};

/**
 * Advance user to next alert in operation (called after grading)
 * This is an internal function, not an API endpoint
 */
const advanceOperationProgress = async (userId, alertId, grade, options = { minScore: 60 }) => {
    try {
        const minPassScore = options?.minScore || 60;
        console.log(`[OpProgress] START: Processing progression for User ${userId}, Alert ${alertId}, Grade ${grade} (MinPass: ${minPassScore})`);
        const parsedGrade = parseInt(grade, 10);

        // Only advance if passed (based on provided minPassScore)
        if (isNaN(parsedGrade) || parsedGrade < minPassScore) {
            console.log(`[OpProgress] FAIL: Grade ${parsedGrade} < MinPass ${minPassScore}. Exiting.`);
            return;
        }

        // 1. Resolve which operation this alert belongs to
        const opAlertResult = await db.query(`
            SELECT oa.operation_id, oa.sequence_order
            FROM operation_alerts oa
            WHERE oa.alert_id = ?
        `, [alertId]);

        if (opAlertResult.rows.length === 0) {
            console.log(`[OpProgress] Alert ${alertId} is NOT part of any operation.`);
            return;
        }

        const { operation_id, sequence_order } = opAlertResult.rows[0];
        const nextSequence = parseInt(sequence_order, 10) + 1;

        console.log(`[OpProgress] User ${userId} passed Alert ${alertId} (Seq: ${sequence_order}) with grade ${grade}.`);

        // 2. Mandatory Check: Get current progress
        const currentProgResult = await db.query(
            'SELECT current_alert_sequence, completed_at FROM user_operation_progress WHERE user_id = ? AND operation_id = ?',
            [userId, operation_id]
        );

        let currentSeq = 1;
        if (currentProgResult.rows.length > 0) {
            currentSeq = parseInt(currentProgResult.rows[0].current_alert_sequence, 10);
            if (currentProgResult.rows[0].completed_at) {
                console.log(`[OpProgress] Operation ${operation_id} already completed for User ${userId}.`);
                return;
            }
        } else {
            // First time progress - initialize
            await db.query(
                'INSERT INTO user_operation_progress (user_id, operation_id, current_alert_sequence) VALUES (?, ?, 1)',
                [userId, operation_id]
            );
        }

        // 3. Logic: Only increment if they are completing their "Current" alert or something newer
        if (parseInt(sequence_order, 10) < currentSeq) {
            console.log(`[OpProgress] User is re-playing an old alert. No sequence advancement needed.`);
            return;
        }

        // 4. Check for Next Alert availability
        const nextAlertCheck = await db.query(
            'SELECT alert_id FROM operation_alerts WHERE operation_id = ? AND sequence_order = ?',
            [operation_id, nextSequence]
        );

        if (nextAlertCheck.rows.length === 0) {
            // NO MORE ALERTS - Operation Complete
            await db.query(
                'UPDATE user_operation_progress SET completed_at = NOW(), current_alert_sequence = ? WHERE user_id = ? AND operation_id = ?',
                [nextSequence, userId, operation_id]
            );
            console.log(`[OpProgress] SUCCESS: User ${userId} COMPLETED Operation ${operation_id}.`);
            return { isOperationComplete: true, operationId: operation_id };
        }

        // 5. Update Progression to Next Alert
        const nextAlertId = nextAlertCheck.rows[0].alert_id;
        
        await db.query(
            'UPDATE user_operation_progress SET current_alert_sequence = ? WHERE user_id = ? AND operation_id = ?',
            [nextSequence, userId, operation_id]
        );

        // Auto-assign next alert
        await db.query(
            'UPDATE alerts SET assigned_to = ? WHERE id = ? AND (assigned_to IS NULL OR assigned_to = ?)',
            [userId, nextAlertId, userId]
        );

        console.log(`[OpProgress] SUCCESS: User ${userId} advanced to Sequence ${nextSequence} (Alert ${nextAlertId}).`);
        return { nextAlertId, nextSequence, operationId: operation_id };
    } catch (error) {
        console.error('Error advancing operation progress:', error);
        return null;
    }
};

/**
 * Assign a daily mission to the user
 * Logic:
 * 1. Check if user already has an active (in_progress) mission. If yes, return it.
 * 2. If no active mission, find the next available mission (ordered by display_order/id) that:
 *    - Is active
 *    - Is not completed by user
 *    - Min level <= User level
 * 3. Start that mission for the user.
 */
const assignDailyMission = async (req, res) => {
    try {
        const userId = Number(req.user.id);

        // 1. Check for active mission
        const activeMissionResult = await db.query(`
            SELECT o.*, uop.current_alert_sequence, uop.started_at
            FROM operations o
            JOIN user_operation_progress uop ON o.id = uop.operation_id
            WHERE uop.user_id = ? AND uop.completed_at IS NULL
            LIMIT 1
        `, [userId]);

        if (activeMissionResult.rows.length > 0) {
            return res.json({ 
                message: 'Active mission found', 
                mission: activeMissionResult.rows[0],
                is_new_assignment: false
            });
        }

        // 2. Find next available mission
        // Get user level first
        const userLevelResult = await db.query('SELECT current_level FROM user_xp WHERE user_id = ?', [userId]);
        const userLevel = userLevelResult.rows[0]?.current_level || 1;

        const nextMissionResult = await db.query(`
            SELECT o.* 
            FROM operations o
            WHERE o.is_active = 1 
            AND o.min_level <= ?
            AND NOT EXISTS (
                SELECT 1 FROM user_operation_progress uop 
                WHERE uop.operation_id = o.id AND uop.user_id = ?
            )
            ORDER BY o.display_order ASC, o.id ASC
            LIMIT 1
        `, [userLevel, userId]);

        if (nextMissionResult.rows.length === 0) {
            return res.json({ 
                message: 'No new missions available for your level', 
                mission: null 
            });
        }

        const missionToAssign = nextMissionResult.rows[0];

        // 3. Start the mission (Assign it)
        try {
            // Check free tier limit
            const usageService = require('../utils/usageService');
            const usageCheck = await usageService.enforceFreeTierLimit(userId);
            if (!usageCheck.allowed) {
                 return res.status(403).json({ 
                     message: usageCheck.message
                 });
            }

            // Mock request object for startOperation-like logic or just direct DB insert
            // Direct insert is safer here to avoid recursion/permissions overhead of startOperation fn
            
            // Get first alert ID
            const firstAlertRes = await db.query('SELECT alert_id FROM operation_alerts WHERE operation_id = ? AND sequence_order = 1', [missionToAssign.id]);
            
            if (firstAlertRes.rows.length === 0) {
                 return res.status(500).json({ message: 'Mission has no alerts configuration' });
            }
            const firstAlertId = firstAlertRes.rows[0].alert_id;

            // Create progress
            await db.query(
                'INSERT INTO user_operation_progress (user_id, operation_id, current_alert_sequence, started_at) VALUES (?, ?, 1, NOW())',
                [userId, missionToAssign.id]
            );

            // Ensure investigation exists for first alert
             const existingInv = await db.query('SELECT id FROM investigations WHERE user_id = ? AND alert_id = ?', [userId, firstAlertId]);
             if (existingInv.rows.length === 0) {
                 await db.query('INSERT INTO investigations (user_id, alert_id, status) VALUES (?, ?, ?)', [userId, firstAlertId, 'investigating']);
             }

             return res.json({
                 message: 'New daily mission assigned',
                 mission: missionToAssign,
                 is_new_assignment: true
             });

        } catch (assignError) {
            console.error('Error assigning mission:', assignError);
            return res.status(500).json({ message: 'Failed to assign mission' });
        }

    } catch (error) {
        console.error('Error in assignDailyMission:', error);
        res.status(500).json({ message: 'Internal server error' });
    }
};

/**
 * Admin: Create new operation manually (with optional AI alert generation)
 */
const createOperation = async (req, res) => {
    try {
        const { generateOperation } = require('../services/operationGenerator');
        const { 
            title, apt_group, description, story_intro, difficulty_level, 
            display_order, is_premium, passing_grade, time_limit_hours, 
            tags, scenario_prompt,
            // AI generation options
            alert_count, generate_with_ai
        } = req.body;

        // Generate slug
        const slug = (title || 'untitled').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');

        // 1. Create the operation first
        const result = await db.query(
            `INSERT INTO operations 
            (title, slug, apt_group, description, story_intro, difficulty_level, display_order, 
             is_active, is_premium, passing_grade, time_limit_hours, tags, scenario_prompt) 
            VALUES (?, ?, ?, ?, ?, ?, ?, TRUE, ?, ?, ?, ?, ?)`,
            [
                title, slug, apt_group, description, story_intro, difficulty_level, 
                display_order || 0, is_premium ?? 1, passing_grade || 60, 
                time_limit_hours || null, tags ? JSON.stringify(tags) : null, 
                scenario_prompt || null
            ]
        );
        const operationId = result.insertId;

        // 2. If AI generation requested, generate alerts
        if (generate_with_ai && alert_count > 0) {
            const apiKey = await getAIKey();
            if (!apiKey) {
                return res.status(201).json({ 
                    message: 'Operation created but AI generation skipped (no API key)',
                    operation_id: operationId,
                    ai_generated: false
                });
            }

            try {
                const { generateOperation } = require('../services/operationGenerator');
                const generatedData = await generateOperation({
                    aptGroup: apt_group || 'Unknown Threat Actor',
                    scenario: scenario_prompt || description || title,
                    difficulty: difficulty_level || 'intermediate',
                    alertCount: parseInt(alert_count),
                    apiKey
                });

                // Insert generated alerts and link them
                for (let i = 0; i < generatedData.alerts.length; i++) {
                    const alert = generatedData.alerts[i];
                    
                    const alertResult = await db.query(
                        `INSERT INTO alerts 
                        (title, severity, source, details, alert_type, mitre_technique, real_world_example, raw_log, playbook_solution, difficulty, status) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'new')`,
                        [
                            alert.title, alert.severity, alert.source, alert.details, 
                            alert.alert_type, alert.mitre_technique, 1, 
                            JSON.stringify(alert.raw_log), 
                            JSON.stringify(alert.playbook_solution),
                            difficulty_level || 'intermediate'
                        ]
                    );
                    const alertId = alertResult.insertId;

                    const report = generatedData.intel_reports?.find(r => r.sequence_order === (i + 1));
                    
                    await db.query(
                        `INSERT INTO operation_alerts 
                        (operation_id, alert_id, sequence_order, intel_report_title, intel_report_content) 
                        VALUES (?, ?, ?, ?, ?)`,
                        [operationId, alertId, i + 1, report?.title || null, report?.content || null]
                    );
                }

                // Update operation details from AI generation (including SEO)
                // We use the generated title/description for both main display and SEO fields
                await db.query(
                    `UPDATE operations 
                     SET total_alerts = ?, 
                         title = COALESCE(?, title), 
                         description = COALESCE(?, description),
                         story_intro = COALESCE(?, story_intro),
                         seo_title = ?,
                         seo_description = ?,
                         tags = COALESCE(?, tags)
                     WHERE id = ?`,
                    [
                        generatedData.alerts.length,
                        generatedData.operation.title,
                        generatedData.operation.description,
                        generatedData.operation.story_intro,
                        generatedData.operation.title, // Use title as SEO title
                        generatedData.operation.description, // Use description as SEO description
                        generatedData.operation.tags ? JSON.stringify(generatedData.operation.tags) : null,
                        operationId
                    ]
                );

                return res.status(201).json({ 
                    message: 'Operation created with AI-generated alerts',
                    operation_id: operationId,
                    ai_generated: true,
                    alerts_created: generatedData.alerts.length
                });
            } catch (aiError) {
                console.error('AI generation failed:', aiError);
                return res.status(201).json({ 
                    message: 'Operation created but AI generation failed: ' + aiError.message,
                    operation_id: operationId,
                    ai_generated: false,
                    ai_error: aiError.message
                });
            }
        }

        res.status(201).json({ 
            message: 'Operation created successfully', 
            operation_id: operationId 
        });
    } catch (error) {
        console.error('Error creating operation:', error);
        res.status(500).json({ message: 'Failed to create operation' });
    }
};

/**
 * Admin: Update operation
 */
const updateOperation = async (req, res) => {
    try {
        const { id } = req.params;
        const { 
            title, apt_group, description, story_intro, difficulty_level, is_active,
            is_premium, passing_grade, time_limit_hours, tags, scenario_prompt, display_order
        } = req.body;

        // Generate slug from title
        const slug = (title || 'untitled').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');

        await db.query(
            `UPDATE operations 
            SET title = ?, slug = ?, apt_group = ?, description = ?, story_intro = ?, 
                difficulty_level = ?, is_active = ?, is_premium = ?, passing_grade = ?,
                time_limit_hours = ?, tags = ?, scenario_prompt = ?, display_order = ?,
                seo_title = ?, seo_description = ?
            WHERE id = ?`,
            [
                title, slug, apt_group, description, story_intro, difficulty_level, is_active,
                is_premium, passing_grade, time_limit_hours, 
                tags ? JSON.stringify(tags) : null, scenario_prompt, display_order || 0, 
                req.body.seo_title || title, // Fallback to title if empty
                req.body.seo_description || description, // Fallback to description
                id
            ]
        );

        res.json({ message: 'Operation updated successfully' });
    } catch (error) {
        console.error('Error updating operation:', error);
        res.status(500).json({ message: 'Failed to update operation' });
    }
};

/**
 * Admin: Delete operation
 */
const deleteOperation = async (req, res) => {
    try {
        const { id } = req.params;

        // 1. Get linked alerts
        const alertsResult = await db.query('SELECT alert_id FROM operation_alerts WHERE operation_id = ?', [id]);
        const alertIds = alertsResult.rows.map(row => row.alert_id);

        // 2. Delete operation_alerts links
        await db.query('DELETE FROM operation_alerts WHERE operation_id = ?', [id]);

        // 3. Delete user progress
        await db.query('DELETE FROM user_operation_progress WHERE operation_id = ?', [id]);

        // 4. Delete the operation itself
        await db.query('DELETE FROM operations WHERE id = ?', [id]);

        // 5. Delete linked alerts? 
        // We usually want to delete the alerts too since they are specific to the operation
        if (alertIds.length > 0) {
            // Delete investigations related to these alerts
            const placeholders = alertIds.map(() => '?').join(',');
            await db.query(`DELETE FROM investigations WHERE alert_id IN (${placeholders})`, alertIds);
            
            // Delete the alerts
            await db.query(`DELETE FROM alerts WHERE id IN (${placeholders})`, alertIds);
        }

        res.json({ message: 'Operation and linked alerts deleted successfully' });
    } catch (error) {
        console.error('Error deleting operation:', error);
        res.status(500).json({ message: 'Failed to delete operation' });
    }
};

/**
 * Admin: Generate Operation with AI
 */
const generateOperationWithAI = async (req, res) => {
    try {
        const { generateOperation } = require('../services/operationGenerator');
        const { aptGroup, scenario, difficulty, alertCount } = req.body;
        
        // Use system API key from env or DB
        const apiKey = await getAIKey();

        if (!apiKey) {
            return res.status(500).json({ message: "Server configuration error: OPENAI_API_KEY missing" });
        }

        // 1. Generate Content via Service
        const generatedData = await generateOperation({
            aptGroup, 
            scenario, 
            difficulty, 
            alertCount: parseInt(alertCount) || 3, 
            apiKey
        });

        // 2. Insert Operation
        const op = generatedData.operation;
        const opResult = await db.query(
            `INSERT INTO operations 
            (title, apt_group, description, story_intro, difficulty_level, total_alerts, is_active) 
            VALUES (?, ?, ?, ?, ?, ?, TRUE)`,
            [op.title, aptGroup, op.description, op.story_intro, difficulty, generatedData.alerts.length]
        );
        const operationId = opResult.insertId || opResult.rows.insertId; // Handle different DB driver returns

        // 3. Insert Alerts & Link them
        for (let i = 0; i < generatedData.alerts.length; i++) {
            const alert = generatedData.alerts[i];
            
            // Insert Alert
            const alertResult = await db.query(
                `INSERT INTO alerts 
                (title, severity, source, details, alert_type, mitre_technique, real_world_example, raw_log, playbook_solution, difficulty, status) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'new')`,
                [
                    alert.title, 
                    alert.severity, 
                    alert.source, 
                    alert.details, 
                    alert.alert_type, 
                    alert.mitre_technique, 
                    1, 
                    JSON.stringify(alert.raw_log), 
                    JSON.stringify(alert.playbook_solution),
                    difficulty
                ]
            );
            const alertId = alertResult.insertId || alertResult.rows.insertId;

            // Find matching intel report for this step (if any)
            // Intel report i unlocks AFTER alert i is completed? 
            // The table has sequence_order via operation_alerts.
            // Let's store intel report for the NEXT step in the CURRENT step's link?
            // Actually `intel_report_title` in `operation_alerts` usually means "Report unlocked AFTER this alert is done"
            // So report i corresponds to alert i.
            
            const report = generatedData.intel_reports.find(r => r.sequence_order === (i + 1));
            
            // Link to Operation
            await db.query(
                `INSERT INTO operation_alerts 
                (operation_id, alert_id, sequence_order, intel_report_title, intel_report_content) 
                VALUES (?, ?, ?, ?, ?)`,
                [
                    operationId, 
                    alertId, 
                    i + 1,
                    report ? report.title : null,
                    report ? report.content : null
                ]
            );
        }

        res.json({ 
            message: 'Operation generated and saved successfully',
            operation: generatedData.operation,
            operation_id: operationId
        });

    } catch (error) {
        console.error('Error generating operation:', error);
        res.status(500).json({ message: 'AI Generation failed: ' + error.message });
    }
};

/**
 * Admin: List all operations with statistics
 */
const listOperationsAdmin = async (req, res) => {
    try {
        const operationsResult = await db.query(`
            SELECT 
                o.*,
                (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = o.id) as total_alerts,
                (SELECT COUNT(*) FROM user_operation_progress WHERE operation_id = o.id) as users_started,
                (SELECT COUNT(*) FROM user_operation_progress WHERE operation_id = o.id AND completed_at IS NOT NULL) as users_completed
            FROM operations o
            ORDER BY o.display_order ASC, o.created_at DESC
        `);

        res.json({ operations: operationsResult.rows });
    } catch (error) {
        console.error('Error listing operations for admin:', error);
        res.status(500).json({ message: 'Failed to list operations' });
    }
};

/**
 * Admin: Add alert to operation
 */
const addAlertToOperation = async (req, res) => {
    try {
        const { id } = req.params; // operation_id
        const { alert_id, sequence_order, intel_report_title, intel_report_content } = req.body;

        if (!alert_id) {
            return res.status(400).json({ message: 'alert_id is required' });
        }

        // Get max sequence if not provided
        let seq = sequence_order;
        if (!seq) {
            const maxSeqResult = await db.query(
                'SELECT MAX(sequence_order) as max_seq FROM operation_alerts WHERE operation_id = ?',
                [id]
            );
            seq = (maxSeqResult.rows[0]?.max_seq || 0) + 1;
        }

        await db.query(
            `INSERT INTO operation_alerts 
            (operation_id, alert_id, sequence_order, intel_report_title, intel_report_content) 
            VALUES (?, ?, ?, ?, ?)`,
            [id, alert_id, seq, intel_report_title || null, intel_report_content || null]
        );

        // Update total_alerts count
        await db.query(
            'UPDATE operations SET total_alerts = (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = ?) WHERE id = ?',
            [id, id]
        );

        res.json({ message: 'Alert added to operation', sequence_order: seq });
    } catch (error) {
        console.error('Error adding alert to operation:', error);
        res.status(500).json({ message: 'Failed to add alert' });
    }
};

/**
 * Admin: Remove alert from operation
 */
const removeAlertFromOperation = async (req, res) => {
    try {
        const { id, alertId } = req.params;
        const { delete_alert } = req.query; // ?delete_alert=true to also delete the alert

        await db.query(
            'DELETE FROM operation_alerts WHERE operation_id = ? AND alert_id = ?',
            [id, alertId]
        );

        // Resequence remaining alerts
        const remaining = await db.query(
            'SELECT id, alert_id FROM operation_alerts WHERE operation_id = ? ORDER BY sequence_order ASC',
            [id]
        );
        for (let i = 0; i < remaining.rows.length; i++) {
            await db.query(
                'UPDATE operation_alerts SET sequence_order = ? WHERE id = ?',
                [i + 1, remaining.rows[i].id]
            );
        }

        // Update total_alerts count
        await db.query(
            'UPDATE operations SET total_alerts = (SELECT COUNT(*) FROM operation_alerts WHERE operation_id = ?) WHERE id = ?',
            [id, id]
        );

        // Optionally delete the alert itself
        if (delete_alert === 'true') {
            await db.query('DELETE FROM investigations WHERE alert_id = ?', [alertId]);
            await db.query('DELETE FROM alerts WHERE id = ?', [alertId]);
        }

        res.json({ message: 'Alert removed from operation' });
    } catch (error) {
        console.error('Error removing alert from operation:', error);
        res.status(500).json({ message: 'Failed to remove alert' });
    }
};

/**
 * Admin: Reorder alerts in operation
 */
const reorderAlerts = async (req, res) => {
    try {
        const { id } = req.params;
        const { order } = req.body; // Array of alert_ids in desired order

        if (!Array.isArray(order)) {
            return res.status(400).json({ message: 'order must be an array of alert_ids' });
        }

        for (let i = 0; i < order.length; i++) {
            await db.query(
                'UPDATE operation_alerts SET sequence_order = ? WHERE operation_id = ? AND alert_id = ?',
                [i + 1, id, order[i]]
            );
        }

        res.json({ message: 'Alerts reordered successfully' });
    } catch (error) {
        console.error('Error reordering alerts:', error);
        res.status(500).json({ message: 'Failed to reorder alerts' });
    }
};

/**
 * Admin: Get operation statistics
 */
const getOperationStats = async (req, res) => {
    try {
        const { id } = req.params;

        // Basic stats
        const statsResult = await db.query(`
            SELECT 
                (SELECT COUNT(*) FROM user_operation_progress WHERE operation_id = ?) as users_started,
                (SELECT COUNT(*) FROM user_operation_progress WHERE operation_id = ? AND completed_at IS NOT NULL) as users_completed
        `, [id, id]);

        // Per-alert stats
        const alertStatsResult = await db.query(`
            SELECT 
                oa.sequence_order,
                oa.alert_id,
                a.title as alert_title,
                COUNT(DISTINCT i.user_id) as users_attempted,
                AVG(i.grade) as avg_grade,
                SUM(CASE WHEN i.grade >= 60 THEN 1 ELSE 0 END) as users_passed
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            LEFT JOIN investigations i ON oa.alert_id = i.alert_id
            WHERE oa.operation_id = ?
            GROUP BY oa.sequence_order, oa.alert_id, a.title
            ORDER BY oa.sequence_order
        `, [id]);

        res.json({
            ...statsResult.rows[0],
            alert_stats: alertStatsResult.rows
        });
    } catch (error) {
        console.error('Error getting operation stats:', error);
        res.status(500).json({ message: 'Failed to get stats' });
    }
};

/**
 * Admin: Get single operation for editing
 */
const getOperationForEdit = async (req, res) => {
    try {
        const { id } = req.params;

        // Get operation details
        const opResult = await db.query('SELECT * FROM operations WHERE id = ?', [id]);
        if (opResult.rows.length === 0) {
            return res.status(404).json({ message: 'Operation not found' });
        }

        // Get linked alerts
        const alertsResult = await db.query(`
            SELECT 
                oa.*,
                a.id as alert_id,
                a.title as alert_title,
                a.severity,
                a.alert_type
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            WHERE oa.operation_id = ?
            ORDER BY oa.sequence_order ASC
        `, [id]);

        res.json({
            operation: opResult.rows[0],
            alerts: alertsResult.rows
        });
    } catch (error) {
        console.error('Error getting operation for edit:', error);
        res.status(500).json({ message: 'Failed to get operation' });
    }
};

/**
 * Get completion report for a finished operation
 * Returns operation details, all mission grades, and overall score
 */
const getCompletionReport = async (req, res) => {
    try {
        const { id } = req.params;
        const userId = Number(req.user.id);

        // 1. Get operation details
        const opResult = await db.query('SELECT * FROM operations WHERE id = ?', [id]);
        if (opResult.rows.length === 0) {
            return res.status(404).json({ message: 'Operation not found' });
        }
        const operation = opResult.rows[0];

        // 2. Check user progress & completion
        const progressResult = await db.query(
            'SELECT * FROM user_operation_progress WHERE user_id = ? AND operation_id = ?',
            [userId, id]
        );
        
        if (progressResult.rows.length === 0) {
            return res.status(404).json({ message: 'No progress found for this operation' });
        }
        const progress = progressResult.rows[0];

        // 3. Get all missions with grades
        const missionsResult = await db.query(`
            SELECT 
                oa.sequence_order,
                oa.alert_id,
                a.title as alert_title,
                a.severity,
                a.alert_type,
                i.grade,
                i.status as investigation_status,
                i.updated_at as graded_at
            FROM operation_alerts oa
            JOIN alerts a ON oa.alert_id = a.id
            LEFT JOIN investigations i ON oa.alert_id = i.alert_id AND i.user_id = ?
            WHERE oa.operation_id = ?
            ORDER BY oa.sequence_order ASC
        `, [userId, id]);

        // 4. Calculate overall score
        const completedMissions = missionsResult.rows.filter(m => m.grade !== null);
        const totalScore = completedMissions.reduce((sum, m) => sum + (m.grade || 0), 0);
        const averageScore = completedMissions.length > 0 
            ? Math.round(totalScore / completedMissions.length) 
            : 0;
        
        // 5. Determine pass/fail status
        const passingGrade = operation.passing_grade || 60;
        const passed = averageScore >= passingGrade;

        res.json({
            operation: {
                id: operation.id,
                title: operation.title,
                apt_group: operation.apt_group,
                description: operation.description,
                story_intro: operation.story_intro,
                difficulty_level: operation.difficulty_level,
                passing_grade: passingGrade
            },
            progress: {
                started_at: progress.started_at,
                completed_at: progress.completed_at,
                is_completed: progress.completed_at !== null
            },
            missions: missionsResult.rows.map(m => ({
                sequence: m.sequence_order,
                alert_id: m.alert_id,
                title: m.alert_title,
                severity: m.severity,
                type: m.alert_type,
                grade: m.grade,
                status: m.investigation_status || 'not_started',
                graded_at: m.graded_at
            })),
            summary: {
                total_missions: missionsResult.rows.length,
                completed_missions: completedMissions.length,
                total_score: totalScore,
                average_score: averageScore,
                passed: passed,
                result: passed ? 'SUCCESS' : 'NEEDS_IMPROVEMENT'
            }
        });
    } catch (error) {
        console.error('Error getting completion report:', error);
        res.status(500).json({ message: 'Failed to get completion report' });
    }
};

module.exports = {
    listOperations,
    listPublicOperations, // Public list
    getPublicOperationDetails, // Public detail (SEO)
    getGuestOperationAlert, // Guest access to alert
    gradeGuestInvestigation, // Guest grading
    getOperationDetails,
    startOperation,
    assignDailyMission,
    getIntelReports,
    advanceOperationProgress,
    createOperation,
    updateOperation,
    deleteOperation,
    generateOperationWithAI,
    // Admin endpoints
    listOperationsAdmin,
    addAlertToOperation,
    removeAlertFromOperation,
    reorderAlerts,
    getOperationStats,
    getOperationForEdit,
    getCompletionReport
};
