from sqlalchemy.orm import Session from app.models.order import Order, OrderItem from app.models.product import ProductComponent from app.models.inventory import InventoryItem, InventoryReservation, InventoryMovement from sqlalchemy import select from sqlalchemy.sql import func from datetime import datetime import time # Orders def get_order(db: Session, order_id: int): return db.query(Order).filter(Order.id == order_id).first() def get_orders(db: Session, skip: int = 0, limit: int = 100): return db.query(Order).offset(skip).limit(limit).all() def create_order(db: Session, *, order_data: dict): items_data = order_data.pop('items', None) # auto-generate order_number if not provided if not order_data.get('order_number'): # use millisecond timestamp to ensure uniqueness order_data['order_number'] = f"ORD{int(time.time() * 1000)}" order = Order(**order_data) db.add(order) db.commit() db.refresh(order) if items_data: for it in items_data: it['order_id'] = order.id oi = OrderItem(**it) db.add(oi) db.commit() db.refresh(order) return order def get_orders_by_cedula(db: Session, cedula: str, skip: int = 0, limit: int = 100): from app.models.client import Client return ( db.query(Order) .join(Client, Order.client_id == Client.id) .filter(Client.cedula == cedula) .offset(skip) .limit(limit) .all() ) def update_order(db: Session, order: Order, updates: dict): for k, v in updates.items(): setattr(order, k, v) db.add(order) db.commit() db.refresh(order) return order def delete_order(db: Session, order: Order): # remove associated items first to avoid FK constraint issues db.query(OrderItem).filter(OrderItem.order_id == order.id).delete() db.delete(order) db.commit() return True # Order Items def get_order_item(db: Session, item_id: int): return db.query(OrderItem).filter(OrderItem.id == item_id).first() def get_items_for_order(db: Session, order_id: int): return db.query(OrderItem).filter(OrderItem.order_id == order_id).all() def create_order_item(db: Session, *, item_data: dict): oi = OrderItem(**item_data) db.add(oi) db.commit() db.refresh(oi) # Reserve inventory according to BOM for the product variant components = db.query(ProductComponent).filter(ProductComponent.product_variant_id == oi.product_variant_id).all() for comp in components: inv_item = db.query(InventoryItem).filter(InventoryItem.id == comp.inventory_item_id).with_for_update().one() qty_needed = comp.qty_per_unit * oi.quantity if inv_item.quantity_on_hand is None: inv_item.quantity_on_hand = 0 if inv_item.quantity_on_hand < qty_needed: # rollback and raise db.rollback() raise RuntimeError(f"Insufficient inventory for item {inv_item.name} (need {qty_needed}, have {inv_item.quantity_on_hand})") inv_item.quantity_on_hand = inv_item.quantity_on_hand - qty_needed # create movement log mov = InventoryMovement( inventory_item_id=inv_item.id, change_qty=-qty_needed, movement_type="reservation", reference_type="order_item", reference_id=oi.id, notes=f"Reserved for order_item {oi.id}", ) db.add(mov) # create reservation record res = InventoryReservation( order_item_id=oi.id, inventory_item_id=inv_item.id, qty_reserved=qty_needed, ) db.add(res) db.commit() db.refresh(oi) return oi def update_order_item(db: Session, item: OrderItem, updates: dict): for k, v in updates.items(): setattr(item, k, v) db.add(item) db.commit() db.refresh(item) return item def delete_order_item(db: Session, item: OrderItem): db.delete(item) db.commit() return True def release_reservations_for_item(db: Session, item_id: int): """Release all active reservations for a given order_item. This will increase `quantity_on_hand` for each inventory item, create an InventoryMovement with positive qty, and mark the InventoryReservation.released_at timestamp. Returns the number of reservations released. """ reservations = ( db.query(InventoryReservation) .filter(InventoryReservation.order_item_id == item_id, InventoryReservation.released_at == None) .with_for_update() .all() ) if not reservations: return 0 for res in reservations: inv_item = db.query(InventoryItem).filter(InventoryItem.id == res.inventory_item_id).with_for_update().one() inv_item.quantity_on_hand = (inv_item.quantity_on_hand or 0) + res.qty_reserved mov = InventoryMovement( inventory_item_id=inv_item.id, change_qty=res.qty_reserved, movement_type="reservation_release", reference_type="order_item", reference_id=item_id, notes=f"Released reservation for order_item {item_id}", ) db.add(mov) res.released_at = func.now() db.add(res) db.commit() return len(reservations) def release_reservations_for_order(db: Session, order_id: int): """Release all reservations for a given order (all items).""" items = db.query(OrderItem).filter(OrderItem.order_id == order_id).all() total = 0 for it in items: total += release_reservations_for_item(db, it.id) return total def finalize_reservations_for_order(db: Session, order_id: int): """Mark reservations as consumed (finalized) so they cannot be released. This assumes inventory quantity was already decremented at reservation time. We mark `consumed_at` and create a movement log for traceability. """ reservations = ( db.query(InventoryReservation) .join(OrderItem, InventoryReservation.order_item_id == OrderItem.id) .filter(OrderItem.order_id == order_id, InventoryReservation.released_at == None, InventoryReservation.consumed_at == None) .with_for_update() .all() ) if not reservations: return 0 for res in reservations: inv_item = db.query(InventoryItem).filter(InventoryItem.id == res.inventory_item_id).with_for_update().one() # create finalization movement (for audit) mov = InventoryMovement( inventory_item_id=inv_item.id, change_qty=0.0, movement_type="consumption_finalized", reference_type="order", reference_id=order_id, notes=f"Finalized consumption for reservation {res.id}", ) db.add(mov) res.consumed_at = func.now() db.add(res) db.commit() return len(reservations)