MongoDB Data Archiving and Lifecycle Management: Automated Retention Policies and Enterprise-Grade Data Governance

-- Traditional PostgreSQL data archiving - complex manual processes and limited automation

-- Complex partitioned table structure for lifecycle management
CREATE TABLE customer_interactions (
    interaction_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    interaction_type VARCHAR(50) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    interaction_data JSONB,
    interaction_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Compliance and governance fields
    data_classification VARCHAR(20) DEFAULT 'internal',
    retention_category VARCHAR(50) DEFAULT 'standard',
    compliance_flags JSONB,

    -- Manual archiving tracking
    archived_status VARCHAR(20) DEFAULT 'active',
    archive_eligible_date DATE,
    archive_priority INTEGER DEFAULT 5,

    -- Audit trail for lifecycle events
    lifecycle_events JSONB DEFAULT '[]',

    -- Performance optimization
    created_date DATE GENERATED ALWAYS AS (interaction_timestamp::date) STORED

) PARTITION BY RANGE (created_date);

-- Create monthly partitions (requires constant manual maintenance)
CREATE TABLE customer_interactions_2023_01 PARTITION OF customer_interactions
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE customer_interactions_2023_02 PARTITION OF customer_interactions
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE customer_interactions_2023_03 PARTITION OF customer_interactions
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
-- ... manual partition creation continues indefinitely

-- Complex stored procedure for manual archiving process
CREATE OR REPLACE FUNCTION archive_old_customer_interactions(
    archive_threshold_days INTEGER DEFAULT 365,
    batch_size INTEGER DEFAULT 1000
) RETURNS TABLE (
    processed_count INTEGER,
    archived_count INTEGER,
    deleted_count INTEGER,
    error_count INTEGER,
    processing_summary JSONB
) AS $$
DECLARE
    cutoff_date DATE := CURRENT_DATE - INTERVAL '1 day' * archive_threshold_days;
    batch_record RECORD;
    processed_total INTEGER := 0;
    archived_total INTEGER := 0;
    deleted_total INTEGER := 0;
    error_total INTEGER := 0;
    current_partition TEXT;
    archive_table_name TEXT;
    batch_cursor CURSOR FOR
        SELECT schemaname, tablename 
        FROM pg_tables 
        WHERE tablename LIKE 'customer_interactions_____'
        AND tablename < 'customer_interactions_' || to_char(cutoff_date, 'YYYY_MM')
        ORDER BY tablename;
BEGIN
    -- Process each partition individually (extremely inefficient)
    FOR batch_record IN batch_cursor LOOP
        current_partition := batch_record.schemaname || '.' || batch_record.tablename;
        archive_table_name := 'archive_' || batch_record.tablename;

        BEGIN
            -- Create archive table if it doesn't exist
            EXECUTE format('
                CREATE TABLE IF NOT EXISTS %I (
                    LIKE %I INCLUDING ALL
                ) INHERITS (customer_interactions_archive)', 
                archive_table_name, current_partition);

            -- Copy data to archive table with complex validation
            EXECUTE format('
                WITH archive_candidates AS (
                    SELECT *,
                        -- Complex compliance validation
                        CASE 
                            WHEN data_classification = ''confidential'' AND 
                                 CURRENT_DATE - created_date > INTERVAL ''7 years'' THEN ''expired_confidential''
                            WHEN data_classification = ''public'' AND 
                                 CURRENT_DATE - created_date > INTERVAL ''3 years'' THEN ''expired_public''
                            WHEN compliance_flags ? ''gdpr_subject'' AND 
                                 CURRENT_DATE - created_date > INTERVAL ''6 years'' THEN ''gdpr_expired''
                            WHEN compliance_flags ? ''financial_record'' AND 
                                 CURRENT_DATE - created_date > INTERVAL ''7 years'' THEN ''financial_expired''
                            ELSE ''active''
                        END as archive_status
                    FROM %I
                    WHERE created_date < %L
                ),
                archive_insertions AS (
                    INSERT INTO %I 
                    SELECT 
                        ac.*,
                        -- Add archiving metadata
                        ac.lifecycle_events || jsonb_build_array(
                            jsonb_build_object(
                                ''event'', ''archived'',
                                ''timestamp'', CURRENT_TIMESTAMP,
                                ''archive_reason'', ac.archive_status,
                                ''archive_batch'', %L
                            )
                        ) as lifecycle_events
                    FROM archive_candidates ac
                    WHERE ac.archive_status != ''active''
                    RETURNING interaction_id
                )
                SELECT COUNT(*) FROM archive_insertions',
                current_partition, cutoff_date, archive_table_name, 
                'batch_' || extract(epoch from now())::text
            ) INTO archived_total;

            processed_total := processed_total + archived_total;

            -- Delete archived records from active table (risky operation)
            EXECUTE format('
                DELETE FROM %I 
                WHERE created_date < %L 
                AND interaction_id IN (
                    SELECT interaction_id FROM %I 
                    WHERE archive_status != ''active''
                )', current_partition, cutoff_date, archive_table_name);

            GET DIAGNOSTICS deleted_total = ROW_COUNT;

            -- Log archiving operation
            INSERT INTO archiving_audit_log (
                table_name, 
                archive_date, 
                records_archived, 
                records_deleted,
                archive_table_name
            ) VALUES (
                current_partition, 
                CURRENT_TIMESTAMP, 
                archived_total, 
                deleted_total,
                archive_table_name
            );

        EXCEPTION WHEN OTHERS THEN
            error_total := error_total + 1;
            INSERT INTO archiving_error_log (
                table_name,
                error_message,
                error_timestamp,
                sqlstate
            ) VALUES (
                current_partition,
                SQLERRM,
                CURRENT_TIMESTAMP,
                SQLSTATE
            );
        END;
    END LOOP;

    RETURN QUERY SELECT 
        processed_total,
        archived_total,
        deleted_total,
        error_total,
        jsonb_build_object(
            'processing_timestamp', CURRENT_TIMESTAMP,
            'archive_threshold_days', archive_threshold_days,
            'batch_size', batch_size,
            'cutoff_date', cutoff_date
        );
END;
$$ LANGUAGE plpgsql;

-- Complex compliance-aware data retention management
WITH data_classification_rules AS (
    SELECT 
        'confidential' as classification,
        ARRAY['financial_record', 'personal_data', 'health_info'] as compliance_tags,
        7 * 365 as retention_days,
        true as encryption_required,
        'secure_deletion' as deletion_method
    UNION ALL
    SELECT 
        'internal' as classification,
        ARRAY['business_record', 'operational_data'] as compliance_tags,
        5 * 365 as retention_days,
        false as encryption_required,
        'standard_deletion' as deletion_method
    UNION ALL
    SELECT 
        'public' as classification,
        ARRAY['marketing_data', 'public_interaction'] as compliance_tags,
        3 * 365 as retention_days,
        false as encryption_required,
        'standard_deletion' as deletion_method
),
retention_analysis AS (
    SELECT 
        ci.interaction_id,
        ci.customer_id,
        ci.data_classification,
        ci.compliance_flags,
        ci.created_date,

        -- Match with retention rules
        dcr.retention_days,
        dcr.encryption_required,
        dcr.deletion_method,

        -- Calculate retention status
        CASE 
            WHEN CURRENT_DATE - ci.created_date > INTERVAL '1 day' * dcr.retention_days THEN 'expired'
            WHEN CURRENT_DATE - ci.created_date > INTERVAL '1 day' * (dcr.retention_days - 30) THEN 'expiring_soon'
            ELSE 'active'
        END as retention_status,

        -- Check for legal holds
        CASE 
            WHEN EXISTS (
                SELECT 1 FROM legal_holds lh 
                WHERE lh.customer_id = ci.customer_id 
                AND lh.status = 'active'
                AND lh.hold_type && ARRAY(SELECT jsonb_array_elements_text(ci.compliance_flags))
            ) THEN 'legal_hold'
            ELSE 'normal_retention'
        END as legal_status,

        -- Complex GDPR compliance checks
        CASE 
            WHEN ci.compliance_flags ? 'gdpr_subject' THEN
                CASE 
                    WHEN EXISTS (
                        SELECT 1 FROM gdpr_deletion_requests gdr 
                        WHERE gdr.customer_id = ci.customer_id 
                        AND gdr.status = 'approved'
                    ) THEN 'gdpr_deletion_required'
                    WHEN CURRENT_DATE - ci.created_date > INTERVAL '6 years' THEN 'gdpr_retention_expired'
                    ELSE 'gdpr_compliant'
                END
            ELSE 'gdpr_not_applicable'
        END as gdpr_status

    FROM customer_interactions ci
    JOIN data_classification_rules dcr ON ci.data_classification = dcr.classification
    WHERE ci.archived_status = 'active'
),
complex_retention_actions AS (
    SELECT 
        ra.*,

        -- Determine required action
        CASE 
            WHEN ra.legal_status = 'legal_hold' THEN 'maintain_with_hold'
            WHEN ra.gdpr_status = 'gdpr_deletion_required' THEN 'immediate_deletion'
            WHEN ra.gdpr_status = 'gdpr_retention_expired' THEN 'gdpr_compliant_deletion'
            WHEN ra.retention_status = 'expired' THEN 'archive_and_purge'
            WHEN ra.retention_status = 'expiring_soon' THEN 'prepare_for_archival'
            ELSE 'no_action_required'
        END as required_action,

        -- Calculate priority
        CASE 
            WHEN ra.gdpr_status IN ('gdpr_deletion_required', 'gdpr_retention_expired') THEN 1
            WHEN ra.retention_status = 'expired' AND ra.encryption_required THEN 2
            WHEN ra.retention_status = 'expired' THEN 3
            WHEN ra.retention_status = 'expiring_soon' THEN 4
            ELSE 5
        END as action_priority,

        -- Estimate processing complexity
        CASE 
            WHEN ra.encryption_required AND ra.gdpr_status != 'gdpr_not_applicable' THEN 'high_complexity'
            WHEN ra.encryption_required OR ra.gdpr_status != 'gdpr_not_applicable' THEN 'medium_complexity'
            ELSE 'low_complexity'
        END as processing_complexity

    FROM retention_analysis ra
),
action_summary AS (
    SELECT 
        required_action,
        processing_complexity,
        action_priority,
        COUNT(*) as record_count,

        -- Group by customer to handle GDPR requests efficiently
        COUNT(DISTINCT customer_id) as affected_customers,

        -- Calculate processing estimates
        CASE processing_complexity
            WHEN 'high_complexity' THEN COUNT(*) * 5  -- 5 seconds per record
            WHEN 'medium_complexity' THEN COUNT(*) * 2  -- 2 seconds per record
            ELSE COUNT(*) * 0.5  -- 0.5 seconds per record
        END as estimated_processing_time_seconds,

        -- Group compliance requirements
        array_agg(DISTINCT data_classification) as data_classifications_affected,
        array_agg(DISTINCT gdpr_status) as gdpr_statuses,
        array_agg(DISTINCT legal_status) as legal_statuses

    FROM complex_retention_actions
    WHERE required_action != 'no_action_required'
    GROUP BY required_action, processing_complexity, action_priority
)

SELECT 
    required_action,
    processing_complexity,
    action_priority,
    record_count,
    affected_customers,
    ROUND(estimated_processing_time_seconds / 3600.0, 2) as estimated_hours,
    data_classifications_affected,
    gdpr_statuses,
    legal_statuses,

    -- Provide actionable recommendations
    CASE required_action
        WHEN 'immediate_deletion' THEN 'Execute secure deletion within 72 hours to comply with GDPR'
        WHEN 'gdpr_compliant_deletion' THEN 'Schedule deletion batch during maintenance window'
        WHEN 'archive_and_purge' THEN 'Move to cold storage then schedule purge after verification'
        WHEN 'prepare_for_archival' THEN 'Begin archival preparation and stakeholder notification'
        WHEN 'maintain_with_hold' THEN 'Maintain records due to legal hold - no action until hold lifted'
        ELSE 'Review retention policy alignment'
    END as recommended_action

FROM action_summary
ORDER BY action_priority, estimated_processing_time_seconds DESC;

-- Problems with traditional data archiving approaches:
-- 1. Manual partition management creates operational overhead and human error risk
-- 2. Complex compliance validation requires extensive custom logic and maintenance
-- 3. No automated lifecycle management - everything requires manual scheduling
-- 4. Limited integration with modern compliance frameworks (GDPR, CCPA, SOX)
-- 5. Expensive cold storage integration requires external tools and ETL processes
-- 6. Poor performance for cross-partition queries during archival operations
-- 7. Complex error handling and rollback mechanisms for failed archival operations
-- 8. No automated cost optimization based on data access patterns
-- 9. Difficult integration with cloud storage tiers and automated cost management
-- 10. Limited audit trails and compliance reporting for data governance requirements

-- Attempt at automated retention with limited PostgreSQL capabilities
CREATE OR REPLACE FUNCTION automated_retention_policy()
RETURNS void AS $$
DECLARE
    policy_record RECORD;
    retention_cursor CURSOR FOR
        SELECT 
            table_name,
            retention_days,
            archive_method,
            deletion_method
        FROM data_retention_policies
        WHERE enabled = true;
BEGIN
    -- Limited automation through basic stored procedures
    FOR policy_record IN retention_cursor LOOP
        -- Execute retention policy (basic implementation)
        EXECUTE format('
            DELETE FROM %I 
            WHERE created_date < CURRENT_DATE - INTERVAL ''%s days''
            AND archived_status = ''eligible_for_deletion''',
            policy_record.table_name,
            policy_record.retention_days
        );

        -- Log retention execution (basic logging)
        INSERT INTO retention_execution_log (
            table_name,
            execution_date,
            records_processed,
            policy_applied
        ) VALUES (
            policy_record.table_name,
            CURRENT_TIMESTAMP,
            ROW_COUNT,
            'automated_retention'
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Schedule retention policy (requires external cron job)
-- SELECT cron.schedule('retention-policy', '0 2 * * 0', 'SELECT automated_retention_policy();');

-- Traditional limitations:
-- 1. No intelligent data tiering based on access patterns
-- 2. Limited support for compliance-aware automated retention
-- 3. No integration with modern cloud storage tiers
-- 4. Complex manual processes for data lifecycle management
-- 5. Poor support for real-time compliance reporting
-- 6. Limited automation capabilities requiring external orchestration
-- 7. No built-in support for legal hold management
-- 8. Difficult integration with data governance frameworks
-- 9. No automated cost optimization or storage tier management
-- 10. Complex backup and recovery for archived data across multiple storage systems

// MongoDB Advanced Data Archiving and Lifecycle Management - automated retention with enterprise governance
const { MongoClient, ObjectId } = require('mongodb');

const client = new MongoClient('mongodb://localhost:27017');
const db = client.db('enterprise_data_governance');

// Comprehensive Data Lifecycle Management System
class AdvancedDataLifecycleManager {
  constructor(db, governanceConfig = {}) {
    this.db = db;
    this.collections = {
      customers: db.collection('customers'),
      interactions: db.collection('customer_interactions'),
      orders: db.collection('orders'),
      payments: db.collection('payments'),

      // Archive collections
      archivedInteractions: db.collection('archived_interactions'),
      archivedOrders: db.collection('archived_orders'),

      // Governance and compliance tracking
      retentionPolicies: db.collection('retention_policies'),
      complianceAuditLog: db.collection('compliance_audit_log'),
      legalHolds: db.collection('legal_holds'),
      dataClassifications: db.collection('data_classifications'),
      lifecycleEvents: db.collection('lifecycle_events'),
      governanceMetrics: db.collection('governance_metrics')
    };

    // Advanced governance configuration
    this.governanceConfig = {
      // Automated retention policies
      enableAutomatedRetention: governanceConfig.enableAutomatedRetention !== false,
      enableIntelligentTiering: governanceConfig.enableIntelligentTiering !== false,
      enableComplianceAutomation: governanceConfig.enableComplianceAutomation !== false,

      // Compliance frameworks
      gdprCompliance: governanceConfig.gdprCompliance !== false,
      ccpaCompliance: governanceConfig.ccpaCompliance || false,
      soxCompliance: governanceConfig.soxCompliance || false,
      hipaaCompliance: governanceConfig.hipaaCompliance || false,

      // Data classification and protection
      enableDataClassification: governanceConfig.enableDataClassification !== false,
      enableEncryptionAtRest: governanceConfig.enableEncryptionAtRest !== false,
      enableSecureDeletion: governanceConfig.enableSecureDeletion !== false,

      // Storage optimization
      enableCloudStorageTiering: governanceConfig.enableCloudStorageTiering || false,
      enableCostOptimization: governanceConfig.enableCostOptimization !== false,
      enableAutomatedArchiving: governanceConfig.enableAutomatedArchiving !== false,

      // Monitoring and reporting
      enableComplianceReporting: governanceConfig.enableComplianceReporting !== false,
      enableAuditTrails: governanceConfig.enableAuditTrails !== false,
      enableGovernanceMetrics: governanceConfig.enableGovernanceMetrics !== false,

      // Default retention periods (in days)
      defaultRetentionPeriods: {
        confidential: 2555,  // 7 years
        internal: 1825,      // 5 years
        public: 1095,        // 3 years
        temporary: 90        // 90 days
      },

      // Archival and deletion policies
      archivalConfig: {
        warmToColStorageThreshold: 90,  // Days
        coldToFrozenThreshold: 365,     // Days
        deletionGracePeriod: 30,        // Days
        batchProcessingSize: 1000,
        enableProgressiveArchival: true
      }
    };

    this.initializeDataGovernance();
  }

  async initializeDataGovernance() {
    console.log('Initializing advanced data governance and lifecycle management...');

    try {
      // Setup automated retention policies
      await this.setupAutomatedRetentionPolicies();

      // Initialize data classification framework
      await this.setupDataClassificationFramework();

      // Setup compliance automation
      await this.setupComplianceAutomation();

      // Initialize intelligent archiving
      await this.setupIntelligentArchiving();

      // Setup governance monitoring
      await this.setupGovernanceMonitoring();

      console.log('Data governance system initialized successfully');

    } catch (error) {
      console.error('Error initializing data governance:', error);
      throw error;
    }
  }

  async setupAutomatedRetentionPolicies() {
    console.log('Setting up automated retention policies with TTL and lifecycle rules...');

    try {
      // Customer interactions with automated TTL based on data classification
      await this.collections.interactions.createIndex(
        { "dataGovernance.retentionExpiry": 1 },
        { 
          expireAfterSeconds: 0,
          background: true,
          name: "automated_retention_policy"
        }
      );

      // Setup sophisticated retention policy framework
      const retentionPolicies = [
        {
          _id: new ObjectId(),
          policyName: 'customer_interactions_retention',
          description: 'Automated retention for customer interaction data based on classification and compliance',

          // Collection and criteria configuration
          targetCollections: ['customer_interactions'],
          retentionCriteria: {
            confidential: {
              retentionPeriod: 2555, // 7 years
              complianceFrameworks: ['SOX', 'Financial_Records'],
              secureDelete: true,
              encryptionRequired: true
            },
            internal: {
              retentionPeriod: 1825, // 5 years
              complianceFrameworks: ['Business_Records'],
              secureDelete: false,
              encryptionRequired: false
            },
            public: {
              retentionPeriod: 1095, // 3 years
              complianceFrameworks: ['Marketing_Data'],
              secureDelete: false,
              encryptionRequired: false
            },
            gdpr_subject: {
              retentionPeriod: 2190, // 6 years
              complianceFrameworks: ['GDPR'],
              rightToErasure: true,
              secureDelete: true
            }
          },

          // Advanced policy configuration
          policyConfig: {
            enableLegalHoldRespect: true,
            enableGdprCompliance: true,
            enableProgressiveArchival: true,
            enableCostOptimization: true,
            batchProcessingSize: 1000,
            executionSchedule: 'daily',
            timezoneHandling: 'UTC'
          },

          // Automation and monitoring
          automationSettings: {
            enableAutomaticExecution: true,
            enableNotifications: true,
            enableAuditLogging: true,
            enableComplianceReporting: true,
            executionWindow: { start: '02:00', end: '06:00' }
          },

          // Governance metadata
          governance: {
            createdBy: 'system',
            createdAt: new Date(),
            approvedBy: 'compliance_team',
            approvedAt: new Date(),
            lastReviewDate: new Date(),
            nextReviewDate: new Date(Date.now() + 365 * 24 * 60 * 60 * 1000), // 1 year
            complianceStatus: 'approved'
          }
        },

        {
          _id: new ObjectId(),
          policyName: 'order_data_retention',
          description: 'Financial and order data retention with enhanced compliance tracking',

          targetCollections: ['orders', 'payments'],
          retentionCriteria: {
            financial_record: {
              retentionPeriod: 2920, // 8 years for financial records
              complianceFrameworks: ['SOX', 'Tax_Records', 'Financial_Regulations'],
              secureDelete: true,
              encryptionRequired: true,
              auditTrailRequired: true
            },
            standard_order: {
              retentionPeriod: 2555, // 7 years
              complianceFrameworks: ['Business_Records'],
              secureDelete: false,
              encryptionRequired: false,
              auditTrailRequired: false
            }
          },

          policyConfig: {
            enableLegalHoldRespect: true,
            enableTaxCompliancet: true,
            enableFinancialAuditSupport: true,
            batchProcessingSize: 500,
            executionSchedule: 'weekly',
            requireManualApproval: true // Financial data requires manual approval
          },

          governance: {
            createdBy: 'finance_team',
            approvedBy: 'compliance_officer',
            complianceStatus: 'approved',
            regulatoryAlignment: ['SOX', 'Tax_Regulations', 'Financial_Compliance']
          }
        }
      ];

      // Insert retention policies
      await this.collections.retentionPolicies.insertMany(retentionPolicies);

      console.log('Automated retention policies configured successfully');

    } catch (error) {
      console.error('Error setting up retention policies:', error);
      throw error;
    }
  }

  async setupDataClassificationFramework() {
    console.log('Setting up data classification framework for automated governance...');

    const classificationFramework = {
      _id: new ObjectId(),
      frameworkName: 'enterprise_data_classification',
      version: '2.1',

      // Data sensitivity levels
      sensitivityLevels: {
        public: {
          level: 0,
          description: 'Information available to general public',
          handlingRequirements: {
            encryption: false,
            accessControl: 'none',
            auditLogging: false,
            retentionPeriod: 1095 // 3 years
          },
          complianceFrameworks: []
        },

        internal: {
          level: 1,
          description: 'Internal business information',
          handlingRequirements: {
            encryption: false,
            accessControl: 'basic',
            auditLogging: true,
            retentionPeriod: 1825 // 5 years
          },
          complianceFrameworks: ['Business_Records']
        },

        confidential: {
          level: 2,
          description: 'Sensitive business information requiring protection',
          handlingRequirements: {
            encryption: true,
            accessControl: 'role_based',
            auditLogging: true,
            retentionPeriod: 2555, // 7 years
            secureDelete: true
          },
          complianceFrameworks: ['SOX', 'Business_Confidential']
        },

        restricted: {
          level: 3,
          description: 'Highly sensitive information with strict access controls',
          handlingRequirements: {
            encryption: true,
            accessControl: 'multi_factor',
            auditLogging: true,
            retentionPeriod: 2555, // 7 years
            secureDelete: true,
            approvalRequired: true
          },
          complianceFrameworks: ['SOX', 'Financial_Records', 'Executive_Information']
        }
      },

      // Data categories with specific handling requirements
      dataCategories: {
        personal_data: {
          category: 'personal_data',
          description: 'Personally identifiable information subject to privacy regulations',
          sensitivityLevel: 'confidential',
          specialHandling: {
            gdprApplicable: true,
            ccpaApplicable: true,
            rightToErasure: true,
            dataSubjectRights: true,
            consentTracking: true,
            retentionPeriod: 2190 // 6 years for GDPR
          },
          complianceFrameworks: ['GDPR', 'CCPA', 'Privacy_Regulations']
        },

        financial_data: {
          category: 'financial_data',
          description: 'Financial transactions and accounting information',
          sensitivityLevel: 'restricted',
          specialHandling: {
            soxApplicable: true,
            taxRecordRetention: true,
            auditTrailRequired: true,
            encryptionRequired: true,
            retentionPeriod: 2920 // 8 years for tax records
          },
          complianceFrameworks: ['SOX', 'Tax_Regulations', 'Financial_Compliance']
        },

        health_information: {
          category: 'health_information',
          description: 'Protected health information subject to HIPAA',
          sensitivityLevel: 'restricted',
          specialHandling: {
            hipaaApplicable: true,
            encryptionRequired: true,
            accessLoggingRequired: true,
            minimumNecessaryRule: true,
            retentionPeriod: 2190 // 6 years for health records
          },
          complianceFrameworks: ['HIPAA', 'Health_Privacy']
        },

        business_records: {
          category: 'business_records',
          description: 'General business operational data',
          sensitivityLevel: 'internal',
          specialHandling: {
            businessRecordRetention: true,
            auditSupport: true,
            retentionPeriod: 1825 // 5 years
          },
          complianceFrameworks: ['Business_Records']
        }
      },

      // Automated classification rules
      classificationRules: {
        piiDetection: {
          enabled: true,
          patterns: [
            { field: 'email', pattern: /^[^\s@]+@[^\s@]+\.[^\s@]+$/, classification: 'personal_data' },
            { field: 'phone', pattern: /^\+?[\d\s\-\(\)]{10,}$/, classification: 'personal_data' },
            { field: 'ssn', pattern: /^\d{3}-?\d{2}-?\d{4}$/, classification: 'personal_data' },
            { field: 'credit_card', pattern: /^\d{4}[\s\-]?\d{4}[\s\-]?\d{4}[\s\-]?\d{4}$/, classification: 'financial_data' }
          ]
        },

        financialDataDetection: {
          enabled: true,
          indicators: [
            { fieldNames: ['amount', 'price', 'total', 'payment'], classification: 'financial_data' },
            { fieldNames: ['account_number', 'routing_number'], classification: 'financial_data' },
            { collectionNames: ['payments', 'transactions', 'invoices'], classification: 'financial_data' }
          ]
        },

        healthDataDetection: {
          enabled: true,
          indicators: [
            { fieldNames: ['medical_record', 'diagnosis', 'treatment'], classification: 'health_information' },
            { fieldNames: ['patient_id', 'medical_history'], classification: 'health_information' }
          ]
        }
      },

      // Governance metadata
      governance: {
        frameworkOwner: 'data_governance_team',
        lastUpdated: new Date(),
        nextReview: new Date(Date.now() + 180 * 24 * 60 * 60 * 1000), // 6 months
        approvalStatus: 'approved',
        version: '2.1'
      }
    };

    await this.collections.dataClassifications.replaceOne(
      { frameworkName: 'enterprise_data_classification' },
      classificationFramework,
      { upsert: true }
    );

    console.log('Data classification framework established');
  }

  async executeAutomatedRetentionPolicy(policyName = null) {
    console.log(`Executing automated retention policies${policyName ? ` for: ${policyName}` : ''}...`);
    const executionStart = new Date();

    try {
      // Get active retention policies
      const policies = policyName ? 
        await this.collections.retentionPolicies.find({ policyName: policyName, 'governance.complianceStatus': 'approved' }).toArray() :
        await this.collections.retentionPolicies.find({ 'governance.complianceStatus': 'approved' }).toArray();

      const executionResults = [];

      for (const policy of policies) {
        console.log(`Processing retention policy: ${policy.policyName}`);

        const policyResult = await this.executeIndividualRetentionPolicy(policy);
        executionResults.push({
          policyName: policy.policyName,
          ...policyResult
        });

        // Log policy execution
        await this.logRetentionPolicyExecution(policy, policyResult);
      }

      // Generate comprehensive execution summary
      const executionSummary = await this.generateRetentionExecutionSummary(executionResults, executionStart);

      return executionSummary;

    } catch (error) {
      console.error('Error executing retention policies:', error);
      await this.logRetentionPolicyError(error, { policyName, executionStart });
      throw error;
    }
  }

  async executeIndividualRetentionPolicy(policy) {
    console.log(`Executing policy: ${policy.policyName}`);
    const policyStart = new Date();

    const results = {
      documentsProcessed: 0,
      documentsArchived: 0,
      documentsDeleted: 0,
      documentsSkipped: 0,
      errors: [],
      legalHoldsRespected: 0,
      complianceActionsPerformed: 0
    };

    try {
      for (const collectionName of policy.targetCollections) {
        const collection = this.db.collection(collectionName);

        // Process each retention criteria
        for (const [classification, criteria] of Object.entries(policy.retentionCriteria)) {
          console.log(`Processing classification: ${classification} for collection: ${collectionName}`);

          const classificationResult = await this.processRetentionCriteria(
            collection, 
            classification, 
            criteria, 
            policy.policyConfig
          );

          // Aggregate results
          results.documentsProcessed += classificationResult.documentsProcessed;
          results.documentsArchived += classificationResult.documentsArchived;
          results.documentsDeleted += classificationResult.documentsDeleted;
          results.documentsSkipped += classificationResult.documentsSkipped;
          results.legalHoldsRespected += classificationResult.legalHoldsRespected;
          results.complianceActionsPerformed += classificationResult.complianceActionsPerformed;

          if (classificationResult.errors.length > 0) {
            results.errors.push(...classificationResult.errors);
          }
        }
      }

      results.processingTime = Date.now() - policyStart.getTime();
      results.success = true;

      return results;

    } catch (error) {
      console.error(`Error executing policy ${policy.policyName}:`, error);
      results.success = false;
      results.error = error.message;
      results.processingTime = Date.now() - policyStart.getTime();
      return results;
    }
  }

  async processRetentionCriteria(collection, classification, criteria, policyConfig) {
    console.log(`Processing retention criteria for classification: ${classification}`);

    const results = {
      documentsProcessed: 0,
      documentsArchived: 0,
      documentsDeleted: 0,
      documentsSkipped: 0,
      legalHoldsRespected: 0,
      complianceActionsPerformed: 0,
      errors: []
    };

    try {
      // Calculate retention cutoff date
      const retentionCutoffDate = new Date(Date.now() - criteria.retentionPeriod * 24 * 60 * 60 * 1000);

      // Build query for documents eligible for retention processing
      const retentionQuery = {
        'dataGovernance.classification': classification,
        'dataGovernance.createdAt': { $lt: retentionCutoffDate },

        // Exclude documents under legal hold
        ...(policyConfig.enableLegalHoldRespect && {
          'dataGovernance.legalHold.status': { $ne: 'active' }
        }),

        // Include GDPR-specific filtering
        ...(policyConfig.enableGdprCompliance && classification === 'gdpr_subject' && {
          $or: [
            { 'dataGovernance.gdpr.consentStatus': 'withdrawn' },
            { 'dataGovernance.gdpr.retentionExpiry': { $lt: new Date() } }
          ]
        })
      };

      // Process documents in batches
      const batchSize = policyConfig.batchProcessingSize || 1000;
      let batchOffset = 0;
      let hasMoreDocuments = true;

      while (hasMoreDocuments) {
        const documentsToProcess = await collection.find(retentionQuery)
          .skip(batchOffset)
          .limit(batchSize)
          .toArray();

        if (documentsToProcess.length === 0) {
          hasMoreDocuments = false;
          break;
        }

        // Process each document
        for (const document of documentsToProcess) {
          try {
            const processingResult = await this.processDocumentRetention(
              collection, 
              document, 
              classification, 
              criteria, 
              policyConfig
            );

            // Update results based on processing outcome
            results.documentsProcessed++;

            switch (processingResult.action) {
              case 'archived':
                results.documentsArchived++;
                break;
              case 'deleted':
                results.documentsDeleted++;
                break;
              case 'skipped':
                results.documentsSkipped++;
                break;
              case 'legal_hold_respected':
                results.legalHoldsRespected++;
                results.documentsSkipped++;
                break;
            }

            if (processingResult.complianceAction) {
              results.complianceActionsPerformed++;
            }

          } catch (error) {
            console.error(`Error processing document ${document._id}:`, error);
            results.errors.push({
              documentId: document._id,
              error: error.message,
              classification: classification
            });
          }
        }

        batchOffset += batchSize;

        // Add processing delay to avoid overwhelming the database
        await new Promise(resolve => setTimeout(resolve, 100));
      }

      return results;

    } catch (error) {
      console.error(`Error processing retention criteria for ${classification}:`, error);
      results.errors.push({
        classification: classification,
        error: error.message
      });
      return results;
    }
  }

  async processDocumentRetention(collection, document, classification, criteria, policyConfig) {
    console.log(`Processing document retention for ${document._id}`);

    try {
      // Check for legal holds
      if (policyConfig.enableLegalHoldRespect && document.dataGovernance?.legalHold?.status === 'active') {
        await this.logGovernanceEvent({
          documentId: document._id,
          collection: collection.collectionName,
          action: 'retention_blocked_legal_hold',
          classification: classification,
          legalHoldId: document.dataGovernance.legalHold.holdId,
          timestamp: new Date()
        });

        return { action: 'legal_hold_respected', complianceAction: true };
      }

      // Check GDPR right to erasure
      if (policyConfig.enableGdprCompliance && 
          document.dataGovernance?.gdpr?.rightToErasureRequested) {

        await this.executeGdprErasure(collection, document);

        await this.logGovernanceEvent({
          documentId: document._id,
          collection: collection.collectionName,
          action: 'gdpr_right_to_erasure',
          classification: classification,
          timestamp: new Date()
        });

        return { action: 'deleted', complianceAction: true };
      }

      // Determine appropriate retention action
      if (criteria.secureDelete || policyConfig.requireManualApproval) {
        // Archive first, then schedule for deletion
        await this.archiveDocument(collection, document, criteria);

        return { action: 'archived', complianceAction: false };
      } else {
        // Direct deletion for non-sensitive data
        await this.deleteDocumentWithAuditTrail(collection, document, criteria);

        return { action: 'deleted', complianceAction: false };
      }

    } catch (error) {
      console.error(`Error processing document retention for ${document._id}:`, error);
      throw error;
    }
  }

  async archiveDocument(collection, document, criteria) {
    console.log(`Archiving document ${document._id} to cold storage...`);

    try {
      // Prepare archived document with governance metadata
      const archivedDocument = {
        ...document,
        archivedMetadata: {
          originalCollection: collection.collectionName,
          archiveDate: new Date(),
          archiveReason: 'automated_retention_policy',
          retentionCriteria: criteria,
          archiveId: new ObjectId()
        },
        dataGovernance: {
          ...document.dataGovernance,
          lifecycleStage: 'archived',
          archiveTimestamp: new Date(),
          scheduledDeletion: criteria.secureDelete ? 
            new Date(Date.now() + 30 * 24 * 60 * 60 * 1000) : null // 30 day grace period
        }
      };

      // Insert into archive collection
      const archiveCollectionName = `archived_${collection.collectionName}`;
      await this.db.collection(archiveCollectionName).insertOne(archivedDocument);

      // Remove from active collection
      await collection.deleteOne({ _id: document._id });

      // Log archival event
      await this.logGovernanceEvent({
        documentId: document._id,
        collection: collection.collectionName,
        action: 'document_archived',
        archiveCollection: archiveCollectionName,
        archiveId: archivedDocument.archivedMetadata.archiveId,
        retentionCriteria: criteria,
        timestamp: new Date()
      });

      console.log(`Document ${document._id} archived successfully`);

    } catch (error) {
      console.error(`Error archiving document ${document._id}:`, error);
      throw error;
    }
  }

  async executeGdprErasure(collection, document) {
    console.log(`Executing GDPR right to erasure for document ${document._id}...`);

    try {
      // Log GDPR erasure before deletion (compliance requirement)
      await this.logGovernanceEvent({
        documentId: document._id,
        collection: collection.collectionName,
        action: 'gdpr_right_to_erasure_executed',
        gdprRequestId: document.dataGovernance?.gdpr?.erasureRequestId,
        dataSubject: document.dataGovernance?.gdpr?.dataSubject,
        timestamp: new Date(),
        legalBasis: 'GDPR Article 17 - Right to Erasure'
      });

      // Perform secure deletion
      await this.secureDeleteDocument(collection, document);

      // Update GDPR compliance tracking
      await this.updateGdprComplianceStatus(
        document.dataGovernance?.gdpr?.erasureRequestId, 
        'completed'
      );

      console.log(`GDPR erasure completed for document ${document._id}`);

    } catch (error) {
      console.error(`Error executing GDPR erasure for document ${document._id}:`, error);
      throw error;
    }
  }

  async secureDeleteDocument(collection, document) {
    console.log(`Performing secure deletion for document ${document._id}...`);

    try {
      // Create deletion audit record
      const deletionAudit = {
        _id: new ObjectId(),
        originalDocumentId: document._id,
        originalCollection: collection.collectionName,
        deletionTimestamp: new Date(),
        deletionMethod: 'secure_deletion',
        deletionReason: 'automated_retention_policy',
        documentHash: this.generateDocumentHash(document),
        complianceFrameworks: document.dataGovernance?.complianceFrameworks || [],
        auditRetentionPeriod: new Date(Date.now() + 10 * 365 * 24 * 60 * 60 * 1000) // 10 years
      };

      // Store deletion audit record
      await this.collections.complianceAuditLog.insertOne(deletionAudit);

      // Delete the actual document
      await collection.deleteOne({ _id: document._id });

      console.log(`Secure deletion completed for document ${document._id}`);

    } catch (error) {
      console.error(`Error performing secure deletion for document ${document._id}:`, error);
      throw error;
    }
  }

  async setupIntelligentArchiving() {
    console.log('Setting up intelligent archiving with automated tiering...');

    try {
      // Create TTL indexes for different tiers
      const archivingIndexes = [
        {
          collection: 'customer_interactions',
          index: { "dataGovernance.warmToColumnTierDate": 1 },
          options: { 
            expireAfterSeconds: 0,
            background: true,
            name: "warm_to_column_tiering"
          }
        },
        {
          collection: 'customer_interactions',
          index: { "dataGovernance.coldToFrozenTierDate": 1 },
          options: { 
            expireAfterSeconds: 0,
            background: true,
            name: "cold_to_frozen_tiering"
          }
        },
        {
          collection: 'archived_customer_interactions',
          index: { "archivedMetadata.scheduledDeletion": 1 },
          options: { 
            expireAfterSeconds: 0,
            background: true,
            name: "archived_data_deletion"
          }
        }
      ];

      for (const indexConfig of archivingIndexes) {
        await this.db.collection(indexConfig.collection).createIndex(
          indexConfig.index,
          indexConfig.options
        );
      }

      console.log('Intelligent archiving indexes created successfully');

    } catch (error) {
      console.error('Error setting up intelligent archiving:', error);
      throw error;
    }
  }

  async generateComplianceReport(reportType = 'comprehensive', dateRange = null) {
    console.log(`Generating ${reportType} compliance report...`);

    try {
      const reportStart = dateRange?.start || new Date(Date.now() - 30 * 24 * 60 * 60 * 1000); // 30 days ago
      const reportEnd = dateRange?.end || new Date();

      const complianceReport = {
        reportId: new ObjectId(),
        reportType: reportType,
        generatedAt: new Date(),
        reportPeriod: { start: reportStart, end: reportEnd },
        complianceFrameworks: []
      };

      // Data governance metrics
      complianceReport.dataGovernanceMetrics = await this.generateDataGovernanceMetrics(reportStart, reportEnd);

      // Retention policy compliance
      complianceReport.retentionCompliance = await this.generateRetentionComplianceMetrics(reportStart, reportEnd);

      // GDPR compliance metrics
      if (this.governanceConfig.gdprCompliance) {
        complianceReport.gdprCompliance = await this.generateGdprComplianceMetrics(reportStart, reportEnd);
        complianceReport.complianceFrameworks.push('GDPR');
      }

      // SOX compliance metrics
      if (this.governanceConfig.soxCompliance) {
        complianceReport.soxCompliance = await this.generateSoxComplianceMetrics(reportStart, reportEnd);
        complianceReport.complianceFrameworks.push('SOX');
      }

      // Data lifecycle metrics
      complianceReport.lifecycleMetrics = await this.generateLifecycleMetrics(reportStart, reportEnd);

      // Risk and audit metrics
      complianceReport.riskMetrics = await this.generateRiskMetrics(reportStart, reportEnd);

      // Store compliance report
      await this.collections.governanceMetrics.insertOne(complianceReport);

      return complianceReport;

    } catch (error) {
      console.error('Error generating compliance report:', error);
      throw error;
    }
  }

  async generateDataGovernanceMetrics(startDate, endDate) {
    console.log('Generating data governance metrics...');

    const metrics = await this.collections.lifecycleEvents.aggregate([
      {
        $match: {
          timestamp: { $gte: startDate, $lte: endDate }
        }
      },
      {
        $group: {
          _id: '$action',
          count: { $sum: 1 },
          collections: { $addToSet: '$collection' },
          complianceFrameworks: { $addToSet: '$retentionCriteria.complianceFrameworks' },
          avgProcessingTime: { $avg: '$processingTime' }
        }
      },
      {
        $project: {
          action: '$_id',
          count: 1,
          collectionsCount: { $size: '$collections' },
          complianceFrameworksCount: { $size: '$complianceFrameworks' },
          avgProcessingTimeMs: { $round: ['$avgProcessingTime', 2] }
        }
      }
    ]).toArray();

    return {
      totalGovernanceEvents: metrics.reduce((sum, m) => sum + m.count, 0),
      actionBreakdown: metrics,
      period: { start: startDate, end: endDate }
    };
  }

  // Utility methods for governance operations

  generateDocumentHash(document) {
    const crypto = require('crypto');
    const documentString = JSON.stringify(document, Object.keys(document).sort());
    return crypto.createHash('sha256').update(documentString).digest('hex');
  }

  async logGovernanceEvent(eventData) {
    try {
      const event = {
        _id: new ObjectId(),
        ...eventData,
        timestamp: eventData.timestamp || new Date()
      };

      await this.collections.lifecycleEvents.insertOne(event);

    } catch (error) {
      console.error('Error logging governance event:', error);
      // Don't throw - logging shouldn't break governance operations
    }
  }

  async logRetentionPolicyExecution(policy, results) {
    try {
      const executionLog = {
        _id: new ObjectId(),
        policyName: policy.policyName,
        executionTimestamp: new Date(),
        results: results,
        policyConfiguration: policy.policyConfig,
        governance: {
          executedBy: 'automated_system',
          complianceStatus: results.success ? 'successful' : 'failed',
          auditTrail: true
        }
      };

      await this.collections.complianceAuditLog.insertOne(executionLog);

    } catch (error) {
      console.error('Error logging retention policy execution:', error);
    }
  }
}

// Enterprise-ready data lifecycle automation
class EnterpriseDataLifecycleAutomation extends AdvancedDataLifecycleManager {
  constructor(db, enterpriseConfig) {
    super(db, enterpriseConfig);

    this.enterpriseConfig = {
      ...enterpriseConfig,
      enableCloudStorageIntegration: true,
      enableCostOptimization: true,
      enableComplianceOrchestration: true,
      enableExecutiveDashboards: true,
      enableAutomatedReporting: true
    };

    this.setupEnterpriseAutomation();
  }

  async setupEnterpriseAutomation() {
    console.log('Setting up enterprise data lifecycle automation...');

    // Setup automated scheduling
    await this.setupAutomatedScheduling();

    // Setup cost optimization
    await this.setupCostOptimization();

    // Setup compliance orchestration
    await this.setupComplianceOrchestration();

    console.log('Enterprise automation configured successfully');
  }

  async setupAutomatedScheduling() {
    console.log('Setting up automated retention scheduling...');

    // Implementation would include:
    // - Cron-like scheduling system
    // - Load balancing across retention operations
    // - Maintenance window awareness
    // - Performance impact monitoring

    const schedulingConfig = {
      retentionSchedule: {
        daily: { time: '02:00', timezone: 'UTC', enabled: true },
        weekly: { day: 'Sunday', time: '01:00', timezone: 'UTC', enabled: true },
        monthly: { day: 1, time: '00:00', timezone: 'UTC', enabled: true }
      },

      maintenanceWindows: [
        { start: '01:00', end: '05:00', timezone: 'UTC', priority: 'high' },
        { start: '13:00', end: '14:00', timezone: 'UTC', priority: 'medium' }
      ],

      performanceThresholds: {
        maxConcurrentOperations: 3,
        maxDocumentsPerMinute: 10000,
        maxMemoryUsage: '2GB',
        cpuThrottling: 80
      }
    };

    // Store scheduling configuration
    await this.collections.governanceMetrics.replaceOne(
      { configType: 'scheduling' },
      { configType: 'scheduling', ...schedulingConfig, lastUpdated: new Date() },
      { upsert: true }
    );
  }

  async setupCostOptimization() {
    console.log('Setting up automated cost optimization...');

    const costOptimizationConfig = {
      storageTiering: {
        hotStorage: { maxAge: 30, costPerGB: 0.023 }, // 30 days
        warmStorage: { maxAge: 90, costPerGB: 0.012 }, // 90 days
        coldStorage: { maxAge: 365, costPerGB: 0.004 }, // 1 year
        frozenStorage: { maxAge: 2555, costPerGB: 0.001 } // 7 years
      },

      optimizationRules: {
        enableAutomatedTiering: true,
        enableCostAlerts: true,
        enableUsageAnalytics: true,
        optimizationSchedule: 'weekly'
      }
    };

    await this.collections.governanceMetrics.replaceOne(
      { configType: 'cost_optimization' },
      { configType: 'cost_optimization', ...costOptimizationConfig, lastUpdated: new Date() },
      { upsert: true }
    );
  }
}

// Benefits of MongoDB Advanced Data Lifecycle Management:
// - Automated retention policies with native TTL and governance integration
// - Comprehensive compliance framework support (GDPR, CCPA, SOX, HIPAA)
// - Intelligent data tiering and cost optimization
// - Enterprise-grade audit trails and compliance reporting
// - Automated data classification and sensitivity detection
// - Legal hold management with automated compliance tracking
// - Native integration with MongoDB's storage and archiving capabilities
// - SQL-compatible lifecycle management through QueryLeaf integration
// - Real-time governance monitoring and alerting
// - Scalable automation for enterprise data volumes

module.exports = {
  AdvancedDataLifecycleManager,
  EnterpriseDataLifecycleAutomation
};