135 lines
5.1 KiB
JavaScript
135 lines
5.1 KiB
JavaScript
#!/usr/bin/env node
|
|
|
|
/**
|
|
* Fix Player Subscriptions Script
|
|
*
|
|
* This script fixes the player_subscriptions table by:
|
|
* 1. Identifying orphaned subscriptions (UUIDs that don't match any player)
|
|
* 2. Optionally migrating them to real player IDs
|
|
* 3. Cleaning up invalid subscriptions
|
|
*/
|
|
|
|
const { Pool } = require('pg');
|
|
|
|
// Database connection
|
|
const pool = new Pool({
|
|
user: process.env.DB_USER || 'postgres',
|
|
host: process.env.DB_HOST || 'localhost',
|
|
database: process.env.DB_NAME || 'ninjacross',
|
|
password: process.env.DB_PASSWORD || 'postgres',
|
|
port: process.env.DB_PORT || 5432,
|
|
});
|
|
|
|
async function fixPlayerSubscriptions() {
|
|
console.log('🔧 Starting Player Subscriptions Fix...\n');
|
|
|
|
try {
|
|
// 1. Find orphaned subscriptions (UUIDs that don't match any player)
|
|
console.log('📊 Step 1: Finding orphaned subscriptions...');
|
|
const orphanedQuery = `
|
|
SELECT ps.player_id, ps.created_at, ps.endpoint
|
|
FROM player_subscriptions ps
|
|
LEFT JOIN players p ON ps.player_id = p.id
|
|
WHERE p.id IS NULL
|
|
`;
|
|
|
|
const orphanedResult = await pool.query(orphanedQuery);
|
|
console.log(`Found ${orphanedResult.rows.length} orphaned subscriptions`);
|
|
|
|
if (orphanedResult.rows.length > 0) {
|
|
console.log('\n📋 Orphaned subscriptions:');
|
|
orphanedResult.rows.forEach((sub, index) => {
|
|
console.log(` ${index + 1}. Player ID: ${sub.player_id}`);
|
|
console.log(` Created: ${sub.created_at}`);
|
|
console.log(` Endpoint: ${sub.endpoint.substring(0, 50)}...`);
|
|
});
|
|
}
|
|
|
|
// 2. Find players without subscriptions
|
|
console.log('\n📊 Step 2: Finding players without subscriptions...');
|
|
const playersWithoutSubsQuery = `
|
|
SELECT p.id, p.firstname, p.lastname, p.supabase_user_id
|
|
FROM players p
|
|
LEFT JOIN player_subscriptions ps ON p.id = ps.player_id
|
|
WHERE ps.player_id IS NULL
|
|
`;
|
|
|
|
const playersWithoutSubsResult = await pool.query(playersWithoutSubsQuery);
|
|
console.log(`Found ${playersWithoutSubsResult.rows.length} players without subscriptions`);
|
|
|
|
if (playersWithoutSubsResult.rows.length > 0) {
|
|
console.log('\n📋 Players without subscriptions:');
|
|
playersWithoutSubsResult.rows.forEach((player, index) => {
|
|
console.log(` ${index + 1}. ${player.firstname} ${player.lastname} (${player.id})`);
|
|
console.log(` Supabase User ID: ${player.supabase_user_id || 'None'}`);
|
|
});
|
|
}
|
|
|
|
// 3. Show current subscription statistics
|
|
console.log('\n📊 Step 3: Current subscription statistics...');
|
|
const statsQuery = `
|
|
SELECT
|
|
COUNT(*) as total_subscriptions,
|
|
COUNT(DISTINCT ps.player_id) as unique_player_ids,
|
|
COUNT(p.id) as linked_to_players,
|
|
COUNT(*) - COUNT(p.id) as orphaned_count
|
|
FROM player_subscriptions ps
|
|
LEFT JOIN players p ON ps.player_id = p.id
|
|
`;
|
|
|
|
const statsResult = await pool.query(statsQuery);
|
|
const stats = statsResult.rows[0];
|
|
|
|
console.log(`Total subscriptions: ${stats.total_subscriptions}`);
|
|
console.log(`Unique player IDs: ${stats.unique_player_ids}`);
|
|
console.log(`Linked to real players: ${stats.linked_to_players}`);
|
|
console.log(`Orphaned subscriptions: ${stats.orphaned_count}`);
|
|
|
|
// 4. Ask user what to do
|
|
console.log('\n🔧 Step 4: What would you like to do?');
|
|
console.log('1. Clean up orphaned subscriptions (DELETE)');
|
|
console.log('2. Keep orphaned subscriptions (no action)');
|
|
console.log('3. Show detailed analysis only');
|
|
|
|
// For now, just show the analysis
|
|
console.log('\n✅ Analysis complete. No changes made.');
|
|
console.log('\n💡 Recommendations:');
|
|
console.log('- Orphaned subscriptions should be cleaned up');
|
|
console.log('- New subscriptions will now use real player IDs');
|
|
console.log('- Existing valid subscriptions will continue to work');
|
|
|
|
} catch (error) {
|
|
console.error('❌ Error fixing player subscriptions:', error);
|
|
} finally {
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
// Clean up orphaned subscriptions
|
|
async function cleanupOrphanedSubscriptions() {
|
|
console.log('🧹 Cleaning up orphaned subscriptions...');
|
|
|
|
try {
|
|
const deleteQuery = `
|
|
DELETE FROM player_subscriptions
|
|
WHERE player_id NOT IN (SELECT id FROM players)
|
|
`;
|
|
|
|
const result = await pool.query(deleteQuery);
|
|
console.log(`✅ Deleted ${result.rowCount} orphaned subscriptions`);
|
|
|
|
} catch (error) {
|
|
console.error('❌ Error cleaning up subscriptions:', error);
|
|
}
|
|
}
|
|
|
|
// Run the script
|
|
if (require.main === module) {
|
|
fixPlayerSubscriptions();
|
|
}
|
|
|
|
module.exports = {
|
|
fixPlayerSubscriptions,
|
|
cleanupOrphanedSubscriptions
|
|
};
|