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

// We need a minimal badge service wrapper or direct DB calls since we are doing custom migration
// const badgeService = require('../services/badgeService'); 

async function fixBadges() {
    try {
        console.log('🔄 Starting badge fix...');

        // 1. Ensure Badges exist in DB
        const badges = [
            { name: 'Golden Analyst', type: 'special', xp: 500, desc: 'Awarded for 1st Place in the monthly leaderboard (Gold).' },
            { name: 'Silver Analyst', type: 'special', xp: 300, desc: 'Awarded for 2nd Place in the monthly leaderboard (Silver).' },
            { name: 'Bronze Analyst', type: 'special', xp: 100, desc: 'Awarded for 3rd Place in the monthly leaderboard (Bronze).' }
        ];

        for (const b of badges) {
            const check = await db.query('SELECT id FROM badges WHERE name = ?', [b.name]);
            if (check.rows.length === 0) {
                console.log(`➕ Creating badge: ${b.name}`);
                await db.query('INSERT INTO badges (name, description, badge_type, xp_reward) VALUES (?, ?, ?, ?)', 
                    [b.name, b.desc, b.type, b.xp]
                );
            }
        }

        // 2. Process Users
        const users = await db.query('SELECT id, badges FROM users');
        console.log(`👥 Processing ${users.rows.length} users...`);

        for (const user of users.rows) {
            if (!user.badges) continue;

            let userBadgesJSON = [];
            try {
                userBadgesJSON = typeof user.badges === 'string' ? JSON.parse(user.badges) : user.badges;
            } catch (e) {
                console.warn(`⚠️ Failed to parse badges for user ${user.id}`);
                continue;
            }

            if (!Array.isArray(userBadgesJSON)) continue;

            // Find legacy badges in JSON
            const specialBadges = userBadgesJSON.filter(b => 
                ['Golden Analyst', 'Silver Analyst', 'Bronze Analyst'].includes(b.name)
            );

            if (specialBadges.length > 0) {
                console.log(`👤 User ${user.id} has ${specialBadges.length} special badges in JSON.`);
                
                // Group by Name + Year + Month
                const uniqueAwards = {};

                specialBadges.forEach(b => {
                    const date = new Date(b.awarded_at || Date.now());
                    const key = `${b.name}-${date.getFullYear()}-${date.getMonth()}`; // e.g. "Golden Analyst-2026-0" (Jan is 0)
                    
                    if (!uniqueAwards[key]) {
                        uniqueAwards[key] = b;
                    } else {
                        // Keep the earliest one? Or latest? Earliest seems safer for "when they got it".
                        const existingDate = new Date(uniqueAwards[key].awarded_at);
                        if (date < existingDate) {
                            uniqueAwards[key] = b;
                        }
                    }
                });

                const badgesToMigrate = Object.values(uniqueAwards);
                console.log(`   - Consolidating to ${badgesToMigrate.length} unique monthly awards.`);

                for (const b of badgesToMigrate) {
                    // Get Badge ID
                    const badgeRes = await db.query('SELECT id FROM badges WHERE name = ?', [b.name]);
                    if (badgeRes.rows.length === 0) continue;
                    const badgeId = badgeRes.rows[0].id;

                    // Check if exists in user_badges for that month
                    // We check somewhat loosely by approximate timestamp or just existence in that month
                    const awardedDate = new Date(b.awarded_at);
                    
                    // Construct a date range for that month
                    const startOfMonth = new Date(awardedDate.getFullYear(), awardedDate.getMonth(), 1);
                    const endOfMonth = new Date(awardedDate.getFullYear(), awardedDate.getMonth() + 1, 0, 23, 59, 59);

                    const existing = await db.query(`
                        SELECT id FROM user_badges 
                        WHERE user_id = ? 
                        AND badge_id = ? 
                        AND awarded_at >= ? 
                        AND awarded_at <= ?
                    `, [user.id, badgeId, startOfMonth, endOfMonth]);

                    if (existing.rows.length === 0) {
                        console.log(`     -> Migrating ${b.name} for ${awardedDate.toISOString().slice(0, 7)}`);
                        await db.query(
                            'INSERT INTO user_badges (user_id, badge_id, awarded_at) VALUES (?, ?, ?)',
                            [user.id, badgeId, new Date(b.awarded_at)]
                        );
                    } else {
                        console.log(`     -> Skipping ${b.name} (already exists in DB for this month)`);
                    }
                }
            }
            
            // Re-sync JSON from DB Source of Truth
            // This effectively cleans up the duplicates in the JSON column
            const allBadgesResult = await db.query(`
                SELECT b.id, b.name, b.icon_url, b.description, b.badge_type, b.xp_reward, ub.awarded_at
                FROM user_badges ub 
                JOIN badges b ON ub.badge_id = b.id 
                WHERE ub.user_id = ?
                ORDER BY ub.awarded_at ASC
            `, [user.id]);

            const badgesForJson = allBadgesResult.rows.map(b => {
                let visualType = 'bronze';
                // Use type from DB or infer from XP/Name if needed
                if (b.badge_type === 'gold' || b.name.includes('Gold')) visualType = 'gold';
                else if (b.badge_type === 'silver' || b.name.includes('Silver')) visualType = 'silver';
                else if (b.badge_type === 'bronze' || b.name.includes('Bronze')) visualType = 'bronze';
                else if (b.xp_reward >= 500) visualType = 'gold';
                else if (b.xp_reward >= 100) visualType = 'silver';
                
                return {
                    id: b.id,
                    name: b.name,
                    icon: b.icon_url,
                    description: b.description,
                    type: visualType,
                    xp_reward: b.xp_reward,
                    awarded_at: b.awarded_at
                };
            });

            await db.query('UPDATE users SET badges = ? WHERE id = ?', [JSON.stringify(badgesForJson), user.id]);
        }

        console.log('✅ Badge fix handling complete.');
        process.exit(0);

    } catch (error) {
        console.error('❌ Error during badge fix:', error);
        process.exit(1);
    }
}

fixBadges();
