const { Pool } = require('pg');
const cron = require('node-cron');

// Database connection
const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'ninjacross',
    password: 'postgres',
    port: 5432,
});

async function checkAndNotifyBestTimes() {
    try {
        console.log('🔔 Checking best times for notifications...');
        
        const currentDate = new Date();
        const today = currentDate.toISOString().split('T')[0];
        const weekStart = new Date(currentDate.setDate(currentDate.getDate() - currentDate.getDay()));
        const monthStart = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
        
        // Check daily best times
        const dailyBestQuery = `
            WITH daily_best AS (
                SELECT 
                    t.player_id,
                    MIN(t.recorded_time) as best_time,
                    p.name as player_name,
                    p.email
                FROM times t
                JOIN players p ON t.player_id = p.id
                WHERE DATE(t.created_at AT TIME ZONE 'Europe/Berlin') = $1
                GROUP BY t.player_id, p.name, p.email
            ),
            global_daily_best AS (
                SELECT MIN(best_time) as global_best
                FROM daily_best
            )
            SELECT 
                db.player_id,
                db.player_name,
                db.email,
                db.best_time,
                gdb.global_best
            FROM daily_best db
            CROSS JOIN global_daily_best gdb
            WHERE db.best_time = gdb.global_best
        `;
        
        const dailyResult = await pool.query(dailyBestQuery, [today]);
        
        for (const row of dailyResult.rows) {
            console.log(`🏆 Daily best time: ${row.player_name} with ${row.best_time}`);
            // Here we would send the notification
            // For now, just log it
        }
        
        // Check weekly best times
        const weeklyBestQuery = `
            WITH weekly_best AS (
                SELECT 
                    t.player_id,
                    MIN(t.recorded_time) as best_time,
                    p.name as player_name,
                    p.email
                FROM times t
                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') <= $2
                GROUP BY t.player_id, p.name, p.email
            ),
            global_weekly_best AS (
                SELECT MIN(best_time) as global_best
                FROM weekly_best
            )
            SELECT 
                wb.player_id,
                wb.player_name,
                wb.email,
                wb.best_time,
                gwb.global_best
            FROM weekly_best wb
            CROSS JOIN global_weekly_best gwb
            WHERE wb.best_time = gwb.global_best
        `;
        
        const weeklyResult = await pool.query(weeklyBestQuery, [weekStart.toISOString().split('T')[0], today]);
        
        for (const row of weeklyResult.rows) {
            console.log(`🏆 Weekly best time: ${row.player_name} with ${row.best_time}`);
        }
        
        // Check monthly best times
        const monthlyBestQuery = `
            WITH monthly_best AS (
                SELECT 
                    t.player_id,
                    MIN(t.recorded_time) as best_time,
                    p.name as player_name,
                    p.email
                FROM times t
                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') <= $2
                GROUP BY t.player_id, p.name, p.email
            ),
            global_monthly_best AS (
                SELECT MIN(best_time) as global_best
                FROM monthly_best
            )
            SELECT 
                mb.player_id,
                mb.player_name,
                mb.email,
                mb.best_time,
                gmb.global_best
            FROM monthly_best mb
            CROSS JOIN global_monthly_best gmb
            WHERE mb.best_time = gmb.global_best
        `;
        
        const monthlyResult = await pool.query(monthlyBestQuery, [monthStart.toISOString().split('T')[0], today]);
        
        for (const row of monthlyResult.rows) {
            console.log(`🏆 Monthly best time: ${row.player_name} with ${row.best_time}`);
        }
        
        console.log('✅ Best time notifications check completed');
        
    } catch (error) {
        console.error('❌ Error checking best times:', error);
    }
}

// Schedule to run every day at 19:00 (7 PM)
cron.schedule('0 19 * * *', () => {
    console.log('🕐 Running best time notifications check...');
    checkAndNotifyBestTimes();
});

console.log('📅 Best time notifications scheduler started - runs daily at 19:00');