orion/app/modules/catalog/services/catalog_service.py

# app/modules/catalog/services/catalog_service.py
"""
Catalog service for storefront product browsing.

This module provides:
- Public product catalog retrieval
- Product search functionality
- Product detail retrieval

Note: This is distinct from the product_service which handles
store product management. The catalog service is for public
storefront operations only.
"""

import logging

from sqlalchemy import or_
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session, joinedload

from app.modules.catalog.exceptions import (
    ProductNotFoundException,
    ProductValidationException,
)
from app.modules.catalog.models import Product, ProductTranslation

logger = logging.getLogger(__name__)


class CatalogService:
    """Service for public catalog browsing operations."""

    def get_product(self, db: Session, store_id: int, product_id: int) -> Product:
        """
        Get a product from store catalog.

        Args:
            db: Database session
            store_id: Store ID
            product_id: Product ID

        Returns:
            Product object

        Raises:
            ProductNotFoundException: If product not found
        """
        product = (
            db.query(Product)
            .filter(Product.id == product_id, Product.store_id == store_id)
            .first()
        )

        if not product:
            raise ProductNotFoundException(f"Product {product_id} not found")

        return product

    def get_catalog_products(
        self,
        db: Session,
        store_id: int,
        skip: int = 0,
        limit: int = 100,
        is_featured: bool | None = None,
    ) -> tuple[list[Product], int]:
        """
        Get products in store catalog for public display.

        Only returns active products visible to customers.

        Args:
            db: Database session
            store_id: Store ID
            skip: Pagination offset
            limit: Pagination limit
            is_featured: Filter by featured status

        Returns:
            Tuple of (products, total_count)
        """
        try:
            # Always filter for active products only
            query = db.query(Product).filter(
                Product.store_id == store_id,
                Product.is_active == True,
            )

            if is_featured is not None:
                query = query.filter(Product.is_featured == is_featured)

            total = query.count()
            products = query.offset(skip).limit(limit).all()

            return products, total

        except SQLAlchemyError as e:
            logger.error(f"Error getting catalog products: {str(e)}")
            raise ProductValidationException("Failed to retrieve products")

    def search_products(
        self,
        db: Session,
        store_id: int,
        query: str,
        skip: int = 0,
        limit: int = 50,
        language: str = "en",
    ) -> tuple[list[Product], int]:
        """
        Search products in store catalog.

        Searches across:
        - Product title and description (from translations)
        - Product SKU, brand, and GTIN

        Args:
            db: Database session
            store_id: Store ID
            query: Search query string
            skip: Pagination offset
            limit: Pagination limit
            language: Language for translation search (default: 'en')

        Returns:
            Tuple of (products, total_count)
        """
        try:
            # Prepare search pattern for LIKE queries
            search_pattern = f"%{query}%"

            # Use subquery to get distinct IDs (PostgreSQL can't compare JSON for DISTINCT)
            id_subquery = (
                db.query(Product.id)
                .outerjoin(
                    ProductTranslation,
                    (Product.id == ProductTranslation.product_id)
                    & (ProductTranslation.language == language),
                )
                .filter(
                    Product.store_id == store_id,
                    Product.is_active == True,
                )
                .filter(
                    or_(
                        # Search in translations
                        ProductTranslation.title.ilike(search_pattern),
                        ProductTranslation.description.ilike(search_pattern),
                        ProductTranslation.short_description.ilike(search_pattern),
                        # Search in product fields
                        Product.store_sku.ilike(search_pattern),
                        Product.brand.ilike(search_pattern),
                        Product.gtin.ilike(search_pattern),
                    )
                )
                .distinct()
                .subquery()
            )

            base_query = db.query(Product).filter(
                Product.id.in_(db.query(id_subquery.c.id))
            )

            # Get total count
            total = base_query.count()

            # Get paginated results with eager loading for performance
            products = (
                base_query.options(joinedload(Product.translations))
                .offset(skip)
                .limit(limit)
                .all()
            )

            logger.debug(
                f"Search '{query}' for store {store_id}: {total} results"
            )
            return products, total

        except SQLAlchemyError as e:
            logger.error(f"Error searching products: {str(e)}")
            raise ProductValidationException("Failed to search products")


# Create service instance
catalog_service = CatalogService()