← back to Dw Five Field Step0
match-jd-combined-cost.py
222 lines
#!/usr/bin/env python3
"""
COMBINED DRY-RUN: cost-source the ~2,095 live Justin-David private-label products
(mfr_sku 'JD-PATTERN-COLOR', live under Shopify vendor "Los Angeles Fabrics" +
a few "Phillipe Romano" / "Architectural Wallcoverings") from BOTH price lists
that arrived in the info@ inbox:
(A) POINTE xref list -> vendor-pricelists/pointe-jd-prices.json
keyed on alnum(pattern+color) == its `jd` field ("Pattern/Color").
Color-specific cut_yd_price. Already cleanly unlocked 371 (prior pass).
(B) JD OWN-LINE list -> vendor-pricelists/jd-ownline-prices.json
509 patterns, single per-yard pattern-level `price` (no per-color).
Source = JD's own "2026 January Price List" PDF.
READ-ONLY. Writes NO cost to dw_unified / Shopify. The cost write stays GATED
for Steve after he reviews the combined unlock.
DTD-locked (panel 3/3, 2026-06-21, all-A):
Q1 own-line PATTERN match = LONGEST-PREFIX with '-' word-boundary guard
(alnum(sku-JD) must start with an ownline alnum(pattern); pick the longest;
the matched prefix must end on a '-' boundary in the raw sku so JD-ALDEN
matches 'Alden' but JD-ALDENWOOD does NOT).
Q2 SOURCE PRECEDENCE = prefer Pointe/cut_yd_price (color-specific); own-line
only fills the Pointe misses.
Q3 cost = matched price/yd; retail = cost/0.65/0.85; tariff pass-through
(mirrors the already-locked Pointe policy).
Only price ACTIVE/STANDARD/in-line items; hold discontinued/MTO.
NEVER expose Pointe / Justin David / Command54 customer-facing (private label).
"""
import json, re, subprocess
from collections import Counter
HERE = "/Users/macstudio3/Projects/dw-five-field-step0"
POINTE = f"{HERE}/vendor-pricelists/pointe-jd-prices.json"
OWNLINE = f"{HERE}/vendor-pricelists/jd-ownline-prices.json"
OUT = f"{HERE}/out/jd-combined-cost-diff.json"
alnum = lambda s: re.sub(r'[^A-Z0-9]', '', (s or '').upper())
nstat = lambda s: (s or '').strip().upper()
# pattern flags ('+', '*') are catalog annotations, not part of the name
clean_pat = lambda s: re.sub(r'[+*]', ' ', (s or '')).strip()
# Pointe statuses that count as in-line / sellable
LIVE_POINTE = ('STANDARD', 'ACTIVE')
def live_rows():
sql = ("select shopify_id||'|'||coalesce(mfr_sku,'')||'|'||coalesce(dw_sku,'')"
"||'|'||coalesce(vendor,'')||'|'||coalesce(status,'')||'|'"
"||coalesce(cost::text,'')||'|'||replace(coalesce(title,''),'|','')"
" from shopify_products where mfr_sku like 'JD-%';")
out = subprocess.check_output(["psql", "-d", "dw_unified", "-tAc", sql], text=True)
rows = []
for line in out.splitlines():
p = line.split('|')
if len(p) < 7:
continue
rows.append(dict(shopify_id=p[0], mfr_sku=p[1], dw_sku=p[2], vendor=p[3],
status=p[4], cost=p[5], title=p[6]))
return rows
def build_pointe_idx():
"""alnum(pattern+color) -> best priced/STANDARD row (prefer cut_yd_price)."""
d = json.load(open(POINTE))
idx = {}
for r in d:
if not r.get('jd') or '/' not in r['jd']:
continue
p, c = r['jd'].split('/', 1)
idx.setdefault(alnum(p) + alnum(c), []).append(r)
best = {}
for k, rs in idx.items():
best[k] = sorted(rs, key=lambda r: (1 if r.get('cut_yd_price') else 0,
{'STANDARD': 3, 'ACTIVE': 2}.get(nstat(r['status']), 0)),
reverse=True)[0]
return best
def build_ownline():
"""list of (alnum_pattern, raw_pattern_clean, row) sorted longest alnum first."""
d = json.load(open(OWNLINE))
out = []
for r in d:
pc = clean_pat(r.get('pattern'))
ap = alnum(pc)
if ap:
out.append((ap, pc, r))
# longest alnum first so longest-prefix wins
out.sort(key=lambda t: len(t[0]), reverse=True)
return out
def ownline_match(mfr_sku, ownline):
"""Longest-prefix match with '-' word-boundary guard. Returns row or None."""
body = mfr_sku[3:] if mfr_sku.upper().startswith('JD-') else mfr_sku # drop 'JD-'
a = alnum(body)
if not a:
return None
for ap, pc, r in ownline: # already longest-first
if a.startswith(ap):
# boundary guard: the pattern prefix must end at a '-' (or end-of-sku)
# in the RAW body. Walk the raw body counting alnum chars until we have
# consumed len(ap) alnum chars; the next raw char must be '-' or EOS.
consumed, i = 0, 0
while i < len(body) and consumed < len(ap):
if body[i].isalnum():
consumed += 1
i += 1
nxt = body[i] if i < len(body) else ''
if nxt in ('', '-'):
return r
return None
def retail(cost):
return round(cost / 0.65 / 0.85, 2) if cost else None
def main():
pointe = build_pointe_idx()
ownline = build_ownline()
rows = live_rows()
lkey = lambda m: alnum(m[3:] if m.upper().startswith('JD-') else m)
out_rows = []
for row in rows:
mfr = row['mfr_sku']
# --- (A) Pointe xref (color-specific) takes precedence ---
p = pointe.get(lkey(mfr))
if p:
st = nstat(p['status'])
cost = p.get('cut_yd_price')
load = bool(cost and st in LIVE_POINTE)
out_rows.append(dict(
shopify_id=row['shopify_id'], mfr_sku=mfr, dw_sku=row['dw_sku'] or None,
vendor=row['vendor'], shopify_status=row['status'],
current_cost=row['cost'] or None, private_label_title=row['title'],
source='pointe-xref', matched_pattern=p.get('jd'),
matched_pointe=p.get('pointe'), source_status=p.get('status'),
type=p.get('type'), proposed_cost=cost,
tariff_passthrough=p.get('tariff'), proposed_retail=retail(cost),
will_load=load,
block_reason=None if load else ('no_price' if not cost else 'discontinued_or_mto'),
))
continue
# --- (B) JD own-line (pattern-level) fallback ---
o = ownline_match(mfr, ownline)
if o:
cost = o.get('price')
# ownline list = JD's current in-line catalog -> all rows sellable
load = bool(cost)
tariff = o.get('tariff')
tariff = None if (tariff in (None, '', 'n/a')) else tariff
out_rows.append(dict(
shopify_id=row['shopify_id'], mfr_sku=mfr, dw_sku=row['dw_sku'] or None,
vendor=row['vendor'], shopify_status=row['status'],
current_cost=row['cost'] or None, private_label_title=row['title'],
source='jd-ownline', matched_pattern=o.get('pattern'),
matched_pointe=None, source_status='IN-LINE',
type=o.get('collection'), proposed_cost=cost,
tariff_passthrough=tariff, proposed_retail=retail(cost),
will_load=load, block_reason=None if load else 'no_price',
))
continue
# --- unmatched ---
out_rows.append(dict(
shopify_id=row['shopify_id'], mfr_sku=mfr, dw_sku=row['dw_sku'] or None,
vendor=row['vendor'], shopify_status=row['status'],
current_cost=row['cost'] or None, private_label_title=row['title'],
source=None, matched_pattern=None, matched_pointe=None, source_status=None,
type=None, proposed_cost=None, tariff_passthrough=None, proposed_retail=None,
will_load=False, block_reason='unmatched',
))
unlock = [x for x in out_rows if x['will_load']]
blocked = [x for x in out_rows if not x['will_load'] and x['source']] # matched but held
unmatched = [x for x in out_rows if x['source'] is None]
by_src = Counter(x['source'] for x in unlock)
costs = [x['proposed_cost'] for x in unlock]
active_nocost = [x for x in unlock
if x['shopify_status'] == 'ACTIVE' and not x['current_cost']]
# 10-row sample: mfr_sku -> source -> pattern -> cost -> retail
sample = [dict(mfr_sku=x['mfr_sku'], source=x['source'],
pattern=x['matched_pattern'], cost=x['proposed_cost'],
retail=x['proposed_retail'])
for x in unlock[:10]]
summary = dict(
generated='2026-06-21',
mode='READ-ONLY combined dry-run (cost write GATED for Steve)',
sources=dict(
pointe='vendor-pricelists/pointe-jd-prices.json (color-specific cut_yd_price; precedence)',
ownline='vendor-pricelists/jd-ownline-prices.json (509 JD own-line patterns; fallback)'),
dtd='panel 3/3 all-A 2026-06-21: Q1 longest-prefix+boundary, Q2 Pointe-first, Q3 cost=price retail=/0.65/0.85 tariff-passthrough',
live_jd_mfr_rows=len(rows),
combined_unlock=len(unlock),
unlock_by_source=dict(by_src),
unlock_currently_active_and_nocost=len(active_nocost),
held_discontinued_or_mto=len(blocked),
held_breakdown=dict(Counter(x['block_reason'] for x in blocked)),
still_unmatched=len(unmatched),
cost_range=[min(costs), max(costs)] if costs else None,
retail_range=[min(x['proposed_retail'] for x in unlock),
max(x['proposed_retail'] for x in unlock)] if unlock else None,
unlock_vendor_split=dict(Counter(x['vendor'] for x in unlock)),
)
json.dump(dict(summary=summary, sample_10=sample, rows=out_rows),
open(OUT, 'w'), indent=1)
print(json.dumps(summary, indent=1))
print('\n--- 10-row sample (mfr_sku -> source -> pattern -> cost -> retail) ---')
for s in sample:
print(f" {s['mfr_sku']:34} {s['source']:11} {str(s['pattern'])[:24]:24} "
f"${s['cost']:>6} -> ${s['retail']}")
if __name__ == '__main__':
main()