#!/usr/bin/env python3 """Fix size dedup issues in feed-sync-v4.py: 1. Strip "- Maat XX - Material" from Foot Locker names 2. Prevent multiple offers per product per feed (merge sizes) 3. Better name normalization """ with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'r') as f: code = f.read() # 1. Fix normalize_product_name to strip "- Maat XX" patterns from middle/end old_norm = '''def normalize_product_name(title, brand): name = re.sub(r'\\s+(?:eu|eur|maat|size)\\s*\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', title.strip(), flags=re.I) name = re.sub(r'\\s+\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name) # Remove size like "- US3.0" or "- EU 42" from end name = re.sub(r'\\s*[-/]\\s*(?:US|EU)\\s*\\d+(?:\\.\\d+)?\\s*$', '', name, flags=re.I) return name.strip(' -')''' new_norm = '''def normalize_product_name(title, brand): name = title.strip() # Strip "- Maat XX" or "- Maat XX 2/3" from anywhere (Foot Locker pattern) name = re.sub(r'\\s*-\\s*Maat\\s+\\d+(?:\\s+\\d+/\\d+|[.,]\\d+)?\\s*', ' ', name, flags=re.I) # Strip "- Material" suffix like "- Leer", "- Mesh/Synthetisch", "- Textil" name = re.sub(r'\\s*-\\s*(?:Leer|Mesh[/\\w]*|Textil|Synthetisch|Suede|Canvas|Nubuck|Rubber)\\s*$', '', name, flags=re.I) # Strip "EU XX", "Maat XX", "Size XX" from end name = re.sub(r'\\s+(?:eu|eur|maat|size)\\s*\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name, flags=re.I) # Strip bare size number from end name = re.sub(r'\\s+\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name) # Strip "- US3.0,EU42.3,UK9.0" (Patta pattern) name = re.sub(r'\\s*-\\s*US\\d+[.,]\\d+.*$', '', name, flags=re.I) # Strip "- US3.0" or "- EU 42" from end name = re.sub(r'\\s*[-/]\\s*(?:US|EU)\\s*\\d+(?:\\.\\d+)?\\s*$', '', name, flags=re.I) # Clean up double spaces and trailing dashes name = re.sub(r'\\s+', ' ', name).strip(' -') return name''' if old_norm in code: code = code.replace(old_norm, new_norm) print("PATCHED: normalize_product_name") else: print("WARNING: Could not find normalize_product_name to patch") # 2. Add a dedup step after all offers are created: merge offers with same product_id+feed_id # We'll add this as a post-processing function and call it at the end of each shop's processing dedup_func = ''' def merge_duplicate_offers(cur, conn, db_feed_id, shop_name): """Merge offers that point to the same product from the same feed. This happens when different group keys resolve to the same product via EAN/fingerprint. Keeps the offer with the most sizes, merges sizes from others, then deletes dupes.""" cur.execute(""" SELECT product_id, array_agg(id ORDER BY jsonb_array_length(sizes) DESC) as offer_ids, COUNT(*) as cnt FROM product_offers WHERE feed_id = %s GROUP BY product_id HAVING COUNT(*) > 1 """, (db_feed_id,)) dupes = cur.fetchall() if not dupes: return 0 merged = 0 for row in dupes: product_id = row['product_id'] offer_ids = row['offer_ids'] keep_id = offer_ids[0] # the one with most sizes delete_ids = offer_ids[1:] # Collect all sizes from all offers cur.execute(""" SELECT sizes, price, original_price FROM product_offers WHERE id = ANY(%s) ORDER BY jsonb_array_length(sizes) DESC """, (offer_ids,)) all_sizes = [] seen_size_keys = set() best_price = None best_orig = None for offer_row in cur.fetchall(): import json sizes = offer_row['sizes'] if isinstance(offer_row['sizes'], list) else json.loads(offer_row['sizes'] or '[]') price = offer_row['price'] orig = offer_row['original_price'] if best_price is None or (price and price < best_price): best_price = price if orig and (best_orig is None or orig < best_orig): best_orig = orig for s in sizes: size_key = s.get('size', '') + '|' + s.get('ean', '') if size_key not in seen_size_keys: seen_size_keys.add(size_key) all_sizes.append(s) # Update the keeper with merged sizes import json cur.execute(""" UPDATE product_offers SET sizes = %s::jsonb, price = %s, original_price = %s WHERE id = %s """, (json.dumps(all_sizes, ensure_ascii=False), best_price, best_orig, keep_id)) # Delete the duplicates cur.execute("DELETE FROM product_offers WHERE id = ANY(%s)", (delete_ids,)) merged += len(delete_ids) conn.commit() if merged > 0: print(f" Merged {merged} duplicate offers for {len(dupes)} products") return merged ''' # Insert the function before "# ── Main ──" main_marker = "\n# ── Main ──" main_idx = code.find(main_marker) if main_idx >= 0: code = code[:main_idx] + dedup_func + code[main_idx:] print("PATCHED: Added merge_duplicate_offers function") else: print("WARNING: Could not find Main section") # 3. Call merge_duplicate_offers after each shop's offers are committed # For Webgains shops old_wg_end = ''' 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") # ── Awin Feeds ──''' new_wg_end = ''' # Merge duplicate offers (same product, same feed) merged = merge_duplicate_offers(cur, conn, db_feed_id, shop_name) shop_offers -= merged 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") # ── Awin Feeds ──''' if old_wg_end in code: code = code.replace(old_wg_end, new_wg_end) print("PATCHED: Added merge call for Webgains shops") else: print("WARNING: Could not find Webgains end section to patch") # For Awin shops - find the similar pattern old_aw_end = ''' 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") # Summary''' new_aw_end = ''' # Merge duplicate offers (same product, same feed) merged = merge_duplicate_offers(cur, conn, db_feed_id, shop_name) shop_offers -= merged 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") # Summary''' if old_aw_end in code: code = code.replace(old_aw_end, new_aw_end) print("PATCHED: Added merge call for Awin shops") else: print("WARNING: Could not find Awin end section to patch") with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'w') as f: f.write(code) print("\nAll patches applied!")