#!/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
};