euchre_camp/scripts/cleanup-prod-db.js

#!/usr/bin/env node

/**
 * Clean up production database - remove test records only
 * 
 * WARNING: This script modifies the production database.
 * It only removes records that match test patterns.
 * It preserves actual player data, matches, and tournaments.
 */

const { execSync } = require('child_process');

// Database connection string - must be explicitly set via environment variable
const DB_URL = process.env.PROD_DATABASE_URL || process.env.DATABASE_URL;

// Check if database URL is set
if (!DB_URL) {
  console.error('❌ No database URL set');
  console.error('Please set PROD_DATABASE_URL or DATABASE_URL environment variable');
  console.error('For production: PROD_DATABASE_URL="postgresql://user:pass@host:port/dbname"');
  process.exit(1);
}

// Validate that we're not accidentally using dev database
if (DB_URL.includes('_dev') || DB_URL.includes('_dev_')) {
  console.error('❌ ERROR: This script should not be used with the development database!');
  console.error('   Current DATABASE_URL:', DB_URL);
  console.error('   Please set PROD_DATABASE_URL for production database operations.');
  process.exit(1);
}

// Single source of truth for test object patterns
const TEST_PATTERNS = {
  players: [
    '%Test%',
    '%Setup%',
    '%Home Test%',
    '%Home Match Player%',
    '%Admin User%',
    '%NinePart%',
    '%Nine Part%',
    '%Test Player%',
    '%TestUser%',
    '%Cucumber%',
    '%Config Admin%',
  ],
  events: [
    '%Test%',
    '%Setup%',
    '%Recent%',
    '%Test Tournament%',
    '%Cucumber%',
  ],
  users: [
    '%test%',
    '%setup%',
    '%cucumber%',
    '%TestUser%',
  ]
};

// Helper to build SQL LIKE clause from patterns
function buildLikeClause(patterns) {
  return patterns.map(p => `name LIKE '${p}'`).join(' OR ');
}

// Helper to build email LIKE clause
function buildEmailLikeClause(patterns) {
  return patterns.map(p => `email LIKE '${p}'`).join(' OR ');
}

// Helper to run SQL and log results
function runSQL(sql, description) {
  console.log(`\n🔍 ${description}...`);
  try {
    const result = execSync(`psql "${DB_URL}" -c "${sql}"`, { encoding: 'utf8' });
    console.log(result);
    return true;
  } catch (error) {
    console.error(`❌ Error: ${error.message}`);
    return false;
  }
}

// Helper to run SQL via file (avoids quote escaping issues)
function runSQLViaFile(sql, description) {
  console.log(`\n🔍 ${description}...`);
  const fs = require('fs');
  const os = require('os');
  const path = require('path');
  
  try {
    // Create temp file with SQL
    const tmpFile = path.join(os.tmpdir(), `cleanup_${Date.now()}.sql`);
    fs.writeFileSync(tmpFile, sql);
    
    const result = execSync(`psql "${DB_URL}" -f "${tmpFile}"`, { encoding: 'utf8' });
    console.log(result);
    
    // Clean up temp file
    fs.unlinkSync(tmpFile);
    return true;
  } catch (error) {
    console.error(`❌ Error: ${error.message}`);
    return false;
  }
}

// Helper to count records matching any of the patterns
function countRecordsByPatterns(table, patterns) {
  const whereClause = table === 'users' 
    ? buildEmailLikeClause(patterns)
    : buildLikeClause(patterns);
  const sql = `SELECT COUNT(*) FROM ${table} WHERE ${whereClause};`;
  try {
    const result = execSync(`psql "${DB_URL}" -c "${sql}" -t`, { encoding: 'utf8' }).trim();
    return parseInt(result);
  } catch (error) {
    return 0;
  }
}

async function main() {
  console.log('🧹 Cleaning up test records from production database');
  console.log('=============================================\n');

  // Confirm with user
  const readline = require('readline');
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
  });

  rl.question('⚠️  This will DELETE test records from PRODUCTION database. Continue? (y/N) ', async (answer) => {
    if (answer.toLowerCase() !== 'y' && answer.toLowerCase() !== 'yes') {
      console.log('❌ Cleanup cancelled');
      rl.close();
      process.exit(0);
    }

    rl.close();

    console.log('\n📋 Checking test records...\n');

    // Count test records using centralized patterns
    const testPlayerCount = countRecordsByPatterns('players', TEST_PATTERNS.players);
    console.log(`Test players found: ${testPlayerCount}`);

    const testEventCount = countRecordsByPatterns('events', TEST_PATTERNS.events);
    console.log(`Test tournaments found: ${testEventCount}`);

    const testUserCount = countRecordsByPatterns('users', TEST_PATTERNS.users);
    console.log(`Test users found: ${testUserCount}`);

    console.log('\n🔄 Starting cleanup...\n');

    // Step 1: Delete test tournaments (with matches due to cascade delete)
    console.log('1. Deleting test tournaments (matches will be deleted via cascade)...');
    const eventWhere = buildLikeClause(TEST_PATTERNS.events);
    runSQLViaFile(
      `DELETE FROM events WHERE (${eventWhere});`,
      'Deleted test tournaments'
    );

    // Step 2: Delete test players (all of them, since we deleted their matches)
    console.log('\n2. Deleting test players...');
    const playerWhere = buildLikeClause(TEST_PATTERNS.players);
    runSQLViaFile(
      `DELETE FROM players WHERE (${playerWhere});`,
      'Deleted test players'
    );

    // Step 3: Delete test users (they shouldn't have player associations)
    console.log('\n3. Deleting test users...');
    const userWhere = buildEmailLikeClause(TEST_PATTERNS.users);
    runSQLViaFile(
      `DELETE FROM users WHERE (${userWhere}) AND "playerId" IS NULL;`,
      'Deleted test users'
    );

    // Summary
    console.log('\n✅ Cleanup complete!');
    console.log('\n📊 Remaining test records:');
    
    const remainingPlayerWhere = buildLikeClause(TEST_PATTERNS.players);
    runSQLViaFile(
      `SELECT COUNT(*) FROM players WHERE ${remainingPlayerWhere};`,
      'Remaining test players'
    );
    
    const remainingEventWhere = buildLikeClause(TEST_PATTERNS.events);
    runSQLViaFile(
      `SELECT COUNT(*) FROM events WHERE ${remainingEventWhere};`,
      'Remaining test tournaments'
    );
    
    const remainingUserWhere = buildEmailLikeClause(TEST_PATTERNS.users);
    runSQLViaFile(
      `SELECT COUNT(*) FROM users WHERE ${remainingUserWhere};`,
      'Remaining test users'
    );

    console.log('\n💡 Note: All test records deleted. Matches are automatically deleted');
    console.log('   via cascade delete when their parent tournament is deleted.');
    console.log('\n📝 To add new test patterns, edit TEST_PATTERNS in this script.');
  });
}

// Run main function
main();