`build_dwkk_variant_map.py`

105 lines
#!/usr/bin/env python3
"""
Build authoritative dw_sku → (product_id, variant_id, current_price) map from Shopify.
Queries all products with SKU starting DWKK- and extracts main variant.
Persists to PG: kravet_dwkk_variant_map (dw_sku, product_gid, variant_gid, price, status, updated_at)
"""
import json, subprocess, time, urllib.request

TOKEN=os.environ.get('SHOPIFY_ADMIN_TOKEN','')
URL='https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json'
SSH=['ssh','root@45.61.58.125']


def gql(q, v=None):
    body=json.dumps({"query":q,"variables":v or {}}).encode()
    req=urllib.request.Request(URL,data=body,method="POST",
        headers={"Content-Type":"application/json","X-Shopify-Access-Token":TOKEN})
    d=json.loads(urllib.request.urlopen(req,timeout=30).read())
    if "errors" in d: raise RuntimeError(d["errors"])
    return d["data"]


def pg_exec(sql):
    subprocess.run(SSH + [
        f'PGPASSWORD=DW2024! psql -h 127.0.0.1 -U dw_admin -d dw_unified -c "{sql}"'],
        capture_output=True, text=True, check=True)


Q="""query($cursor:String){
  products(first:100, after:$cursor, query:"sku:DWKK-*") {
    pageInfo{ hasNextPage endCursor }
    nodes{
      id status
      variants(first:10){
        nodes{ id sku price inventoryItem{ id unitCost{ amount } } }
      }
    }
  }
}"""

print("fetching DWKK products from Shopify ...")
rows = []
cursor = None
pages = 0
while True:
    d = gql(Q, {"cursor": cursor})
    prods = d["products"]["nodes"]
    for p in prods:
        for v in p["variants"]["nodes"]:
            sku = v["sku"] or ""
            # Keep only exact DWKK-NNNN (not -Sample, not -Per Yard, etc.) main variants.
            # Accept both plain DWKK-NNNNN and DWKK-NNNNN (no suffix)
            if sku.startswith("DWKK-") and "-Sample" not in sku and " " not in sku and sku.count("-") == 1:
                rows.append({
                    "dw_sku":     sku,
                    "product_gid": p["id"],
                    "variant_gid": v["id"],
                    "price":       v["price"],
                    "cost":        (v["inventoryItem"].get("unitCost") or {}).get("amount"),
                    "status":      p["status"],
                })
    pages += 1
    if pages % 5 == 0:
        print(f"  page {pages}  products={sum(1 for _ in rows)}  cursor={cursor[:40] if cursor else '-'}")
    if not d["products"]["pageInfo"]["hasNextPage"]: break
    cursor = d["products"]["pageInfo"]["endCursor"]

print(f"collected {len(rows)} DWKK main variants across {pages} pages")

# Build unique map (first-wins on dupe dw_sku)
seen = {}
for r in rows:
    seen.setdefault(r["dw_sku"], r)
print(f"unique dw_skus: {len(seen)}")

# Status breakdown
from collections import Counter
print(f"statuses: {dict(Counter(r['status'] for r in seen.values()))}")

# Persist
pg_exec("""
CREATE TABLE IF NOT EXISTS kravet_dwkk_variant_map (
  dw_sku       text PRIMARY KEY,
  product_gid  text NOT NULL,
  variant_gid  text NOT NULL,
  price        numeric,
  cost         numeric,
  status       text,
  updated_at   timestamptz DEFAULT now()
);
TRUNCATE kravet_dwkk_variant_map;
""")

# Bulk insert via COPY
import tempfile, csv
with tempfile.NamedTemporaryFile("w", suffix=".csv", delete=False, newline="") as tf:
    w = csv.writer(tf)
    for r in seen.values():
        w.writerow([r["dw_sku"], r["product_gid"], r["variant_gid"],
                    r["price"] or "", r["cost"] or "", r["status"]])
    tmp = tf.name
subprocess.run(["scp", tmp, "root@45.61.58.125:/tmp/dwkk_map.csv"], check=True)
subprocess.run(SSH + ["""PGPASSWORD=DW2024! psql -h 127.0.0.1 -U dw_admin -d dw_unified -c \"\\copy kravet_dwkk_variant_map (dw_sku,product_gid,variant_gid,price,cost,status) FROM '/tmp/dwkk_map.csv' WITH (FORMAT csv)\""""], check=True)
print(f"wrote {len(seen)} rows to kravet_dwkk_variant_map")