/**
 * Assignment Service
 * Handles daily alert assignments based on user level and specialization path
 */

const db = require('../db');
const { 
    SPECIALIZATION_PATHS, 
    CORE_PATH, 
    LEVEL_CONFIG, 
    ASSIGNMENT_CONFIG,
    getPathInfo 
} = require('../config/specializationPaths');

/**
 * Get user's current level from user_xp table
 */
async function getUserLevel(userId) {
    const result = await db.query(
        'SELECT current_level FROM user_xp WHERE user_id = ?',
        [userId]
    );
    const rows = result.rows || [];
    return rows.length > 0 ? rows[0].current_level : 1;
}

/**
 * Map user level to allowed alert difficulties based on Analyst Tiers
 */
function getAllowedDifficulties(level) {
    // Level 1-20: Novice, 21-49: Tier 1, 50-99: Tier 2, 100-199: Senior, 200+: Manager
    if (level < 21) return ['Novice', 'Beginner'];
    if (level < 50) return ['Beginner', 'Intermediate'];
    if (level < 100) return ['Intermediate', 'Advanced'];
    if (level < 200) return ['Advanced', 'Expert'];
    return ['Expert', 'Advanced']; // Level 200+
}

/**
 * Get user's path selections
 */
async function getUserPaths(userId) {
    const result = await db.query(
        'SELECT primary_path, secondary_path FROM users WHERE id = ?',
        [userId]
    );
    const rows = result.rows || [];
    if (rows.length === 0) {
        return { primary_path: 'SIEM', secondary_path: null };
    }
    return rows[0];
}

/**
 * Get alerts completed by user (to avoid duplicates)
 */
async function getCompletedAlertIds(userId) {
    const result = await db.query(
        `SELECT DISTINCT alert_id FROM investigations 
         WHERE user_id = ? AND status IN ('submitted', 'graded')`,
        [userId]
    );
    const rows = result.rows || [];
    return rows.map(r => r.alert_id);
}

/**
 * Select alerts matching criteria
 * For Foundations phase or when no alerts are tagged, select any available alerts
 * NOTE: Using string interpolation for all values due to mysql2 parameterized query issues
 */
async function selectAlerts({ pathCode, allowedDifficulties, excludeIds, count }) {
    // Build query with inline values to avoid mysql2 prepared statement issues
    let conditions = [`(a.status = 'new' OR a.status = 'New')`, `NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)`];

    // Path filter - if pathCode is specified, filter by it OR untagged/CORE alerts
    if (pathCode && pathCode !== CORE_PATH) {
        conditions.push(`(a.path_code = '${pathCode}' OR a.path_code = 'CORE' OR a.path_code IS NULL)`);
    }

    // Difficulty filter - filter by tier-appropriate difficulties
    if (allowedDifficulties && allowedDifficulties.length > 0) {
        const diffStrings = allowedDifficulties.map(d => `'${d}'`).join(',');
        conditions.push(`(a.difficulty IN (${diffStrings}) OR a.difficulty IS NULL)`);
    }

    // Exclude already completed
    if (excludeIds && excludeIds.length > 0) {
        const safeIds = excludeIds.filter(id => Number.isInteger(id) && id > 0);
        if (safeIds.length > 0) {
            conditions.push(`a.id NOT IN (${safeIds.join(',')})`);
        }
    }

    const limitCount = parseInt(count) || 10;
    const query = `
        SELECT a.id, a.title, a.severity, a.source, a.details, a.playbook_solution, a.path_code, a.min_level, a.difficulty
        FROM alerts a
        WHERE ${conditions.join(' AND ')}
        ORDER BY RAND() 
        LIMIT ${limitCount}
    `;

    try {
        const result = await db.query(query);
        console.log(`[selectAlerts] Found ${result.rows?.length || 0} alerts`);
        return result.rows || [];
    } catch (err) {
        console.error('selectAlerts error:', err.message);
        console.error('Query:', query);
        return [];
    }
}

/**
 * Generate daily queue for a user
 * Called when user opens dashboard or via cron job
 */
async function generateDailyQueue(userId) {
    const today = new Date().toISOString().split('T')[0];

    // Check if queue already exists for today
    // Check if queue already exists for today (Counting only NON-MISSION alerts)
    // This ensures that if mission alerts were accidentally assigned, they don't block new valid alerts
    const existingResult = await db.query(
        `SELECT COUNT(*) as count 
         FROM user_alert_assignments uaa
         JOIN alerts a ON uaa.alert_id = a.id
         WHERE uaa.user_id = ? 
         AND uaa.assigned_date = ?
         AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)`,
        [userId, today]
    );
    const existing = existingResult.rows || [];

    if (existing[0]?.count >= ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE) {
        // Queue already generated
        return getDailyQueue(userId);
    }

    const userLevel = await getUserLevel(userId);
    const { primary_path, secondary_path } = await getUserPaths(userId);
    const completedIds = await getCompletedAlertIds(userId);

    const queue = [];
    const levelVariance = ASSIGNMENT_CONFIG.LEVEL_VARIANCE;

    const isFoundations = userLevel <= LEVEL_CONFIG.FOUNDATIONS_END;
    const allowedDifficulties = getAllowedDifficulties(userLevel);

    if (isFoundations) {
        // Foundations: Mixed alerts from all paths
        const alerts = await selectAlerts({
            pathCode: null, // All paths
            allowedDifficulties: ['Novice', 'Beginner'],
            excludeIds: completedIds,
            count: ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE
        });
        queue.push(...alerts);
    } else {
        // Path-specific distribution
        const primaryCount = Math.floor(ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE * ASSIGNMENT_CONFIG.PRIMARY_PATH_WEIGHT);
        const coreCount = Math.floor(ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE * ASSIGNMENT_CONFIG.CORE_WEIGHT);
        const secondaryCount = ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE - primaryCount - coreCount;

        // Primary path alerts - if no primary_path, select from all alerts
        const primaryAlerts = await selectAlerts({
            pathCode: primary_path || null, // null = all paths
            allowedDifficulties,
            excludeIds: [...completedIds, ...queue.map(a => a.id)],
            count: primary_path ? primaryCount : ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE
        });
        queue.push(...primaryAlerts);

        // Only add CORE and secondary if primary_path was set and we need more
        if (primary_path && queue.length < ASSIGNMENT_CONFIG.DAILY_QUEUE_SIZE) {
            // CORE alerts
            const coreAlerts = await selectAlerts({
                pathCode: CORE_PATH,
                allowedDifficulties,
                excludeIds: [...completedIds, ...queue.map(a => a.id)],
                count: coreCount
            });
            queue.push(...coreAlerts);

            // Secondary path alerts
            if (secondary_path && secondaryCount > 0) {
                const secondaryAlerts = await selectAlerts({
                    pathCode: secondary_path,
                    allowedDifficulties,
                    excludeIds: [...completedIds, ...queue.map(a => a.id)],
                    count: secondaryCount
                });
                queue.push(...secondaryAlerts);
            }
        }
    }

    // Insert assignments into database one by one
    if (queue.length > 0) {
        for (const alert of queue) {
            try {
                await db.query(
                    `INSERT IGNORE INTO user_alert_assignments 
                     (user_id, alert_id, assigned_date, completed, is_replay, xp_earned)
                     VALUES (?, ?, ?, 0, 0, 0)`,
                    [userId, alert.id, today]
                );
            } catch (insertErr) {
                // Ignore duplicate key errors
                console.debug('Assignment insert skipped (may exist):', insertErr.message);
            }
        }
    }

    return getDailyQueue(userId);
}

/**
 * Get today's queue for a user
 */
async function getDailyQueue(userId) {
    const today = new Date().toISOString().split('T')[0];

    const result = await db.query(
        `SELECT 
            uaa.id as assignment_id,
            uaa.completed,
            uaa.is_replay,
            uaa.xp_earned,
            a.id as alert_id,
            a.title,
            a.severity,
            a.source,
            a.details,
            a.playbook_solution,
            a.path_code,
            a.min_level,
            a.difficulty,
            a.mitre_technique
         FROM user_alert_assignments uaa
         JOIN alerts a ON uaa.alert_id = a.id
         WHERE uaa.user_id = ? AND uaa.completed = 0 AND uaa.assigned_date = ? AND NOT EXISTS (SELECT 1 FROM operation_alerts oa WHERE oa.alert_id = a.id)
         ORDER BY a.severity DESC`,
        [userId, today]
    );

    return result.rows || [];
}

/**
 * Mark an assignment as completed
 */
async function markAssignmentCompleted(userId, alertId, xpEarned = 0) {
    await db.query(
        `UPDATE user_alert_assignments 
         SET completed = 1, completed_at = NOW(), xp_earned = ?
         WHERE user_id = ? AND alert_id = ? AND completed = 0`,
        [xpEarned, userId, alertId]
    );

    // Update path specialization stats
    const alertResult = await db.query(
        'SELECT path_code FROM alerts WHERE id = ?',
        [alertId]
    );
    const alertInfo = alertResult.rows || [];

    if (alertInfo.length > 0 && alertInfo[0].path_code) {
        await db.query(
            `INSERT INTO user_path_specialization (user_id, path_code, alerts_completed, last_activity)
             VALUES (?, ?, 1, NOW())
             ON DUPLICATE KEY UPDATE 
                alerts_completed = alerts_completed + 1,
                last_activity = NOW()`,
            [userId, alertInfo[0].path_code]
        );
    }
}

/**
 * Get user's specialization stats
 */
async function getUserSpecializationStats(userId) {
    // Get historical stats (completed)
    const result = await db.query(
        `SELECT path_code, alerts_completed, missions_completed, average_score
         FROM user_path_specialization
         WHERE user_id = ?`,
        [userId]
    );
    const stats = result.rows || [];

    // Get active counts (currently assigned)
    const activeResult = await db.query(
        `SELECT a.path_code, COUNT(*) as active_count
         FROM user_alert_assignments uaa
         JOIN alerts a ON uaa.alert_id = a.id
         WHERE uaa.user_id = ? AND uaa.completed = 0
         GROUP BY a.path_code`,
        [userId]
    );
    const activeCounts = {};
    (activeResult.rows || []).forEach(row => {
        if (row.path_code) activeCounts[row.path_code] = row.active_count;
    });

    // Merge active counts into stats or create new entries for paths with only active alerts
    const mergedStats = [...stats];
    
    // Update existing stats with active count
    mergedStats.forEach(stat => {
        stat.alerts_active = activeCounts[stat.path_code] || 0;
        delete activeCounts[stat.path_code]; // Remove processed
    });

    // Add remaining paths that have active alerts but no history yet
    Object.keys(activeCounts).forEach(pathCode => {
        mergedStats.push({
            path_code: pathCode,
            alerts_completed: 0,
            missions_completed: 0,
            average_score: 0,
            alerts_active: activeCounts[pathCode]
        });
    });

    return mergedStats.map(row => ({
        ...row,
        pathInfo: getPathInfo(row.path_code)
    })).sort((a, b) => (b.alerts_completed + b.alerts_active) - (a.alerts_completed + a.alerts_active));
}

/**
 * Update user's primary/secondary path
 */
async function updateUserPaths(userId, primaryPath, secondaryPath = null) {
    // Validate paths
    if (primaryPath && !SPECIALIZATION_PATHS[primaryPath]) {
        throw new Error('Invalid primary path');
    }
    if (secondaryPath && !SPECIALIZATION_PATHS[secondaryPath]) {
        throw new Error('Invalid secondary path');
    }
    if (primaryPath === secondaryPath) {
        throw new Error('Primary and secondary paths must be different');
    }

    await db.query(
        `UPDATE users SET primary_path = ?, secondary_path = ? WHERE id = ?`,
        [primaryPath, secondaryPath, userId]
    );

    return { primary_path: primaryPath, secondary_path: secondaryPath };
}

/**
 * Check if user should be prompted to select a path
 */
async function shouldPromptPathSelection(userId) {
    const userLevel = await getUserLevel(userId);
    const { primary_path } = await getUserPaths(userId);

    // Prompt at level 3 if no path selected
    return userLevel >= LEVEL_CONFIG.PATH_SELECTION_LEVEL && !primary_path;
}

/**
 * Get available alerts filtered by user's level and path
 */
async function getAvailableAlerts(userId, options = {}) {
    const userLevel = await getUserLevel(userId);
    const { primary_path } = await getUserPaths(userId);
    const completedIds = await getCompletedAlertIds(userId);

    const { 
        limit = 20, 
        offset = 0, 
        pathFilter = null,
        showLocked = false 
    } = options;

    let query = `
        SELECT 
            a.*,
            CASE WHEN a.min_level > ? THEN 1 ELSE 0 END as is_locked,
            CASE WHEN i.id IS NOT NULL THEN 1 ELSE 0 END as is_completed
        FROM alerts a
        LEFT JOIN investigations i ON a.id = i.alert_id AND i.user_id = ? AND i.status IN ('submitted', 'graded')
        WHERE 1=1
    `;
    const params = [userLevel, userId];

    // Path filter
    if (pathFilter) {
        query += ` AND (a.path_code = ? OR a.path_code IS NULL)`;
        params.push(pathFilter);
    }

    // Hide locked alerts unless requested
    if (!showLocked) {
        const allowedDifficulties = getAllowedDifficulties(userLevel);
        const diffStrings = allowedDifficulties.map(d => `'${d}'`).join(',');
        query += ` AND (a.difficulty IN (${diffStrings}) OR a.difficulty IS NULL)`;
    }

    query += ` ORDER BY a.id DESC LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    const result = await db.query(query, params);
    return result.rows || [];
}

module.exports = {
    getUserLevel,
    getUserPaths,
    generateDailyQueue,
    getDailyQueue,
    markAssignmentCompleted,
    getUserSpecializationStats,
    updateUserPaths,
    shouldPromptPathSelection,
    getAvailableAlerts,
    getAllowedDifficulties
};
