// routes/public.js
const express = require('express');
const { Pool } = require('pg');
const router = express.Router();

// PostgreSQL Pool mit .env Konfiguration
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
});

// Fehlerbehandlung für Pool
pool.on('error', (err) => {
    console.error('PostgreSQL Pool Fehler:', err);
});

// Public endpoint für Standorte (keine Authentifizierung erforderlich)
router.get('/locations', async (req, res) => {
    try {
        const result = await pool.query('SELECT * FROM "GetLocations"');
        
        res.json({
            success: true,
            data: result.rows
        });
        
    } catch (error) {
        console.error('Fehler beim Abrufen der getlocations:', error);
        res.status(500).json({ 
            success: false, 
            message: 'Fehler beim Abrufen der Standorte' 
        });
    }
});

// Public route to get times for location with parameter
router.get('/times', async (req, res) => {
    const { location } = req.query;
    
    try {
        // First, let's check if the view exists and has data
        const viewCheck = await pool.query('SELECT COUNT(*) as count FROM "GetTimesWithPlayerAndLocation"');
        
        // Check what location names are available
        const availableLocations = await pool.query('SELECT DISTINCT location_name FROM "GetTimesWithPlayerAndLocation"');
        
        // Now search for the specific location
        const result = await pool.query('SELECT * FROM "GetTimesWithPlayerAndLocation" WHERE location_name = $1', [location]);
        
        res.json({
            success: true,
            data: result.rows,
            debug: {
                searchedFor: location,
                totalRecords: viewCheck.rows[0].count,
                availableLocations: availableLocations.rows.map(r => r.location_name),
                foundRecords: result.rows.length
            }
        });
        
    } catch (error) {
        console.error('❌ Fehler beim Abrufen der Zeiten:', error);
        res.status(500).json({
            success: false,
            message: 'Fehler beim Abrufen der Zeiten',
            error: error.message
        });
    }
});

// Public route to get all times with player and location details for leaderboard
router.get('/times-with-details', async (req, res) => {
    try {
        const { location, period } = req.query;
        
        // Build WHERE clause for location filter
        let locationFilter = '';
        if (location && location !== 'all') {
            locationFilter = `AND l.name ILIKE '%${location}%'`;
        }
        
        // Build WHERE clause for date filter using PostgreSQL timezone functions
        let dateFilter = '';
        if (period === 'today') {
            // Today in local timezone (UTC+2)
            dateFilter = `AND DATE(t.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') = CURRENT_DATE`;
        } else if (period === 'week') {
            // This week starting from Monday in local timezone
            dateFilter = `AND DATE(t.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') >= DATE_TRUNC('week', CURRENT_DATE)`;
        } else if (period === 'month') {
            // This month starting from 1st in local timezone
            dateFilter = `AND DATE(t.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') >= DATE_TRUNC('month', CURRENT_DATE)`;
        }
        
        // Get all times with player and location details, ordered by time (fastest first)
        // SECURITY: Only return data needed for leaderboard display
        const result = await pool.query(`
            SELECT 
                EXTRACT(EPOCH FROM t.recorded_time) as recorded_time_seconds,
                t.created_at,
                json_build_object(
                    'firstname', p.firstname,
                    'lastname', p.lastname
                ) as player,
                json_build_object(
                    'name', l.name
                ) as location
            FROM times t
            LEFT JOIN players p ON t.player_id = p.id
            LEFT JOIN locations l ON t.location_id = l.id
            WHERE 1=1 ${locationFilter} ${dateFilter}
            ORDER BY t.recorded_time ASC
            LIMIT 50
        `);
        
        // Convert seconds to minutes:seconds.milliseconds format
        const formattedResults = result.rows.map(row => {
            const totalSeconds = parseFloat(row.recorded_time_seconds);
            const minutes = Math.floor(totalSeconds / 60);
            const seconds = Math.floor(totalSeconds % 60);
            const milliseconds = Math.floor((totalSeconds % 1) * 1000);
            
            return {
                ...row,
                recorded_time: {
                    minutes: minutes,
                    seconds: seconds,
                    milliseconds: milliseconds
                }
            };
        });
        
        res.json(formattedResults);
        
    } catch (error) {
        console.error('❌ Fehler beim Abrufen der Zeiten mit Details:', error);
        res.status(500).json({
            success: false,
            message: 'Fehler beim Abrufen der Zeiten mit Details',
            error: error.message
        });
    }
});

// Public route to get all locations for filter dropdown
router.get('/locations', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT id, name, latitude, longitude
            FROM locations
            ORDER BY name ASC
        `);
        
        res.json({
            success: true,
            data: result.rows
        });
        
    } catch (error) {
        console.error('❌ Fehler beim Abrufen der Standorte:', error);
        res.status(500).json({
            success: false,
            message: 'Fehler beim Abrufen der Standorte',
            error: error.message
        });
    }
});




module.exports = router;