from typing import List, Optional, Tuple
from sqlalchemy.orm import Session
from sqlalchemy import or_, desc, asc
from app.models.product import Product
from app.repositories.base import BaseRepository

class ProductRepository(BaseRepository[Product]):
    """Repository handling database queries for the Product model, supporting search, filters, and pagination."""

    def get_by_slug(self, db: Session, slug: str) -> Optional[Product]:
        """Fetch an active product by slug."""
        return db.query(self.model).filter(
            self.model.slug == slug, 
            self.model.deleted_at == None
        ).first()

    def get_filtered_products(
        self,
        db: Session,
        *,
        search: Optional[str] = None,
        material: Optional[str] = None,
        category_id: Optional[str] = None,
        sort_by: Optional[str] = "created_at",  # price, created_at
        sort_order: Optional[str] = "desc",     # asc, desc
        skip: int = 0,
        limit: int = 12
    ) -> Tuple[List[Product], int]:
        """Fetch list of products with search, material filter, category filter, sorting, and pagination."""
        query = db.query(self.model).filter(self.model.deleted_at == None)

        # 1. Search filter (nama atau deskripsi)
        if search:
            search_filter = f"%{search}%"
            query = query.filter(
                or_(
                    self.model.name.ilike(search_filter),
                    self.model.description.ilike(search_filter)
                )
            )

        # 2. Material filter (titanium / xuping)
        if material:
            query = query.filter(self.model.material == material.lower())

        # 3. Category filter
        if category_id:
            query = query.filter(self.model.category_id == category_id)

        # Count total matches before pagination limit
        total_count = query.count()

        # 4. Sorting logic
        order_col = getattr(self.model, sort_by, self.model.created_at)
        if sort_order == "desc":
            query = query.order_by(desc(order_col))
        else:
            query = query.order_by(asc(order_col))

        # 5. Pagination
        products = query.offset(skip).limit(limit).all()

        return products, total_count

product_repository = ProductRepository(Product)