#!/bin/bash
# Check if feed images have transparency (alpha channel)
# Need: pip install Pillow (or use python3 -c with PIL)

TMPDIR="/tmp/transparency-check"
mkdir -p "$TMPDIR"

# We'll check by feed - get samples from DB
docker exec -i supabase-db psql -U postgres -d sneakerpicks -t -A -F'|' <<'SQL'
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;
SQL

echo "---"
echo "Downloading and checking images..."

# Process the query output
docker exec -i supabase-db psql -U postgres -d sneakerpicks -t -A -F'|' <<'SQL' | while IFS='|' read -r merchant feed_id url; do
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;
SQL
  FNAME="$TMPDIR/${feed_id}_$(echo $url | md5sum | cut -c1-8)"
  curl -sL -o "$FNAME" "$url" 2>/dev/null
  
  # Check file type and alpha
  FTYPE=$(file -b "$FNAME" 2>/dev/null)
  HAS_ALPHA="no"
  
  if echo "$FTYPE" | grep -qi "PNG"; then
    # Check for alpha channel using python
    HAS_ALPHA=$(python3 -c "
from PIL import Image
try:
    im = Image.open('$FNAME')
    print('yes' if im.mode in ('RGBA', 'LA', 'PA') else 'no')
except: print('error')
" 2>/dev/null)
  fi
  
  echo "$merchant|$feed_id|$HAS_ALPHA|$FTYPE"
done

echo "=== DONE ==="