`execute_mfr_width_backfill.py`

78 lines
#!/usr/bin/env python3
"""Koroseal manufacturer_sku(custom+dwc) + global.width additive backfill.
PostgreSQL-first (local dw_unified mirror) THEN Shopify metafieldsSet (authoritative).
Additive, only-where-empty. NO price/status change. Default DRY-RUN.
Usage: execute_mfr_width_backfill.py [--apply]
"""
import json,os,sys,subprocess,time,urllib.request,collections
APPLY="--apply" in sys.argv
ROOT="/Users/macstudio3/Projects/koroseal-goodrich-audit"
WL=os.path.join(ROOT,"mfr_width_writelist.jsonl")
def env(k):
    for l in open(os.path.expanduser("~/Projects/secrets-manager/.env")):
        if l.startswith(k+"="): return l.split("=",1)[1].strip()
TOKEN=env("SHOPIFY_ADMIN_TOKEN"); SHOP=env("SHOPIFY_STORE"); VER="2024-10"
writes=[json.loads(l) for l in open(WL)]
nd=sum(1 for w in writes if "dwc.manufacturer_sku" in w["fields"])
nw=sum(1 for w in writes if "global.width" in w["fields"])
print(f"[{'APPLY' if APPLY else 'DRY-RUN'}] products={len(writes)} dwc.manufacturer_sku={nd} global.width={nw} total_mf={nd+nw}")
print(f"  shop={SHOP} api={VER}  cost=$0 (Shopify Admin API, no metered spend)")

def ns_key(k): ns,key=k.split("."); return ns,key
# ---- PG-first: update local mirror metafields jsonb (only-where-empty) ----
def pg_update(w):
    sets=[]
    for k,v in w["fields"].items():
        ns,key=ns_key(k)
        node=json.dumps({"type":"single_line_text_field","value":v}).replace("'","''")
        # only set if that namespace.key value is currently empty/absent
        sets.append((ns,key,node))
    sid=w["shopify_id"]
    for ns,key,node in sets:
        sql=(f"update shopify_products set metafields = jsonb_set("
             f"coalesce(metafields,'{{}}'::jsonb) || "
             f"jsonb_build_object('{ns}', coalesce(metafields->'{ns}','{{}}'::jsonb)), "
             f"'{{{ns},{key}}}', '{node}'::jsonb, true) "
             f"where shopify_id='{sid}' "
             f"and coalesce(nullif(trim(metafields->'{ns}'->'{key}'->>'value'),''),'')='';")
        if APPLY:
            r=subprocess.run(["psql","host=/tmp dbname=dw_unified","-tA","-c",sql],capture_output=True,text=True)
            if r.returncode!=0: print("  PG ERR",sid,r.stderr.strip()[:120])
    return len(sets)

# ---- Shopify metafieldsSet ----
def shopify_set(batch):
    mf=[]
    for w in batch:
        for k,v in w["fields"].items():
            ns,key=ns_key(k)
            mf.append({"ownerId":w["shopify_id"],"namespace":ns,"key":key,
                       "type":"single_line_text_field","value":v})
    q="""mutation($m:[MetafieldsSetInput!]!){metafieldsSet(metafields:$m){userErrors{field message}}}"""
    body=json.dumps({"query":q,"variables":{"m":mf}}).encode()
    req=urllib.request.Request(f"https://{SHOP}/admin/api/{VER}/graphql.json",data=body,
        headers={"X-Shopify-Access-Token":TOKEN,"Content-Type":"application/json"})
    with urllib.request.urlopen(req) as r: return json.load(r)

pg_n=0
for w in writes: pg_n+=pg_update(w)
print(f"  PG-first: {'applied' if APPLY else 'prepared'} {pg_n} metafield upserts to local mirror")

if APPLY:
    # Shopify in batches of 25 owners (<=~50 metafields/call safe)
    B=25; errs=0; done=0
    for i in range(0,len(writes),B):
        batch=writes[i:i+B]
        res=shopify_set(batch)
        ue=res.get("data",{}).get("metafieldsSet",{}).get("userErrors",[])
        if ue: errs+=len(ue); print("  SHOPIFY userErrors:",ue[:3])
        done+=len(batch); time.sleep(0.6)
    print(f"  Shopify: pushed {done} products, userErrors={errs}")
else:
    # show a sample metafieldsSet payload
    sample=writes[0]
    print("  sample Shopify metafieldsSet input for",sample["shopify_id"],":")
    for k,v in sample["fields"].items():
        ns,key=ns_key(k); print(f"    {ns}.{key} = {v!r} (single_line_text_field)")
    print("  DRY-RUN: no writes performed. Re-run with --apply after approval.")