`scripts/c34-osborne-gaston/enqueue-specs.py`

98 lines
#!/usr/bin/env python3
"""
c34 Osborne & Little + Gaston Y Daniela specs metafield push.
Live-blank-verify at enqueue time (officer-required guard), build restore-map,
emit NOT-EXISTS-guarded shopify_api_queue INSERT SQL. Read-only by itself —
prints SQL + restore-map; the SQL is run on Kamatera as the gated step.

Usage:
  enqueue-specs.py --worklist <tsv> --limit N   # canary: first N products
  enqueue-specs.py --worklist <tsv>             # all
Writes: restore-map-<ts>.csv, enqueue-<ts>.sql  (does NOT touch any DB)
"""
import sys, os, csv, json, urllib.request, argparse, datetime

STORE = "designer-laboratory-sandbox.myshopify.com"  # = real prod www.designerwallcoverings.com
API = "2024-10"

def token():
    for line in open(os.path.expanduser("~/Projects/secrets-manager/.env")):
        if line.startswith("SHOPIFY_ADMIN_TOKEN="):
            return line.strip().split("=", 1)[1]
    sys.exit("no SHOPIFY_ADMIN_TOKEN")

def live_metafields(pid, tok):
    url = f"https://{STORE}/admin/api/{API}/products/{pid}/metafields.json"
    req = urllib.request.Request(url, headers={"X-Shopify-Access-Token": tok})
    ms = json.load(urllib.request.urlopen(req, timeout=30)).get("metafields", [])
    cur = {}
    for m in ms:
        if m.get("namespace") == "custom" and m.get("key") in ("width", "material"):
            cur[m["key"]] = m.get("value")
    return cur

def sql_lit(s):
    return "'" + s.replace("'", "''") + "'"

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--worklist", required=True)
    ap.add_argument("--limit", type=int, default=0)
    a = ap.parse_args()
    tok = token()
    ts = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")
    rows = list(csv.DictReader(open(a.worklist), delimiter="\t"))
    # one product per row in this worklist; group unique product_ids preserving order
    seen, products = set(), []
    for r in rows:
        if r["product_id"] not in seen:
            seen.add(r["product_id"]); products.append(r)
    if a.limit:
        products = products[:a.limit]

    restore, inserts, skipped = [], [], []
    for r in products:
        pid = r["product_id"]
        cur = live_metafields(pid, tok)
        restore.append({"product_id": pid, "handle": r["handle"], "vendor": r["vendor"],
                        "live_width": cur.get("width", ""), "live_material": cur.get("material", "")})
        for key, proposed in (("width", r["proposed_width"]), ("material", r["proposed_material"])):
            if not proposed or proposed.startswith(("EXCLUDE", "SKIP")) or proposed in ("(blank)", "N/A"):
                continue
            live_val = cur.get(key)
            if live_val:  # live-blank guard: never overwrite an existing live value
                skipped.append(f"{pid} {key}: live already = {live_val!r} (proposed {proposed!r}) -> SKIP")
                continue
            payload = json.dumps({"metafield": {"namespace": "custom", "key": key,
                                  "type": "single_line_text_field", "value": proposed}})
            endpoint = f"/products/{pid}/metafields.json"
            inserts.append((endpoint, payload, key, pid, proposed))

    rm = f"restore-map-{ts}.csv"
    with open(rm, "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=["product_id", "handle", "vendor", "live_width", "live_material"])
        w.writeheader(); w.writerows(restore)

    sqlf = f"enqueue-{ts}.sql"
    with open(sqlf, "w") as f:
        f.write("-- c34 osborne/gaston specs metafield enqueue (live-blank-verified). Run on Kamatera.\nBEGIN;\n")
        for endpoint, payload, key, pid, proposed in inserts:
            f.write(
                "INSERT INTO shopify_api_queue (method, endpoint, payload, token_alias, priority, status, source_agent)\n"
                f"SELECT 'POST', {sql_lit(endpoint)}, {sql_lit(payload)}::jsonb, 'product', 7, 'pending', 'c34-osborne-gaston-specs'\n"
                "WHERE NOT EXISTS (SELECT 1 FROM shopify_api_queue q WHERE q.request_hash = "
                f"md5(('POST'||{sql_lit(endpoint)})||COALESCE(({sql_lit(payload)}::jsonb)::text,'')) "
                "AND q.status IN ('pending','processing'));\n")
        f.write("COMMIT;\n")

    print(f"products checked: {len(products)}")
    print(f"genuinely-blank writes to enqueue: {len(inserts)}  (width={sum(1 for i in inserts if i[2]=='width')}, material={sum(1 for i in inserts if i[2]=='material')})")
    print(f"skipped (live already filled): {len(skipped)}")
    for s in skipped[:10]:
        print("  ", s)
    print(f"restore-map: {rm}")
    print(f"enqueue SQL: {sqlf}")

if __name__ == "__main__":
    main()