142 lines
4.9 KiB
JavaScript
142 lines
4.9 KiB
JavaScript
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');
|