import uuid from sqlalchemy import select, text from sqlalchemy.exc import IntegrityError from sqlalchemy.ext.asyncio import AsyncSession from app.audit.service import AuditService from app.common.enums import AuditAction, EntityType, Role, FIELD_ROLES from app.common.exceptions import ConflictError, NotFoundError from app.common.utils import snapshot from app.products.models import Product from app.products.schemas import ( ProductCreate, ProductUpdate, StockInventoryCreate, StockInventoryRead, StockInventoryUpdate, ) from app.users.models import User _AUDIT_FIELDS = ["name", "pack", "unit", "is_active"] class ProductService: def __init__(self, db: AsyncSession) -> None: self.db, self.audit = db, AuditService(db) async def list_for(self, actor: User) -> list[Product]: stmt = select(Product).where(Product.company_id == actor.company_id).order_by(Product.name) if actor.role in FIELD_ROLES: stmt = stmt.where(Product.is_active.is_(True)) return list((await self.db.execute(stmt)).scalars().all()) async def list_stock_inventory(self) -> list[StockInventoryRead]: result = await self.db.execute( text( """ SELECT id, serial_no, product_name, pack, quantity, unit FROM stock_inventory ORDER BY serial_no """ ) ) return [StockInventoryRead.model_validate(row) for row in result.mappings()] async def _stock_row(self, item_id: int) -> StockInventoryRead: result = await self.db.execute( text( "SELECT id, serial_no, product_name, pack, quantity, unit " "FROM stock_inventory WHERE id = :id" ), {"id": item_id}, ) row = result.mappings().one_or_none() if row is None: raise NotFoundError("Stock item not found") return StockInventoryRead.model_validate(row) async def _assert_stock_name_free(self, name: str, except_id: int | None = None) -> None: """A product appears once in the stock list — this also absorbs a double-submitted form.""" taken = ( await self.db.execute( text( "SELECT 1 FROM stock_inventory WHERE lower(btrim(product_name)) = lower(btrim(:name)) " "AND (CAST(:skip AS INTEGER) IS NULL OR id <> :skip) LIMIT 1" ), {"name": name, "skip": except_id}, ) ).first() if taken: raise ConflictError("This product is already in the stock list", {"product_name": name}) async def create_stock_item( self, data: StockInventoryCreate, actor: User, ip: str | None = None ) -> StockInventoryRead: await self._assert_stock_name_free(data.product_name) result = await self.db.execute( text( """ INSERT INTO stock_inventory (serial_no, product_name, pack, quantity, unit) VALUES ( (SELECT COALESCE(MAX(serial_no), 0) + 1 FROM stock_inventory), :product_name, :pack, :quantity, :unit ) RETURNING id """ ), data.model_dump(), ) item = await self._stock_row(result.scalar_one()) await self._audit_stock(actor, item, None, item.model_dump(), AuditAction.CREATE, ip) await self.db.commit() return item async def update_stock_item( self, item_id: int, data: StockInventoryUpdate, actor: User, ip: str | None = None ) -> StockInventoryRead: before = await self._stock_row(item_id) changes = data.model_dump(exclude_unset=True) if changes.get("product_name") is None: changes.pop("product_name", None) if "product_name" in changes: await self._assert_stock_name_free(changes["product_name"], except_id=item_id) if changes: assignments = ", ".join(f"{column} = :{column}" for column in changes) await self.db.execute( text(f"UPDATE stock_inventory SET {assignments} WHERE id = :id"), {**changes, "id": item_id}, ) after = await self._stock_row(item_id) await self._audit_stock( actor, after, before.model_dump(), after.model_dump(), AuditAction.UPDATE, ip ) await self.db.commit() return after async def delete_stock_item(self, item_id: int, actor: User, ip: str | None = None) -> None: before = await self._stock_row(item_id) await self.db.execute(text("DELETE FROM stock_inventory WHERE id = :id"), {"id": item_id}) # keep the printed "No." column gap-free, like the company forms do await self.db.execute( text( """ WITH numbered AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY serial_no, id) AS n FROM stock_inventory ) UPDATE stock_inventory s SET serial_no = numbered.n FROM numbered WHERE s.id = numbered.id AND s.serial_no <> numbered.n """ ) ) await self._audit_stock(actor, before, before.model_dump(), None, AuditAction.DELETE, ip) await self.db.commit() async def _audit_stock(self, actor, item, before, after, action, ip) -> None: # entity_id is a UUID column; stock rows use integer ids, so derive a stable UUID. await self.audit.log( actor_user_id=actor.id, entity_type=EntityType.PRODUCT, entity_id=uuid.uuid5(uuid.NAMESPACE_OID, f"stock_inventory:{item.id}"), action=action, before=before, after=after, ip_address=ip, ) async def create(self, data: ProductCreate, actor: User, ip: str | None = None) -> Product: product = Product(company_id=actor.company_id, **data.model_dump()) self.db.add(product) try: await self.db.flush() except IntegrityError as exc: await self.db.rollback() raise ConflictError( "A product with this name already exists", {"cause": "uq_product_company_name"} ) from exc await self.audit.log( actor_user_id=actor.id, entity_type=EntityType.PRODUCT, entity_id=product.id, action=AuditAction.CREATE, after=snapshot(product, _AUDIT_FIELDS), ip_address=ip, ) await self.db.commit() return product async def update( self, product_id: uuid.UUID, data: ProductUpdate, actor: User, ip: str | None = None ) -> Product: product = await self.db.get(Product, product_id) if product is None or product.company_id != actor.company_id: raise NotFoundError("Product not found") before = snapshot(product, _AUDIT_FIELDS) for field, value in data.model_dump(exclude_unset=True).items(): setattr(product, field, value) await self.audit.log( actor_user_id=actor.id, entity_type=EntityType.PRODUCT, entity_id=product.id, action=AuditAction.UPDATE, before=before, after=snapshot(product, _AUDIT_FIELDS), ip_address=ip, ) try: await self.db.commit() except IntegrityError as exc: await self.db.rollback() raise ConflictError( "A product with this name already exists", {"cause": "uq_product_company_name"} ) from exc return product