88 lines
3.3 KiB
JavaScript
88 lines
3.3 KiB
JavaScript
// 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 };
|