const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});

async function runDailyAchievements() {
    const client = await pool.connect();
    
    try {
        console.log('🎯 Starting daily achievement check...');
        
        // Get all players who have played today
        const playersResult = await client.query(`
            SELECT DISTINCT p.id, p.firstname, p.lastname
            FROM players p
            INNER JOIN times t ON p.id = t.player_id
            WHERE DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
        `);
        
        console.log(`Found ${playersResult.rows.length} players who played today`);
        
        let totalAchievements = 0;
        
        // Check achievements for each player
        for (const player of playersResult.rows) {
            console.log(`Checking achievements for ${player.firstname} ${player.lastname}...`);
            
            // Run achievement check function
            await client.query('SELECT check_all_achievements($1)', [player.id]);
            
            // Count new achievements earned today
            const newAchievementsResult = await client.query(`
                SELECT COUNT(*) as count
                FROM player_achievements pa
                INNER JOIN achievements a ON pa.achievement_id = a.id
                WHERE pa.player_id = $1 
                AND pa.is_completed = true
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
            `, [player.id]);
            
            const newAchievements = parseInt(newAchievementsResult.rows[0].count);
            totalAchievements += newAchievements;
            
            if (newAchievements > 0) {
                console.log(`  ✅ ${newAchievements} new achievements earned!`);
                
                // Get details of new achievements
                const achievementsResult = await client.query(`
                    SELECT a.name, a.description, a.icon, a.points
                    FROM player_achievements pa
                    INNER JOIN achievements a ON pa.achievement_id = a.id
                    WHERE pa.player_id = $1 
                    AND pa.is_completed = true
                    AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                    ORDER BY pa.earned_at DESC
                `, [player.id]);
                
                achievementsResult.rows.forEach(achievement => {
                    console.log(`    ${achievement.icon} ${achievement.name} (+${achievement.points} points)`);
                });
            } else {
                console.log(`  ℹ️  No new achievements`);
            }
        }
        
        console.log(`\n🎉 Daily achievement check completed!`);
        console.log(`Total new achievements earned: ${totalAchievements}`);
        
        // Log summary statistics
        const statsResult = await client.query(`
            SELECT 
                COUNT(DISTINCT pa.player_id) as players_with_achievements,
                COUNT(pa.id) as total_achievements_earned,
                SUM(a.points) as total_points_earned
            FROM player_achievements pa
            INNER JOIN achievements a ON pa.achievement_id = a.id
            WHERE pa.is_completed = true
        `);
        
        const stats = statsResult.rows[0];
        console.log(`\n📊 Overall Statistics:`);
        console.log(`Players with achievements: ${stats.players_with_achievements}`);
        console.log(`Total achievements earned: ${stats.total_achievements_earned}`);
        console.log(`Total points earned: ${stats.total_points_earned || 0}`);
        
    } catch (error) {
        console.error('❌ Error running daily achievements:', error);
        throw error;
    } finally {
        client.release();
    }
}

// Run if called directly
if (require.main === module) {
    runDailyAchievements()
        .then(() => {
            console.log('✅ Daily achievements script completed successfully');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ Daily achievements script failed:', error);
            process.exit(1);
        });
}

module.exports = { runDailyAchievements };