app/utils/db_init.py

"""
Database initialization utilities.
Creates indexes and sets up collections for the SCM microservice.
"""
from app.core.logging import get_logger
from app.nosql import get_database
from app.merchants.constants import SCM_MERCHANTS_COLLECTION
from app.employees.constants import SCM_EMPLOYEES_COLLECTION
from app.access_roles.constants import SCM_ROLES_COLLECTION
from app.core.constants import SCM_AUTH_LOGS_COLLECTION
from app.sales.constants import SCM_SALES_ORDERS_COLLECTION, SCM_RMA_COLLECTION, SCM_CREDIT_NOTES_COLLECTION

logger = get_logger(__name__)


async def create_indexes():
    """
    Create all required database indexes.
    Should be called during application startup.
    """
    try:
        logger.info("Creating database indexes")
        
        # Merchants collection indexes
        await get_database()[SCM_MERCHANTS_COLLECTION].create_index("merchant_id", unique=True)
        await get_database()[SCM_MERCHANTS_COLLECTION].create_index("merchant_name", unique=True)
        await get_database()[SCM_MERCHANTS_COLLECTION].create_index("merchant_type")
        await get_database()[SCM_MERCHANTS_COLLECTION].create_index("contact_email")
        logger.info(f"Created indexes for {SCM_MERCHANTS_COLLECTION}")
        
        # Employees collection indexes
        await get_database()[SCM_EMPLOYEES_COLLECTION].create_index("associate_id", unique=True)
        await get_database()[SCM_EMPLOYEES_COLLECTION].create_index("email", unique=True)
        await get_database()[SCM_EMPLOYEES_COLLECTION].create_index("mobile", unique=True)
        await get_database()[SCM_EMPLOYEES_COLLECTION].create_index("merchant_id")
        await get_database()[SCM_EMPLOYEES_COLLECTION].create_index([
            ("merchant_id", 1),
            ("role_id", 1)
        ])
        logger.info(f"Created indexes for {SCM_EMPLOYEES_COLLECTION}")
        
        # Roles collection indexes
        await get_database()[SCM_ROLES_COLLECTION].create_index([
            ("merchant_id", 1),
            ("role_id", 1)
        ], unique=True)
        await get_database()[SCM_ROLES_COLLECTION].create_index("merchant_id")
        logger.info(f"Created indexes for {SCM_ROLES_COLLECTION}")
        
        # Auth logs collection indexes
        await get_database()[SCM_AUTH_LOGS_COLLECTION].create_index([
            ("merchant_id", 1),
            ("timestamp", -1)
        ])
        await get_database()[SCM_AUTH_LOGS_COLLECTION].create_index([
            ("associate_id", 1),
            ("timestamp", -1)
        ])
        await get_database()[SCM_AUTH_LOGS_COLLECTION].create_index("event_type")
        
        # TTL index for 90-day retention (7776000 seconds)
        await get_database()[SCM_AUTH_LOGS_COLLECTION].create_index(
            "timestamp",
            expireAfterSeconds=7776000
        )
        logger.info(f"Created indexes for {SCM_AUTH_LOGS_COLLECTION}")
        
        # Sales orders collection indexes
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("sales_order_id", unique=True)
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("order_number", unique=True)
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("merchant_id")
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("branch_id")
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("status")
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("order_date")
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index("customer.customer_id")
        await get_database()[SCM_SALES_ORDERS_COLLECTION].create_index([
            ("merchant_id", 1),
            ("order_date", -1)
        ])
        logger.info(f"Created indexes for {SCM_SALES_ORDERS_COLLECTION}")
        
        logger.info("All database indexes created successfully")
        
    except Exception as e:
        logger.error("Failed to create database indexes", exc_info=e)
        raise


async def drop_indexes():
    """
    Drop all indexes (useful for testing).
    """
    try:
        logger.warning("Dropping all database indexes")
        
        await get_database()[SCM_MERCHANTS_COLLECTION].drop_indexes()
        await get_database()[SCM_EMPLOYEES_COLLECTION].drop_indexes()
        await get_database()[SCM_ROLES_COLLECTION].drop_indexes()
        await get_database()[SCM_AUTH_LOGS_COLLECTION].drop_indexes()
        await get_database()[SCM_SALES_ORDERS_COLLECTION].drop_indexes()
        
        logger.info("All database indexes dropped")
        
    except Exception as e:
        logger.error("Failed to drop database indexes", exc_info=e)
        raise