bambuddy/backend/app/models/spool.py

from datetime import datetime

from sqlalchemy import Boolean, DateTime, Float, ForeignKey, Integer, String, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from backend.app.core.database import Base


class Spool(Base):
    """Spool inventory item for tracking filament spools and their properties."""

    __tablename__ = "spool"
    # A freed id must not pass to a new spool: its usage history would follow.
    __table_args__ = {"sqlite_autoincrement": True}

    id: Mapped[int] = mapped_column(primary_key=True)
    material: Mapped[str] = mapped_column(String(50))  # PLA, PETG, ABS, etc.
    subtype: Mapped[str | None] = mapped_column(String(50))  # Basic, Matte, Silk, etc.
    color_name: Mapped[str | None] = mapped_column(String(100))  # "Jade White"
    rgba: Mapped[str | None] = mapped_column(String(8))  # RRGGBBAA hex
    # Multi-colour gradient stops for filaments with more than one colour
    # (e.g. tri-colour, multi-colour). Stored as comma-separated 6- or 8-char
    # hex tokens without `#`. Empty/NULL means solid (uses `rgba`). Up to 8
    # stops; combination mode is driven by `subtype` (Gradient, Multicolor).
    extra_colors: Mapped[str | None] = mapped_column(String(255))
    # Visual effect overlay independent of subtype: sparkle, wood, marble,
    # glow, matte. Purely a rendering hint — does not affect MQTT/firmware.
    effect_type: Mapped[str | None] = mapped_column(String(20))
    brand: Mapped[str | None] = mapped_column(String(100))  # "Polymaker"
    label_weight: Mapped[int] = mapped_column(Integer, default=1000)  # Advertised net weight (g)
    core_weight: Mapped[int] = mapped_column(Integer, default=250)  # Empty spool weight (g)
    core_weight_catalog_id: Mapped[int | None] = mapped_column(
        Integer
    )  # Reference to spool_catalog entry for core weight
    weight_used: Mapped[float] = mapped_column(Float, default=0)  # Consumed grams
    # Anchor for the resettable "Total Consumed" stat. The displayed counter
    # is `weight_used - weight_used_baseline`; the Inventory page's "Reset
    # usage to 0" action stamps baseline = weight_used so the counter zeroes
    # without disturbing remaining (= label_weight - weight_used). Matches
    # Spoolman's split between used_weight and remaining_weight (#1390).
    weight_used_baseline: Mapped[float] = mapped_column(Float, default=0, server_default="0")
    weight_locked: Mapped[bool] = mapped_column(Boolean, default=False)  # Lock weight from AMS auto-sync
    last_scale_weight: Mapped[int | None] = mapped_column(Integer)  # Last gross weight from scale (g)
    last_weighed_at: Mapped[datetime | None] = mapped_column(DateTime)  # When last weighed
    slicer_filament: Mapped[str | None] = mapped_column(String(50))  # Preset ID (e.g. "GFL99")
    slicer_filament_name: Mapped[str | None] = mapped_column(String(100))  # Preset name for slicer
    nozzle_temp_min: Mapped[int | None] = mapped_column()  # Override min temp
    nozzle_temp_max: Mapped[int | None] = mapped_column()  # Override max temp
    note: Mapped[str | None] = mapped_column(String(500))
    added_full: Mapped[bool | None] = mapped_column()  # Whether spool was added as full (unused)

    # User-defined category (e.g. "Production", "Prototype", "Client A") for
    # filtering and per-group low-stock thresholds (#729). Free text — the
    # form autocompletes from categories already present on other spools.
    category: Mapped[str | None] = mapped_column(String(50))
    # Per-spool override of the global inventory low-stock threshold (%).
    # NULL falls back to the `low_stock_threshold` setting. Lets users mark
    # production spools with a higher threshold (alert earlier) and prototype
    # spools with a lower one without changing the global default.
    low_stock_threshold_pct: Mapped[int | None] = mapped_column(Integer)

    # Internal material / article number (#2870): the identifier a business
    # purchases and costs by (e.g. "15" = Bambu Lab PLA Basic), distinct from
    # `category` (production grouping) and `note` (free text). Free text, no
    # uniqueness — several spools of the same product share the number, which
    # is exactly what makes it a sort/filter/statistics key. New spools of a
    # matching product inherit it on creation (services/material_number.py,
    # applied by the spool create routes and the RFID auto-add).
    material_number: Mapped[str | None] = mapped_column(String(64))

    # Cost tracking
    cost_per_kg: Mapped[float | None] = mapped_column(Float)  # Cost per kilogram

    storage_location: Mapped[str | None] = mapped_column(String(255))  # User-editable storage location
    location_id: Mapped[int | None] = mapped_column(ForeignKey("locations.id"), index=True)

    last_used: Mapped[datetime | None] = mapped_column(DateTime)  # Last time this spool was used in a print
    # Last drying (#2863): stamped when an AMS drying cycle that held this spool
    # ends having run at least half its length, or set by hand in the spool
    # form for an external dryer. Temperature and hours are only known for
    # cycles Bambuddy watched or started, and a hand-set date clears both.
    last_dried_at: Mapped[datetime | None] = mapped_column(DateTime)
    last_dried_temp: Mapped[int | None] = mapped_column(Integer)  # Target °C
    last_dried_hours: Mapped[float | None] = mapped_column(Float)  # Time actually spent drying
    encode_time: Mapped[datetime | None] = mapped_column(DateTime)  # When spool was encoded/written to tag
    tag_uid: Mapped[str | None] = mapped_column(String(32))  # RFID tag UID (up to 32 hex chars)
    tray_uuid: Mapped[str | None] = mapped_column(String(32))  # Bambu Lab spool UUID (32 hex chars)
    data_origin: Mapped[str | None] = mapped_column(String(20))  # How data was populated: manual, rfid_auto, nfc_link
    tag_type: Mapped[str | None] = mapped_column(String(20))  # Tag vendor: bambulab, generic, etc.
    archived_at: Mapped[datetime | None] = mapped_column(DateTime)  # NULL = active
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), onupdate=func.now())

    k_profiles: Mapped[list["SpoolKProfile"]] = relationship(back_populates="spool", cascade="all, delete-orphan")
    # Per-printer-model preset overrides. Deliberately NOT embedded in
    # SpoolResponse the way k_profiles is: the inventory list returns every
    # spool a user owns, and this list is only ever read by the spool form
    # and the assign path, both of which fetch it for one spool at a time.
    filament_presets: Mapped[list["SpoolFilamentPreset"]] = relationship(
        back_populates="spool", cascade="all, delete-orphan"
    )
    assignments: Mapped[list["SpoolAssignment"]] = relationship(back_populates="spool", cascade="all, delete-orphan")
    location: Mapped["Location | None"] = relationship(back_populates="spools")
    # Supplier assignments (#2988): where this product can be bought, with
    # per-assignment article number / price and a purchase-source marker.
    # Default loader like every other relationship here — the handful of
    # routes that embed them ask for selectinload() at the query site.
    supplier_links: Mapped[list["SpoolSupplier"]] = relationship(back_populates="spool", cascade="all, delete-orphan")


from backend.app.models.location import Location  # noqa: E402
from backend.app.models.spool_assignment import SpoolAssignment  # noqa: E402
from backend.app.models.spool_filament_preset import SpoolFilamentPreset  # noqa: E402
from backend.app.models.spool_k_profile import SpoolKProfile  # noqa: E402
from backend.app.models.supplier import SpoolSupplier  # noqa: E402