`scripts/extract_dominant_hex_v2.py`

69 lines
#!/usr/bin/env python3
"""
v2: center-crop 80% before palette extraction. SP product photos sit on
white studio backgrounds — the v1 dominant_hex over-represents that white.
Cropping the center 80% (skipping 10% margin all sides) restores the
actual pattern color as the dominant.

Idempotent: overwrites dominant_hex + colors on every row.
"""
import json, subprocess
from pathlib import Path
from PIL import Image
from collections import Counter

IMG_DIR = Path('/tmp/sp_imgs')

def dominant_palette(path, k=5):
    img = Image.open(path).convert('RGB')
    # Crop center 80% (strip 10% from each edge)
    w, h = img.size
    crop = img.crop((int(w*0.10), int(h*0.10), int(w*0.90), int(h*0.90)))
    crop.thumbnail((300, 300))
    pal = crop.quantize(colors=k, method=Image.Quantize.MEDIANCUT).convert('RGB')
    pixels = list(pal.getdata())
    cnt = Counter(pixels); total = sum(cnt.values())
    return [{'hex': '#{:02x}{:02x}{:02x}'.format(*rgb), 'pct': round(n/total*100, 2)} for rgb, n in cnt.most_common(k)]

def psql_json(sql):
    out = subprocess.run(['psql','dw_unified','-At','-q','-c',sql], capture_output=True, text=True, check=True).stdout.strip()
    return json.loads(out) if out else []

rows = psql_json("SELECT COALESCE(json_agg(t),'[]'::json) FROM (SELECT sp_product_id, title FROM sisterparish_catalog WHERE primary_image IS NOT NULL ORDER BY sp_product_id) t;")
print(f"{len(rows)} products to re-extract")

updates = []
failed = 0
for i, r in enumerate(rows, 1):
    pid = r['sp_product_id']
    # find local image
    matches = list(IMG_DIR.glob(f'sp_{pid}.*'))
    if not matches:
        failed += 1; continue
    try:
        palette = dominant_palette(matches[0])
        dominant = palette[0]['hex']
        updates.append((pid, dominant, palette))
        if i % 10 == 0:
            print(f"  [{i:>3}/{len(rows)}] {dominant} {r['title'][:50]}")
    except Exception as e:
        failed += 1
        print(f"  [{i:>3}/{len(rows)}] FAIL {r['title'][:40]}: {e}")

# Bulk update
sql_file = '/tmp/sp_hex_v2.sql'
with open(sql_file, 'w') as f:
    f.write("BEGIN;\n")
    for pid, dh, pal in updates:
        pj = json.dumps(pal).replace("'", "''")
        f.write(f"UPDATE sisterparish_catalog SET dominant_hex='{dh}', colors='{pj}'::jsonb WHERE sp_product_id={pid};\n")
    f.write("COMMIT;\n")
subprocess.run(['psql','dw_unified','-q','-f', sql_file], check=True)

print(f"\nUpdated {len(updates)} · failed {failed}")
# Show distribution change
print("\nNew hex distribution (top 10):")
print(subprocess.run(['psql','dw_unified','-At','-F| ','-c',
    "SELECT dominant_hex, COUNT(*) FROM sisterparish_catalog GROUP BY dominant_hex ORDER BY 2 DESC LIMIT 10;"],
    capture_output=True, text=True).stdout)