Plan: Cotton PO Initial Orders Module

Repo: ~/ai-projects-local/rbd-reorder-tool

Goal

Add a Cotton POs module to the RBD Ordering Tool that:

1. Pulls raw sales history from MySQL (netsuite_transaction_items already synced from NetSuite)
2. Calculates suggested D&R yardage per SKU — replicating the existing Google Sheet formula
3. Presents a per-collection PO review UI for buyer adjustments
4. Tracks PO lifecycle status: Created → Done → Sent to Vendor → Imported
5. Exports formatted xlsx matching the existing Cotton PO template

/goal Command

/goal Add Cotton POs module to RBD Ordering Tool. Done when: (1) "Cotton POs" exists under PURCHASING in the nav; (2) collections list shows cotton collections with all 4 status flags (PO Created, PO Done, Sent to Vendor, Imported); (3) clicking a collection opens a per-SKU PO view with suggested D&R yardage calculated from netsuite_transaction_items sales history; (4) buyer can override individual SKU quantities and totals recalculate; (5) precuts section shows precut SKUs with unit totals; (6) all state persists to DB; (7) Export button produces a formatted xlsx. Validate end-to-end with Dapple Dot as the reference collection — compare D&R quantities against the existing Google Sheet.

Context

Existing tool: FastAPI (Python) + React/TypeScript (Vite + Tailwind)
Repo: ~/ai-projects-local/rbd-reorder-tool / GitHub: c-gorringe/rbd-reorder-tool
Database: MySQL at rb.alpineanalytica.com:3307 — rb_warehouse
Reference sheet: Google Sheets 16Gx-N9HK35MSLZfz5DAGJ6FhHN6u2VLdruwmiXIL4lE
Current migrations: 001–004 already deployed (005 pending if any)

What the existing sheet does

1. Data tab — raw NetSuite data (21 columns, A:U) auto-synced via Google Sheets connector from saved search "Initial Order Suggestion - Non Batik {AA}" (search ID 2425) and Dropship search (2941)
2. Collections tab — user lists collections, assigns PO#s, notes kits/precuts, tracks status flags
3. PO template tabs (one per collection) — per-SKU rows with D&R yardage + precut/kit/casepack/advanced yardage, rate, surcharges, totals. Sheet formulas calculate suggested D&R qty from raw sales data. Buyer overrides individual SKUs.
4. Export — Menu > PO Tools > Append active sheet to PO import → clean up → send to vendor

Data available in MySQL (confirmed)

• netsuite_transactions — sales orders and invoices
• netsuite_transaction_items — line items with amounts (known data quality issue: use IF(tran_date > '1970-01-01', tran_date, created_date) pattern)
• NOTE: No item master table confirmed yet — need to verify if item attributes (vendor, cost, class/collection, designer rank, MOQ) are present

Phase 1 Research — COMPLETE (2026-05-12)

Calculation Chain (fully reverse-engineered)

Step 1 — Preorder % (Data col Z "Preorder % of Total"):

base_pct = FORECAST_LINEAR(presale_qty, 
    x=[12,24,36,48,60,72,84,100,200,324],
    y=[30,30,40,45,50,55,60,68,69,70]) / 100
base_pct = max(0.3, min(1.0, base_pct))

# Item-type overrides:
if has_rot or has_laminate or item_type in ("CP15","ASY-FQ"):  pct = 1.0
elif item_type in ("SD","CP10"):                               pct = 0.92
elif group == "1YD":                                           pct = 0.70
else:                                                          pct = base_pct

Step 2 — Suggested QTY (Data col AA):

if item_type == "CP10":
    suggested = min(2, ceil(backorder_qty / preorder_pct / MOQ)) * MOQ
else:
    suggested = ceil(backorder_qty / preorder_pct / MOQ) * MOQ

Step 3 — D/R or Units (Data col AL):

dr_or_units = suggested_qty + au_qty  # AU = additional unit items (presales)

Step 4 — D&R YDS in PO template (col C):

other_yds = precut_yds + kit_yds + casepack_yds + rot_yds + advanced_yds
if dr_or_units + other_yds >= 500:
    dr_yds = dr_or_units
else:
    dr_yds = 500 - other_yds   # bump up to hit 500 minimum

Key Data Columns Mapped

Critical Gap: Item Master Not in MySQL

quantity_backordered and quantity are available. But MOQ (Q) and item type (W) are not in MySQL — they live in the NetSuite item master, which was never synced.

Resolution chosen: CSV seed file (Option 1)

• Buyer exports item master from NetSuite once → we import into cotton_item_master table
• Simple, works day 1, no API setup needed
• Table can be refreshed periodically by re-importing updated CSV

Remaining Open Questions

Architecture

New DB Tables (Migration 006)

CREATE TABLE cotton_collections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  vendor VARCHAR(255),
  po_number VARCHAR(100),
  ship_date DATE,
  advanced_ship_date DATE,
  designer_rank VARCHAR(50),
  collection_rank VARCHAR(50),
  intended_lifecycle VARCHAR(100),
  has_kits BOOLEAN DEFAULT FALSE,
  has_precuts BOOLEAN DEFAULT FALSE,
  po_created BOOLEAN DEFAULT FALSE,
  po_done BOOLEAN DEFAULT FALSE,
  sent_to_vendor BOOLEAN DEFAULT FALSE,
  imported BOOLEAN DEFAULT FALSE,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cotton_po_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  collection_id INT NOT NULL REFERENCES cotton_collections(id),
  sku VARCHAR(100) NOT NULL,
  name VARCHAR(500),
  dr_yds_suggested INT DEFAULT 0,   -- calculated from formula, read-only
  dr_yds_override INT,              -- buyer override; NULL = use suggested
  precut_yds INT DEFAULT 0,
  kit_yds INT DEFAULT 0,
  laminate_yds INT DEFAULT 0,
  casepack_yds INT DEFAULT 0,
  rot_yds INT DEFAULT 0,
  advanced_yds INT DEFAULT 0,
  bolt_size INT,
  rate DECIMAL(10,4),
  surcharge_dr15 DECIMAL(10,2) DEFAULT 0,
  surcharge_dr10 DECIMAL(10,2) DEFAULT 0,
  surcharge_laminate DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cotton_precut_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  collection_id INT NOT NULL REFERENCES cotton_collections(id),
  sku VARCHAR(100) NOT NULL,
  description VARCHAR(500),
  total_units INT DEFAULT 0
);

CREATE TABLE cotton_stacking_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  collection_id INT NOT NULL REFERENCES cotton_collections(id),
  position INT NOT NULL,
  fabric_sku VARCHAR(100) NOT NULL,
  in_fq_48 BOOLEAN DEFAULT FALSE,
  in_fq_22 BOOLEAN DEFAULT FALSE,
  in_fq_12 BOOLEAN DEFAULT FALSE,
  in_5_squares BOOLEAN DEFAULT FALSE,
  in_10_squares BOOLEAN DEFAULT FALSE,
  in_rp_strips BOOLEAN DEFAULT FALSE,
  total_yds INT DEFAULT 0
);

Backend (~/ai-projects-local/rbd-reorder-tool/backend/)

New files:

• routers/cotton_pos.py — CRUD for collections, items, status updates, xlsx export
• services/cotton_calc.py — D&R quantity calculation engine (replicate sheet formula)
• services/netsuite_query.py — Query MySQL netsuite_transaction_items for sales history by SKU prefix

Frontend (~/ai-projects-local/rbd-reorder-tool/frontend/src/)

New files:

• pages/CottonPOListPage.tsx — collections table with status chips + "New Collection" button
• pages/CottonPODetailPage.tsx — per-collection PO: header, SKU table with override inputs, precuts section, export button
• components/CottonPOItemRow.tsx — single SKU row with inline override

Nav update in Layout.tsx:

• Add PURCHASING group: Cotton POs | (Batik POs later) | (Liberty POs later)
• Keep existing ORDERING group: Reorder | Preorder

Export Format

xlsx matching the current Cotton PO template:

• Header: PO#, ship date, advanced ship date
• Cotton rows: SKU, Name, D&R yds, Precut yds, Kit yds, Laminate, Casepack, ROT, Advanced, Total, Rate, Amount, Surcharges, Total
• Totals row
• Precuts summary section
• Stacking order section

Implementation Phases

Phase 1 — Research (resolve open questions, ~1 day)

• ☐ Open Google Sheet in browser, inspect D&R formula cells for a known collection (Dapple Dot)
• ☐ Read Data tab headers (columns A:U) to understand what fields come from NetSuite
• ☐ SSH to rb.alpineanalytica.com, run SHOW COLUMNS FROM netsuite_transaction_items and SHOW COLUMNS FROM netsuite_transactions
• ☐ Check if item master / item attributes exist in any MySQL table
• ☐ Clarify rates source and CP10-2 casepack logic with Cole
• ☐ Exit criteria: formula understood, data fields mapped, no unknowns in the calculation engine

Phase 2 — Foundation (~1 day)

• ☐ Write migration 006 (cotton_collections, cotton_po_items, cotton_precut_items, cotton_stacking_orders)
• ☐ Run migration on local dev DB
• ☐ Backend: collections CRUD + basic items endpoint (routers/cotton_pos.py)
• ☐ Frontend: nav update (add PURCHASING group with Cotton POs route) + empty collections list page
• ☐ Verify route renders in browser

Phase 3 — Data Pull & Calculation (~2 days)

• ☐ Build netsuite_query.py: query sales history from MySQL for a given collection's SKU prefix
• ☐ Build cotton_calc.py: implement D&R suggestion formula (from Phase 1 research)
• ☐ Backend: on collection creation, auto-populate items from MySQL + run calculation
• ☐ Frontend: PO detail page showing suggested quantities (read-only first)

Phase 4 — Buyer Review UI (~1-2 days)

• ☐ Frontend: per-SKU override input — clicking D&R qty cell makes it editable; shows "(suggested: X)" below
• ☐ Frontend: totals recalculate live on override change
• ☐ Backend: PATCH endpoint to save per-item overrides
• ☐ Frontend: 4 status flag checkboxes in collection header (Created / Done / Sent / Imported)
• ☐ Backend: PATCH endpoint for status updates

Phase 5 — Precuts, Stacking Orders & Export (~1-2 days)

• ☐ Frontend: precuts summary section (precut SKU + total units)
• ☐ Frontend: stacking order table (position + SKU + which bundles it appears in)
• ☐ Backend: xlsx export endpoint matching template format
• ☐ Frontend: Export button → download xlsx
• ☐ End-to-end validation: create Dapple Dot collection, compare D&R quantities to existing Google Sheet

Risks

Acceptance Criteria

• ☐ Cotton POs in nav under PURCHASING
• ☐ Collections list with status badges
• ☐ Per-collection PO view with D&R qty for each SKU (confirmed against Dapple Dot reference)
• ☐ Buyer override saves and persists
• ☐ Status flags work
• ☐ xlsx export produced and structurally matches the template

Execution Log

2026-05-12 — Plan Created

• Read Cotton PO Google Sheet, identified structure and workflow
• Confirmed MySQL already has netsuite_transaction_items (no NetSuite API needed)
• Identified 5 open questions to resolve in Phase 1 before coding
• Created this plan

2026-05-12 — Phase 1 Complete (context window 1)

• Fully reverse-engineered D&R formula chain from Google Sheet browser inspection
• Discovered netsuite_items table already has item master (rbd_moq, unit_type, rbd_collection)
• Apps Script confirmed to use NetSuite RESTlet OAuth 1.0a at account 4582045, script 2148, deploy 3
• Resolved data gap: MySQL has all fields needed, no API credentials required for v1

2026-05-12 — Phases 2–4 Complete (context window 2)

• Migration 006 deployed: cotton_collections, cotton_po_items, cotton_precut_items, cotton_stacking_orders
• services/cotton_calc.py: FORECAST.LINEAR engine + item-type overrides (CP15/CP10/SD/ASY-FQ/1YD)
• routers/cotton_pos.py: full CRUD, item overrides, repopulate, xlsx export
• frontend/src/components/Layout.tsx: added PURCHASING nav group
• frontend/src/pages/purchasing/CottonPOListPage.tsx + CottonPODetailPage.tsx
• frontend/src/App.tsx: routes added
• All 8 /goal acceptance criteria verified in browser with Dapple Dot (110 SKUs)

Status: COMPLETE — ready for Cole to validate D&R numbers against Google Sheet

Next for v2: Add NetSuite RESTlet credentials to .env for live search pull; add precut/kit/laminate entry UI; stacking order table