`fetch-publication-status.py`

96 lines
#!/usr/bin/env python3
"""
FIX 3(a) — Full-page the Shopify Admin API for publication status of ALL ACTIVE products.
READ-ONLY (GET only). Pages products.json at 250/page via cursor pagination, polite rate.
Captures id, handle, status, published_at, published_scope for every active product, then
populates the local dw_unified.shopify_products.online_store_published column.

published_at IS NOT NULL  -> published to a sales channel
published_scope='global'  -> published to Online Store + POS (web-visible)
online_store_published = (published_at IS NOT NULL)  [web visibility predicate]

Author: vp-dw-commerce. No writes to Shopify. Local mirror column populate only.
"""
import os, sys, time, json, re, urllib.request, urllib.error
import subprocess

SHOP = "designer-laboratory-sandbox.myshopify.com"
VER = "2024-10"
ENV = os.path.expanduser("~/Projects/secrets-manager/.env")
OUT = os.path.expanduser("~/Projects/dw-catalog-404-audit")

def token():
    for line in open(ENV):
        if line.startswith("SHOPIFY_ADMIN_TOKEN="):
            return line.strip().split("=", 1)[1]
    sys.exit("no token")

TOK = token()

def get(url):
    req = urllib.request.Request(url, headers={"X-Shopify-Access-Token": TOK,
                                               "Accept": "application/json"})
    for attempt in range(6):
        try:
            r = urllib.request.urlopen(req, timeout=40)
            body = r.read()
            link = r.headers.get("Link", "") or r.headers.get("link", "")
            limit = r.headers.get("X-Shopify-Shop-Api-Call-Limit", "")
            return json.loads(body), link, limit
        except urllib.error.HTTPError as e:
            if e.code == 429:
                wait = float(e.headers.get("Retry-After", "2"))
                sys.stderr.write(f"  429 -> sleep {wait}s (attempt {attempt})\n")
                time.sleep(wait + 0.5)
                continue
            raise
    sys.exit("too many 429s")

def next_url(link):
    for part in link.split(","):
        if 'rel="next"' in part:
            m = re.search(r"<([^>]+)>", part)
            if m:
                return m.group(1)
    return None

def main():
    base = (f"https://{SHOP}/admin/api/{VER}/products.json"
            f"?status=active&limit=250&fields=id,handle,status,published_at,published_scope")
    url = base
    rows = []
    page = 0
    t0 = time.time()
    while url:
        data, link, limit = get(url)
        prods = data.get("products", [])
        for p in prods:
            rows.append((p["id"], p.get("handle"), p.get("status"),
                         p.get("published_at"), p.get("published_scope")))
        page += 1
        if page % 20 == 0:
            sys.stderr.write(f"  page {page}: {len(rows)} rows, call-limit {limit}, "
                             f"{time.time()-t0:.0f}s\n")
        url = next_url(link)
        time.sleep(0.5)  # ~2 req/sec, well under the 4/sec leaky-bucket refill
    sys.stderr.write(f"DONE: {len(rows)} active products in {page} pages, "
                     f"{time.time()-t0:.0f}s\n")
    # write raw
    with open(f"{OUT}/publication-status.jsonl", "w") as f:
        for r in rows:
            f.write(json.dumps({"id": r[0], "handle": r[1], "status": r[2],
                                "published_at": r[3], "published_scope": r[4]}) + "\n")
    pub = sum(1 for r in rows if r[3] is not None)
    unpub = sum(1 for r in rows if r[3] is None)
    glob = sum(1 for r in rows if r[4] == "global")
    web = sum(1 for r in rows if r[4] == "web")
    summary = {"active_fetched": len(rows), "published_at_not_null": pub,
               "published_at_null_UNPUBLISHED": unpub,
               "scope_global": glob, "scope_web": web,
               "scope_other": len(rows) - glob - web}
    sys.stderr.write(json.dumps(summary, indent=2) + "\n")
    json.dump(summary, open(f"{OUT}/publication-summary.json", "w"), indent=2)

if __name__ == "__main__":
    main()