"""
Product Category services for business logic operations.
"""

import json
from typing import Optional, List, Tuple, Any, Dict
from sqlalchemy.orm import Session
from sqlalchemy import select, update, delete, func
from sqlalchemy.exc import IntegrityError
from datetime import datetime
import logging

from src.core.utils.enums import FilterOperators
from src.core.utils.constants import API_PREFIXES
from src.apps.product_categories.models.product_category import ProductCategory
from src.apps.product_categories.schemas.common import (
    ProductCategoryResponseSchema,
    ProductCategoryCreateSchema, 
    ProductCategoryUpdateSchema,
    ProductCategoryListFilterSchema
)
from src.helpers.pagination import QueryPaginator
from src.apps.base.utils.functions import generate_unique_slug
from src.core.config import settings

logger = logging.getLogger(__name__)


async def get_product_categories_list(
    db: Session,
    filters: ProductCategoryListFilterSchema,
    _from: Optional[str] = None,
    _to: Optional[str] = None,
    sort_by: str = "-created_at",
    fields: Optional[str] = None,
    skip: int = 0,
    limit: int = 20,
    paginate: bool = True,
) -> Dict[str, Any]:
    """
    Get product categories list with filtering, sorting, and pagination.
    """
    try:
        pagination_url: str = f"{settings.api_base_url()}{API_PREFIXES.CATEGORIES.value}"
        
        # Build base query
        stmt = select(ProductCategory)
        if hasattr(ProductCategory, 'deleted_at'):
            stmt = stmt.where(ProductCategory.deleted_at.is_(None))
        
        if _from:
            from_date = datetime.strptime(_from, "%Y-%m-%d")
            stmt = stmt.where(ProductCategory.created_at >= from_date)
        
        if _to:
            to_date = datetime.strptime(_to, "%Y-%m-%d")
            to_date = to_date.replace(hour=23, minute=59, second=59, microsecond=999999)
            stmt = stmt.where(ProductCategory.created_at <= to_date)
        
        if filters.search:
            search_term = f"%{filters.search}%"
            stmt = stmt.where(
                (ProductCategory.name.ilike(search_term)) |
                (ProductCategory.description.ilike(search_term)) |
                (ProductCategory.code.ilike(search_term))
            )
        
        # Individual field filters
        if filters.name:
            stmt = stmt.where(ProductCategory.name.ilike(f"%{filters.name}%"))
        
        if filters.code:
            stmt = stmt.where(ProductCategory.code.ilike(f"%{filters.code}%"))
        
        if filters.merchant_id is not None:
            stmt = stmt.where(ProductCategory.merchant_id == filters.merchant_id)
        
        if filters.parent_id is not None:
            stmt = stmt.where(ProductCategory.parent_id == filters.parent_id)
        
        # Convert integer filter to boolean for database comparison
        if filters.is_active != 0:  # 0 means "all", so don't filter
            is_active_bool = filters.is_active == 1  # 1 = active (True), -1 = inactive (False)
            stmt = stmt.where(ProductCategory.is_active == is_active_bool)
        
        # Apply sorting
        if sort_by.startswith("-"):
            field_name = sort_by[1:]
            if hasattr(ProductCategory, field_name):
                stmt = stmt.order_by(getattr(ProductCategory, field_name).desc())
        else:
            if hasattr(ProductCategory, sort_by):
                stmt = stmt.order_by(getattr(ProductCategory, sort_by))
        
        # Use QueryPaginator for pagination
        if paginate:
            paginator = QueryPaginator(
                query=stmt,
                schema=ProductCategoryResponseSchema,
                url=pagination_url,
                db=db,
                model=ProductCategory,
                offset=skip,
                limit=limit,
                use_orm=True,
            )
            
            return paginator.to_dict()
        else:
            # Return raw results without pagination
            results = db.execute(stmt.offset(skip).limit(limit)).scalars().all()
            return [ProductCategoryResponseSchema.model_validate(category) for category in results]
        
    except Exception as e:
        logger.error(f"Error getting product categories list: {e}")
        return {"error": str(e)}


async def create_product_category(
    db: Session,
    # merchant_id: int,
    payload: ProductCategoryCreateSchema
) -> ProductCategoryResponseSchema:
    """
    Create a new product category.
    """
    try:
        # Convert schema to dict and add merchant_id
        category_dict = payload.model_dump()
        # category_dict["merchant_id"] = merchant_id
        
        # Generate unique slug from category name
        if not category_dict.get("slug"):
            category_dict["slug"] = generate_unique_slug(
                db=db, 
                instance=ProductCategory, 
                slug_label=payload.name
            )
        
        # Create new product category
        new_category = ProductCategory(**category_dict)
        db.add(new_category)
        db.commit()
        db.refresh(new_category)
        
        return ProductCategoryResponseSchema.model_validate(new_category)
        
    except IntegrityError as e:
        logger.error(f"IntegrityError creating product category: {e}")
        # Handle specific integrity errors
        if "code" in str(e.orig).lower():
            raise ValueError("Product category with this code already exists")
        elif "slug" in str(e.orig).lower():
            raise ValueError("Product category with this slug already exists")
        else:
            raise ValueError("Product category creation failed due to data conflict")
    except Exception as e:
        logger.error(f"Error creating product category: {e}")
        raise ValueError(f"Product category creation failed: {str(e)}")


async def get_product_category_by_id(
    db: Session,
    # merchant_id: int,
    category_id: int
) -> Optional[ProductCategoryResponseSchema]:
    """
    Get a product category by ID.
    """
    try:
        stmt = select(ProductCategory).where(
            ProductCategory.id == category_id,
            # ProductCategory.merchant_id == merchant_id,
            ProductCategory.deleted_at.is_(None)
        )
        
        result = db.execute(stmt)
        category = result.scalar_one_or_none()
        
        if category:
            return ProductCategoryResponseSchema.model_validate(category)
        return None
        
    except Exception as e:
        logger.error(f"Error getting product category by ID: {e}")
        return None


async def update_product_category(
    db: Session,
    # merchant_id: int,
    category_id: int,
    payload: ProductCategoryUpdateSchema
) -> Optional[ProductCategoryResponseSchema]:
    """
    Update a product category by ID.
    """
    try:
        # Check if category exists
        stmt = select(ProductCategory).where(
            ProductCategory.id == category_id,
            # ProductCategory.merchant_id == merchant_id,
            ProductCategory.deleted_at.is_(None)
        )
        result = db.execute(stmt)
        category = result.scalar_one_or_none()
        
        if not category:
            return None
        
        # Update category with new data
        update_data = payload.model_dump(exclude_unset=True)
        
        if update_data:
            # Add updated_at timestamp
            update_data["updated_at"] = datetime.utcnow()
            
            update_stmt = (
                update(ProductCategory)
                .where(
                    ProductCategory.id == category_id,
                    # ProductCategory.merchant_id == merchant_id,
                    ProductCategory.deleted_at.is_(None)
                )
                .values(**update_data)
            )
            db.execute(update_stmt)
            db.commit()
            db.refresh(category)
        
        return ProductCategoryResponseSchema.model_validate(category)
        
    except IntegrityError as e:
        logger.error(f"IntegrityError updating product category: {e}")
        if "code" in str(e.orig).lower():
            raise ValueError("Product category with this code already exists")
        elif "slug" in str(e.orig).lower():
            raise ValueError("Product category with this slug already exists")
        else:
            raise ValueError("Product category update failed due to data conflict")
    except Exception as e:
        logger.error(f"Error updating product category: {e}")
        raise ValueError(f"Product category update failed: {str(e)}")


async def delete_product_category(
    db: Session,
    # merchant_id: int,
    category_id: int
) -> bool:
    """
    Delete a product category by ID (soft delete).
    """
    try:
        # Check if category exists
        stmt = select(ProductCategory).where(
            ProductCategory.id == category_id,
            # ProductCategory.merchant_id == merchant_id,
            ProductCategory.deleted_at.is_(None)
        )
        result = db.execute(stmt)
        category = result.scalar_one_or_none()
        
        if not category:
            return False
        
        # Soft delete by setting deleted_at timestamp
        update_stmt = (
            update(ProductCategory)
            .where(
                ProductCategory.id == category_id,
                # ProductCategory.merchant_id == merchant_id,
                ProductCategory.deleted_at.is_(None)
            )
            .values(deleted_at=datetime.utcnow())
        )
        db.execute(update_stmt)
        db.commit()
        
        return True
        
    except Exception as e:
        logger.error(f"Error deleting product category: {e}")
        raise ValueError(f"Product category deletion failed: {str(e)}")


async def bulk_delete_product_categories(
    db: Session,
    # merchant_id: int,
    category_ids: List[int]
) -> int:
    """
    Delete multiple product categories by IDs (soft delete).
    """
    try:
        # Update multiple categories at once
        update_stmt = (
            update(ProductCategory)
            .where(
                ProductCategory.id.in_(category_ids),
                # ProductCategory.merchant_id == merchant_id,
                ProductCategory.deleted_at.is_(None)
            )
            .values(deleted_at=datetime.utcnow())
        )
        
        result = db.execute(update_stmt)
        db.commit()
        
        return result.rowcount
        
    except Exception as e:
        logger.error(f"Error bulk deleting product categories: {e}")
        raise ValueError(f"Bulk deletion failed: {str(e)}")