Orchard Harvest Crates

1"""Orchard Crates batch ETL built with Pixeltable.
2
3Import harvest crate rows from CSV, derive packing math with pixeltable.functions.math
4(abs/ceil/floor/round/sqrt/log/pow), validate moisture with an on_error=ignore UDF, then
5export an enriched CSV. Schema evolution drops the temporary ingest_batch column and adds
6shelf_life_days + density_pow. Drive with `python run.py` — no HTTP router.
7
8Layout: UDFs live in udfs.py; this file holds table models and @pxt.query functions.
9
10Run locally:
11    pxt schema update app.py orchard
12    python run.py
13"""
14from __future__ import annotations
15
16import pixeltable as pxt
17import pixeltable.functions as pxtf
18from pixeltable.functions import math as pxtm
19
20from udfs import crate_tag, grade_lane, moisture_ok
21
22TableModel = pxt.model_base()
23
24
25class Crates(TableModel, name='crates', has_default_idxs=False):
26    """One orchard harvest crate with packing math and moisture validation."""
27
28    crate_id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
29    crate_code: pxt.String
30    orchard: pxt.String
31    variety: pxt.String
32    weight_kg: pxt.Float
33    length_cm: pxt.Float
34    width_cm: pxt.Float
35    height_cm: pxt.Float
36    moisture_pct: pxt.Float
37
38    # --- fn.math primary helpers ---
39    volume_cm3 = length_cm * width_cm * height_cm
40    abs_weight = pxtm.abs(weight_kg)
41    weight_ceil = pxtm.ceil(weight_kg)
42    weight_floor = pxtm.floor(weight_kg)
43    weight_rounded = pxtm.round(weight_kg, 1)
44    side_sqrt = pxtm.sqrt(pxtm.abs(length_cm))
45    volume_log = pxtm.log(pxtm.abs(volume_cm3) + 1.0)
46    density = weight_kg / (volume_cm3 / 1000.0)  # kg / liter
47    density_pow = pxtm.pow(pxtm.abs(density) + 0.01, 0.5)  # added in schema evolution
48    shelf_life_days = pxtm.floor(30.0 - pxtm.abs(moisture_pct - 12.0))  # evolution add
49
50    # --- on_error UDF path ---
51    moisture_checked = moisture_ok(moisture_pct)
52    lane = grade_lane(variety, weight_kg)
53    tag = crate_tag(crate_code, orchard)
54
55    __indexes__ = [
56        pxt.BtreeIndex(orchard),
57        pxt.BtreeIndex(variety),
58        pxt.BtreeIndex(lane),
59    ]
60
61
62@pxt.query
63def by_orchard(orchard: str):
64    """Crates for one orchard (orchard index)."""
65    return (
66        Crates.where(Crates.orchard == orchard)
67        .select(
68            Crates.crate_code,
69            Crates.variety,
70            Crates.weight_kg,
71            Crates.density,
72            Crates.lane,
73            Crates.tag,
74            Crates.shelf_life_days,
75        )
76        .order_by(Crates.crate_code)
77    )
78
79
80@pxt.query
81def heavy_lanes(min_weight: float):
82    """Crates at or above min_weight with packing math."""
83    return (
84        Crates.where(Crates.weight_kg >= min_weight)
85        .select(
86            Crates.crate_code,
87            Crates.orchard,
88            Crates.weight_rounded,
89            Crates.volume_cm3,
90            Crates.density,
91            Crates.density_pow,
92            Crates.lane,
93        )
94        .order_by(Crates.weight_kg, asc=False)
95    )
96
97
98@pxt.query
99def moisture_failures():
100    """Rows where moisture_ok failed (on_error=ignore → errortype set)."""
101    return (
102        Crates.where(Crates.moisture_checked.errortype != None)  # noqa: E711
103        .select(
104            Crates.crate_code,
105            Crates.moisture_pct,
106            Crates.moisture_checked,
107            err=Crates.moisture_checked.errortype,
108            msg=Crates.moisture_checked.errormsg,
109        )
110        .order_by(Crates.crate_code)
111    )