2025

-- Traditional database infrastructure challenges

-- Manual scaling requires downtime
ALTER TABLE orders 
ADD PARTITION p2025_q1 VALUES LESS THAN ('2025-04-01');
-- Requires planning, testing, and maintenance windows

-- Backup management complexity
CREATE SCHEDULED JOB backup_daily_full
AS 'pg_dump production_db > /backups/full_$(date +%Y%m%d).sql'
SCHEDULE = 'CRON 0 2 * * *';
-- Manual backup verification, rotation, and disaster recovery testing

-- Resource monitoring and alerting
SELECT 
  table_name,
  pg_size_pretty(pg_total_relation_size(table_name)) AS size,
  (SELECT COUNT(*) FROM table_name) AS row_count
FROM information_schema.tables
WHERE table_schema = 'public'
  AND pg_total_relation_size(table_name) > 1073741824;  -- > 1GB
-- Manual monitoring setup and threshold management

-- Security patch management
UPDATE postgresql_version 
SET version = '14.8'
WHERE current_version = '14.7';
-- Requires testing, rollback planning, and downtime coordination

// MongoDB Atlas automated infrastructure management
const atlasCluster = {
  name: "production-cluster",
  provider: "AWS",
  region: "us-east-1",
  tier: "M30",

  // Automatic scaling configuration
  autoScaling: {
    enabled: true,
    minInstanceSize: "M10",
    maxInstanceSize: "M60", 
    scaleDownEnabled: true
  },

  // Automated backup and point-in-time recovery
  backupPolicy: {
    enabled: true,
    snapshotRetentionDays: 30,
    pointInTimeRecoveryEnabled: true,
    continuousBackup: true
  },

  // Built-in monitoring and alerting
  monitoring: {
    performance: true,
    alerts: [
      { condition: "cpu_usage > 80", notification: "email" },
      { condition: "replication_lag > 60s", notification: "slack" },
      { condition: "connections > 80%", notification: "pagerduty" }
    ]
  }
};

// Applications connect seamlessly regardless of scaling events
db.orders.insertOne({
  customer_id: ObjectId("64f1a2c4567890abcdef1234"),
  items: [{ product: "laptop", quantity: 2, price: 1500 }],
  total_amount: 3000,
  status: "pending",
  created_at: new Date()
});
// Atlas handles routing, scaling, and failover transparently

// Production cluster deployment configuration
class AtlasClusterManager {
  constructor(atlasAPI) {
    this.atlasAPI = atlasAPI;
  }

  async deployProductionCluster(config) {
    const clusterConfig = {
      name: config.clusterName || "production-cluster",

      // Infrastructure configuration
      clusterType: "REPLICASET",
      mongoDBMajorVersion: "7.0",

      // Cloud provider settings
      providerSettings: {
        providerName: config.provider || "AWS",
        regionName: config.region || "US_EAST_1", 
        instanceSizeName: config.tier || "M30",

        // High availability across availability zones
        electableSpecs: {
          instanceSize: config.tier || "M30",
          nodeCount: 3,  // 3-node replica set
          ebsVolumeType: "GP3",
          diskIOPS: 3000
        },

        // Read-only analytics nodes
        readOnlySpecs: {
          instanceSize: config.analyticsTier || "M20",
          nodeCount: config.analyticsNodes || 2
        }
      },

      // Auto-scaling configuration
      autoScaling: {
        diskGBEnabled: true,
        compute: {
          enabled: true,
          scaleDownEnabled: true,
          minInstanceSize: config.minTier || "M10",
          maxInstanceSize: config.maxTier || "M60"
        }
      },

      // Backup configuration
      backupEnabled: true,
      pitEnabled: true,  // Point-in-time recovery

      // Advanced configuration
      encryptionAtRestProvider: "AWS",
      labels: [
        { key: "Environment", value: config.environment || "production" },
        { key: "Application", value: config.application },
        { key: "CostCenter", value: config.costCenter }
      ]
    };

    try {
      const deploymentResult = await this.atlasAPI.clusters.create(
        config.projectId,
        clusterConfig
      );

      // Wait for cluster to become available
      await this.waitForClusterReady(config.projectId, clusterConfig.name);

      // Configure network access
      await this.configureNetworkSecurity(config.projectId, config.allowedIPs);

      // Set up database users
      await this.configureUserAccess(config.projectId, config.users);

      return {
        success: true,
        cluster: deploymentResult,
        connectionString: await this.getConnectionString(config.projectId, clusterConfig.name)
      };
    } catch (error) {
      throw new Error(`Cluster deployment failed: ${error.message}`);
    }
  }

  async configureNetworkSecurity(projectId, allowedIPs) {
    // Configure IP allowlist for network security
    const networkConfig = allowedIPs.map(ip => ({
      ipAddress: ip.address,
      comment: ip.description || `Access from ${ip.address}`
    }));

    return await this.atlasAPI.networkAccess.create(projectId, networkConfig);
  }

  async configureUserAccess(projectId, users) {
    // Create database users with appropriate privileges
    for (const user of users) {
      const userConfig = {
        username: user.username,
        password: user.password || this.generateSecurePassword(),
        roles: user.roles.map(role => ({
          roleName: role.name,
          databaseName: role.database
        })),
        scopes: user.scopes || []
      };

      await this.atlasAPI.databaseUsers.create(projectId, userConfig);
    }
  }

  async waitForClusterReady(projectId, clusterName, timeoutMs = 1800000) {
    const startTime = Date.now();

    while (Date.now() - startTime < timeoutMs) {
      const cluster = await this.atlasAPI.clusters.get(projectId, clusterName);

      if (cluster.stateName === "IDLE") {
        return cluster;
      }

      console.log(`Cluster status: ${cluster.stateName}. Waiting...`);
      await this.sleep(30000);  // Check every 30 seconds
    }

    throw new Error(`Cluster deployment timeout after ${timeoutMs / 60000} minutes`);
  }

  generateSecurePassword(length = 16) {
    const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*';
    return Array.from(crypto.getRandomValues(new Uint8Array(length)))
      .map(x => chars[x % chars.length])
      .join('');
  }

  sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }
}

-- SQL cloud database deployment concepts
CREATE MANAGED_DATABASE_CLUSTER production_cluster AS (
  -- Infrastructure specification
  PROVIDER = 'AWS',
  REGION = 'us-east-1',
  INSTANCE_CLASS = 'db.r5.xlarge',
  STORAGE_TYPE = 'gp3',
  ALLOCATED_STORAGE = 500,  -- GB

  -- High availability configuration
  MULTI_AZ = true,
  REPLICA_COUNT = 2,
  AUTOMATIC_FAILOVER = true,

  -- Auto-scaling settings
  AUTO_SCALING_ENABLED = true,
  MIN_CAPACITY = 'db.r5.large',
  MAX_CAPACITY = 'db.r5.4xlarge',
  SCALE_DOWN_ENABLED = true,

  -- Backup and recovery
  AUTOMATED_BACKUP = true,
  BACKUP_RETENTION_DAYS = 30,
  POINT_IN_TIME_RECOVERY = true,

  -- Security settings
  ENCRYPTION_AT_REST = true,
  ENCRYPTION_IN_TRANSIT = true,
  VPC_SECURITY_GROUP = 'sg-production-db'
)
WITH DEPLOYMENT_TIMEOUT = 30 MINUTES,
     MAINTENANCE_WINDOW = 'sun:03:00-sun:04:00';

// Auto-scaling configuration and monitoring
class AtlasScalingManager {
  constructor(atlasAPI, projectId) {
    this.atlasAPI = atlasAPI;
    this.projectId = projectId;
  }

  async configureAutoScaling(clusterName, scalingRules) {
    const autoScalingConfig = {
      // Compute auto-scaling
      compute: {
        enabled: true,
        scaleDownEnabled: scalingRules.allowScaleDown || true,
        minInstanceSize: scalingRules.minTier || "M10",
        maxInstanceSize: scalingRules.maxTier || "M60",

        // Scaling triggers
        scaleUpThreshold: {
          cpuUtilization: scalingRules.scaleUpCPU || 75,
          memoryUtilization: scalingRules.scaleUpMemory || 80,
          connectionUtilization: scalingRules.scaleUpConnections || 80
        },

        scaleDownThreshold: {
          cpuUtilization: scalingRules.scaleDownCPU || 50,
          memoryUtilization: scalingRules.scaleDownMemory || 60,
          connectionUtilization: scalingRules.scaleDownConnections || 50
        }
      },

      // Storage auto-scaling  
      storage: {
        enabled: true,
        diskGBEnabled: true
      }
    };

    try {
      await this.atlasAPI.clusters.updateAutoScaling(
        this.projectId,
        clusterName,
        autoScalingConfig
      );

      return {
        success: true,
        configuration: autoScalingConfig
      };
    } catch (error) {
      throw new Error(`Auto-scaling configuration failed: ${error.message}`);
    }
  }

  async monitorScalingEvents(clusterName, timeframeDays = 7) {
    // Get scaling events from Atlas monitoring
    const endDate = new Date();
    const startDate = new Date(endDate.getTime() - (timeframeDays * 24 * 60 * 60 * 1000));

    const scalingEvents = await this.atlasAPI.monitoring.getScalingEvents(
      this.projectId,
      clusterName,
      startDate,
      endDate
    );

    // Analyze scaling patterns
    const analysis = this.analyzeScalingPatterns(scalingEvents);

    return {
      events: scalingEvents,
      analysis: analysis,
      recommendations: this.generateScalingRecommendations(analysis)
    };
  }

  analyzeScalingPatterns(events) {
    const scaleUpEvents = events.filter(e => e.action === 'SCALE_UP');
    const scaleDownEvents = events.filter(e => e.action === 'SCALE_DOWN');

    // Calculate peak usage patterns
    const hourlyDistribution = new Array(24).fill(0);
    events.forEach(event => {
      const hour = new Date(event.timestamp).getHours();
      hourlyDistribution[hour]++;
    });

    const peakHours = hourlyDistribution
      .map((count, hour) => ({ hour, count }))
      .filter(item => item.count > 0)
      .sort((a, b) => b.count - a.count)
      .slice(0, 3);

    return {
      totalScaleUps: scaleUpEvents.length,
      totalScaleDowns: scaleDownEvents.length,
      peakUsageHours: peakHours.map(p => p.hour),
      avgScalingFrequency: events.length / 7,  // Per day over week
      mostCommonTrigger: this.findMostCommonTrigger(events)
    };
  }

  generateScalingRecommendations(analysis) {
    const recommendations = [];

    if (analysis.totalScaleUps > analysis.totalScaleDowns * 2) {
      recommendations.push({
        type: 'baseline_adjustment',
        message: 'Consider increasing minimum instance size to reduce frequent scale-ups',
        priority: 'medium'
      });
    }

    if (analysis.avgScalingFrequency > 2) {
      recommendations.push({
        type: 'scaling_sensitivity',
        message: 'High scaling frequency detected. Consider adjusting thresholds',
        priority: 'low'
      });
    }

    if (analysis.peakUsageHours.length > 0) {
      recommendations.push({
        type: 'predictive_scaling',
        message: `Peak usage detected at hours ${analysis.peakUsageHours.join(', ')}. Consider scheduled scaling`,
        priority: 'medium'
      });
    }

    return recommendations;
  }
}

// Atlas performance optimization strategies
class AtlasPerformanceOptimizer {
  constructor(client, atlasAPI) {
    this.client = client;
    this.atlasAPI = atlasAPI;
  }

  async optimizeClusterPerformance(clusterName) {
    // Analyze current performance metrics
    const performanceData = await this.collectPerformanceMetrics(clusterName);

    // Generate optimization recommendations
    const optimizations = await this.generateOptimizations(performanceData);

    // Apply automated optimizations
    const applied = await this.applyOptimizations(clusterName, optimizations);

    return {
      currentMetrics: performanceData,
      recommendations: optimizations,
      appliedOptimizations: applied
    };
  }

  async collectPerformanceMetrics(clusterName) {
    // Get comprehensive cluster metrics
    const metrics = {
      cpu: await this.getMetricSeries('CPU_USAGE', clusterName),
      memory: await this.getMetricSeries('MEMORY_USAGE', clusterName),
      connections: await this.getMetricSeries('CONNECTIONS', clusterName),
      diskIOPS: await this.getMetricSeries('DISK_IOPS', clusterName),
      networkIO: await this.getMetricSeries('NETWORK_BYTES_OUT', clusterName),

      // Query performance metrics
      slowQueries: await this.getSlowQueryAnalysis(clusterName),
      indexUsage: await this.getIndexEfficiency(clusterName),

      // Operational metrics
      replicationLag: await this.getReplicationMetrics(clusterName),
      oplogStats: await this.getOplogUtilization(clusterName)
    };

    return metrics;
  }

  async getSlowQueryAnalysis(clusterName) {
    // Analyze slow query logs through Atlas API
    const slowQueries = await this.atlasAPI.monitoring.getSlowQueries(
      this.projectId,
      clusterName,
      { 
        duration: { $gte: 1000 },  // Queries > 1 second
        limit: 100
      }
    );

    // Group by operation pattern
    const queryPatterns = new Map();

    slowQueries.forEach(query => {
      const pattern = this.normalizeQueryPattern(query.command);
      if (!queryPatterns.has(pattern)) {
        queryPatterns.set(pattern, {
          pattern: pattern,
          count: 0,
          totalDuration: 0,
          avgDuration: 0,
          collections: new Set()
        });
      }

      const stats = queryPatterns.get(pattern);
      stats.count++;
      stats.totalDuration += query.duration;
      stats.avgDuration = stats.totalDuration / stats.count;
      stats.collections.add(query.ns);
    });

    return Array.from(queryPatterns.values())
      .sort((a, b) => b.totalDuration - a.totalDuration)
      .slice(0, 10);
  }

  async generateIndexRecommendations(clusterName) {
    // Use Atlas Performance Advisor API
    const recommendations = await this.atlasAPI.performanceAdvisor.getSuggestedIndexes(
      this.projectId,
      clusterName
    );

    // Prioritize recommendations by impact
    return recommendations.suggestedIndexes
      .map(rec => ({
        collection: rec.namespace,
        index: rec.index,
        impact: rec.impact,
        queries: rec.queryPatterns,
        estimatedSizeBytes: rec.estimatedSize,
        priority: this.calculateIndexPriority(rec)
      }))
      .sort((a, b) => b.priority - a.priority);
  }

  calculateIndexPriority(recommendation) {
    let priority = 0;

    // High impact operations get higher priority
    if (recommendation.impact > 0.8) priority += 3;
    else if (recommendation.impact > 0.5) priority += 2;
    else priority += 1;

    // Frequent queries get priority boost
    if (recommendation.queryPatterns.length > 10) priority += 2;

    // Small indexes are easier to implement
    if (recommendation.estimatedSize < 1024 * 1024 * 100) priority += 1; // < 100MB

    return priority;
  }
}

-- SQL performance optimization equivalent
-- Analyze query performance
SELECT 
  query_text,
  calls,
  total_time / 1000.0 AS total_seconds,
  mean_time / 1000.0 AS avg_seconds,
  rows_returned / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE total_time > 60000  -- Queries taking > 1 minute total
ORDER BY total_time DESC
LIMIT 10;

-- Auto-scaling configuration
ALTER DATABASE production_db 
SET auto_scaling = 'enabled',
    min_capacity = 2,
    max_capacity = 64,
    target_cpu_utilization = 70,
    scale_down_cooldown = 300;  -- 5 minutes

-- Index recommendations based on query patterns
WITH query_analysis AS (
  SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
  FROM pg_stat_user_tables
  WHERE seq_scan > idx_scan  -- More sequential than index scans
)
SELECT 
  schemaname,
  tablename,
  'CREATE INDEX idx_' || tablename || '_recommended ON ' || 
  schemaname || '.' || tablename || ' (column_list);' AS recommended_index
FROM query_analysis
WHERE seq_tup_read > 10000;  -- High sequential reads

// Global cluster configuration for multi-region deployment
class GlobalClusterManager {
  constructor(atlasAPI) {
    this.atlasAPI = atlasAPI;
  }

  async deployGlobalCluster(config) {
    const globalConfig = {
      name: config.clusterName,
      clusterType: "GEOSHARDED",  // Global clusters use geo-sharding

      // Regional configurations
      replicationSpecs: [
        {
          // Primary region (US East)
          id: "primary-region",
          numShards: config.primaryShards || 2,
          zoneName: "Zone 1",
          regionsConfig: {
            "US_EAST_1": {
              analyticsSpecs: {
                instanceSize: "M20",
                nodeCount: 1
              },
              electableSpecs: {
                instanceSize: "M30", 
                nodeCount: 3
              },
              priority: 7,  // Highest priority
              readOnlySpecs: {
                instanceSize: "M20",
                nodeCount: 2
              }
            }
          }
        },
        {
          // Secondary region (Europe)
          id: "europe-region", 
          numShards: config.europeShards || 1,
          zoneName: "Zone 2",
          regionsConfig: {
            "EU_WEST_1": {
              electableSpecs: {
                instanceSize: "M20",
                nodeCount: 3
              },
              priority: 6,
              readOnlySpecs: {
                instanceSize: "M10",
                nodeCount: 1
              }
            }
          }
        },
        {
          // Asia-Pacific region
          id: "asia-region",
          numShards: config.asiaShards || 1, 
          zoneName: "Zone 3",
          regionsConfig: {
            "AP_SOUTHEAST_1": {
              electableSpecs: {
                instanceSize: "M20",
                nodeCount: 3
              },
              priority: 5,
              readOnlySpecs: {
                instanceSize: "M10",
                nodeCount: 1
              }
            }
          }
        }
      ],

      // Global cluster settings
      mongoDBMajorVersion: "7.0",
      encryptionAtRestProvider: "AWS",
      backupEnabled: true,
      pitEnabled: true
    };

    const deployment = await this.atlasAPI.clusters.create(this.projectId, globalConfig);

    // Configure zone mappings for data locality
    await this.configureZoneMappings(config.clusterName, config.zoneMappings);

    return deployment;
  }

  async configureZoneMappings(clusterName, zoneMappings) {
    // Configure shard key ranges for geographic data distribution
    for (const mapping of zoneMappings) {
      await this.client.db('admin').command({
        updateZoneKeyRange: `${mapping.database}.${mapping.collection}`,
        min: mapping.min,
        max: mapping.max,
        zone: mapping.zone
      });
    }
  }

  async optimizeGlobalReadPreferences(applications) {
    // Configure region-aware read preferences
    const readPreferenceConfigs = applications.map(app => ({
      application: app.name,
      regions: app.regions.map(region => ({
        region: region.name,
        readPreference: {
          mode: "nearest",
          tags: [{ region: region.atlasRegion }],
          maxStalenessMS: region.maxStaleness || 120000
        }
      }))
    }));

    return readPreferenceConfigs;
  }
}

// Geographic data routing
class GeographicDataRouter {
  constructor(client) {
    this.client = client;
    this.regionMappings = {
      'us': { tags: [{ zone: 'Zone 1' }] },
      'eu': { tags: [{ zone: 'Zone 2' }] },
      'asia': { tags: [{ zone: 'Zone 3' }] }
    };
  }

  async getUserDataByRegion(userId, userRegion) {
    const readPreference = {
      mode: "nearest",
      tags: this.regionMappings[userRegion]?.tags || [],
      maxStalenessMS: 120000
    };

    return await this.client.db('ecommerce')
      .collection('users')
      .findOne(
        { _id: userId },
        { readPreference }
      );
  }

  async insertRegionalData(collection, document, region) {
    // Ensure data is written to appropriate geographic zone
    const writeOptions = {
      writeConcern: {
        w: "majority",
        j: true,
        wtimeout: 10000
      }
    };

    // Add regional metadata for proper sharding
    const regionalDocument = {
      ...document,
      _region: region,
      _zone: this.getZoneForRegion(region),
      created_at: new Date()
    };

    return await this.client.db('ecommerce')
      .collection(collection)
      .insertOne(regionalDocument, writeOptions);
  }

  getZoneForRegion(region) {
    const zoneMap = {
      'us-east-1': 'Zone 1',
      'eu-west-1': 'Zone 2', 
      'ap-southeast-1': 'Zone 3'
    };
    return zoneMap[region] || 'Zone 1';
  }
}

// Atlas backup and recovery management
class AtlasBackupManager {
  constructor(atlasAPI, projectId) {
    this.atlasAPI = atlasAPI;
    this.projectId = projectId;
  }

  async configureBackupPolicy(clusterName, policy) {
    const backupConfig = {
      // Snapshot scheduling
      snapshotSchedulePolicy: {
        snapshotIntervalHours: policy.snapshotInterval || 24,
        snapshotRetentionDays: policy.retentionDays || 30,
        clusterCheckpointIntervalMin: policy.checkpointInterval || 15
      },

      // Point-in-time recovery
      pointInTimeRecoveryEnabled: policy.pointInTimeEnabled || true,

      // Cross-region backup replication
      copySettings: policy.crossRegionBackup ? [
        {
          cloudProvider: "AWS",
          regionName: policy.backupRegion || "US_WEST_2",
          shouldCopyOplogs: true,
          frequencies: ["HOURLY", "DAILY", "WEEKLY", "MONTHLY"]
        }
      ] : [],

      // Backup compliance settings
      restoreWindowDays: policy.restoreWindow || 7,
      updateSnapshots: policy.updateSnapshots || true
    };

    try {
      await this.atlasAPI.backups.updatePolicy(
        this.projectId,
        clusterName,
        backupConfig
      );

      return {
        success: true,
        policy: backupConfig
      };
    } catch (error) {
      throw new Error(`Backup policy configuration failed: ${error.message}`);
    }
  }

  async performOnDemandBackup(clusterName, description) {
    const snapshot = await this.atlasAPI.backups.createSnapshot(
      this.projectId,
      clusterName,
      {
        description: description || `On-demand backup - ${new Date().toISOString()}`,
        retentionInDays: 30
      }
    );

    // Wait for snapshot completion
    await this.waitForSnapshotCompletion(clusterName, snapshot.id);

    return snapshot;
  }

  async restoreFromBackup(sourceCluster, targetCluster, restoreOptions) {
    const restoreConfig = {
      // Source configuration
      snapshotId: restoreOptions.snapshotId,

      // Target cluster configuration
      targetClusterName: targetCluster,
      targetGroupId: this.projectId,

      // Restore options
      deliveryType: restoreOptions.deliveryType || "automated",

      // Point-in-time recovery
      pointInTimeUTCSeconds: restoreOptions.pointInTime 
        ? Math.floor(restoreOptions.pointInTime.getTime() / 1000)
        : null
    };

    try {
      const restoreJob = await this.atlasAPI.backups.createRestoreJob(
        this.projectId,
        sourceCluster,
        restoreConfig
      );

      // Monitor restore progress
      await this.waitForRestoreCompletion(restoreJob.id);

      return {
        success: true,
        restoreJob: restoreJob,
        targetCluster: targetCluster
      };
    } catch (error) {
      throw new Error(`Restore operation failed: ${error.message}`);
    }
  }

  async validateBackupIntegrity(clusterName) {
    // Get recent snapshots
    const snapshots = await this.atlasAPI.backups.getSnapshots(
      this.projectId,
      clusterName,
      { limit: 10 }
    );

    const validationResults = [];

    for (const snapshot of snapshots) {
      // Test restore to temporary cluster
      const tempClusterName = `temp-restore-${Date.now()}`;

      try {
        // Create temporary cluster for restore testing
        const tempCluster = await this.createTemporaryCluster(tempClusterName);

        // Restore snapshot to temporary cluster
        await this.restoreFromBackup(clusterName, tempClusterName, {
          snapshotId: snapshot.id,
          deliveryType: "automated"
        });

        // Validate restored data
        const validation = await this.validateRestoredData(tempClusterName);

        validationResults.push({
          snapshotId: snapshot.id,
          snapshotDate: snapshot.createdAt,
          valid: validation.success,
          dataIntegrity: validation.integrity,
          validationTime: new Date()
        });

        // Clean up temporary cluster
        await this.atlasAPI.clusters.delete(this.projectId, tempClusterName);

      } catch (error) {
        validationResults.push({
          snapshotId: snapshot.id,
          snapshotDate: snapshot.createdAt,
          valid: false,
          error: error.message
        });
      }
    }

    return {
      totalSnapshots: snapshots.length,
      validSnapshots: validationResults.filter(r => r.valid).length,
      validationResults: validationResults
    };
  }
}

-- SQL-style cloud security configuration concepts
-- Network access control
CREATE SECURITY_GROUP atlas_database_access AS (
  -- Application server access
  ALLOW IP_RANGE '10.0.1.0/24' 
  COMMENT 'Production application servers',

  -- VPC peering for internal access
  ALLOW VPC 'vpc-12345678' 
  COMMENT 'Production VPC peering connection',

  -- Specific analytics server access
  ALLOW IP_ADDRESS '203.0.113.100' 
  COMMENT 'Analytics server - quarterly reports',

  -- Development environment access (temporary)
  ALLOW IP_RANGE '192.168.1.0/24'
  COMMENT 'Development team access'
  EXPIRE_DATE = '2025-09-30'
);

-- Database user management with roles
CREATE USER analytics_service 
WITH PASSWORD = 'secure_password',
     AUTHENTICATION_DATABASE = 'admin';

GRANT ROLE readWrite ON DATABASE ecommerce TO analytics_service;
GRANT ROLE read ON DATABASE analytics TO analytics_service;

-- Custom role for application service
CREATE ROLE order_processor_role AS (
  PRIVILEGES = [
    { database: 'ecommerce', collection: 'orders', actions: ['find', 'insert', 'update'] },
    { database: 'ecommerce', collection: 'inventory', actions: ['find', 'update'] },
    { database: 'ecommerce', collection: 'customers', actions: ['find'] }
  ],
  INHERITANCE = false
);

CREATE USER order_service 
WITH PASSWORD = 'service_password',
     AUTHENTICATION_DATABASE = 'admin';

GRANT ROLE order_processor_role TO order_service;

// Atlas security configuration
class AtlasSecurityManager {
  constructor(atlasAPI, projectId) {
    this.atlasAPI = atlasAPI;
    this.projectId = projectId;
  }

  async configureNetworkSecurity(securityRules) {
    // IP allowlist configuration
    const ipAllowlist = securityRules.allowedIPs.map(rule => ({
      ipAddress: rule.address,
      comment: rule.description,
      ...(rule.expireDate && { deleteAfterDate: rule.expireDate })
    }));

    await this.atlasAPI.networkAccess.createMultiple(this.projectId, ipAllowlist);

    // VPC peering configuration for private network access
    if (securityRules.vpcPeering) {
      for (const vpc of securityRules.vpcPeering) {
        await this.atlasAPI.networkPeering.create(this.projectId, {
          containerId: vpc.containerId,
          providerName: vpc.provider,
          routeTableCidrBlock: vpc.cidrBlock,
          vpcId: vpc.vpcId,
          awsAccountId: vpc.accountId
        });
      }
    }

    // PrivateLink configuration for secure connectivity
    if (securityRules.privateLink) {
      await this.configurePrivateLink(securityRules.privateLink);
    }
  }

  async configurePrivateLink(privateConfig) {
    // AWS PrivateLink endpoint configuration
    const endpoint = await this.atlasAPI.privateEndpoints.create(
      this.projectId,
      {
        providerName: "AWS",
        region: privateConfig.region,
        serviceAttachmentNames: privateConfig.serviceAttachments || []
      }
    );

    return {
      endpointId: endpoint.id,
      serviceName: endpoint.serviceName,
      serviceAttachmentNames: endpoint.serviceAttachmentNames
    };
  }

  async setupDatabaseUsers(userConfigurations) {
    const createdUsers = [];

    for (const userConfig of userConfigurations) {
      // Create custom roles if needed
      if (userConfig.customRoles) {
        for (const role of userConfig.customRoles) {
          await this.createCustomRole(role);
        }
      }

      // Create database user
      const user = await this.atlasAPI.databaseUsers.create(this.projectId, {
        username: userConfig.username,
        password: userConfig.password,
        databaseName: userConfig.authDatabase || "admin",

        roles: userConfig.roles.map(role => ({
          roleName: role.name,
          databaseName: role.database
        })),

        // Scope restrictions
        scopes: userConfig.scopes || [],

        // Authentication restrictions
        ...(userConfig.restrictions && {
          awsIAMType: userConfig.restrictions.awsIAMType,
          ldapAuthType: userConfig.restrictions.ldapAuthType
        })
      });

      createdUsers.push({
        username: user.username,
        roles: user.roles,
        created: new Date()
      });
    }

    return createdUsers;
  }

  async createCustomRole(roleDefinition) {
    return await this.atlasAPI.customRoles.create(this.projectId, {
      roleName: roleDefinition.name,
      privileges: roleDefinition.privileges.map(priv => ({
        resource: {
          db: priv.database,
          collection: priv.collection || ""
        },
        actions: priv.actions
      })),
      inheritedRoles: roleDefinition.inheritedRoles || []
    });
  }

  async rotateUserPasswords(usernames) {
    const rotationResults = [];

    for (const username of usernames) {
      const newPassword = this.generateSecurePassword();

      try {
        await this.atlasAPI.databaseUsers.update(
          this.projectId,
          username,
          { password: newPassword }
        );

        rotationResults.push({
          username: username,
          success: true,
          rotatedAt: new Date()
        });
      } catch (error) {
        rotationResults.push({
          username: username,
          success: false,
          error: error.message
        });
      }
    }

    return rotationResults;
  }
}

// Atlas monitoring and alerting configuration
class AtlasMonitoringManager {
  constructor(atlasAPI, projectId) {
    this.atlasAPI = atlasAPI;
    this.projectId = projectId;
  }

  async setupProductionAlerting(clusterName, alertConfig) {
    const alerts = [
      // Performance alerts
      {
        typeName: "HOST_CPU_USAGE_AVERAGE",
        threshold: alertConfig.cpuThreshold || 80,
        operator: "GREATER_THAN",
        units: "RAW",
        notifications: alertConfig.notifications
      },
      {
        typeName: "HOST_MEMORY_USAGE_AVERAGE", 
        threshold: alertConfig.memoryThreshold || 85,
        operator: "GREATER_THAN",
        units: "RAW",
        notifications: alertConfig.notifications
      },

      // Replication alerts
      {
        typeName: "REPLICATION_LAG",
        threshold: alertConfig.replicationLagThreshold || 60,
        operator: "GREATER_THAN", 
        units: "SECONDS",
        notifications: alertConfig.criticalNotifications
      },

      // Connection alerts
      {
        typeName: "CONNECTIONS_PERCENT",
        threshold: alertConfig.connectionThreshold || 80,
        operator: "GREATER_THAN",
        units: "RAW",
        notifications: alertConfig.notifications
      },

      // Storage alerts
      {
        typeName: "DISK_USAGE_PERCENT",
        threshold: alertConfig.diskThreshold || 75,
        operator: "GREATER_THAN",
        units: "RAW", 
        notifications: alertConfig.notifications
      },

      // Security alerts
      {
        typeName: "TOO_MANY_UNHEALTHY_MEMBERS",
        threshold: 1,
        operator: "GREATER_THAN_OR_EQUAL",
        units: "RAW",
        notifications: alertConfig.criticalNotifications
      }
    ];

    const createdAlerts = [];

    for (const alert of alerts) {
      try {
        const alertResult = await this.atlasAPI.alerts.create(this.projectId, {
          ...alert,
          enabled: true,
          matchers: [
            {
              fieldName: "CLUSTER_NAME",
              operator: "EQUALS",
              value: clusterName
            }
          ]
        });

        createdAlerts.push(alertResult);
      } catch (error) {
        console.error(`Failed to create alert ${alert.typeName}:`, error.message);
      }
    }

    return createdAlerts;
  }

  async createCustomMetricsDashboard(clusterName) {
    // Custom dashboard for business-specific metrics
    const dashboardConfig = {
      name: `${clusterName} - Production Metrics`,

      charts: [
        {
          name: "Order Processing Rate",
          type: "line",
          metricType: "custom",
          query: {
            collection: "orders",
            pipeline: [
              {
                $match: {
                  created_at: { $gte: new Date(Date.now() - 3600000) }  // Last hour
                }
              },
              {
                $group: {
                  _id: {
                    $dateToString: {
                      format: "%Y-%m-%d %H:00:00",
                      date: "$created_at"
                    }
                  },
                  order_count: { $sum: 1 },
                  total_revenue: { $sum: "$total_amount" }
                }
              }
            ]
          }
        },
        {
          name: "Database Response Time",
          type: "area", 
          metricType: "DATABASE_AVERAGE_OPERATION_TIME",
          aggregation: "average"
        },
        {
          name: "Active Connection Distribution",
          type: "stacked-column",
          metricType: "CONNECTIONS",
          groupBy: "replica_set_member"
        }
      ]
    };

    return await this.atlasAPI.monitoring.createDashboard(
      this.projectId,
      dashboardConfig
    );
  }

  async generatePerformanceReport(clusterName, timeframeDays = 7) {
    const endDate = new Date();
    const startDate = new Date(endDate.getTime() - (timeframeDays * 24 * 60 * 60 * 1000));

    // Collect metrics for analysis
    const metrics = await Promise.all([
      this.getMetricData("CPU_USAGE", clusterName, startDate, endDate),
      this.getMetricData("MEMORY_USAGE", clusterName, startDate, endDate),
      this.getMetricData("DISK_IOPS", clusterName, startDate, endDate),
      this.getMetricData("CONNECTIONS", clusterName, startDate, endDate),
      this.getSlowQueryAnalysis(clusterName, startDate, endDate)
    ]);

    const [cpu, memory, diskIOPS, connections, slowQueries] = metrics;

    // Analyze performance trends
    const analysis = {
      cpuTrends: this.analyzeMetricTrends(cpu),
      memoryTrends: this.analyzeMetricTrends(memory),
      diskTrends: this.analyzeMetricTrends(diskIOPS),
      connectionTrends: this.analyzeMetricTrends(connections),
      queryPerformance: this.analyzeQueryPerformance(slowQueries),
      recommendations: []
    };

    // Generate recommendations based on analysis
    analysis.recommendations = this.generatePerformanceRecommendations(analysis);

    return {
      cluster: clusterName,
      timeframe: { start: startDate, end: endDate },
      analysis: analysis,
      generatedAt: new Date()
    };
  }
}