#!/usr/bin/env python3 """Import Patta feed (25578) into SneakerPicks — additive, no truncation.""" import requests, psycopg2, psycopg2.extras, re, hashlib, time, sys 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" FEED_ID = 25578 CAMPAIGN_ID = "1622230" # any campaign works HEADERS = { "Authorization": f"Bearer {WEBGAINS_TOKEN}", "Content-Type": "application/json", "Accept": "application/json", } PRODUCT_FIELDS = ["title", "image_link", "link", "price", "brand", "description", "id", "sale_price", "gtin", "mpn", "colour", "size", "availability", "condition", "product_type", "google_product_category"] # Sneaker keywords for filtering SNEAKER_KEYWORDS = [ 'sneaker', 'sneakers', 'trainer', 'trainers', 'running shoe', 'air max', 'air force', 'jordan', 'dunk', 'yeezy', 'new balance', 'converse', 'chuck taylor', 'vans old skool', 'vans sk8', 'adidas originals', 'stan smith', 'superstar', 'forum', 'puma suede', 'puma rs', 'reebok classic', 'club c', 'asics gel', 'saucony', 'on cloud', 'hoka', 'nb 550', 'nb 990', 'nb 574', 'nb 2002', ] SHOE_CATEGORIES = ['shoes', 'footwear', 'sneakers', 'schoenen', 'sportschoenen'] 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) return float(match.group()) if match else None def is_sneaker(p): """Filter: is this product a sneaker?""" title = (p.get("title") or "").lower() if isinstance(p.get("title"), str) else "" pt_raw = p.get("product_type") or "" ptype = " ".join(pt_raw) if isinstance(pt_raw, list) else str(pt_raw) ptype = ptype.lower() gc_raw = p.get("google_product_category") or "" gcat = " ".join(gc_raw) if isinstance(gc_raw, list) else str(gc_raw) gcat = gcat.lower() desc = (p.get("description") or "").lower() if isinstance(p.get("description"), str) else "" combined = f"{title} {ptype} {gcat} {desc}" # Check category signals for cat in SHOE_CATEGORIES: if cat in ptype or cat in gcat: return True # Check keywords for kw in SNEAKER_KEYWORDS: if kw in combined: return True return False def fetch_all_products(max_pages=200): url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{CAMPAIGN_ID}/feeds/products" all_products = [] seen_ids = set() cursor = None for page in range(max_pages): params = {"feeds[]": [FEED_ID], "size": 1000, "fields[]": PRODUCT_FIELDS} if cursor: params["search_cursor"] = cursor r = requests.get(url, headers=HEADERS, params=params, timeout=60) r.raise_for_status() data = r.json() products = data.get("data", []) if not products: break pagination = data.get("pagination", {}) new_cursor = pagination.get("search_cursor") new = [p for p in products if p.get("id") and p["id"] not in seen_ids] for p in new: seen_ids.add(p["id"]) all_products.extend(new) if page % 5 == 0: print(f" Page {page}: total {len(all_products)}") if new_cursor == cursor or not new_cursor or len(new) < 900: break cursor = new_cursor time.sleep(0.3) return all_products def main(): print("=== Patta Feed Import ===\n") # Fetch products print("Fetching Patta feed products...") all_products = fetch_all_products() print(f"Total in feed: {len(all_products)}") # Filter sneakers sneakers = [p for p in all_products if is_sneaker(p)] print(f"Sneakers after filter: {len(sneakers)}") # If very few sneakers, Patta is a sneaker store so import all footwear-like items # Actually let's be less strict — Patta is primarily a sneaker/streetwear store # Import all products that have a price and look like shoes # Let's check what product_types exist ptypes = {} for p in all_products: pt_raw = p.get("product_type") or "unknown" pt = " > ".join(pt_raw).strip() if isinstance(pt_raw, list) else str(pt_raw).strip() ptypes[pt] = ptypes.get(pt, 0) + 1 print("\nProduct types:") for pt, c in sorted(ptypes.items(), key=lambda x: -x[1])[:30]: print(f" {pt}: {c}") # Also filter by product_type containing Footwear or sneakers footwear = [] for p in all_products: pt_raw = p.get("product_type") or "" ptype = " ".join(pt_raw).lower() if isinstance(pt_raw, list) else str(pt_raw).lower() if "footwear" in ptype or "sneaker" in ptype or "shoe" in ptype: footwear.append(p) print(f"Footwear by product_type: {len(footwear)}") # Use footwear filter (more precise than keyword matching) if len(footwear) >= 100: sneakers = footwear print(f"\nImporting {len(sneakers)} products...") conn = psycopg2.connect(DB_URL) conn.autocommit = False cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Get provider cur.execute("SELECT id FROM providers WHERE slug = 'webgains'") provider_id = cur.fetchone()["id"] # Check if feed already exists cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s", (provider_id, str(FEED_ID))) existing = cur.fetchone() if existing: db_feed_id_tmp = existing["id"] # Delete old offers for this feed to reimport cleanly cur.execute("DELETE FROM product_offers WHERE feed_id = %s", (db_feed_id_tmp,)) cur.execute("DELETE FROM feeds WHERE id = %s", (db_feed_id_tmp,)) conn.commit() 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_ID), "Patta", "Patta", len(sneakers))) db_feed_id = cur.fetchone()["id"] conn.commit() # Brand cache brand_cache = {} cur.execute("SELECT id, slug FROM brands") for row in cur.fetchall(): brand_cache[row["slug"]] = row["id"] # EAN cache from existing products ean_cache = {} cur.execute("SELECT id, ean FROM products WHERE ean IS NOT NULL AND ean != ''") for row in cur.fetchall(): ean_cache[row["ean"]] = row["id"] new_products = 0 new_offers = 0 ean_matches = 0 for i, p in enumerate(sneakers): 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 colour = (p.get("colour") or "").strip() 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: try: 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"] conn.commit() except: conn.rollback() cur.execute("SELECT id FROM brands WHERE slug = %s", (brand_slug,)) row = cur.fetchone() brand_cache[brand_slug] = row["id"] if row else None if not brand_cache[brand_slug]: continue brand_id = brand_cache[brand_slug] # Check EAN match with existing products product_id = None if ean and ean in ean_cache: product_id = ean_cache[ean] ean_matches += 1 if not product_id: base_slug = slugify(f"{brand_name}-{title}")[:180] slug_hash = hashlib.md5(f"{FEED_ID}-{ext_id}".encode()).hexdigest()[:8] product_slug = f"{base_slug}-{slug_hash}" try: cur.execute(""" INSERT INTO products (name, slug, brand_id, brand, image_url, description, ean, colorway, brand_normalized, first_seen_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (slug) DO NOTHING RETURNING id """, (title, product_slug, brand_id, brand_name, image, (p.get("description") or "")[:500], ean, colour, brand_name.lower() if brand_name else None)) row = cur.fetchone() if row: product_id = row["id"] new_products += 1 if ean: ean_cache[ean] = product_id else: cur.execute("SELECT id FROM products WHERE slug = %s", (product_slug,)) row = cur.fetchone() product_id = row["id"] if row else None conn.commit() except Exception as e: conn.rollback() continue if not product_id: 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)) new_offers += 1 conn.commit() except Exception as e: conn.rollback() continue if (i + 1) % 500 == 0: print(f" Processed {i+1}/{len(sneakers)}") # Update feed cur.execute("UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success' WHERE id = %s", (new_offers, db_feed_id)) conn.commit() print(f"\n=== RESULTS ===") print(f"Total in feed: {len(all_products)}") print(f"Sneakers filtered: {len(sneakers)}") print(f"EAN matches (dedup): {ean_matches}") print(f"New products: {new_products}") print(f"Offers created: {new_offers}") # DB totals cur.execute("SELECT COUNT(*) FROM products") print(f"\nDB Total products: {cur.fetchone()[0]}") cur.execute("SELECT COUNT(*) FROM product_offers") print(f"DB Total offers: {cur.fetchone()[0]}") cur.execute("SELECT f.name, COUNT(po.id) FROM feeds f LEFT JOIN product_offers po ON po.feed_id = f.id GROUP BY f.name ORDER BY COUNT(po.id) DESC") print("\nOffers per feed:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]}") cur.close() conn.close() print("\nDone!") if __name__ == "__main__": main()