xenia-pos-local/local_backend/models/pricing.py

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])