// backend/src/controllers/learningPathController.js
const db = require('../db');
const xpService = require('../utils/xpService');

// Get all learning paths
const getLearningPaths = async (req, res) => {
    try {
        const paths = await db.query(
            'SELECT * FROM learning_paths WHERE is_active = TRUE ORDER BY display_order ASC'
        );

        // For each path, calculate total modules and tasks
        for (let path of paths.rows) {
            const moduleCount = await db.query(
                'SELECT COUNT(*) as count FROM modules WHERE learning_path_id = ? AND is_active = TRUE',
                [path.id]
            );

            const taskCount = await db.query(
                'SELECT COUNT(*) as count FROM tasks t JOIN modules m ON t.module_id = m.id WHERE m.learning_path_id = ? AND t.is_active = TRUE',
                [path.id]
            );

            path.total_modules = moduleCount.rows[0].count;
            path.total_tasks = taskCount.rows[0].count;

            // If user is authenticated, include their progress
            if (req.user) {
                const progress = await db.query(
                    'SELECT percentage_completed, enrolled_at, completed_at FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
                    [req.user.id, path.id]
                );
                path.user_progress = progress.rows[0] || { percentage_completed: 0, enrolled_at: null };
            }
        }

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

// Get modules for a specific learning path
const getPathModules = async (req, res) => {
    try {
        const { pathId } = req.params;

        const modules = await db.query(
            'SELECT * FROM modules WHERE learning_path_id = ? AND is_active = TRUE ORDER BY display_order ASC',
            [pathId]
        );

        // For each module, calculate task counts and user progress
        for (let module of modules.rows) {
            const taskCount = await db.query(
                'SELECT COUNT(*) as count FROM tasks WHERE module_id = ? AND is_active = TRUE',
                [module.id]
            );
            module.total_tasks = parseInt(taskCount.rows[0].count);

            if (req.user) {
                const completedTasks = await db.query(
                    'SELECT COUNT(*) as count FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id WHERE t.module_id = ? AND utp.user_id = ? AND utp.completed = TRUE',
                    [module.id, req.user.id]
                );
                module.completed_tasks = parseInt(completedTasks.rows[0].count);
                module.is_unlocked = await checkModuleUnlocked(req.user.id, module);
            }
        }

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

// Get tasks for a specific module
const getModuleTasks = async (req, res) => {
    try {
        const { moduleId } = req.params;

        const tasks = await db.query(
            `SELECT t.*, m.title as module_title, m.learning_path_id 
             FROM tasks t 
             JOIN modules m ON t.module_id = m.id 
             WHERE t.module_id = ? AND t.is_active = TRUE 
             ORDER BY t.display_order ASC`,
            [moduleId]
        );

        // If user is authenticated, include completion status
        if (req.user) {
            for (let task of tasks.rows) {
                const progress = await db.query(
                    'SELECT completed, completed_at FROM user_task_progress WHERE user_id = ? AND task_id = ?',
                    [req.user.id, task.id]
                );
                task.is_completed = progress.rows[0]?.completed || false;
                task.completed_at = progress.rows[0]?.completed_at || null;
            }
        }

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

// Enroll user in a learning path
const enrollInPath = async (req, res) => {
    try {
        const { pathId } = req.params;
        const userId = req.user.id;

        // Check prerequisites
        const pathData = await db.query('SELECT prerequisite_path_id, title FROM learning_paths WHERE id = ?', [pathId]);
        if (pathData.rows.length === 0) {
            return res.status(404).json({ message: 'Path not found' });
        }
        
        const prereqId = pathData.rows[0].prerequisite_path_id;
        if (prereqId) {
            const prereqProgress = await db.query(
                'SELECT percentage_completed FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
                [userId, prereqId]
            );
            
            if (prereqProgress.rows.length === 0 || parseFloat(prereqProgress.rows[0].percentage_completed) < 100) {
                const prereqTitle = await db.query('SELECT title FROM learning_paths WHERE id = ?', [prereqId]);
                return res.status(403).json({ 
                    message: `You must complete '${prereqTitle.rows[0]?.title || 'the prerequisite path'}' before starting this path.`,
                    prerequisite_required: true 
                });
            }
        }

        // Check if already enrolled
        const existing = await db.query(
            'SELECT * FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
            [userId, pathId]
        );

        if (existing.rows.length > 0) {
            return res.status(400).json({ message: 'Already enrolled in this path' });
        }

        // Enroll the user
        await db.query(
            'INSERT INTO user_path_progress (user_id, learning_path_id, percentage_completed) VALUES (?, ?, 0)',
            [userId, pathId]
        );

        res.json({ message: 'Successfully enrolled in learning path', enrolled: true });
    } catch (error) {
        console.error('Error enrolling in path:', error);
        res.status(500).json({ message: 'Failed to enroll in path' });
    }
};

// Mark a task as complete
const completeTask = async (req, res) => {
    try {
        const { taskId } = req.params;
        const userId = req.user.id;

        // Check if already completed
        const existing = await db.query(
            'SELECT * FROM user_task_progress WHERE user_id = ? AND task_id = ?',
            [userId, taskId]
        );

        if (existing.rows.length > 0 && existing.rows[0].completed) {
            return res.status(400).json({ message: 'Task already completed' });
        }

        // Get task details for XP reward
        const taskResult = await db.query('SELECT * FROM tasks WHERE id = ?', [taskId]);
        const task = taskResult.rows[0];

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

        // Mark task as complete
        if (existing.rows.length > 0) {
            await db.query(
                'UPDATE user_task_progress SET completed = TRUE, completed_at = NOW() WHERE user_id = ? AND task_id = ?',
                [userId, taskId]
            );
        } else {
            await db.query(
                'INSERT INTO user_task_progress (user_id, task_id, completed, completed_at) VALUES (?, ?, TRUE, NOW())',
                [userId, taskId]
            );
        }

        // Award XP with streak bonus
        const xpResult = await xpService.awardXPWithStreak(userId, task.xp_reward);

        // Update path progress percentage
        await updatePathProgress(userId, task.module_id);

        // Check for badge unlocks
        const newBadges = await checkBadgeUnlocks(userId);

        res.json({
            message: 'Task completed successfully',
            xp_earned: xpResult.totalXPEarned,
            streak_bonus: xpResult.streakBonus,
            streak_days: xpResult.newStreakDays,
            badges_earned: newBadges
        });
    } catch (error) {
        console.error('Error completing task:', error);
        res.status(500).json({ message: 'Failed to complete task' });
    }
};

// Get user's overall progress
const getUserProgress = async (req, res) => {
    try {
        const userId = req.user.id;

        // Get XP and level
        let xpData = await db.query('SELECT * FROM user_xp WHERE user_id = ?', [userId]);

        if (xpData.rows.length === 0) {
            // Initialize XP record
            await db.query(
                'INSERT INTO user_xp (user_id, total_xp, current_level, xp_to_next_level) VALUES (?, 0, 1, 100)',
                [userId]
            );
            xpData = await db.query('SELECT * FROM user_xp WHERE user_id = ?', [userId]);
        }

        // Get enrolled paths with progress
        const paths = await db.query(
            `SELECT lp.*, upp.percentage_completed, upp.enrolled_at, upp.completed_at 
             FROM user_path_progress upp 
             JOIN learning_paths lp ON upp.learning_path_id = lp.id 
             WHERE upp.user_id = ?`,
            [userId]
        );

        // Get earned badges
        const badges = await db.query(
            `SELECT b.*, ub.earned_at 
             FROM user_badges ub 
             JOIN badges b ON ub.badge_id = b.id 
             WHERE ub.user_id = ? 
             ORDER BY ub.earned_at DESC`,
            [userId]
        );

        res.json({
            xp: xpData.rows[0],
            enrolled_paths: paths.rows,
            badges: badges.rows
        });
    } catch (error) {
        console.error('Error fetching user progress:', error);
        res.status(500).json({ message: 'Failed to fetch user progress' });
    }
};

// Helper: Check if module is unlocked for user
const checkModuleUnlocked = async (userId, module) => {
    if (!module.prerequisite_module_id) {
        return true; // No prerequisite, always unlocked
    }

    // Check if prerequisite module is completed
    const prereqTasks = await db.query(
        'SELECT COUNT(*) as total FROM tasks WHERE module_id = ? AND is_active = TRUE',
        [module.prerequisite_module_id]
    );

    const completedTasks = await db.query(
        'SELECT COUNT(*) as completed FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id WHERE t.module_id = ? AND utp.user_id = ? AND utp.completed = TRUE',
        [module.prerequisite_module_id, userId]
    );

    return completedTasks.rows[0].completed >= prereqTasks.rows[0].total;
};

// Helper: Award XP to user (now delegated to xpService)
const awardXP = async (userId, xpAmount) => xpService.awardXP(userId, xpAmount);

// Helper: Update path progress percentage
const updatePathProgress = async (userId, moduleId) => {
    // Get the learning path for this module
    const moduleData = await db.query('SELECT learning_path_id FROM modules WHERE id = ?', [moduleId]);
    const pathId = moduleData.rows[0].learning_path_id;

    // Calculate total tasks in the path
    const totalTasks = await db.query(
        'SELECT COUNT(*) as count FROM tasks t JOIN modules m ON t.module_id = m.id WHERE m.learning_path_id = ? AND t.is_active = TRUE',
        [pathId]
    );

    // Calculate completed tasks
    const completedTasks = await db.query(
        `SELECT COUNT(*) as count FROM user_task_progress utp 
         JOIN tasks t ON utp.task_id = t.id 
         JOIN modules m ON t.module_id = m.id 
         WHERE m.learning_path_id = ? AND utp.user_id = ? AND utp.completed = TRUE`,
        [pathId, userId]
    );

    const percentage = (completedTasks.rows[0].count / totalTasks.rows[0].count) * 100;
    const completed_at = percentage >= 100 ? new Date() : null;

    // Generate certificate ID if path is completed
    let certificateId = null;
    if (percentage >= 100) {
        // Check if progress record exists to get existing certificate
        const existing = await db.query(
            'SELECT certificate_id FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
            [userId, pathId]
        );
        
        if (existing.rows[0]?.certificate_id) {
            certificateId = existing.rows[0].certificate_id;
        } else {
            // Generate unique certificate ID: CERT-YEAR-PATHID-USERID-RANDOM
            const year = new Date().getFullYear();
            const random = Math.random().toString(36).substring(2, 8).toUpperCase();
            certificateId = `CERT-${year}-${pathId}-${userId}-${random}`;
        }
    }

    // Check if progress record exists for upsert
    const progressCheck = await db.query(
        'SELECT id FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
        [userId, pathId]
    );

    if (progressCheck.rows.length === 0) {
        // Create new record
        await db.query(
            'INSERT INTO user_path_progress (user_id, learning_path_id, enrolled_at, percentage_completed, completed_at, certificate_id) VALUES (?, ?, NOW(), ?, ?, ?)',
            [userId, pathId, percentage, completed_at, certificateId]
        );
    } else {
        // Update existing record
        await db.query(
            'UPDATE user_path_progress SET percentage_completed = ?, completed_at = ?, certificate_id = ? WHERE user_id = ? AND learning_path_id = ?',
            [percentage, completed_at, certificateId, userId, pathId]
        );
    }
};

// Helper: Check for badge unlocks
const checkBadgeUnlocks = async (userId) => {
    const newBadges = [];

    // Check for "First Steps" badge
    const taskCount = await db.query(
        'SELECT COUNT(*) as count FROM user_task_progress WHERE user_id = ? AND completed = TRUE',
        [userId]
    );

    if (parseInt(taskCount.rows[0].count) === 1) {
        const firstStepsBadge = await db.query('SELECT * FROM badges WHERE name = ?', ['First Steps']);
        if (firstStepsBadge.rows.length > 0) {
            const added = await awardBadge(userId, firstStepsBadge.rows[0].id);
            if (added) newBadges.push(firstStepsBadge.rows[0]);
        }
    }

    // Check for Linux Novice
    const linuxMod = await db.query('SELECT id FROM modules WHERE title = ?', ['Linux Fundamentals']);
    if (linuxMod.rows.length > 0) {
        const modId = linuxMod.rows[0].id;
        const tasksReq = await db.query('SELECT COUNT(*) as c FROM tasks WHERE module_id = ? AND is_active = TRUE', [modId]);
        const reqCount = parseInt(tasksReq.rows[0].c);
        if (reqCount > 0) {
            const tasksDone = await db.query('SELECT COUNT(*) as c FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id WHERE t.module_id = ? AND utp.user_id = ? AND utp.completed = TRUE', [modId, userId]);
            if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                const b = await db.query('SELECT * FROM badges WHERE name = ?', ['Linux Novice']);
                if (b.rows.length > 0) {
                    const added = await awardBadge(userId, b.rows[0].id);
                    if (added) newBadges.push(b.rows[0]);
                }
            }
        }
    }

    // Check for Windows Explorer
    const winMod = await db.query('SELECT id FROM modules WHERE title = ?', ['Windows Fundamentals']);
    if (winMod.rows.length > 0) {
        const modId = winMod.rows[0].id;
        const tasksReq = await db.query('SELECT COUNT(*) as c FROM tasks WHERE module_id = ? AND is_active = TRUE', [modId]);
        const reqCount = parseInt(tasksReq.rows[0].c);
        if (reqCount > 0) {
            const tasksDone = await db.query('SELECT COUNT(*) as c FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id WHERE t.module_id = ? AND utp.user_id = ? AND utp.completed = TRUE', [modId, userId]);
            if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                const b = await db.query('SELECT * FROM badges WHERE name = ?', ['Windows Explorer']);
                if (b.rows.length > 0) {
                    const added = await awardBadge(userId, b.rows[0].id);
                    if (added) newBadges.push(b.rows[0]);
                }
            }
        }
    }

    // Check for Network Navigator
    const netMod = await db.query('SELECT id FROM modules WHERE title = ?', ['Networking Essentials']);
    if (netMod.rows.length > 0) {
        const modId = netMod.rows[0].id;
        const tasksReq = await db.query('SELECT COUNT(*) as c FROM tasks WHERE module_id = ? AND is_active = TRUE', [modId]);
        const reqCount = parseInt(tasksReq.rows[0].c);
        if (reqCount > 0) {
            const tasksDone = await db.query('SELECT COUNT(*) as c FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id WHERE t.module_id = ? AND utp.user_id = ? AND utp.completed = TRUE', [modId, userId]);
            if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                const b = await db.query('SELECT * FROM badges WHERE name = ?', ['Network Navigator']);
                if (b.rows.length > 0) {
                    const added = await awardBadge(userId, b.rows[0].id);
                    if (added) newBadges.push(b.rows[0]);
                }
            }
        }
    }

    // Check for Security Foundations (Path completed)
    const preSecPath = await db.query('SELECT id FROM learning_paths WHERE title = ?', ['Pre-Security Fundamentals']);
    if (preSecPath.rows.length > 0) {
        const pId = preSecPath.rows[0].id;
        const progress = await db.query('SELECT percentage_completed FROM user_path_progress WHERE learning_path_id = ? AND user_id = ?', [pId, userId]);
        if (progress.rows.length > 0 && parseFloat(progress.rows[0].percentage_completed) >= 100) {
            const b = await db.query('SELECT * FROM badges WHERE name = ?', ['Security Foundations']);
            if (b.rows.length > 0) {
                const added = await awardBadge(userId, b.rows[0].id);
                if (added) newBadges.push(b.rows[0]);
            }
        }
    }

    return newBadges;
};

// Helper: Award badge to user
const awardBadge = async (userId, badgeId) => {
    const existing = await db.query(
        'SELECT * FROM user_badges WHERE user_id = ? AND badge_id = ?',
        [userId, badgeId]
    );

    if (existing.rows.length === 0) {
        await db.query(
            'INSERT INTO user_badges (user_id, badge_id, awarded_at) VALUES (?, ?, NOW())',
            [userId, badgeId]
        );

        // Award badge XP
        const badge = await db.query('SELECT xp_reward FROM badges WHERE id = ?', [badgeId]);
        await awardXP(userId, badge.rows[0].xp_reward);
        return true;
    }
    return false;
};

// Get lesson content and questions for a task
const getLessonContent = async (req, res) => {
    try {
        const { taskId } = req.params;

        // Get task details (title, module info)
        const taskRes = await db.query(
            `SELECT t.id, t.title, t.module_id, t.xp_reward, t.display_order, t.alert_id, m.title as module_title 
             FROM tasks t 
             JOIN modules m ON t.module_id = m.id 
             WHERE t.id = ?`,
            [taskId]
        );

        if (taskRes.rows.length === 0) {
            return res.status(404).json({ message: 'Task not found' });
        }
        const task = taskRes.rows[0];

        // Free tier access check: Only first lesson (display_order = 1) is accessible
        if (req.user) {
            const userResult = await db.query('SELECT subscription_plan, subscription_status FROM users WHERE id = ?', [req.user.id]);
            const userPlan = userResult.rows[0];
            
            // Block cancelled subscriptions
            if (userPlan.subscription_status === 'cancelled' || userPlan.subscription_status === 'canceled') {
                return res.status(403).json({ 
                    message: 'Your subscription has expired. Please upgrade to continue.',
                    requires_upgrade: true
                });
            }
            
            // Free tier: Only task 349 (first EDR lesson) is accessible
            if (userPlan.subscription_plan === 'free' && parseInt(taskId) !== 349) {
                return res.status(403).json({ 
                    message: 'Free tier is limited to the first lesson. Upgrade to unlock all content.',
                    requires_upgrade: true
                });
            }
        }

        // Get lesson content
        const content = await db.query(
            'SELECT * FROM lesson_content WHERE task_id = ?',
            [taskId]
        );

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

        // Get questions for this lesson
        const questions = await db.query(
            'SELECT id, question_text, question_order, hint, options FROM lesson_questions WHERE task_id = ? ORDER BY question_order ASC',
            [taskId]
        );

        // If user is authenticated, get their submitted answers and grade
        let userAnswers = [];
        let userGrade = null;
        if (req.user) {
            const answers = await db.query(
                `SELECT ula.question_id, ula.submitted_answer, ula.is_correct, ula.submitted_at 
                 FROM user_lesson_answers ula 
                 JOIN lesson_questions lq ON ula.question_id = lq.id 
                 WHERE ula.user_id = ? AND lq.task_id = ?`,
                [req.user.id, taskId]
            );
            userAnswers = answers.rows;

            // Get user's grade if they've completed this lesson
            const grade = await db.query(
                'SELECT * FROM user_lesson_grades WHERE user_id = ? AND task_id = ?',
                [req.user.id, taskId]
            );
            if (grade.rows.length > 0) {
                userGrade = grade.rows[0];
            }
        }

        // Find next task in the same module
        const nextTaskRes = await db.query(
            'SELECT id FROM tasks WHERE module_id = ? AND display_order > ? AND is_active = TRUE ORDER BY display_order ASC LIMIT 1',
            [task.module_id, task.display_order]
        );
        const nextTaskId = nextTaskRes.rows.length > 0 ? nextTaskRes.rows[0].id : null;

        res.json({
            task: task,
            content: content.rows[0],
            questions: questions.rows,
            user_answers: userAnswers,
            user_grade: userGrade,
            next_task_id: nextTaskId
        });
    } catch (error) {
        console.error('Error fetching lesson content:', error);
        res.status(500).json({ message: 'Failed to fetch lesson content' });
    }
};

// Submit answer for a question
const submitAnswer = async (req, res) => {
    try {
        const { taskId, questionId } = req.params;
        const { answer } = req.body;
        const userId = req.user.id;

        // Get the correct answer
        const question = await db.query(
            'SELECT * FROM lesson_questions WHERE id = ? AND task_id = ?',
            [questionId, taskId]
        );

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

        const correctAnswer = question.rows[0].correct_answer;
        const caseSensitive = question.rows[0].case_sensitive;

        // Check if answer is correct
        let isCorrect = false;
        if (caseSensitive) {
            isCorrect = answer.trim() === correctAnswer.trim();
        } else {
            isCorrect = answer.trim().toLowerCase() === correctAnswer.trim().toLowerCase();
        }

        // Save or update the answer
        const existing = await db.query(
            'SELECT * FROM user_lesson_answers WHERE user_id = ? AND question_id = ?',
            [userId, questionId]
        );

        if (existing.rows.length > 0) {
            await db.query(
                'UPDATE user_lesson_answers SET submitted_answer = ?, is_correct = ?, submitted_at = NOW() WHERE user_id = ? AND question_id = ?',
                [answer, isCorrect, userId, questionId]
            );
        } else {
            await db.query(
                'INSERT INTO user_lesson_answers (user_id, question_id, submitted_answer, is_correct) VALUES (?, ?, ?, ?)',
                [userId, questionId, answer, isCorrect]
            );
        }

        res.json({
            is_correct: isCorrect,
            message: isCorrect ? 'Correct!' : 'Incorrect. Try again!'
        });
    } catch (error) {
        console.error('Error submitting answer:', error);
        res.status(500).json({ message: 'Failed to submit answer' });
    }
};

// Grade the lesson and mark as complete
const gradeLesson = async (req, res) => {
    try {
        const { taskId } = req.params;
        const userId = req.user.id;
        
        // Check subscription access
        const userResult = await db.query('SELECT subscription_plan, subscription_status FROM users WHERE id = ?', [userId]);
        const userPlan = userResult.rows[0];
        
        // Block cancelled subscriptions
        if (userPlan.subscription_status === 'cancelled' || userPlan.subscription_status === 'canceled') {
            return res.status(403).json({ 
                message: 'Your subscription has expired. Please upgrade to continue.',
                requires_upgrade: true
            });
        }
        
        // Free tier limit: Max 1 completed lesson
        if (userPlan.subscription_plan === 'free') {
            const completedCount = await db.query(
                'SELECT COUNT(*) as count FROM user_task_progress WHERE user_id = ? AND completed = TRUE',
                [userId]
            );
            const alreadyCompleted = await db.query(
                'SELECT * FROM user_task_progress WHERE user_id = ? AND task_id = ? AND completed = TRUE',
                [userId, taskId]
            );
            
            // If this task is already completed, allow re-grading
            // If not completed and user already has 1+ completed lessons, block
            if (alreadyCompleted.rows.length === 0 && completedCount.rows[0].count >= 1) {
                return res.status(403).json({ 
                    message: 'Free tier is limited to 1 lesson. Upgrade to unlock all content.',
                    requires_upgrade: true
                });
            }
        }
        
        let score = 0;
        let passed = false;
        let totalQuestions = 0;
        let correctAnswers = 0;
        
        // Support for investigation-based lesson completion
        if (req.body.passed_investigation) {
            score = parseFloat(req.body.score) || 100;
            passed = score >= 80;
            totalQuestions = 1;
            correctAnswers = passed ? 1 : 0;
            
             // Save or update grade
            const existingGrade = await db.query(
                'SELECT * FROM user_lesson_grades WHERE user_id = ? AND task_id = ?',
                [userId, taskId]
            );

            if (existingGrade.rows.length > 0) {
                await db.query(
                    'UPDATE user_lesson_grades SET score = ?, total_questions = ?, correct_answers = ?, passed = ?, completed_at = NOW() WHERE user_id = ? AND task_id = ?',
                    [score, totalQuestions, correctAnswers, passed, userId, taskId]
                );
            } else {
                await db.query(
                    'INSERT INTO user_lesson_grades (user_id, task_id, score, total_questions, correct_answers, passed) VALUES (?, ?, ?, ?, ?, ?)',
                    [userId, taskId, score, totalQuestions, correctAnswers, passed]
                );
            }
        } else {
            // Standard Question-based Grading
            // Get all questions for this task
            const questions = await db.query(
                'SELECT id FROM lesson_questions WHERE task_id = ?',
                [taskId]
            );
            
            totalQuestions = questions.rows.length;
    
            if (totalQuestions === 0) {
                return res.status(404).json({ message: 'No questions found for this lesson' });
            }
    
            // Get user's answers
            const answers = await db.query(
                `SELECT ula.is_correct 
                 FROM user_lesson_answers ula 
                 JOIN lesson_questions lq ON ula.question_id = lq.id 
                 WHERE ula.user_id = ? AND lq.task_id = ?`,
                [userId, taskId]
            );
    
            // Check if all questions are answered
            if (answers.rows.length < totalQuestions) {
                return res.status(400).json({
                    message: 'Please answer all questions before submitting',
                    answered: answers.rows.length,
                    total: totalQuestions
                });
            }
    
            // Calculate score
            correctAnswers = answers.rows.filter(a => a.is_correct).length;
            score = (correctAnswers / totalQuestions) * 100;
            passed = score >= 70; // 70% passing grade
            
            // Save or update grade
             const existingGrade = await db.query(
                'SELECT * FROM user_lesson_grades WHERE user_id = ? AND task_id = ?',
                [userId, taskId]
            );

            if (existingGrade.rows.length > 0) {
                await db.query(
                    'UPDATE user_lesson_grades SET score = ?, total_questions = ?, correct_answers = ?, passed = ?, completed_at = NOW() WHERE user_id = ? AND task_id = ?',
                    [score, totalQuestions, correctAnswers, passed, userId, taskId]
                );
            } else {
                await db.query(
                    'INSERT INTO user_lesson_grades (user_id, task_id, score, total_questions, correct_answers, passed) VALUES (?, ?, ?, ?, ?, ?)',
                    [userId, taskId, score, totalQuestions, correctAnswers, passed]
                );
            }
        }

        // If passed, mark task as complete and award XP
        if (passed) {
            const taskResult = await db.query('SELECT * FROM tasks WHERE id = ?', [taskId]);
            const task = taskResult.rows[0];

            // Check if task is already completed
            const taskProgress = await db.query(
                'SELECT * FROM user_task_progress WHERE user_id = ? AND task_id = ?',
                [userId, taskId]
            );

            if (taskProgress.rows.length === 0 || !taskProgress.rows[0].completed) {
                // Mark task as complete
                if (taskProgress.rows.length > 0) {
                    await db.query(
                        'UPDATE user_task_progress SET completed = TRUE, completed_at = NOW() WHERE user_id = ? AND task_id = ?',
                        [userId, taskId]
                    );
                } else {
                    await db.query(
                        'INSERT INTO user_task_progress (user_id, task_id, completed, completed_at) VALUES (?, ?, TRUE, NOW())',
                        [userId, taskId]
                    );
                }

                // Award XP
                await awardXP(userId, task.xp_reward);

                // Update path progress
                await updatePathProgress(userId, task.module_id);

                // Check for badge unlocks
                const newBadges = await checkBadgeUnlocks(userId);

                return res.json({
                    passed: true,
                    score: score.toFixed(2),
                    correct_answers: correctAnswers,
                    total_questions: totalQuestions,
                    xp_earned: task.xp_reward,
                    badges_earned: newBadges,
                    message: 'Congratulations! Lesson completed successfully!'
                });
            }
        }

        res.json({
            passed,
            score: score.toFixed(2),
            correct_answers: correctAnswers,
            total_questions: totalQuestions,
            message: passed ? 'Lesson completed!' : 'Keep trying! You need 70% to pass.'
        });
    } catch (error) {
        console.error('Error grading lesson:', error);
        res.status(500).json({ message: 'Failed to grade lesson' });
    }
};

// Get user's progress on a lesson
const getLessonProgress = async (req, res) => {
    try {
        const { taskId } = req.params;
        const userId = req.user.id;

        // Get total questions
        const questions = await db.query(
            'SELECT COUNT(*) as count FROM lesson_questions WHERE task_id = ?',
            [taskId]
        );

        // Get answered questions
        const answers = await db.query(
            `SELECT COUNT(*) as count, SUM(CASE WHEN is_correct = 1 THEN 1 ELSE 0 END) as correct 
             FROM user_lesson_answers ula 
             JOIN lesson_questions lq ON ula.question_id = lq.id 
             WHERE ula.user_id = ? AND lq.task_id = ?`,
            [userId, taskId]
        );

        // Get grade if exists
        const grade = await db.query(
            'SELECT * FROM user_lesson_grades WHERE user_id = ? AND task_id = ?',
            [userId, taskId]
        );

        res.json({
            total_questions: questions.rows[0].count,
            answered_questions: answers.rows[0].count || 0,
            correct_answers: answers.rows[0].correct || 0,
            grade: grade.rows[0] || null
        });
    } catch (error) {
        console.error('Error fetching lesson progress:', error);
        res.status(500).json({ message: 'Failed to fetch lesson progress' });
    }
};

// Update lesson content
const updateLessonContent = async (req, res) => {
    try {
        const { taskId } = req.params;
        const { content } = req.body;
        const userId = req.user.id;

        // Admin check
        // Ideally checking against DB role, but using existing pattern or req.user populated by middleware
        const userRes = await db.query('SELECT role, email FROM users WHERE id = ?', [userId]);
        const user = userRes.rows[0];

        if (!user || (user.role !== 'admin' && user.email !== 'admin@infoseclabs.io')) {
            return res.status(403).json({ message: 'Not authorized to edit lessons' });
        }

        const result = await db.query(
            'UPDATE lesson_content SET content = ? WHERE task_id = ?',
            [content, taskId]
        );

        if (result.rowCount === 0) {
            // Try inserting if it doesn't exist? Or return error?
            // Usually lesson content works 1:1 with task. If not found, maybe create it.
            // Check if task exists first.
            const taskExists = await db.query('SELECT id FROM tasks WHERE id = ?', [taskId]);
            if (taskExists.rows.length === 0) {
                return res.status(404).json({ message: 'Task not found' });
            }

            // Insert if not exists
            await db.query(
                'INSERT INTO lesson_content (task_id, content) VALUES (?, ?)',
                [taskId, content]
            );
        }

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

const PDFDocument = require('pdfkit');

// Generate and download completion certificate
const getCertificate = async (req, res) => {
    try {
        const { pathId } = req.params;
        const userId = req.user.id;

        // Check if user has completed the path
        const progress = await db.query(
            'SELECT percentage_completed, completed_at, certificate_id FROM user_path_progress WHERE user_id = ? AND learning_path_id = ?',
            [userId, pathId]
        );

        if (progress.rows.length === 0 || progress.rows[0].percentage_completed < 100) {
            return res.status(403).json({ message: 'Certificate not available. Please complete all modules first.' });
        }

        // Generate certificate ID if not exists
        let certificateId = progress.rows[0].certificate_id;
        if (!certificateId) {
            const year = new Date().getFullYear();
            const random = Math.random().toString(36).substring(2, 8).toUpperCase();
            certificateId = `CERT-${year}-${pathId}-${userId}-${random}`;
            await db.query(
                'UPDATE user_path_progress SET certificate_id = ? WHERE user_id = ? AND learning_path_id = ?',
                [certificateId, userId, pathId]
            );
        }

        // Get Path and User details
        const pathData = await db.query('SELECT title, description FROM learning_paths WHERE id = ?', [pathId]);
        const userData = await db.query('SELECT name FROM users WHERE id = ?', [userId]);

        const pathTitle = pathData.rows[0].title;
        const userName = userData.rows[0].name || 'InfoSecLabs User';
        const completedDate = new Date(progress.rows[0].completed_at || Date.now()).toLocaleDateString('en-US', {
            year: 'numeric', month: 'long', day: 'numeric'
        });

        // Create PDF
        const doc = new PDFDocument({
            layout: 'landscape',
            size: 'A4',
            margin: 0
        });

        // Stream to response
        res.setHeader('Content-Type', 'application/pdf');
        res.setHeader('Content-Disposition', `attachment; filename=Certificate-${pathTitle.replace(/\s+/g, '-')}.pdf`);
        doc.pipe(res);

        // --- PDF DESIGN ---
        const width = doc.page.width;
        const height = doc.page.height;
        const path = require('path');

        // Background
        doc.rect(0, 0, width, height).fill('#1a1b1e'); // Dark background
        doc.rect(20, 20, width - 40, height - 40).lineWidth(2).stroke('#b026ff'); // Purple border
        doc.rect(25, 25, width - 50, height - 50).lineWidth(1).stroke('#4a4a6e'); // Inner border

        // Logo - Center top (Enlarged)
        const logoPath = path.join(__dirname, '../../public/logo.png');
        try {
            const fs = require('fs');
            if (fs.existsSync(logoPath)) {
                // Enlarged logo to 260px width, shifted up to 40
                doc.image(logoPath, width / 2 - 130, 40, { width: 260 });
            } else {
                // Fallback to text if logo not found
                doc.font('Helvetica-Bold').fontSize(32).fillColor('#ffffff')
                   .text('InfoSecLabs', 0, 75, { align: 'center' });
            }
        } catch (e) {
            // Fallback to text
            doc.font('Helvetica-Bold').fontSize(32).fillColor('#ffffff')
               .text('InfoSecLabs', 0, 75, { align: 'center' });
        }

        // Subheading - Shifted up to 140
        doc.font('Helvetica').fontSize(12).fillColor('#aaaaaa')
           .text('HANDS-ON CYBER DEFENSE LABS', 0, 140, { align: 'center', characterSpacing: 2 });

        // Title - Shifted up to 175
        doc.font('Helvetica-Bold').fontSize(36).fillColor('#ffffff')
           .text('CERTIFICATE OF COMPLETION', 0, 175, { align: 'center' });
        
        // Verifies Text - Shifted up to 230
        doc.font('Helvetica').fontSize(14).fillColor('#b026ff')
           .text('This verifies that', 0, 230, { align: 'center' });

        // User Name
        doc.font('Helvetica-Bold').fontSize(32).fillColor('#ffffff')
           .text(userName, 0, 280, { align: 'center' });
        
        // Underline under name
        doc.rect(width / 2 - 120, 320, 240, 1).fill('#b026ff');

        // Accomplishment Text
        doc.font('Helvetica').fontSize(14).fillColor('#cccccc')
           .text('Has successfully completed the learning path:', 0, 345, { align: 'center' });

        // Path Title - Purple to match website branding
        doc.font('Helvetica-Bold').fontSize(28).fillColor('#b026ff')
           .text(pathTitle, 0, 380, { align: 'center' });

        // Date - centered
        doc.font('Helvetica').fontSize(12).fillColor('#888888')
           .text(`Date of Completion: ${completedDate}`, 0, 450, { align: 'center' });

        // Footer - Fixed positioning for bottom
        const footerY = height - 60;
        
        // Certificate ID - Centered above footer
        doc.font('Helvetica-Bold').fontSize(10).fillColor('#888888')
           .text(`Certificate ID: ${certificateId}`, 0, footerY - 20, { align: 'center' });
        
        doc.font('Helvetica').fontSize(9).fillColor('#666666')
           .text('Verify at: infoseclabs.io/verify', 50, footerY, { width: 250, align: 'left' });

        doc.font('Helvetica').fontSize(9).fillColor('#666666')
           .text('Authorized by InfoSecLabs Training Division', width - 300, footerY, { width: 250, align: 'right' });

        doc.end();

    } catch (error) {
        console.error('Error generating certificate:', error);
        res.status(500).json({ message: 'Failed to generate certificate' });
    }
};

// Verify a certificate by ID (Public endpoint)
const verifyCertificate = async (req, res) => {
    try {
        const { certificateId } = req.params;

        if (!certificateId || !certificateId.startsWith('CERT-')) {
            return res.status(400).json({ valid: false, message: 'Invalid certificate ID format' });
        }

        // Look up the certificate
        const result = await db.query(
            `SELECT upp.certificate_id, upp.completed_at, upp.percentage_completed,
                    u.name as user_name, lp.title as path_title
             FROM user_path_progress upp
             JOIN users u ON upp.user_id = u.id
             JOIN learning_paths lp ON upp.learning_path_id = lp.id
             WHERE upp.certificate_id = ?`,
            [certificateId]
        );

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

        const cert = result.rows[0];
        const completedDate = new Date(cert.completed_at).toLocaleDateString('en-US', {
            year: 'numeric', month: 'long', day: 'numeric'
        });

        res.json({
            valid: true,
            certificate: {
                id: cert.certificate_id,
                userName: cert.user_name,
                pathTitle: cert.path_title,
                completedDate: completedDate
            }
        });
    } catch (error) {
        console.error('Error verifying certificate:', error);
        res.status(500).json({ valid: false, message: 'Failed to verify certificate' });
    }
};

// Get comprehensive user analytics for dashboard
const getUserAnalytics = async (req, res) => {
    try {
        const userId = Number(req.user.id);
        const results = {
            xp: { total: 0, level: 1, xpToNext: 100, streak_days: 0 },
            learningPaths: [],
            moduleScores: [],
            certificates: 0,
            investigations: { total: 0, completed: 0, inProgress: 0, avgGrade: 0 },
            investigationsByType: [],
            recentActivity: [],
            badges: []
        };

        // 1. XP and Level
        try {
            const xpResult = await db.query('SELECT * FROM user_xp WHERE user_id = ?', [userId]);
            if (xpResult.rows && xpResult.rows.length > 0) {
                const xpData = xpResult.rows[0];
                results.xp = {
                    total: parseInt(xpData.total_xp) || 0,
                    level: parseInt(xpData.current_level) || 1,
                    xpToNext: parseInt(xpData.xp_to_next_level) || 100,
                    streak_days: parseInt(xpData.streak_days) || 0
                };
            }
        } catch (e) { console.error('Analytics XP Error:', e.message); }

        // 2. Learning Paths Progress
        try {
            const pathsResult = await db.query(`
                SELECT lp.id, lp.title, lp.difficulty_level,
                       (SELECT (COUNT(DISTINCT CASE WHEN utp.completed = TRUE THEN utp.task_id END) * 100.0 / NULLIF(COUNT(DISTINCT t.id), 0))
                        FROM tasks t 
                        JOIN modules m ON t.module_id = m.id 
                        LEFT JOIN user_task_progress utp ON t.id = utp.task_id AND utp.user_id = ?
                        WHERE m.learning_path_id = lp.id AND t.is_active = TRUE
                       ) as percentage_completed,
                       upp.enrolled_at, upp.completed_at, upp.certificate_id,
                       (SELECT COUNT(*) FROM modules m WHERE m.learning_path_id = lp.id AND m.is_active = TRUE) as total_modules,
                       (SELECT COUNT(*) 
                        FROM modules m 
                        WHERE m.learning_path_id = lp.id AND m.is_active = TRUE 
                        AND NOT EXISTS (
                            SELECT t.id FROM tasks t 
                            WHERE t.module_id = m.id AND t.is_active = TRUE
                            AND t.id NOT IN (SELECT task_id FROM user_task_progress WHERE user_id = ? AND completed = TRUE)
                        )
                       ) as completed_modules,
                       (SELECT COUNT(*) FROM tasks t JOIN modules m ON t.module_id = m.id WHERE m.learning_path_id = lp.id AND t.is_active = TRUE) as total_tasks
                FROM learning_paths lp
                LEFT JOIN user_path_progress upp ON lp.id = upp.learning_path_id AND upp.user_id = ?
                WHERE upp.user_id IS NOT NULL 
                   OR lp.id IN (
                       SELECT DISTINCT m.learning_path_id 
                       FROM user_task_progress utp 
                       JOIN tasks t ON utp.task_id = t.id 
                       JOIN modules m ON t.module_id = m.id 
                       WHERE utp.user_id = ? AND utp.completed = TRUE
                   )
                ORDER BY COALESCE(upp.enrolled_at, (SELECT MIN(utp.completed_at) FROM user_task_progress utp JOIN tasks t ON utp.task_id = t.id JOIN modules m ON t.module_id = m.id WHERE m.learning_path_id = lp.id AND utp.user_id = ?)) DESC
            `, [userId, userId, userId, userId, userId]);
            results.learningPaths = pathsResult.rows || [];
        } catch (e) { console.error('Analytics Paths Error:', e.message); }

        // 3. Module-level scores (for radar chart)
        try {
            const moduleScoresResult = await db.query(`
                SELECT m.id, m.title, m.learning_path_id,
                       COUNT(DISTINCT t.id) as total_tasks,
                       COUNT(DISTINCT utp.task_id) as completed_tasks,
                       COALESCE(AVG(ulg.score), 0) as avg_score
                FROM modules m
                JOIN tasks t ON t.module_id = m.id
                LEFT JOIN user_task_progress utp ON utp.task_id = t.id AND utp.user_id = ? AND utp.completed = TRUE
                LEFT JOIN user_lesson_grades ulg ON ulg.task_id = t.id AND ulg.user_id = ? AND ulg.passed = TRUE
                WHERE m.is_active = TRUE AND t.is_active = TRUE
                GROUP BY m.id, m.title, m.learning_path_id
                HAVING completed_tasks > 0
                ORDER BY avg_score DESC
                LIMIT 10
            `, [userId, userId]);
            results.moduleScores = (moduleScoresResult.rows || []).map(m => ({
                ...m,
                avg_score: parseFloat(m.avg_score) || 0
            }));
        } catch (e) { console.error('Analytics Modules Error:', e.message); }

        // 4. Certificates earned
        try {
            const certResult = await db.query('SELECT COUNT(*) as count FROM user_path_progress WHERE user_id = ? AND certificate_id IS NOT NULL', [userId]);
            results.certificates = parseInt(certResult.rows[0]?.count) || 0;
        } catch (e) { console.error('Analytics Certs Error:', e.message); }

        try {
            const invStatsResult = await db.query(`
                SELECT 
                    COUNT(*) as total_investigations,
                    AVG(CASE WHEN grade IS NOT NULL THEN grade ELSE NULL END) as avg_grade,
                    COUNT(CASE WHEN status IN ('graded', 'completed') THEN 1 END) as completed_investigations,
                    COUNT(CASE WHEN status IN ('investigating', 'submitted', 'pending', 'in progress') THEN 1 END) as in_progress
                FROM investigations WHERE user_id = ?
            `, [userId]);
            if (invStatsResult.rows && invStatsResult.rows.length > 0) {
                const invStats = invStatsResult.rows[0];
                results.investigations = {
                    total: parseInt(invStats.total_investigations) || 0,
                    completed: parseInt(invStats.completed_investigations) || 0,
                    inProgress: parseInt(invStats.in_progress) || 0,
                    avgGrade: invStats.avg_grade ? Math.round(parseFloat(invStats.avg_grade)) : 0
                };
            }
        } catch (e) { console.error('Analytics Inv Error:', e.message); }

        // 6. Investigations by Alert Type
        try {
            const invByTypeResult = await db.query(`
                SELECT a.alert_type, COUNT(*) as count, AVG(i.grade) as avg_grade
                FROM investigations i
                JOIN alerts a ON i.alert_id = a.id
                WHERE i.user_id = ? AND i.status IN ('graded', 'completed')
                GROUP BY a.alert_type
                ORDER BY count DESC
                LIMIT 6
            `, [userId]);
            results.investigationsByType = invByTypeResult.rows || [];
        } catch (e) { console.error('Analytics InvType Error:', e.message); }

        // 7. Recent Activity (last 10 completions)
        try {
            const activityResult = await db.query(`
                (SELECT 'lesson' as type, t.title as title, ulg.completed_at as date, ulg.score
                 FROM user_lesson_grades ulg
                 JOIN tasks t ON ulg.task_id = t.id
                 WHERE ulg.user_id = ? AND ulg.passed = TRUE
                 ORDER BY ulg.completed_at DESC LIMIT 5)
                UNION ALL
                (SELECT 'investigation' as type, a.title as title, i.updated_at as date, i.grade as score
                 FROM investigations i
                 JOIN alerts a ON i.alert_id = a.id
                 WHERE i.user_id = ? AND i.status IN ('graded', 'completed')
                 ORDER BY i.updated_at DESC LIMIT 5)
                ORDER BY date DESC LIMIT 10
            `, [userId, userId]);
            results.recentActivity = activityResult.rows || [];
        } catch (e) { console.error('Analytics Activity Error:', e.message); }

        // 8. Badges
        try {
            const badgesResult = await db.query(`
                SELECT b.name, b.icon_url, b.description, ub.earned_at
                FROM user_badges ub
                JOIN badges b ON ub.badge_id = b.id
                WHERE ub.user_id = ?
                ORDER BY ub.earned_at DESC
            `, [userId]);
            results.badges = badgesResult.rows || [];
        } catch (e) { console.error('Analytics Badges Error:', e.message); }

        res.json(results);

    } catch (error) {
        console.error('Critical Analytics Error:', error.message);
        res.status(500).json({ message: 'Failed to fetch analytics' });
    }
};

// Fetch user's earned certificates
const getMyCertificates = async (req, res) => {
    try {
        const userId = req.user.id;
        const result = await db.query(`
            SELECT upp.certificate_id, upp.completed_at, lp.title as path_title, lp.id as path_id
            FROM user_path_progress upp
            JOIN learning_paths lp ON upp.learning_path_id = lp.id
            WHERE upp.user_id = ? AND upp.certificate_id IS NOT NULL AND upp.percentage_completed = 100
            ORDER BY upp.completed_at DESC
        `, [userId]);

        res.json({ certificates: result.rows || [] });
    } catch (error) {
        console.error('Error fetching certificates:', error);
        res.status(500).json({ message: 'Failed to fetch certificates' });
    }
};

module.exports = {
    getLearningPaths,
    getPathModules,
    getModuleTasks,
    enrollInPath,
    completeTask,
    getUserProgress,
    getLessonContent,
    submitAnswer,
    gradeLesson,
    getLessonProgress,
    updateLessonContent,
    getCertificate,
    verifyCertificate,
    getUserAnalytics,
    getMyCertificates
};
