2025

// Slow: No indexes, scanning entire collection
db.posts.find({
  author: "john_smith",
  published: true,
  tags: { $in: ["mongodb", "database"] },
  created_at: { $gte: ISODate("2025-01-01") }
})

// This query might scan millions of documents
// Taking seconds instead of milliseconds

-- SQL equivalent - also slow without indexes
SELECT post_id, title, content, created_at
FROM posts 
WHERE author = 'john_smith'
  AND published = true
  AND tags LIKE '%mongodb%'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Without proper indexes: full table scan
-- With proper indexes: index seeks + range scan

// Analyze query performance
db.posts.find({
  author: "john_smith",
  published: true,
  created_at: { $gte: ISODate("2025-01-01") }
}).explain("executionStats")

// Key metrics to analyze:
// - executionTimeMillis: Total query execution time
// - totalDocsExamined: Documents scanned
// - totalDocsReturned: Documents returned
// - executionStages: Query execution plan

-- Equivalent SQL explain plan analysis
EXPLAIN (ANALYZE, BUFFERS) 
SELECT post_id, title, created_at
FROM posts
WHERE author = 'john_smith'
  AND published = true
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC;

-- Look for:
-- - Index Scan vs Seq Scan
-- - Rows examined vs rows returned
-- - Buffer usage and I/O costs
-- - Sort operations and memory usage

// Performance baseline measurement
const queryStart = Date.now();

const result = db.posts.find({
  author: "john_smith",
  published: true
}).limit(20);

const executionTime = Date.now() - queryStart;
const documentsExamined = result.explain().executionStats.totalDocsExamined;
const documentsReturned = result.explain().executionStats.totalDocsReturned;

// Performance ratios
const selectivityRatio = documentsReturned / documentsExamined;
const indexEffectiveness = selectivityRatio > 0.1 ? "Good" : "Poor";

// Create indexes for common query patterns
db.posts.createIndex({ "author": 1 })
db.posts.createIndex({ "published": 1 })
db.posts.createIndex({ "created_at": -1 })  // Descending for recent-first queries
db.posts.createIndex({ "tags": 1 })

-- SQL index creation
CREATE INDEX idx_posts_author ON posts (author);
CREATE INDEX idx_posts_published ON posts (published);
CREATE INDEX idx_posts_created_desc ON posts (created_at DESC);
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);  -- For array/text search

-- Analyze index usage
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'posts'
ORDER BY idx_scan DESC;

// Compound index supporting multiple query patterns
db.posts.createIndex({
  "author": 1,
  "published": 1,
  "created_at": -1
})

// This index supports queries like:
// { author: "john_smith" }
// { author: "john_smith", published: true }
// { author: "john_smith", published: true, created_at: { $gte: date } }

// Query using compound index
db.posts.find({
  author: "john_smith",
  published: true,
  created_at: { $gte: ISODate("2025-01-01") }
}).sort({ created_at: -1 }).limit(20)

-- SQL compound index best practices
CREATE INDEX idx_posts_author_published_created ON posts (
  author,           -- Equality conditions first
  published,        -- Additional equality conditions  
  created_at DESC   -- Range/sort conditions last
);

-- Covering index to avoid table lookups
CREATE INDEX idx_posts_covering ON posts (
  author,
  published,
  created_at DESC
) INCLUDE (title, excerpt, view_count);

// Create text index for content search
db.posts.createIndex({
  "title": "text",
  "content": "text", 
  "tags": "text"
}, {
  "weights": {
    "title": 10,    // Title matches are more important
    "content": 5,   // Content matches are less important  
    "tags": 8       // Tag matches are quite important
  }
})

// Optimized text search query
db.posts.find({
  $text: { 
    $search: "mongodb performance optimization",
    $caseSensitive: false
  },
  published: true
}, {
  score: { $meta: "textScore" }
}).sort({ 
  score: { $meta: "textScore" },
  created_at: -1 
})

// Optimized aggregation pipeline
db.posts.aggregate([
  // 1. Filter early to reduce document set
  { 
    $match: { 
      published: true,
      created_at: { $gte: ISODate("2025-01-01") }
    }
  },

  // 2. Limit early if possible
  { $sort: { created_at: -1 } },
  { $limit: 100 },

  // 3. Lookup/join operations on reduced set
  {
    $lookup: {
      from: "users",
      localField: "author_id", 
      foreignField: "_id",
      as: "author_info"
    }
  },

  // 4. Project to reduce memory usage
  {
    $project: {
      title: 1,
      excerpt: 1,
      created_at: 1,
      "author_info.name": 1,
      "author_info.avatar_url": 1,
      view_count: 1,
      comment_count: 1
    }
  }
])

-- Optimized SQL query with similar performance patterns
WITH recent_posts AS (
  SELECT 
    post_id,
    title,
    excerpt, 
    author_id,
    created_at,
    view_count,
    comment_count
  FROM posts
  WHERE published = true
    AND created_at >= '2025-01-01'
  ORDER BY created_at DESC
  LIMIT 100
)
SELECT 
  rp.post_id,
  rp.title,
  rp.excerpt,
  rp.created_at,
  u.name AS author_name,
  u.avatar_url,
  rp.view_count,
  rp.comment_count
FROM recent_posts rp
JOIN users u ON rp.author_id = u.user_id
ORDER BY rp.created_at DESC;

// Monitor and optimize memory usage
db.posts.aggregate([
  { $match: { published: true } },

  // Use $project to reduce document size early
  { 
    $project: {
      title: 1,
      author_id: 1,
      created_at: 1,
      tags: 1,
      view_count: 1
    }
  },

  {
    $group: {
      _id: "$author_id",
      post_count: { $sum: 1 },
      total_views: { $sum: "$view_count" },
      recent_posts: { 
        $push: {
          title: "$title",
          created_at: "$created_at"
        }
      }
    }
  },

  // Sort after grouping to use less memory
  { $sort: { total_views: -1 } },
  { $limit: 50 }
], {
  allowDiskUse: true,  // Enable disk usage for large datasets
  maxTimeMS: 30000     // Set query timeout
})

// Inefficient: Searches entire array for each document
db.posts.find({
  "tags": { $in: ["mongodb", "database", "performance"] }
})

// Better: Use multikey index
db.posts.createIndex({ "tags": 1 })

// More specific: Use compound index for better selectivity
db.posts.createIndex({
  "published": 1,
  "tags": 1,
  "created_at": -1
})

// Query with proper index utilization
db.posts.find({
  published: true,
  tags: "mongodb",
  created_at: { $gte: ISODate("2025-01-01") }
}).sort({ created_at: -1 })

-- Optimized range queries using familiar SQL patterns
SELECT post_id, title, created_at, view_count
FROM posts
WHERE created_at BETWEEN '2025-01-01' AND '2025-08-22'
  AND published = true
  AND view_count >= 1000
ORDER BY created_at DESC, view_count DESC
LIMIT 25;

-- Compound index: (published, created_at, view_count)
-- This supports the WHERE clause efficiently

// Create supporting compound index
db.posts.createIndex({
  "published": 1,      // Equality first
  "created_at": -1,    // Range condition
  "view_count": -1     // Secondary sort
})

// Optimized query
db.posts.find({
  published: true,
  created_at: { 
    $gte: ISODate("2025-01-01"),
    $lte: ISODate("2025-08-22")
  },
  view_count: { $gte: 1000 }
}).sort({
  created_at: -1,
  view_count: -1
}).limit(25)

// Optimized MongoDB connection settings
const client = new MongoClient(uri, {
  maxPoolSize: 50,           // Maximum number of connections
  minPoolSize: 5,            // Minimum number of connections
  maxIdleTimeMS: 30000,      // Close connections after 30 seconds of inactivity
  serverSelectionTimeoutMS: 5000,  // Timeout for server selection
  socketTimeoutMS: 45000,    // Socket timeout
  family: 4                  // Use IPv4
})

// Monitor connection pool metrics
const poolStats = client.db().admin().serverStatus().connections;
console.log(`Active connections: ${poolStats.current}`);
console.log(`Available connections: ${poolStats.available}`);

-- PostgreSQL connection pool configuration
-- (typically configured in application/connection pooler)
-- max_connections = 200
-- shared_buffers = 256MB
-- effective_cache_size = 1GB
-- work_mem = 4MB

-- Monitor connection usage
SELECT 
  datname,
  usename,
  client_addr,
  state,
  query_start,
  now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

// Configure read preferences for optimal performance
const readOptions = {
  readPreference: 'secondaryPreferred',  // Use secondary nodes when available
  readConcern: { level: 'local' },       // Local read concern for performance
  maxTimeMS: 10000                       // Query timeout
}

// Different read preferences for different query types
const realtimeData = db.posts.find(
  { published: true },
  { readPreference: 'primary' }  // Real-time data requires primary reads
)

const analyticsData = db.posts.aggregate([
  { $match: { created_at: { $gte: ISODate("2025-01-01") } } },
  { $group: { _id: "$author_id", count: { $sum: 1 } } }
], {
  readPreference: 'secondary',   // Analytics can use secondary reads
  allowDiskUse: true
})

// Custom performance monitoring
class MongoPerformanceMonitor {
  constructor(db) {
    this.db = db;
    this.metrics = new Map();
  }

  async trackQuery(queryName, queryFn) {
    const startTime = Date.now();
    const startStats = await this.db.serverStatus();

    const result = await queryFn();

    const endTime = Date.now();
    const endStats = await this.db.serverStatus();

    const metrics = {
      executionTime: endTime - startTime,
      documentsExamined: endStats.opcounters.query - startStats.opcounters.query,
      memoryUsage: endStats.mem.resident - startStats.mem.resident,
      indexHits: endStats.indexCounters?.hits || 0,
      timestamp: new Date()
    };

    this.metrics.set(queryName, metrics);
    return result;
  }

  getSlowQueries(thresholdMs = 1000) {
    return Array.from(this.metrics.entries())
      .filter(([_, metrics]) => metrics.executionTime > thresholdMs)
      .sort((a, b) => b[1].executionTime - a[1].executionTime);
  }
}

// Enable profiler for slow operations
db.setProfilingLevel(2, { slowms: 100 });

// Analyze slow queries
db.system.profile.find({
  ts: { $gte: new Date(Date.now() - 3600000) },  // Last hour
  millis: { $gte: 100 }  // Operations taking more than 100ms
}).sort({ millis: -1 }).limit(10).forEach(
  op => {
    console.log(`Command: ${JSON.stringify(op.command)}`);
    console.log(`Duration: ${op.millis}ms`);
    console.log(`Docs examined: ${op.docsExamined}`);
    console.log(`Docs returned: ${op.nreturned}`);
    console.log('---');
  }
);

-- PostgreSQL slow query analysis
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE mean_time > 100  -- Queries averaging more than 100ms
ORDER BY mean_time DESC
LIMIT 20;

-- Index usage statistics
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE tablename = 'posts'
  AND n_distinct > 100;

// Performance-optimized denormalized structure
{
  "_id": ObjectId("..."),
  "post_id": "post_12345",
  "title": "MongoDB Performance Tips",
  "content": "...",
  "created_at": ISODate("2025-08-22"),

  // Denormalized author data for read performance
  "author": {
    "user_id": ObjectId("..."),
    "name": "John Smith",
    "avatar_url": "https://example.com/avatar.jpg",
    "follower_count": 1250
  },

  // Precalculated statistics
  "stats": {
    "view_count": 1547,
    "like_count": 89,
    "comment_count": 23,
    "last_engagement": ISODate("2025-08-22T10:30:00Z")
  },

  // Recent comments embedded for fast display
  "recent_comments": [
    {
      "comment_id": ObjectId("..."),
      "author_name": "Jane Doe", 
      "text": "Great article!",
      "created_at": ISODate("2025-08-22T09:15:00Z")
    }
  ]
}

-- SQL-style schema optimization
CREATE TABLE posts (
  post_id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL,

  -- Separate frequently-queried fields
  published BOOLEAN NOT NULL DEFAULT false,
  featured BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- Index-friendly status enumeration
  status VARCHAR(20) NOT NULL DEFAULT 'draft',

  -- Separate large text fields that aren't frequently filtered
  title VARCHAR(255) NOT NULL,
  excerpt TEXT,
  content TEXT,

  -- Precalculated values for performance
  view_count INTEGER DEFAULT 0,
  like_count INTEGER DEFAULT 0,
  comment_count INTEGER DEFAULT 0
);

-- Indexes supporting common query patterns
CREATE INDEX idx_posts_author_published ON posts (author_id, published, created_at DESC);
CREATE INDEX idx_posts_status_featured ON posts (status, featured, created_at DESC);
CREATE INDEX idx_posts_engagement ON posts (like_count DESC, view_count DESC) WHERE published = true;

-- QueryLeaf analyzes SQL patterns and suggests MongoDB optimizations
WITH popular_posts AS (
  SELECT 
    p.post_id,
    p.title,
    p.author_id,
    p.created_at,
    p.view_count,
    u.name AS author_name,
    u.follower_count
  FROM posts p
  JOIN users u ON p.author_id = u.user_id
  WHERE p.published = true
    AND p.view_count > 1000
    AND p.created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
  author_name,
  COUNT(*) AS popular_post_count,
  SUM(view_count) AS total_views,
  AVG(view_count) AS avg_views_per_post,
  MAX(follower_count) AS follower_count
FROM popular_posts
GROUP BY author_id, author_name, follower_count
HAVING COUNT(*) >= 3
ORDER BY total_views DESC
LIMIT 20;

-- QueryLeaf automatically:
-- 1. Creates optimal compound indexes
-- 2. Uses aggregation pipeline for complex JOINs
-- 3. Implements proper $lookup and $group stages
-- 4. Provides index recommendations
-- 5. Suggests schema denormalization opportunities

// Load testing framework
class MongoLoadTest {
  async simulateLoad(concurrency, duration) {
    const promises = [];
    const startTime = Date.now();

    for (let i = 0; i < concurrency; i++) {
      promises.push(this.runLoadTest(startTime + duration));
    }

    const results = await Promise.all(promises);
    return this.aggregateResults(results);
  }

  async runLoadTest(endTime) {
    const results = [];

    while (Date.now() < endTime) {
      const start = Date.now();

      // Simulate real user queries
      await db.posts.find({
        published: true,
        created_at: { $gte: new Date(Date.now() - 86400000) }
      }).sort({ created_at: -1 }).limit(20).toArray();

      results.push(Date.now() - start);

      // Simulate user think time
      await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
    }

    return results;
  }
}

-- Create performance monitoring views
CREATE VIEW slow_operations AS
SELECT 
  collection,
  operation_type,
  AVG(duration_ms) as avg_duration,
  MAX(duration_ms) as max_duration,
  COUNT(*) as operation_count,
  SUM(docs_examined) as total_docs_examined,
  SUM(docs_returned) as total_docs_returned
FROM performance_log
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
  AND duration_ms > 100
GROUP BY collection, operation_type
ORDER BY avg_duration DESC;

-- Alert on performance degradation
SELECT 
  collection,
  operation_type,
  avg_duration,
  'Performance Alert: High average query time' as alert_message
FROM slow_operations
WHERE avg_duration > 500;  -- Alert if average > 500ms

-- SQL schema with built-in validation
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  age INTEGER CHECK (age >= 13 AND age <= 120),
  status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'suspended')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  profile JSONB,
  CONSTRAINT valid_profile CHECK (jsonb_typeof(profile->'preferences') = 'object')
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total_amount DECIMAL(10,2) CHECK (total_amount > 0),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))
);

// Inconsistent MongoDB documents without validation
{
  "_id": ObjectId("..."),
  "email": "[email protected]",
  "age": 25,
  "status": "active",
  "created_at": ISODate("2025-08-21")
}

{
  "_id": ObjectId("..."),
  "email": "invalid-email",  // Invalid email format
  "age": -5,                 // Invalid age
  "status": "unknown",       // Invalid status value
  "createdAt": "2025-08-21", // Different field name and format
  "profile": "not-an-object" // Wrong data type
}

// Create collection with validation schema
db.createCollection("users", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["email", "age", "status"],
      properties: {
        email: {
          bsonType: "string",
          pattern: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$",
          description: "Must be a valid email address"
        },
        age: {
          bsonType: "int",
          minimum: 13,
          maximum: 120,
          description: "Must be an integer between 13 and 120"
        },
        status: {
          enum: ["active", "inactive", "suspended"],
          description: "Must be one of: active, inactive, suspended"
        },
        profile: {
          bsonType: "object",
          properties: {
            firstName: { bsonType: "string" },
            lastName: { bsonType: "string" },
            preferences: {
              bsonType: "object",
              properties: {
                notifications: { bsonType: "bool" },
                theme: { enum: ["light", "dark", "auto"] }
              }
            }
          }
        },
        created_at: {
          bsonType: "date",
          description: "Must be a valid date"
        }
      },
      additionalProperties: false
    }
  },
  validationAction: "error",
  validationLevel: "strict"
})

-- Create unique indexes for constraint enforcement
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

-- Prevent duplicate entries using SQL patterns
INSERT INTO users (email, age, status)
VALUES ('[email protected]', 28, 'active')
ON CONFLICT (email) 
DO UPDATE SET 
  age = EXCLUDED.age,
  status = EXCLUDED.status,
  updated_at = CURRENT_TIMESTAMP;

// MongoDB equivalent using validation expressions
db.createCollection("products", {
  validator: {
    $expr: {
      $and: [
        { $gte: ["$price", 0] },
        { $lte: ["$price", 10000] },
        { $gt: ["$quantity", 0] },
        { 
          $in: ["$category", ["electronics", "clothing", "books", "home", "sports"]]
        },
        {
          $cond: {
            if: { $eq: ["$status", "sale"] },
            then: { $and: [
              { $ne: ["$sale_price", null] },
              { $lt: ["$sale_price", "$price"] }
            ]},
            else: true
          }
        }
      ]
    }
  }
})

-- SQL-style reference validation
SELECT COUNT(*) FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;  -- Find orphaned orders

-- Enforce referential integrity in application logic
INSERT INTO orders (user_id, total_amount, status)
SELECT 'user123', 99.99, 'pending'
WHERE EXISTS (
  SELECT 1 FROM users 
  WHERE _id = 'user123' AND status = 'active'
);

// Validation that depends on document state
db.createCollection("orders", {
  validator: {
    $expr: {
      $switch: {
        branches: [
          {
            case: { $eq: ["$status", "completed"] },
            then: {
              $and: [
                { $ne: ["$payment_method", null] },
                { $ne: ["$shipping_address", null] },
                { $gte: ["$total_amount", 0.01] },
                { $ne: ["$completed_at", null] }
              ]
            }
          },
          {
            case: { $eq: ["$status", "cancelled"] },
            then: {
              $and: [
                { $ne: ["$cancelled_at", null] },
                { $ne: ["$cancellation_reason", null] }
              ]
            }
          },
          {
            case: { $in: ["$status", ["pending", "processing"]] },
            then: {
              $and: [
                { $eq: ["$completed_at", null] },
                { $eq: ["$cancelled_at", null] }
              ]
            }
          }
        ],
        default: true
      }
    }
  }
})

// Ensure data consistency across fields
db.createCollection("events", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["title", "start_date", "end_date", "status"],
      properties: {
        title: { bsonType: "string", minLength: 3, maxLength: 100 },
        start_date: { bsonType: "date" },
        end_date: { bsonType: "date" },
        status: { enum: ["draft", "published", "archived"] },
        registration_deadline: { bsonType: "date" },
        max_attendees: { bsonType: "int", minimum: 1 },
        current_attendees: { bsonType: "int", minimum: 0 }
      }
    },
    $expr: {
      $and: [
        // End date must be after start date
        { $lte: ["$start_date", "$end_date"] },
        // Registration deadline must be before start date
        {
          $cond: {
            if: { $ne: ["$registration_deadline", null] },
            then: { $lt: ["$registration_deadline", "$start_date"] },
            else: true
          }
        },
        // Current attendees cannot exceed maximum
        {
          $cond: {
            if: { $ne: ["$max_attendees", null] },
            then: { $lte: ["$current_attendees", "$max_attendees"] },
            else: true
          }
        }
      ]
    }
  }
})

// Comprehensive data type validation
db.createCollection("financial_records", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["account_id", "transaction_date", "amount", "type"],
      properties: {
        account_id: {
          bsonType: "objectId",
          description: "Must be a valid ObjectId"
        },
        transaction_date: {
          bsonType: "date",
          description: "Must be a valid date"
        },
        amount: {
          bsonType: "decimal",
          description: "Must be a decimal number"
        },
        type: {
          enum: ["debit", "credit"],
          description: "Must be either debit or credit"
        },
        description: {
          bsonType: "string",
          minLength: 1,
          maxLength: 500,
          description: "Must be a non-empty string"
        },
        metadata: {
          bsonType: "object",
          properties: {
            source_system: { bsonType: "string" },
            batch_id: { bsonType: "string" },
            processed_by: { bsonType: "string" }
          },
          additionalProperties: false
        }
      },
      additionalProperties: false
    }
  }
})

// Validate array contents and structure
db.createCollection("user_profiles", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      properties: {
        user_id: { bsonType: "objectId" },
        skills: {
          bsonType: "array",
          minItems: 1,
          maxItems: 20,
          uniqueItems: true,
          items: {
            bsonType: "object",
            required: ["name", "level"],
            properties: {
              name: { 
                bsonType: "string",
                minLength: 2,
                maxLength: 50
              },
              level: {
                bsonType: "int",
                minimum: 1,
                maximum: 10
              },
              verified: { bsonType: "bool" }
            }
          }
        },
        contact_methods: {
          bsonType: "array",
          items: {
            bsonType: "object",
            required: ["type", "value"],
            properties: {
              type: { enum: ["email", "phone", "linkedin", "github"] },
              value: { bsonType: "string" },
              primary: { bsonType: "bool" }
            }
          }
        }
      }
    }
  }
})

-- Check constraint equivalent
CREATE TABLE products (
  _id OBJECTID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0 AND price < 10000),
  category VARCHAR(50) CHECK (category IN ('electronics', 'clothing', 'books')),
  quantity INTEGER CHECK (quantity >= 0),
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'discontinued')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Validate data integrity using SQL patterns
SELECT 
  _id,
  name,
  price,
  quantity,
  CASE 
    WHEN price <= 0 THEN 'Invalid price: must be positive'
    WHEN price >= 10000 THEN 'Invalid price: exceeds maximum'
    WHEN quantity < 0 THEN 'Invalid quantity: cannot be negative'
    WHEN category NOT IN ('electronics', 'clothing', 'books') THEN 'Invalid category'
    ELSE 'Valid'
  END AS validation_status
FROM products
WHERE validation_status != 'Valid';

-- Enforce referential integrity
SELECT o.order_id, o.user_id, 'Orphaned order' AS issue
FROM orders o
LEFT JOIN users u ON o.user_id = u._id
WHERE u._id IS NULL;

// Provide meaningful error messages
db.createCollection("customers", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["email", "phone"],
      properties: {
        email: {
          bsonType: "string",
          pattern: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"
        },
        phone: {
          bsonType: "string",
          pattern: "^\\+?[1-9]\\d{1,14}$"
        }
      }
    },
    $expr: {
      $and: [
        {
          $cond: {
            if: { $ne: [{ $type: "$email" }, "string"] },
            then: { $literal: false },
            else: true
          }
        }
      ]
    }
  },
  validationAction: "error"
})

-- Handle validation failures gracefully
INSERT INTO customers (email, phone, status)
SELECT 
  email,
  phone,
  CASE 
    WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN 'active'
    ELSE 'needs_verification'
  END
FROM staging_customers
WHERE email IS NOT NULL 
  AND phone IS NOT NULL;

-- Track validation failures for review
INSERT INTO validation_errors (
  collection_name,
  document_data,
  error_message,
  error_date
)
SELECT 
  'customers',
  JSON_BUILD_OBJECT(
    'email', email,
    'phone', phone
  ),
  'Invalid email format',
  CURRENT_TIMESTAMP
FROM staging_customers
WHERE NOT email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

// Measure validation performance
db.runCommand({
  collMod: "large_collection",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["required_field"],
      properties: {
        indexed_field: { bsonType: "string" },
        optional_field: { bsonType: "int" }
      }
    }
  },
  validationLevel: "moderate"  // Validate only new inserts and updates
})

// Monitor validation performance
db.serverStatus().metrics.document.validation

// Apply validation only to specific operations
db.createCollection("logs", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["timestamp", "level", "message"],
      properties: {
        timestamp: { bsonType: "date" },
        level: { enum: ["debug", "info", "warn", "error", "fatal"] },
        message: { bsonType: "string", maxLength: 1000 }
      }
    }
  },
  validationLevel: "moderate",  // Only validate inserts and updates
  validationAction: "warn"      // Log warnings instead of rejecting
})

-- Test validation rules systematically
WITH test_cases AS (
  SELECT 'valid_user' AS test_name, '[email protected]' AS email, 25 AS age, 'active' AS status
  UNION ALL
  SELECT 'invalid_email', 'not-an-email', 25, 'active'
  UNION ALL
  SELECT 'invalid_age', '[email protected]', -5, 'active'
  UNION ALL
  SELECT 'invalid_status', '[email protected]', 25, 'unknown'
)
SELECT 
  test_name,
  CASE 
    WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
         AND age BETWEEN 13 AND 120
         AND status IN ('active', 'inactive', 'suspended')
    THEN 'PASS'
    ELSE 'FAIL'
  END AS validation_result,
  email, age, status
FROM test_cases;

// Monitor validation effectiveness
db.createView("validation_metrics", "validation_logs", [
  {
    $group: {
      _id: {
        collection: "$collection",
        error_type: "$error_type",
        date: { $dateToString: { format: "%Y-%m-%d", date: "$timestamp" } }
      },
      error_count: { $sum: 1 },
      documents_affected: { $addToSet: "$document_id" }
    }
  },
  {
    $project: {
      collection: "$_id.collection",
      error_type: "$_id.error_type", 
      date: "$_id.date",
      error_count: 1,
      unique_documents: { $size: "$documents_affected" }
    }
  },
  { $sort: { date: -1, error_count: -1 } }
])

// Gradually introduce validation
// Step 1: Validate with warnings
db.runCommand({
  collMod: "existing_collection",
  validator: { /* validation rules */ },
  validationLevel: "moderate",
  validationAction: "warn"
})

// Step 2: Clean up existing data
db.existing_collection.find({
  $or: [
    { email: { $not: /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/ } },
    { age: { $not: { $gte: 13, $lte: 120 } } }
  ]
}).forEach(function(doc) {
  // Fix or flag problematic documents
  if (doc.email && !doc.email.match(/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/)) {
    doc._validation_issues = doc._validation_issues || [];
    doc._validation_issues.push("invalid_email");
  }
  db.existing_collection.replaceOne({ _id: doc._id }, doc);
})

// Step 3: Enable strict validation
db.runCommand({
  collMod: "existing_collection",
  validationAction: "error"
})