`exports/mica/build-mic.py`

36 lines
import subprocess, io, urllib.request, datetime
from PIL import Image, ImageDraw, ImageFont
q="""select handle,title,coalesce(dw_sku,''),coalesce(mfr_sku,''),vendor,coalesce(tags,''),coalesce(image_url,''),status,coalesce(product_type,'')
from shopify_products where status='ACTIVE' and (dw_sku ilike 'mic-%' or sku ilike 'mic-%' or mfr_sku ilike 'mic-%' or variant_sku ilike 'mic-%') order by dw_sku,handle"""
out=subprocess.run(['psql','-h','/tmp','dw_unified','-At','-F','\t','-c',q],capture_output=True,text=True).stdout
R=[l.split('\t') for l in out.splitlines() if l.count('\t')>=8]
print(len(R),'active MIC- products')
def get(u):
    if not u: return None
    u+=('&' if '?' in u else '?')+'width=480'
    try: return Image.open(io.BytesIO(urllib.request.urlopen(urllib.request.Request(u,headers={'User-Agent':'Mozilla/5.0'}),timeout=25).read())).convert('RGB')
    except Exception: return None
imgs=[get(r[6]) for r in R]
F=lambda s,b=False: ImageFont.truetype('/System/Library/Fonts/Helvetica.ttc',s,index=1 if b else 0)
COLS=3; W,T,H=400,360,500; PAD=16; HEAD=100
rows=(len(R)+COLS-1)//COLS
im=Image.new('RGB',(COLS*W+PAD,HEAD+rows*H+PAD),'#f6f3ec'); d=ImageDraw.Draw(im)
d.text((PAD,16),'MIC- SKUs — active catalog',font=F(32,True),fill='#2a2a2a')
d.text((PAD,60),f'{len(R)} active products · SKU prefix MIC- (dw_unified) · {datetime.date.today()}',font=F(16),fill='#777')
def trunc(t,f,w):
    while d.textlength(t,font=f)>w and len(t)>3: t=t[:-2]
    return t
for i,r in enumerate(R):
    h,title,dw,mfr,ven,tags,_,st,pt=r; x=PAD+(i%COLS)*W; y=HEAD+(i//COLS)*H
    d.rectangle([x,y,x+W-PAD,y+H-PAD],fill='white',outline='#ddd6c4')
    a=imgs[i]
    if a:
        s=max((W-PAD-2)/a.width,(T-2)/a.height); a=a.resize((int(a.width*s)+1,int(a.height*s)+1))
        l=(a.width-(W-PAD-2))//2; t=(a.height-(T-2))//2
        im.paste(a.crop((l,t,l+W-PAD-2,t+T-2)),(x+1,y+1))
    else: d.text((x+10,y+10),'no image',font=F(14),fill='#c0392b')
    tl=[t for t in tags.replace('{','').replace('}','').replace('"','').split(',') if t.strip()]
    lines=[(title,F(15,True),'#222'),('DW SKU: '+(dw or '—'),F(14),'#222'),('Mfr SKU: '+(mfr or '—'),F(13),'#555'),(ven+(' · '+pt if pt else ''),F(13),'#8a7a45'),(', '.join(tl)[:80],F(11),'#999')]
    for k,(t,f,c) in enumerate(lines): d.text((x+8,y+T+8+k*22),trunc(t,f,W-PAD-16),font=f,fill=c)
im.save('mic-grid.png'); print(im.size)