scripts/create_indexes.py

"""
MongoDB Index Creation Script
Author: AI Generated
Created: 2025-11-24 (Fixed for actual schema)
Purpose: Create performance indexes matching actual MongoDB structure
"""

from database import db
from config import settings


def create_all_indexes():
    """
    Create all necessary indexes for optimal performance.
    Based on ACTUAL MongoDB structure from models.txt
    """
    
    print("=" * 60)
    print("🔧 Creating MongoDB Indexes (Corrected Schema)")
    print("=" * 60)
    
    # Payment Collection Indexes
    print("\n📊 Payment Collection:")
    
    # Index for event-specific ticket purchases
    db.payments.create_index(
        [("eventCode", 1), ("status", 1), ("userId", 1)],
        name="idx_payment_event_status_user"
    )
    print("  ✓ Created: idx_payment_event_status_user")
    
    # Index for user RFM calculation
    db.payments.create_index(
        [("userId", 1), ("transactionDate", -1)],
        name="idx_payment_user_date"
    )
    print("  ✓ Created: idx_payment_user_date")
    
    # User Collection Indexes (UserFollows is EMBEDDED)
    print("\n👥 User Collection:")
    
    # Index for finding users who follow a specific event
    # UserFollows is an embedded array, so we use dot notation
    db.users.create_index(
        [("UserFollows.eventCode", 1)],
        name="idx_user_follows_event",
        sparse=True  # Skip documents without UserFollows
    )
    print("  ✓ Created: idx_user_follows_event (embedded array)")
    
    # Index for user status (to filter Active users)
    db.users.create_index(
        [("status", 1)],
        name="idx_user_status"
    )
    print("  ✓ Created: idx_user_status")
    
    # PostSocialMedia Collection Indexes
    print("\n💬 PostSocialMedia Collection:")
    
    # Index for event-specific posts
    db.post_social_media.create_index(
        [("eventCode", 1), ("createdAt", -1)],
        name="idx_post_event_date"
    )
    print("  ✓ Created: idx_post_event_date")
    
    # Index for searching comments (nested in Images.UserCommentPosts)
    # Using wildcard for nested arrays
    db.post_social_media.create_index(
        [("eventCode", 1), ("images.userCommentPosts.commentedAt", -1)],
        name="idx_post_comments",
        sparse=True
    )
    print("  ✓ Created: idx_post_comments (nested array)")
    
    # EventAudienceSegment Collection Indexes
    print("\n🎯 EventAudienceSegment Collection:")
    
    # Index for event owner dashboard
    db.event_audience_segments.create_index(
        [("event_code", 1)],
        name="idx_segment_event"
    )
    print("  ✓ Created: idx_segment_event")
    
    # Index for status filtering
    db.event_audience_segments.create_index(
        [("event_code", 1), ("marketing_content.status", 1)],
        name="idx_segment_event_status"
    )
    print("  ✓ Created: idx_segment_event_status")
    
    # EventSentimentSummary Collection Indexes
    print("\n📊 EventSentimentSummary Collection:")
    
    # Index for event sentiment lookup
    db.event_sentiment_summary.create_index(
        [("event_code", 1), ("last_updated", -1)],
        name="idx_sentiment_event_date"
    )
    print("  ✓ Created: idx_sentiment_event_date")
    
    # SentimentAnalysisResult Collection Indexes
    print("\n💭 SentimentAnalysisResult Collection:")
    
    # Index for event-specific sentiment results
    db.sentiment_results.create_index(
        [("event_code", 1), ("analyzed_at", -1)],
        name="idx_sentiment_result_event_date"
    )
    print("  ✓ Created: idx_sentiment_result_event_date")
    
    # Index for sentiment label filtering
    db.sentiment_results.create_index(
        [("event_code", 1), ("sentiment_label", 1)],
        name="idx_sentiment_event_label"
    )
    print("  ✓ Created: idx_sentiment_event_label")
    
    print("\n" + "=" * 60)
    print("✅ All Indexes Created Successfully!")
    print("=" * 60)
    
    # List all indexes for verification
    print("\n📋 Index Summary:")
    print(f"  Payment: {len(list(db.payments.list_indexes()))} indexes")
    print(f"  User: {len(list(db.users.list_indexes()))} indexes")
    print(f"  PostSocialMedia: {len(list(db.post_social_media.list_indexes()))} indexes")
    print(f"  EventAudienceSegment: {len(list(db.event_audience_segments.list_indexes()))} indexes")
    print(f"  EventSentimentSummary: {len(list(db.event_sentiment_summary.list_indexes()))} indexes")
    print(f"  SentimentAnalysisResult: {len(list(db.sentiment_results.list_indexes()))} indexes")


if __name__ == "__main__":
    create_all_indexes()