services/db_optimize.py

"""
Database optimization script - creates indexes for better query performance.
MongoDB indexes are automatically created on connection, but this script
can be used to verify or recreate them.
"""

import asyncio
import sys
import os

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from services.database import db_service


async def create_indexes():
    """Create database indexes for better query performance."""
    
    print("Creating MongoDB indexes for optimization...")
    print("=" * 60)
    
    await db_service.connect()
    
    if not db_service.collection:
        print("✗ Database not connected")
        return
    
    indexes = [
        # Index on niche for filtering
        ("niche", "niche"),
        
        # Index on created_at for sorting (most common query)
        ("created_at", "created_at"),
        
        # Composite index for niche + created_at (common query pattern)
        ([("niche", 1), ("created_at", -1)], "niche + created_at"),
        
        # Index on generation_method for filtering
        ("generation_method", "generation_method"),
    ]
    
    success_count = 0
    fail_count = 0
    
    for index_spec, index_name in indexes:
        try:
            if isinstance(index_spec, list):
                # Composite index
                await db_service.collection.create_index(index_spec, name=f"idx_{index_name.replace(' ', '_')}")
            else:
                # Single field index
                await db_service.collection.create_index(index_spec, name=f"idx_{index_spec}")
            print(f"✓ Created index: {index_name}")
            success_count += 1
        except Exception as e:
            # Index might already exist
            if "already exists" in str(e).lower() or "duplicate" in str(e).lower():
                print(f"✓ Index already exists: {index_name}")
                success_count += 1
            else:
                print(f"✗ Failed to create index {index_name}: {e}")
                fail_count += 1
    
    print()
    print("=" * 60)
    print(f"Summary: {success_count} indexes created/verified, {fail_count} failed")
    
    await db_service.disconnect()


if __name__ == "__main__":
    asyncio.run(create_indexes())