/**
 * NinjaCross Achievement System
 * 
 * JavaScript-basierte Achievement-Logik für das NinjaCross Parkour System.
 * Liest Achievement-Definitionen aus der Datenbank und prüft/vergibt Achievements.
 * 
 * @author NinjaCross Team
 * @version 1.0.0
 */

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

// Database connection
const pool = new Pool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false
});

class AchievementSystem {
    constructor() {
        this.achievements = new Map();
        this.playerAchievements = new Map();
    }

    /**
     * Lädt alle aktiven Achievements aus der Datenbank
     */
    async loadAchievements() {
        try {
            const result = await pool.query(`
                SELECT id, name, description, category, condition_type, condition_value, icon, points, can_be_earned_multiple_times
                FROM achievements 
                WHERE is_active = true
                ORDER BY category, condition_type
            `);
            
            this.achievements.clear();
            result.rows.forEach(achievement => {
                this.achievements.set(achievement.id, achievement);
            });
            
            console.log(`📋 ${this.achievements.size} Achievements geladen`);
            return true;
        } catch (error) {
            console.error('❌ Fehler beim Laden der Achievements:', error);
            return false;
        }
    }

    /**
     * Lädt alle Spieler-Achievements für einen Spieler
     * Jetzt werden alle Completions geladen (nicht nur die neueste)
     */
    async loadPlayerAchievements(playerId) {
        try {
            // Initialisiere immer eine leere Map für den Spieler
            this.playerAchievements.set(playerId, new Map());
            
            const result = await pool.query(`
                SELECT pa.achievement_id, pa.progress, pa.is_completed, pa.earned_at
                FROM player_achievements pa
                WHERE pa.player_id = $1
                ORDER BY pa.earned_at DESC
            `, [playerId]);
            
            // Gruppiere nach achievement_id und zähle Completions
            const achievementCounts = new Map();
            
            result.rows.forEach(pa => {
                if (!achievementCounts.has(pa.achievement_id)) {
                    achievementCounts.set(pa.achievement_id, {
                        count: 0,
                        latest: pa
                    });
                }
                achievementCounts.get(pa.achievement_id).count++;
            });
            
            // Speichere die neueste Completion und die Anzahl
            achievementCounts.forEach((data, achievementId) => {
                this.playerAchievements.get(playerId).set(achievementId, {
                    ...data.latest,
                    completion_count: data.count
                });
            });
            
            // Count unique achievements and duplicates
            const uniqueAchievements = new Set(result.rows.map(row => row.achievement_id));
            const totalCount = result.rows.length;
            const uniqueCount = uniqueAchievements.size;
            const duplicateCount = totalCount - uniqueCount;
            
            if (duplicateCount > 0) {
                console.log(`📋 ${totalCount} Achievements für Spieler ${playerId} geladen (${uniqueCount} eindeutige, ${duplicateCount} doppelt)`);
            } else {
                console.log(`📋 ${totalCount} Achievements für Spieler ${playerId} geladen`);
            }
            return true;
        } catch (error) {
            console.error(`❌ Fehler beim Laden der Spieler-Achievements für ${playerId}:`, error);
            return false;
        }
    }

    /**
     * Prüft alle Achievements für einen Spieler
     */
    async checkAllAchievements(playerId) {
        console.log(`🎯 Prüfe Achievements für Spieler ${playerId}...`);
        
        // Lade Spieler-Achievements
        await this.loadPlayerAchievements(playerId);
        
        const newAchievements = [];
        
        // Prüfe alle Achievement-Kategorien
        await this.checkConsistencyAchievements(playerId, newAchievements);
        await this.checkImprovementAchievements(playerId, newAchievements);
        await this.checkSeasonalAchievements(playerId, newAchievements);
        await this.checkBestTimeAchievements(playerId, newAchievements);
        
        console.log(`✅ ${newAchievements.length} neue Achievements für Spieler ${playerId}`);
        return newAchievements;
    }

    /**
     * Prüft sofortige Achievements für einen Spieler (bei neuer Zeit)
     * Diese Achievements werden sofort vergeben, nicht nur um 19:00 Uhr
     */
    async checkImmediateAchievements(playerId) {
        console.log(`⚡ Prüfe sofortige Achievements für Spieler ${playerId}...`);
        
        // Lade alle Achievements (falls noch nicht geladen)
        if (this.achievements.size === 0) {
            console.log('📋 Lade alle Achievements...');
            await this.loadAchievements();
        }
        
        // Lade Spieler-Achievements
        await this.loadPlayerAchievements(playerId);
        
        const newAchievements = [];
        
        // Prüfe nur sofortige Achievement-Kategorien
        await this.checkImmediateConsistencyAchievements(playerId, newAchievements);
        await this.checkImprovementAchievements(playerId, newAchievements);
        await this.checkSeasonalAchievements(playerId, newAchievements);
        // Best-Time Achievements werden NICHT sofort geprüft (nur um 19:00 Uhr)
        
        if (newAchievements.length > 0) {
            console.log(`🏆 ${newAchievements.length} sofortige Achievements für Spieler ${playerId}:`);
            newAchievements.forEach(achievement => {
                console.log(`   ${achievement.icon} ${achievement.name} (+${achievement.points} Punkte)`);
            });
        }
        
        return newAchievements;
    }

    /**
     * Prüft Konsistenz-basierte Achievements
     */
    async checkConsistencyAchievements(playerId, newAchievements) {
        // Erste Schritte
        await this.checkFirstTimeAchievement(playerId, newAchievements);
        
        // Versuche pro Tag
        await this.checkAttemptsPerDayAchievements(playerId, newAchievements);
        
        // Einzigartige Tage
        await this.checkUniqueDaysAchievements(playerId, newAchievements);
    }

    /**
     * Prüft nur sofortige Konsistenz-basierte Achievements
     */
    async checkImmediateConsistencyAchievements(playerId, newAchievements) {
        // Erste Schritte
        await this.checkFirstTimeAchievement(playerId, newAchievements);
        
        // Versuche pro Tag
        await this.checkAttemptsPerDayAchievements(playerId, newAchievements);
        
        // Einzigartige Tage werden NICHT sofort geprüft (nur um 19:00 Uhr)
    }

    /**
     * Prüft "Erste Schritte" Achievement
     */
    async checkFirstTimeAchievement(playerId, newAchievements) {
        const achievement = Array.from(this.achievements.values())
            .find(a => a.category === 'consistency' && a.condition_type === 'first_time');
        
        if (!achievement) return;

        // Prüfe ob bereits erreicht
        if (this.isAchievementCompleted(playerId, achievement.id)) return;

        // Zähle Gesamtversuche
        const result = await pool.query(`
            SELECT COUNT(*) as count
            FROM times t
            WHERE t.player_id = $1
        `, [playerId]);

        const totalAttempts = parseInt(result.rows[0].count);
        
        if (totalAttempts >= achievement.condition_value) {
            await this.awardAchievement(playerId, achievement, totalAttempts, newAchievements);
        }
    }

    /**
     * Prüft "Versuche pro Tag" Achievements
     */
    async checkAttemptsPerDayAchievements(playerId, newAchievements) {
        const achievements = Array.from(this.achievements.values())
            .filter(a => a.category === 'consistency' && a.condition_type === 'attempts_per_day');

        // Zähle Versuche heute
        const result = await pool.query(`
            SELECT COUNT(*) as count
            FROM times t
            WHERE t.player_id = $1 
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
        `, [playerId]);

        const attemptsToday = parseInt(result.rows[0].count);

        for (const achievement of achievements) {
            if (this.isAchievementCompleted(playerId, achievement.id)) continue;
            
            // Prüfe ob das Achievement heute bereits vergeben wurde
            const alreadyEarnedToday = await pool.query(`
                SELECT COUNT(*) as count
                FROM player_achievements pa
                WHERE pa.player_id = $1 
                AND pa.achievement_id = $2
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
            `, [playerId, achievement.id]);
            
            if (parseInt(alreadyEarnedToday.rows[0].count) > 0) continue;
            
            if (attemptsToday >= achievement.condition_value) {
                await this.awardAchievement(playerId, achievement, attemptsToday, newAchievements);
            }
        }
    }

    /**
     * Prüft "Einzigartige Tage" Achievements
     */
    async checkUniqueDaysAchievements(playerId, newAchievements) {
        const achievements = Array.from(this.achievements.values())
            .filter(a => a.category === 'consistency' && a.condition_type === 'unique_days');

        // Zähle einzigartige Tage
        const result = await pool.query(`
            SELECT COUNT(DISTINCT DATE(t.created_at AT TIME ZONE 'Europe/Berlin')) as count
            FROM times t
            WHERE t.player_id = $1
        `, [playerId]);

        const uniqueDays = parseInt(result.rows[0].count);

        for (const achievement of achievements) {
            if (this.isAchievementCompleted(playerId, achievement.id)) continue;
            
            if (uniqueDays >= achievement.condition_value) {
                await this.awardAchievement(playerId, achievement, uniqueDays, newAchievements);
            }
        }
    }

    /**
     * Prüft Verbesserungs-basierte Achievements
     */
    async checkImprovementAchievements(playerId, newAchievements) {
        const achievements = Array.from(this.achievements.values())
            .filter(a => a.category === 'improvement' && a.condition_type === 'time_improvement');

        // Hole beste und zweitbeste Zeit
        const result = await pool.query(`
            SELECT 
                MIN(recorded_time) as best_time,
                (SELECT MIN(recorded_time) 
                 FROM times t2 
                 WHERE t2.player_id = $1 
                 AND t2.recorded_time > (SELECT MIN(recorded_time) FROM times t3 WHERE t3.player_id = $1)
                ) as second_best_time
            FROM times t
            WHERE t.player_id = $1
        `, [playerId]);

        const { best_time, second_best_time } = result.rows[0];
        
        if (!best_time || !second_best_time) return;

        // Berechne Verbesserung in Sekunden
        const improvementSeconds = Math.floor((new Date(second_best_time) - new Date(best_time)) / 1000);

        for (const achievement of achievements) {
            if (this.isAchievementCompleted(playerId, achievement.id)) continue;
            
            if (improvementSeconds >= achievement.condition_value) {
                await this.awardAchievement(playerId, achievement, improvementSeconds, newAchievements);
            }
        }
    }

    /**
     * Prüft Saisonale Achievements
     */
    async checkSeasonalAchievements(playerId, newAchievements) {
        const now = new Date();
        const currentHour = now.getHours();
        const currentMonth = now.getMonth() + 1; // JavaScript months are 0-based
        const currentDayOfWeek = now.getDay(); // 0 = Sunday

        // Zeit-basierte Achievements
        await this.checkTimeBasedAchievements(playerId, currentHour, currentDayOfWeek, newAchievements);
        
        // Monatliche Achievements
        await this.checkMonthlyAchievements(playerId, currentMonth, newAchievements);
        
        // Jahreszeiten-Achievements
        await this.checkSeasonalTimeAchievements(playerId, currentMonth, newAchievements);
    }

    /**
     * Prüft zeit-basierte Achievements (Wochenende, Morgen, etc.)
     */
    async checkTimeBasedAchievements(playerId, currentHour, currentDayOfWeek, newAchievements) {
        const timeAchievements = [
            { condition: 'weekend_play', check: () => currentDayOfWeek === 0 || currentDayOfWeek === 6 },
            { condition: 'morning_play', check: () => currentHour < 10 },
            { condition: 'afternoon_play', check: () => currentHour >= 14 && currentHour < 18 },
            { condition: 'evening_play', check: () => currentHour >= 18 }
        ];

        for (const timeAchievement of timeAchievements) {
            if (!timeAchievement.check()) continue;

            const achievements = Array.from(this.achievements.values())
                .filter(a => a.category === 'seasonal' && a.condition_type === timeAchievement.condition);

            for (const achievement of achievements) {
                if (this.isAchievementCompleted(playerId, achievement.id)) continue;

                // Prüfe ob das Achievement heute bereits vergeben wurde
                const alreadyEarnedToday = await pool.query(`
                    SELECT COUNT(*) as count
                    FROM player_achievements pa
                    WHERE pa.player_id = $1 
                    AND pa.achievement_id = $2
                    AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                `, [playerId, achievement.id]);
                
                if (parseInt(alreadyEarnedToday.rows[0].count) > 0) continue;

                // Prüfe ob Spieler zu dieser Zeit gespielt hat
                const hasPlayed = await this.checkTimeBasedPlay(playerId, timeAchievement.condition);
                
                if (hasPlayed) {
                    await this.awardAchievement(playerId, achievement, 1, newAchievements);
                }
            }
        }
    }

    /**
     * Prüft ob Spieler zu einer bestimmten Zeit gespielt hat
     */
    async checkTimeBasedPlay(playerId, conditionType) {
        let query = '';
        
        switch (conditionType) {
            case 'weekend_play':
                query = `
                    SELECT EXISTS(
                        SELECT 1 FROM times t 
                        WHERE t.player_id = $1 
                        AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                        AND EXTRACT(DOW FROM t.created_at AT TIME ZONE 'Europe/Berlin') IN (0, 6)
                    ) as has_played
                `;
                break;
            case 'morning_play':
                query = `
                    SELECT EXISTS(
                        SELECT 1 FROM times t 
                        WHERE t.player_id = $1 
                        AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                        AND EXTRACT(HOUR FROM t.created_at AT TIME ZONE 'Europe/Berlin') < 10
                    ) as has_played
                `;
                break;
            case 'afternoon_play':
                query = `
                    SELECT EXISTS(
                        SELECT 1 FROM times t 
                        WHERE t.player_id = $1 
                        AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                        AND EXTRACT(HOUR FROM t.created_at AT TIME ZONE 'Europe/Berlin') BETWEEN 14 AND 17
                    ) as has_played
                `;
                break;
            case 'evening_play':
                query = `
                    SELECT EXISTS(
                        SELECT 1 FROM times t 
                        WHERE t.player_id = $1 
                        AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
                        AND EXTRACT(HOUR FROM t.created_at AT TIME ZONE 'Europe/Berlin') >= 18
                    ) as has_played
                `;
                break;
        }

        if (!query) return false;

        const result = await pool.query(query, [playerId]);
        return result.rows[0].has_played;
    }

    /**
     * Prüft monatliche Achievements (einmal pro Jahr)
     */
    async checkMonthlyAchievements(playerId, currentMonth, newAchievements) {
        const monthNames = [
            'january', 'february', 'march', 'april', 'may', 'june',
            'july', 'august', 'september', 'october', 'november', 'december'
        ];

        const currentMonthName = monthNames[currentMonth - 1];
        const currentYear = new Date().getFullYear();
        
        const achievements = Array.from(this.achievements.values())
            .filter(a => a.category === 'monthly' && a.condition_type === currentMonthName);

        for (const achievement of achievements) {
            if (this.isAchievementCompleted(playerId, achievement.id)) continue;

            // Prüfe ob Spieler in diesem Monat dieses Jahres gespielt hat
            const result = await pool.query(`
                SELECT EXISTS(
                    SELECT 1 FROM times t 
                    WHERE t.player_id = $1 
                    AND EXTRACT(MONTH FROM t.created_at AT TIME ZONE 'Europe/Berlin') = $2
                    AND EXTRACT(YEAR FROM t.created_at AT TIME ZONE 'Europe/Berlin') = $3
                ) as has_played
            `, [playerId, currentMonth, currentYear]);

            if (result.rows[0].has_played) {
                // Prüfe ob Achievement bereits in diesem Jahr erreicht wurde
                const alreadyEarnedThisYear = await pool.query(`
                    SELECT COUNT(*) as count
                    FROM player_achievements pa
                    WHERE pa.player_id = $1 
                    AND pa.achievement_id = $2
                    AND EXTRACT(YEAR FROM pa.earned_at AT TIME ZONE 'Europe/Berlin') = $3
                `, [playerId, achievement.id, currentYear]);

                if (parseInt(alreadyEarnedThisYear.rows[0].count) === 0) {
                    await this.awardAchievement(playerId, achievement, 1, newAchievements);
                }
            }
        }
    }

    /**
     * Prüft Jahreszeiten-Achievements (einmal pro Jahr)
     */
    async checkSeasonalTimeAchievements(playerId, currentMonth, newAchievements) {
        const season = this.getSeason(currentMonth);
        const currentYear = new Date().getFullYear();
        
        const achievements = Array.from(this.achievements.values())
            .filter(a => a.category === 'seasonal' && a.condition_type === season);

        for (const achievement of achievements) {
            if (this.isAchievementCompleted(playerId, achievement.id)) continue;

            // Prüfe ob Spieler in dieser Jahreszeit dieses Jahres gespielt hat
            const monthRanges = {
                spring: [3, 4, 5],
                summer: [6, 7, 8],
                autumn: [9, 10, 11],
                winter: [12, 1, 2]
            };

            const months = monthRanges[season];
            const result = await pool.query(`
                SELECT EXISTS(
                    SELECT 1 FROM times t 
                    WHERE t.player_id = $1 
                    AND EXTRACT(MONTH FROM t.created_at AT TIME ZONE 'Europe/Berlin') = ANY($2)
                    AND EXTRACT(YEAR FROM t.created_at AT TIME ZONE 'Europe/Berlin') = $3
                ) as has_played
            `, [playerId, months, currentYear]);

            if (result.rows[0].has_played) {
                // Prüfe ob Achievement bereits in diesem Jahr erreicht wurde
                const alreadyEarnedThisYear = await pool.query(`
                    SELECT COUNT(*) as count
                    FROM player_achievements pa
                    WHERE pa.player_id = $1 
                    AND pa.achievement_id = $2
                    AND EXTRACT(YEAR FROM pa.earned_at AT TIME ZONE 'Europe/Berlin') = $3
                `, [playerId, achievement.id, currentYear]);

                if (parseInt(alreadyEarnedThisYear.rows[0].count) === 0) {
                    await this.awardAchievement(playerId, achievement, 1, newAchievements);
                }
            }
        }
    }

    /**
     * Prüft Best-Time Achievements (nur um 19:00 Uhr)
     */
    async checkBestTimeAchievements(playerId, newAchievements) {
        const now = new Date();
        const currentHour = now.getHours();
        const currentDayOfWeek = now.getDay();
        const currentDate = now.toISOString().split('T')[0];
        const isLastDayOfMonth = this.isLastDayOfMonth(now);

        // Nur um 19:00 Uhr prüfen
        if (currentHour !== 19) return;

        // Tageskönig (jeden Tag um 19:00)
        await this.checkDailyBest(playerId, currentDate, newAchievements);

        // Wochenchampion (nur Sonntag um 19:00)
        if (currentDayOfWeek === 0) {
            await this.checkWeeklyBest(playerId, currentDate, newAchievements);
        }

        // Monatsmeister (nur am letzten Tag des Monats um 19:00)
        if (isLastDayOfMonth) {
            await this.checkMonthlyBest(playerId, currentDate, newAchievements);
        }
    }

    /**
     * Prüft Tageskönig Achievement pro Standort
     */
    async checkDailyBest(playerId, currentDate, newAchievements) {
        const achievement = Array.from(this.achievements.values())
            .find(a => a.category === 'time' && a.condition_type === 'best_time_daily_location');

        if (!achievement) return;

        // Hole alle Standorte, an denen der Spieler heute gespielt hat
        const locationsResult = await pool.query(`
            SELECT DISTINCT t.location_id, l.name as location_name
            FROM times t
            INNER JOIN locations l ON t.location_id = l.id
            WHERE t.player_id = $1 
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = $2
        `, [playerId, currentDate]);

        for (const location of locationsResult.rows) {
            // Prüfe ob das Achievement heute bereits für diesen Standort vergeben wurde
            const alreadyEarnedToday = await pool.query(`
                SELECT COUNT(*) as count
                FROM player_achievements pa
                WHERE pa.player_id = $1 
                AND pa.achievement_id = $2
                AND pa.location_id = $3
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE
            `, [playerId, achievement.id, location.location_id]);
            
            if (parseInt(alreadyEarnedToday.rows[0].count) > 0) continue;

            // Hole beste Zeit des Spielers heute an diesem Standort
            const playerResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.player_id = $1 
                AND t.location_id = $2
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = $3
            `, [playerId, location.location_id, currentDate]);

            // Hole beste Zeit des Tages an diesem Standort
            const dailyResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.location_id = $1
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = $2
            `, [location.location_id, currentDate]);

            const playerBest = playerResult.rows[0].best_time;
            const dailyBest = dailyResult.rows[0].best_time;

            if (playerBest && dailyBest && playerBest === dailyBest) {
                await this.awardAchievement(playerId, achievement, 1, newAchievements, location.location_id);
                console.log(`🏆 Tageskönig Achievement vergeben für Standort: ${location.location_name}`);
            }
        }
    }

    /**
     * Prüft Wochenchampion Achievement pro Standort
     */
    async checkWeeklyBest(playerId, currentDate, newAchievements) {
        const achievement = Array.from(this.achievements.values())
            .find(a => a.category === 'time' && a.condition_type === 'best_time_weekly_location');

        if (!achievement) return;

        // Berechne Woche
        const currentDateObj = new Date(currentDate);
        const dayOfWeek = currentDateObj.getDay();
        const weekStart = new Date(currentDateObj);
        weekStart.setDate(currentDateObj.getDate() - (dayOfWeek === 0 ? 6 : dayOfWeek - 1));
        const weekStartStr = weekStart.toISOString().split('T')[0];

        // Hole alle Standorte, an denen der Spieler diese Woche gespielt hat
        const locationsResult = await pool.query(`
            SELECT DISTINCT t.location_id, l.name as location_name
            FROM times t
            INNER JOIN locations l ON t.location_id = l.id
            WHERE t.player_id = $1 
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $2
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $3
        `, [playerId, weekStartStr, currentDate]);

        for (const location of locationsResult.rows) {
            // Prüfe ob das Achievement diese Woche bereits für diesen Standort vergeben wurde
            const alreadyEarnedThisWeek = await pool.query(`
                SELECT COUNT(*) as count
                FROM player_achievements pa
                WHERE pa.player_id = $1 
                AND pa.achievement_id = $2
                AND pa.location_id = $3
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') >= $4
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') <= $5
            `, [playerId, achievement.id, location.location_id, weekStartStr, currentDate]);
            
            if (parseInt(alreadyEarnedThisWeek.rows[0].count) > 0) continue;

            // Hole beste Zeit des Spielers diese Woche an diesem Standort
            const playerResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.player_id = $1 
                AND t.location_id = $2
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $3
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $4
            `, [playerId, location.location_id, weekStartStr, currentDate]);

            // Hole beste Zeit der Woche an diesem Standort
            const weeklyResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.location_id = $1
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $2
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $3
            `, [location.location_id, weekStartStr, currentDate]);

            const playerBest = playerResult.rows[0].best_time;
            const weeklyBest = weeklyResult.rows[0].best_time;

            if (playerBest && weeklyBest && playerBest === weeklyBest) {
                await this.awardAchievement(playerId, achievement, 1, newAchievements, location.location_id);
                console.log(`🏆 Wochenchampion Achievement vergeben für Standort: ${location.location_name}`);
            }
        }
    }

    /**
     * Prüft Monatsmeister Achievement pro Standort
     */
    async checkMonthlyBest(playerId, currentDate, newAchievements) {
        const achievement = Array.from(this.achievements.values())
            .find(a => a.category === 'time' && a.condition_type === 'best_time_monthly_location');

        if (!achievement) return;

        // Berechne Monatsstart
        const currentDateObj = new Date(currentDate);
        const monthStart = new Date(currentDateObj.getFullYear(), currentDateObj.getMonth(), 1);
        const monthStartStr = monthStart.toISOString().split('T')[0];

        // Hole alle Standorte, an denen der Spieler diesen Monat gespielt hat
        const locationsResult = await pool.query(`
            SELECT DISTINCT t.location_id, l.name as location_name
            FROM times t
            INNER JOIN locations l ON t.location_id = l.id
            WHERE t.player_id = $1 
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $2
            AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $3
        `, [playerId, monthStartStr, currentDate]);

        for (const location of locationsResult.rows) {
            // Prüfe ob das Achievement diesen Monat bereits für diesen Standort vergeben wurde
            const alreadyEarnedThisMonth = await pool.query(`
                SELECT COUNT(*) as count
                FROM player_achievements pa
                WHERE pa.player_id = $1 
                AND pa.achievement_id = $2
                AND pa.location_id = $3
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') >= $4
                AND DATE(pa.earned_at AT TIME ZONE 'Europe/Berlin') <= $5
            `, [playerId, achievement.id, location.location_id, monthStartStr, currentDate]);
            
            if (parseInt(alreadyEarnedThisMonth.rows[0].count) > 0) continue;

            // Hole beste Zeit des Spielers diesen Monat an diesem Standort
            const playerResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.player_id = $1 
                AND t.location_id = $2
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $3
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $4
            `, [playerId, location.location_id, monthStartStr, currentDate]);

            // Hole beste Zeit des Monats an diesem Standort
            const monthlyResult = await pool.query(`
                SELECT MIN(recorded_time) as best_time
                FROM times t
                WHERE t.location_id = $1
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') >= $2
                AND DATE(t.created_at AT TIME ZONE 'Europe/Berlin') <= $3
            `, [location.location_id, monthStartStr, currentDate]);

            const playerBest = playerResult.rows[0].best_time;
            const monthlyBest = monthlyResult.rows[0].best_time;

            if (playerBest && monthlyBest && playerBest === monthlyBest) {
                await this.awardAchievement(playerId, achievement, 1, newAchievements, location.location_id);
                console.log(`🏆 Monatsmeister Achievement vergeben für Standort: ${location.location_name}`);
            }
        }
    }

    /**
     * Vergibt ein Achievement an einen Spieler
     * Erstellt immer einen neuen Eintrag (keine Updates mehr)
     */
    async awardAchievement(playerId, achievement, progress, newAchievements, locationId = null) {
        try {
            await pool.query(`
                INSERT INTO player_achievements (player_id, achievement_id, progress, is_completed, earned_at, location_id)
                VALUES ($1, $2, $3, true, NOW(), $4)
            `, [playerId, achievement.id, progress, locationId]);

            newAchievements.push({
                id: achievement.id,
                name: achievement.name,
                description: achievement.description,
                icon: achievement.icon,
                points: achievement.points,
                progress: progress,
                locationId: locationId
            });

            const locationText = locationId ? ` (Standort: ${locationId})` : '';
            console.log(`🏆 Achievement vergeben: ${achievement.icon} ${achievement.name} (+${achievement.points} Punkte)${locationText}`);
        } catch (error) {
            console.error(`❌ Fehler beim Vergeben des Achievements ${achievement.name}:`, error);
        }
    }

    /**
     * Prüft ob ein Achievement bereits erreicht wurde
     * Berücksichtigt ob das Achievement mehrmals erreicht werden kann
     */
    isAchievementCompleted(playerId, achievementId) {
        const achievement = this.achievements.get(achievementId);
        if (!achievement) return false;
        
        // Wenn das Achievement mehrmals erreicht werden kann, ist es nie "abgeschlossen"
        if (achievement.can_be_earned_multiple_times) {
            return false;
        }
        
        // Für einmalige Achievements prüfen wir, ob sie bereits erreicht wurden
        const playerAchievements = this.playerAchievements.get(playerId);
        if (!playerAchievements) {
            console.log(`⚠️ Player achievements not loaded for ${playerId}, assuming not completed`);
            return false;
        }
        
        return playerAchievements.has(achievementId);
    }

    /**
     * Hilfsfunktionen
     */
    getSeason(month) {
        if (month >= 3 && month <= 5) return 'spring';
        if (month >= 6 && month <= 8) return 'summer';
        if (month >= 9 && month <= 11) return 'autumn';
        return 'winter';
    }

    isLastDayOfMonth(date) {
        const tomorrow = new Date(date);
        tomorrow.setDate(date.getDate() + 1);
        return tomorrow.getMonth() !== date.getMonth();
    }

    /**
     * Berechnet die Gesamtpunkte eines Spielers (inklusive aller Completions)
     */
    async getPlayerTotalPoints(playerId) {
        try {
            const result = await pool.query(`
                SELECT 
                    SUM(a.points) as total_points,
                    COUNT(pa.id) as total_completions
                FROM player_achievements pa
                INNER JOIN achievements a ON pa.achievement_id = a.id
                WHERE pa.player_id = $1 AND pa.is_completed = true
            `, [playerId]);
            
            return {
                totalPoints: parseInt(result.rows[0].total_points) || 0,
                totalCompletions: parseInt(result.rows[0].total_completions) || 0
            };
        } catch (error) {
            console.error(`❌ Fehler beim Berechnen der Gesamtpunkte für ${playerId}:`, error);
            return { totalPoints: 0, totalCompletions: 0 };
        }
    }

    /**
     * Führt tägliche Achievement-Prüfung für alle Spieler durch
     */
    async runDailyAchievementCheck() {
        console.log('🎯 Starte tägliche Achievement-Prüfung...');
        
        // Lade Achievements
        await this.loadAchievements();
        
        // Hole alle Spieler, die heute gespielt haben
        const playersResult = await pool.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
        `);

        let totalNewAchievements = 0;
        const allNewAchievements = [];

        for (const player of playersResult.rows) {
            console.log(`🔍 Prüfe Achievements für ${player.firstname} ${player.lastname}...`);
            
            const newAchievements = await this.checkAllAchievements(player.id);
            totalNewAchievements += newAchievements.length;
            
            if (newAchievements.length > 0) {
                allNewAchievements.push({
                    player: `${player.firstname} ${player.lastname}`,
                    achievements: newAchievements
                });
            }
        }

        console.log(`🎉 Tägliche Achievement-Prüfung abgeschlossen!`);
        console.log(`📊 ${totalNewAchievements} neue Achievements vergeben`);
        
        return {
            totalNewAchievements,
            playerAchievements: allNewAchievements
        };
    }
}

module.exports = AchievementSystem;