const db = require('../db');
const xpService = require('../utils/xpService');
const { formatUserUrls } = require('../utils/urlHelper');
const operationController = require('./operationController');

/**
 * GET /api/mobile/triage-status
 * Check if user has already completed triage today
 */
exports.getTriageStatus = async (req, res) => {
  const userId = req.user.id;
  
  try {
    // Check subscription plan
    const userResult = await db.query('SELECT subscription_plan FROM users WHERE id = ?', [userId]);
    const plan = userResult.rows?.[0]?.subscription_plan || 'free';
    const isPro = plan === 'pro' || plan === 'monthly' || plan === 'yearly';
    
    // Limits: 3 per day for Triage Trainer
    const DAILY_LIMIT = 3;
    
    let completedToday = false;
    let gamesPlayedToday = 0;
    
    try {
      // Use played_at DESC LIMIT 1 for timezone safe check
      const result = await db.query(`
        SELECT COUNT(*) as count FROM mobile_triage_results 
        WHERE user_id = ? AND game_type = 'triage' AND DATE(played_at) = CURDATE()
      `, [userId]);
      
      gamesPlayedToday = result.rows[0].count || 0;
      completedToday = gamesPlayedToday >= DAILY_LIMIT;
    } catch (e) {
      // Table may not exist
    }
    
    res.json({
      completedToday,
      gamesPlayedToday,
      dailyLimit: DAILY_LIMIT,
      isPro
    });
  } catch (error) {
    console.error('Error checking triage status:', error);
    res.status(500).json({ message: 'Failed to check status' });
  }
};

/**
 * POST /api/mobile/triage-submit
 * Submits triage game results and awards XP
 */
exports.submitTriageResults = async (req, res) => {
  const userId = req.user.id;
  const { score, correctCount, totalCards, durationSeconds, gameType } = req.body;

  try {
    // Validate input
    if (totalCards === undefined || correctCount === undefined) {
      return res.status(400).json({ message: 'Missing required fields: correctCount, totalCards' });
    }

    // Check subscription plan
    const userResult = await db.query('SELECT subscription_plan FROM users WHERE id = ?', [userId]);
    const plan = userResult.rows?.[0]?.subscription_plan || 'free';
    const isPro = plan === 'pro' || plan === 'monthly' || plan === 'yearly';

    // Check daily limit
    // Check daily limit
    const gameTypeStr = gameType || 'triage';
    let DAILY_LIMIT = 3; // Default for triage/quiz/other

    let dailyCount = 0;
    try {
      const dailyResult = await db.query(`
        SELECT COUNT(*) as count FROM mobile_triage_results 
        WHERE user_id = ? AND game_type = ? AND DATE(played_at) = CURDATE()
      `, [userId, gameTypeStr]);
      
      dailyCount = dailyResult.rows[0].count || 0;
    } catch (e) {
      // Table may not exist
    }

    const dailyLimitReached = dailyCount >= DAILY_LIMIT;

    // Calculate XP reward based on performance
    const accuracy = totalCards > 0 ? (correctCount / totalCards) : 0;
    let xpReward = 0;
    
    // Only award XP if at least 20% accuracy AND not past daily limit
    if (!dailyLimitReached && accuracy >= 0.2) {
      if (accuracy >= 0.9) {
        xpReward = 50; // Excellent
      } else if (accuracy >= 0.7) {
        xpReward = 30; // Good
      } else if (accuracy >= 0.5) {
        xpReward = 15; // Passing
      } else {
        xpReward = 5; // Minimum effort (20-49% correct)
      }

      // Bonus XP for speed (under 2 minutes)
      if (durationSeconds && durationSeconds < 120 && accuracy >= 0.7) {
        xpReward += 10;
      }
    }

    // Save result to database
    try {
      await db.query(`
        INSERT INTO mobile_triage_results (user_id, score, correct_count, total_cards, duration_seconds, xp_earned, game_type)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `, [userId, score || 0, correctCount, totalCards, durationSeconds || null, xpReward, gameTypeStr]);
    } catch (insertErr) {
      console.warn('Could not save result (table may not exist):', insertErr.message);
    }

    // Award XP only if earned
    if (xpReward > 0) {
      await xpService.awardXP(userId, xpReward, gameTypeStr);
      await xpService.updateStreak(userId);
    }

    // Mark alerts as completed in user_alert_assignments
    if (req.body.processedAlertIds && Array.isArray(req.body.processedAlertIds) && req.body.processedAlertIds.length > 0) {
      const ids = req.body.processedAlertIds.filter(id => Number.isInteger(id));
      if (ids.length > 0) {
        // Safe parameter expansion for IN clause
        const placeholders = ids.map(() => '?').join(',');
        try {
          await db.query(
            `UPDATE user_alert_assignments 
             SET completed = 1, completed_at = NOW() 
             WHERE user_id = ? AND alert_id IN (${placeholders})`,
            [userId, ...ids]
          );
        } catch (updateErr) {
          console.error('Failed to mark alerts as completed:', updateErr);
        }
      }
    }

    // Get updated user stats
    const userStats = await xpService.getUserStats(userId);

    res.json({
      message: xpReward > 0 ? 'Results submitted successfully!' : (dailyLimitReached ? 'Daily limit reached!' : 'Try to get at least 20% correct!'),
      xpEarned: xpReward,
      accuracy: Math.round(accuracy * 100),
      dailyLimitReached,
      newStats: userStats
    });

  } catch (error) {
    console.error('Error submitting results:', error);
    res.status(500).json({ message: 'Failed to submit results' });
  }
};

/**
 * GET /api/mobile/dashboard
 * Returns aggregated stats for mobile dashboard
 */
exports.getDashboard = async (req, res) => {
  const userId = req.user.id;

  try {
    // 0. Get user's level/xp and basic info
    const userStats = await xpService.getUserStats(userId);
    const userInfo = await db.query('SELECT created_at FROM users WHERE id = ?', [userId]);
    const createdAt = userInfo.rows?.[0]?.created_at;

    // 1. Get Flags Found (Graded investigations)
    const flagsResult = await db.query(
      "SELECT COUNT(*) as count FROM investigations WHERE user_id = ? AND status = 'graded'",
      [userId]
    );
    const flagsFound = flagsResult.rows?.[0]?.count || 0;

    // 2. Get Global Rank
    const rankResult = await db.query(`
      SELECT COUNT(*) + 1 as rank
      FROM user_xp ux
      JOIN users u ON ux.user_id = u.id
      WHERE ux.total_xp > COALESCE((SELECT total_xp FROM user_xp WHERE user_id = ?), 0)
      AND u.role != 'admin' AND u.username != 'admin'
    `, [userId]);
    const globalRank = rankResult.rows?.[0]?.rank || 1;

    // Extend userStats with new production data
    const enrichedStats = {
      ...userStats,
      flagsFound,
      globalRank: `#${globalRank}`,
      joinedDate: createdAt ? new Date(createdAt).toLocaleDateString('en-US', { month: 'short', year: 'numeric' }) : 'Jan 2025'
    };

    // Get user's enrolled learning path
    let userPath = null;
    try {
      const pathResult = await db.query(`
        SELECT lp.id, lp.title, lp.description, upp.percentage_completed
        FROM user_path_progress upp
        JOIN learning_paths lp ON upp.learning_path_id = lp.id
        WHERE upp.user_id = ?
        ORDER BY upp.enrolled_at DESC
        LIMIT 1
      `, [userId]);
      if (pathResult.rows.length > 0) {
        userPath = pathResult.rows[0];
      }
    } catch (e) {
      // Table may not exist
    }

    // Get today's triage progress
    let triageCountToday = 0;
    let recentTriageResult = null;
    try {
      const result = await db.query(`
        SELECT score, correct_count, total_cards, xp_earned, played_at
        FROM mobile_triage_results
        WHERE user_id = ? AND game_type = 'triage'
        ORDER BY played_at DESC
      `, [userId]);
      
      if (result.rows.length > 0) {
        recentTriageResult = result.rows[0];
        // Count how many were today
        const now = new Date().toDateString();
        triageCountToday = result.rows.filter(r => new Date(r.played_at).toDateString() === now).length;
      }
    } catch (e) {
      // Table may not exist
    }

    // Get active alerts (My Investigations + Assignments)
    let activeAlerts = [];
    try {
      console.log(`[Mobile Dashboard] Fetching active alerts for user_id: ${userId}`);
      
      // Priority 1: Active Investigations (Manual Assignments)
      const investigationsResult = await db.query(`
        SELECT a.id, a.title, a.severity, a.source, a.created_at, a.details, i.status,
               (SELECT oa.operation_id FROM operation_alerts oa 
                JOIN user_operation_progress uop ON oa.operation_id = uop.operation_id
                WHERE oa.alert_id = a.id AND uop.user_id = i.user_id 
                AND uop.current_alert_sequence = oa.sequence_order
                LIMIT 1) as operation_id
        FROM investigations i
        JOIN alerts a ON i.alert_id = a.id
        WHERE i.user_id = ? 
        AND i.status IN ('investigating', 'in progress', 'new')
        AND a.status NOT IN ('closed', 'resolved')
        ORDER BY i.updated_at DESC
        LIMIT 5
      `, [userId]);
      
      console.log(`[Mobile Dashboard] Investigations found: ${investigationsResult.rows?.length || 0}`);
      
      if (investigationsResult.rows && investigationsResult.rows.length > 0) {
        activeAlerts = investigationsResult.rows;
        console.log(`[Mobile Dashboard] Using ${activeAlerts.length} investigations`);
      } else {
        // Priority 2: Daily Queue (Automated Assignments) if no manual investigations
        console.log(`[Mobile Dashboard] No investigations, checking queue...`);
        const queueResult = await db.query(`
            SELECT a.id, a.title, a.severity, a.source, a.created_at, a.details
            FROM user_alert_assignments uaa
            JOIN alerts a ON uaa.alert_id = a.id
            WHERE uaa.user_id = ? 
            AND (uaa.completed = 0)
            AND a.status NOT IN ('closed', 'resolved')
            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 AND i.user_id = ? AND i.status IN ('completed', 'graded', 'closed', 'resolved'))
            ORDER BY a.severity DESC
            LIMIT 3
        `, [userId, userId]);
        activeAlerts = queueResult.rows || [];
        console.log(`[Mobile Dashboard] Queue alerts found: ${activeAlerts.length}`);
      }

      // Priority 3: Pool Alerts (for new users who have nothing assigned and no queue)
      if (activeAlerts.length === 0) {
          console.log(`[Mobile Dashboard] No personal alerts. Fetching from pool for new user...`);
          // Only need userLevel here since assignmentService has its own getUserLevel,
          // but let's grab it for the pool fallback below just in case.
          const userLevelResult = await db.query('SELECT current_level FROM user_xp WHERE user_id = ?', [userId]);
          const userLevel = userLevelResult.rows.length > 0 ? userLevelResult.rows[0].current_level : 1;

          // 3. Fallback: Pool Alerts (If no queue or operation alerts)
          // Ensure they haven't completed it
          if (activeAlerts.length === 0) { 
              const { getAllowedDifficulties } = require('../services/assignmentService');
              const allowedDifficulties = getAllowedDifficulties(userLevel);
              const diffStrings = allowedDifficulties.map(d => `'${d}'`).join(',');

              // Fetch mixed difficulty pool alerts they haven't done
              const poolResult = await db.query(`
                  SELECT a.id, a.title, a.severity, a.difficulty as min_level, 'pool' as assignment_type,
                         CAST(NULL AS CHAR) as operation_name
                  FROM alerts a
                  WHERE a.status IN ('new', 'New')
                  AND (a.difficulty IN (${diffStrings}) OR a.difficulty IS NULL)
                  AND NOT EXISTS (
                      SELECT 1 FROM investigations i 
                      WHERE i.alert_id = a.id AND i.user_id = ? AND i.status IN ('completed', 'graded', 'closed', 'resolved')
                  )
                  ORDER BY RAND()
                  LIMIT 5
              `, [userId]);
            
              activeAlerts = poolResult.rows || [];
              console.log(`[Mobile Dashboard] Pool alerts found: ${activeAlerts.length}`);
          }
      }
    } catch (e) {
      console.error('[Mobile Dashboard] Error fetching alerts:', e.message, e.stack);
    }

    res.json({
      stats: enrichedStats,
      learningPath: userPath,
      recentTriage: recentTriageResult,
      dailyGoal: {
        triageCompleted: Math.min(3, triageCountToday),
        triageTarget: 3
      },
      activeAlerts: activeAlerts
    });

  } catch (error) {
    console.error('Mobile dashboard error:', error);
    res.status(500).json({ message: 'Failed to fetch dashboard' });
  }
};

/**
 * POST /api/mobile/push-token
 * Updates the user's Expo Push Token
 */
exports.updatePushToken = async (req, res) => {
  const userId = req.user.id;
  const { token } = req.body;

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

  try {
    await db.query('UPDATE users SET expo_push_token = ? WHERE id = ?', [token, userId]);
    res.json({ message: 'Push token updated successfully' });
  } catch (error) {
    console.error('Error updating push token:', error);
    res.status(500).json({ message: 'Failed to update push token' });
  }
};

/**
 * GET /api/mobile/quiz-questions
 * Returns 5 random quiz questions from the database
 */
exports.getQuizQuestions = async (req, res) => {
  try {
    // Fetch random questions that have options (multiple choice)
    const result = await db.query(`
      SELECT lq.id, lq.question_text, lq.options, lq.correct_answer, lq.hint, 
             t.title as task_title, m.title as module_title
      FROM lesson_questions lq
      JOIN tasks t ON lq.task_id = t.id
      JOIN modules m ON t.module_id = m.id
      WHERE lq.options IS NOT NULL AND lq.options != ''
      ORDER BY RAND()
      LIMIT 5
    `);

    if (result.rows.length === 0) {
      return res.json({ questions: [], message: 'No questions available' });
    }

    // Format questions for mobile app
    const questions = result.rows.map((q, index) => {
      let options = [];
      try {
        // Options are stored as JSON string
        options = typeof q.options === 'string' ? JSON.parse(q.options) : q.options;
        if (!Array.isArray(options)) options = [];
      } catch (e) {
        options = [];
      }

      // Find correct answer index
      let correctIndex = 0;
      const correctAnswer = q.correct_answer?.toLowerCase().trim();
      options.forEach((opt, i) => {
        if (opt.toLowerCase().trim() === correctAnswer) {
          correctIndex = i;
        }
      });

      return {
        id: q.id,
        question: q.question_text,
        options: options,
        correct: correctIndex,
        category: q.module_title || 'Security',
        hint: q.hint || null
      };
    });

    res.json({ questions });

  } catch (error) {
    console.error('Error fetching quiz questions:', error);
    res.status(500).json({ message: 'Failed to fetch questions' });
  }
};

/**
 * GET /api/mobile/lessons
 * Returns today's micro-learning lessons (Limited for Free users)
 */
exports.getMicroLessons = async (req, res) => {
  const userId = req.user.id;

  try {
    // Check subscription plan
    const userResult = await db.query('SELECT subscription_plan FROM users WHERE id = ?', [userId]);
    const plan = userResult.rows?.[0]?.subscription_plan || 'free';
    const isPro = plan === 'pro' || plan === 'monthly' || plan === 'yearly';
    
    // Limits: Free = 3, Pro = 5
    const DAILY_LESSON_LIMIT = isPro ? 5 : 3;
    // Ensure table exists
    try {
      await db.query(`
        CREATE TABLE IF NOT EXISTS mobile_lesson_completions (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          lesson_id INT NOT NULL,
          xp_earned INT DEFAULT 0,
          completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_user_lesson (user_id, lesson_id),
          INDEX idx_user_date (user_id, completed_at)
        )
      `);
    } catch (tableErr) {
      // Table might already exist
      console.log('Table exists or error:', tableErr.message);
    }
    
    // Get today's date string for consistent seeding
    const today = new Date().toISOString().split('T')[0]; // YYYY-MM-DD
    
    // Check which lessons user has completed today
    let completedToday = [];
    try {
      const completedResult = await db.query(`
        SELECT lesson_id FROM mobile_lesson_completions 
        WHERE user_id = ? AND DATE(completed_at) = CURDATE()
      `, [userId]);
      completedToday = (completedResult.rows || []).map(r => r.lesson_id);
    } catch (e) {
      console.log('Completion check error:', e.message);
    }

    // Check if user has completed their daily quota
    const allDone = completedToday.length >= DAILY_LESSON_LIMIT;

    // Get today's lessons using a seed based on date+userId for consistency
    const seed = parseInt(today.replace(/-/g, '')) + userId;
    
    const result = await db.query(`
      SELECT lc.id, lc.task_id, t.title, lc.content, lc.reading_time_minutes,
             m.title as module_title
      FROM lesson_content lc
      JOIN tasks t ON lc.task_id = t.id
      JOIN modules m ON t.module_id = m.id
      WHERE lc.reading_time_minutes IS NOT NULL AND lc.reading_time_minutes <= 5
      ORDER BY RAND(?)
      LIMIT ?
    `, [seed, DAILY_LESSON_LIMIT]);

    if (!result.rows || result.rows.length === 0) {
      return res.json({ lessons: [], allDone: false, completedCount: 0, dailyLimit: DAILY_LESSON_LIMIT });
    }

    // Mark completion status for each lesson
    const lessons = result.rows.map((lesson) => {
      const isCompleted = completedToday.includes(lesson.id);

      // Get first 150 chars as preview
      let preview = lesson.content || '';
      preview = preview.replace(/[#*`]/g, '').substring(0, 150) + '...';

      return {
        id: lesson.id,
        task_id: lesson.task_id,
        title: lesson.title,
        category: lesson.module_title || 'Security',
        readTime: lesson.reading_time_minutes || 3,
        xp: (lesson.reading_time_minutes || 3) * 5,
        completed: isCompleted,
        content: lesson.content,
        preview: preview
      };
    });

    res.json({ 
      lessons, 
      allDone,
      completedCount: completedToday.length,
      dailyLimit: DAILY_LESSON_LIMIT
    });

  } catch (error) {
    console.error('Error fetching micro lessons:', error);
    res.status(500).json({ message: 'Failed to fetch lessons' });
  }
};

/**
 * POST /api/mobile/lessons/:lessonId/complete
 * Marks a lesson as completed for today and awards XP
 */
exports.completeLesson = async (req, res) => {
  const userId = req.user.id;
  const { lessonId } = req.params;
  const { xp } = req.body;

  try {
    // Check if already completed today
    let alreadyCompleted = false;
    try {
      const checkResult = await db.query(`
        SELECT id FROM mobile_lesson_completions 
        WHERE user_id = ? AND lesson_id = ? AND DATE(completed_at) = CURDATE()
      `, [userId, lessonId]);
      alreadyCompleted = checkResult.rows.length > 0;
    } catch (e) {
      // Table may not exist, create it
      try {
        await db.query(`
          CREATE TABLE IF NOT EXISTS mobile_lesson_completions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            lesson_id INT NOT NULL,
            xp_earned INT DEFAULT 0,
            completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_date (user_id, completed_at)
          )
        `);
      } catch (createErr) {
        console.warn('Could not create table:', createErr.message);
      }
    }

    if (alreadyCompleted) {
      return res.json({ message: 'Already completed today', xpEarned: 0 });
    }

    // Save completion
    const xpReward = xp || 15;
    try {
      await db.query(`
        INSERT INTO mobile_lesson_completions (user_id, lesson_id, xp_earned)
        VALUES (?, ?, ?)
      `, [userId, lessonId, xpReward]);
    } catch (insertErr) {
      console.warn('Could not save completion:', insertErr.message);
    }

    // Award XP
    await xpService.awardXP(userId, xpReward, 'microlearning');
    await xpService.updateStreak(userId);

    // Get updated stats
    const userStats = await xpService.getUserStats(userId);

    // Check how many completed today
    let completedCount = 0;
    try {
      const countResult = await db.query(`
        SELECT COUNT(*) as count FROM mobile_lesson_completions 
        WHERE user_id = ? AND DATE(completed_at) = CURDATE()
      `, [userId]);
      completedCount = countResult.rows?.[0]?.count || 0;
    } catch (e) {}

    res.json({
      message: 'Lesson completed!',
      xpEarned: xpReward,
      completedCount,
      allDone: completedCount >= 5,
      newStats: userStats
    });

  } catch (error) {
    console.error('Error completing lesson:', error);
    res.status(500).json({ message: 'Failed to complete lesson' });
  }
};

/**
 * GET /api/mobile/leaderboard
 * Returns global leaderboard sorted by Total XP
 */
exports.getLeaderboard = async (req, res) => {
  const userId = req.user.id;
  try {
    // 1. Get Top 100
    const result = await db.query(`
      SELECT u.id, u.username, u.profile_picture, u.role,
             ux.current_level, ux.total_xp, ux.streak_days
      FROM user_xp ux
      JOIN users u ON ux.user_id = u.id
      WHERE u.role != 'admin' AND u.username != 'admin'
      ORDER BY ux.total_xp DESC
      LIMIT 10
    `);

    const leaderboard = (result.rows || []).map((row, index) => formatUserUrls({
      rank: index + 1,
      id: row.id,
      username: row.username,
      avatar: row.profile_picture,
      role: row.role,
      level: row.current_level,
      xp: row.total_xp,
      streak: row.streak_days,
      isCurrentUser: row.id === userId
    }));

    // 2. Find current user rank if not in top 100
    let userRank = leaderboard.find(r => r.id === userId);
    
    if (!userRank) {
      // Calculate rank: Count users with MORE xp than current user
      // Use COALESCE to handle case where user has no XP record (treat as 0)
      const rankResult = await db.query(`
        SELECT COUNT(*) + 1 as rank
        FROM user_xp ux
        JOIN users u ON ux.user_id = u.id
        WHERE ux.total_xp > COALESCE((SELECT total_xp FROM user_xp WHERE user_id = ?), 0)
        AND u.role != 'admin' AND u.username != 'admin'
      `, [userId]);
      
      const rank = rankResult.rows?.[0]?.rank || (leaderboard.length + 1);

      // Get user stats (handles missing record internally)
      const userStats = await xpService.getUserStats(userId);
      const userDetails = await db.query('SELECT username, profile_picture, role FROM users WHERE id = ?', [userId]);
      
      const user = userDetails.rows?.[0] || { username: 'Agent', profile_picture: null, role: 'Rookie' };
      
      userRank = formatUserUrls({
        rank: parseInt(rank),
        id: userId,
        username: user.username,
        avatar: user.profile_picture,
        role: user.role,
        level: userStats.level,
        xp: userStats.totalXP,
        streak: userStats.streakDays,
        isCurrentUser: true
      });
    }

    res.json({ leaderboard, userRank });
  } catch (error) {
    console.error('Error fetching leaderboard:', error);
    // Return empty leaderboard instead of error to prevent app crash
    res.json({ leaderboard: [], userRank: null }); 
  }
};

/**
 * POST /api/mobile/field-ops-submit
 * Submits field ops action from mobile app
 * - Updates alert status
 * - Marks queue assignment as completed
 * - Awards XP
 */
exports.submitFieldOps = async (req, res) => {
  const userId = req.user.id;
  const { alert_id, action_type, verdict, operation_id, score } = req.body;
  
  let opProgress = null;
  let intelReport = null;
  let xpEarned = 0;

  try {
    console.log(`[Field Ops] DEBUG: Entering submitFieldOps for User ${userId}, Alert ${alert_id}`);
    if (!alert_id || !action_type) {
      console.log(`[Field Ops] DEBUG: Missing fields. alert_id: ${alert_id}, action_type: ${action_type}`);
      return res.status(400).json({ message: 'Missing required fields: alert_id, action_type' });
    }

    const parsedAlertId = parseInt(alert_id);
    if (isNaN(parsedAlertId)) {
        return res.status(400).json({ message: 'Invalid alert_id' });
    }

    // Check free tier limit
    const usageService = require('../utils/usageService');
    const usageCheck = await usageService.enforceFreeTierLimit(userId);
    if (!usageCheck.allowed) {
        return res.status(403).json({ success: false, message: usageCheck.message });
    }

    console.log(`[Field Ops] User ${userId} submitting action '${action_type}' for alert ${parsedAlertId}`);

    // 1. Determine new status (Local to investigation)
    const newStatus = verdict === 'false_positive' ? 'closed' : 'resolved';
    
    // NOTE: Removed global UPDATE alerts SET status = ? WHERE id = ? 
    // to preserve multi-user isolation on the same alert.

    // 2. Mark user_alert_assignment as completed (syncs with Today's Queue)
    try {
      await db.query(
        'UPDATE user_alert_assignments SET completed = 1, completed_at = NOW() WHERE user_id = ? AND alert_id = ?',
        [userId, parsedAlertId]
      );
    } catch (e) {
      // Assignment may not exist
    }

    // 3. Update or create investigation record - CRITICAL FOR MISSION PROGRESS
    const finalGrade = score !== undefined ? score : 60;
    try {
      console.log(`[Field Ops] DEBUG: Attempting to mark investigation as COMPLETED for Alert ${parsedAlertId} / User ${userId} with grade ${finalGrade}`);
      const existingInv = await db.query(
        'SELECT id, status FROM investigations WHERE user_id = ? AND alert_id = ?',
        [userId, parsedAlertId]
      );
      
      if (existingInv.rows?.length > 0) {
        console.log(`[Field Ops] DEBUG: Found existing investigation (${existingInv.rows[0].id}). Updating status to COMPLETED with grade ${finalGrade}.`);
        await db.query(
          'UPDATE investigations SET status = ?, verdict = ?, grade = ?, updated_at = NOW() WHERE user_id = ? AND alert_id = ?',
          ['completed', verdict || 'true_positive', finalGrade, userId, parsedAlertId]
        );
      } else {
        console.log(`[Field Ops] DEBUG: No existing investigation. Creating new COMPLETED record with grade ${finalGrade}.`);
        await db.query(
          'INSERT INTO investigations (user_id, alert_id, status, verdict, grade, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW())',
          [userId, parsedAlertId, 'completed', verdict || 'true_positive', finalGrade]
        );
      }
      console.log(`[Field Ops] DEBUG: Investigation status successfully updated to COMPLETED for Jacob (User ${userId})`);
    } catch (e) {
      console.error('[Field Ops] ERROR: Could not update investigation status:', e.message);
    }

    // 4. Award XP for completing field ops (Halved for mobile by user request)
    try {
      // Different XP based on action type
      const xpMap = {
        block_ip: 15,
        block_url: 15,
        block_hash: 10,
        isolate_host: 20,
        reset_credentials: 10,
        collect_forensics: 15,
        escalate: 10,
        close_alert: 5,
        dismiss: 5,
        investigation_complete: 50
      };
      xpEarned = xpMap[action_type] || 10;
      
      await xpService.awardXP(userId, xpEarned, `Field Ops: ${action_type}`);
      
      // 4.5. Advance operation progress (Failsafe: Even if operation_id is missing, find it!)
      if (action_type === 'investigation_complete') {
        try {
          console.log(`[Field Ops] Triggering Failsafe Progression call for Alert ${parsedAlertId} (User ${userId})`);
          opProgress = await operationController.advanceOperationProgress(userId, parsedAlertId, finalGrade, { minScore: 30 });
          console.log(`[Field Ops] Progression result:`, opProgress);
          
          if (opProgress?.operationId) {
            const opId = opProgress.operationId;
            // Fetch the intel report for this alert
            const intelRes = await db.query(
                'SELECT intel_report_title, intel_report_content FROM operation_alerts WHERE operation_id = ? AND alert_id = ?',
                [opId, parsedAlertId]
            );
            if (intelRes.rows?.length > 0) {
              intelReport = intelRes.rows[0];
            }
          }
        } catch (opErr) {
          console.error('[Field Ops] Progression error:', opErr.message);
        }
      }
    } catch (e) {
      console.log('Could not award XP or advance progress:', e.message);
    }

    // 5. Log the field ops action
    try {
      await db.query(`
        INSERT INTO mobile_triage_results (user_id, score, correct_count, total_cards, game_type, played_at)
        VALUES (?, ?, 1, 1, 'field_ops', NOW())
      `, [userId, xpEarned]);

      // Check and award Mobile Badges (Field Operative, On The Go)
      const mobileCountRes = await db.query(
        "SELECT COUNT(*) as count FROM mobile_triage_results WHERE user_id = ? AND game_type = 'field_ops'",
        [userId]
      );
      const count = mobileCountRes.rows?.[0]?.count || 0;

      const badgesToAward = [];
      if (count >= 1) badgesToAward.push('Field Operative');
      if (count >= 10) badgesToAward.push('On The Go');

      for (const badgeName of badgesToAward) {
        // Check if user has badge
        const hasBadge = await db.query(
          "SELECT 1 FROM user_badges ub JOIN badges b ON ub.badge_id = b.id WHERE ub.user_id = ? AND b.name = ?",
          [userId, badgeName]
        );
        if (hasBadge.rows.length === 0) {
           // Get badge ID
           const badgeRes = await db.query("SELECT id, xp_reward FROM badges WHERE name = ?", [badgeName]);
           if (badgeRes.rows.length > 0) {
             const { id, xp_reward } = badgeRes.rows[0];
             await db.query("INSERT INTO user_badges (user_id, badge_id, awarded_at) VALUES (?, ?, NOW())", [userId, id]);
             console.log(`[Mobile Badge] Awarded ${badgeName} to user ${userId}`);
             if (xp_reward > 0) {
               await xpService.awardXP(userId, xp_reward, `Badge: ${badgeName}`);
             }
           }
        }
      }

    } catch (e) {
      console.error('Error processing field ops log/badges:', e);
    }

    res.json({
      success: true,
      message: `Action ${action_type} completed`,
      xpEarned,
      alertStatus: newStatus,
      intel_report: intelReport,
      isOperationComplete: !!opProgress?.isOperationComplete
    });

  } catch (error) {
    console.error('Error submitting field ops:', error);
    res.status(500).json({ message: 'Failed to submit field ops action' });
  }
};

/**
 * GET /api/mobile/phishing-scenario
 * Returns a random Phishing Scenario (Mixed Real Alerts & Synthetic Safe)
 */
exports.getPhishingScenario = async (req, res) => {
  try {
    // 50% chance to derive from Real Alert (Malicious), 50% chance for Synthetic Clean
    const isPhishing = Math.random() > 0.4; // 60% Phishing, 40% Safe

    if (isPhishing) {
      // Fetch a random Phishing alert
      // Fetch a random Phishing alert (Parameterized)
      const result = await db.query(
        "SELECT * FROM alerts WHERE alert_type LIKE ? OR path_code = ? ORDER BY RAND() LIMIT 1",
        ['%hish%', 'PHISH']
      );
      const alerts = result.rows;
      console.log(`[Phishing] Found ${alerts?.length} alerts`);

      if (alerts && alerts.length > 0) {
        const alert = alerts[0];
        // Enhance Subject Line realism
        let subject = alert.title || 'Urgent: Action Required';
        const metaKeywords = ['Phishing', 'Malware', 'Detected', 'Alert', 'Suspicious', 'Attack'];
        if (metaKeywords.some(kw => subject.includes(kw))) {
           const commonSubjects = [
             'Urgent: Invoice Overdue', 
             'Account Suspended', 
             'Security Alert: New Login', 
             'HR: Important Update', 
             'CEO: Wire Transfer Request', 
             'Delivery Attempt Failed'
           ];
           subject = commonSubjects[Math.floor(Math.random() * commonSubjects.length)];
        }

        let emailData = {
          id: alert.id,
          type: 'alert',
          sender: 'unknown@suspicious.com',
          subject: subject,
          body: alert.description || 'Please click the link below to verify your account.',
          isPhishing: true,
          reason: 'This email matches known phishing patterns found in our SOC database.'
        };

        if (alert.raw_log) {
           const senderMatch = alert.raw_log.match(/from:?\s*([^\s,]+)/i);
           // Fallback to searching for email pattern if from: prefix missing
           if (!senderMatch) {
             const emailPattern = /[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,6}/;
             const emailMatch = alert.raw_log.match(emailPattern);
             if (emailMatch) emailData.sender = emailMatch[0];
           } else {
             emailData.sender = senderMatch[1];
           }
        }
        
        // Dynamic Malicious Attachments (70% chance)
        if (Math.random() > 0.3) {
            const maliciousAttachments = ['Invoice_Pending.exe', 'Salary_Report.js', 'System_Update.vbs', 'Payment_Receipt.html', 'Urgent_Doc.docm', 'Order_Details.zip'];
            emailData.attachment = maliciousAttachments[Math.floor(Math.random() * maliciousAttachments.length)];
        } else {
            emailData.attachment = null;
        }

        return res.json(emailData);
      }
    }

    // Fallback or Safe Email (Synthetic)
    const safeEmails = [
      {
          id: 'safe_1',
          type: 'synthetic',
          sender: 'notifications@slack.com',
          subject: 'New message from #general',
          body: 'Hey team, just a reminder about the meeting at 3 PM today.',
          isPhishing: false,
          reason: 'Legitimate notification from a verified internal domain.',
          attachment: null
      },
      {
          id: 'safe_2',
          type: 'synthetic',
          sender: 'security@infoseclabs.io',
          subject: 'Password Expiry Notification',
          body: 'Your password will expire in 7 days. Please change it via the official portal settings.',
          isPhishing: false,
          reason: 'Internal system notification. Links point to valid internal domains.',
          attachment: null
      },
      {
          id: 'safe_3',
          type: 'synthetic',
          sender: 'newsletter@techweekly.com',
          subject: 'This Week in Tech',
          body: 'Here are the top stories for this week. Check out the attached summary.',
          isPhishing: false,
          reason: 'Standard newsletter subscription.',
          attachment: 'Weekly_Digest.pdf'
      },
      {
          id: 'safe_4',
          type: 'synthetic',
          sender: 'hr@infoseclabs.io',
          subject: 'Holiday Calendar 2026',
          body: 'Attached is the updated holiday calendar for the upcoming year.',
          isPhishing: false,
          reason: 'Internal HR communication with standard document type.',
          attachment: 'Holiday_Calendar.xlsx'
      }
    ];
    
    const randomSafe = safeEmails[Math.floor(Math.random() * safeEmails.length)];
    // Add random chance to remove attachment even for safe emails that have them, 
    // or add one to those that don't (though adding might contradict body text).
    // Let's just keep the safe emails' attachment logic as defined in the object for consistency with body text.
    
    return res.json(randomSafe);

  } catch (error) {
    console.error('Get Phishing Scenario Error:', error);
    res.status(500).json({ message: 'Failed to get scenario' });
  }
};

/**
 * POST /api/mobile/phishing-submit
 */
exports.submitPhishingDecision = async (req, res) => {
  const { scenarioId, decision, isPhishing } = req.body; // decision: 'malicious' | 'safe'
  const userId = req.user.id;

  try {
      const correct = (decision === 'malicious' && isPhishing) || (decision === 'safe' && !isPhishing);
      const xpReward = correct ? 25 : 0;

      if (correct) {
        await xpService.awardXP(
            userId, 
            xpReward, 
            'PHISHING_HUNTER_WIN'
        );
      }

      let feedback = '';
      if (correct) {
        feedback = 'Excellent work, Operative. Threat analysis confirmed.';
      } else {
        if (isPhishing && decision === 'safe') {
          feedback = 'Mission Failed. You trusted a malicious email. Look for sender anomalies and urgent language.';
        } else {
          feedback = 'Mission Failed. You flagged a legitimate email. Verify internal domains before blocking.';
        }
      }

      res.json({
          correct,
          feedback,
          xpEarned: xpReward
      });

  } catch (error) {
      console.error('Submit Phishing Error:', error);
      res.status(500).json({ message: 'Error submitting decision' });
  }
};

/**
 * GET /api/mobile/log-scenario
 */
exports.getLogScenario = async (req, res) => {
  try {
      const result = await db.query(
          "SELECT id, alert_type, raw_log FROM alerts WHERE raw_log IS NOT NULL AND CHAR_LENGTH(raw_log) > 10 ORDER BY RAND() LIMIT 1",
          []
      );
      const alerts = result.rows;

      if (!alerts || alerts.length === 0) {
          return res.status(404).json({ message: 'No logs available' });
      }

      const alert = alerts[0];
      
      const categories = ['Phishing', 'Malware', 'Network Intrusion', 'Identity/Auth', 'Data Leak'];
      const correctCategory = mapCategoryToOption(alert.alert_type, alert.raw_log);
      
      // Ensure correct category is in the options list and standardized
      if (!categories.includes(correctCategory)) {
           categories[0] = correctCategory; 
      }
      
      // Pretty print log if it's JSON
      let formattedLog = alert.raw_log;
      try {
        const parsed = JSON.parse(formattedLog);
        formattedLog = JSON.stringify(parsed, null, 2); // Pretty print with indentation
      } catch (e) {
        // Not JSON or partial JSON, keep as is
      }
      
      // Limit length but keep it reasonably long for scrolling
      if (formattedLog.length > 1000) {
        formattedLog = formattedLog.substring(0, 1000) + '... [TRUNCATED]';
      }

      return res.json({
          id: alert.id,
          log: formattedLog, 
          options: shuffleArray(categories),
          correctAnswer: correctCategory
      });

  } catch (error) {
      console.error('Get Log Scenario Error:', error);
      res.status(500).json({ message: 'Failed to get log scenario' });
  }
};

/**
 * POST /api/mobile/log-submit
 */
/**
 * POST /api/mobile/log-submit
 */
exports.submitLogAnswer = async (req, res) => {
  const { scenarioId, answer, correctAnswer } = req.body;
  const userId = req.user.id;

  try {
      const isCorrect = answer === correctAnswer;
      const xpReward = isCorrect ? 30 : 0;

      if (isCorrect) {
        await xpService.awardXP(
            userId, 
            xpReward, 
            'LOG_DETECTIVE_WIN'
        );
      }

      res.json({
          correct: isCorrect,
          xpEarned: xpReward
      });

  } catch (error) {
      console.error('Log Submit Error:', error);
      res.status(500).json({ message: 'Error submitting log answer' });
  }
};

// Helper to normalize categories
function mapCategoryToOption(dbCategory, rawLog = '') {
    if (!dbCategory && !rawLog) return 'Network Intrusion';
    
    const combined = (dbCategory + ' ' + rawLog).toLowerCase();

    if (combined.includes('phish') || combined.includes('email') || combined.includes('sender')) return 'Phishing';
    if (combined.includes('malware') || combined.includes('ransom') || combined.includes('virus') || combined.includes('trojan') || combined.includes('exe') || combined.includes('powershell')) return 'Malware';
    if (combined.includes('login') || combined.includes('auth') || combined.includes('password') || combined.includes('credential')) return 'Identity/Auth';
    if (combined.includes('leak') || combined.includes('filt') || combined.includes('data') || combined.includes('sensitive')) return 'Data Leak';
    
    return 'Network Intrusion'; 
}

// Helper to shuffle array
/**
 * GET /api/mobile/specialization
 * Returns user's current specialization paths and available options
 */
exports.getSpecialization = async (req, res) => {
  const userId = req.user.id;
  try {
    const { getUserPaths, getUserSpecializationStats } = require('../services/assignmentService');
    const { getPathsForDropdown } = require('../config/specializationPaths');

    const currentPaths = await getUserPaths(userId);
    const stats = await getUserSpecializationStats(userId);
    const availablePaths = getPathsForDropdown();

    res.json({
      primary_path: currentPaths.primary_path,
      secondary_path: currentPaths.secondary_path,
      available_paths: availablePaths,
      stats: stats
    });
  } catch (error) {
    console.error('Get Specialization Error:', error);
    res.status(500).json({ message: 'Failed to fetch specialization' });
  }
};

/**
 * POST /api/mobile/specialization
 * Updates user's primary and secondary specialization paths
 */
exports.updateSpecialization = async (req, res) => {
  const userId = req.user.id;
  const { primary_path, secondary_path } = req.body;

  try {
    if (!primary_path) {
      return res.status(400).json({ message: 'Primary path is required' });
    }

    const { updateUserPaths } = require('../services/assignmentService');
    const result = await updateUserPaths(userId, primary_path, secondary_path || null);

    res.json({
      message: 'Specialization updated successfully',
      ...result
    });
  } catch (error) {
    console.error('Update Specialization Error:', error);
    res.status(400).json({ message: error.message || 'Failed to update specialization' });
  }
};

function shuffleArray(array) {
    for (let i = array.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [array[i], array[j]] = [array[j], array[i]];
    }
    return array;
}
