`scripts/fix-broken-titles.py`

59 lines
#!/usr/bin/env python3
# Fix the 82 SKUs whose color-name is fiber-spec/texture junk: re-derive a proper
# colorway name from the hex via the interior-designer lexicon, rebuild title+tags,
# update DB + PUT live to Shopify.
import json, re, subprocess, urllib.request
DB="postgresql:///dw_unified?host=/tmp&user=stevestudio2"
env=open(f"{subprocess.os.environ['HOME']}/Projects/secrets-manager/.env").read()
def pick(k):
    m=re.search(rf'^{k}=(.*)$',env,re.M); return (m.group(1).strip().strip('"').strip("'")) if m else ''
STORE=pick('SHOPIFY_STORE') or 'designer-laboratory-sandbox.myshopify.com'; TOK=pick('SHOPIFY_ADMIN_TOKEN')

# interior-designer colorway lexicon (name -> representative hex)
LEX={ 'Chalk':'#f2efe9','Alabaster':'#eae6dc','Cotton':'#f0ece2','Ivory':'#efe7d3','Bone':'#e3d9c6','Cream':'#efe6cf',
 'Linen':'#e8e0d0','Oatmeal':'#d8ccб4'.replace('б','b'),'Flax':'#d9c9a8','Greige':'#c9bfae','Mushroom':'#b8ab99',
 'Putty':'#c2b6a3','Stone':'#b3a692','Sand':'#cdb891','Wheat':'#d6bd8a','Taupe':'#a89a86','Pebble':'#b7afa2',
 'Buff':'#d5b98a','Honey':'#c99a4e','Tan':'#b48a5e','Camel':'#a6794f','Khaki':'#8f7f5a','Caramel':'#8a5a34',
 'Pecan':'#6f4a2f','Cognac':'#7b4a2b','Chestnut':'#5e3a22','Tobacco':'#6b4e2e','Walnut':'#4d3521','Mocha':'#4a3728',
 'Espresso':'#3a2a1f','Dove':'#cfccc6','Silver':'#c4c4c4','Ash':'#b0aeaa','Fog':'#c8c9c7','Smoke':'#9a9a97',
 'Pewter':'#8c8b88','Nickel':'#9fa1a0','Slate':'#5f6a70','Zinc':'#7d8285','Graphite':'#4b4e51','Charcoal':'#3b3d3f',
 'Ink':'#26292c','Onyx':'#1c1d1f','Ebony':'#211d1a','Celadon':'#b6c3a8','Sage':'#9caa8b','Eucalyptus':'#8fa08a',
 'Moss':'#6f7a4f','Fern':'#5c6b3d','Olive':'#6b6535','Loden':'#4f5836','Hunter':'#33503f','Forest':'#274233',
 'Powder':'#c3d2de','Sky':'#a9c6db','Wedgwood':'#6f93b0','Cornflower':'#6f88c0','Denim':'#4f6785','Teal':'#3f7f82',
 'Peacock':'#2a6f78','Indigo':'#33406b','Navy':'#2a3350','Midnight':'#20263a','Champagne':'#e6d6b0','Gold':'#c9a227',
 'Ochre':'#b8863b','Mustard':'#c39a2e','Amber':'#c47f2a','Clay':'#a8674a','Terracotta':'#9e5b3f','Rust':'#8a4a2b',
 'Sienna':'#8a5030','Copper':'#9a5b34','Blush':'#e3c4bc','Rose':'#c98f8a','Dusty Rose':'#bfa0a0','Coral':'#cf7a5c',
 'Brick':'#8a4a3c','Oxblood':'#5c2f2c','Burgundy':'#5a2733','Claret':'#6e2b34','Lavender':'#c3bad6','Lilac':'#c6b6d6',
 'Heather':'#9f92a8','Mauve':'#9c8592','Plum':'#5f3a54','Aubergine':'#3f2a3a','White':'#f4f2ee','Warm White':'#f0ebe0' }
def rgb(h):
    h=h.lstrip('#'); return tuple(int(h[i:i+2],16) for i in (0,2,4))
LEXR={k:rgb(v) for k,v in LEX.items()}
def nearest(hexv):
    try: r=rgb(hexv)
    except: return 'Natural'
    return min(LEXR, key=lambda k: sum((a-b)**2 for a,b in zip(r,LEXR[k])))

MATWORD={'Silk':'Silk','Grass':'Grasscloth','Wood':'Wood Veneer','Suede':'Suede','Linen':'Linen','Raffia':'Raffia',
 'Paper Weave':'Paper Weave','Abaca':'Abaca','Hemp':'Hemp','Sisal':'Sisal','Jute':'Jute','Velvet':'Velvet','Wool':'Wool',
 'Arrowroot':'Arrowroot','Cotton Yarn':'Cotton','Water hyacinth':'Water Hyacinth','Metal Leaf':'Metal Leaf'}
JUNK=re.compile(r'(gsm|texture|[0-9]{2,}\s*%|viscose|polyester|polynosic|rayon|width:|cm/|meter|[0-9]{3,})',re.I)

rows=json.loads(subprocess.check_output(["psql",DB,"-tAc",
 "select coalesce(json_agg(json_build_object('sku',dw_sku,'pid',shopify_product_id,'city',city,'mat',material,'hex',hex,'color',clean_color,'hue',hue,'style',style))::text,'[]') from greenland_full_catalog where shopify_product_id is not null and clean_color ~* '(gsm|texture|[0-9]{2,}%|viscose|polyester|polynosic|rayon|width:|cm/|meter)'"]).decode().strip())
print(f"fixing {len(rows)} junk-title SKUs")
def put(pid,body):
    req=urllib.request.Request(f"https://{STORE}/admin/api/2024-10/products/{pid}.json",
      data=json.dumps(body).encode(),headers={'X-Shopify-Access-Token':TOK,'Content-Type':'application/json'},method='PUT')
    urllib.request.urlopen(req).read()
import time
ok=0
for r in rows:
    color=nearest(r['hex'])
    mw=MATWORD.get(r['mat'],'')
    title=f"{r['city']}-{color} {mw+' ' if mw else ''}Wallcovering | Phillipe Romano".replace('  ',' ')
    tags=', '.join([r['mat'],r['city'],'Phillipe Romano','quotes',f"Style: {r['style']}",f"Color: {color}",f"Hue: {r['hue']}",f"Hex: {r['hex']}"])
    subprocess.run(["psql",DB,"-tAc",f"update greenland_full_catalog set clean_color='{color}', title='{title.replace(chr(39),chr(39)+chr(39))}' where dw_sku='{r['sku']}'"],check=True)
    put(r['pid'],{'product':{'id':r['pid'],'title':title,'tags':tags}})
    ok+=1; time.sleep(0.5)
print(f"DONE fixed {ok} titles+tags (DB+Shopify)")