from sqlalchemy import Column, Integer, String, Boolean, Float, DateTime, ForeignKey, Text from sqlalchemy.orm import relationship from datetime import datetime, timezone from database import Base def _utcnow(): return datetime.now(timezone.utc) class PriceGroup(Base): """Named manual toggle (e.g. 'Happy Hour'). Modifiers reference these by ID.""" __tablename__ = "price_groups" id = Column(Integer, primary_key=True, index=True) name = Column(String, nullable=False) description = Column(Text, nullable=True) color = Column(String, nullable=True) is_active = Column(Integer, nullable=False, default=0) # 0|1 # Optional auto-schedule: flip is_active based on time + day of week. # Evaluated at condition-check time — no background daemon needed. auto_enable_time = Column(String, nullable=True) # "HH:MM" 24h auto_disable_time = Column(String, nullable=True) # "HH:MM" 24h auto_days = Column(Text, nullable=True) # JSON [0..6], Mon=0 created_at = Column(DateTime(timezone=True), default=_utcnow) created_by = Column(Integer, ForeignKey("users.id"), nullable=True) class PriceModifier(Base): """ A single pricing rule. Can be item-scoped (applies only to one product) or global (applies to all targets listed in PriceModifierTarget). Evaluation order is controlled by sort_order (ascending = higher priority). Stacking algorithm: - Collect all passing modifiers for a product. - Separate into stackable (allow_stack=1) and non-stackable. - Apply the FIRST passing non-stackable modifier (sort_order ascending). - Apply ALL passing stackable modifiers in sort_order order, on the running price. - Apply final system rounding to nearest 0.10 after all modifiers. """ __tablename__ = "price_modifiers" id = Column(Integer, primary_key=True, index=True) name = Column(String, nullable=False) description = Column(Text, nullable=True) color = Column(String, nullable=True) is_active = Column(Integer, nullable=False, default=1) # quick on/off toggle is_favorite = Column(Integer, nullable=False, default=0) # show on dashboard allow_stack = Column(Integer, nullable=False, default=0) # participates in stackable pile sort_order = Column(Integer, nullable=False, default=0) # user-controlled priority # Scope scope = Column(String, nullable=False, default="global") # 'item' | 'global' item_id = Column(Integer, ForeignKey("products.id"), nullable=True) # set when scope='item' # Action — what happens to the price when this modifier fires action_type = Column(String, nullable=False) # 'set' | 'add_amount' | 'add_percent' action_value = Column(Float, nullable=False) # the number; negative = discount # Optional per-modifier rounding applied immediately after this modifier's math. # System-wide nearest-0.10 rounding is always applied as a final pass regardless. round_to = Column(String, nullable=True) # '0.05'|'0.10'|'0.20'|'0.50'|'x.99'|'x.00' created_at = Column(DateTime(timezone=True), default=_utcnow) created_by = Column(Integer, ForeignKey("users.id"), nullable=True) updated_at = Column(DateTime(timezone=True), nullable=True) updated_by = Column(Integer, ForeignKey("users.id"), nullable=True) conditions = relationship("PriceModifierCondition", back_populates="modifier", cascade="all, delete-orphan") targets = relationship("PriceModifierTarget", back_populates="modifier", cascade="all, delete-orphan") item = relationship("Product", foreign_keys=[item_id]) class PriceModifierCondition(Base): """ One condition on a modifier. ALL conditions must pass for the modifier to fire (AND logic). A modifier with zero conditions is 'manual-only' — never fires automatically. condition_type registry and params shapes: time_range {"from": "HH:MM", "to": "HH:MM"} (crosses midnight if from > to) date_range {"from": "YYYY-MM-DD", "to": "YYYY-MM-DD"} (inclusive) specific_date {"dates": ["YYYY-MM-DD", ...]} day_of_week {"days": [0,1,2,3,4,5,6]} (Mon=0 Sun=6) min_item_quantity {"min": N} (this product's qty in cart) min_category_qty {"category_id": N, "min": N} (items in category in cart) min_order_value {"min": 0.00} (cart total before modifiers) order_channel {"channels": ["pos","online","qr","takeaway"]} price_group_active {"price_group_id": N} user_tier {"tiers": ["bronze","silver","gold"]} (placeholder, needs user accounts) low_stock {"threshold": N} (placeholder, needs inventory) """ __tablename__ = "price_modifier_conditions" id = Column(Integer, primary_key=True, index=True) modifier_id = Column(Integer, ForeignKey("price_modifiers.id", ondelete="CASCADE"), nullable=False) condition_type = Column(String, nullable=False) params = Column(Text, nullable=False, default="{}") # JSON modifier = relationship("PriceModifier", back_populates="conditions") class PriceModifierTarget(Base): """ Target rows exist only for global modifiers. Defines which products the modifier applies to. A modifier matches a product if ANY of its target rows covers that product (OR across rows). target_type='all' → applies to every product (target_id and target_tag are NULL) target_type='item' → target_id is a product id target_type='category' → target_id is a category id (includes subcategories) target_type='prep_zone' → target_id is a prep_zone id target_type='tag' → target_tag is a string matched against products.tags JSON array """ __tablename__ = "price_modifier_targets" id = Column(Integer, primary_key=True, index=True) modifier_id = Column(Integer, ForeignKey("price_modifiers.id", ondelete="CASCADE"), nullable=False) target_type = Column(String, nullable=False) # 'all'|'item'|'category'|'prep_zone'|'tag' target_id = Column(Integer, nullable=True) # legacy single value target_tag = Column(String, nullable=True) # legacy single value target_ids = Column(Text, nullable=True) # JSON array of ints (multi-select) target_tags = Column(Text, nullable=True) # JSON array of strings (multi-tag) modifier = relationship("PriceModifier", back_populates="targets") class Deal(Base): """ Event-driven promotion. When deal_conditions + deal_targets are met (a product/category/tag is present in the cart in sufficient quantity), the action fires. Evaluated on every add-items call. If conditions are newly met, the waiter is notified via the response payload and prompted to confirm/select (never auto-applied silently). action_type registry: apply_modifier → action_modifier_id (FK to PriceModifier with no conditions) set_price → action_value (exact new unit price) add_amount → action_value (delta; negative = discount) add_percent → action_value (percent; negative = discount) free_item → action_free_item_id (specific product added free) free_choice → pool defined by action_free_target_type + action_free_target_ids """ __tablename__ = "deals" id = Column(Integer, primary_key=True, index=True) name = Column(String, nullable=False) description = Column(Text, nullable=True) color = Column(String, nullable=True) is_active = Column(Integer, nullable=False, default=1) sort_order = Column(Integer, nullable=False, default=0) # Action action_type = Column(String, nullable=False) action_modifier_id = Column(Integer, ForeignKey("price_modifiers.id"), nullable=True) action_value = Column(Float, nullable=True) action_free_item_id = Column(Integer, ForeignKey("products.id"), nullable=True) action_free_target_type = Column(String, nullable=True) # 'item'|'category'|'tag' action_free_target_ids = Column(Text, nullable=True) # JSON array of IDs or tag strings action_free_quantity = Column(Integer, nullable=False, default=1) created_at = Column(DateTime(timezone=True), default=_utcnow) created_by = Column(Integer, ForeignKey("users.id"), nullable=True) conditions = relationship("DealCondition", back_populates="deal", cascade="all, delete-orphan") targets = relationship("DealTarget", back_populates="deal", cascade="all, delete-orphan") action_modifier = relationship("PriceModifier", foreign_keys=[action_modifier_id]) action_free_item = relationship("Product", foreign_keys=[action_free_item_id]) class DealCondition(Base): """Same condition_type registry as PriceModifierCondition.""" __tablename__ = "deal_conditions" id = Column(Integer, primary_key=True, index=True) deal_id = Column(Integer, ForeignKey("deals.id", ondelete="CASCADE"), nullable=False) condition_type = Column(String, nullable=False) params = Column(Text, nullable=False, default="{}") # JSON deal = relationship("Deal", back_populates="conditions") class DealTarget(Base): """ Defines the 'buy THESE' side of a deal (what must be in the cart for the deal to trigger). Multiple rows are OR-combined: any matching item satisfies a row. """ __tablename__ = "deal_targets" id = Column(Integer, primary_key=True, index=True) deal_id = Column(Integer, ForeignKey("deals.id", ondelete="CASCADE"), nullable=False) target_type = Column(String, nullable=False) # 'item'|'category'|'tag'|'any' target_id = Column(Integer, nullable=True) # legacy single value target_tag = Column(String, nullable=True) # legacy single value target_ids = Column(Text, nullable=True) # JSON array of ints (multi-select) target_tags = Column(Text, nullable=True) # JSON array of strings (multi-tag) deal = relationship("Deal", back_populates="targets") class PriceEventLog(Base): """ Immutable append-only audit record. Written for every pricing event on an order: automatic modifier fires, deal triggers/accepts/dismisses, waiter discounts, free items. Nothing is deleted from this table. """ __tablename__ = "price_event_log" id = Column(Integer, primary_key=True, index=True) order_id = Column(Integer, ForeignKey("orders.id"), nullable=False) order_item_id = Column(Integer, ForeignKey("order_items.id"), nullable=True) # NULL for order-level events # Event classification event_type = Column(String, nullable=False) # modifier_applied — automatic price modifier fired # deal_triggered — deal conditions met, action executed on a specific item # deal_offer_shown — waiter was shown a free-choice prompt # deal_offer_accepted — waiter confirmed and selected item(s) # deal_offer_dismissed — waiter dismissed the deal offer # waiter_discount — manual waiter discount applied # free_item_added — free item added to order as result of a deal modifier_id = Column(Integer, ForeignKey("price_modifiers.id"), nullable=True) deal_id = Column(Integer, ForeignKey("deals.id"), nullable=True) # Price delta — NULL for events that don't change a price (offer_shown, dismissed) price_before = Column(Float, nullable=True) price_after = Column(Float, nullable=True) delta_amount = Column(Float, nullable=True) # price_after - price_before; negative = discount # Waiter discount fields applied_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True) waiter_note = Column(Text, nullable=True) # Snapshot of the conditions that evaluated to True when the event fired. # JSON object: {"condition_type": evaluated_value, ...} # e.g. {"day_of_week": 5, "time_range": "21:45", "price_group_active": true} conditions_snapshot = Column(Text, nullable=True) # For deal_offer_accepted: product IDs the waiter chose selected_item_ids = Column(Text, nullable=True) # JSON array of product IDs applied_at = Column(DateTime(timezone=True), default=_utcnow) order = relationship("Order", foreign_keys=[order_id]) order_item = relationship("OrderItem", foreign_keys=[order_item_id]) modifier = relationship("PriceModifier", foreign_keys=[modifier_id]) deal = relationship("Deal", foreign_keys=[deal_id]) applied_by = relationship("User", foreign_keys=[applied_by_user_id]) class WaiterDiscountSettings(Base): """ Per-waiter discount limits. All limit fields are nullable — NULL means no limit. The master can_apply_discounts flag acts as an on/off switch for this waiter. Global limits are stored in pos_settings under the 'discounts.*' namespace. """ __tablename__ = "waiter_discount_settings" id = Column(Integer, primary_key=True, index=True) user_id = Column(Integer, ForeignKey("users.id"), nullable=False, unique=True) can_apply_discounts = Column(Integer, nullable=False, default=0) # 0|1 master toggle max_discount_percent = Column(Float, nullable=True) # e.g. 20.0 = max 20% off max_discount_amount = Column(Float, nullable=True) # max € off a single item max_total_value_shift = Column(Float, nullable=True) # max total € given away per shift max_total_value_workday = Column(Float, nullable=True) # max total € given away per workday max_items_per_shift = Column(Integer, nullable=True) max_items_per_workday = Column(Integer, nullable=True) max_items_per_order = Column(Integer, nullable=True) user = relationship("User", foreign_keys=[user_id])