Source: migrateAchievements.js

import {query, transaction, UUID2hex, HEX2uuid} from '@commtool/sql-query'
import { errorLoggerUpdate } from '../utils/requestLogger.js'

/**
 * Migrates achievements from 05.01.2023 to 01.01.1900
 * Uses delete and re-insert to handle system versioned table
 * 
 * @async
 * @function migrateAchievementDates
 * @returns {Promise<void>}
 */
async function migrateAchievementDates() {
  try {
    console.log('Starting achievement date migration...');
    
    // Find all achievements with date 05.01.2023
    const targetDate = '2023-01-05';
    const targetAchievements = await query(
      `SELECT ob.UID, ob.Type, ob.UIDuser, ob.UIDBelongsTo, ob.Title, ob.SortName, ob.dindex, ob.Data
       FROM ObjectBase ob
       WHERE ob.Type='achievement' 
       AND DATE(ob.validFrom) = ?`,
      [targetDate]
    );
    
    console.log(`Found ${targetAchievements.length} achievements to migrate`);
    
    if (targetAchievements.length === 0) {
      console.log('No achievements to update. Exiting.');
      return;
    }
    
    // Process each achievement
    for (const achievement of targetAchievements) {
      await transaction(async (connection) => {
        // Get links for this achievement
        const links = await connection.query(
          `SELECT Type, UIDTarget, UIDuser FROM Links WHERE UID = ?`,
          [achievement.UID]
        );
        
        // Delete existing links
        await connection.query(
          `DELETE FROM Links WHERE UID = ?`,
          [achievement.UID]
        );
        
        // Delete existing achievement
        await connection.query(
          `DELETE FROM ObjectBase WHERE UID = ?`,
          [achievement.UID]
        );
        
        // Insert the achievement with new date
        await connection.query(
          `INSERT INTO ObjectBase (UID, Type, UIDuser, UIDBelongsTo, Title, SortName, dindex, Data)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            achievement.UID,
            achievement.Type,
            achievement.UIDuser,
            achievement.UIDBelongsTo,
            achievement.Title,
            achievement.SortName,
            achievement.dindex,
            achievement.Data
          ]
        );
        
        // Re-create links
        for (const link of links) {
          await connection.query(
            `INSERT INTO Links (UID, Type, UIDTarget, UIDuser)
             VALUES (?, ?, ?, ?)`,
            [achievement.UID, link.Type, link.UIDTarget, link.UIDuser]
          );
        }
        
        console.log(`Migrated achievement: ${achievement.UID}`);
      },{backDate: '1900-01-01 00:00:00'});
    }
    
    console.log('Achievement date migration completed successfully');
  } catch (error) {
    console.error('Error migrating achievement dates:', error);
    errorLoggerUpdate(error);
  }
}

// Execute the function when the script is run directly
migrateAchievementDates().catch(error => {
  console.error('Fatal error in date migration script:', error);
  process.exit(1);
});