from sqlalchemy.orm import Session, joinedload
from sqlalchemy import or_
from app.models.product import Product, Category, ProductImage
from app.schemas.product import ProductCreate, ProductUpdate

def get_products(db: Session, search: str = None, category_id: int = None, skip: int = 0, limit: int = 20):
    q = db.query(Product).options(joinedload(Product.category)).filter(Product.is_active == True)
    if search:
        q = q.filter(or_(Product.name.ilike(f"%{search}%"), Product.description.ilike(f"%{search}%")))
    if category_id:
        q = q.filter(Product.category_id == category_id)
    total = q.count()
    items = q.offset(skip).limit(limit).all()
    return {"items": items, "total": total, "skip": skip, "limit": limit}

def get_product(db: Session, product_id: int):
    return db.query(Product).options(
        joinedload(Product.category),
        joinedload(Product.images)
    ).filter(Product.id == product_id).first()

def create_product(db: Session, product_in: ProductCreate):
    product = Product(**product_in.model_dump())
    db.add(product); db.commit(); db.refresh(product)
    return product

def update_product(db: Session, product_id: int, product_in: ProductUpdate):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product: return None
    for field, value in product_in.model_dump(exclude_unset=True).items():
        setattr(product, field, value)
    db.commit(); db.refresh(product)
    return product

from sqlalchemy.exc import IntegrityError

def delete_product(db: Session, product_id: int):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        return None
    try:
        db.delete(product)
        db.commit()
    except IntegrityError:
        db.rollback()
        # Soft delete fallback: hide from shop but keep for order history
        product.is_active = False
        db.commit()
        db.refresh(product)
    return product

def get_categories(db: Session):
    return db.query(Category).all()

def create_category(db: Session, category_in):
    cat = Category(**category_in.model_dump())
    db.add(cat); db.commit(); db.refresh(cat)
    return cat

def add_product_image(db: Session, product_id: int, url: str, is_primary: bool = False):
    img = ProductImage(product_id=product_id, url=url, is_primary=is_primary)
    db.add(img); db.commit(); db.refresh(img)
    return img

def delete_product_image(db: Session, image_id: int):
    img = db.query(ProductImage).filter(ProductImage.id == image_id).first()
    if img:
        db.delete(img)
        db.commit()
    return img