#!/usr/bin/env python3
"""Check which feeds provide images with transparency."""
import subprocess, json, urllib.request, tempfile, os, sys

# Get samples from DB
result = subprocess.run(
    ['docker', 'exec', '-i', 'supabase-db', 'psql', '-U', 'postgres', '-d', 'sneakerpicks', '-t', '-A', '-F', '|'],
    input="""
WITH ranked AS (
  SELECT f.merchant_name, f.id as feed_id, po.image_url,
    ROW_NUMBER() OVER (PARTITION BY f.id ORDER BY random()) as rn
  FROM feeds f
  JOIN product_offers po ON po.feed_id = f.id
  WHERE po.image_url IS NOT NULL AND po.image_url != ''
)
SELECT merchant_name, feed_id, image_url
FROM ranked
WHERE rn <= 5
ORDER BY feed_id, rn;
""",
    capture_output=True, text=True
)

lines = [l.strip() for l in result.stdout.strip().split('\n') if l.strip()]
print(f"Got {len(lines)} sample URLs")

try:
    from PIL import Image
    has_pil = True
except ImportError:
    has_pil = False
    print("WARNING: Pillow not installed, checking file headers only")

results = {}  # feed_id -> {merchant, total, png_count, alpha_count}

for line in lines:
    parts = line.split('|')
    if len(parts) < 3:
        continue
    merchant, feed_id, url = parts[0], parts[1], parts[2]
    
    if feed_id not in results:
        results[feed_id] = {'merchant': merchant, 'total': 0, 'png': 0, 'alpha': 0, 'jpg': 0}
    
    results[feed_id]['total'] += 1
    
    try:
        tmpfile = f"/tmp/transp_check_{feed_id}_{results[feed_id]['total']}"
        req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'})
        with urllib.request.urlopen(req, timeout=10) as resp:
            data = resp.read()
        with open(tmpfile, 'wb') as f:
            f.write(data)
        
        # Check magic bytes
        is_png = data[:4] == b'\x89PNG'
        is_jpg = data[:2] == b'\xff\xd8'
        
        if is_jpg:
            results[feed_id]['jpg'] += 1
        elif is_png:
            results[feed_id]['png'] += 1
            if has_pil:
                img = Image.open(tmpfile)
                if img.mode in ('RGBA', 'LA', 'PA'):
                    results[feed_id]['alpha'] += 1
        
        os.unlink(tmpfile)
    except Exception as e:
        print(f"  Error checking {url[:60]}...: {e}")

print("\n=== RESULTS ===")
print(f"{'Merchant':<25} {'Feed':>4} {'Total':>5} {'JPG':>4} {'PNG':>4} {'Alpha':>5}")
print("-" * 55)
for fid, r in sorted(results.items(), key=lambda x: x[1]['merchant']):
    print(f"{r['merchant']:<25} {fid:>4} {r['total']:>5} {r['jpg']:>4} {r['png']:>4} {r['alpha']:>5}")