
const db = require('../src/db');
const dotenv = require('dotenv');
dotenv.config();

async function fixDb() {
    try {
        console.log('Fixing DB...');
        
        // Create user_badges table
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS user_badges (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                badge_id INT NOT NULL,
                awarded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE,
                UNIQUE KEY unique_user_badge (user_id, badge_id)
            );
        `;
        await db.query(createTableQuery);
        console.log('Created user_badges table.');

        // Check if badges exist
        const badgesRes = await db.query("SELECT count(*) as count FROM badges");
        console.log('Badges count:', badgesRes.rows[0].count);
        
        // Add mobile badges if not exist
        await db.query(`
            INSERT IGNORE INTO badges (name, description, icon_url, badge_type, xp_reward, created_at, criteria) 
            SELECT 'Field Operative', 'Awarded for completing your first mobile investigation.', '📱', 'milestone', 100, NOW(), '{"manual": true}' 
            WHERE NOT EXISTS (SELECT 1 FROM badges WHERE name = 'Field Operative');
        `);
        console.log('Added Field Operative badge (if missing).');

        console.log('DB Fix Complete.');
        process.exit(0);
    } catch (error) {
        console.error('Error fixing DB:', error);
        process.exit(1);
    }
}

fixDb();
