MongoDB TTL Collections and Automatic Data Lifecycle Management: Intelligent Data Expiration and Cleanup for Scalable Applications

-- Traditional PostgreSQL data cleanup approach (complex and resource-intensive)

-- Example: Managing session data with manual cleanup procedures
CREATE TABLE user_sessions (
  session_id VARCHAR(128) PRIMARY KEY,
  user_id BIGINT NOT NULL,
  session_data JSONB,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  last_accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_sessions_expires_at ON user_sessions(expires_at);
CREATE INDEX idx_sessions_last_accessed ON user_sessions(last_accessed_at);

-- Manual cleanup procedure (requires scheduled execution)
CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
  deleted_count INTEGER;
BEGIN
  -- Delete expired sessions in batches to avoid long locks
  WITH expired_sessions AS (
    SELECT session_id 
    FROM user_sessions 
    WHERE expires_at < NOW()
    LIMIT 10000  -- Batch processing to prevent lock contention
  )
  DELETE FROM user_sessions 
  WHERE session_id IN (SELECT session_id FROM expired_sessions);

  GET DIAGNOSTICS deleted_count = ROW_COUNT;

  RAISE NOTICE 'Deleted % expired sessions', deleted_count;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Schedule cleanup job (requires external cron or scheduler)
-- This must be configured outside the database:
-- 0 */6 * * * psql -d myapp -c "SELECT cleanup_expired_sessions();"

-- Problems with manual cleanup approach:
-- 1. Requires external scheduling and monitoring systems
-- 2. Batch processing creates inconsistent cleanup timing
-- 3. Resource-intensive operations during cleanup windows
-- 4. Risk of cleanup failure without proper monitoring
-- 5. Complex coordination across multiple tables and relationships
-- 6. Difficult to optimize cleanup performance vs. application performance

-- Example: Log data cleanup with cascading complexity
CREATE TABLE application_logs (
  log_id BIGSERIAL PRIMARY KEY,
  application_id INTEGER NOT NULL,
  severity_level VARCHAR(10) NOT NULL,
  message TEXT NOT NULL,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

  -- Manual retention policy management
  retention_category VARCHAR(20) DEFAULT 'standard', -- 'critical', 'standard', 'debug'
  retention_expires_at TIMESTAMP WITH TIME ZONE
);

-- Complex trigger for setting retention dates
CREATE OR REPLACE FUNCTION set_log_retention_date()
RETURNS TRIGGER AS $$
BEGIN
  NEW.retention_expires_at := CASE NEW.retention_category
    WHEN 'critical' THEN NEW.created_at + INTERVAL '2 years'
    WHEN 'standard' THEN NEW.created_at + INTERVAL '6 months'
    WHEN 'debug' THEN NEW.created_at + INTERVAL '7 days'
    ELSE NEW.created_at + INTERVAL '30 days'
  END;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_log_retention 
  BEFORE INSERT OR UPDATE ON application_logs
  FOR EACH ROW 
  EXECUTE FUNCTION set_log_retention_date();

-- Complex cleanup with retention policy handling
CREATE OR REPLACE FUNCTION cleanup_application_logs()
RETURNS TABLE(retention_category VARCHAR, deleted_count BIGINT) AS $$
DECLARE
  category VARCHAR;
  del_count BIGINT;
BEGIN
  -- Process each retention category separately
  FOR category IN SELECT DISTINCT l.retention_category FROM application_logs l LOOP
    WITH expired_logs AS (
      SELECT log_id
      FROM application_logs
      WHERE retention_category = category 
        AND retention_expires_at < NOW()
      LIMIT 50000  -- Large batch size for logs
    )
    DELETE FROM application_logs 
    WHERE log_id IN (SELECT log_id FROM expired_logs);

    GET DIAGNOSTICS del_count = ROW_COUNT;

    RETURN QUERY SELECT category, del_count;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Traditional approach limitations:
-- 1. Complex stored procedure logic for different retention policies
-- 2. Risk of cleanup procedures failing and accumulating stale data
-- 3. Performance impact during cleanup operations
-- 4. Difficult to test and validate cleanup logic
-- 5. Manual coordination required for related table cleanup
-- 6. No atomic cleanup guarantees across related documents
-- 7. Resource contention between cleanup and application operations

-- Example: MySQL cleanup with limited capabilities
CREATE TABLE mysql_cache_entries (
  cache_key VARCHAR(255) PRIMARY KEY,
  cache_value LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NOT NULL,

  INDEX idx_expires_at (expires_at)
);

-- MySQL cleanup event (requires event scheduler enabled)
DELIMITER ;;
CREATE EVENT cleanup_cache_entries
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
  -- Simple cleanup with limited error handling
  DELETE FROM mysql_cache_entries 
  WHERE expires_at < NOW();
END;;
DELIMITER ;

-- MySQL limitations:
-- 1. Basic event scheduler with limited scheduling options
-- 2. No sophisticated batch processing or resource management
-- 3. Limited error handling and monitoring capabilities
-- 4. Event scheduler can be disabled accidentally
-- 5. No built-in support for complex retention policies
-- 6. Cleanup operations can block other database operations
-- 7. No automatic optimization for cleanup performance

-- Oracle approach with job scheduling
CREATE TABLE oracle_temp_data (
  temp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  data_content CLOB,
  created_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  expiry_date TIMESTAMP NOT NULL
);

-- Oracle job for cleanup (complex setup required)
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CLEANUP_TEMP_DATA_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          DELETE FROM oracle_temp_data 
                          WHERE expiry_date < SYSTIMESTAMP;
                          COMMIT;
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
    enabled         => TRUE
  );
END;

-- Oracle complexity issues:
-- 1. Requires DBMS_SCHEDULER privileges and configuration
-- 2. Job management complexity and monitoring requirements  
-- 3. Manual transaction management in cleanup procedures
-- 4. Complex scheduling syntax and limited flexibility
-- 5. Jobs can fail silently without proper monitoring
-- 6. Resource management and performance tuning required
-- 7. Expensive licensing for advanced job scheduling features

// MongoDB TTL Collections - native automatic document expiration and lifecycle management

const { MongoClient } = require('mongodb');

// Comprehensive MongoDB TTL and Data Lifecycle Management System
class MongoDBTTLManager {
  constructor(db) {
    this.db = db;
    this.ttlCollections = new Map();
    this.lifecyclePolicies = new Map();
    this.expirationStats = {
      documentsExpired: 0,
      storageReclaimed: 0,
      lastCleanupRun: null
    };
    this.ttlIndexSpecs = new Map();
  }

  // Create collection with automatic TTL expiration
  async createTTLCollection(collectionName, ttlConfig) {
    console.log(`Creating TTL collection: ${collectionName}`);

    const {
      ttlField = 'expiresAt',
      expireAfterSeconds = null,
      indexOnCreatedAt = false,
      additionalIndexes = [],
      validationSchema = null
    } = ttlConfig;

    try {
      // Create collection with optional validation
      const collectionOptions = {};
      if (validationSchema) {
        collectionOptions.validator = validationSchema;
        collectionOptions.validationLevel = 'strict';
      }

      await this.db.createCollection(collectionName, collectionOptions);
      const collection = this.db.collection(collectionName);

      // Create TTL index for automatic expiration
      if (expireAfterSeconds !== null) {
        // TTL index with expireAfterSeconds for automatic cleanup
        await collection.createIndex(
          { [ttlField]: 1 },
          { 
            expireAfterSeconds: expireAfterSeconds,
            background: true,
            name: `ttl_${ttlField}_${expireAfterSeconds}`
          }
        );

        console.log(`Created TTL index on ${ttlField} with expiration: ${expireAfterSeconds} seconds`);
      } else {
        // TTL index on Date field for document-specific expiration
        await collection.createIndex(
          { [ttlField]: 1 },
          {
            expireAfterSeconds: 0, // Documents expire based on date value
            background: true,
            name: `ttl_${ttlField}_document_specific`
          }
        );

        console.log(`Created document-specific TTL index on ${ttlField}`);
      }

      // Optional index on created timestamp for queries
      if (indexOnCreatedAt) {
        await collection.createIndex(
          { createdAt: 1 },
          { background: true, name: 'idx_created_at' }
        );
      }

      // Create additional indexes as specified
      for (const indexSpec of additionalIndexes) {
        await collection.createIndex(
          indexSpec.fields,
          { background: true, ...indexSpec.options }
        );
      }

      // Store TTL configuration for reference
      this.ttlCollections.set(collectionName, {
        ttlField: ttlField,
        expireAfterSeconds: expireAfterSeconds,
        collection: collection,
        createdAt: new Date(),
        config: ttlConfig
      });

      this.ttlIndexSpecs.set(collectionName, {
        field: ttlField,
        expireAfterSeconds: expireAfterSeconds,
        indexName: expireAfterSeconds !== null ? 
          `ttl_${ttlField}_${expireAfterSeconds}` : 
          `ttl_${ttlField}_document_specific`
      });

      console.log(`TTL collection ${collectionName} created successfully`);
      return collection;

    } catch (error) {
      console.error(`Failed to create TTL collection ${collectionName}:`, error);
      throw error;
    }
  }

  // Session management with automatic cleanup
  async createSessionsCollection() {
    console.log('Creating user sessions collection with TTL');

    const sessionValidation = {
      $jsonSchema: {
        bsonType: 'object',
        required: ['sessionId', 'userId', 'createdAt', 'expiresAt'],
        properties: {
          sessionId: {
            bsonType: 'string',
            minLength: 32,
            maxLength: 128,
            description: 'Unique session identifier'
          },
          userId: {
            bsonType: 'objectId',
            description: 'Reference to user document'
          },
          sessionData: {
            bsonType: ['object', 'null'],
            description: 'Session-specific data'
          },
          ipAddress: {
            bsonType: ['string', 'null'],
            pattern: '^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$|^(?:[0-9a-fA-F]{1,4}:){7}[0-9a-fA-F]{1,4}$',
            description: 'Client IP address'
          },
          userAgent: {
            bsonType: ['string', 'null'],
            maxLength: 500,
            description: 'Client user agent'
          },
          createdAt: {
            bsonType: 'date',
            description: 'Session creation timestamp'
          },
          lastAccessedAt: {
            bsonType: 'date', 
            description: 'Last session access timestamp'
          },
          expiresAt: {
            bsonType: 'date',
            description: 'Session expiration timestamp for TTL'
          },
          isActive: {
            bsonType: 'bool',
            description: 'Session active status'
          }
        }
      }
    };

    await this.createTTLCollection('userSessions', {
      ttlField: 'expiresAt',
      expireAfterSeconds: 0, // Document-specific expiration
      indexOnCreatedAt: true,
      validationSchema: sessionValidation,
      additionalIndexes: [
        {
          fields: { sessionId: 1 },
          options: { unique: true, name: 'idx_session_id' }
        },
        {
          fields: { userId: 1, isActive: 1 },
          options: { name: 'idx_user_active_sessions' }
        },
        {
          fields: { lastAccessedAt: -1 },
          options: { name: 'idx_last_accessed' }
        }
      ]
    });

    return this.db.collection('userSessions');
  }

  // Create optimized logging collection with retention policies
  async createLoggingCollection() {
    console.log('Creating application logs collection with TTL');

    const logValidation = {
      $jsonSchema: {
        bsonType: 'object',
        required: ['level', 'message', 'timestamp', 'source'],
        properties: {
          level: {
            enum: ['debug', 'info', 'warn', 'error', 'fatal'],
            description: 'Log severity level'
          },
          message: {
            bsonType: 'string',
            maxLength: 10000,
            description: 'Log message content'
          },
          source: {
            bsonType: 'string',
            maxLength: 100,
            description: 'Log source component'
          },
          timestamp: {
            bsonType: 'date',
            description: 'Log entry timestamp'
          },
          metadata: {
            bsonType: ['object', 'null'],
            description: 'Additional log metadata'
          },
          userId: {
            bsonType: ['objectId', 'null'],
            description: 'Associated user ID if applicable'
          },
          requestId: {
            bsonType: ['string', 'null'],
            description: 'Request correlation ID'
          },
          retentionCategory: {
            enum: ['debug', 'standard', 'audit', 'critical'],
            description: 'Data retention classification'
          },
          tags: {
            bsonType: ['array', 'null'],
            items: { bsonType: 'string' },
            description: 'Searchable tags'
          }
        }
      }
    };

    // Create multiple collections for different retention periods
    const retentionPolicies = [
      { category: 'debug', days: 7 },
      { category: 'standard', days: 90 },
      { category: 'audit', days: 365 },
      { category: 'critical', days: 2555 } // 7 years
    ];

    for (const policy of retentionPolicies) {
      const collectionName = `applicationLogs_${policy.category}`;

      await this.createTTLCollection(collectionName, {
        ttlField: 'timestamp',
        expireAfterSeconds: policy.days * 24 * 60 * 60, // Convert days to seconds
        indexOnCreatedAt: false,
        validationSchema: logValidation,
        additionalIndexes: [
          {
            fields: { level: 1, timestamp: -1 },
            options: { name: 'idx_level_timestamp' }
          },
          {
            fields: { source: 1, timestamp: -1 },
            options: { name: 'idx_source_timestamp' }
          },
          {
            fields: { userId: 1, timestamp: -1 },
            options: { name: 'idx_user_timestamp', sparse: true }
          },
          {
            fields: { requestId: 1 },
            options: { name: 'idx_request_id', sparse: true }
          },
          {
            fields: { tags: 1 },
            options: { name: 'idx_tags', sparse: true }
          }
        ]
      });
    }

    this.lifecyclePolicies.set('applicationLogs', retentionPolicies);
    return retentionPolicies.map(p => ({ 
      category: p.category, 
      collection: this.db.collection(`applicationLogs_${p.category}`) 
    }));
  }

  // Cache collection with flexible TTL
  async createCacheCollection() {
    console.log('Creating cache collection with TTL');

    const cacheValidation = {
      $jsonSchema: {
        bsonType: 'object',
        required: ['key', 'value', 'createdAt'],
        properties: {
          key: {
            bsonType: 'string',
            maxLength: 500,
            description: 'Cache key identifier'
          },
          value: {
            description: 'Cached data value (any type)'
          },
          createdAt: {
            bsonType: 'date',
            description: 'Cache entry creation time'
          },
          expiresAt: {
            bsonType: ['date', 'null'],
            description: 'Optional specific expiration time'
          },
          namespace: {
            bsonType: ['string', 'null'],
            maxLength: 100,
            description: 'Cache namespace for organization'
          },
          tags: {
            bsonType: ['array', 'null'],
            items: { bsonType: 'string' },
            description: 'Cache entry tags'
          },
          size: {
            bsonType: ['int', 'null'],
            minimum: 0,
            description: 'Cached data size in bytes'
          },
          hitCount: {
            bsonType: 'int',
            minimum: 0,
            description: 'Number of times cache entry was accessed'
          },
          lastAccessedAt: {
            bsonType: 'date',
            description: 'Last access timestamp'
          }
        }
      }
    };

    await this.createTTLCollection('cache', {
      ttlField: 'createdAt',
      expireAfterSeconds: 3600, // Default 1 hour expiration
      indexOnCreatedAt: false,
      validationSchema: cacheValidation,
      additionalIndexes: [
        {
          fields: { key: 1 },
          options: { unique: true, name: 'idx_cache_key' }
        },
        {
          fields: { namespace: 1, createdAt: -1 },
          options: { name: 'idx_namespace_created', sparse: true }
        },
        {
          fields: { tags: 1 },
          options: { name: 'idx_cache_tags', sparse: true }
        },
        {
          fields: { lastAccessedAt: -1 },
          options: { name: 'idx_last_accessed' }
        },
        {
          fields: { expiresAt: 1 },
          options: { 
            name: 'ttl_expires_at_custom',
            expireAfterSeconds: 0, // Custom expiration times
            sparse: true 
          }
        }
      ]
    });

    return this.db.collection('cache');
  }

  // Temporary data collection for processing workflows
  async createTempDataCollection() {
    console.log('Creating temporary data collection with short TTL');

    const tempDataValidation = {
      $jsonSchema: {
        bsonType: 'object',
        required: ['workflowId', 'data', 'createdAt', 'status'],
        properties: {
          workflowId: {
            bsonType: 'string',
            maxLength: 100,
            description: 'Workflow identifier'
          },
          stepId: {
            bsonType: ['string', 'null'],
            maxLength: 100,
            description: 'Workflow step identifier'
          },
          data: {
            description: 'Temporary processing data'
          },
          status: {
            enum: ['pending', 'processing', 'completed', 'failed'],
            description: 'Processing status'
          },
          createdAt: {
            bsonType: 'date',
            description: 'Creation timestamp'
          },
          processedAt: {
            bsonType: ['date', 'null'],
            description: 'Processing completion timestamp'
          },
          priority: {
            bsonType: 'int',
            minimum: 1,
            maximum: 10,
            description: 'Processing priority level'
          },
          retryCount: {
            bsonType: 'int',
            minimum: 0,
            description: 'Number of retry attempts'
          },
          errorMessage: {
            bsonType: ['string', 'null'],
            maxLength: 1000,
            description: 'Error message if processing failed'
          }
        }
      }
    };

    await this.createTTLCollection('tempProcessingData', {
      ttlField: 'createdAt',
      expireAfterSeconds: 86400, // 24 hours
      indexOnCreatedAt: false,
      validationSchema: tempDataValidation,
      additionalIndexes: [
        {
          fields: { workflowId: 1, status: 1 },
          options: { name: 'idx_workflow_status' }
        },
        {
          fields: { status: 1, priority: -1, createdAt: 1 },
          options: { name: 'idx_processing_queue' }
        },
        {
          fields: { stepId: 1 },
          options: { name: 'idx_step_id', sparse: true }
        }
      ]
    });

    return this.db.collection('tempProcessingData');
  }

  // Insert documents with intelligent expiration management
  async insertWithTTL(collectionName, documents, ttlOptions = {}) {
    console.log(`Inserting ${Array.isArray(documents) ? documents.length : 1} documents with TTL into ${collectionName}`);

    const collection = this.db.collection(collectionName);
    const ttlConfig = this.ttlCollections.get(collectionName);

    if (!ttlConfig) {
      throw new Error(`Collection ${collectionName} is not configured for TTL`);
    }

    const documentsToInsert = Array.isArray(documents) ? documents : [documents];
    const currentTime = new Date();

    // Process each document to set appropriate expiration
    const processedDocuments = documentsToInsert.map(doc => {
      const processedDoc = { ...doc };

      // Set creation timestamp if not present
      if (!processedDoc.createdAt) {
        processedDoc.createdAt = currentTime;
      }

      // Handle TTL field based on collection configuration
      if (ttlConfig.expireAfterSeconds === 0) {
        // Document-specific expiration - use provided or calculate
        if (!processedDoc[ttlConfig.ttlField]) {
          const customTTL = ttlOptions.customExpireAfterSeconds || 
                           ttlOptions.expireAfterSeconds ||
                           3600; // Default 1 hour

          processedDoc[ttlConfig.ttlField] = new Date(
            currentTime.getTime() + (customTTL * 1000)
          );
        }
      } else {
        // Fixed expiration - set TTL field to current time for consistent expiration
        if (!processedDoc[ttlConfig.ttlField]) {
          processedDoc[ttlConfig.ttlField] = currentTime;
        }
      }

      // Add metadata for tracking
      processedDoc._ttl_configured = true;
      processedDoc._ttl_field = ttlConfig.ttlField;
      processedDoc._ttl_policy = ttlConfig.expireAfterSeconds === 0 ? 'document_specific' : 'collection_fixed';

      return processedDoc;
    });

    try {
      const result = Array.isArray(documents) ? 
        await collection.insertMany(processedDocuments) :
        await collection.insertOne(processedDocuments[0]);

      console.log(`Successfully inserted documents with TTL configuration`);
      return result;

    } catch (error) {
      console.error(`Failed to insert documents with TTL:`, error);
      throw error;
    }
  }

  // Update TTL configuration for existing collections
  async modifyTTLExpiration(collectionName, newExpireAfterSeconds) {
    console.log(`Modifying TTL expiration for ${collectionName} to ${newExpireAfterSeconds} seconds`);

    const ttlConfig = this.ttlCollections.get(collectionName);
    if (!ttlConfig) {
      throw new Error(`Collection ${collectionName} is not configured for TTL`);
    }

    const indexSpec = this.ttlIndexSpecs.get(collectionName);

    try {
      // Use collMod command to change TTL expiration
      await this.db.runCommand({
        collMod: collectionName,
        index: {
          keyPattern: { [ttlConfig.ttlField]: 1 },
          expireAfterSeconds: newExpireAfterSeconds
        }
      });

      // Update our tracking
      ttlConfig.expireAfterSeconds = newExpireAfterSeconds;
      indexSpec.expireAfterSeconds = newExpireAfterSeconds;

      console.log(`TTL expiration updated successfully for ${collectionName}`);
      return { success: true, newExpiration: newExpireAfterSeconds };

    } catch (error) {
      console.error(`Failed to modify TTL expiration:`, error);
      throw error;
    }
  }

  // Monitor TTL collection statistics and performance
  async getTTLStatistics() {
    console.log('Gathering TTL collection statistics...');

    const statistics = {
      collections: new Map(),
      summary: {
        totalCollections: this.ttlCollections.size,
        totalDocuments: 0,
        estimatedStorageSize: 0,
        oldestDocument: null,
        newestDocument: null
      }
    };

    for (const [collectionName, config] of this.ttlCollections) {
      try {
        const collection = config.collection;
        const stats = await collection.stats();

        // Get sample documents for age analysis
        const oldestDoc = await collection.findOne(
          {},
          { sort: { [config.ttlField]: 1 } }
        );

        const newestDoc = await collection.findOne(
          {},
          { sort: { [config.ttlField]: -1 } }
        );

        // Calculate expiration statistics
        const now = new Date();
        let documentsExpiringSoon = 0;

        if (config.expireAfterSeconds === 0) {
          // Document-specific expiration
          documentsExpiringSoon = await collection.countDocuments({
            [config.ttlField]: {
              $lte: new Date(now.getTime() + (3600 * 1000)) // Next hour
            }
          });
        } else {
          // Fixed expiration
          const cutoffTime = new Date(now.getTime() - (config.expireAfterSeconds - 3600) * 1000);
          documentsExpiringSoon = await collection.countDocuments({
            [config.ttlField]: { $lte: cutoffTime }
          });
        }

        const collectionStats = {
          name: collectionName,
          documentCount: stats.count,
          storageSize: stats.storageSize,
          indexSize: stats.totalIndexSize,
          averageDocumentSize: stats.avgObjSize,
          ttlField: config.ttlField,
          expireAfterSeconds: config.expireAfterSeconds,
          expirationPolicy: config.expireAfterSeconds === 0 ? 'document_specific' : 'collection_fixed',
          oldestDocument: oldestDoc ? oldestDoc[config.ttlField] : null,
          newestDocument: newestDoc ? newestDoc[config.ttlField] : null,
          documentsExpiringSoon: documentsExpiringSoon,
          createdAt: config.createdAt
        };

        statistics.collections.set(collectionName, collectionStats);
        statistics.summary.totalDocuments += stats.count;
        statistics.summary.estimatedStorageSize += stats.storageSize;

        if (!statistics.summary.oldestDocument || 
            (oldestDoc && oldestDoc[config.ttlField] < statistics.summary.oldestDocument)) {
          statistics.summary.oldestDocument = oldestDoc ? oldestDoc[config.ttlField] : null;
        }

        if (!statistics.summary.newestDocument || 
            (newestDoc && newestDoc[config.ttlField] > statistics.summary.newestDocument)) {
          statistics.summary.newestDocument = newestDoc ? newestDoc[config.ttlField] : null;
        }

      } catch (error) {
        console.warn(`Could not gather statistics for ${collectionName}:`, error.message);
      }
    }

    return statistics;
  }

  // Advanced TTL management and monitoring
  async performTTLHealthCheck() {
    console.log('Performing comprehensive TTL health check...');

    const healthCheck = {
      status: 'healthy',
      issues: [],
      recommendations: [],
      collections: new Map(),
      summary: {
        totalCollections: this.ttlCollections.size,
        healthyCollections: 0,
        collectionsWithIssues: 0,
        totalDocuments: 0
      }
    };

    for (const [collectionName, config] of this.ttlCollections) {
      const collectionHealth = {
        name: collectionName,
        status: 'healthy',
        issues: [],
        recommendations: [],
        metrics: {}
      };

      try {
        const collection = config.collection;
        const stats = await collection.stats();

        // Check for orphaned documents (shouldn't exist with proper TTL)
        const now = new Date();
        let expiredDocuments = 0;

        if (config.expireAfterSeconds === 0) {
          expiredDocuments = await collection.countDocuments({
            [config.ttlField]: { $lte: new Date(now.getTime() - 300000) } // 5 minutes ago
          });
        } else {
          const expiredCutoff = new Date(now.getTime() - config.expireAfterSeconds * 1000 - 300000);
          expiredDocuments = await collection.countDocuments({
            [config.ttlField]: { $lte: expiredCutoff }
          });
        }

        collectionHealth.metrics = {
          documentCount: stats.count,
          storageSize: stats.storageSize,
          indexCount: stats.nindexes,
          expiredDocuments: expiredDocuments
        };

        // Analyze potential issues
        if (expiredDocuments > 0) {
          collectionHealth.status = 'warning';
          collectionHealth.issues.push(`Found ${expiredDocuments} documents that should have expired`);
          collectionHealth.recommendations.push('Monitor TTL background task performance');
        }

        if (stats.count > 1000000) {
          collectionHealth.recommendations.push('Consider partitioning strategy for large collections');
        }

        if (stats.storageSize > 1073741824) { // 1GB
          collectionHealth.recommendations.push('Monitor storage usage and consider shorter retention periods');
        }

        // Check index health
        const indexes = await collection.indexes();
        const ttlIndex = indexes.find(idx => 
          idx.expireAfterSeconds !== undefined && 
          Object.keys(idx.key).includes(config.ttlField)
        );

        if (!ttlIndex) {
          collectionHealth.status = 'error';
          collectionHealth.issues.push('TTL index missing or misconfigured');
        }

        healthCheck.collections.set(collectionName, collectionHealth);
        healthCheck.summary.totalDocuments += stats.count;

        if (collectionHealth.status === 'healthy') {
          healthCheck.summary.healthyCollections++;
        } else {
          healthCheck.summary.collectionsWithIssues++;
          if (collectionHealth.status === 'error') {
            healthCheck.status = 'error';
          } else if (healthCheck.status === 'healthy') {
            healthCheck.status = 'warning';
          }
        }

      } catch (error) {
        collectionHealth.status = 'error';
        collectionHealth.issues.push(`Health check failed: ${error.message}`);
        healthCheck.status = 'error';
        healthCheck.summary.collectionsWithIssues++;
      }
    }

    // Generate overall recommendations
    if (healthCheck.summary.collectionsWithIssues > 0) {
      healthCheck.recommendations.push('Review collections with issues and optimize TTL configurations');
    }

    if (healthCheck.summary.totalDocuments > 10000000) {
      healthCheck.recommendations.push('Consider implementing data archiving strategy for historical data');
    }

    console.log(`TTL health check completed: ${healthCheck.status}`);
    return healthCheck;
  }

  // Get comprehensive TTL management report
  async generateTTLReport() {
    console.log('Generating comprehensive TTL management report...');

    const [statistics, healthCheck] = await Promise.all([
      this.getTTLStatistics(),
      this.performTTLHealthCheck()
    ]);

    const report = {
      generatedAt: new Date(),
      overview: {
        totalCollections: statistics.summary.totalCollections,
        totalDocuments: statistics.summary.totalDocuments,
        totalStorageSize: statistics.summary.estimatedStorageSize,
        healthStatus: healthCheck.status
      },
      collections: [],
      recommendations: healthCheck.recommendations,
      issues: healthCheck.issues
    };

    // Combine statistics and health data
    for (const [collectionName, stats] of statistics.collections) {
      const health = healthCheck.collections.get(collectionName);

      report.collections.push({
        name: collectionName,
        documentCount: stats.documentCount,
        storageSize: stats.storageSize,
        ttlConfiguration: {
          field: stats.ttlField,
          expireAfterSeconds: stats.expireAfterSeconds,
          policy: stats.expirationPolicy
        },
        dataAge: {
          oldest: stats.oldestDocument,
          newest: stats.newestDocument
        },
        expiration: {
          documentsExpiringSoon: stats.documentsExpiringSoon
        },
        health: {
          status: health?.status || 'unknown',
          issues: health?.issues || [],
          recommendations: health?.recommendations || []
        }
      });
    }

    console.log('TTL management report generated successfully');
    return report;
  }
}

// Example usage demonstrating comprehensive TTL management
async function demonstrateTTLOperations() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const db = client.db('ttl_management_demo');

  const ttlManager = new MongoDBTTLManager(db);

  // Create various TTL collections
  await ttlManager.createSessionsCollection();
  await ttlManager.createLoggingCollection();
  await ttlManager.createCacheCollection();
  await ttlManager.createTempDataCollection();

  // Insert sample data with TTL
  await ttlManager.insertWithTTL('userSessions', [
    {
      sessionId: 'sess_' + Math.random().toString(36).substr(2, 16),
      userId: new ObjectId(),
      sessionData: { preferences: { theme: 'dark' } },
      ipAddress: '192.168.1.100',
      userAgent: 'Mozilla/5.0...',
      lastAccessedAt: new Date(),
      isActive: true
    }
  ]);

  // Insert cache entries with custom expiration
  await ttlManager.insertWithTTL('cache', [
    {
      key: 'user_profile_12345',
      value: { name: 'John Doe', email: '[email protected]' },
      namespace: 'user_profiles',
      tags: ['profile', 'active'],
      size: 256,
      hitCount: 0,
      lastAccessedAt: new Date()
    }
  ], { customExpireAfterSeconds: 7200 }); // 2 hours

  // Generate comprehensive report
  const report = await ttlManager.generateTTLReport();
  console.log('TTL Management Report:', JSON.stringify(report, null, 2));

  await client.close();
}

// Enterprise MongoDB TTL Management with Advanced Patterns and Monitoring
class EnterpriseTTLManager extends MongoDBTTLManager {
  constructor(db, enterpriseConfig = {}) {
    super(db);

    this.enterpriseConfig = {
      enableMetrics: enterpriseConfig.enableMetrics || true,
      enableAlerting: enterpriseConfig.enableAlerting || true,
      metricsCollection: enterpriseConfig.metricsCollection || 'ttl_metrics',
      alertThresholds: {
        expiredDocumentThreshold: 1000,
        storageSizeThreshold: 5368709120, // 5GB
        healthCheckFailureThreshold: 3
      },
      ...enterpriseConfig
    };

    this.metricsHistory = [];
    this.alertHistory = [];
    this.setupEnterpriseFeatures();
  }

  async setupEnterpriseFeatures() {
    if (this.enterpriseConfig.enableMetrics) {
      await this.createMetricsCollection();
      this.startMetricsCollection();
    }
  }

  async createMetricsCollection() {
    try {
      await this.db.createCollection(this.enterpriseConfig.metricsCollection, {
        validator: {
          $jsonSchema: {
            bsonType: 'object',
            required: ['timestamp', 'collectionName', 'metrics'],
            properties: {
              timestamp: { bsonType: 'date' },
              collectionName: { bsonType: 'string' },
              metrics: {
                bsonType: 'object',
                properties: {
                  documentCount: { bsonType: 'int' },
                  storageSize: { bsonType: 'long' },
                  expiredDocuments: { bsonType: 'int' },
                  documentsExpiringSoon: { bsonType: 'int' }
                }
              }
            }
          }
        }
      });

      // TTL for metrics (keep for 30 days)
      await this.db.collection(this.enterpriseConfig.metricsCollection).createIndex(
        { timestamp: 1 },
        { expireAfterSeconds: 2592000 } // 30 days
      );

      console.log('Enterprise TTL metrics collection created');
    } catch (error) {
      console.warn('Could not create metrics collection:', error.message);
    }
  }

  async createHierarchicalTTLCollection(collectionName, ttlHierarchy) {
    console.log(`Creating hierarchical TTL collection: ${collectionName}`);

    // TTL hierarchy example: { debug: 7*24*3600, info: 30*24*3600, error: 365*24*3600 }
    const baseValidator = {
      $jsonSchema: {
        bsonType: 'object',
        required: ['level', 'data', 'timestamp'],
        properties: {
          level: {
            enum: Object.keys(ttlHierarchy),
            description: 'Data classification level'
          },
          data: { description: 'Document data' },
          timestamp: { bsonType: 'date' },
          customExpiration: {
            bsonType: ['date', 'null'],
            description: 'Override expiration time'
          }
        }
      }
    };

    await this.db.createCollection(collectionName, { validator: baseValidator });
    const collection = this.db.collection(collectionName);

    // Create multiple TTL indexes for different levels
    for (const [level, expireSeconds] of Object.entries(ttlHierarchy)) {
      await collection.createIndex(
        { level: 1, timestamp: 1 },
        {
          expireAfterSeconds: expireSeconds,
          partialFilterExpression: { level: level },
          name: `ttl_${level}_${expireSeconds}`,
          background: true
        }
      );
    }

    // Additional TTL index for custom expiration
    await collection.createIndex(
      { customExpiration: 1 },
      {
        expireAfterSeconds: 0,
        sparse: true,
        name: 'ttl_custom_expiration'
      }
    );

    return collection;
  }

  async createConditionalTTLCollection(collectionName, conditionalRules) {
    console.log(`Creating conditional TTL collection: ${collectionName}`);

    // Conditional rules example:
    // [
    //   { condition: { status: 'completed' }, expireAfterSeconds: 86400 },
    //   { condition: { status: 'failed' }, expireAfterSeconds: 604800 },
    //   { condition: { priority: 'high' }, expireAfterSeconds: 2592000 }
    // ]

    await this.db.createCollection(collectionName);
    const collection = this.db.collection(collectionName);

    // Create conditional TTL indexes
    for (const [index, rule] of conditionalRules.entries()) {
      await collection.createIndex(
        { createdAt: 1, ...rule.condition },
        {
          expireAfterSeconds: rule.expireAfterSeconds,
          partialFilterExpression: rule.condition,
          name: `ttl_conditional_${index}`,
          background: true
        }
      );
    }

    return collection;
  }

  startMetricsCollection() {
    if (!this.enterpriseConfig.enableMetrics) return;

    // Collect metrics every 5 minutes
    setInterval(async () => {
      try {
        await this.collectAndStoreMetrics();
      } catch (error) {
        console.error('Failed to collect TTL metrics:', error);
      }
    }, 300000); // 5 minutes

    console.log('TTL metrics collection started');
  }

  async collectAndStoreMetrics() {
    const metricsCollection = this.db.collection(this.enterpriseConfig.metricsCollection);
    const timestamp = new Date();

    for (const [collectionName, config] of this.ttlCollections) {
      try {
        const collection = config.collection;
        const stats = await collection.stats();

        // Calculate expired documents
        const now = new Date();
        let expiredDocuments = 0;
        let documentsExpiringSoon = 0;

        if (config.expireAfterSeconds === 0) {
          expiredDocuments = await collection.countDocuments({
            [config.ttlField]: { $lte: new Date(now.getTime() - 300000) }
          });
          documentsExpiringSoon = await collection.countDocuments({
            [config.ttlField]: {
              $lte: new Date(now.getTime() + 3600000),
              $gt: now
            }
          });
        } else {
          const expiredCutoff = new Date(now.getTime() - config.expireAfterSeconds * 1000 - 300000);
          expiredDocuments = await collection.countDocuments({
            [config.ttlField]: { $lte: expiredCutoff }
          });
        }

        const metrics = {
          timestamp: timestamp,
          collectionName: collectionName,
          metrics: {
            documentCount: stats.count,
            storageSize: stats.storageSize,
            indexSize: stats.totalIndexSize,
            expiredDocuments: expiredDocuments,
            documentsExpiringSoon: documentsExpiringSoon
          }
        };

        await metricsCollection.insertOne(metrics);

        // Check for alert conditions
        await this.checkAlertConditions(collectionName, metrics.metrics);

      } catch (error) {
        console.error(`Failed to collect metrics for ${collectionName}:`, error);
      }
    }
  }

  async checkAlertConditions(collectionName, metrics) {
    const alerts = [];
    const thresholds = this.enterpriseConfig.alertThresholds;

    if (metrics.expiredDocuments > thresholds.expiredDocumentThreshold) {
      alerts.push({
        severity: 'warning',
        message: `Collection ${collectionName} has ${metrics.expiredDocuments} expired documents`,
        metric: 'expired_documents',
        value: metrics.expiredDocuments,
        threshold: thresholds.expiredDocumentThreshold
      });
    }

    if (metrics.storageSize > thresholds.storageSizeThreshold) {
      alerts.push({
        severity: 'warning',
        message: `Collection ${collectionName} storage size ${metrics.storageSize} exceeds threshold`,
        metric: 'storage_size',
        value: metrics.storageSize,
        threshold: thresholds.storageSizeThreshold
      });
    }

    if (alerts.length > 0 && this.enterpriseConfig.enableAlerting) {
      await this.processAlerts(collectionName, alerts);
    }
  }

  async processAlerts(collectionName, alerts) {
    for (const alert of alerts) {
      console.warn(`TTL Alert - ${alert.severity.toUpperCase()}: ${alert.message}`);

      this.alertHistory.push({
        timestamp: new Date(),
        collectionName: collectionName,
        alert: alert
      });
    }
  }
}