app/api/products.py

from typing import Optional

from fastapi import APIRouter, Depends, HTTPException, Query, status
from sqlalchemy import or_, desc, asc, func, text, case, cast, String
from sqlalchemy.orm import Session, joinedload
from datetime import datetime, timezone

from app.db.base import get_db
from app.models.product import Product, ProductImage, Category
from app.schemas.product import (
    ProductCreate,
    ProductUpdate,
    ProductListItem,
    ProductDetail,
    ProductImageCreate,
    PaginatedResponse,
)
from app.schemas.auth import AuthResponse
from app.core.security import get_current_user
from app.core.logging import log_audit_event

router = APIRouter(prefix="/products", tags=["Products"])


@router.get("", response_model=PaginatedResponse)
async def list_products(
    search: Optional[str] = Query(None),
    brand: Optional[str] = Query(None),
    category_id: Optional[int] = Query(None),
    min_price: Optional[float] = Query(None),
    max_price: Optional[float] = Query(None),
    is_featured: Optional[bool] = Query(None),
    has_discount: Optional[bool] = Query(None),
    sort_by: str = Query("created_at", regex="^(created_at|price|rating|name_en)$"),
    sort_order: str = Query("desc", regex="^(asc|desc)$"),
    page: int = Query(1, ge=1),
    page_size: int = Query(12, ge=1, le=2000),
    limit: Optional[int] = Query(None, ge=1, le=2000),
    specs_filter: Optional[str] = Query(None, description="JSON string of filters, e.g. {'RAM': ['8GB']}"),
    db: Session = Depends(get_db),
):
    # Use limit if provided, otherwise use page_size
    effective_page_size = limit if limit is not None else page_size
    
    from app.models.settings import StoreSettings
    settings = db.query(StoreSettings).first()
    global_discount = settings.global_discount if settings else 0
    multiplier = (1 - global_discount / 100)

    query = db.query(Product).filter(
        Product.is_active == True,   # noqa: E712
        Product.deleted_at == None   # noqa: E711
    )

    # Search (with Arabic Normalization: أ, إ, آ -> ا | ة -> ه | ى -> ي)
    if search:
        search_term = f"%{search}%"
        
        # Search (with Arabic Normalization: أ, إ, آ -> ا | ة -> ه | ى -> ي | ignore Tashkeel)
        def normalize_arabic(column):
            # 1-7. Previous normalization
            col = func.replace(column, 'أ', 'ا')
            col = func.replace(col, 'إ', 'ا')
            col = func.replace(col, 'آ', 'ا')
            col = func.replace(col, 'ة', 'ه')
            col = func.replace(col, 'ى', 'ي')
            col = func.replace(col, 'ؤ', 'و')
            col = func.replace(col, 'ئ', 'ي')
            # 8. Ignore Tashkeel/Diacritics (Fatha, Damma, Kesra, Shadda, etc)
            tashkeel = ['ً', 'ٌ', 'ٍ', 'َ', 'ُ', 'ِ', 'ّ', 'ْ', 'ـ']
            for char in tashkeel:
                col = func.replace(col, char, '')
            return col
            
        # Normalize the user's search term in python
        n_search = search.replace('أ', 'ا').replace('إ', 'ا').replace('آ', 'ا').replace('ة', 'ه').replace('ى', 'ي').replace('ؤ', 'و').replace('ئ', 'ي')
        # Remove Tashkeel from search term too
        for char in ['ً', 'ٌ', 'ٍ', 'َ', 'ُ', 'ِ', 'ّ', 'ْ', 'ـ']:
            n_search = n_search.replace(char, '')
        n_search_term = f"%{n_search}%"

        query = query.filter(
            or_(
                Product.name_en.ilike(search_term),
                normalize_arabic(Product.name_ar).ilike(n_search_term),
                Product.description_en.ilike(search_term),
                normalize_arabic(Product.description_ar).ilike(n_search_term),
            )
        )

    # Filters
    if category_id is not None:
        # Get category and its children to allow filtering by parent category
        category = db.query(Category).filter(Category.id == category_id).first()
        if category:
            subcats = db.query(Category.id).filter(Category.parent_id == category_id).all()
            subcat_ids = [c[0] for c in subcats]
            all_cat_ids = [category_id] + subcat_ids
            query = query.filter(Product.category_id.in_(all_cat_ids))
            
    if brand:
        brands = [b.strip() for b in brand.split(',') if b.strip()]
        brand_conditions = []
        for b in brands:
            brand_search = f"%{b.lower()}%"
            brand_conditions.append(
                or_(
                    cast(Product.specs['brand_name'], String).ilike(brand_search),
                    cast(Product.specs['brand'], String).ilike(brand_search),
                    cast(Product.specs['details_en']['Brand Name'], String).ilike(brand_search),
                    cast(Product.specs['details_en']['Brand'], String).ilike(brand_search)
                )
            )
        if brand_conditions:
            query = query.filter(or_(*brand_conditions))
            
    if specs_filter:
        import json
        try:
            filters = json.loads(specs_filter)
            if isinstance(filters, dict):
                for key, values in filters.items():
                    if values and isinstance(values, list):
                        # Postgres JSONB extraction
                        query = query.filter(
                            or_(
                                cast(Product.specs[key], String).in_(values),
                                cast(Product.specs['details_en'][key], String).in_(values)
                            )
                        )
        except Exception as e:
            print(f"Error parsing specs_filter: {e}")

    if min_price is not None:
        query = query.filter(Product.price >= min_price)
    if max_price is not None:
        query = query.filter(Product.price <= max_price)
    if is_featured is not None:
        query = query.filter(Product.is_featured == is_featured)
    if has_discount is True:
        query = query.filter(Product.compare_price > Product.price)
    elif has_discount is False:
        query = query.filter(or_(Product.compare_price == None, Product.compare_price <= Product.price))

    # Count
    total = query.count()

    # Sort
    from sqlalchemy import case
    
    # Custom Hybrid Sorting Logic:
    # 1. New Products (ID > 3708) first, sorted by ID DESC
    # 2. Original Catalog (ID <= 3708) next, sorted by ID ASC
    query = query.order_by(
        case(
            (Product.id > 3708, 0),
            else_=1
        ),
        Product.id.asc()
    )

    # Pagination
    offset = (page - 1) * effective_page_size
    products = (
        query.options(joinedload(Product.category), joinedload(Product.images))
        .offset(offset)
        .limit(effective_page_size)
        .all()
    )

    # Map to response
    items = []
    for p in products:
        primary_image = None
        if p.images:
            sorted_imgs = sorted(p.images, key=lambda x: x.sort_order)
            primary_image = sorted_imgs[0].image_url if sorted_imgs else None

        # Pricing for combined discount display:
        # price = original_price * multiplier
        # compare_price = original_compare (raw)
        original_price = p.price
        original_compare = p.compare_price if p.compare_price else p.price
        
        item = ProductListItem(
            id=p.id,
            slug=p.slug,
            name_ar=p.name_ar,
            name_en=p.name_en,
            price=original_price * multiplier,
            compare_price=original_compare if (multiplier < 1.0 or p.compare_price) else None,
            stock=p.stock,
            category_id=p.category_id,
            category=p.category,
            rating=p.rating,
            rating_count=p.rating_count,
            is_featured=p.is_featured,
            image_url=primary_image,
            created_at=p.created_at,
            quality_score=p.quality_score or 100,
            deleted_at=p.deleted_at,
        )
        items.append(item.model_dump())

    # Serialize dates
    for item in items:
        if isinstance(item.get("created_at"), datetime):
            item["created_at"] = item["created_at"].isoformat()
        if item.get("deleted_at") and isinstance(item["deleted_at"], datetime):
            item["deleted_at"] = item["deleted_at"].isoformat()

    total_pages = (total + page_size - 1) // page_size

    return PaginatedResponse(
        isSuccess=True,
        value={
            "items": items,
            "total": total,
            "page": page,
            "page_size": page_size,
            "total_pages": total_pages,
        },
        statusCode=200,
    )
@router.get("/admin/deleted", response_model=PaginatedResponse)
async def list_deleted_products(
    page: int = Query(1, ge=1),
    page_size: int = Query(12, ge=1, le=48),
    db: Session = Depends(get_db),
    user_id: int = Depends(get_current_user),
):
    query = db.query(Product).filter(Product.deleted_at != None) # noqa: E711
    
    total = query.count()
    offset = (page - 1) * page_size
    products = (
        query.options(joinedload(Product.category), joinedload(Product.images))
        .order_by(desc(Product.deleted_at))
        .offset(offset)
        .limit(page_size)
        .all()
    )

    items = []
    for p in products:
        item = ProductDetail.model_validate(p).model_dump()
        if isinstance(item.get("created_at"), datetime):
            item["created_at"] = item["created_at"].isoformat()
        if item.get("deleted_at") and isinstance(item["deleted_at"], datetime):
            item["deleted_at"] = item["deleted_at"].isoformat()
        items.append(item)

    total_pages = (total + page_size - 1) // page_size

    return PaginatedResponse(
        isSuccess=True,
        value={
            "items": items,
            "total": total,
            "page": page,
            "page_size": page_size,
            "total_pages": total_pages,
        },
        statusCode=200,
    )


@router.get("/filters")
async def get_filters(category_id: Optional[int] = Query(None), db: Session = Depends(get_db)):
    """
    Returns filter metadata for a category (price range, brands, dynamic attributes).
    Supports parent categories by aggregating children. If category_id is None, returns global filters.
    """
    all_cat_ids = None
    cat_name_en = ""
    
    if category_id is not None:
        category = db.query(Category).filter(Category.id == category_id).first()
        if not category:
            raise HTTPException(status_code=404, detail="Category not found")
        
        subcats = db.query(Category.id).filter(Category.parent_id == category_id).all()
        all_cat_ids = [category_id] + [c[0] for c in subcats]
        cat_name_en = (category.name_en or "").lower()
        
    base_filter = [Product.is_active == True, Product.deleted_at == None]
    if all_cat_ids is not None:
        base_filter.append(Product.category_id.in_(all_cat_ids))
    
    # 1. Price Range
    price_stats = db.query(
        func.min(Product.price).label("min_p"),
        func.max(Product.price).label("max_p")
    ).filter(*base_filter).first()
    
    # 2. Brands
    brands_q1 = db.query(func.distinct(cast(Product.specs['brand_name'], String))).filter(*base_filter)
    brands_q2 = db.query(func.distinct(cast(Product.specs['details_en']['Brand Name'], String))).filter(*base_filter)
    brands = sorted(list(set([b[0] for b in brands_q1.all() if b[0]] + [b[0] for b in brands_q2.all() if b[0]])))

    # 3. Dynamic Attributes per subcategory
    attr_mappings = []
    
    if cat_name_en:
        # ── Electronics ──
        if any(k in cat_name_en for k in ["mobile", "phone", "smartphone", "جوالات"]):
            attr_mappings = [
                {"key": "RAM", "label_ar": "الرام", "label_en": "RAM"},
                {"key": "Internal Memory", "label_ar": "المساحة الداخلية", "label_en": "Storage"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Screen Size", "label_ar": "حجم الشاشة", "label_en": "Screen Size"},
                {"key": "Network Type", "label_ar": "نوع الشبكة", "label_en": "Network"},
            ]
        elif any(k in cat_name_en for k in ["laptop", "computer", "لابتوب"]):
            attr_mappings = [
                {"key": "Processor", "label_ar": "المعالج", "label_en": "Processor"},
                {"key": "RAM", "label_ar": "الرام", "label_en": "RAM"},
                {"key": "Hard Drive Capacity", "label_ar": "سعة التخزين", "label_en": "Storage"},
                {"key": "Screen Size", "label_ar": "حجم الشاشة", "label_en": "Screen Size"},
                {"key": "Graphics Card", "label_ar": "كرت الشاشة", "label_en": "Graphics Card"},
                {"key": "Operating System", "label_ar": "نظام التشغيل", "label_en": "OS"},
            ]
        elif any(k in cat_name_en for k in ["tablet", "لوحي"]):
            attr_mappings = [
                {"key": "RAM", "label_ar": "الرام", "label_en": "RAM"},
                {"key": "Internal Memory", "label_ar": "المساحة", "label_en": "Storage"},
                {"key": "Screen Size", "label_ar": "حجم الشاشة", "label_en": "Screen Size"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
            ]
        elif any(k in cat_name_en for k in ["tv", "television", "تلفزيون"]):
            attr_mappings = [
                {"key": "Screen Size", "label_ar": "حجم الشاشة", "label_en": "Screen Size"},
                {"key": "Resolution Type", "label_ar": "الدقة", "label_en": "Resolution"},
                {"key": "Smart TV", "label_ar": "تلفزيون ذكي", "label_en": "Smart TV"},
                {"key": "Display Type", "label_ar": "نوع الشاشة", "label_en": "Display Type"},
            ]
        elif any(k in cat_name_en for k in ["camera", "كاميرا"]):
            attr_mappings = [
                {"key": "Resolution", "label_ar": "الدقة", "label_en": "Resolution"},
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
            ]
        elif any(k in cat_name_en for k in ["printer", "طابع"]):
            attr_mappings = [
                {"key": "Print Technology", "label_ar": "تقنية الطباعة", "label_en": "Print Tech"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Connectivity", "label_ar": "الاتصال", "label_en": "Connectivity"},
            ]
        # ── Accessories ──
        elif any(k in cat_name_en for k in ["audio", "headphone", "سماعات", "صوت"]):
            attr_mappings = [
                {"key": "Connection Type", "label_ar": "نوع الاتصال", "label_en": "Connection"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
            ]
        elif any(k in cat_name_en for k in ["watch", "ساعات"]):
            attr_mappings = [
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Band Material", "label_ar": "مادة السوار", "label_en": "Band Material"},
                {"key": "Display Type", "label_ar": "نوع الشاشة", "label_en": "Display"},
            ]
        # ── Gaming ──
        elif any(k in cat_name_en for k in ["gaming", "console", "ألعاب"]):
            attr_mappings = [
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Storage Capacity", "label_ar": "سعة التخزين", "label_en": "Storage"},
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
            ]
        # ── Home Appliances ──
        elif any(k in cat_name_en for k in ["refrigerator", "ثلاج"]):
            attr_mappings = [
                {"key": "Capacity", "label_ar": "السعة", "label_en": "Capacity"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Door Type", "label_ar": "نوع الباب", "label_en": "Door Type"},
            ]
        elif any(k in cat_name_en for k in ["wash", "غسال"]):
            attr_mappings = [
                {"key": "Capacity", "label_ar": "السعة", "label_en": "Capacity (kg)"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Load Type", "label_ar": "نوع التحميل", "label_en": "Load Type"},
            ]
        elif any(k in cat_name_en for k in ["conditioner", "مكيف"]):
            attr_mappings = [
                {"key": "Capacity", "label_ar": "السعة (BTU)", "label_en": "Capacity (BTU)"},
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
                {"key": "Energy Rating", "label_ar": "كفاءة الطاقة", "label_en": "Energy Rating"},
            ]
        elif any(k in cat_name_en for k in ["small appliance", "منزلية صغيرة"]):
            attr_mappings = [
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Wattage", "label_ar": "القدرة", "label_en": "Wattage"},
            ]
        elif any(k in cat_name_en for k in ["large appliance", "منزلية كبيرة"]):
            attr_mappings = [
                {"key": "Capacity", "label_ar": "السعة", "label_en": "Capacity"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Type", "label_ar": "النوع", "label_en": "Type"},
            ]
        # ── Parent categories (aggregate) ──
        elif any(k in cat_name_en for k in ["electronic", "إلكتروني"]):
            attr_mappings = [
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
            ]
        elif any(k in cat_name_en for k in ["accessor", "ملحقات"]):
            attr_mappings = [
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
                {"key": "Connection Type", "label_ar": "نوع الاتصال", "label_en": "Connection"},
            ]
        elif any(k in cat_name_en for k in ["home appliance", "المنزلية"]):
            attr_mappings = [
                {"key": "Capacity", "label_ar": "السعة", "label_en": "Capacity"},
                {"key": "Color", "label_ar": "اللون", "label_en": "Color"},
            ]

    # 4. Resolve attribute values from DB
    attributes = []
    for attr in attr_mappings:
        key = attr["key"]
        # Try direct spec key first, then details_en
        values_query = db.query(func.distinct(cast(Product.specs[key], String))).filter(
            *base_filter
        ).all()
        vals = [v[0] for v in values_query if v[0]]
        
        if not vals:
            values_query = db.query(func.distinct(cast(Product.specs['details_en'][key], String))).filter(
                *base_filter
            ).all()
            vals = [v[0] for v in values_query if v[0]]
        
        if vals:
            attributes.append({
                "key": key,
                "label_ar": attr["label_ar"],
                "label_en": attr["label_en"],
                "options": sorted(list(set(vals)))
            })

    return PaginatedResponse(
        isSuccess=True,
        value={
            "min_price": float(price_stats.min_p) if price_stats and price_stats.min_p else 0,
            "max_price": float(price_stats.max_p) if price_stats and price_stats.max_p else 10000,
            "brands": brands,
            "attributes": attributes
        },
        statusCode=200
    )



@router.get("/{identifier}", response_model=PaginatedResponse)
async def get_product(identifier: str, raw: bool = False, db: Session = Depends(get_db)):
    query = db.query(Product).options(joinedload(Product.category), joinedload(Product.images))
    if identifier.isdigit():
        product = query.filter(Product.id == int(identifier)).first()
        # If not found by ID, maybe the slug itself is numeric?
        if not product:
            product = query.filter(Product.slug == identifier).first()
    else:
        product = query.filter(Product.slug == identifier).first()

    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    from app.models.settings import StoreSettings
    settings = db.query(StoreSettings).first()
    global_discount = settings.global_discount if settings else 0
    multiplier = (1 - global_discount / 100)

    detail = ProductDetail.model_validate(product).model_dump()
    
    # Only apply discount if NOT raw
    if not raw:
        # Normalize pricing for combined discount display:
        # price = original_price * multiplier
        # compare_price = original_compare (raw)
        original_price = detail["price"]
        original_compare = detail.get("compare_price") if detail.get("compare_price") else original_price
        
        detail["price"] = original_price * multiplier
        if multiplier < 1.0 or detail.get("compare_price"):
            detail["compare_price"] = original_compare
            
        # Apply to variants in specs
        if detail.get("specs") and "variants" in detail["specs"]:
            for v in detail["specs"]["variants"]:
                if v.get("price"):
                    v_original_price = v["price"]
                    v["price"] = v_original_price * multiplier
                    # Optional: Could add compare_price to variants too, but mostly price is enough 
                    # as the main product's compare_price is often used for the range.
                if v.get("price_modifier"):
                    v["price_modifier"] *= multiplier

    return PaginatedResponse(
        isSuccess=True,
        value=detail,
        statusCode=200,
    )


@router.post("", response_model=AuthResponse, status_code=status.HTTP_201_CREATED)
async def create_product(
    request: ProductCreate,
    user_id: int = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    if not request.images or len(request.images) == 0:
        raise HTTPException(status_code=400, detail="Main image is required / صورة رئيسية مطلوبة")

    product = Product(
        name_ar=request.name_ar,
        name_en=request.name_en,
        description_ar=request.description_ar,
        description_en=request.description_en,
        price=request.price,
        compare_price=request.compare_price,
        stock=request.stock,
        category_id=request.category_id,
        is_featured=request.is_featured,
        is_active=request.is_active,
        specs=request.specs,
    )
    db.add(product)
    db.flush()

    # Add images
    for img in request.images:
        db_img = ProductImage(
            product_id=product.id,
            image_url=img.image_url,
            alt_text=img.alt_text,
            sort_order=img.sort_order,
        )
        db.add(db_img)

    db.commit()
    db.refresh(product)

    return AuthResponse(
        isSuccess=True,
        value={"product_id": product.id},
        statusCode=201,
    )


@router.put("/{product_id}", response_model=AuthResponse)
async def update_product(
    product_id: int,
    request: ProductUpdate,
    user_id: int = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product or product.deleted_at is not None:
        raise HTTPException(status_code=404, detail="Product not found")

    update_data = request.model_dump(exclude_unset=True)
    for field, value in update_data.items():
        setattr(product, field, value)

    db.commit()

    return AuthResponse(
        isSuccess=True,
        value={"product_id": product.id},
        statusCode=200,
    )


@router.delete("/{product_id}", response_model=AuthResponse)
async def delete_product(
    product_id: int,
    user_id: int = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product or product.deleted_at is not None:
        raise HTTPException(status_code=404, detail="Product not found")

    # Soft Delete
    product.deleted_at = datetime.now(timezone.utc)
    product.deleted_by = user_id
    product.deletion_reason = "Manual deletion by admin"
    
    db.commit()

    return AuthResponse(
        isSuccess=True,
        value={"deleted": True, "soft_delete": True},
        statusCode=200,
    )

@router.delete("/{product_id}/hard", response_model=AuthResponse)
async def hard_delete_product(
    product_id: int,
    user_id: int = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    db.delete(product)
    db.commit()

    return AuthResponse(
        isSuccess=True,
        value={"deleted": True, "hard_delete": True},
        statusCode=200,
    )

@router.post("/{product_id}/restore", response_model=AuthResponse)
async def restore_product(
    product_id: int,
    user_id: int = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product or product.deleted_at is None:
        raise HTTPException(status_code=404, detail="Product not found or not deleted")

    product.deleted_at = None
    product.deleted_by = None
    product.deletion_reason = None
    
    db.commit()

    return AuthResponse(
        isSuccess=True,
        value={"restored": True},
        statusCode=200,
    )





# End of products.py