#!/usr/bin/env python3 """ SneakerPicks — Import specific Webgains feeds one by one. Uses feedIds[] param + search_cursor pagination. Filters for sneakers only, excludes clothing/accessories. """ import requests import psycopg2 import psycopg2.extras import re import hashlib import time import sys import json # ── Config ── WEBGAINS_API = "https://platform-api.webgains.com" PUBLISHER_ID = "1347930" WEBGAINS_TOKEN = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhdWQiOiIxMDAwMzEiLCJqdGkiOiIwNjM5ODEwYzQ0YzM1MTBlZmZiMGRkYWNiM2EyNWFlNTg2YzFmNmQ5ODNjNGQ2MDA4NzkwZmM0NTkzY2EyNjg4ZGViZDE4NTgwOThmYjJjYiIsImlhdCI6MTc3MTUxNjc4OS44MzAxMzUsIm5iZiI6MTc3MTUxNjc4OS44MzAxMzcsImV4cCI6MjA4NzA0OTU4OS44MjcwODgsInN1YiI6IjIwMTcwOCIsInNjb3BlcyI6W119.ZxlCDsyBI7_SNUWjO-PUxDYKvQVfuUBzU5n-SgqHdAZCnOj9253yVcJIS5NRXJLrnyx0E_9bn8ymfXS71cvPISA4hPAlKCSMjdkv9f2k2FFwdAlQQ6mdhBB2m9dzHUIn34QwA8s4-ikB1ATKIZeqiJL-3Gh9eiMYPpvaJdCCjN-oGPMhhlehkgA04cZSgwy6HfKgb6uPvUSG9w_tvN8U06uEHKV11DedQHNWWGzuXpd2FEL3baV0z9KjSNMZsJZHx2yHUb6gVWJwaR7_louahMvrPXG-YLgnGvNMcPDPjm-z5HJ-JKcPv9_FBqAYGItZxgeGgVoS8MVhW6lc1a-6CvYtVanfXF42MPPNP-Qq76qRtHPLRBdkIATMuBxVJrMzlprT3n4F0sUUltGR4ixZuzMUm5LeyPMHgmjy2UfYzsl9eVqx0oJCmKUyY9maBXwFLJa8J1NHM_nWp3hPpCGkMyhzHK1DIf_z8Wirg1hp5vKts93eERWJBlQwhucUUS_KnZeC_gVaQ7kT4jCwaEWkpPxPxyXWRoK0mcYS_JGz3JbpVhRLj5DVYIB9k54DXqjYlCx8ezJZE9sf2SX3VWvPvD1xSQD1caTCm0To0B-MkeU1YpEfWdUslpCJXrpOy2Wbs8iDHR1BzNrMnOEr4zbz_EoB4JJe3tGTGjzNLNIw6XI" CAMPAIGN_ID = "1622230" DB_DSN = "host=172.18.0.5 port=5432 dbname=sneakerpicks user=sneakerpicks password=SP.r4nD0m-sEcuRe99" HEADERS = { "Authorization": f"Bearer {WEBGAINS_TOKEN}", "Accept": "application/json", } # ── Sneaker filter ── EXCLUDE_TITLE_WORDS = [ 't-shirt', 'tshirt', 'tee ', 'hoodie', 'hoody', 'sweater', 'sweatshirt', 'jacket', 'coat', 'pants', 'trouser', 'jogger', 'shorts', 'jeans', 'shirt', 'polo shirt', 'vest', 'cap ', 'hat ', 'beanie', 'scarf', 'glove', 'sock ', 'socks', 'backpack', 'bag ', 'wallet', 'belt ', 'watch ', 'sunglasses', 'underwear', 'boxer', 'brief', 'bikini', 'swim', 'towel', 'perfume', 'fragrance', 'deodorant', 'candle', 'keychain', 'keyring', 'phone case', 'airpod', 'insole', 'laces', 'cleaner', 'protector', 'spray', 'brush', 'tracksuit', 'track pant', 'windbreaker', 'puffer', 'fleece', 'cardigan', 'crewneck', 'crew neck', 'longsleeve', 'long sleeve', 'tank top', 'legging', 'skirt', 'dress ', 'jumpsuit', 'blanket', 'pillow', 'duvet', 'cushion', 'rug ', 'mat ', 'poster', 'print ', 'mug ', 'cup ', 'bottle', 'incense', 'book ', 'magazine', 'sticker', 'pin ', 'patch', 'lanyard', 'tote', ] FOOTWEAR_CATEGORIES = ['footwear', 'shoe', 'sneaker', 'schoen', 'trainer'] NON_FOOTWEAR_CATEGORIES = ['clothing', 'apparel', 'accessori', 'bag', 'hat', 'cap', 'lifestyle', 'home', 'fragrance', 'art', 'book'] def is_sneaker(product): """Determine if product is footwear.""" title = (product.get('title') or '').lower() cat_raw = product.get('product_type') or product.get('category') or '' if isinstance(cat_raw, list): category = ' '.join(str(x) for x in cat_raw).lower() else: category = str(cat_raw).lower() # Category-based (most reliable) if category: if any(w in category for w in FOOTWEAR_CATEGORIES): return True if any(w in category for w in NON_FOOTWEAR_CATEGORIES): return False # Title-based exclusion for word in EXCLUDE_TITLE_WORDS: if word in title: return False # If no category info and no exclusion matched, check title for shoe indicators shoe_words = ['sneaker', 'shoe', 'trainer', 'runner', 'boot', 'slide', 'sandal', 'slipper', 'mule', 'clog', 'foam', 'dunk', 'force 1', 'air max', 'jordan', 'yeezy', 'ultraboost', 'superstar', 'stan smith', 'old skool', 'gel-', '990', '550', '2002r', '327', '574', 'speedcat', 'spezial'] for word in shoe_words: if word in title: return True # Unknown — exclude to be safe return False def slugify(text): text = text.lower().strip() text = re.sub(r'[^\w\s-]', '', text) text = re.sub(r'[\s_]+', '-', text) text = re.sub(r'-+', '-', text) return text[:200] def parse_price(val): if not val: return None val = str(val).replace(',', '.').strip() match = re.search(r'[\d.]+', val) if match: try: return float(match.group()) except: return None return None def fetch_all_products(feed_id): """Fetch ALL products from a feed using cursor pagination.""" url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{CAMPAIGN_ID}/feeds/products" all_products = [] seen_ids = set() cursor = None for page in range(20): # Max 20 pages = 20k products params = {"feedIds[]": [feed_id], "size": 1000} if cursor: params["search_cursor"] = cursor try: r = requests.get(url, headers=HEADERS, params=params, timeout=60) r.raise_for_status() data = r.json() batch = data.get("data", []) pag = data.get("pagination", {}) total = pag.get("total", "?") cursor = pag.get("search_cursor") if not batch: break # Deduplicate new_count = 0 for p in batch: pid = str(p.get("id", "")) if pid not in seen_ids: seen_ids.add(pid) all_products.append(p) new_count += 1 print(f" Page {page+1}: {len(batch)} fetched, {new_count} new, total unique: {len(all_products)}/{total}") if len(batch) < 1000 or new_count == 0: break time.sleep(0.3) except Exception as e: print(f" ERROR page {page+1}: {e}") break return all_products def import_feed(feed_id, merchant_name, feed_name): """Import a single feed into the database.""" print(f"\n{'='*60}") print(f"IMPORTING: {merchant_name} — Feed {feed_id} ({feed_name})") print(f"{'='*60}") # 1. Fetch all products print(f"\n1. Fetching products...") products = fetch_all_products(feed_id) print(f" Total unique products: {len(products)}") if not products: print(" ❌ No products!") return # 2. Filter sneakers print(f"\n2. Filtering sneakers...") sneakers = [p for p in products if is_sneaker(p)] excluded = [p for p in products if not is_sneaker(p)] print(f" ✅ Sneakers: {len(sneakers)}") print(f" ✗ Excluded: {len(excluded)}") # Show category breakdown cats = {} for p in products: c = p.get('product_type') or ['Unknown'] if isinstance(c, list): c = c[0] if c else 'Unknown' c = str(c).strip()[:40] cats[c] = cats.get(c, 0) + 1 print(f" Categories: {sorted(cats.items(), key=lambda x: -x[1])[:8]}") if excluded: print(f"\n Sample excluded (first 15):") for p in excluded[:15]: print(f" ✗ {p.get('brand','?'):20s} | {p.get('title','?')[:55]}") if not sneakers: print(" ❌ No sneakers after filtering!") return # Show sample sneakers print(f"\n Sample sneakers (first 10):") for p in sneakers[:10]: price = parse_price(p.get('sale_price') or p.get('price')) print(f" ✓ {p.get('brand','?'):20s} | €{price or '?':>7} | {p.get('title','?')[:50]}") # 3. Import to DB print(f"\n3. Importing {len(sneakers)} sneakers...") conn = psycopg2.connect(DB_DSN) conn.autocommit = False cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Provider cur.execute("SELECT id FROM providers WHERE slug = 'webgains'") provider_id = cur.fetchone()["id"] # Feed record cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s", (provider_id, str(feed_id))) row = cur.fetchone() if row: db_feed_id = row["id"] else: cur.execute(""" INSERT INTO feeds (provider_id, external_id, name, merchant_name, product_count) VALUES (%s, %s, %s, %s, 0) RETURNING id """, (provider_id, str(feed_id), feed_name, merchant_name)) db_feed_id = cur.fetchone()["id"] conn.commit() # Brand cache brand_cache = {} cur.execute("SELECT id, slug FROM brands") for r in cur.fetchall(): brand_cache[r["slug"]] = r["id"] imported = 0 skipped = 0 for p in sneakers: title = (p.get("title") or "").strip().rstrip('|').strip() brand_name = (p.get("brand") or "").strip() image = (p.get("image_link") or "").strip() link = (p.get("link") or "").strip() price = parse_price(p.get("sale_price") or p.get("price")) original_price = parse_price(p.get("price")) if p.get("sale_price") else None ext_id = str(p.get("id", "")).strip() gtin_val = p.get("gtin") or "" if isinstance(gtin_val, list): gtin_val = gtin_val[0] if gtin_val else "" ean = str(gtin_val).strip() or None size_val = (p.get("size") or "").strip() if not title or not link or not price: skipped += 1 continue # Clean title: remove trailing pipe, size info already in separate field title = re.sub(r'\s*\|\s*$', '', title) # Brand brand_slug = slugify(brand_name) if brand_name else "unknown" if brand_slug not in brand_cache: cur.execute(""" INSERT INTO brands (name, slug) VALUES (%s, %s) ON CONFLICT (slug) DO UPDATE SET name = EXCLUDED.name RETURNING id """, (brand_name or "Unknown", brand_slug)) brand_cache[brand_slug] = cur.fetchone()["id"] brand_id = brand_cache[brand_slug] # Product slug slug_base = slugify(f"{brand_name}-{title}")[:180] slug_hash = hashlib.md5(f"{feed_id}-{ext_id}".encode()).hexdigest()[:8] product_slug = f"{slug_base}-{slug_hash}" # Find by EAN or create product_id = None if ean: cur.execute("SELECT id FROM products WHERE ean = %s", (ean,)) row = cur.fetchone() if row: product_id = row["id"] if not product_id: try: cur.execute(""" INSERT INTO products (name, slug, brand_id, brand, image_url, description, ean, brand_normalized, first_seen_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (slug) DO UPDATE SET updated_at = NOW() RETURNING id """, (title, product_slug, brand_id, brand_name, image, (p.get("description") or "")[:500], ean, brand_name.lower() if brand_name else None)) product_id = cur.fetchone()["id"] except Exception as e: conn.rollback() skipped += 1 continue # Insert offer try: cur.execute(""" INSERT INTO product_offers (product_id, feed_id, external_id, price, original_price, currency, product_url, image_url, in_stock, last_seen_at) VALUES (%s, %s, %s, %s, %s, 'EUR', %s, %s, true, NOW()) ON CONFLICT (feed_id, external_id) DO UPDATE SET price = EXCLUDED.price, original_price = EXCLUDED.original_price, product_url = EXCLUDED.product_url, in_stock = true, last_seen_at = NOW() """, (product_id, db_feed_id, ext_id, price, original_price, link, image)) imported += 1 except Exception as e: conn.rollback() skipped += 1 continue conn.commit() # Update feed stats cur.execute("UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success' WHERE id = %s", (imported, db_feed_id)) conn.commit() # Brand stats for this import cur.execute(""" SELECT b.name, COUNT(*) as cnt FROM products p JOIN brands b ON p.brand_id = b.id JOIN product_offers po ON po.product_id = p.id WHERE po.feed_id = %s GROUP BY b.name ORDER BY cnt DESC LIMIT 10 """, (db_feed_id,)) brand_stats = cur.fetchall() print(f"\n{'='*60}") print(f"RESULTS: {merchant_name}") print(f"{'='*60}") print(f" Total in feed: {len(products)}") print(f" After filter: {len(sneakers)}") print(f" Imported: {imported}") print(f" Skipped: {skipped}") print(f" Top brands:") for row in brand_stats: print(f" {row['name']:20s} {row['cnt']}") cur.execute("SELECT COUNT(*) FROM products") print(f"\n DB totals: {cur.fetchone()[0]} products", end="") cur.execute("SELECT COUNT(*) FROM product_offers") print(f", {cur.fetchone()[0]} offers", end="") cur.execute("SELECT COUNT(*) FROM feeds") print(f", {cur.fetchone()[0]} feeds") cur.close() conn.close() if __name__ == "__main__": if len(sys.argv) < 4: print("Usage: python import-feeds.py ") sys.exit(1) import_feed(int(sys.argv[1]), sys.argv[2], sys.argv[3])