MongoDB Schema Evolution and Migration Strategies: Advanced Patterns for Database Versioning, Backward Compatibility, and SQL-Style Schema Management

-- Traditional PostgreSQL schema migration - rigid and disruptive approach

-- Step 1: Create backup table (downtime and storage overhead)
CREATE TABLE users_backup AS SELECT * FROM users;

-- Step 2: Add new columns with application downtime
ALTER TABLE users 
ADD COLUMN user_preferences JSONB DEFAULT '{}',
ADD COLUMN subscription_tier VARCHAR(50) DEFAULT 'basic',
ADD COLUMN last_login_timestamp TIMESTAMP,
ADD COLUMN account_status VARCHAR(20) DEFAULT 'active',
ADD COLUMN profile_completion_percentage INTEGER DEFAULT 0;

-- Step 3: Update existing data (potentially long-running operation)
BEGIN TRANSACTION;

-- Complex data transformation requiring application logic
UPDATE users 
SET user_preferences = jsonb_build_object(
  'email_notifications', true,
  'privacy_level', 'standard',
  'theme', 'light',
  'language', 'en'
)
WHERE user_preferences = '{}';

-- Derive subscription tier from existing data
UPDATE users 
SET subscription_tier = CASE 
  WHEN annual_subscription_fee > 120 THEN 'premium'
  WHEN annual_subscription_fee > 60 THEN 'plus' 
  ELSE 'basic'
END
WHERE subscription_tier = 'basic';

-- Calculate profile completion
UPDATE users 
SET profile_completion_percentage = (
  CASE WHEN email IS NOT NULL THEN 20 ELSE 0 END +
  CASE WHEN phone IS NOT NULL THEN 20 ELSE 0 END +
  CASE WHEN address IS NOT NULL THEN 20 ELSE 0 END +
  CASE WHEN birth_date IS NOT NULL THEN 20 ELSE 0 END +
  CASE WHEN bio IS NOT NULL AND LENGTH(bio) > 50 THEN 20 ELSE 0 END
)
WHERE profile_completion_percentage = 0;

COMMIT TRANSACTION;

-- Step 4: Create new indexes (additional downtime)
CREATE INDEX CONCURRENTLY users_subscription_tier_idx ON users(subscription_tier);
CREATE INDEX CONCURRENTLY users_last_login_idx ON users(last_login_timestamp);
CREATE INDEX CONCURRENTLY users_account_status_idx ON users(account_status);

-- Step 5: Drop old columns (breaking change requiring application updates)
ALTER TABLE users 
DROP COLUMN IF EXISTS old_preferences_text,
DROP COLUMN IF EXISTS legacy_status_code,
DROP COLUMN IF EXISTS deprecated_login_count;

-- Step 6: Rename columns (coordinated deployment required)
ALTER TABLE users 
RENAME COLUMN user_email TO email_address,
RENAME COLUMN user_phone to phone_number;

-- Step 7: Create migration log table (manual tracking)
CREATE TABLE schema_migrations (
    migration_id SERIAL PRIMARY KEY,
    migration_name VARCHAR(200) NOT NULL,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    application_version VARCHAR(50),
    database_version VARCHAR(50),
    rollback_script TEXT,
    migration_notes TEXT
);

INSERT INTO schema_migrations (
    migration_name, 
    application_version, 
    database_version,
    rollback_script,
    migration_notes
) VALUES (
    'users_table_v2_migration',
    '2.1.0',
    '2.1.0',
    'ALTER TABLE users DROP COLUMN user_preferences, subscription_tier, last_login_timestamp, account_status, profile_completion_percentage;',
    'Added user preferences, subscription tiers, and profile completion tracking'
);

-- Problems with traditional schema migration approaches:
-- 1. Application downtime required for structural changes
-- 2. All-or-nothing migration approach with limited rollback capabilities
-- 3. Complex coordination between database and application deployments
-- 4. Risk of data loss during migration failures
-- 5. Performance impact during large table modifications
-- 6. Limited support for gradual migration and A/B testing scenarios
-- 7. Difficulty in maintaining multiple application versions simultaneously
-- 8. Complex rollback procedures requiring manual intervention
-- 9. Poor support for distributed systems and microservices architectures
-- 10. High operational overhead for migration planning and execution

// MongoDB Schema Evolution - flexible and non-disruptive approach
const { MongoClient } = require('mongodb');

// Advanced MongoDB Schema Migration and Evolution Management System
class MongoSchemaEvolutionManager {
  constructor(connectionUri, options = {}) {
    this.client = new MongoClient(connectionUri);
    this.db = null;
    this.collections = new Map();

    // Schema evolution configuration
    this.config = {
      // Migration strategy settings
      migrationStrategy: {
        approachType: options.migrationStrategy?.approachType || 'gradual', // gradual, immediate, hybrid
        batchSize: options.migrationStrategy?.batchSize || 1000,
        concurrentOperations: options.migrationStrategy?.concurrentOperations || 3,
        maxExecutionTimeMs: options.migrationStrategy?.maxExecutionTimeMs || 300000, // 5 minutes
        enableRollback: options.migrationStrategy?.enableRollback !== false
      },

      // Version management
      versionManagement: {
        trackDocumentVersions: options.versionManagement?.trackDocumentVersions !== false,
        versionField: options.versionManagement?.versionField || '_schema_version',
        migrationLogCollection: options.versionManagement?.migrationLogCollection || 'schema_migrations',
        enableVersionValidation: options.versionManagement?.enableVersionValidation !== false
      },

      // Backward compatibility
      backwardCompatibility: {
        maintainOldFields: options.backwardCompatibility?.maintainOldFields !== false,
        gracefulDegradation: options.backwardCompatibility?.gracefulDegradation !== false,
        compatibilityPeriodDays: options.backwardCompatibility?.compatibilityPeriodDays || 90,
        enableFieldAliasing: options.backwardCompatibility?.enableFieldAliasing !== false
      },

      // Performance optimization
      performanceSettings: {
        useIndexedMigration: options.performanceSettings?.useIndexedMigration !== false,
        enableProgressTracking: options.performanceSettings?.enableProgressTracking !== false,
        optimizeConcurrency: options.performanceSettings?.optimizeConcurrency !== false,
        memoryLimitMB: options.performanceSettings?.memoryLimitMB || 512
      }
    };

    // Schema version registry
    this.schemaVersions = new Map();
    this.migrationPlans = new Map();
    this.activeMigrations = new Map();

    // Migration execution state
    this.migrationProgress = new Map();
    this.rollbackStrategies = new Map();
  }

  async initialize(databaseName) {
    console.log('Initializing MongoDB Schema Evolution Manager...');

    try {
      await this.client.connect();
      this.db = this.client.db(databaseName);

      // Setup system collections for schema management
      await this.setupSchemaManagementCollections();

      // Load existing schema versions and migration history
      await this.loadSchemaVersionRegistry();

      console.log('Schema evolution manager initialized successfully');

    } catch (error) {
      console.error('Error initializing schema evolution manager:', error);
      throw error;
    }
  }

  async setupSchemaManagementCollections() {
    console.log('Setting up schema management collections...');

    // Schema version registry
    const schemaVersions = this.db.collection('schema_versions');
    await schemaVersions.createIndexes([
      { key: { collection_name: 1, version: 1 }, unique: true },
      { key: { is_active: 1 } },
      { key: { created_at: -1 } }
    ]);

    // Migration execution log
    const migrationLog = this.db.collection(this.config.versionManagement.migrationLogCollection);
    await migrationLog.createIndexes([
      { key: { migration_id: 1 }, unique: true },
      { key: { collection_name: 1, execution_timestamp: -1 } },
      { key: { migration_status: 1 } },
      { key: { schema_version_from: 1, schema_version_to: 1 } }
    ]);

    // Migration progress tracking
    const migrationProgress = this.db.collection('migration_progress');
    await migrationProgress.createIndexes([
      { key: { migration_id: 1 }, unique: true },
      { key: { collection_name: 1 } },
      { key: { status: 1 } }
    ]);
  }

  async defineSchemaVersion(collectionName, versionConfig) {
    console.log(`Defining schema version for collection: ${collectionName}`);

    const schemaVersion = {
      collection_name: collectionName,
      version: versionConfig.version,
      version_name: versionConfig.versionName || `v${versionConfig.version}`,

      // Schema definition
      schema_definition: {
        fields: versionConfig.fields || {},
        required_fields: versionConfig.requiredFields || [],
        optional_fields: versionConfig.optionalFields || [],
        deprecated_fields: versionConfig.deprecatedFields || [],

        // Field transformations and mappings
        field_mappings: versionConfig.fieldMappings || {},
        data_transformations: versionConfig.dataTransformations || {},
        validation_rules: versionConfig.validationRules || {}
      },

      // Migration configuration
      migration_config: {
        migration_type: versionConfig.migrationType || 'additive', // additive, transformative, breaking
        backward_compatible: versionConfig.backwardCompatible !== false,
        requires_reindex: versionConfig.requiresReindex || false,
        data_transformation_required: versionConfig.dataTransformationRequired || false,

        // Performance settings
        batch_processing: versionConfig.batchProcessing !== false,
        parallel_execution: versionConfig.parallelExecution || false,
        estimated_duration_minutes: versionConfig.estimatedDuration || 0
      },

      // Compatibility and rollback
      compatibility_info: {
        compatible_with_versions: versionConfig.compatibleVersions || [],
        breaking_changes: versionConfig.breakingChanges || [],
        rollback_strategy: versionConfig.rollbackStrategy || 'automatic',
        rollback_script: versionConfig.rollbackScript || null
      },

      // Metadata
      version_metadata: {
        created_by: versionConfig.createdBy || 'system',
        created_at: new Date(),
        is_active: versionConfig.isActive !== false,
        deployment_notes: versionConfig.deploymentNotes || '',
        business_justification: versionConfig.businessJustification || ''
      }
    };

    // Store schema version definition
    const schemaVersions = this.db.collection('schema_versions');
    await schemaVersions.replaceOne(
      { collection_name: collectionName, version: versionConfig.version },
      schemaVersion,
      { upsert: true }
    );

    // Cache schema version
    this.schemaVersions.set(`${collectionName}:${versionConfig.version}`, schemaVersion);

    console.log(`Schema version ${versionConfig.version} defined for ${collectionName}`);
    return schemaVersion;
  }

  async createMigrationPlan(collectionName, fromVersion, toVersion, options = {}) {
    console.log(`Creating migration plan: ${collectionName} v${fromVersion} → v${toVersion}`);

    const sourceSchema = this.schemaVersions.get(`${collectionName}:${fromVersion}`);
    const targetSchema = this.schemaVersions.get(`${collectionName}:${toVersion}`);

    if (!sourceSchema || !targetSchema) {
      throw new Error(`Schema version not found for migration: ${fromVersion} → ${toVersion}`);
    }

    const migrationPlan = {
      migration_id: this.generateMigrationId(),
      collection_name: collectionName,
      schema_version_from: fromVersion,
      schema_version_to: toVersion,

      // Migration analysis
      migration_analysis: {
        migration_type: this.analyzeMigrationType(sourceSchema, targetSchema),
        impact_assessment: await this.assessMigrationImpact(collectionName, sourceSchema, targetSchema),
        field_changes: this.analyzeFieldChanges(sourceSchema, targetSchema),
        data_transformation_required: this.requiresDataTransformation(sourceSchema, targetSchema)
      },

      // Execution plan
      execution_plan: {
        migration_steps: await this.generateMigrationSteps(sourceSchema, targetSchema),
        execution_order: options.executionOrder || 'sequential',
        batch_configuration: {
          batch_size: options.batchSize || this.config.migrationStrategy.batchSize,
          concurrent_batches: options.concurrentBatches || this.config.migrationStrategy.concurrentOperations,
          throttle_delay_ms: options.throttleDelay || 10
        },

        // Performance predictions
        estimated_execution_time: await this.estimateExecutionTime(collectionName, sourceSchema, targetSchema),
        resource_requirements: await this.calculateResourceRequirements(collectionName, sourceSchema, targetSchema)
      },

      // Safety and rollback
      safety_measures: {
        backup_required: options.backupRequired !== false,
        validation_checks: await this.generateValidationChecks(sourceSchema, targetSchema),
        rollback_plan: await this.generateRollbackPlan(sourceSchema, targetSchema),
        progress_checkpoints: options.progressCheckpoints || []
      },

      // Metadata
      plan_metadata: {
        created_at: new Date(),
        created_by: options.createdBy || 'system',
        plan_version: '1.0',
        approval_required: options.approvalRequired || false,
        deployment_window: options.deploymentWindow || null
      }
    };

    // Store migration plan
    await this.db.collection('migration_plans').replaceOne(
      { migration_id: migrationPlan.migration_id },
      migrationPlan,
      { upsert: true }
    );

    // Cache migration plan
    this.migrationPlans.set(migrationPlan.migration_id, migrationPlan);

    console.log(`Migration plan created: ${migrationPlan.migration_id}`);
    return migrationPlan;
  }

  async executeMigration(migrationId, options = {}) {
    console.log(`Executing migration: ${migrationId}`);

    const migrationPlan = this.migrationPlans.get(migrationId);
    if (!migrationPlan) {
      throw new Error(`Migration plan not found: ${migrationId}`);
    }

    const executionId = this.generateExecutionId();
    const startTime = Date.now();

    try {
      // Initialize migration execution tracking
      await this.initializeMigrationExecution(executionId, migrationPlan, options);

      // Pre-migration validation and preparation
      await this.performPreMigrationChecks(migrationPlan);

      // Execute migration based on strategy
      const migrationResult = await this.executeByStrategy(migrationPlan, executionId, options);

      // Post-migration validation
      await this.performPostMigrationValidation(migrationPlan, migrationResult);

      // Update migration log
      await this.logMigrationCompletion(executionId, migrationPlan, migrationResult, {
        start_time: startTime,
        end_time: Date.now(),
        status: 'success'
      });

      console.log(`Migration completed successfully: ${migrationId}`);
      return migrationResult;

    } catch (error) {
      console.error(`Migration failed: ${migrationId}`, error);

      // Attempt automatic rollback if enabled
      if (this.config.migrationStrategy.enableRollback && options.autoRollback !== false) {
        try {
          await this.executeRollback(executionId, migrationPlan);
        } catch (rollbackError) {
          console.error('Rollback failed:', rollbackError);
        }
      }

      // Log migration failure
      await this.logMigrationCompletion(executionId, migrationPlan, null, {
        start_time: startTime,
        end_time: Date.now(),
        status: 'failed',
        error: error.message
      });

      throw error;
    }
  }

  async executeByStrategy(migrationPlan, executionId, options) {
    const strategy = options.strategy || this.config.migrationStrategy.approachType;

    switch (strategy) {
      case 'gradual':
        return await this.executeGradualMigration(migrationPlan, executionId, options);
      case 'immediate':
        return await this.executeImmediateMigration(migrationPlan, executionId, options);
      case 'hybrid':
        return await this.executeHybridMigration(migrationPlan, executionId, options);
      default:
        throw new Error(`Unknown migration strategy: ${strategy}`);
    }
  }

  async executeGradualMigration(migrationPlan, executionId, options) {
    console.log('Executing gradual migration strategy...');

    const collection = this.db.collection(migrationPlan.collection_name);
    const batchConfig = migrationPlan.execution_plan.batch_configuration;

    let processedCount = 0;
    let totalCount = await collection.countDocuments();
    let lastId = null;

    console.log(`Processing ${totalCount} documents in batches of ${batchConfig.batch_size}`);

    while (processedCount < totalCount) {
      // Build batch query
      const batchQuery = lastId 
        ? { _id: { $gt: lastId }, [this.config.versionManagement.versionField]: migrationPlan.schema_version_from }
        : { [this.config.versionManagement.versionField]: migrationPlan.schema_version_from };

      // Get batch of documents
      const batch = await collection
        .find(batchQuery)
        .sort({ _id: 1 })
        .limit(batchConfig.batch_size)
        .toArray();

      if (batch.length === 0) {
        break; // No more documents to process
      }

      // Process batch
      const batchResult = await this.processMigrationBatch(
        collection, 
        batch, 
        migrationPlan.execution_plan.migration_steps,
        migrationPlan.schema_version_to
      );

      processedCount += batch.length;
      lastId = batch[batch.length - 1]._id;

      // Update progress
      await this.updateMigrationProgress(executionId, {
        processed_count: processedCount,
        total_count: totalCount,
        progress_percentage: (processedCount / totalCount) * 100,
        last_processed_id: lastId
      });

      // Throttle to avoid overwhelming the system
      if (batchConfig.throttle_delay_ms > 0) {
        await new Promise(resolve => setTimeout(resolve, batchConfig.throttle_delay_ms));
      }

      console.log(`Processed ${processedCount}/${totalCount} documents (${((processedCount / totalCount) * 100).toFixed(1)}%)`);
    }

    return {
      strategy: 'gradual',
      processed_count: processedCount,
      total_count: totalCount,
      batches_processed: Math.ceil(processedCount / batchConfig.batch_size),
      success: true
    };
  }

  async processMigrationBatch(collection, documents, migrationSteps, targetVersion) {
    const bulkOperations = [];

    for (const doc of documents) {
      let transformedDoc = { ...doc };

      // Apply each migration step
      for (const step of migrationSteps) {
        transformedDoc = await this.applyMigrationStep(transformedDoc, step);
      }

      // Update schema version
      transformedDoc[this.config.versionManagement.versionField] = targetVersion;
      transformedDoc._migration_timestamp = new Date();

      // Add to bulk operations
      bulkOperations.push({
        replaceOne: {
          filter: { _id: doc._id },
          replacement: transformedDoc
        }
      });
    }

    // Execute bulk operation
    if (bulkOperations.length > 0) {
      const result = await collection.bulkWrite(bulkOperations, { ordered: false });
      return {
        modified_count: result.modifiedCount,
        matched_count: result.matchedCount,
        errors: result.getWriteErrors()
      };
    }

    return { modified_count: 0, matched_count: 0, errors: [] };
  }

  async applyMigrationStep(document, migrationStep) {
    let transformedDoc = { ...document };

    switch (migrationStep.type) {
      case 'add_field':
        transformedDoc[migrationStep.field_name] = migrationStep.default_value;
        break;

      case 'rename_field':
        if (transformedDoc[migrationStep.old_field_name] !== undefined) {
          transformedDoc[migrationStep.new_field_name] = transformedDoc[migrationStep.old_field_name];
          delete transformedDoc[migrationStep.old_field_name];
        }
        break;

      case 'transform_field':
        if (transformedDoc[migrationStep.field_name] !== undefined) {
          transformedDoc[migrationStep.field_name] = await this.applyFieldTransformation(
            transformedDoc[migrationStep.field_name],
            migrationStep.transformation
          );
        }
        break;

      case 'nested_restructure':
        transformedDoc = await this.applyNestedRestructure(transformedDoc, migrationStep.restructure_config);
        break;

      case 'data_type_conversion':
        if (transformedDoc[migrationStep.field_name] !== undefined) {
          transformedDoc[migrationStep.field_name] = this.convertDataType(
            transformedDoc[migrationStep.field_name],
            migrationStep.target_type
          );
        }
        break;

      case 'conditional_transformation':
        if (this.evaluateCondition(transformedDoc, migrationStep.condition)) {
          transformedDoc = await this.applyConditionalTransformation(transformedDoc, migrationStep.transformation);
        }
        break;

      default:
        console.warn(`Unknown migration step type: ${migrationStep.type}`);
    }

    return transformedDoc;
  }

  async generateBackwardCompatibilityLayer(collectionName, fromVersion, toVersion) {
    console.log(`Generating backward compatibility layer: ${collectionName} v${fromVersion} ↔ v${toVersion}`);

    const sourceSchema = this.schemaVersions.get(`${collectionName}:${fromVersion}`);
    const targetSchema = this.schemaVersions.get(`${collectionName}:${toVersion}`);

    const compatibilityLayer = {
      collection_name: collectionName,
      source_version: fromVersion,
      target_version: toVersion,

      // Field mapping for backward compatibility
      field_mappings: {
        // Map old field names to new field names
        old_to_new: this.generateFieldMappings(sourceSchema, targetSchema, 'forward'),
        new_to_old: this.generateFieldMappings(targetSchema, sourceSchema, 'backward')
      },

      // Data transformation functions
      transformation_functions: {
        forward_transform: await this.generateTransformationFunction(sourceSchema, targetSchema, 'forward'),
        backward_transform: await this.generateTransformationFunction(targetSchema, sourceSchema, 'backward')
      },

      // API compatibility
      api_compatibility: {
        deprecated_fields: this.identifyDeprecatedFields(sourceSchema, targetSchema),
        field_aliases: this.generateFieldAliases(sourceSchema, targetSchema),
        default_values: this.generateDefaultValues(targetSchema)
      },

      // Migration instructions
      migration_instructions: {
        application_changes_required: this.identifyRequiredApplicationChanges(sourceSchema, targetSchema),
        breaking_changes: this.identifyBreakingChanges(sourceSchema, targetSchema),
        migration_timeline: this.generateMigrationTimeline(sourceSchema, targetSchema)
      }
    };

    // Store compatibility layer configuration
    await this.db.collection('compatibility_layers').replaceOne(
      { collection_name: collectionName, source_version: fromVersion, target_version: toVersion },
      compatibilityLayer,
      { upsert: true }
    );

    return compatibilityLayer;
  }

  async validateMigrationIntegrity(collectionName, migrationId, options = {}) {
    console.log(`Validating migration integrity: ${collectionName} (${migrationId})`);

    const collection = this.db.collection(collectionName);
    const migrationPlan = this.migrationPlans.get(migrationId);

    if (!migrationPlan) {
      throw new Error(`Migration plan not found: ${migrationId}`);
    }

    const validationResults = {
      migration_id: migrationId,
      collection_name: collectionName,
      validation_timestamp: new Date(),

      // Document count validation
      document_counts: {
        total_documents: await collection.countDocuments(),
        migrated_documents: await collection.countDocuments({
          [this.config.versionManagement.versionField]: migrationPlan.schema_version_to
        }),
        unmigrated_documents: await collection.countDocuments({
          [this.config.versionManagement.versionField]: { $ne: migrationPlan.schema_version_to }
        })
      },

      // Schema validation
      schema_validation: await this.validateSchemaCompliance(collection, migrationPlan.schema_version_to),

      // Data integrity checks
      data_integrity: await this.performDataIntegrityChecks(collection, migrationPlan),

      // Performance impact assessment
      performance_impact: await this.assessPerformanceImpact(collection, migrationPlan),

      // Compatibility verification
      compatibility_status: await this.verifyBackwardCompatibility(collection, migrationPlan)
    };

    // Calculate overall validation status
    validationResults.overall_status = this.calculateOverallValidationStatus(validationResults);

    // Store validation results
    await this.db.collection('migration_validations').insertOne(validationResults);

    console.log(`Migration validation completed: ${validationResults.overall_status}`);
    return validationResults;
  }

  // Utility methods for migration management
  generateMigrationId() {
    return `migration_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
  }

  generateExecutionId() {
    return `exec_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
  }

  async loadSchemaVersionRegistry() {
    const schemaVersions = await this.db.collection('schema_versions')
      .find({ 'version_metadata.is_active': true })
      .toArray();

    schemaVersions.forEach(schema => {
      this.schemaVersions.set(`${schema.collection_name}:${schema.version}`, schema);
    });

    console.log(`Loaded ${schemaVersions.length} active schema versions`);
  }

  analyzeMigrationType(sourceSchema, targetSchema) {
    const sourceFields = new Set(Object.keys(sourceSchema.schema_definition.fields));
    const targetFields = new Set(Object.keys(targetSchema.schema_definition.fields));

    const addedFields = [...targetFields].filter(field => !sourceFields.has(field));
    const removedFields = [...sourceFields].filter(field => !targetFields.has(field));
    const modifiedFields = [...sourceFields].filter(field => 
      targetFields.has(field) && 
      JSON.stringify(sourceSchema.schema_definition.fields[field]) !== 
      JSON.stringify(targetSchema.schema_definition.fields[field])
    );

    if (removedFields.length > 0 || modifiedFields.length > 0) {
      return 'breaking';
    } else if (addedFields.length > 0) {
      return 'additive';
    } else {
      return 'maintenance';
    }
  }
}

// Example usage demonstrating comprehensive MongoDB schema evolution
async function demonstrateSchemaEvolution() {
  const schemaManager = new MongoSchemaEvolutionManager('mongodb://localhost:27017');

  try {
    await schemaManager.initialize('ecommerce_platform');

    console.log('Defining initial user schema version...');

    // Define initial schema version
    await schemaManager.defineSchemaVersion('users', {
      version: '1.0',
      versionName: 'initial_user_schema',
      fields: {
        _id: { type: 'ObjectId', required: true },
        email: { type: 'String', required: true, unique: true },
        password_hash: { type: 'String', required: true },
        created_at: { type: 'Date', required: true },
        last_login: { type: 'Date', required: false }
      },
      requiredFields: ['_id', 'email', 'password_hash', 'created_at'],
      migrationType: 'initial',
      backwardCompatible: true
    });

    // Define enhanced schema version
    await schemaManager.defineSchemaVersion('users', {
      version: '2.0',
      versionName: 'enhanced_user_profile',
      fields: {
        _id: { type: 'ObjectId', required: true },
        email: { type: 'String', required: true, unique: true },
        password_hash: { type: 'String', required: true },

        // New profile fields
        profile: {
          type: 'Object',
          required: false,
          fields: {
            first_name: { type: 'String', required: false },
            last_name: { type: 'String', required: false },
            avatar_url: { type: 'String', required: false },
            bio: { type: 'String', required: false, max_length: 500 }
          }
        },

        // Enhanced user preferences
        preferences: {
          type: 'Object',
          required: false,
          fields: {
            email_notifications: { type: 'Boolean', default: true },
            privacy_level: { type: 'String', enum: ['public', 'friends', 'private'], default: 'public' },
            theme: { type: 'String', enum: ['light', 'dark'], default: 'light' },
            language: { type: 'String', default: 'en' }
          }
        },

        // Subscription and status
        subscription: {
          type: 'Object',
          required: false,
          fields: {
            tier: { type: 'String', enum: ['basic', 'plus', 'premium'], default: 'basic' },
            expires_at: { type: 'Date', required: false },
            auto_renewal: { type: 'Boolean', default: false }
          }
        },

        // Tracking and analytics
        activity: {
          type: 'Object',
          required: false,
          fields: {
            last_login: { type: 'Date', required: false },
            login_count: { type: 'Number', default: 0 },
            profile_completion: { type: 'Number', min: 0, max: 100, default: 0 }
          }
        },

        created_at: { type: 'Date', required: true },
        updated_at: { type: 'Date', required: true }
      },
      requiredFields: ['_id', 'email', 'password_hash', 'created_at', 'updated_at'],

      // Migration configuration
      migrationType: 'additive',
      backwardCompatible: true,

      // Field mappings and transformations
      fieldMappings: {
        last_login: 'activity.last_login'
      },

      dataTransformations: {
        // Transform old last_login field to new nested structure
        'activity.last_login': 'document.last_login',
        'activity.login_count': '1',
        'profile_completion': 'calculateProfileCompletion(document)',
        'preferences': 'generateDefaultPreferences()',
        'subscription.tier': 'deriveTierFromHistory(document)'
      }
    });

    // Create migration plan
    const migrationPlan = await schemaManager.createMigrationPlan('users', '1.0', '2.0', {
      batchSize: 500,
      concurrentBatches: 2,
      backupRequired: true,
      deploymentWindow: {
        start: '2024-01-15T02:00:00Z',
        end: '2024-01-15T06:00:00Z'
      }
    });

    console.log('Migration plan created:', migrationPlan.migration_id);

    // Generate backward compatibility layer
    const compatibilityLayer = await schemaManager.generateBackwardCompatibilityLayer('users', '1.0', '2.0');
    console.log('Backward compatibility layer generated');

    // Execute migration (if approved and in deployment window)
    if (process.env.EXECUTE_MIGRATION === 'true') {
      const migrationResult = await schemaManager.executeMigration(migrationPlan.migration_id, {
        strategy: 'gradual',
        autoRollback: true
      });

      console.log('Migration executed:', migrationResult);

      // Validate migration integrity
      const validationResults = await schemaManager.validateMigrationIntegrity('users', migrationPlan.migration_id);
      console.log('Migration validation:', validationResults.overall_status);
    }

  } catch (error) {
    console.error('Schema evolution demonstration error:', error);
  }
}

module.exports = {
  MongoSchemaEvolutionManager,
  demonstrateSchemaEvolution
};

// Production-ready schema evolution with advanced migration patterns
class EnterpriseSchemaEvolutionManager extends MongoSchemaEvolutionManager {
  constructor(connectionUri, enterpriseConfig) {
    super(connectionUri, enterpriseConfig);

    this.enterpriseFeatures = {
      // Advanced migration orchestration
      migrationOrchestration: {
        distributedMigration: true,
        crossCollectionDependencies: true,
        transactionalMigration: true,
        rollbackOrchestration: true
      },

      // Enterprise integration
      enterpriseIntegration: {
        cicdIntegration: true,
        approvalWorkflows: true,
        auditCompliance: true,
        performanceMonitoring: true
      },

      // Advanced compatibility management
      compatibilityManagement: {
        multiVersionSupport: true,
        apiVersioning: true,
        clientCompatibilityTracking: true,
        automaticDeprecation: true
      }
    };
  }

  async orchestrateDistributedMigration(migrationConfig) {
    console.log('Orchestrating distributed migration across collections...');

    const distributedPlan = {
      // Cross-collection dependency management
      dependencyGraph: await this.analyzeCrossCollectionDependencies(migrationConfig.collections),

      // Coordinated execution strategy
      executionStrategy: {
        coordinationMethod: 'transaction', // transaction, phased, eventually_consistent
        consistencyLevel: 'strong', // strong, eventual, causal
        isolationLevel: 'snapshot', // snapshot, read_committed, read_uncommitted
        rollbackStrategy: 'coordinated' // coordinated, independent, manual
      },

      // Performance optimization
      performanceOptimization: {
        parallelCollections: true,
        resourceBalancing: true,
        priorityQueueing: true,
        adaptiveThrottling: true
      }
    };

    return await this.executeDistributedMigration(distributedPlan);
  }

  async implementSmartRollback(migrationId, rollbackConfig) {
    console.log('Implementing smart rollback with data recovery...');

    const rollbackStrategy = {
      // Intelligent rollback analysis
      rollbackAnalysis: {
        dataImpactAssessment: true,
        dependencyReversal: true,
        performanceImpactMinimization: true,
        dataConsistencyVerification: true
      },

      // Recovery mechanisms
      recoveryMechanisms: {
        pointInTimeRecovery: rollbackConfig.pointInTimeRecovery || false,
        incrementalRollback: rollbackConfig.incrementalRollback || false,
        dataReconciliation: rollbackConfig.dataReconciliation || true,
        consistencyRepair: rollbackConfig.consistencyRepair || true
      }
    };

    return await this.executeSmartRollback(migrationId, rollbackStrategy);
  }
}

-- QueryLeaf schema evolution with SQL-familiar migration patterns

-- Define comprehensive schema version with validation and constraints
CREATE SCHEMA_VERSION users_v2 FOR COLLECTION users AS (
  -- Schema version metadata
  version_number = '2.0',
  version_name = 'enhanced_user_profiles',
  migration_type = 'additive',
  backward_compatible = true,

  -- Field definitions with validation rules
  field_definitions = JSON_OBJECT(
    '_id', JSON_OBJECT('type', 'ObjectId', 'required', true, 'primary_key', true),
    'email', JSON_OBJECT('type', 'String', 'required', true, 'unique', true, 'format', 'email'),
    'password_hash', JSON_OBJECT('type', 'String', 'required', true, 'min_length', 60),

    -- New nested profile structure
    'profile', JSON_OBJECT(
      'type', 'Object',
      'required', false,
      'fields', JSON_OBJECT(
        'first_name', JSON_OBJECT('type', 'String', 'max_length', 50),
        'last_name', JSON_OBJECT('type', 'String', 'max_length', 50),
        'display_name', JSON_OBJECT('type', 'String', 'max_length', 100),
        'avatar_url', JSON_OBJECT('type', 'String', 'format', 'url'),
        'bio', JSON_OBJECT('type', 'String', 'max_length', 500),
        'date_of_birth', JSON_OBJECT('type', 'Date', 'format', 'YYYY-MM-DD'),
        'location', JSON_OBJECT(
          'type', 'Object',
          'fields', JSON_OBJECT(
            'city', JSON_OBJECT('type', 'String'),
            'country', JSON_OBJECT('type', 'String', 'length', 2),
            'timezone', JSON_OBJECT('type', 'String')
          )
        )
      )
    ),

    -- Enhanced user preferences with defaults
    'preferences', JSON_OBJECT(
      'type', 'Object',
      'required', false,
      'default', JSON_OBJECT(
        'email_notifications', true,
        'privacy_level', 'public',
        'theme', 'light',
        'language', 'en'
      ),
      'fields', JSON_OBJECT(
        'email_notifications', JSON_OBJECT('type', 'Boolean', 'default', true),
        'privacy_level', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('public', 'friends', 'private'), 'default', 'public'),
        'theme', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('light', 'dark', 'auto'), 'default', 'light'),
        'language', JSON_OBJECT('type', 'String', 'pattern', '^[a-z]{2}$', 'default', 'en'),
        'notification_settings', JSON_OBJECT(
          'type', 'Object',
          'fields', JSON_OBJECT(
            'push_notifications', JSON_OBJECT('type', 'Boolean', 'default', true),
            'email_frequency', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('immediate', 'daily', 'weekly'), 'default', 'daily')
          )
        )
      )
    ),

    -- Subscription and billing information
    'subscription', JSON_OBJECT(
      'type', 'Object',
      'required', false,
      'fields', JSON_OBJECT(
        'tier', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('free', 'basic', 'plus', 'premium'), 'default', 'free'),
        'status', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('active', 'cancelled', 'expired', 'trial'), 'default', 'active'),
        'starts_at', JSON_OBJECT('type', 'Date'),
        'expires_at', JSON_OBJECT('type', 'Date'),
        'auto_renewal', JSON_OBJECT('type', 'Boolean', 'default', false),
        'billing_cycle', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('monthly', 'yearly'), 'default', 'monthly')
      )
    ),

    -- Activity tracking and analytics
    'activity_metrics', JSON_OBJECT(
      'type', 'Object',
      'required', false,
      'fields', JSON_OBJECT(
        'last_login_at', JSON_OBJECT('type', 'Date'),
        'login_count', JSON_OBJECT('type', 'Integer', 'min', 0, 'default', 0),
        'profile_completion_score', JSON_OBJECT('type', 'Integer', 'min', 0, 'max', 100, 'default', 0),
        'account_verification_status', JSON_OBJECT('type', 'String', 'enum', JSON_ARRAY('pending', 'verified', 'rejected'), 'default', 'pending'),
        'last_profile_update', JSON_OBJECT('type', 'Date'),
        'feature_usage_stats', JSON_OBJECT(
          'type', 'Object',
          'fields', JSON_OBJECT(
            'dashboard_visits', JSON_OBJECT('type', 'Integer', 'default', 0),
            'api_calls_count', JSON_OBJECT('type', 'Integer', 'default', 0),
            'storage_usage_bytes', JSON_OBJECT('type', 'Long', 'default', 0)
          )
        )
      )
    ),

    -- Timestamps and audit trail
    'created_at', JSON_OBJECT('type', 'Date', 'required', true, 'immutable', true),
    'updated_at', JSON_OBJECT('type', 'Date', 'required', true, 'auto_update', true),
    '_schema_version', JSON_OBJECT('type', 'String', 'required', true, 'default', '2.0')
  ),

  -- Migration mapping from previous version
  migration_mappings = JSON_OBJECT(
    -- Direct field mappings
    'last_login', 'activity_metrics.last_login_at',

    -- Computed field mappings
    'activity_metrics.login_count', 'COALESCE(login_count, 1)',
    'activity_metrics.profile_completion_score', 'CALCULATE_PROFILE_COMPLETION(profile)',
    'subscription.tier', 'DERIVE_TIER_FROM_USAGE(usage_history)',
    'preferences', 'GENERATE_DEFAULT_PREFERENCES()',
    'updated_at', 'CURRENT_TIMESTAMP'
  ),

  -- Validation rules for data integrity
  validation_rules = JSON_ARRAY(
    JSON_OBJECT('rule', 'email_domain_validation', 'expression', 'email REGEXP ''^[^@]+@[^@]+\\.[^@]+$'''),
    JSON_OBJECT('rule', 'subscription_dates_consistency', 'expression', 'subscription.expires_at > subscription.starts_at'),
    JSON_OBJECT('rule', 'profile_completion_accuracy', 'expression', 'activity_metrics.profile_completion_score <= 100'),
    JSON_OBJECT('rule', 'timezone_validation', 'expression', 'profile.location.timezone IN (SELECT timezone FROM valid_timezones)')
  ),

  -- Index optimization for new schema
  index_definitions = JSON_ARRAY(
    JSON_OBJECT('fields', JSON_OBJECT('email', 1), 'unique', true, 'sparse', false),
    JSON_OBJECT('fields', JSON_OBJECT('subscription.tier', 1, 'subscription.status', 1), 'background', true),
    JSON_OBJECT('fields', JSON_OBJECT('activity_metrics.last_login_at', -1), 'background', true),
    JSON_OBJECT('fields', JSON_OBJECT('profile.location.country', 1), 'sparse', true),
    JSON_OBJECT('fields', JSON_OBJECT('_schema_version', 1), 'background', true)
  ),

  -- Compatibility and deprecation settings
  compatibility_settings = JSON_OBJECT(
    'maintain_old_fields_days', 90,
    'deprecated_fields', JSON_ARRAY('last_login', 'login_count'),
    'breaking_changes', JSON_ARRAY(),
    'migration_required_for', JSON_ARRAY('v1.0', 'v1.5')
  )
);

-- Create comprehensive migration plan with performance optimization
WITH migration_analysis AS (
  SELECT 
    collection_name,
    current_schema_version,
    target_schema_version,

    -- Document analysis for migration planning
    COUNT(*) as total_documents,
    AVG(LENGTH(BSON_SIZE(document))) as avg_document_size,
    SUM(LENGTH(BSON_SIZE(document))) / 1024 / 1024 as total_size_mb,

    -- Performance projections
    CASE 
      WHEN COUNT(*) > 10000000 THEN 'large_collection_parallel_required'
      WHEN COUNT(*) > 1000000 THEN 'medium_collection_batch_optimize'
      ELSE 'small_collection_standard_processing'
    END as processing_category,

    -- Migration complexity assessment
    CASE 
      WHEN target_schema_version LIKE '%.0' THEN 'major_version_comprehensive_testing'
      WHEN COUNT_SCHEMA_CHANGES(current_schema_version, target_schema_version) > 10 THEN 'complex_migration'
      ELSE 'standard_migration'
    END as migration_complexity,

    -- Resource requirements estimation
    CEIL(COUNT(*) / 1000.0) as estimated_batches,
    CEIL((SUM(LENGTH(BSON_SIZE(document))) / 1024 / 1024) / 100.0) * 2 as estimated_duration_minutes,
    CEIL(COUNT(*) / 10000.0) * 512 as estimated_memory_mb

  FROM users u
  JOIN schema_version_registry svr ON u._schema_version = svr.version
  WHERE svr.collection_name = 'users'
  GROUP BY collection_name, current_schema_version, target_schema_version
),

-- Generate optimized migration execution plan
migration_execution_plan AS (
  SELECT 
    ma.*,

    -- Batch processing configuration
    CASE ma.processing_category
      WHEN 'large_collection_parallel_required' THEN 
        JSON_OBJECT(
          'batch_size', 500,
          'concurrent_batches', 5,
          'parallel_collections', true,
          'memory_limit_per_batch_mb', 256,
          'throttle_delay_ms', 50
        )
      WHEN 'medium_collection_batch_optimize' THEN
        JSON_OBJECT(
          'batch_size', 1000,
          'concurrent_batches', 3,
          'parallel_collections', false,
          'memory_limit_per_batch_mb', 128,
          'throttle_delay_ms', 10
        )
      ELSE
        JSON_OBJECT(
          'batch_size', 2000,
          'concurrent_batches', 1,
          'parallel_collections', false,
          'memory_limit_per_batch_mb', 64,
          'throttle_delay_ms', 0
        )
    END as batch_configuration,

    -- Safety and rollback configuration
    JSON_OBJECT(
      'backup_required', CASE WHEN ma.total_documents > 100000 THEN true ELSE false END,
      'rollback_enabled', true,
      'validation_sample_size', LEAST(ma.total_documents * 0.1, 10000),
      'progress_checkpoint_interval', GREATEST(ma.estimated_batches / 10, 1),
      'failure_threshold_percent', 5.0
    ) as safety_configuration,

    -- Performance monitoring setup
    JSON_OBJECT(
      'monitor_memory_usage', true,
      'monitor_throughput', true,
      'monitor_lock_contention', true,
      'alert_on_slowdown_percent', 50,
      'performance_baseline_samples', 100
    ) as monitoring_configuration

  FROM migration_analysis ma
)

-- Create and execute migration plan
CREATE MIGRATION_PLAN users_v1_to_v2 AS (
  SELECT 
    mep.*,

    -- Migration steps with detailed transformations
    JSON_ARRAY(
      -- Step 1: Add new schema version field
      JSON_OBJECT(
        'step_number', 1,
        'step_type', 'add_field',
        'field_name', '_schema_version',
        'default_value', '2.0',
        'description', 'Add schema version tracking'
      ),

      -- Step 2: Restructure activity data
      JSON_OBJECT(
        'step_number', 2,
        'step_type', 'nested_restructure',
        'restructure_config', JSON_OBJECT(
          'create_nested_object', 'activity_metrics',
          'field_mappings', JSON_OBJECT(
            'last_login', 'activity_metrics.last_login_at',
            'login_count', 'activity_metrics.login_count'
          ),
          'computed_fields', JSON_OBJECT(
            'activity_metrics.profile_completion_score', 'CALCULATE_PROFILE_COMPLETION(profile)',
            'activity_metrics.account_verification_status', '''pending'''
          )
        )
      ),

      -- Step 3: Generate default preferences
      JSON_OBJECT(
        'step_number', 3,
        'step_type', 'add_field',
        'field_name', 'preferences',
        'transformation', 'GENERATE_DEFAULT_PREFERENCES()',
        'description', 'Add user preferences with smart defaults'
      ),

      -- Step 4: Initialize subscription data
      JSON_OBJECT(
        'step_number', 4,
        'step_type', 'add_field',
        'field_name', 'subscription',
        'transformation', 'DERIVE_SUBSCRIPTION_INFO(user_history)',
        'description', 'Initialize subscription information from usage history'
      ),

      -- Step 5: Update timestamps
      JSON_OBJECT(
        'step_number', 5,
        'step_type', 'add_field',
        'field_name', 'updated_at',
        'default_value', 'CURRENT_TIMESTAMP',
        'description', 'Add updated timestamp for audit trail'
      )
    ) as migration_steps,

    -- Validation and verification tests
    JSON_ARRAY(
      JSON_OBJECT(
        'test_name', 'schema_version_consistency',
        'test_query', 'SELECT COUNT(*) FROM users WHERE _schema_version != ''2.0''',
        'expected_result', 0,
        'severity', 'critical'
      ),
      JSON_OBJECT(
        'test_name', 'data_completeness_check',
        'test_query', 'SELECT COUNT(*) FROM users WHERE activity_metrics IS NULL',
        'expected_result', 0,
        'severity', 'critical'
      ),
      JSON_OBJECT(
        'test_name', 'preferences_initialization',
        'test_query', 'SELECT COUNT(*) FROM users WHERE preferences IS NULL',
        'expected_result', 0,
        'severity', 'high'
      ),
      JSON_OBJECT(
        'test_name', 'profile_completion_accuracy',
        'test_query', 'SELECT COUNT(*) FROM users WHERE activity_metrics.profile_completion_score < 0 OR activity_metrics.profile_completion_score > 100',
        'expected_result', 0,
        'severity', 'medium'
      )
    ) as validation_tests

  FROM migration_execution_plan mep
);

-- Execute migration with comprehensive monitoring and safety checks
EXECUTE MIGRATION users_v1_to_v2 WITH OPTIONS (
  -- Execution settings
  execution_mode = 'gradual',  -- gradual, immediate, test_mode
  safety_checks_enabled = true,
  automatic_rollback = true,

  -- Performance settings
  resource_limits = JSON_OBJECT(
    'max_memory_usage_mb', 1024,
    'max_execution_time_minutes', 120,
    'max_cpu_usage_percent', 80,
    'io_throttling_enabled', true
  ),

  -- Monitoring and alerting
  monitoring = JSON_OBJECT(
    'progress_reporting_interval_seconds', 30,
    'performance_metrics_collection', true,
    'alert_on_errors', true,
    'alert_email', '[email protected]'
  ),

  -- Backup and recovery
  backup_settings = JSON_OBJECT(
    'create_backup_before_migration', true,
    'backup_location', 'migrations/backup_users_v1_to_v2',
    'verify_backup_integrity', true
  )
);

-- Monitor migration progress with real-time analytics
WITH migration_progress AS (
  SELECT 
    migration_id,
    execution_id,
    collection_name,
    schema_version_from,
    schema_version_to,

    -- Progress tracking
    total_documents,
    processed_documents,
    ROUND((processed_documents::numeric / total_documents) * 100, 2) as progress_percentage,

    -- Performance metrics
    EXTRACT(SECONDS FROM CURRENT_TIMESTAMP - migration_started_at) as elapsed_seconds,
    ROUND(processed_documents::numeric / EXTRACT(SECONDS FROM CURRENT_TIMESTAMP - migration_started_at), 2) as documents_per_second,

    -- Resource utilization
    current_memory_usage_mb,
    peak_memory_usage_mb,
    cpu_usage_percent,

    -- Quality indicators
    error_count,
    warning_count,
    validation_failures,

    -- ETA calculation
    CASE 
      WHEN processed_documents > 0 AND migration_status = 'running' THEN
        CURRENT_TIMESTAMP + 
        (INTERVAL '1 second' * 
         ((total_documents - processed_documents) / 
          (processed_documents::numeric / EXTRACT(SECONDS FROM CURRENT_TIMESTAMP - migration_started_at))))
      ELSE NULL
    END as estimated_completion_time,

    migration_status

  FROM migration_execution_status
  WHERE migration_status IN ('running', 'validating', 'finalizing')
),

-- Performance trend analysis
performance_trends AS (
  SELECT 
    migration_id,

    -- Throughput trends (last 5 minutes)
    AVG(documents_per_second) OVER (
      ORDER BY checkpoint_timestamp 
      ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) as avg_throughput_5min,

    -- Memory usage trends
    AVG(memory_usage_mb) OVER (
      ORDER BY checkpoint_timestamp
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW  
    ) as avg_memory_usage_10min,

    -- Error rate trends
    SUM(errors_since_last_checkpoint) OVER (
      ORDER BY checkpoint_timestamp
      ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) as error_count_20min,

    -- Performance indicators
    CASE 
      WHEN documents_per_second < avg_documents_per_second * 0.7 THEN 'degraded_performance'
      WHEN memory_usage_mb > peak_memory_usage_mb * 0.9 THEN 'high_memory_usage'
      WHEN error_count > 0 THEN 'errors_detected'
      ELSE 'healthy'
    END as health_status

  FROM migration_performance_checkpoints
  WHERE checkpoint_timestamp >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
)

-- Migration monitoring dashboard
SELECT 
  -- Current status overview
  mp.migration_id,
  mp.collection_name,
  mp.progress_percentage || '%' as progress,
  mp.documents_per_second || ' docs/sec' as throughput,
  mp.estimated_completion_time,
  mp.migration_status,

  -- Resource utilization
  mp.current_memory_usage_mb || 'MB (' || 
    ROUND((mp.current_memory_usage_mb::numeric / mp.peak_memory_usage_mb) * 100, 1) || '% of peak)' as memory_usage,
  mp.cpu_usage_percent || '%' as cpu_usage,

  -- Quality indicators
  mp.error_count as errors,
  mp.warning_count as warnings,
  mp.validation_failures as validation_issues,

  -- Performance health
  pt.health_status,
  pt.avg_throughput_5min || ' docs/sec (5min avg)' as recent_throughput,

  -- Recommendations
  CASE 
    WHEN pt.health_status = 'degraded_performance' THEN 'Consider reducing batch size or increasing resources'
    WHEN pt.health_status = 'high_memory_usage' THEN 'Monitor for potential memory issues'
    WHEN pt.health_status = 'errors_detected' THEN 'Review error logs and consider pausing migration'
    WHEN mp.progress_percentage > 95 THEN 'Migration nearing completion, prepare for validation'
    ELSE 'Migration proceeding normally'
  END as recommendation,

  -- Next actions
  CASE 
    WHEN mp.migration_status = 'running' AND mp.progress_percentage > 99 THEN 'Begin final validation phase'
    WHEN mp.migration_status = 'validating' THEN 'Performing post-migration validation tests'
    WHEN mp.migration_status = 'finalizing' THEN 'Completing migration and cleanup'
    ELSE 'Continue monitoring progress'
  END as next_action

FROM migration_progress mp
LEFT JOIN performance_trends pt ON mp.migration_id = pt.migration_id
WHERE mp.migration_id = (SELECT MAX(migration_id) FROM migration_progress)

UNION ALL

-- Historical migration performance summary
SELECT 
  'HISTORICAL_SUMMARY' as migration_id,
  collection_name,
  NULL as progress,
  AVG(final_throughput) || ' docs/sec avg' as throughput,
  NULL as estimated_completion_time,
  'completed' as migration_status,
  AVG(peak_memory_usage_mb) || 'MB avg peak' as memory_usage,
  AVG(avg_cpu_usage_percent) || '% avg' as cpu_usage,
  SUM(total_errors) as errors,
  SUM(total_warnings) as warnings,
  SUM(validation_failures) as validation_issues,

  CASE 
    WHEN AVG(success_rate) > 99 THEN 'excellent_historical_performance'
    WHEN AVG(success_rate) > 95 THEN 'good_historical_performance'
    ELSE 'performance_issues_detected'
  END as health_status,

  COUNT(*) || ' previous migrations' as recent_throughput,
  'Historical performance baseline' as recommendation,
  'Use for future migration planning' as next_action

FROM migration_history
WHERE migration_completed_at >= CURRENT_DATE - INTERVAL '6 months'
  AND collection_name = 'users'
GROUP BY collection_name;

-- QueryLeaf schema evolution capabilities:
-- 1. SQL-familiar schema version definition with comprehensive validation rules
-- 2. Automated migration plan generation with performance optimization
-- 3. Advanced batch processing configuration based on collection size and complexity
-- 4. Real-time migration monitoring with progress tracking and performance analytics
-- 5. Comprehensive safety checks including automatic rollback and validation testing
-- 6. Backward compatibility management with deprecated field handling
-- 7. Resource utilization monitoring and optimization recommendations
-- 8. Historical performance analysis for migration planning and optimization
-- 9. Enterprise-grade error handling and recovery mechanisms
-- 10. Integration with MongoDB's native document flexibility while maintaining SQL familiarity