backend/app/database.py

# [File: backend/app/database.py]
# PATCH: Thêm user_foods_collection + hướng dẫn đổi DB_NAME qua env var
import os
import certifi
from dotenv import load_dotenv
from pymongo import MongoClient

load_dotenv()

MONGO_URI = os.getenv("MONGO_URI") or os.getenv("MONGODB_URI") or "mongodb://localhost:27017"
# ✅ PATCH: Đổi default thành "health_ai_db" nếu muốn dùng DB đó
# Hoặc set env var DB_NAME=health_ai_db trên Render (khuyến nghị)
DB_NAME = os.getenv("DB_NAME", "saman_fitness")  # → đổi thành "health_ai_db" nếu cần

safe_uri = MONGO_URI.split("@")[-1] if "@" in MONGO_URI else MONGO_URI
print(f"🔌 Connecting to MongoDB: {safe_uri}")
print(f"📂 Database Name: {DB_NAME}")

client = None
db = None

users_collection = None
foods_collection = None
nutrition_collection = None
story_logs_collection = None
workout_history_collection = None
custom_plans_collection = None
recipes_collection = None
water_collection = None
supplements_collection = None
user_foods_collection = None   # ✅ NEW: My Food per-user
exercises_collection = None    # ✅ NEW: Exercise library
video_analysis_collection = None  # ✅ NEW: Video analysis sessions

try:
    if "localhost" in MONGO_URI or "127.0.0.1" in MONGO_URI:
        print("⚠️  Detected Localhost. Disabling SSL.")
        client = MongoClient(MONGO_URI, serverSelectionTimeoutMS=5000)
    else:
        print("☁️  Detected Cloud DB. Enabling SSL.")
        client = MongoClient(MONGO_URI, tlsCAFile=certifi.where(), serverSelectionTimeoutMS=5000)

    db = client[DB_NAME]

    users_collection              = db["users"]
    foods_collection              = db["foods"]
    nutrition_collection          = db["nutrition_plans"]
    story_logs_collection         = db["story_logs"]
    workout_history_collection    = db["workout_history"]
    custom_plans_collection       = db["custom_plans"]
    recipes_collection            = db["recipes"]
    water_collection              = db["water_logs"]
    supplements_collection        = db["supplement_logs"]
    user_foods_collection         = db["user_foods"]   # ✅ NEW
    exercises_collection          = db["exercises"]     # ✅ NEW
    video_analysis_collection     = db["video_analysis"]  # ✅ NEW

    client.admin.command('ping')
    print("✅ Successfully connected to MongoDB!")

except Exception as e:
    print(f"❌ MongoDB Connection Error: {e}")
    print("⚠️  Continuing without database connection (some features may not work)")
    client = None
    db = None
    users_collection = None
    foods_collection = None
    nutrition_collection = None
    story_logs_collection = None
    workout_history_collection = None
    custom_plans_collection = None
    recipes_collection = None
    water_collection = None
    supplements_collection = None
    user_foods_collection = None
    exercises_collection = None
    video_analysis_collection = None


async def create_indexes():
    """Create indexes for video analysis collection to optimize pagination queries."""
    if video_analysis_collection is None:
        print("⚠️  Cannot create indexes: video_analysis_collection is None")
        return

    try:
        print("⏳ Creating indexes for video_analysis_collection...")
        
        # Create index on created_at for sorting (descending)
        await video_analysis_collection.create_index([("created_at", -1)])
        print("✅ Index created: created_at (-1)")
        
        # Create index on status for filtering
        await video_analysis_collection.create_index([("status", 1)])
        print("✅ Index created: status (1)")
        
        # Create compound index for status + created_at (most common query pattern)
        await video_analysis_collection.create_index([("status", 1), ("created_at", -1)])
        print("✅ Index created: status (1), created_at (-1)")
        
        print("✅ All indexes created successfully")
    except Exception as e:
        print(f"❌ Failed to create indexes: {e}")


async def init_database_indexes():
    """Initialize database indexes - call this during app startup."""
    await create_indexes()