class ProductRepository:
    async def get_all(self, db: AsyncSession, filters: ProductFilter) -> list[Product]:
        stmt = select(Product).where(Product.deleted == False)
        # إضافة فلاتر من نموذج Pydantic
        if filters.category:
            stmt = stmt.where(Product.category == filters.category)
        result = await db.execute(stmt)
        return result.scalars().all()

from sqlalchemy import select, update, delete
from sqlalchemy.ext.asyncio import AsyncSession

class ProductRepository:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def get_by_id(self, product_id: int) -> Product | None:
        stmt = select(Product).where(Product.id == product_id)
        result = await self.db.execute(stmt)
        return result.scalar_one_or_none()

    async def get_all(
        self,
        category: str | None = None,
        skip: int = 0,
        limit: int = 20,
    ) -> list[Product]:
        stmt = select(Product).offset(skip).limit(limit)
        if category:
            stmt = stmt.where(Product.category == category)
        result = await self.db.execute(stmt)
        return list(result.scalars().all())

    async def create(self, product: ProductCreate) -> Product:
        db_product = Product(**product.model_dump())
        self.db.add(db_product)
        await self.db.flush()
        await self.db.refresh(db_product)
        return db_product

    async def update(self, product_id: int, data: dict) -> Product | None:
        stmt = (
            update(Product)
            .where(Product.id == product_id)
            .values(**data)
            .returning(Product)
        )
        result = await self.db.execute(stmt)
        return result.scalar_one_or_none()

    async def delete(self, product_id: int) -> bool:
        stmt = delete(Product).where(Product.id == product_id)
        result = await self.db.execute(stmt)
        return result.rowcount > 0

# تم تعريف الدالة بنجاح

class PriceRepository:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def get_by_product(
        self,
        product_id: int,
        min_price: float | None = None,
        max_price: float | None = None,
        skip: int = 0,
        limit: int = 50,
    ) -> list[Price]:
        stmt = (
            select(Price)
            .where(Price.product_id == product_id)
            .order_by(Price.recorded_at.desc())
            .offset(skip)
            .limit(limit)
        )
        if min_price is not None:
            stmt = stmt.where(Price.price >= min_price)
        if max_price is not None:
            stmt = stmt.where(Price.price <= max_price)
        result = await self.db.execute(stmt)
        return list(result.scalars().all())

    async def bulk_create(self, prices: list[PriceCreate]) -> list[Price]:
        db_prices = [Price(**p.model_dump()) for p in prices]
        self.db.add_all(db_prices)
        await self.db.flush()
        return db_prices

# تم تعريف الدالة بنجاح

from pydantic import BaseModel, Field
from typing import Optional

class PaginationParams(BaseModel):
    skip: int = Field(0, ge=0, description="عدد السجلات للتخطي")
    limit: int = Field(20, ge=1, le=100, description="الحد الأقصى للسجلات المرجعة")

class ProductFilter(PaginationParams):
    category: Optional[str] = Field(None, max_length=100)
    min_price: Optional[float] = Field(None, ge=0, description="الحد الأدنى للسطح بالدولار")
    max_price: Optional[float] = Field(None, ge=0, description="الحد الأقصى للسطح بالدولار")
    sort_by: str = Field("name", pattern="^(name|base_price|created_at)$")
    sort_order: str = Field("asc", pattern="^(asc|desc)$")

# التنفيذ ناجح

from fastapi import FastAPI, Depends, Query
from sqlalchemy.ext.asyncio import AsyncSession

app = FastAPI()

@app.get("/products", response_model=list[ProductResponse])
async def list_products(
    category: Optional[str] = Query(None),
    min_price: Optional[float] = Query(None, ge=0),
    max_price: Optional[float] = Query(None, ge=0),
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    repo = ProductRepository(db)
    return await repo.get_all(
        category=category,
        min_price=min_price,
        max_price=max_price,
        skip=skip,
        limit=limit,
    )

# تم تعريف الدالة بنجاح

# سيء: استعلامات N+1 - استعلام واحد للمنتجات + N استعلام للأسعار
stmt = select(Product)
result = await db.execute(stmt)
products = result.scalars().all()
for p in products:
    # كل وصول يؤدي إلى استعلام منفصل!
    print(len(p.prices))  # N استعلام

# التنفيذ ناجح

from sqlalchemy.orm import selectinload, joinedload

# جيد: استعلامان فقط - منتجات + أسعار (باستخدام SELECT IN)
stmt = select(Product).options(selectinload(Product.prices))
result = await db.execute(stmt)
products = result.scalars().all()
for p in products:
    print(len(p.prices))  # لا استعلامات إضافية

# التنفيذ ناجح

async def get_product_with_prices(
    self, product_id: int
) -> Product | None:
    stmt = (
        select(Product)
        .options(selectinload(Product.prices))
        .where(Product.id == product_id)
    )
    result = await self.db.execute(stmt)
    return result.scalar_one_or_none()

# تم تعريف الدالة بنجاح

from sqlalchemy.ext.asyncio import AsyncSession

class PriceService:
    def __init__(self, db: AsyncSession):
        self.db = db
        self.price_repo = PriceRepository(db)
        self.product_repo = ProductRepository(db)

    async def bulk_import_prices(
        self,
        product_id: int,
        prices: list[PriceCreate],
    ) -> list[Price]:
        # التحقق من وجود المنتج
        product = await self.product_repo.get_by_id(product_id)
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        # التحقق من أن جميع الأسعار تشير إلى نفس المنتج
        for p in prices:
            p.product_id = product_id

        # إدراج مجمّع داخل المعاملة (جلسة db تتعامل مع الالتزام/الإرجاع)
        db_prices = await self.price_repo.bulk_create(prices)

        # تحديث السعر الأساسي للمنتج إلى الأحدث
        latest_price = db_prices[-1]
        await self.product_repo.update(
            product_id,
            {"base_price": latest_price.price},
        )

        return db_prices

# تم تعريف الدالة بنجاح

@app.post("/products/{product_id}/prices/bulk", response_model=list[PriceResponse])
async def bulk_import(
    product_id: int = Path(gt=0),
    prices: list[PriceCreate] = ...,
    db: AsyncSession = Depends(get_db),
    user: dict = Depends(get_current_user),
):
    # فحص الاشتراك: المستخدمون المجانيون محدودون بـ 1000 سعر
    if user["subscription"] == "free" and len(prices) > 1000:
        raise HTTPException(
            status_code=403,
            detail="Free plan limited to 1000 prices per import. Upgrade to Pro.",
        )
    
    service = PriceService(db)
    return await service.bulk_import_prices(product_id, prices)

# تم تعريف الدالة بنجاح