#!/usr/bin/env python3
import psycopg2, psycopg2.extras
conn = psycopg2.connect("postgresql://supabase_admin:HzWrn2rMMxUYWO6OT5X0PScIVEvSjQMup9YSLIrkPQ@172.18.0.5:5432/sneakerpicks")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

# Products with multiple offers from same feed
cur.execute("""
SELECT p.name, f.name as shop, COUNT(*) as cnt
FROM product_offers po
JOIN products p ON p.id = po.product_id
JOIN feeds f ON f.id = po.feed_id
GROUP BY p.name, f.name HAVING COUNT(*) > 1
ORDER BY cnt DESC LIMIT 10
""")
print("Products with multiple offers from same shop:")
for r in cur.fetchall():
    print(f"  {r['shop']}: {r['name']} ({r['cnt']}x)")

# Single-size offers
cur.execute("SELECT COUNT(*) FROM product_offers WHERE jsonb_array_length(sizes) = 1")
single = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM product_offers")
total = cur.fetchone()[0]
print(f"\nOffers with 1 size: {single}/{total}")

# Check Patta grouping — look at raw item_group_id usage
cur.execute("""
SELECT po.external_id, po.price, jsonb_array_length(po.sizes) as sz, f.name
FROM product_offers po
JOIN feeds f ON f.id = po.feed_id
WHERE f.name = 'Patta'
ORDER BY po.external_id
LIMIT 10
""")
print("\nPatta offers:")
for r in cur.fetchall():
    print(f"  ext_id={r['external_id']}, price={r['price']}, sizes={r['sz']}")

# Check a product that appears multiple times from same shop
cur.execute("""
SELECT p.id, p.name, f.name as shop, po.external_id, po.price, 
       jsonb_array_length(po.sizes) as sz
FROM product_offers po
JOIN products p ON p.id = po.product_id
JOIN feeds f ON f.id = po.feed_id
WHERE p.name IN (
    SELECT p2.name FROM product_offers po2
    JOIN products p2 ON p2.id = po2.product_id
    JOIN feeds f2 ON f2.id = po2.feed_id
    GROUP BY p2.name, f2.name HAVING COUNT(*) > 1
    LIMIT 1
)
ORDER BY p.name, f.name
LIMIT 10
""")
print("\nDuplicate example:")
for r in cur.fetchall():
    print(f"  product={r['id']}, name={r['name'][:60]}, shop={r['shop']}, ext={r['external_id']}, price={r['price']}, sizes={r['sz']}")

# Check how names differ for same item_group_id products in Patta
cur.execute("""
SELECT f.name as shop, COUNT(*) as offers, COUNT(DISTINCT po.product_id) as products
FROM feeds f JOIN product_offers po ON po.feed_id = f.id
GROUP BY f.name ORDER BY offers DESC
""")
print("\nOffers vs products per shop:")
for r in cur.fetchall():
    ratio = r['offers'] / r['products'] if r['products'] > 0 else 0
    print(f"  {r['shop']}: {r['offers']} offers, {r['products']} products (ratio {ratio:.1f})")