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

const pool = new Pool({
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 5432,
    database: process.env.DB_NAME || 'ninjacross',
    user: process.env.DB_USER || '',
    password: process.env.DB_PASSWORD || '',
    ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});

async function runBestTimeAchievements() {
    const client = await pool.connect();
    
    try {
        console.log('🏆 Starting best-time achievement check at 19:00...');
        
        const currentHour = new Date().getHours();
        const currentDay = new Date().getDay(); // 0 = Sunday
        const currentDate = new Date();
        const isLastDayOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0).getDate() === currentDate.getDate();
        
        console.log(`Current time: ${currentHour}:00`);
        console.log(`Is Sunday: ${currentDay === 0}`);
        console.log(`Is last day of month: ${isLastDayOfMonth}`);
        
        // Get all players who have played
        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
        `);
        
        console.log(`Found ${playersResult.rows.length} players with times`);
        
        let dailyAwards = 0;
        let weeklyAwards = 0;
        let monthlyAwards = 0;
        
        // Check best-time achievements for each player
        for (const player of playersResult.rows) {
            console.log(`Checking best-time achievements for ${player.firstname} ${player.lastname}...`);
            
            // Run best-time achievement check function
            await client.query('SELECT check_best_time_achievements_timed($1)', [player.id]);
            
            // Check if new daily achievement was earned today
            const dailyResult = 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 a.category = 'best_time' 
                AND a.condition_type = 'daily_best'
                AND pa.is_completed = true
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
            `, [player.id]);
            
            if (parseInt(dailyResult.rows[0].count) > 0) {
                dailyAwards++;
                console.log(`  🥇 Daily best achievement earned!`);
            }
            
            // Check if new weekly achievement was earned (only on Sunday)
            if (currentDay === 0) {
                const weeklyResult = 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 a.category = 'best_time' 
                    AND a.condition_type = 'weekly_best'
                    AND pa.is_completed = true
                    AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                `, [player.id]);
                
                if (parseInt(weeklyResult.rows[0].count) > 0) {
                    weeklyAwards++;
                    console.log(`  🏆 Weekly best achievement earned!`);
                }
            }
            
            // Check if new monthly achievement was earned (only on last day of month)
            if (isLastDayOfMonth) {
                const monthlyResult = 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 a.category = 'best_time' 
                    AND a.condition_type = 'monthly_best'
                    AND pa.is_completed = true
                    AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                `, [player.id]);
                
                if (parseInt(monthlyResult.rows[0].count) > 0) {
                    monthlyAwards++;
                    console.log(`  👑 Monthly best achievement earned!`);
                }
            }
        }
        
        console.log(`\n🎉 Best-time achievement check completed!`);
        console.log(`Daily awards: ${dailyAwards}`);
        console.log(`Weekly awards: ${weeklyAwards}`);
        console.log(`Monthly awards: ${monthlyAwards}`);
        
        // Get current best times for today
        const bestTimesResult = await client.query(`
            SELECT 
                'daily' as period,
                p.firstname || ' ' || p.lastname as player_name,
                MIN(t.recorded_time) as best_time
            FROM times t
            INNER JOIN players p ON t.player_id = p.id
            WHERE DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
            GROUP BY p.id, p.firstname, p.lastname
            ORDER BY MIN(t.recorded_time) ASC
            LIMIT 1
        `);
        
        if (bestTimesResult.rows.length > 0) {
            const dailyBest = bestTimesResult.rows[0];
            console.log(`\n🥇 Today's best time: ${dailyBest.player_name} - ${dailyBest.best_time}`);
        }
        
        // Get current best times for this week (if Sunday)
        if (currentDay === 0) {
            const weekStart = new Date();
            weekStart.setDate(weekStart.getDate() - weekStart.getDay());
            
            const weeklyBestResult = await client.query(`
                SELECT 
                    'weekly' as period,
                    p.firstname || ' ' || p.lastname as player_name,
                    MIN(t.recorded_time) as best_time
                FROM times t
                INNER JOIN players p ON t.player_id = p.id
                WHERE DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $1
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= CURRENT_DATE
                GROUP BY p.id, p.firstname, p.lastname
                ORDER BY MIN(t.recorded_time) ASC
                LIMIT 1
            `, [weekStart.toISOString().split('T')[0]]);
            
            if (weeklyBestResult.rows.length > 0) {
                const weeklyBest = weeklyBestResult.rows[0];
                console.log(`🏆 This week's best time: ${weeklyBest.player_name} - ${weeklyBest.best_time}`);
            }
        }
        
        // Get current best times for this month (if last day of month)
        if (isLastDayOfMonth) {
            const monthStart = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
            
            const monthlyBestResult = await client.query(`
                SELECT 
                    'monthly' as period,
                    p.firstname || ' ' || p.lastname as player_name,
                    MIN(t.recorded_time) as best_time
                FROM times t
                INNER JOIN players p ON t.player_id = p.id
                WHERE DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $1
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= CURRENT_DATE
                GROUP BY p.id, p.firstname, p.lastname
                ORDER BY MIN(t.recorded_time) ASC
                LIMIT 1
            `, [monthStart.toISOString().split('T')[0]]);
            
            if (monthlyBestResult.rows.length > 0) {
                const monthlyBest = monthlyBestResult.rows[0];
                console.log(`👑 This month's best time: ${monthlyBest.player_name} - ${monthlyBest.best_time}`);
            }
        }
        
    } catch (error) {
        console.error('❌ Error running best-time achievements:', error);
        throw error;
    } finally {
        client.release();
    }
}

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

module.exports = { runBestTimeAchievements };