import uuid
from typing import Any

from sqlalchemy import func, or_, select

from app.models import Category, Coupon, Product
from app.repositories.base import BaseRepository


class CategoryRepository(BaseRepository[Category]):
    model = Category

    async def list_ordered(self, only_enabled: bool = True) -> list[Category]:
        stmt = select(Category).order_by(Category.sort_order, Category.title)
        if only_enabled:
            stmt = stmt.where(Category.enabled.is_(True))
        return list((await self.session.execute(stmt)).scalars().all())

    async def get_by_slug(self, slug: str) -> Category | None:
        return await self.get_by(slug=slug)


class ProductRepository(BaseRepository[Product]):
    model = Product

    def _base_query(self, filters: dict[str, Any]) -> Any:
        stmt = select(Product)
        category_id = filters.get("category_id")
        if category_id:
            stmt = stmt.where(Product.category_id == category_id)
        flow = filters.get("flow")
        if flow:
            stmt = stmt.where(Product.flow == flow)
        enabled = filters.get("enabled")
        if enabled is not None:
            stmt = stmt.where(Product.enabled.is_(bool(enabled)))
        search = filters.get("search")
        if search:
            pattern = f"%{search}%"
            stmt = stmt.where(or_(Product.title.ilike(pattern), Product.slug.ilike(pattern)))
        return stmt.order_by(Product.sort_order, Product.price)

    async def search(self, page: int, size: int, **filters: Any) -> tuple[list[Product], int]:
        return await self.paginate(self._base_query(filters), page, size)

    async def list_filtered(self, **filters: Any) -> list[Product]:
        stmt = self._base_query(filters)
        return list((await self.session.execute(stmt)).scalars().all())

    async def get_by_slug(self, slug: str) -> Product | None:
        return await self.get_by(slug=slug)

    async def list_by_category_slug(self, slug: str, only_enabled: bool = True) -> list[Product]:
        stmt = (
            select(Product)
            .join(Category)
            .where(Category.slug == slug)
            .order_by(Product.sort_order, Product.price)
        )
        if only_enabled:
            stmt = stmt.where(Product.enabled.is_(True), Category.enabled.is_(True))
        return list((await self.session.execute(stmt)).scalars().all())


class CouponRepository(BaseRepository[Coupon]):
    model = Coupon

    async def get_by_code(self, code: str) -> Coupon | None:
        stmt = select(Coupon).where(func.upper(Coupon.code) == code.strip().upper())
        return (await self.session.execute(stmt)).scalar_one_or_none()

    async def search(self, page: int, size: int, search: str | None = None) -> tuple[list[Coupon], int]:
        stmt = select(Coupon).order_by(Coupon.created_at.desc())
        if search:
            stmt = stmt.where(Coupon.code.ilike(f"%{search}%"))
        return await self.paginate(stmt, page, size)

    async def count_user_usage(self, coupon_id: uuid.UUID, user_id: uuid.UUID) -> int:
        from app.models import Order, OrderStatus

        stmt = (
            select(func.count())
            .select_from(Order)
            .where(
                Order.coupon_id == coupon_id,
                Order.user_id == user_id,
                Order.status.in_(
                    [OrderStatus.PAID, OrderStatus.PROCESSING, OrderStatus.COMPLETED]
                ),
            )
        )
        return int((await self.session.execute(stmt)).scalar_one())