`pending-approval/twil_price_update.py`

156 lines
#!/usr/bin/env python3
"""
twil_price_update.py — apply TWIL vendor email pricing (Jerry Boyas STOCK INFO.xlsx)
to all Fentucci grasscloth products on Shopify + the TWIL PRices sheet.

Pricing chain (verified against sheet's own existing rows):
  vendor "List price PER SR" == column C (cost per single roll / 8yd bolt)
  Cost/yd (L) = cost / 4
  DW Price (O, sell per yd) = (cost/4)*3 = cost * 0.75   <- the DW retail used at burst

DRY (default): read Shopify + sheet, print plan, no writes.
CONFIRM=1 : write variant prices, activate priced drafts, update sheet.
"""
import os, sys, json, time, urllib.request, urllib.parse

SHOP="designer-laboratory-sandbox.myshopify.com"; API="2024-10"
DRY = os.environ.get("CONFIRM","0") != "1"
TOKEN=os.environ.get("SHOPIFY_ADMIN_TOKEN","")
if not TOKEN:
    for line in open(os.path.expanduser("~/Projects/secrets-manager/.env")):
        if line.startswith("SHOPIFY_ADMIN_TOKEN="):
            TOKEN=line.strip().split("=",1)[1]; break
assert TOKEN, "no SHOPIFY_ADMIN_TOKEN"
BASE=f"https://{SHOP}/admin/api/{API}"
H={"X-Shopify-Access-Token":TOKEN,"Content-Type":"application/json"}

def api(method, path, payload=None):
    data=json.dumps(payload).encode() if payload is not None else None
    req=urllib.request.Request(BASE+path, data=data, headers=H, method=method)
    with urllib.request.urlopen(req, timeout=60) as r:
        return r.status, json.loads(r.read().decode())

def graphql(query, variables=None):
    st,res=api("POST","/graphql.json",{"query":query,"variables":variables or {}})
    return res

# ---- Jerry price map ----
def norm(s): return (s or "").strip().upper().rstrip("T")
jerry_raw=json.load(open("/tmp/twil_prices.json"))
PRICE={}   # norm(mfr) -> dw_price
COST={}
for x in jerry_raw:
    c=x["list_per_sr"]
    if c is None: continue
    dw=round(c*0.75,2)
    PRICE[norm(x["code"])]=dw
    COST[norm(x["code"])]=c

# ---- pull all Fentucci products via GraphQL (paginated) ----
Q="""
query($cursor:String){
  products(first:100, query:"vendor:Fentucci", after:$cursor){
    pageInfo{hasNextPage endCursor}
    edges{node{
      id legacyResourceId title status
      mfr: metafield(namespace:"custom", key:"manufacturer_sku"){value}
      mfr2: metafield(namespace:"dwc", key:"manufacturer_sku"){value}
      variants(first:10){edges{node{ id legacyResourceId title sku price }}}
    }}
  }
}"""
prods=[]; cursor=None
while True:
    res=graphql(Q,{"cursor":cursor})
    d=res.get("data",{}).get("products",{})
    if not d:
        print("GRAPHQL ERR:", json.dumps(res)[:500]); sys.exit(1)
    for e in d["edges"]: prods.append(e["node"])
    if d["pageInfo"]["hasNextPage"]: cursor=d["pageInfo"]["endCursor"]
    else: break

print(f"Fentucci products on Shopify: {len(prods)}")
print(f"Jerry priced SKUs: {len(PRICE)}\n")

plan=[]   # (variant_id, new_price, old_price, product_id, status, mfr, title, action)
unmatched=[]
for p in prods:
    mfr = (p.get("mfr") or {}).get("value") or (p.get("mfr2") or {}).get("value") or ""
    n=norm(mfr)
    dw=PRICE.get(n)
    # find Roll variant (option1 Roll => title 'Roll'); sample is the -Sample sku
    roll=None
    for ve in p["variants"]["edges"]:
        v=ve["node"]
        if (v.get("title") or "").lower()=="roll" or (v.get("sku","") and not v["sku"].endswith("-Sample") and "Sample" not in (v.get("title") or "")):
            roll=v; break
    if roll is None and p["variants"]["edges"]:
        roll=p["variants"]["edges"][0]["node"]
    if dw is None:
        unmatched.append((mfr, p["title"], p["status"]))
        continue
    old=roll["price"]
    pid=p["legacyResourceId"]
    vid=roll["legacyResourceId"]
    needs_price = (str(old) != str(dw))
    needs_activate = (p["status"]!="ACTIVE")
    action=[]
    if needs_price: action.append(f"price {old}->{dw}")
    if needs_activate: action.append("ACTIVATE")
    if not action: action.append("ok")
    plan.append({"vid":vid,"pid":pid,"new":dw,"old":old,"status":p["status"],
                 "mfr":mfr,"title":p["title"],"action":", ".join(action),
                 "needs_price":needs_price,"needs_activate":needs_activate})

# report
chg=[x for x in plan if x["needs_price"] or x["needs_activate"]]
print(f"=== {len(plan)} matched | {len(chg)} need change | {len(unmatched)} unmatched ===\n")
for x in sorted(plan,key=lambda z:z["action"]):
    print(f"  {x['mfr']:>10} {x['status']:7} ${str(x['old']):>7} -> ${x['new']:<7} [{x['action']}]  {x['title'][:42]}")
if unmatched:
    print(f"\n--- {len(unmatched)} Fentucci products NOT in Jerry's price list ---")
    for m,t,s in unmatched: print(f"  {m:>10} {s:7} {t[:50]}")

json.dump(plan, open("/tmp/twil_update_plan.json","w"), indent=2)
print(f"\nplan -> /tmp/twil_update_plan.json")

if DRY:
    print("\n[DRY-RUN] no writes. Re-run with CONFIRM=1 to apply.")
    sys.exit(0)

# ---- EXECUTE ----
print("\n=== APPLYING ===")
def all_pubs():
    res=graphql("{ publications(first:50){edges{node{id name}}} }")
    # GMC source-fix (Steve policy, 2026-07-08): exclude "Google & YouTube"
    # (publication 29646651457). Shopify auto-syncs minVariantPrice ($4.25 sample)
    # to Merchant Center -> price disapproval. Google is fed by the controlled TSV
    # feed only. Rollback: delete the filter below.
    return [e["node"]["id"] for e in res.get("data",{}).get("publications",{}).get("edges",[])
            if e["node"]["id"]!="gid://shopify/Publication/29646651457"]
PUBS=all_pubs()
def publish(pid):
    gid=f"gid://shopify/Product/{pid}"
    q="""mutation pub($id:ID!,$input:[PublicationInput!]!){ publishablePublish(id:$id,input:$input){ userErrors{message} } }"""
    res=graphql(q,{"id":gid,"input":[{"publicationId":x} for x in PUBS]})
    return res.get("data",{}).get("publishablePublish",{}).get("userErrors",[])

PRICE_ONLY = os.environ.get("PRICE_ONLY","0")=="1"
npx=nac=0
for x in chg:
    try:
        if x["needs_price"]:
            api("PUT", f"/variants/{x['vid']}.json", {"variant":{"id":x["vid"],"price":str(x["new"])}})
            npx+=1
        if x["needs_activate"] and not PRICE_ONLY:
            api("PUT", f"/products/{x['pid']}.json", {"product":{"id":x["pid"],"status":"active"}})
            errs=publish(x["pid"])
            nac+=1
            if errs: print(f"  pub err {x['mfr']}: {errs}")
        print(f"  done {x['mfr']:>10} -> ${x['new']} {'ACTIVE' if (x['needs_activate'] and not PRICE_ONLY) else '(draft)'}")
        time.sleep(0.4)
    except Exception as e:
        body=getattr(e,'read',lambda:b'')() if hasattr(e,'read') else b''
        print(f"  ERR {x['mfr']}: {e} {body[:200]}")
print(f"\nDONE — prices updated: {npx}, activated: {nac}")