// scripts/setup-players.js
// Script to set up players table and fix the player name issue
const { Pool } = require('pg');
require('dotenv').config();

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
});

async function setupPlayers() {
    try {
        console.log('🚀 Setting up players table and views...');
        
        // Test connection
        await pool.query('SELECT NOW()');
        console.log('✅ Database connection successful');
        
        // Check if players table exists and has the expected structure
        const tableCheck = await pool.query(`
            SELECT column_name, data_type 
            FROM information_schema.columns 
            WHERE table_name = 'players'
        `);
        
        if (tableCheck.rows.length === 0) {
            console.log('❌ Players table not found. Please create it first with the structure:');
            console.log('   - id (UUID)');
            console.log('   - firstname (VARCHAR)');
            console.log('   - lastname (VARCHAR)');
            console.log('   - birthdate (DATE)');
            console.log('   - created_at (TIMESTAMP)');
            console.log('   - rfiduid (VARCHAR)');
            process.exit(1);
        }
        
        console.log('✅ Players table structure verified');
        console.log('📋 Available columns:', tableCheck.rows.map(r => r.column_name).join(', '));
        
        // Create the updated view using your actual table structure
        await pool.query(`
            CREATE OR REPLACE VIEW "GetTimesWithPlayerAndLocation" AS
            SELECT 
                gt.*,
                l.name as location_name,
                l.latitude,
                l.longitude,
                COALESCE(CONCAT(p.firstname, ' ', p.lastname), 'Unknown Player') as player_name
            FROM "gettimes" gt
            JOIN locations l ON gt.location_id = l.id
            LEFT JOIN players p ON gt.player_id = p.id
        `);
        console.log('✅ Updated view created');
        
        // Test the view
        const testResult = await pool.query('SELECT COUNT(*) as count FROM "GetTimesWithPlayerAndLocation"');
        console.log(`✅ View test successful: ${testResult.rows[0].count} records found`);
        
        // Show sample data
        const sampleData = await pool.query('SELECT player_name, location_name, recorded_time FROM "GetTimesWithPlayerAndLocation" LIMIT 3');
        console.log('📊 Sample data from view:');
        sampleData.rows.forEach((row, index) => {
            console.log(`  ${index + 1}. ${row.player_name} at ${row.location_name}: ${row.recorded_time}`);
        });
        
        console.log('\n🎉 Setup completed successfully!');
        console.log('📝 Your index.html should now display player names instead of IDs');
        console.log('🔄 Restart your server to use the new view');
        
    } catch (error) {
        console.error('❌ Error during setup:', error);
        process.exit(1);
    } finally {
        await pool.end();
    }
}

// Run if called directly
if (require.main === module) {
    setupPlayers();
}

module.exports = { setupPlayers };