'use strict';

/**
 * Orphaned Application Details Cleanup Worker
 * 
 * This script identifies and removes application detail records where the referenced fileId 
 * no longer exists in the AppFile collection. These orphaned records can accumulate over time
 * when application files are deleted but their corresponding detail records remain.
 * 
 * Key Features:
 * - Identifies orphaned application details by checking fileId references
 * - Uses in-memory caching of AppFile IDs for fast lookup performance
 * - Time-based processing (yearly/monthly periods) for handling billion+ documents
 * - Efficient ObjectId timestamp filtering for date ranges
 * - Batch processing with configurable batch sizes for large datasets
 * - Bulk delete operations for efficient cleanup
 * - Comprehensive progress tracking per time period and overall
 * - Supports dry-run mode for safe testing
 * - Implements robust error handling with retry logic
 * - Follows the same database connection pattern as other worker scripts
 * 
 * Usage:
 *   # Check and remove orphaned application details for all years (2020-2025)
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js
 * 
 *   # Process only a specific year using command line argument
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --specific-year=2024
 * 
 *   # Process a range of years using command line arguments
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --start-year=2022 --end-year=2024
 * 
 *   # Process a specific date range using command line arguments
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --start-date=2024-06-01 --end-date=2024-06-30
 * 
 *   # Process from a specific date to now
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --start-date=2024-01-15
 *
 *   # Process with ISO datetime format 
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --start-date=2024-06-01T10:30:00Z --end-date=2024-06-15T15:45:00Z
 * 
 *   # Dry run mode with command line arguments
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --dry-run --start-year=2025
 * 
 *   # Check only mode with command line arguments
 *   DEBUG=agm:clean-orphaned-details node server/workers/cleanOrphanedAppDetails.js --check-only --specific-year=2024
 * 
 *   # Using environment variables (legacy method)
 *   DEBUG=agm:clean-orphaned-details SPECIFIC_YEAR=2024 node server/workers/cleanOrphanedAppDetails.js
 *   DEBUG=agm:clean-orphaned-details START_YEAR=2022 END_YEAR=2024 node server/workers/cleanOrphanedAppDetails.js
 *   DEBUG=agm:clean-orphaned-details START_DATE=2024-06-01 END_DATE=2024-06-30 node server/workers/cleanOrphanedAppDetails.js
 *   DEBUG=agm:clean-orphaned-details DRY_RUN=true START_DATE=2024-01-01 node server/workers/cleanOrphanedAppDetails.js
 * 
 * Command Line Arguments:
 *   --dry-run                         # Only reports what would be deleted without making changes
 *   --check-only                      # Only check for orphaned records without deleting
 *   --start-year=YYYY                 # Starting year for processing (default: 2020)
 *   --end-year=YYYY                   # Ending year for processing (default: current year)
 *   --specific-year=YYYY              # Process only a specific year (overrides start/end year)
 *   --start-date=YYYY-MM-DD           # Starting date for processing (YYYY-MM-DD or ISO format)
 *   --end-date=YYYY-MM-DD             # Ending date for processing (YYYY-MM-DD or ISO format)
 *   --batch-size=N                    # Number of documents per batch (default: 1000)
 * 
 * Environment Variables:
 *   - DRY_RUN=true                    # Only reports what would be deleted without making changes
 *   - BATCH_SIZE=1000                 # Number of documents per batch (default: 1000)
 *   - MAX_RETRIES=3                   # Maximum number of retries for errors (default: 3)
 *   - RETRY_DELAY=1000                # Base delay in ms between retries (default: 1000)
 *   - SHOW_PROGRESS=true              # Whether to show progress indicator (default: true)
 *   - CHECK_ONLY=false                # Only check for orphaned records without deleting (default: false)
 *   - TIME_PERIOD=yearly              # Time period for batching: yearly, monthly, or custom (default: yearly)
 *   - START_YEAR=2020                 # Starting year for processing (default: 2020)
 *   - END_YEAR=2025                   # Ending year for processing (default: current year)
 *   - SPECIFIC_YEAR=2024              # Process only a specific year (overrides START_YEAR/END_YEAR)
 *   - START_DATE=2024-01-01           # Starting date for processing (YYYY-MM-DD or ISO format)
 *   - END_DATE=2024-12-31             # Ending date for processing (YYYY-MM-DD or ISO format)
 */

const debug = require('debug')('agm:clean-orphaned-details');
const env = require('../helpers/env.js');
const { DBConnection } = require('../helpers/db/connect.js');
const mongoose = require('mongoose');
const utils = require('../helpers/utils.js');
const AppDetail = require('../model/application_detail.js');
const AppFile = require('../model/application_file.js');

/**
 * Parse command line arguments
 * @returns {Object} Parsed configuration object
 */
function parseArguments() {
  const args = process.argv.slice(2);
  const config = {
    dryRun: process.env.DRY_RUN === 'true',
    batchSize: parseInt(process.env.BATCH_SIZE || '1000', 10),
    maxRetries: parseInt(process.env.MAX_RETRIES || '3', 10),
    retryDelay: parseInt(process.env.RETRY_DELAY || '1000', 10),
    showProgress: process.env.SHOW_PROGRESS !== 'false',
    checkOnly: process.env.CHECK_ONLY === 'true',
    timePeriod: process.env.TIME_PERIOD || 'yearly',
    startYear: parseInt(process.env.START_YEAR || '2020', 10),
    endYear: parseInt(process.env.END_YEAR || new Date().getFullYear().toString(), 10),
    specificYear: process.env.SPECIFIC_YEAR ? parseInt(process.env.SPECIFIC_YEAR, 10) : null,
    startDate: process.env.START_DATE || null,
    endDate: process.env.END_DATE || null
  };

  // Parse command line arguments and override environment variables
  for (let i = 0; i < args.length; i++) {
    const arg = args[i];
    
    if (arg === '--dry-run') {
      config.dryRun = true;
    } else if (arg === '--check-only') {
      config.checkOnly = true;
    } else if (arg.startsWith('--start-year=')) {
      config.startYear = parseInt(arg.split('=')[1], 10);
      config.specificYear = null; // Clear specific year if start year is provided
    } else if (arg.startsWith('--end-year=')) {
      config.endYear = parseInt(arg.split('=')[1], 10);
      config.specificYear = null; // Clear specific year if end year is provided
    } else if (arg.startsWith('--specific-year=')) {
      config.specificYear = parseInt(arg.split('=')[1], 10);
    } else if (arg.startsWith('--start-date=')) {
      config.startDate = arg.split('=')[1];
      config.specificYear = null; // Clear specific year if start date is provided
    } else if (arg.startsWith('--end-date=')) {
      config.endDate = arg.split('=')[1];
      config.specificYear = null; // Clear specific year if end date is provided
    } else if (arg.startsWith('--batch-size=')) {
      config.batchSize = parseInt(arg.split('=')[1], 10);
    }
  }

  return config;
}

// Parse configuration from both environment variables and command line arguments
const CONFIG = parseArguments();

// Configuration constants for backward compatibility
const DRY_RUN = CONFIG.dryRun;
const BATCH_SIZE = CONFIG.batchSize;
const MAX_RETRIES = CONFIG.maxRetries;
const RETRY_DELAY_MS = CONFIG.retryDelay;
const SHOW_PROGRESS = CONFIG.showProgress;
const CHECK_ONLY = CONFIG.checkOnly;
const TIME_PERIOD = CONFIG.timePeriod;
const START_YEAR = CONFIG.startYear;
const END_YEAR = CONFIG.endYear;
const SPECIFIC_YEAR = CONFIG.specificYear;
const START_DATE = CONFIG.startDate;
const END_DATE = CONFIG.endDate;

/**
 * Create ObjectId from date for filtering
 * @param {Date|string} date - Date to convert to ObjectId
 * @returns {mongoose.Types.ObjectId} ObjectId with timestamp
 */
function createObjectIdFromDate(date) {
  const dateObj = new Date(date);
  const timestamp = Math.floor(dateObj.getTime() / 1000);
  const objectIdHex = timestamp.toString(16) + '0000000000000000';
  return new mongoose.Types.ObjectId(objectIdHex);
}

/**
 * Parse date string into Date object with validation
 * @param {string} dateString - Date string in YYYY-MM-DD or ISO format
 * @param {string} paramName - Parameter name for error messages
 * @returns {Date} Parsed date object
 */
function parseDate(dateString, paramName) {
  if (!dateString) return null;
  
  let date;
  
  // Try parsing as ISO string first, then as YYYY-MM-DD
  if (dateString.includes('T') || dateString.includes('Z')) {
    date = new Date(dateString);
  } else {
    // Assume YYYY-MM-DD format and create at start of day UTC
    date = new Date(`${dateString}T00:00:00.000Z`);
  }
  
  if (isNaN(date.getTime())) {
    throw new Error(`Invalid date format for ${paramName}: ${dateString}. Use YYYY-MM-DD or ISO format.`);
  }
  
  return date;
}

/**
 * Generate time periods to process based on configuration
 * @returns {Array} Array of time period objects with start and end dates
 */
function generateTimePeriods() {
  const periods = [];

  // If specific dates are provided, use them (highest priority)
  if (START_DATE || END_DATE) {
    const startDate = START_DATE ? parseDate(START_DATE, 'START_DATE') : new Date('2020-01-01T00:00:00.000Z');
    const endDate = END_DATE ? parseDate(END_DATE, 'END_DATE') : new Date(); // Current date if not specified
    
    // Ensure end date is after start date
    if (endDate <= startDate) {
      throw new Error(`End date (${endDate.toISOString()}) must be after start date (${startDate.toISOString()})`);
    }
    
    // For date ranges, create periods based on the time span
    const daysDiff = Math.ceil((endDate - startDate) / (1000 * 60 * 60 * 24));
    
    if (daysDiff <= 31) {
      // Single period for ranges up to 1 month
      periods.push({
        name: `Custom Range: ${startDate.toISOString().split('T')[0]} to ${endDate.toISOString().split('T')[0]}`,
        startDate: startDate,
        endDate: endDate
      });
    } else if (daysDiff <= 365) {
      // Monthly periods for ranges up to 1 year
      let currentDate = new Date(startDate);
      let periodCount = 1;
      
      while (currentDate < endDate) {
        const periodEnd = new Date(Math.min(
          new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 1).getTime(),
          endDate.getTime()
        ));
        
        periods.push({
          name: `Period ${periodCount}: ${currentDate.toISOString().split('T')[0]} to ${periodEnd.toISOString().split('T')[0]}`,
          startDate: new Date(currentDate),
          endDate: periodEnd
        });
        
        currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 1);
        periodCount++;
      }
    } else {
      // Yearly periods for ranges longer than 1 year
      let currentDate = new Date(startDate);
      let periodCount = 1;
      
      while (currentDate < endDate) {
        const periodEnd = new Date(Math.min(
          new Date(currentDate.getFullYear() + 1, 0, 1).getTime(),
          endDate.getTime()
        ));
        
        periods.push({
          name: `Period ${periodCount}: ${currentDate.toISOString().split('T')[0]} to ${periodEnd.toISOString().split('T')[0]}`,
          startDate: new Date(currentDate),
          endDate: periodEnd
        });
        
        currentDate = new Date(currentDate.getFullYear() + 1, 0, 1);
        periodCount++;
      }
    }
  } else if (SPECIFIC_YEAR) {
    // Process only the specific year
    periods.push({
      name: `Year ${SPECIFIC_YEAR}`,
      startDate: new Date(`${SPECIFIC_YEAR}-01-01T00:00:00.000Z`),
      endDate: new Date(`${SPECIFIC_YEAR + 1}-01-01T00:00:00.000Z`)
    });
  } else {
    // Process from START_YEAR to END_YEAR
    for (let year = START_YEAR; year <= END_YEAR; year++) {
      periods.push({
        name: `Year ${year}`,
        startDate: new Date(`${year}-01-01T00:00:00.000Z`),
        endDate: new Date(`${year + 1}-01-01T00:00:00.000Z`)
      });
    }
  }

  return periods;
}

/**
 * Sleep for specified milliseconds
 * @param {number} ms - Milliseconds to sleep
 * @returns {Promise<void>}
 */
function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

/**
 * Retry wrapper for operations with exponential backoff
 * @param {Function} operation - Operation to retry
 * @param {string} operationName - Name of the operation for logging
 * @param {number} maxRetries - Maximum number of retries
 * @returns {Promise<any>} Result of the operation
 */
async function withRetry(operation, operationName, maxRetries = MAX_RETRIES) {
  let lastError;

  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      lastError = error;

      if (attempt === maxRetries) {
        debug(`${operationName} failed after ${maxRetries + 1} attempts: ${error.message}`);
        throw error;
      }

      const delay = RETRY_DELAY_MS * Math.pow(2, attempt);
      debug(`${operationName} failed (attempt ${attempt + 1}/${maxRetries + 1}): ${error.message}. Retrying in ${delay}ms...`);
      await sleep(delay);
    }
  }

  throw lastError;
}

/**
 * Load all existing AppFile IDs into memory for fast lookup
 * @returns {Promise<Set>} Set of all existing AppFile _ids
 */
async function loadAppFileIds() {
  debug('Loading all AppFile IDs into memory...');

  return await withRetry(async () => {
    const appFiles = await AppFile.find({ markedDelete: { $ne: true } }, { _id: 1 }).lean();
    const fileIds = new Set(appFiles.map(file => file._id.toString()));
    debug(`Loaded ${fileIds.size} AppFile IDs into memory cache`);
    return fileIds;
  }, 'Load AppFile IDs');
}

/**
 * Find orphaned application details by checking against in-memory cache for a specific time period
 * This approach loads all AppFile IDs into memory first, then checks each application detail within the time range
 * @param {Object} timePeriod - Time period object with startDate and endDate
 * @param {Set} existingFileIds - Set of existing AppFile IDs for lookup
 * @returns {Promise<Array>} Array of orphaned application detail documents
 */
async function findOrphanedAppDetailsForPeriod(timePeriod, existingFileIds) {
  debug(`Finding orphaned application details for ${timePeriod.name}...`);

  // Create ObjectId filters for the time period
  const startObjectId = createObjectIdFromDate(timePeriod.startDate);
  const endObjectId = createObjectIdFromDate(timePeriod.endDate);

  debug(`Period: ${timePeriod.startDate.toISOString()} to ${timePeriod.endDate.toISOString()}`);
  debug(`ObjectId range: ${startObjectId} to ${endObjectId}`);

  // Get total count of application details for this period
  const periodFilter = {
    _id: {
      $gte: startObjectId,
      $lt: endObjectId
    }
  };

  const totalAppDetails = await withRetry(async () => {
    return await AppDetail.countDocuments(periodFilter);
  }, `Count application details for ${timePeriod.name}`);

  debug(`Checking ${totalAppDetails} application details in ${timePeriod.name} against ${existingFileIds.size} existing file IDs`);

  if (totalAppDetails === 0) {
    debug(`No application details found for ${timePeriod.name}`);
    return [];
  }

  const orphanedRecords = [];
  let processed = 0;
  let skip = 0;
  const checkBatchSize = Math.min(BATCH_SIZE, 5000); // Use smaller batches for memory checking

  while (true) {
    // Fetch batch of application details for this time period
    const appDetailsBatch = await withRetry(async () => {
      return await AppDetail.find(periodFilter)
        .select('_id fileId')
        .sort({ _id: 1 })
        .skip(skip)
        .limit(checkBatchSize)
        .lean();
    }, `Fetch application details batch for ${timePeriod.name} (skip: ${skip})`);

    if (appDetailsBatch.length === 0) {
      break; // No more records
    }

    // Check each record in this batch against the in-memory cache
    for (const appDetail of appDetailsBatch) {
      if (!existingFileIds.has(appDetail.fileId.toString())) {
        orphanedRecords.push({
          _id: appDetail._id,
          fileId: appDetail.fileId
        });
      }
      processed++;
    }

    // Show progress for the checking phase
    if (SHOW_PROGRESS && processed % (checkBatchSize * 2) === 0) {
      const percentage = ((processed / totalAppDetails) * 100).toFixed(1);
      debug(`${timePeriod.name} progress: ${processed}/${totalAppDetails} (${percentage}%) - Found ${orphanedRecords.length} orphaned so far`);
    }

    skip += checkBatchSize;

    // Break if we've processed fewer records than requested (end of collection)
    if (appDetailsBatch.length < checkBatchSize) {
      break;
    }

    // Small delay to prevent overwhelming the database
    await sleep(10);
  }

  debug(`Completed checking ${processed} application details for ${timePeriod.name} - Found ${orphanedRecords.length} orphaned records`);
  return orphanedRecords;
}

/**
 * Get sample of orphaned records for reporting
 * @param {Array} orphanedIds - Array of orphaned document _ids
 * @param {number} sampleSize - Number of sample records to retrieve
 * @returns {Promise<Array>} Sample of orphaned records
 */
async function getSampleOrphanedRecords(orphanedIds, sampleSize = 5) {
  if (orphanedIds.length === 0) return [];

  const sampleIds = orphanedIds.slice(0, sampleSize).map(doc => doc._id);

  return await withRetry(async () => {
    return await AppDetail.find({
      _id: { $in: sampleIds }
    }).select('_id fileId lat lon').lean();
  }, 'Get sample orphaned records');
}

/**
 * Delete a batch of orphaned application details
 * @param {Array} batch - Batch of document _ids to delete
 * @param {Object} stats - Statistics object to update
 * @returns {Promise<void>}
 */
async function deleteBatch(batch, stats) {
  if (!batch || batch.length === 0) {
    return;
  }

  const batchIds = batch.map(doc => doc._id);
  debug(`Processing batch of ${batchIds.length} orphaned records...`);

  if (DRY_RUN || CHECK_ONLY) {
    debug(`${DRY_RUN ? 'DRY RUN' : 'CHECK ONLY'}: Would delete ${batchIds.length} orphaned application details`);
    stats.processed += batchIds.length;
    stats.dryRunCount += batchIds.length;
    return;
  }

  // Execute bulk delete operation with retry
  const result = await withRetry(async () => {
    return await AppDetail.deleteMany({
      _id: { $in: batchIds }
    });
  }, `Delete batch of ${batchIds.length} orphaned records`);

  // Update statistics
  stats.processed += batchIds.length;
  stats.deleted += result.deletedCount || 0;

  debug(`Batch completed: ${result.deletedCount} records deleted`);
}

/**
 * Display progress information
 * @param {number} processed - Number of documents processed
 * @param {number} total - Total number of documents
 * @param {Date} startTime - Start time of the operation
 */
function showProgress(processed, total, startTime) {
  if (!SHOW_PROGRESS || total === 0) return;

  const elapsed = Date.now() - startTime.getTime();
  const rate = processed / (elapsed / 1000);
  const remaining = total - processed;
  const eta = remaining > 0 ? remaining / rate : 0;
  const percentage = ((processed / total) * 100).toFixed(1);

  const formatTime = (seconds) => {
    const hours = Math.floor(seconds / 3600);
    const minutes = Math.floor((seconds % 3600) / 60);
    const secs = Math.floor(seconds % 60);

    if (hours > 0) {
      return `${hours}h ${minutes}m ${secs}s`;
    } else if (minutes > 0) {
      return `${minutes}m ${secs}s`;
    } else {
      return `${secs}s`;
    }
  };

  debug(`Progress: ${processed}/${total} (${percentage}%) | Rate: ${rate.toFixed(1)} records/sec | ETA: ${formatTime(eta)}`);
}

/**
 * Main function to clean orphaned application details
 * @returns {Promise<Object>} Statistics about the cleanup operation
 */
async function cleanOrphanedAppDetails() {
  const startTime = new Date();
  debug(`Starting orphaned application details cleanup...`);
  debug(`Configuration:`);
  debug(`  - DRY_RUN: ${DRY_RUN}`);
  debug(`  - CHECK_ONLY: ${CHECK_ONLY}`);
  debug(`  - BATCH_SIZE: ${BATCH_SIZE}`);
  debug(`  - TIME_PERIOD: ${TIME_PERIOD}`);
  debug(`  - SPECIFIC_YEAR: ${SPECIFIC_YEAR || 'none'}`);
  debug(`  - START_YEAR: ${START_YEAR}`);
  debug(`  - END_YEAR: ${END_YEAR}`);
  debug(`  - START_DATE: ${START_DATE || 'none'}`);
  debug(`  - END_DATE: ${END_DATE || 'none'}`);
  debug(`  - Date range mode: ${START_DATE || END_DATE ? 'ENABLED' : 'DISABLED'}`);
  debug(`  - Years to process: ${SPECIFIC_YEAR || (START_DATE || END_DATE ? 'custom date range' : `${START_YEAR}-${END_YEAR}`)}`);
  debug(`  - Command line args: ${process.argv.slice(2).join(' ') || 'none'}`);

  // Initialize statistics
  const stats = {
    processed: 0,
    deleted: 0,
    errors: 0,
    dryRunCount: 0,
    totalOrphaned: 0,
    batches: 0,
    periodsProcessed: 0,
    periodsTotal: 0
  };

  try {
    // Load all AppFile IDs into memory once at the beginning
    debug('Loading AppFile IDs into memory...');
    const existingFileIds = await loadAppFileIds();

    // Generate time periods to process
    const timePeriods = generateTimePeriods();
    stats.periodsTotal = timePeriods.length;

    debug(`Processing ${timePeriods.length} time periods:`);
    timePeriods.forEach(period => {
      debug(`  - ${period.name}: ${period.startDate.toISOString().split('T')[0]} to ${period.endDate.toISOString().split('T')[0]}`);
    });

    let allOrphanedRecords = [];

    // Process each time period
    for (const timePeriod of timePeriods) {
      try {
        debug(`\n${'='.repeat(60)}`);
        debug(`Processing ${timePeriod.name}...`);
        debug(`${'='.repeat(60)}`);

        // Find orphaned records for this time period
        const periodOrphanedRecords = await findOrphanedAppDetailsForPeriod(timePeriod, existingFileIds);

        if (periodOrphanedRecords.length > 0) {
          debug(`Found ${periodOrphanedRecords.length} orphaned records in ${timePeriod.name}`);
          allOrphanedRecords = allOrphanedRecords.concat(periodOrphanedRecords);
        } else {
          debug(`No orphaned records found in ${timePeriod.name}`);
        }

        stats.periodsProcessed++;

      } catch (error) {
        debug(`Error processing ${timePeriod.name}: ${error.message}`);
        stats.errors++;

        // Continue with next period unless too many errors
        if (stats.errors > 3) {
          debug('Too many period errors, stopping operation');
          break;
        }
      }
    }

    stats.totalOrphaned = allOrphanedRecords.length;

    if (allOrphanedRecords.length === 0) {
      debug('\nNo orphaned application details found across all time periods. Database is clean!');
      return stats;
    }

    debug(`\nTotal orphaned records found across all periods: ${allOrphanedRecords.length}`);

    // Get sample records for reporting
    const sampleRecords = await getSampleOrphanedRecords(allOrphanedRecords, 5);
    if (sampleRecords.length > 0) {
      debug('Sample orphaned records:');
      sampleRecords.forEach((record, index) => {
        const createdDate = record._id.getTimestamp().toISOString();
        debug(`  ${index + 1}. ID: ${record._id}, FileID: ${record.fileId}, Created: ${createdDate}, Lat/Lon: ${record.lat},${record.lon}`);
      });
      if (allOrphanedRecords.length > 5) {
        debug(`  ... and ${allOrphanedRecords.length - 5} more records`);
      }
    }

    if (CHECK_ONLY) {
      debug('CHECK_ONLY mode: Not performing deletion');
      stats.processed = allOrphanedRecords.length;
      stats.dryRunCount = allOrphanedRecords.length;
      return stats;
    }

    // Process orphaned records in batches for deletion
    debug(`\nStarting deletion of ${allOrphanedRecords.length} orphaned records...`);
    const batches = utils.chunkArray(allOrphanedRecords, BATCH_SIZE);
    debug(`Processing ${batches.length} deletion batches of up to ${BATCH_SIZE} records each`);

    for (const batch of batches) {
      try {
        stats.batches++;

        // Delete the batch
        await deleteBatch(batch, stats);

        // Update progress
        showProgress(stats.processed, allOrphanedRecords.length, startTime);

        // Small delay between batches to reduce database load
        if (stats.batches < batches.length) {
          await sleep(100);
        }

      } catch (error) {
        debug(`Error processing deletion batch ${stats.batches}: ${error.message}`);
        stats.errors++;

        // Continue with next batch, but break if too many consecutive errors
        if (stats.errors > 8) {
          debug('Too many deletion errors, stopping operation');
          break;
        }
      }
    }

    const endTime = new Date();
    const duration = (endTime.getTime() - startTime.getTime()) / 1000;

    debug('\nCleanup operation completed!');
    debug('='.repeat(50));
    debug(`Time periods processed: ${stats.periodsProcessed}/${stats.periodsTotal}`);
    debug(`Total orphaned records found: ${stats.totalOrphaned}`);
    debug(`Total deletion batches processed: ${stats.batches}`);
    debug(`Records processed: ${stats.processed}`);

    if (DRY_RUN) {
      debug(`Dry run count: ${stats.dryRunCount}`);
    } else if (CHECK_ONLY) {
      debug(`Check only count: ${stats.dryRunCount}`);
    } else {
      debug(`Records deleted: ${stats.deleted}`);
    }

    debug(`Errors encountered: ${stats.errors}`);
    debug(`Duration: ${duration.toFixed(2)} seconds`);
    debug(`Average rate: ${(stats.processed / duration).toFixed(1)} records/second`);
    debug('='.repeat(50));

    return stats;

  } catch (error) {
    debug(`Fatal error during cleanup operation: ${error.message}`);
    throw error;
  }
}

/**
 * Set up global process error handling
 */
process
  .on('uncaughtException', function (err) {
    debug('Uncaught Exception:', err);
    process.exit(1);
  })
  .on('unhandledRejection', (reason, p) => {
    debug('Unhandled Rejection at Promise:', p, 'reason:', reason);
    process.exit(1);
  });

/**
 * Main execution - follows the same pattern as other worker scripts
 */
async function main() {
  const dbConn = new DBConnection('Clean Orphaned App Details Script');
  
  try {
    await dbConn.initialize({ setupExitHandlers: false });
    debug('Database connected');

    // Run the cleanup operation
    const result = await cleanOrphanedAppDetails();

    // Log final result
    if (result.errors > 0) {
      debug(`Operation completed with ${result.errors} errors`);
    } else {
      debug('Operation completed successfully');
    }

  } catch (error) {
    debug('Operation failed:', error);
  } finally {
    await dbConn.close();
    process.exit();
  }
}

// Execute main function
main();