ffxi-trusts/update_synergy_names.js

/**
 * Script to update trust_synergy_names column with character names extracted from trust_synergy
 */

const fs = require('fs');
const { Pool } = require('pg');
const path = require('path');

// Read database configuration
const dbConfFile = fs.readFileSync('db.conf', 'utf8');
const dbConfig = {};

// Parse the db.conf file
dbConfFile.split('\n').forEach(line => {
  if (line.trim() === '') return;
  
  const [key, value] = line.split('=');
  if (key && value) {
    // Remove quotes if present
    const cleanValue = value.replace(/^['"]|['"]$/g, '');
    dbConfig[key] = cleanValue;
  }
});

// Configure PostgreSQL connection
const pool = new Pool({
  user: dbConfig.PSQL_USER,
  host: dbConfig.PSQL_HOST,
  database: dbConfig.PSQL_DBNAME,
  password: dbConfig.PSQL_PASSWORD,
  port: dbConfig.PSQL_PORT,
});

// Function to execute SQL from a file
async function executeSqlFile(filePath) {
  const client = await pool.connect();
  try {
    const sql = fs.readFileSync(filePath, 'utf8');
    await client.query('BEGIN');
    await client.query(sql);
    await client.query('COMMIT');
    console.log(`SQL file ${filePath} executed successfully`);
  } catch (e) {
    await client.query('ROLLBACK');
    console.error(`Error executing SQL file ${filePath}:`, e);
    throw e;
  } finally {
    client.release();
  }
}

// Function to get all characters from the database
async function getAllCharacters() {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT id, name, alt_name, trust_synergy FROM trusts');
    return result.rows;
  } catch (e) {
    console.error('Error fetching characters:', e);
    throw e;
  } finally {
    client.release();
  }
}

// Function to extract character names from trust_synergy text
function extractCharacterNames(synergyText, currentCharName) {
  if (!synergyText) return [];
  
  // Get all character names and alt_names from the database to use as a reference
  const allCharNames = allCharacters.map(char => char.name);
  
  // Create a map of alt_names to their corresponding character names
  const altNameMap = new Map();
  allCharacters.forEach(char => {
    if (char.alt_name) {
      altNameMap.set(char.alt_name, char.name);
    }
  });
  
  // Create a set to store unique character names
  const synergyNames = new Set();
  
  // Common patterns in trust synergy text
  const patterns = [
    // Pattern: Name/Name/Name: description
    /([A-Za-z\s\-']+(?:\([A-Z]\))?(?:\/[A-Za-z\s\-']+(?:\([A-Z]\))?)+):/g,
    
    // Pattern: Name, Name, and Name
    /([A-Za-z\s\-']+(?:\([A-Z]\))?)(?:,\s+([A-Za-z\s\-']+(?:\([A-Z]\))?))(?:,?\s+and\s+([A-Za-z\s\-']+(?:\([A-Z]\))?))/g,
    
    // Pattern: Name and Name
    /([A-Za-z\s\-']+(?:\([A-Z]\))?)\s+and\s+([A-Za-z\s\-']+(?:\([A-Z]\))?)/g
  ];
  
  // Apply each pattern
  for (const pattern of patterns) {
    const matches = synergyText.matchAll(pattern);
    for (const match of matches) {
      // Process the first match which might contain multiple names separated by '/'
      if (match[1] && match[1].includes('/')) {
        const names = match[1].split('/').map(name => name.trim());
        names.forEach(name => {
          if (name !== currentCharName && allCharNames.includes(name)) {
            synergyNames.add(name);
          }
        });
      } 
      // Process individual names from the match groups
      else {
        for (let i = 1; i < match.length; i++) {
          const name = match[i]?.trim();
          if (name && name !== currentCharName && allCharNames.includes(name)) {
            synergyNames.add(name);
          }
        }
      }
    }
  }
  
  // Also check for direct mentions of character names
  allCharNames.forEach(name => {
    // Skip the current character's name
    if (name === currentCharName) return;
    
    // Only consider names with 4 or more characters to avoid false positives
    if (name.length < 4) return;
    
    // Check if the name appears as a whole word in the synergy text
    const nameRegex = new RegExp(`\\b${name}\\b`, 'g');
    if (nameRegex.test(synergyText)) {
      synergyNames.add(name);
    }
  });
  
  // Check for alt_names in the synergy text
  altNameMap.forEach((charName, altName) => {
    // Skip the current character's alt_name
    if (charName === currentCharName) return;
    
    // Only consider alt_names with 4 or more characters to avoid false positives
    if (altName.length < 4) return;
    
    // Check if the alt_name appears as a whole word in the synergy text
    const altNameRegex = new RegExp(`\\b${altName}\\b`, 'g');
    if (altNameRegex.test(synergyText)) {
      synergyNames.add(charName);
    }
  });
  
  return Array.from(synergyNames);
}

// Function to update trust_synergy_names for a character
async function updateSynergyNames(id, synergyNames) {
  const client = await pool.connect();
  try {
    // Convert array to PostgreSQL array format
    const pgArray = `{${synergyNames.map(name => `"${name}"`).join(',')}}`;
    
    await client.query(
      'UPDATE trusts SET trust_synergy_names = $1 WHERE id = $2',
      [pgArray, id]
    );
    
    return true;
  } catch (e) {
    console.error(`Error updating synergy names for character ID ${id}:`, e);
    return false;
  } finally {
    client.release();
  }
}

// Main function
async function main() {
  try {
    // Skip adding the column since it already exists
    console.log('Column trust_synergy_names already exists, skipping creation...');
    
    // 2. Get all characters
    console.log('Fetching all characters...');
    global.allCharacters = await getAllCharacters();
    
    // 3. Process each character
    console.log('Processing characters...');
    let successCount = 0;
    let failCount = 0;
    
    for (const char of allCharacters) {
      console.log(`Processing ${char.name}...`);
      
      // Extract character names from trust_synergy
      const synergyNames = extractCharacterNames(char.trust_synergy, char.name);
      
      // Update the database
      const success = await updateSynergyNames(char.id, synergyNames);
      
      if (success) {
        console.log(`Updated ${char.name} with synergy names: [${synergyNames.join(', ')}]`);
        successCount++;
      } else {
        console.error(`Failed to update ${char.name}`);
        failCount++;
      }
    }
    
    console.log(`\nProcessing complete!`);
    console.log(`Successfully updated: ${successCount} characters`);
    console.log(`Failed to update: ${failCount} characters`);
    
    // 4. Close the pool
    await pool.end();
    
  } catch (e) {
    console.error('Error in main process:', e);
    process.exit(1);
  }
}

// Run the main function
main();