`enrich_palette.py`

139 lines
#!/usr/bin/env python3
"""Carnegie palette enrichment — free local PIL ($0). Per sellable SKU:
download swatch image local, extract full palette (hex + coverage %), nearest
colorway name, primary hex + bucket, color_tags; then set activation_pass per
the 5-field + full-palette gate. Caches by image_url to avoid redundant fetches.
"""
import io, json, os, subprocess, sys, time, urllib.request
from PIL import Image

DSN = "host=/tmp dbname=dw_unified"
IMGDIR = os.path.expanduser("~/Projects/carnegie-import/images")
os.makedirs(IMGDIR, exist_ok=True)
UA = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36"

# compact interior-designer colorway lexicon (name, r,g,b)
LEX = [
 ("White",245,245,245),("Alabaster",237,234,224),("Cream",243,235,215),("Ivory",250,246,228),
 ("Oatmeal",223,213,193),("Sand",214,196,166),("Beige",210,196,171),("Taupe",180,165,145),
 ("Greige",176,168,156),("Khaki",160,145,110),("Camel",176,140,98),("Gold",196,160,70),
 ("Yellow",220,200,60),("Mustard",190,150,40),("Orange",210,120,50),("Rust",150,80,45),
 ("Terracotta",180,95,70),("Red",180,50,50),("Burgundy",110,35,45),("Pink",210,150,160),
 ("Blush",220,190,185),("Purple",110,75,140),("Plum",95,60,90),("Navy",35,45,80),
 ("Blue",60,95,150),("Denim",75,105,140),("Sky",150,180,205),("Teal",40,120,120),
 ("Aqua",120,180,180),("Green",70,120,70),("Sage",150,165,140),("Olive",110,110,60),
 ("Forest",45,80,55),("Grey",150,150,150),("Silver",190,190,192),("Slate",100,110,120),
 ("Charcoal",70,72,78),("Graphite",55,55,58),("Black",30,30,30),("Brown",110,80,55),("Chocolate",75,55,40),
]
def nearest(r,g,b):
    best=None;bd=1e9
    for n,rr,gg,bb in LEX:
        d=(r-rr)**2+(g-gg)**2+(b-bb)**2
        if d<bd:bd=d;best=n
    return best
def bucket(r,g,b):
    mx,mn=max(r,g,b),min(r,g,b)
    if mx<60:return "Dark"
    if mn>200:return "Light"
    if mx-mn<25:return "Neutral"
    if r>=g and r>=b:return "Warm"
    if b>=r and b>=g:return "Cool"
    return "Warm" if g>=b else "Cool"

def palette_of(img):
    im=img.convert("RGB");im.thumbnail((120,120))
    q=im.quantize(colors=8,method=Image.FASTOCTREE)
    pal=q.getpalette();counts=q.getcolors() or []
    total=sum(c for c,_ in counts) or 1
    out=[]
    for cnt,idx in sorted(counts,reverse=True):
        r,g,b=pal[idx*3:idx*3+3]
        pct=round(100.0*cnt/total,1)
        if pct<2:continue
        out.append({"hex":"#%02x%02x%02x"%(r,g,b),"name":nearest(r,g,b),
                    "bucket":bucket(r,g,b),"pct":pct,"rgb":[r,g,b]})
    # merge same-name
    merged={}
    for c in out:
        k=c["name"]
        if k in merged:merged[k]["pct"]=round(merged[k]["pct"]+c["pct"],1)
        else:merged[k]=c
    return sorted(merged.values(),key=lambda x:-x["pct"])

def fetch(url):
    req=urllib.request.Request(url,headers={"User-Agent":UA})
    with urllib.request.urlopen(req,timeout=40) as r:return r.read()

def psql(sql):
    p=subprocess.run(["psql",DSN,"-v","ON_ERROR_STOP=1","-q"],input=sql,text=True,capture_output=True)
    if p.returncode!=0:
        sys.stderr.write(p.stderr[:1500]);raise SystemExit("psql fail")

def rows():
    out=subprocess.run(["psql",DSN,"-tAF","\t","-c",
        "SELECT id,dw_sku,image_url FROM carnegie_catalog WHERE palette IS NULL AND image_url IS NOT NULL ORDER BY id"],
        text=True,capture_output=True).stdout.strip()
    return [l.split("\t") for l in out.splitlines() if l]

def q(v):
    if v is None:return "NULL"
    if isinstance(v,(dict,list)):return "'"+json.dumps(v).replace("'","''")+"'"
    return "'"+str(v).replace("'","''")+"'"
def arr(l):
    if not l:return "NULL"
    return "'{"+",".join('"'+str(x).replace('"','\\"')+'"' for x in l)+"}'"

def main():
    todo=rows();n=len(todo);print(f"[enrich] {n} SKUs need palette",flush=True)
    cache={};done=0;fail=0;buf=[]
    for rid,dwsku,url in todo:
        try:
            if url in cache:
                pal,lp=cache[url]
            else:
                b=fetch(url);img=Image.open(io.BytesIO(b))
                pal=palette_of(img)
                ext=".png" if (url.lower().split("?")[0].endswith("png")) else ".jpg"
                lp=os.path.join(IMGDIR,f"{dwsku}{ext}")
                with open(lp,"wb") as f:f.write(b)
                cache[url]=(pal,lp)
                time.sleep(0.15)
            if not pal:raise ValueError("empty palette")
            ctags=[c["name"] for c in pal[:3]]
            phex=pal[0]["hex"];pbucket=pal[0]["bucket"]
            buf.append((rid,pal,ctags,phex,pbucket,os.path.basename(lp)))
            done+=1
        except Exception:
            buf.append((rid,None,None,None,None,None));fail+=1
        if len(buf)>=100:
            flush(buf);buf=[]
            print(f"[enrich] {done+fail}/{n} ok={done} fail={fail}",flush=True)
    if buf:flush(buf)
    # activation gate
    psql("""UPDATE carnegie_catalog SET
      activation_pass = (local_image IS NOT NULL AND price>0 AND description_text IS NOT NULL
        AND array_length(style_tags,1)>=2 AND palette IS NOT NULL AND array_length(color_tags,1)>=1),
      activation_reasons = array_remove(ARRAY[
        CASE WHEN local_image IS NULL THEN 'no_local_image' END,
        CASE WHEN price IS NULL OR price<=0 THEN 'no_price' END,
        CASE WHEN description_text IS NULL THEN 'no_desc' END,
        CASE WHEN array_length(style_tags,1)<2 THEN 'lt2_style_tags' END,
        CASE WHEN palette IS NULL THEN 'no_palette' END,
        CASE WHEN array_length(color_tags,1)<1 THEN 'no_color_tags' END], NULL);""")
    print(json.dumps({"enriched":done,"failed":fail,"total":n}),flush=True)

def flush(buf):
    parts=[]
    for rid,pal,ctags,phex,pbucket,lp in buf:
        if pal is None:
            parts.append(f"({rid},NULL,NULL,NULL,NULL,NULL)")
        else:
            parts.append(f"({rid},{q(pal)}::jsonb,{arr(ctags)},{q(phex)},{q(pbucket)},{q(lp)})")
    sql=("UPDATE carnegie_catalog c SET palette=v.palette, color_tags=v.color_tags::text[], "
         "primary_hex=v.primary_hex, color_bucket=v.color_bucket, local_image=v.local_image, updated_at=now() "
         "FROM (VALUES\n"+",\n".join(parts)+
         "\n) AS v(id,palette,color_tags,primary_hex,color_bucket,local_image) WHERE c.id=v.id;")
    psql(sql)

if __name__=="__main__":main()