`backfill.py`

51 lines
import json, subprocess, re, datetime, sys

PSQL=["psql","-h","/tmp","-d","dw_unified","-At","-F","\t"]
def q(sql):
    r=subprocess.run(PSQL+["-c",sql],capture_output=True,text=True)
    if r.returncode!=0: print("SQL ERR:",r.stderr); sys.exit(1)
    return [line.split("\t") for line in r.stdout.strip().split("\n") if line]

cw=json.load(open("/tmp/maya-width-fix/collection-widths.json"))
# slug -> clean width (only the 14 that resolved)
slug2w={s:v["width"] for s,v in cw.items() if v.get("width")}
UNRESOLVED={'entwine-inlet-linen','entwine-serene-silk','wisping-weaves-montauk','ajiro-sunburst','cozy-nestle','cozy-bed-fellow'}

# 1. restore-map: every polluted row's current state (dw_sku, mfr_sku, old width, old width_inches, slug)
rows=q("""SELECT dw_sku, mfr_sku, width, coalesce(width_inches::text,''), split_part(product_url,'/collections/',2)
          FROM maya_catalog WHERE width LIKE '%device-width%' ORDER BY dw_sku""")
restore=[]
updates=[]   # rows to fix
unknown=[]   # still-unknown
for dw_sku, mfr_sku, old_w, old_wi, slug in rows:
    restore.append({"dw_sku":dw_sku,"mfr_sku":mfr_sku,"old_width":old_w,"old_width_inches":old_wi,"slug":slug})
    if slug in slug2w:
        new_w=slug2w[slug]
        mi=re.search(r'([0-9]+(?:\.[0-9]+)?)', new_w)  # leading inch number
        new_wi=mi.group(1) if mi else None
        updates.append({"dw_sku":dw_sku,"new_width":new_w,"new_width_inches":new_wi})
    else:
        unknown.append({"dw_sku":dw_sku,"mfr_sku":mfr_sku,"slug":slug})

ts=datetime.datetime.now().isoformat()
rm={"ts":ts,"table":"maya_catalog","column":"width,width_inches",
    "polluted_string":"=device-width, initial-scale=1\">",
    "total_polluted":len(restore),"to_fix":len(updates),"still_unknown":len(unknown),
    "restore_map":restore,"updates_applied":updates,"still_unknown_rows":unknown,
    "collection_widths":slug2w}
path=f"/Users/macstudio3/Projects/maya-width-fix/restore-map-{ts.replace(':','-')}.json"
json.dump(rm, open(path,"w"), indent=2)
print("RESTORE-MAP:",path)
print(f"total_polluted={len(restore)} to_fix={len(updates)} still_unknown={len(unknown)}")

# 2. Apply updates (bounded, per-row UPDATE by dw_sku)
def esc(s): return s.replace("'","''")
n=0
for u in updates:
    wi = f"width_inches = {u['new_width_inches']}" if u['new_width_inches'] else "width_inches = NULL"
    sql=f"UPDATE maya_catalog SET width='{esc(u['new_width'])}', {wi}, updated_at=now() WHERE dw_sku='{esc(u['dw_sku'])}' AND width LIKE '%device-width%';"
    r=subprocess.run(PSQL+["-c",sql],capture_output=True,text=True)
    if r.returncode!=0: print("UPDATE ERR",u['dw_sku'],r.stderr); sys.exit(1)
    n+=1
print(f"APPLIED {n} row updates")