#!/usr/bin/env python3 """ SneakerPicks Feed Sync — Webgains Fetches all products from Webgains feeds and inserts into PostgreSQL. """ import requests import psycopg2 import psycopg2.extras import re import hashlib import time import sys # ── 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" DB_URL = "postgresql://supabase_admin:HzWrn2rMMxUYWO6OT5X0PScIVEvSjQMup9YSLIrkPQ@172.18.0.5:5432/sneakerpicks" # Use all 4 campaigns to discover feeds CAMPAIGNS = { "manify.nl": "1622230", "ladify.nl": "1622250", "thehike.nl": "1622240", "mellowed.nl": "1622260", } HEADERS = { "Authorization": f"Bearer {WEBGAINS_TOKEN}", "Content-Type": "application/json", "Accept": "application/json", } 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 get_feeds(): """Get all unique feeds across all campaigns.""" all_feeds = {} for site, camp_id in CAMPAIGNS.items(): url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{camp_id}/feeds" try: r = requests.get(url, headers=HEADERS, params={"size": 100}, timeout=30) r.raise_for_status() data = r.json() for f in data.get("data", []): fid = f["id"] if fid not in all_feeds: all_feeds[fid] = { "id": fid, "name": f.get("name", ""), "program_name": f.get("program", {}).get("name", ""), "program_id": str(f.get("program", {}).get("id", "")), "product_count": f.get("products_successfully_stored", 0), "campaign_id": camp_id, } print(f" {site} ({camp_id}): {len(data.get('data', []))} feeds") except Exception as e: print(f" {site} ERROR: {e}") return all_feeds def fetch_products_for_feed(campaign_id, feed_id, max_products=5000): """Fetch products from a single feed. Try pagination, fall back to large batch.""" url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{campaign_id}/feeds/products" all_products = [] # Try fetching in one big batch first (offset may be ignored) params = { "feeds[]": [feed_id], "size": 1000, "fields[]": ["title", "image_link", "link", "price", "brand", "description", "id", "sale_price", "gtin", "mpn", "colour", "size", "availability", "condition"], } try: r = requests.get(url, headers=HEADERS, params=params, timeout=60) r.raise_for_status() data = r.json() products = data.get("data", []) all_products.extend(products) total = data.get("recordsTotal", len(products)) print(f" Got {len(products)}/{total} products (batch 1)") # Try pagination if there are more if total > len(all_products) and len(products) > 0: for offset in range(len(products), min(total, max_products), 1000): params["offset"] = offset try: r = requests.get(url, headers=HEADERS, params=params, timeout=60) r.raise_for_status() data = r.json() batch = data.get("data", []) if not batch: break # Check if we're getting duplicates (pagination broken) if batch[0].get("id") == all_products[0].get("id"): print(f" Pagination returning duplicates, stopping at {len(all_products)}") break all_products.extend(batch) print(f" Got {len(batch)} more (total: {len(all_products)})") time.sleep(0.5) except Exception as e: print(f" Pagination error at offset {offset}: {e}") break except Exception as e: print(f" Fetch error: {e}") return all_products def main(): print("=== SneakerPicks Feed Sync ===") print() # Connect to DB print("Connecting to database...") conn = psycopg2.connect(DB_URL) conn.autocommit = False cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Get webgains provider_id cur.execute("SELECT id FROM providers WHERE slug = 'webgains'") provider_row = cur.fetchone() if not provider_row: print("ERROR: webgains provider not found!") return provider_id = provider_row["id"] print(f"Provider ID: {provider_id}") # Get feeds from API print("\nFetching feeds from Webgains API...") feeds = get_feeds() print(f"\nFound {len(feeds)} unique feeds") # Brand cache brand_cache = {} cur.execute("SELECT id, slug FROM brands") for row in cur.fetchall(): brand_cache[row["slug"]] = row["id"] total_products = 0 total_offers = 0 for feed_ext_id, feed_info in sorted(feeds.items()): fname = feed_info["name"] pcount = feed_info["product_count"] camp_id = feed_info["campaign_id"] print(f"\n--- Feed: {fname} (ext_id={feed_ext_id}, ~{pcount} products) ---") # Ensure feed exists in DB cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s", (provider_id, str(feed_ext_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, %s) RETURNING id """, (provider_id, str(feed_ext_id), fname, feed_info["program_name"], pcount or 0)) db_feed_id = cur.fetchone()["id"] conn.commit() # Fetch products products = fetch_products_for_feed(camp_id, feed_ext_id) if not products: print(" No products, skipping") continue feed_new = 0 feed_offers = 0 for p in products: title = (p.get("title") or "").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 if not title or not link or not price: continue # 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 - find or create product_slug = slugify(f"{brand_name}-{title}")[:200] # Make slug unique by appending ext_id hash slug_hash = hashlib.md5(f"{feed_ext_id}-{ext_id}".encode()).hexdigest()[:6] product_slug = f"{product_slug}-{slug_hash}" # Try EAN match first 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: # Insert new product 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"] feed_new += 1 except Exception as e: conn.rollback() # Try with more unique slug product_slug = f"{product_slug}-{hashlib.md5(title.encode()).hexdigest()[:8]}" 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"] feed_new += 1 except Exception as e2: print(f" Skip product '{title[:40]}': {e2}") conn.rollback() 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, image_url = EXCLUDED.image_url, in_stock = true, last_seen_at = NOW(), updated_at = NOW() """, (product_id, db_feed_id, ext_id, price, original_price, link, image)) feed_offers += 1 except Exception as e: print(f" Skip offer '{title[:40]}': {e}") conn.rollback() continue # Commit per feed conn.commit() # Update feed stats cur.execute(""" UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success' WHERE id = %s """, (feed_offers, db_feed_id)) conn.commit() total_products += feed_new total_offers += feed_offers print(f" ✓ {feed_new} new products, {feed_offers} offers inserted") print(f"\n=== DONE ===") print(f"Total new products: {total_products}") print(f"Total offers: {total_offers}") # Final counts cur.execute("SELECT COUNT(*) FROM products") print(f"Products in DB: {cur.fetchone()[0]}") cur.execute("SELECT COUNT(*) FROM product_offers") print(f"Offers in DB: {cur.fetchone()[0]}") cur.execute("SELECT COUNT(*) FROM brands") print(f"Brands in DB: {cur.fetchone()[0]}") cur.execute("SELECT COUNT(*) FROM feeds") print(f"Feeds in DB: {cur.fetchone()[0]}") cur.close() conn.close() if __name__ == "__main__": main()