`backfill-multiwidth.py`

93 lines
#!/usr/bin/env python3
"""
TK-11029 phase 2: recover the 16 multi-width Maya rows still polluted with the
'=device-width...' placeholder. Verified $0 fetch of the vendor collection pages
shows each of these patterns is genuinely offered in TWO roll widths and EVERY
colorway is available in BOTH — width is a pattern-level attribute, not per-SKU.
So we store the vendor's REAL verified full multi-width string (accurate, not a
guess). The 2 x 404 collections (cozy-nestle, cozy-bed-fellow) are truly gone
from Maya's site -> left polluted-flagged, needs rep/PDF (Steve's rule: no guess).

Reversible: writes a restore-map (old value per dw_sku) BEFORE any UPDATE.
"""
import json, subprocess, re, datetime, sys, os

PSQL = ["psql", "-h", "/tmp", "-d", "dw_unified", "-At", "-F", "\t"]
HERE = os.path.dirname(os.path.abspath(__file__))

# Vendor's REAL verified width text, captured from the live collection pages
# (curl $0, HTTP 200, specs-table "Width" row) 2026-08-31.
COLLECTION_WIDTH = {
    "entwine-inlet-linen":  '27" Width Approx. 27 in untrimmed (68.6 cm); 54" Width Approx. 54 in untrimmed (137.2 cm)',
    "entwine-serene-silk":  '27" Width Approx. 27 in untrimmed (68.6 cm); 54" Width Approx. 54 in untrimmed (137.2 cm)',
    "wisping-weaves-montauk": '27" untrimmed (68.6 cm), 6.75 sq ft/yd (0.63 sq m/yd); 54" untrimmed (137.2 cm), 13.5 sq ft/yd (1.25 sq m/yd)',
    "ajiro-sunburst":       '15" Width Approx. 30 in untrimmed (76.2 cm); 18" Width Approx. 36 in untrimmed (91.4 cm)',
}
# These stay polluted (truly discontinued, 404, no web width):
STILL_UNKNOWN_SLUGS = {"cozy-nestle", "cozy-bed-fellow"}

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]

def esc(s): return s.replace("'", "''")

APPLY = "--apply" in sys.argv

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, unknown = [], [], []
for dw_sku, mfr_sku, old_w, old_wi, slug in rows:
    if slug in COLLECTION_WIDTH:
        new_w = COLLECTION_WIDTH[slug]
        # multi-width => no single width_inches; leave NULL (ambiguous by design)
        restore.append({"dw_sku": dw_sku, "mfr_sku": mfr_sku, "slug": slug,
                        "old_width": old_w, "old_width_inches": old_wi})
        updates.append({"dw_sku": dw_sku, "slug": slug, "new_width": new_w,
                        "new_width_inches": None})
    else:
        unknown.append({"dw_sku": dw_sku, "mfr_sku": mfr_sku, "slug": slug,
                        "reason": "collection 404 / discontinued on mayaromanoff.com; needs Maya rep/PDF"})

ts = datetime.datetime.now().isoformat()
rm = {"ts": ts, "ticket": "TK-11029", "phase": "2-multiwidth",
      "table": "maya_catalog", "column": "width,width_inches",
      "polluted_string": '=device-width, initial-scale=1">',
      "total_polluted_before": len(rows), "to_fix": len(updates),
      "still_unknown": len(unknown),
      "restore_map": restore, "updates_planned": updates,
      "still_unknown_rows": unknown,
      "collection_widths": COLLECTION_WIDTH,
      "note": "multi-width patterns: each colorway offered in BOTH widths; storing the "
              "vendor's real verified multi-width string (accurate, not a guess). "
              "width_inches left NULL (no single width). cozy-nestle/cozy-bed-fellow "
              "404 on vendor site -> left polluted, needs rep."}
path = os.path.join(HERE, f"restore-map-multiwidth-{ts.replace(':','-')}.json")
json.dump(rm, open(path, "w"), indent=2)
print("RESTORE-MAP:", path)
print(f"total_polluted={len(rows)} to_fix={len(updates)} still_unknown={len(unknown)}")

if not APPLY:
    print("\nDRY-RUN (no --apply). Planned updates:")
    for u in updates:
        print(f"  {u['dw_sku']:12s} [{u['slug']}] -> {u['new_width'][:60]}...")
    print("\nStill-unknown (left polluted):")
    for u in unknown:
        print(f"  {u['dw_sku']:12s} {u['mfr_sku']:12s} [{u['slug']}]")
    sys.exit(0)

n = 0
for u in updates:
    sql = (f"UPDATE maya_catalog SET width='{esc(u['new_width'])}', "
           f"width_inches=NULL, updated_at=now() "
           f"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")