const db = require('../db');
const badgeService = require('../services/badgeService');

// The missing badges to ensure exist in the badges table
const missingBadges = [
    { name: 'Golden Analyst', description: 'Awarded for 1st Place in the monthly leaderboard (Gold).', badge_type: 'special', icon_url: '🏆', xp_reward: 500 },
    { name: 'Silver Analyst', description: 'Awarded for 2nd Place in the monthly leaderboard (Silver).', badge_type: 'special', icon_url: '🥈', xp_reward: 250 },
    { name: 'Bronze Analyst', description: 'Awarded for 3rd Place in the monthly leaderboard (Bronze).', badge_type: 'special', icon_url: '🥉', xp_reward: 100 },
    { name: 'The Grinder', description: 'Awarded for completing 50 graded investigations.', badge_type: 'milestone', icon_url: '⚙️', xp_reward: 250 },
    { name: 'Elite Investigator', description: 'Awarded for completing 100 graded investigations.', badge_type: 'milestone', icon_url: '🦅', xp_reward: 500 }
];

async function migrate() {
    console.log("Starting Badge Migration and Synchronization Script...");
    try {
        // 1. Insert missing badges into `badges` table
        console.log("-- Ensuring required badges exist --");
        for (const b of missingBadges) {
            const exists = await db.query('SELECT id FROM badges WHERE name = ?', [b.name]);
            if (exists.rows.length === 0) {
                await db.query(
                    'INSERT INTO badges (name, description, badge_type, icon_url, xp_reward) VALUES (?, ?, ?, ?, ?)',
                    [b.name, b.description, b.badge_type, b.icon_url, b.xp_reward]
                );
                console.log(`Inserted '${b.name}' into badges table.`);
            } else {
                console.log(`'${b.name}' already exists.`);
            }
        }

        // Cache badge IDs by name
        const allBadgesResult = await db.query('SELECT id, name FROM badges');
        const badgeMap = {};
        allBadgesResult.rows.forEach(row => {
            badgeMap[row.name] = row.id;
        });

        // 2. Fetch all users to migrate JSON badges and retroactively check stats
        console.log("\\n-- Fetching all users --");
        const users = await db.query('SELECT id, username, badges FROM users');
        console.log(`Found ${users.rows.length} users to process.`);

        for (const user of users.rows) {
            // A. Migrate from JSON column
            let oldBadges = [];
            try {
                if (typeof user.badges === 'string' && user.badges.trim() !== '') {
                    oldBadges = JSON.parse(user.badges);
                } else if (Array.isArray(user.badges)) {
                    oldBadges = user.badges;
                }
            } catch (e) {
                // Ignore parse errors
            }

            for (const ob of oldBadges) {
                if (!ob.name) continue;
                
                // If it's a known badge, check if it's already in user_badges
                const badgeId = badgeMap[ob.name];
                if (badgeId) {
                    const existingUb = await db.query(
                        'SELECT id FROM user_badges WHERE user_id = ? AND badge_id = ? AND DATE(awarded_at) = DATE(?)',
                        [user.id, badgeId, ob.awarded_at] // Match by date to avoid re-inserting same legacy award
                    );

                    if (existingUb.rows.length === 0) {
                        try {
                            const dateToUse = new Date(ob.awarded_at);
                            if (isNaN(dateToUse.getTime())) {
                                await db.query(
                                    'INSERT INTO user_badges (user_id, badge_id) VALUES (?, ?)',
                                    [user.id, badgeId]
                                );
                            } else {
                                await db.query(
                                    'INSERT INTO user_badges (user_id, badge_id, awarded_at) VALUES (?, ?, ?)',
                                    [user.id, badgeId, dateToUse]
                                );
                            }
                            console.log(`[User ${user.username}] Migrated legacy JSON badge: ${ob.name}`);
                        } catch (err) {
                            console.error(`Error migrating JSON badge ${ob.name} for user ${user.username}`, err.message);
                        }
                    }
                }
            }

            // B. Retroactively check Volume Badges (The Grinder, Elite)
            // badgeService.checkVolumeBadges awards if count >= 50 or 100
            await badgeService.checkVolumeBadges(user.id);

            // C. Retroactively test Academy completions
            // 1. Linux Novice (completed Linux Fundamentals module)
            const linuxMod = await db.query('SELECT id FROM modules WHERE title = ?', ['Linux Fundamentals']);
            if (linuxMod.rows.length > 0) {
                const modId = linuxMod.rows[0].id;
                // Count active tasks for this module
                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, user.id]);
                    if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                        await badgeService.awardBadge(user.id, 'Linux Novice');
                    }
                }
            }

            // 2. 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, user.id]);
                    if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                        await badgeService.awardBadge(user.id, 'Windows Explorer');
                    }
                }
            }

            // 3. 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, user.id]);
                    if (parseInt(tasksDone.rows[0].c) >= reqCount) {
                        await badgeService.awardBadge(user.id, 'Network Navigator');
                    }
                }
            }

            // 4. 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, user.id]);
                if (progress.rows.length > 0 && parseFloat(progress.rows[0].percentage_completed) >= 100) {
                    await badgeService.awardBadge(user.id, 'Security Foundations');
                }
            }
            
            // 5. First Steps (At least 1 task complete)
            const tasksDoneTotal = await db.query('SELECT COUNT(*) as c FROM user_task_progress WHERE user_id = ? AND completed = TRUE', [user.id]);
            if (parseInt(tasksDoneTotal.rows[0].c) >= 1) {
                await badgeService.awardBadge(user.id, 'First Steps');
            }
        }

        console.log("\\nMigration and Sync complete.");

    } catch (e) {
        console.error('Migration Error:', e);
    } finally {
        process.exit();
    }
}

migrate();
