Purchasing recipe-explosion ordering tool - store order totals become a raw-material purchase list in one pass; multi-level BOM with stock and in-transit netting, over-order remainder shown so accumulation is visible

You are a coding agent. Build a single-file HTML "recipe
explosion ordering" tool for central kitchen purchasing:
paste store order totals, explode them through the recipe
book into a raw-material purchase list. Runs locally in the
browser - no network, no dependencies, no external
resources.

1. Inputs (three pasteable tables)
   a. Order totals: store / finished item / quantity /
      need-by date
   b. Recipe table (BOM): parent / child / unit usage -
      multiple levels allowed: finished items consume
      sub-recipes, sub-recipes consume raw materials
      (braised pork bun → braising liquid → spices);
      unit usage is per one unit of the parent
   c. Stock and in-transit: child item / on-hand /
      in-transit qty / available date

2. Computation (explosion logic is fixed)
   - Multi-level explosion: walk from finished items down
     to the lowest level; where the same child appears on
     multiple paths, merge gross demand
   - Net requirement = gross demand − stock and in-transit
     whose available date is not later than the need-by
     date; never negative
   - Sub-recipes first: a child marked "make in-house"
     nets against its own stock first; only the gap
     explodes down to its children
   - Supplier pack conversion: each purchase item carries
     a pack size (e.g. 25kg per case); order qty = net
     requirement rounded up to a whole multiple of the
     pack
   - Remainder as its own column: the over-ordered amount
     from rounding. Weekly accumulation of the same
     item's remainder is common hidden stock build-up -
     the tool doesn't judge it for you, it makes you see
     it

3. Output
   - Table 1, purchase list: material / net requirement /
     pack size / suggested qty / remainder / finished
     items pulling it (which orders drove it)
   - Table 2, sub-recipe production note: net gaps per
     in-house item, copyable for the head chef's
     production plan
   - Totals row + one-click copy as tab-separated text
     (pastes cleanly into WeChat and Excel) + CSV export

4. Error guards
   - Circular BOM (a child that resolves back to itself):
     detect before exploding; on detection, list the
     cycle path and refuse to compute - never loop
   - Rows with blank or zero unit usage: flagged red and
     excluded from computation
   - A material with no stock row: net requirement
     defaults to gross, flagged in the output
   - In-transit stock available after the need-by date
     may not net against that requirement

5. Data
   - Recipe and pack-size tables persist in localStorage
     with JSON export/import for backup; order totals are
     paste-and-go, never saved
   - No price computation - price is a purchasing
     negotiation variable, and baking it into a tool
     teaches people to read it as a fixed cost

UI: desktop first, three input blocks on one screen,
result tables directly selectable and copyable. Deliver
one complete HTML file with all CSS and JS inlined, plus
a Chinese usage note under 200 characters.

Why it is written this way

Multi-level explosion with cycle detection is the tool's real threshold - the part Excel cannot do gracefully. Purchasing hand-splits three recipe levels in a spreadsheet where any order change forces a full recalculation and any recipe revision (the braising liquid changed) forces row-by-row checking. The most error-prone step in that manual grind is exactly "the same material on multiple paths must merge": spices appear in both the braising liquid and the marinade, and hand-kept subtotals miss a column and under-order. The tool fixes merging, netting, and rounding rules once; people maintain only two master tables - recipes and pack sizes.

Netting sub-recipe stock before exploding is what separates a central kitchen from ordinary restaurant ordering: for "2000 braised pork buns," a pure trading logic explodes straight down to spices, but the kitchen still has 80kg of braising liquid in the tank - skip that netting and purchasing buys a batch of spices it doesn't need. Treating in-house items as an inventoried middle level also yields the production note (Table 2) for free: the chef's gap numbers and the buyer's material numbers come from the same explosion, so the two departments never reconcile.

The in-transit available-date check exists because of a hole someone already fell into: in-transit quantities look like they net against demand, but with the truck arriving Monday and production scheduled Sunday, the numbers offset while the goods run out. Requiring the available date to clear the need-by date before netting welds that hole shut once.

Showing the remainder without recommending anything is deliberate restraint: the over-ordered bit looks harmless this week, and week after week it becomes slowly growing stock in the chiller. The tool's job is to move "an extra 7kg every time" from invisible to visible; whether to add safety stock or negotiate smaller packs is a decision for the person holding the number. Data tools help people see - they don't decide for them.

Variables