`match-pr-pointe-cost.py`

114 lines
#!/usr/bin/env python3
"""
DRY-RUN: Match DW Phillipe-Romano / Los-Angeles-Fabrics private-label SKUs to the
Pointe -> Justin David vendor price list and produce a cost-load diff.

READ-ONLY. Writes NO cost to dw_unified / Shopify. The actual cost write is gated
for Steve after he reviews the unlock count.

MATCH KEY (the link between PR private-label SKUs and the upstream Pointe pattern):
  live `shopify_products.mfr_sku` of the form 'JD-PATTERN-COLOR'
    -> strip 'JD-', uppercase, drop all non-alphanumerics, concat(pattern+color)
  == price-list `jd` field 'Pattern/Color' normalized the same way.
The price list cross-references `jd` (Justin David name) <-> `pointe` (manufacturer
Pointe name); we key on the JD side because that is what the live mfr_sku carries.

  NOTE: command54_catalog.original_name (AJA/LAURUS...) is a DIFFERENT upstream and
  is NOT priced by this Pointe/JD list (0 overlap) -- do not use it here.

DTD-locked policy (panel 3/3, 2026-06-21):
  Q1 cost = cut_yd_price ONLY (tariff tracked separately as pass-through).
  Q2 load cost ONLY to priced STANDARD/ACTIVE rows; hold DISCONTINUED/CUSTOM/MTO.
  Q3 retail = cost / 0.65 / 0.85.
  Q4 the dw_unified cost-write stays gated for Steve.

NEVER expose Pointe / Justin David / Command54 customer-facing (private label).
"""
import json, re, subprocess, sys
from collections import Counter

HERE = "/Users/macstudio3/Projects/dw-five-field-step0"
PRICE = f"{HERE}/vendor-pricelists/pointe-jd-prices.json"
OUT = f"{HERE}/out/pr-pointe-cost-diff.json"

alnum = lambda s: re.sub(r'[^A-Z0-9]', '', (s or '').upper())
nstat = lambda s: (s or '').strip().upper()


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 main():
    d = json.load(open(PRICE))
    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)

    rows = live_rows()
    lkey = lambda m: alnum(m[3:] if m.upper().startswith('JD-') else m)

    matched, unmatched = [], []
    for row in rows:
        pr = idx.get(lkey(row['mfr_sku']))
        if pr:
            best = sorted(pr, key=lambda r: (1 if r.get('cut_yd_price') else 0,
                          {'STANDARD': 3, 'ACTIVE': 2}.get(nstat(r['status']), 0)),
                          reverse=True)[0]
            matched.append((row, best))
        else:
            unmatched.append(row['mfr_sku'])

    out_rows = []
    for r, b in matched:
        st, cost, tariff = nstat(b['status']), b.get('cut_yd_price'), b.get('tariff')
        load = bool(cost and st in ('STANDARD', 'ACTIVE'))
        out_rows.append(dict(
            shopify_id=r['shopify_id'], mfr_sku=r['mfr_sku'], dw_sku=r['dw_sku'] or None,
            vendor=r['vendor'], shopify_status=r['status'], current_cost=r['cost'] or None,
            private_label_title=r['title'], matched_jd=b.get('jd'), matched_pointe=b.get('pointe'),
            pointe_status=b.get('status'), type=b.get('type'),
            proposed_cost=cost, tariff_passthrough=tariff,
            proposed_retail=round(cost / 0.65 / 0.85, 2) if cost else None,
            will_load=load,
            block_reason=None if load else ('no_price' if not cost else 'discontinued_or_mto'),
        ))

    unlock = [x for x in out_rows if x['will_load']]
    blocked = [x for x in out_rows if not x['will_load']]
    costs = [x['proposed_cost'] for x in unlock]
    retails = [x['proposed_retail'] for x in unlock]
    summary = dict(
        generated='2026-06-21', mode='READ-ONLY dry-run (cost write GATED for Steve)',
        live_jd_mfr_rows=len(rows), matched=len(matched), unmatched=len(unmatched),
        unlock_priced_standard_active=len(unlock),
        unlock_currently_active_and_nocost_in_shopify=len(
            [x for x in unlock if x['shopify_status'] == 'ACTIVE' and not x['current_cost']]),
        blocked_total=len(blocked),
        blocked_breakdown=dict(Counter(x['block_reason'] for x in blocked)),
        cost_range=[min(costs), max(costs)] if costs else None,
        retail_range=[min(retails), max(retails)] if retails else None,
        unlock_vendor_split=dict(Counter(x['vendor'] for x in unlock)),
    )
    json.dump(dict(summary=summary, rows=out_rows), open(OUT, 'w'), indent=1)
    print(json.dumps(summary, indent=1))


if __name__ == '__main__':
    main()