Index Management

/**
 * Create or ensure index exists on specified field
 * @param {object} options - Index configuration options
 * @param {function} callback - Optional callback function (err) => {}
 */
ensureIndex(options, callback);

/**
 * Index configuration options
 */
interface IndexOptions {
  fieldName: string;           // Field name to index (required)
  unique?: boolean;           // Enforce unique constraint (default: false)
  sparse?: boolean;           // Create sparse index, ignoring null/undefined values (default: false)
  expireAfterSeconds?: number; // TTL in seconds for automatic document expiration
}

const db = new Datastore({ filename: './users.db', autoload: true });

// Basic field index
db.ensureIndex({ fieldName: 'email' }, (err) => {
  if (err) {
    console.error('Failed to create email index:', err);
  } else {
    console.log('Email index created successfully');
  }
});

// Unique index (prevents duplicate values)
db.ensureIndex({ fieldName: 'username', unique: true }, (err) => {
  if (err) {
    console.error('Failed to create unique username index:', err);
  } else {
    console.log('Unique username index created');
  }
});

// Sparse index (ignores null/undefined values)
db.ensureIndex({ fieldName: 'phoneNumber', sparse: true }, (err) => {
  if (!err) {
    console.log('Sparse phone number index created');
  }
});

// TTL index for automatic expiration
db.ensureIndex({ 
  fieldName: 'expiresAt', 
  expireAfterSeconds: 3600  // 1 hour
}, (err) => {
  if (!err) {
    console.log('TTL index created - documents expire after 1 hour');
  }
});

// Compound options
db.ensureIndex({ 
  fieldName: 'sessionToken', 
  unique: true, 
  sparse: true,
  expireAfterSeconds: 1800  // 30 minutes
}, (err) => {
  if (!err) {
    console.log('Unique, sparse TTL index on sessionToken created');
  }
});

// Index on nested fields using dot notation
db.ensureIndex({ fieldName: 'profile.email' }, (err) => {
  if (!err) {
    console.log('Index created on nested email field');
  }
});

/**
 * Remove index from specified field
 * @param {string} fieldName - Name of field to remove index from
 * @param {function} callback - Optional callback function (err) => {}
 */
removeIndex(fieldName, callback);

const db = new Datastore({ filename: './users.db', autoload: true });

// Remove single index
db.removeIndex('email', (err) => {
  if (err) {
    console.error('Failed to remove email index:', err);
  } else {
    console.log('Email index removed successfully');
  }
});

// Remove nested field index
db.removeIndex('profile.email', (err) => {
  if (!err) {
    console.log('Nested email index removed');
  }
});

// Note: Cannot remove the automatic _id index
db.removeIndex('_id', (err) => {
  console.log('Cannot remove _id index:', err.message);
});

/**
 * Database index properties
 */
interface IndexProperties {
  indexes: { [fieldName: string]: Index };     // Map of field names to Index objects
  ttlIndexes: { [fieldName: string]: number }; // Map of TTL field names to expiration seconds
}

const db = new Datastore({ filename: './users.db', autoload: true });

// List all indexed fields
console.log('Indexed fields:', Object.keys(db.indexes));

// Check if specific field is indexed
if (db.indexes['email']) {
  console.log('Email field is indexed');
} else {
  console.log('Email field is not indexed');
}

// List TTL indexes
console.log('TTL indexes:', db.ttlIndexes);
Object.keys(db.ttlIndexes).forEach(field => {
  console.log(`- ${field}: expires after ${db.ttlIndexes[field]} seconds`);
});

// Index count
console.log('Total indexes:', Object.keys(db.indexes).length);

// Check for automatic _id index
console.log('_id index exists:', !!db.indexes['_id']);

/**
 * TTL index configuration for automatic document expiration
 * Documents are automatically removed when:
 * document[fieldName] + expireAfterSeconds < currentTime
 */
interface TTLBehavior {
  fieldName: string;           // Must be a Date field
  expireAfterSeconds: number;  // Seconds after field date when document expires
}

const db = new Datastore({ filename: './sessions.db', autoload: true });

// Create TTL index for session expiration
db.ensureIndex({ 
  fieldName: 'createdAt', 
  expireAfterSeconds: 1800  // 30 minutes
}, (err) => {
  if (!err) {
    console.log('Session TTL index created');
  }
});

// Insert documents with TTL field
const now = new Date();
db.insert([
  { sessionId: 'sess_1', userId: 'user1', createdAt: now },
  { sessionId: 'sess_2', userId: 'user2', createdAt: new Date(now.getTime() - 1000000) }
], (err, docs) => {
  if (!err) {
    console.log('Sessions inserted with TTL timestamps');
  }
});

// TTL for log entries with custom expiration field
const logsDb = new Datastore({ filename: './logs.db', autoload: true });

logsDb.ensureIndex({ 
  fieldName: 'expiresAt', 
  expireAfterSeconds: 0  // Expire exactly at the specified time
}, (err) => {
  if (!err) {
    console.log('Log expiration index created');
  }
});

// Insert log with specific expiration time
const expireTime = new Date();
expireTime.setHours(expireTime.getHours() + 24); // Expire in 24 hours

logsDb.insert({
  message: 'System started',
  level: 'info',
  timestamp: new Date(),
  expiresAt: expireTime
}, (err, doc) => {
  if (!err) {
    console.log('Log entry inserted with 24-hour expiration');
  }
});

// TTL for temporary data
const tempDb = new Datastore({ filename: './temp.db', autoload: true });

tempDb.ensureIndex({ 
  fieldName: 'validUntil', 
  expireAfterSeconds: 300  // 5 minutes after validUntil date
}, (err) => {
  if (!err) {
    console.log('Temporary data TTL index created');
  }
});

/**
 * Index performance characteristics
 */
interface IndexPerformance {
  memoryUsage: 'proportional_to_document_count';  // Each index requires memory
  insertSpeed: 'slower_with_more_indexes';        // More indexes = slower inserts
  querySpeed: 'faster_on_indexed_fields';         // Indexed queries are much faster
  uniqueConstraints: 'enforced_automatically';    // Unique indexes prevent duplicates
  sparseIndexes: 'ignore_null_undefined';         // Sparse indexes save space
}

const db = new Datastore({ filename: './products.db', autoload: true });

// Performance-conscious index creation
// Index frequently queried fields
db.ensureIndex({ fieldName: 'category' });  // Often used in filters
db.ensureIndex({ fieldName: 'price' });     // Often used in range queries
db.ensureIndex({ fieldName: 'sku', unique: true }); // Business requirement

// Avoid over-indexing
// Don't index fields that are rarely queried
// db.ensureIndex({ fieldName: 'description' }); // Rarely queried, skip

// Use sparse indexes for optional fields
db.ensureIndex({ fieldName: 'discountCode', sparse: true }); // Many nulls expected

// Measure query performance
const startTime = Date.now();
db.find({ category: 'electronics', price: { $lte: 500 } }, (err, products) => {
  const queryTime = Date.now() - startTime;
  console.log(`Query completed in ${queryTime}ms with ${products.length} results`);
});

// Monitor index effectiveness
db.find({ category: 'electronics' }, (err, products) => {
  // This should be fast with category index
  console.log('Found products by category (indexed):', products.length);
});

db.find({ description: { $regex: /smartphone/i } }, (err, products) => {
  // This will be slower without description index
  console.log('Found products by description (not indexed):', products.length);
});

// 1. Index frequently queried fields
db.ensureIndex({ fieldName: 'status' });      // if you often query by status
db.ensureIndex({ fieldName: 'userId' });      // if you often query by user

// 2. Use unique indexes for business constraints
db.ensureIndex({ fieldName: 'email', unique: true });
db.ensureIndex({ fieldName: 'productCode', unique: true });

// 3. Use sparse indexes for optional fields to save memory
db.ensureIndex({ fieldName: 'phoneNumber', sparse: true });
db.ensureIndex({ fieldName: 'middleName', sparse: true });

// 4. Create compound queries efficiently by indexing the most selective field
// For queries like: { category: 'books', price: { $lt: 20 } }
// If category has fewer distinct values, index price instead:
db.ensureIndex({ fieldName: 'price' });

// 5. Use TTL indexes for cleanup automation
db.ensureIndex({ 
  fieldName: 'createdAt', 
  expireAfterSeconds: 86400  // Auto-remove day-old records
});

// 6. Remove unused indexes to improve insert performance
db.removeIndex('oldFieldName');

// 7. Monitor index usage and performance
console.log('Current indexes:', Object.keys(db.indexes));
console.log('TTL indexes:', db.ttlIndexes);