Cotton PO Import — Web App Port

Source of truth: ~/ai-projects-local/cotton-po-scripts/rb-cotton-po/Populate Import.js Related plan: ~/ai-projects/mission-control/plans/vscode/session-5.md (GOAL section ~line 129) Related memory: [[cotton-po-import-spec]], [[rbd-reorder-tool]], [[cotton-po-naming]]

What this is

Sabrina builds Cotton POs in a Google Sheet using a custom Apps Script. The buildCottonPOImportFromActiveSheet() function generates the NetSuite import rows from each PO tab. We spent 2026-06-11/12 rewriting that function to Sabrina's locked spec across ~15 iterations.

Now we want the same import button inside the reorder portal (rb.alpineanalytica.com/ordering/, code at ~/ai-projects-local/rbd-reorder-tool/) so Sabrina can drive the workflow without ever leaving the web app — eventually pushing the POs directly into NetSuite via RESTlet (skip CSV).

Why this is worth doing

1. One workflow surface. Sabrina is already building/editing collections in the web app. The import step shouldn't kick her back to a Google Sheet.
2. Eliminate the CSV paste step. Direct-to-NetSuite push removes a copy-paste error class.
3. Auditability. Web app can log ns_po_internal_id + last_exported_at for each push — currently invisible.
4. Idempotency. App can guard against double-push; manual CSV paste can't.

Locked spec (mirror this exactly)

Three POs per collection:

SKU classification (in order, first match wins):

FFYD-*           → NONE (excluded everywhere)
*-ROT            → ROT
CP15*            → CP15
CP10*            → CP10
KT-*             → KIT
^(FQ-|10-|5-|RP-|1YD|1P-|ASY-|JB-|HQC|FFYD-|3YD-|SW-)  → PRECUT
default          → COTTON

Hardcoded surcharges: $0.15/yd for 15-yd bolts, $0.20/yd for 10-yd bolts. Vendor-agnostic.

Cotton qty + rate (Main PO):

• Qty from PO tab "Ship to / 15 yd D/R / Riley Blake Designs" + "Ship to / 10 yd D/R / Riley Blake Designs" — 3-row stacked header match required (otherwise we'd grab FQS/MSQC ship-to columns).
• Rate is either-or (never blended): qty10>0 && qty15==0 → base + $0.20; else base + $0.15.

Precut qty (Main PO): PO tab's PRECUTS section "Riley Blake Designs" column (already nets out advance samples + dropships). Fall back to Data tab AE → AA if missing.

KIT qty + rate (Main PO): PO tab's KIT section. Qty = "Total Kits" col, Rate = "Rate" col. KT- only — don't capture the cotton-component rows under the KT- line.

CP10/CP15 default rate (Sabrina edits after every import):

• CP15: Σ (cotton.base + $0.15) for all collection cottons × 15
• CP10: Σ (cotton.base + $0.20) for all collection cottons × 10
• Exclude RG/FG/ST/WB prefixes from this sum (they default to COTTON but aren't quilting cottons).
• Highlight CP rows yellow (#FFF2CC) in the output so Sabrina can spot them and manually adjust.

Locations:

• ASY-FQ → Sandy Warehouse
• Other ASY-* → Alpine Office
• Everything else → Sandy Warehouse
• The -ASY PO is hardcoded Alpine Office for every row regardless of SKU.

Output schema (10 cols): PO Name | Vendor | Ship Date | Item | QTY | Rate | Amount | Internal ID | External ID | Location

• External ID = 1{PO Name} (literal "1" prefix)
• Vendor = Data tab BC (per-SKU)
• Internal ID = Data tab H

Open spec questions (resolve before coding)

1. Panel SKUs (P* prefix). Currently default to COTTON → get $0.15 surcharge added. For example P18597-Panel came back at $3.00 (base $2.85 + $0.15). Is that right, or should panels be a separate type without surcharge?

2. NetSuite RESTlet write capability. Existing RESTlet at script id 2140 is for saved-search reads only. Does it support PO creation, or do we need a new RESTlet deployed? (Phase 1 research before any backend code.)

3. Vendor per SKU. Sheet uses Data tab BC (per-SKU vendor). In NS 2716 saved search, is Preferred_Vendor returned? If not, three options on session-5.md.

4. CP-to-cotton mapping UI. Sheet default is "every collection cotton is in every CP" → Sabrina edits CP rows after import. For the web app, do we want a UI to deselect cottons per CP, or keep the "highlight + manual edit" pattern?

5. Should the web app's import preview match the sheet exactly, or do we add new affordances (per-row vendor override, dry-run preview, etc.)?

Phased implementation (from session-5.md, still valid)

Phase 1 — Research & spec lock

• Confirm NS RESTlet 2140 PO-write support (or new RESTlet needed)
• Confirm NS 2716 returns Preferred_Vendor / ROT / CP10 / CP15 / Advance_Sample_Yardage / Precut_Yardage
• Resolve panel-rate question above
• Decide: preview-only first, or full push-to-NetSuite from day one?

Phase 2 — Data layer

• Extend populate_collection_items in cotton_calc.py to read additional NS 2716 fields per SKU (rot_yds, asy_yds, precut_yds, cp10_yds, cp15_yds, vendor)
• Schema: add to cotton_po_items table — migration 014
• Port classifySku_() and surcharge constants to cotton_import.py (new file in services/)
• Port buildPoSkuMap_, scanPoSection_, buildPoNonCottonMaps_ equivalents

Phase 3 — Endpoint + UI

• POST /cotton-pos/{id}/build-import → returns 10-col rows in JSON (preview)
• POST /cotton-pos/{id}/push-to-netsuite → if RESTlet write confirmed in Phase 1
• Frontend: "Generate Import" button on CottonPODetailPage → preview table → "Push to NetSuite" or "Copy to clipboard" → confirmation modal with row counts

Phase 4 — Polish

• Per-item Vendor override UI
• CP-to-cotton mapping editor
• Dry-run side-by-side preview vs Apps Script output

Acceptance criteria (Phase 3 ship)

• Click "Generate Import" on BEEBLOSSM-060926 in the portal → preview shows 60+ rows total across Main + ASYFQ + ASY POs, matching the Apps Script output row-for-row (same SKUs, qty, rate, amount, location).
• CP10/CP15 rows visually flagged (yellow background or icon) so user knows to adjust manually before push.
• "Push to NetSuite" (if Phase 1 unblocks it): 3 POs created in NS, internal IDs returned, cotton_collections.last_exported_at + ns_po_internal_id populated.
• Idempotency: clicking Push twice doesn't duplicate POs (disabled after first push, or NS external ID guard).

Files we'll touch

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

• services/cotton_calc.py — extend populate_collection_items
• services/cotton_import.py — NEW (mirror Apps Script logic)
• services/netsuite_client.py — add PO-write helper (Phase 3)
• routers/cotton_pos.py — new endpoints
• models/cotton_po_item.py — new fields
• alembic/versions/014_xxx.py — migration

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

• src/pages/purchasing/CottonPODetailPage.tsx — add import button + preview modal
• src/api/cottonPos.ts — new endpoint wrappers

When picking this back up

1. Read [[cotton-po-import-spec]] memory for the current spec
2. Re-read the actual Apps Script at ~/ai-projects-local/cotton-po-scripts/rb-cotton-po/Populate Import.js (always source of truth — spec may have drifted)
3. Read this file
4. Re-read session-5.md GOAL sections for context
5. Resolve the 5 Open Spec Questions above before any code
6. Confirm Phase 1 research outputs with Cole
7. Then start Phase 2