#!/usr/bin/env python3 """Patch feed-sync-v4.py to add Awin feed support.""" with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'r') as f: code = f.read() # 1. Add gzip import code = code.replace( 'import requests, psycopg2, psycopg2.extras, re, hashlib, time, json, sys, csv, io', 'import requests, psycopg2, psycopg2.extras, re, hashlib, time, json, sys, csv, io, gzip' ) # 2. Add AWIN_FEEDS config after FEEDS dict awin_config = ''' # ── Awin Feeds (gzip compressed CSV, no auth needed) ── AWIN_FEEDS = { "77079": { "name": "JD Sports NL", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/language/nl/cid/139,614,189,194/fid/77079/rid/0/hasEnhancedFeeds/0/columns/aw_deep_link,product_name,aw_product_id,merchant_product_id,merchant_image_url,description,merchant_category,search_price,merchant_name,merchant_id,category_name,category_id,aw_image_url,currency,store_price,delivery_cost,merchant_deep_link,language,last_updated,display_price,data_feed_id,commission_group,merchant_product_category_path,merchant_product_second_category,merchant_product_third_category,brand_name,brand_id,colour,product_short_description,specifications,condition,product_model,model_number,dimensions,keywords,promotional_text,product_type,merchant_thumb_url,large_image,alternate_image,aw_thumb_url,alternate_image_two,alternate_image_three,alternate_image_four,ean,isbn,upc,mpn,parent_product_id,product_GTIN,basket_link,Fashion%3Asuitable_for,Fashion%3Acategory,Fashion%3Asize,Fashion%3Amaterial,Fashion%3Apattern,Fashion%3Aswatch/format/csv/delimiter/%2C/compression/gzip/adultcontent/1/", }, "29905": { "name": "Foot Locker NL", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/29905/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cean%2Cmpn%2Cisbn%2Cmodel_number%2Cproduct_name%2Cdescription%2Cspecifications%2Clanguage%2Cmerchant_deep_link%2Cmerchant_thumb_url%2Cmerchant_image_url%2Cdelivery_time%2Cvalid_from%2Cvalid_to%2Ccurrency%2Csearch_price%2Cstore_price%2Cdelivery_cost%2Cweb_offer%2Cpre_order%2Cin_stock%2Cstock_quantity%2Cwarranty%2Ccondition%2Cparent_product_id%2Ccommission_group%2Clast_updated%2Cdimensions%2Ccolour%2Ckeywords%2Ccustom_1%2Ccustom_2%2Csaving%2CFashion%3Asuitable_for%2CFashion%3Asize%2CFashion%3Amaterial%2CFashion%3Apattern%2CFashion%3Aswatch%2Crating%2Calternate_image%2Clarge_image%2Cbasket_link%2Cproduct_short_description%2Cmerchant_product_category_path%2Cmerchant_product_second_category%2Cmerchant_product_third_category%2Csavings_percent%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Calternate_image_four/", }, "92135": { "name": "adidas NL", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/92135/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cupc%2Cean%2Cmpn%2Cproduct_name%2Cdescription%2Cspecifications%2Cmerchant_deep_link%2Cmerchant_image_url%2Cdelivery_time%2Ccurrency%2Csearch_price%2Cdelivery_cost%2Cin_stock%2Cstock_quantity%2Ccolour%2Ccustom_1%2Ccustom_2%2Ccustom_3%2Ccustom_4%2Ccustom_5%2CFashion%3Asuitable_for%2CFashion%3Asize%2Crating%2Calternate_image%2Cbase_price%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Ccustom_6%2Calternate_image_four/", }, "19187": { "name": "Schuurman Schoenen", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/19187/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cean%2Cmpn%2Cisbn%2Cmodel_number%2Cproduct_name%2Cdescription%2Cspecifications%2Clanguage%2Cmerchant_deep_link%2Cmerchant_thumb_url%2Cmerchant_image_url%2Cdelivery_time%2Cvalid_from%2Cvalid_to%2Ccurrency%2Csearch_price%2Cstore_price%2Cdelivery_cost%2Cweb_offer%2Cpre_order%2Cin_stock%2Cstock_quantity%2Cwarranty%2Ccondition%2Cparent_product_id%2Ccommission_group%2Clast_updated%2Cdimensions%2Ccolour%2Ckeywords%2Ccustom_1%2Ccustom_2%2Csaving%2CFashion%3Asuitable_for%2CFashion%3Asize%2CFashion%3Amaterial%2CFashion%3Apattern%2CFashion%3Aswatch%2Crating%2Calternate_image%2Clarge_image%2Cbasket_link%2Cproduct_short_description%2Cmerchant_product_category_path%2Cmerchant_product_second_category%2Cmerchant_product_third_category%2Csavings_percent%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Calternate_image_four/", }, } ''' # Insert after the FEEDS dict closing brace feeds_end = code.find("}\n\n# ── Sneaker Detection") if feeds_end == -1: print("ERROR: Could not find FEEDS dict end") exit(1) code = code[:feeds_end+1] + awin_config + code[feeds_end+1:] # 3. Add download_awin_csv function after download_feed_csv awin_download = ''' def download_awin_csv(feed_url): """Download gzip-compressed CSV from Awin.""" try: r = requests.get(feed_url, timeout=300) r.raise_for_status() decompressed = gzip.decompress(r.content).decode('utf-8', errors='replace') return list(csv.DictReader(io.StringIO(decompressed))) except Exception as e: print(f" Error downloading Awin feed: {e}") return [] def normalize_awin_row(row): """Normalize Awin CSV row to match Webgains-like dict for processing.""" ean = (row.get('ean') or row.get('product_GTIN') or '').strip() or None price_str = row.get('search_price') or '' store_price_str = row.get('store_price') or row.get('product_price_old') or '' price = parse_price(price_str) store_price = parse_price(store_price_str) # sale_price/price logic: if store_price > search_price, it's on sale sale_price = None orig_price = None if store_price and price and store_price > price: sale_price = str(price) orig_price = str(store_price) return { 'title': (row.get('product_name') or '').strip(), 'brand': (row.get('brand_name') or '').strip(), 'gtin': ean, 'link': (row.get('aw_deep_link') or '').strip(), 'price': orig_price or str(price) if price else '', 'sale_price': sale_price or '', 'image_link': (row.get('merchant_image_url') or row.get('aw_image_url') or '').strip(), 'google_product_category': '', # Awin doesn't have gcat IDs 'google_product_category_text': '', 'product_type': (row.get('product_type') or row.get('merchant_product_category_path') or row.get('merchant_category') or '').strip(), 'item_group_id': (row.get('parent_product_id') or '').strip(), 'color': (row.get('colour') or '').strip(), 'colour': (row.get('colour') or '').strip(), 'size': (row.get('Fashion:size') or '').strip(), 'id': (row.get('aw_product_id') or row.get('merchant_product_id') or '').strip(), 'description': (row.get('description') or '')[:500], 'gender': (row.get('Fashion:suitable_for') or '').strip(), # Extra for model extraction '_model_hint': (row.get('product_model') or '').strip(), } ''' # Insert after download_feed_csv dl_end = code.find("\n# ── Main ──") if dl_end == -1: print("ERROR: Could not find Main section") exit(1) code = code[:dl_end] + awin_download + code[dl_end:] # 4. Add Awin processing loop before the Summary section # Find the summary marker summary_marker = " # Summary\n" summary_idx = code.find(summary_marker) if summary_idx == -1: print("ERROR: Could not find Summary section") exit(1) awin_loop = ''' # ── Awin Feeds ── # Ensure awin provider exists cur.execute("INSERT INTO providers (name, slug, adapter_type) VALUES ('Awin', 'awin', 'csv') ON CONFLICT (slug) DO NOTHING") cur.execute("SELECT id FROM providers WHERE slug = 'awin'") awin_provider_id = cur.fetchone()["id"] conn.commit() for aw_feed_id, aw_cfg in AWIN_FEEDS.items(): if only_feed and aw_feed_id != only_feed: continue shop_name = aw_cfg["name"] feed_url = aw_cfg["url"] print(f"\\n{'='*60}") print(f"Awin: {shop_name} (feed {aw_feed_id})") print(f"{'='*60}") cur.execute(""" INSERT INTO feeds (provider_id, external_id, name, merchant_name, feed_url, product_count) VALUES (%s, %s, %s, %s, %s, 0) ON CONFLICT (provider_id, external_id) DO UPDATE SET name=EXCLUDED.name, merchant_name=EXCLUDED.merchant_name, feed_url=EXCLUDED.feed_url RETURNING id """, (awin_provider_id, f"aw-{aw_feed_id}", shop_name, shop_name, feed_url)) db_feed_id = cur.fetchone()["id"] conn.commit() print(f" Downloading Awin CSV (gzip)...") raw_rows = download_awin_csv(feed_url) if not raw_rows: print(f" No data, skipping"); continue print(f" Downloaded {len(raw_rows)} rows") # Normalize to Webgains-like format rows = [normalize_awin_row(r) for r in raw_rows] rows = [r for r in rows if r['title'] and (r['price'] or r['sale_price'])] print(f" {len(rows)} valid after normalization") # Filter sneakers accepted = [] rejected_count = 0 reject_batch = [] for row in rows: title = (row.get("title") or "").strip() brand_name = (row.get("brand") or "").strip() ptype = (row.get("product_type") or "").strip() gcat = (row.get("google_product_category") or "").strip() gcat_text = (row.get("google_product_category_text") or "").strip() is_snkr, reason = is_sneaker(title, brand_name, ptype, gcat, gcat_text) if is_snkr: accepted.append(row) else: rejected_count += 1 reject_batch.append((db_feed_id, str(row.get("id","")), title, brand_name, ptype or gcat_text, reason)) if reject_batch: psycopg2.extras.execute_batch(cur, """ INSERT INTO import_rejects (feed_id, external_id, name, brand, category, reason) VALUES (%s,%s,%s,%s,%s,%s) """, reject_batch, page_size=500) conn.commit() print(f" Filtered: {len(accepted)} sneakers, {rejected_count} rejected") total_rejected += rejected_count # Group by parent_product_id or brand+name groups = defaultdict(list) for row in accepted: ig = (row.get("item_group_id") or "").strip() if ig: key = f"ig:{ig}" else: t = (row.get("title") or "").strip() b = (row.get("brand") or "").strip() key = f"nm:{b.lower()}||{normalize_product_name(t, b).lower()}" groups[key].append(row) ig_count = sum(1 for k in groups if k.startswith("ig:")) nm_count = len(groups) - ig_count print(f" Grouped: {len(groups)} products ({ig_count} by parent_product_id, {nm_count} by name)") new_products = shop_offers = 0 ph_batch = [] for gkey, variants in groups.items(): primary = variants[0] title = normalize_product_name((primary.get("title") or "").strip(), (primary.get("brand") or "").strip()) brand_name = (primary.get("brand") or "").strip() image = (primary.get("image_link") or "").strip() colour = (primary.get("color") or primary.get("colour") or "").strip() description = (primary.get("description") or "")[:500] ptype = (primary.get("product_type") or "").strip() gcat = (primary.get("google_product_category") or "").strip() gcat_text = (primary.get("google_product_category_text") or "").strip() gender = (primary.get("gender") or "").strip() model, model_name = extract_model(title, brand_name) # Use Awin model hint if no model extracted if not model and primary.get('_model_hint'): model = model_name = primary['_model_hint'] sizes_data = [] all_eans = set() min_price = min_orig = None best_link = (primary.get("link") or "").strip() for v in variants: sp = (v.get("sale_price") or "").strip() pp = (v.get("price") or "").strip() price = parse_price(sp) if sp else parse_price(pp) orig = parse_price(pp) if sp else None link = (v.get("link") or "").strip() ean = (v.get("gtin") or "").strip() or None size = (v.get("size") or "").strip() or None if ean: all_eans.add(ean) if price: entry = {"size": size or "One Size", "price": price} if ean: entry["ean"] = ean if link: entry["url"] = link sizes_data.append(entry) if min_price is None or price < min_price: min_price = price; best_link = link if orig and (min_orig is None or orig < min_orig): min_orig = orig if not min_price or not title or not best_link: continue primary_ean = next(iter(all_eans), None) # Cross-shop matching product_id = None for ean in all_eans: if ean in ean_cache: product_id = ean_cache[ean]; break cur.execute("SELECT id FROM products WHERE ean = %s", (ean,)) r = cur.fetchone() if r: product_id = r["id"]; ean_cache[ean] = product_id; break if not product_id: fp = make_fingerprint(brand_name, title, colour) if fp in fp_cache: product_id = fp_cache[fp] else: cur.execute("SELECT id FROM products WHERE match_fingerprint = %s", (fp,)) r = cur.fetchone() if r: product_id = r["id"]; fp_cache[fp] = product_id if not product_id: 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"] except: conn.rollback() cur.execute("SELECT id FROM brands WHERE slug=%s", (brand_slug,)) r = cur.fetchone() if r: brand_cache[brand_slug] = r["id"] brand_id = brand_cache.get(brand_slug) fp = make_fingerprint(brand_name, title, colour) product_slug = f"{slugify(f'{brand_name}-{title}')[:180]}-{hashlib.md5(f'aw-{aw_feed_id}-{gkey}'.encode()).hexdigest()[:8]}" try: cur.execute(""" INSERT INTO products (name,slug,brand_id,brand,image_url,description,ean, colorway,gender,brand_normalized,model_normalized,colorway_normalized, match_fingerprint,category,product_type, model,model_name,needs_cutout,first_seen_at) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,true,NOW()) RETURNING id """, (title, product_slug, brand_id, brand_name, image, description, primary_ean, colour, gender or None, brand_name.lower() if brand_name else None, title.lower() if title else None, colour.lower() if colour else None, fp, ptype, ptype, model, model_name)) product_id = cur.fetchone()["id"] new_products += 1 fp_cache[fp] = product_id for ean in all_eans: ean_cache[ean] = product_id except psycopg2.errors.UniqueViolation: conn.rollback() cur.execute("SELECT id FROM products WHERE slug=%s", (product_slug,)) r = cur.fetchone() if r: product_id = r["id"] else: continue except Exception as e: conn.rollback() print(f" Error creating '{title}': {e}"); continue else: try: cur.execute("""UPDATE products SET model=COALESCE(model,%s), model_name=COALESCE(model_name,%s), category=COALESCE(NULLIF(category,''),%s), product_type=COALESCE(NULLIF(product_type,''),%s), gender=COALESCE(gender,%s) WHERE id=%s""", (model, model_name, ptype, ptype, gender or None, product_id)) except: conn.rollback() ext_id = str(variants[0].get("id","")).strip() try: cur.execute(""" INSERT INTO product_offers (product_id,feed_id,external_id,price,original_price, currency,product_url,image_url,in_stock,sizes,last_seen_at) VALUES (%s,%s,%s,%s,%s,'EUR',%s,%s,true,%s::jsonb,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,sizes=EXCLUDED.sizes,last_seen_at=NOW(),updated_at=NOW() """, (product_id, db_feed_id, ext_id, min_price, min_orig, best_link, image, json.dumps(sizes_data, ensure_ascii=False))) shop_offers += 1 ph_batch.append((product_id, db_feed_id, min_price)) except Exception as e: conn.rollback() print(f" Error offer '{title}': {e}"); continue if shop_offers % 200 == 0: conn.commit() conn.commit() ph_count = 0 for pid, fid, price in ph_batch: try: cur.execute("""INSERT INTO price_history (product_id,feed_id,price,recorded_at) SELECT %s,%s,%s,NOW() WHERE NOT EXISTS ( SELECT 1 FROM price_history WHERE product_id=%s AND feed_id=%s AND price=%s AND recorded_at > NOW() - INTERVAL '23 hours')""", (pid, fid, price, pid, fid, price)) if cur.rowcount > 0: ph_count += 1 except: conn.rollback() conn.commit() cur.execute("UPDATE feeds SET product_count=%s, last_sync_at=NOW(), last_sync_status='success' WHERE id=%s", (shop_offers, db_feed_id)) conn.commit() total_new += new_products; total_offers += shop_offers; total_ph += ph_count print(f" => {new_products} new products, {shop_offers} offers, {ph_count} price records") ''' code = code[:summary_idx] + awin_loop + code[summary_idx:] with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'w') as f: f.write(code) print("PATCHED: Added Awin feed support with 4 feeds (JD Sports, Foot Locker, adidas, Schuurman)")