#!/usr/bin/env python3 """ SneakerPicks Feed Sync v4 — CSV-based per-shop feed download Downloads each shop's feed as CSV, uses item_group_id for size grouping, extracts model names, stores google_product_category. """ import requests, psycopg2, psycopg2.extras, re, hashlib, time, json, sys, csv, io, gzip, os from collections import defaultdict from dotenv import load_dotenv load_dotenv() # ── Config ── WEBGAINS_API = "https://platform-api.webgains.com" PUBLISHER_ID = os.getenv("WEBGAINS_PUBLISHER_ID", "1347930") WEBGAINS_TOKEN = os.environ["WEBGAINS_TOKEN"] DB_URL = os.environ["DATABASE_URL"] CAMPAIGN_ID = os.getenv("WEBGAINS_CAMPAIGN_ID", "1622230") AWIN_API_KEY = os.environ.get("AWIN_API_KEY", "") # Daisycon feeds: program_id -> {name, url} DAISYCON_FEEDS = { "16474": { "name": "Mizuno", "url": "https://daisycon.io/datafeed/?media_id=418615&standard_id=7&language_code=nl&locale_id=1&type=csv&program_id=16474&html_transform=none&encoding=utf8&general=false", }, "20246": { "name": "OFM (Only For Men)", "url": "https://daisycon.io/datafeed/?media_id=418615&standard_id=7&language_code=nl&locale_id=1&type=csv&program_id=20246&html_transform=none&encoding=utf8&general=false", "pre_filter": "sneaker_keyword", }, } # TradeTracker feeds: feed_key -> {name, url, campaign_id} TRADETRACKER_FEEDS = { "schoenenwinkel": { "name": "Schoenenwinkel.nl", "campaign_id": "3151", "url": "https://pf.tradetracker.net/?aid=506170&encoding=utf-8&type=csv&fid=1855121&categoryType=2&additionalType=2&csvDelimiter=%3B&csvEnclosure=%22&filter_extended=1", }, "mooiesneakers": { "name": "MooieSneakers", "campaign_id": "921189", "url": "https://pf.tradetracker.net/?aid=506170&encoding=utf-8&type=csv&fid=921189&categoryType=2&additionalType=2&csvDelimiter=%3B&csvEnclosure=%22&filter_extended=1", }, "sneakeroutlet": { "name": "SneakerOutlet", "campaign_id": "1661507", "url": "https://pf.tradetracker.net/?aid=506170&encoding=utf-8&type=csv&fid=1661507&categoryType=2&additionalType=2&csvDelimiter=%3B&csvEnclosure=%22&filter_extended=1", }, "keepitsneaker": { "name": "KeepItSneaker", "campaign_id": "2104431", "url": "https://pf.tradetracker.net/?aid=506170&encoding=utf-8&type=csv&fid=2104431&categoryType=2&additionalType=2&csvDelimiter=%3B&csvEnclosure=%22&filter_extended=1", }, } # Feed ID -> Shop config (confirmed from Webgains dashboard) FEEDS = { "17108": {"name": "Van Arendonk", "program_id": "282285"}, "26388": {"name": "Sneakermood", "program_id": "298100"}, "36168": {"name": "Patta", "program_id": "299060"}, "18588": {"name": "Baskets", "program_id": "299740"}, "18808": {"name": "Dickies Life", "program_id": "299900"}, "19868": {"name": "Calico Club", "program_id": "300390"}, "20068": {"name": "Exclusivexitems", "program_id": "300640"}, "25538": {"name": "SikSilk", "program_id": "302970"}, } # Awin feeds: feed_id -> {name, url} 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/", }, "112984": { "name": "Snipes EU", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/language/nl/cid/139,614,189,194/fid/112984/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/", }, "19279": { "name": "Spartoo NL", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/language/nl/fid/19279/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/", }, "91887": { "name": "Street-One NL", "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/language/nl/fid/91887/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/", }, "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/", }, # --- Inactive feeds (old API key expired, need new URLs from Awin) --- # "29905": {"name": "Foot Locker NL"}, # "19187": {"name": "Schuurman Schoenen"}, # "89598": {"name": "Lounge by Zalando NL"}, # "72133": {"name": "Peek & Cloppenburg"}, } # ── Sneaker Detection ── SNEAKER_BRANDS = { 'nike', 'adidas', 'adidas originals', 'new balance', 'puma', 'asics', 'asisc', 'jordan', 'reebok', 'converse', 'vans', 'saucony', 'hoka', 'on running', 'on', 'salomon', 'mizuno', 'diadora', 'karhu', 'filling pieces', 'axel arigato', 'common projects', 'autry', 'golden goose', 'versace', 'gucci', 'balenciaga', 'alexander mcqueen', 'off-white', 'fear of god', 'rick owens', 'maison margiela', 'mm6 maison margiela', 'y-3', 'stone island', 'moncler', 'premiata', 'ghoud', 'philippe model', 'p448', 'date', 'd.a.t.e.', 'womsh', 'copenhagen', 'copenhagen studios', 'nubikk', 'mason garments', 'mercer amsterdam', 'floris van bommel', 'cruyff', 'android homme', 'etq amsterdam', 'ekn', 'yeezy', 'li-ning', 'anta', 'hi-tec', 'hts', 'mikakus', 'cph', 'cph40', 'birkenstock', 'clarks', 'dr. martens', 'lacoste', 'tommy hilfiger', 'calvin klein', 'hugo boss', 'boss', 'fila', 'k-swiss', 'le coq sportif', 'ellesse', 'umbro', 'lotto', 'under armour', 'brooks', 'altra', 'topo athletic', 'ugg', } # Google product category IDs that are footwear (accept these) FOOTWEAR_GCAT_IDS = {'187', '1604', '5325', '5327'} # product_type values that indicate sneakers SNEAKER_PTYPE_PATTERNS = [ 'sneaker', 'footwear', 'schoen', 'shoes', 'trainer', 'running shoe', 'sportschoenen', ] # product_type values that are NOT sneakers (only check product_type, not gcat_text) NON_SNEAKER_PTYPE_PATTERNS = [ 'clothing', 'accessori', 'bag', 'hat', 'cap', 'fragrance', 'home', 'shirt', 'jacket', 'trouser', 'pant', 'hoodie', 'sweater', ] EXCLUDE_TITLE_PATTERNS = [ r'\bt-shirt', r'\btshirt', r'\btee\b', r'\bhoodie', r'\bhoody\b', r'\bsweater', r'\bsweatshirt', r'\bjacket\b', r'\bcoat\b', r'\bpants\b', r'\btrouser', r'\bjogger', r'\bshorts\b', r'\bjeans\b', r'\bshirt\b', r'\bpolo\b', r'\bvest\b', r'\bcap\b', r'\bhat\b', r'\bbeanie\b', r'\bscarf\b', r'\bglove', r'\bsock\b', r'\bsocks\b', r'\bbackpack', r'\bbag\b', r'\bwallet\b', r'\bbelt\b', r'\bwatch\b', r'\bsunglasses', r'\bunderwear', r'\bboxer', r'\bswim', r'\bperfume', r'\bfragrance', r'\bdeodorant', r'\bcandle', r'\bkeychain', r'\bkeyring', r'\bphone case', r'\bairpod', r'\binsole', r'\blaces\b', r'\bcleaner\b', r'\bprotector\b', r'\bspray\b', r'\bbrush\b', r'\btracksuit', r'\bwindbreaker', r'\bpuffer\b', r'\bfleece\b', r'\bcardigan', r'\bcrewneck', r'\blongsleeve', r'\btank top', r'\blegging', r'\bskirt\b', r'\bdress\b', r'\bjumpsuit', r'\bblanket', r'\bpillow', r'\bmug\b', r'\bcup\b', r'\bbottle\b', r'\bincense', r'\bsticker\b', r'\bpatch\b', r'\blanyard', r'\btote\b', r'\bflip.?flop', r'\bsandal', r'\bslipper', r'\bclog\b', r'\bloafer', r'\bpump\b', r'\bheel\b', r'\bwedge\b', r'\bdress shoe', r'\boxford\b', r'\bderby\b', r'\bbrogue\b', r'\bchelsea boot', ] SNEAKER_MODEL_PATTERNS = [ r'air max', r'air force', r'air jordan', r'\baj\d', r'\bdunk\b', r'\bwaffle\b', r'\bcortez\b', r'\bblaze\b', r'\bpegasus\b', r'\b574\b', r'\b990\b', r'\b992\b', r'\b993\b', r'\b550\b', r'\b530\b', r'\b2002r?\b', r'\bgel.?lyte', r'\bgel.?kayano', r'\bgel.?1130', r'\bgel.?nyc', r'\bsamba\b', r'\bgazelle\b', r'\bsuperstar\b', r'\bstan smith\b', r'\bforum\b', r'\bozweego\b', r'\byung\b', r'\bnite jogger', r'\bultraboost\b', r'\bnmd\b', r'\bold skool\b', r'\bsk8.?hi\b', r'\bera\b', r'\bauthentic\b', r'\bslip.?on\b', r'\bchuck taylor\b', r'\ball star\b', r'\bone star\b', r'\bjack purcell\b', r'\bclassic leather\b', r'\bclub c\b', r'\bquestion\b', r'\bsuede\b', r'\brs-?x\b', r'\brs.?connect', r'\beasy rider\b', r'\bshadow\b.*\b6000\b', r'\bjazz\b', r'\bgrid\b', r'\bclifton\b', r'\bbondi\b', r'\bmafate\b', r'\bspeedgoat\b', r'\bxt-?\d', r'\bspeedcross\b', r'\bwave rider\b', r'\bmorelia\b', r'\bchunky\b', r'\bretro\b', r'\bplatform\b', r'\bprogrind\b', r'\bprogrid\b', r'\bcloud\b.*\bmonster', r'\bcloudmonster\b', r'\bozelle\b', r'\bspezial\b', r'\bhandball\b', ] def is_sneaker(title, brand, product_type, gcat_id, gcat_text=''): """Determine if a product is a sneaker. Detection order: 1. Title exclusions (clothing/accessories keywords) 2. Google product category ID (187=Shoes → accept immediately) 3. product_type exclusions (only product_type, NOT gcat_text to avoid false negatives from "Apparel & Accessories > Shoes" matching 'accessori') 4. product_type / gcat_text sneaker patterns 5. Known sneaker brand 6. Known model name patterns """ title_lower = (title or '').lower() brand_lower = (brand or '').lower() ptype_lower = (product_type or '').lower() gcat_id_clean = (gcat_id or '').strip() gcat_text_lower = (gcat_text or '').lower() # Step 1: Title exclusions for pat in EXCLUDE_TITLE_PATTERNS: if re.search(pat, title_lower): return False, f'title_exclude:{pat}' # Step 2: Google category ID check (BEFORE non-sneaker check!) # 187 = "Apparel & Accessories > Shoes" — this is footwear, accept it if gcat_id_clean in FOOTWEAR_GCAT_IDS: return True, f'gcat_id:{gcat_id_clean}' # Step 3: product_type exclusions (only check product_type, not gcat_text) for pat in NON_SNEAKER_PTYPE_PATTERNS: if pat in ptype_lower: return False, f'ptype_exclude:{pat}' # Step 4: product_type / gcat_text sneaker patterns combined = f"{ptype_lower} {gcat_text_lower}" for pat in SNEAKER_PTYPE_PATTERNS: if pat in combined: return True, f'category_match:{pat}' # Step 5: Known sneaker brand if brand_lower in SNEAKER_BRANDS: return True, f'brand_match:{brand_lower}' # Step 6: Model patterns for pat in SNEAKER_MODEL_PATTERNS: if re.search(pat, title_lower): return True, f'model_match:{pat}' return False, 'no_match' # ── Model Extraction ── MODEL_PATTERNS = { 'nike': [ (r'\b(Air Max Plus)\b', 'Air Max Plus'), (r'\b(Air Max 90)\b', 'Air Max 90'), (r'\b(Air Max 95)\b', 'Air Max 95'), (r'\b(Air Max 97)\b', 'Air Max 97'), (r'\b(Air Max 1)\b', 'Air Max 1'), (r'\b(Air Max \d+)\b', None), (r'\b(Air Max)\b', 'Air Max'), (r'\b(Air Force 1)\b', 'Air Force 1'), (r'\b(Air Jordan \d+)\b', None), (r'\b(Dunk (?:Low|High|Mid))\b', None), (r'\b(Dunk)\b', 'Dunk'), (r'\b(Cortez)\b', 'Cortez'), (r'\b(Blazer (?:Low|Mid))\b', None), (r'\b(Blazer)\b', 'Blazer'), (r'\b(Pegasus \d+)\b', None), (r'\b(Pegasus)\b', 'Pegasus'), (r'\b(Vomero \d+)\b', None), (r'\b(V2K Run)\b', 'V2K Run'), (r'\b(P-6000)\b', 'P-6000'), ], 'jordan': [(r'\b(?:Air )?Jordan (\d+)\b', None)], 'adidas': [ (r'\b(Samba OG)\b', 'Samba OG'), (r'\b(Samba)\b', 'Samba'), (r'\b(Gazelle Bold)\b', 'Gazelle Bold'), (r'\b(Gazelle)\b', 'Gazelle'), (r'\b(Superstar)\b', 'Superstar'), (r'\b(Stan Smith)\b', 'Stan Smith'), (r'\b(Forum (?:Low|Mid|High))\b', None), (r'\b(Forum)\b', 'Forum'), (r'\b(Campus \d+s?)\b', None), (r'\b(Campus)\b', 'Campus'), (r'\b(Ozweego)\b', 'Ozweego'), (r'\b(Ozelle)\b', 'Ozelle'), (r'\b(NMD[_ ]?[A-Z]?\d*)\b', None), (r'\b(Ultraboost)\b', 'Ultraboost'), (r'\b(Handball Spezial)\b', 'Handball Spezial'), (r'\b(SL ?72)\b', 'SL 72'), (r'\b(Response CL)\b', 'Response CL'), ], 'adidas originals': [ (r'\b(Samba OG)\b', 'Samba OG'), (r'\b(Samba)\b', 'Samba'), (r'\b(Gazelle)\b', 'Gazelle'), (r'\b(Superstar)\b', 'Superstar'), (r'\b(Stan Smith)\b', 'Stan Smith'), (r'\b(Campus)\b', 'Campus'), ], 'new balance': [(r'\b(\d{3,4}[A-Z]?(?:v\d)?)\b', None)], 'asics': [(r'\b(GEL-[\w]+)', None), (r'\b(GT-\d+)\b', None)], 'puma': [ (r'\b(Suede (?:Classic|XL|VTG))\b', None), (r'\b(Suede)\b', 'Suede'), (r'\b(RS-X)\b', 'RS-X'), (r'\b(Easy Rider)\b', 'Easy Rider'), (r'\b(Palermo)\b', 'Palermo'), (r'\b(Speedcat)\b', 'Speedcat'), ], 'converse': [ (r'\b(Chuck Taylor)\b', 'Chuck Taylor'), (r'\b(Chuck 70)\b', 'Chuck 70'), (r'\b(All Star)\b', 'All Star'), (r'\b(One Star)\b', 'One Star'), ], 'vans': [ (r'\b(Old Skool)\b', 'Old Skool'), (r'\b(Sk8-Hi)\b', 'Sk8-Hi'), (r'\b(Knu Skool)\b', 'Knu Skool'), ], 'reebok': [(r'\b(Classic Leather)\b', 'Classic Leather'), (r'\b(Club C \d+)\b', None)], 'hoka': [(r'\b(Clifton \d+)\b', None), (r'\b(Bondi \d+)\b', None), (r'\b(Speedgoat \d+)\b', None)], 'salomon': [(r'\b(XT-\d+)\b', None), (r'\b(Speedcross \d+)\b', None), (r'\b(ACS Pro)\b', 'ACS Pro')], } def extract_model(title, brand): if not title or not brand: return None, None brand_lower = brand.lower().strip() for bl in [brand_lower, brand_lower.replace(' originals', '')]: for pat, canonical in MODEL_PATTERNS.get(bl, []): m = re.search(pat, title, re.IGNORECASE) if m: return (canonical or m.group(1)), (canonical or m.group(1)) # Generic fallback after_brand = title for b in [brand, brand.split()[0]]: after_brand = re.sub(re.escape(b), '', after_brand, count=1, flags=re.IGNORECASE).strip() color_words = r'\b(?:black|white|grey|gray|blue|red|green|yellow|pink|purple|orange|brown|cream|beige|navy|olive|khaki|silver|gold|phantom|sail|bone|sand|chalk|core|cblack|ftwwht|cloud)\b' parts = re.split(color_words, after_brand, maxsplit=1, flags=re.IGNORECASE) model_part = parts[0].strip(' -/') if parts else after_brand model_part = re.sub(r'\s+(?:Mens?|Womens?|Unisex|Junior|Kids?|WMNS|GS|[WM])$', '', model_part, flags=re.IGNORECASE).strip() if model_part and 1 < len(model_part) < 60: return model_part, model_part return None, None # ── Utilities ── def slugify(text): text = re.sub(r'[^\w\s-]', '', text.lower().strip()) text = re.sub(r'[\s_]+', '-', text) return re.sub(r'-+', '-', text)[:200] def parse_price(val): if not val: return None val = re.sub(r'[A-Z]{3}', '', str(val)).replace(',', '.').strip() m = re.search(r'[\d.]+', val) try: return float(m.group()) if m else None except: return None # ── Size Normalization ── # Valid EU shoe sizes (adult sneakers) VALID_EU_SIZES = set() for _whole in range(35, 53): VALID_EU_SIZES.add(str(_whole)) # 35, 36, ... VALID_EU_SIZES.add(f"{_whole}.5") # 35.5, 36.5, ... VALID_EU_SIZES.add(str(float(_whole))) # 35.0 -> normalize later # Kids sizes for _whole in range(17, 35): VALID_EU_SIZES.add(str(_whole)) VALID_EU_SIZES.add(f"{_whole}.5") # US -> EU conversion (approximate, for men's sneakers) US_TO_EU = { '3': '35.5', '3.5': '36', '4': '36.5', '4.5': '37', '5': '37.5', '5.5': '38', '6': '38.5', '6.5': '39', '7': '40', '7.5': '40.5', '8': '41', '8.5': '42', '9': '42.5', '9.5': '43', '10': '44', '10.5': '44.5', '11': '45', '11.5': '45.5', '12': '46', '12.5': '46.5', '13': '47.5', '14': '48.5', '15': '49.5', } # UK -> EU conversion (approximate) UK_TO_EU = { '3': '36', '3.5': '36.5', '4': '37', '4.5': '37.5', '5': '38', '5.5': '38.5', '6': '39', '6.5': '40', '7': '40.5', '7.5': '41', '8': '42', '8.5': '42.5', '9': '43', '9.5': '44', '10': '44.5', '10.5': '45', '11': '45.5', '11.5': '46', '12': '47', '13': '48', } def normalize_size(raw): """Normalize a raw size string to a clean EU size. Handles formats like: - "MAAT5,5/UK5/US5,5/EUR38,5" -> "38.5" - "42,5" -> "42.5" - "Maat 43" -> "43" - "EU 42.5" -> "42.5" - "EUR38.5" -> "38.5" - "US 10" -> "44" (via conversion table) - "UK 9" -> "43" (via conversion table) - "36 2/3" -> "36.5" (adidas fractional) """ if not raw or not isinstance(raw, str): return None raw = raw.strip() if not raw or raw.lower() in ('one size', 'onesize', 'os', ''): return None # Replace Dutch comma decimals with dots s = raw.replace(',', '.') # Try to extract EUR/EU size first (most reliable) eur_match = re.search(r'(?:EUR?|EU)\s*(\d{2,3}(?:\.\d{1,2})?)', s, re.I) if eur_match: return _clean_eu_size(eur_match.group(1)) # Multi-format string like "MAAT5.5/UK5/US5.5/EUR38.5" — split on / if '/' in s: parts = s.split('/') # Look for EUR/EU part for part in parts: eur_match = re.search(r'(?:EUR?|EU)\s*(\d{2,3}(?:\.\d{1,2})?)', part, re.I) if eur_match: return _clean_eu_size(eur_match.group(1)) # Look for MAAT part (often EU) for part in parts: maat_match = re.search(r'MAAT\s*(\d{2,3}(?:\.\d{1,2})?)', part, re.I) if maat_match: val = maat_match.group(1) size = _clean_eu_size(val) if size and _is_eu_range(size): return size # Try US conversion as last resort for part in parts: us_match = re.search(r'US\s*(\d{1,2}(?:\.\d{1,2})?)', part, re.I) if us_match: eu = US_TO_EU.get(us_match.group(1)) if eu: return eu # "Maat 43" or "Maat 43.5" maat_match = re.search(r'(?:Maat|Size)\s*(\d{2,3}(?:\.\d{1,2})?)', s, re.I) if maat_match: return _clean_eu_size(maat_match.group(1)) # adidas fractional sizes: "36 2/3", "42 1/3" frac_match = re.match(r'^(\d{2})\s+(\d)/(\d)\s*$', s.strip()) if frac_match: whole = int(frac_match.group(1)) num, den = int(frac_match.group(2)), int(frac_match.group(3)) frac = num / den if frac > 0.25: return f"{whole}.5" else: return str(whole) # "US 10" or "US10" (no EU present) us_only = re.match(r'^US\s*(\d{1,2}(?:\.\d{1,2})?)\s*$', s, re.I) if us_only: eu = US_TO_EU.get(us_only.group(1)) if eu: return eu # "UK 9" or "UK9" (no EU present) uk_only = re.match(r'^UK\s*(\d{1,2}(?:\.\d{1,2})?)\s*$', s, re.I) if uk_only: eu = UK_TO_EU.get(uk_only.group(1)) if eu: return eu # Plain number like "42.5" or "42" — check if it's in valid EU range plain_match = re.match(r'^(\d{2,3}(?:\.\d{1,2})?)\s*$', s.strip()) if plain_match: return _clean_eu_size(plain_match.group(1)) # Could not parse — return None (will become "One Size" downstream) return None def _clean_eu_size(val): """Normalize a numeric EU size string: '42.50' -> '42.5', '42.0' -> '42'.""" try: n = float(val) except (ValueError, TypeError): return None if not _is_eu_range_num(n): return None # Snap to nearest 0.5 n = round(n * 2) / 2 if n == int(n): return str(int(n)) return f"{n:.1f}" def _is_eu_range(size_str): """Check if a size string is in valid EU sneaker range (17-52).""" try: return _is_eu_range_num(float(size_str)) except (ValueError, TypeError): return False def _is_eu_range_num(n): """Check if a numeric value is in valid EU sneaker range.""" return 17 <= n <= 52 def normalize_product_name(title, brand): name = title.strip() # Remove "- Maat XX" or "- Maat XX.X" anywhere in the string name = re.sub(r'\s*-\s*(?:Maat|Size)\s*\d{2,3}(?:\s+\d+/\d+|[.,]\d{1,2})?\b', '', name, flags=re.I) # Remove trailing size patterns like "maat 42", "eu 42", "size 42" name = re.sub(r'\s+(?:eu|eur|maat|size)\s*\d{2}(?:[.,]\d{1,2})?\s*$', '', name, 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) # Remove trailing "-SIZE maat SIZE" pattern (e.g., "-36 2/3 maat 36 2/3", "-40 maat 40") name = re.sub(r'-\d{2,3}(?:\s+\d+/\d+)?\s+maat\s+\d{2,3}(?:\s+\d+/\d+)?\s*$', '', name, flags=re.I) # Remove trailing bare size suffix like "-36", "-36 2/3", "-29/30" name = re.sub(r'-\d{2,3}(?:\s+\d+/\d+|/\d+)?\s*$', '', name) # Remove trailing bare "maat" (without number) name = re.sub(r'\s+maat\s*$', '', name, flags=re.I) # Remove trailing material info like "- Mesh/Synthetisch" or "- Leer" name = re.sub(r'\s*-\s*(?:Mesh|Synthetisch|Leer|Canvas|Textiel|Suède|Rubber|Nubuck)(?:/\w+)*\s*$', '', name, flags=re.I) return name.strip(' -') def make_fingerprint(brand, name, colorway): parts = [(brand or '').lower(), re.sub(r'[^\w\s]', '', (name or '').lower()), (colorway or '').lower()] return hashlib.md5('|'.join(p.strip() for p in parts).encode()).hexdigest() # ── CSV Download ── def download_feed_csv(feed_id): url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{CAMPAIGN_ID}/feeds/products" headers = {"Authorization": f"Bearer {WEBGAINS_TOKEN}", "Accept": "text/csv"} try: r = requests.get(url, headers=headers, params={"feedIds[]": feed_id, "format": "csv"}, timeout=300) r.raise_for_status() return list(csv.DictReader(io.StringIO(r.text))) except Exception as e: print(f" Error downloading feed {feed_id}: {e}") return [] def download_tradetracker_csv(feed_url): """Download TradeTracker CSV feed (semicolon-delimited).""" print(f" Downloading TradeTracker CSV...") r = requests.get(feed_url, timeout=300) r.raise_for_status() rows = list(csv.DictReader(io.StringIO(r.text), delimiter=';', quotechar='"')) print(f" Downloaded {len(rows)} rows") return rows def normalize_tradetracker_row(row): """Normalize a TradeTracker row to the same format as Webgains for processing.""" ean = (row.get('EAN') or '').strip() or None price = parse_price(row.get('price')) from_price = parse_price(row.get('fromPrice')) original_price = from_price if from_price and price and from_price > price else None size = (row.get('size') or '').strip() # Extract size from product name if size field is empty (e.g., MooieSneakers) if not size: title = (row.get('name') or '').strip() # Match trailing "-36", "-36 2/3", "-29/30", "-36 2/3 maat 36 2/3" size_match = re.search(r'-(\d{2,3}(?:\s+\d+/\d+|/\d+)?)\s*(?:maat\s+\d[\d/ ]*)?\s*$', title, re.I) if size_match: size = size_match.group(1) return { 'title': (row.get('name') or '').strip(), 'brand': (row.get('brand') or '').strip(), 'gtin': ean, 'link': (row.get('productURL') or '').strip(), 'price': str(price) if price else '', 'sale_price': '', 'image_link': (row.get('imageURL_large') or row.get('imageURL') or '').strip(), 'product_type': (row.get('categories') or '').strip(), 'google_product_category': '', 'google_product_category_text': (row.get('subcategories') or '').strip(), 'item_group_id': '', # TradeTracker doesn't have parent grouping 'color': (row.get('color') or '').strip(), 'size': size, 'id': (row.get('product ID') or row.get('productID') or '').strip(), 'description': (row.get('description') or '')[:500], 'gender': (row.get('gender') or '').strip(), '_original_price_str': str(original_price) if original_price else '', } def download_awin_csv(feed_url): """Download gzip-compressed Awin CSV feed.""" print(f" Downloading Awin CSV (gzip)...") r = requests.get(feed_url, timeout=300) r.raise_for_status() decompressed = gzip.decompress(r.content).decode('utf-8', errors='replace') rows = list(csv.DictReader(io.StringIO(decompressed))) print(f" Downloaded {len(rows)} rows") return rows def normalize_awin_row(row): """Normalize an Awin row to the same format as Webgains for processing.""" ean = (row.get('ean') or row.get('product_GTIN') or row.get('upc') or '').strip() or None price = parse_price(row.get('search_price')) store_price = parse_price(row.get('store_price') or row.get('product_price_old')) original_price = store_price if store_price and price and store_price > price else None size = (row.get('Fashion:size') or '').strip() 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': str(price) if price else '', 'sale_price': '', 'image_link': (row.get('merchant_image_url') or row.get('aw_image_url') or '').strip(), 'product_type': (row.get('merchant_product_category_path') or row.get('merchant_category') or '').strip(), 'google_product_category': '', 'google_product_category_text': (row.get('category_name') or '').strip(), 'item_group_id': (row.get('parent_product_id') or '').strip(), 'color': (row.get('colour') or '').strip(), 'size': size, '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(), '_original_price_str': str(original_price) if original_price else '', } def download_daisycon_csv(feed_url): """Download Daisycon CSV feed (semicolon-delimited).""" print(f" Downloading Daisycon CSV...") r = requests.get(feed_url, timeout=300) r.raise_for_status() rows = list(csv.DictReader(io.StringIO(r.text), delimiter=';', quotechar='"')) print(f" Downloaded {len(rows)} rows") return rows def normalize_daisycon_row(row): """Normalize a Daisycon row to the same format as Webgains for processing.""" ean = (row.get('ean') or '').strip() or None price = parse_price(row.get('price')) price_old = parse_price(row.get('price_old')) original_price = price_old if price_old and price and price_old > price else None size = (row.get('size') or '').strip() # Size normalization happens in the main processing loop via normalize_size() # Clean title: remove size suffix like "Dames/Heren Maat 42,5" title = (row.get('title') or '').strip() title = re.sub(r'\s+(?:Dames/Heren|Dames|Heren|Unisex)\s+Maat\s+[\d,./]+\s*$', '', title, flags=re.I).strip() # Clean title: strip "| Heren |" / "| Dames |" segments (OFM style) title = re.sub(r'\s*\|\s*(?:Heren|Dames|Unisex|Kids)\s*\|?\s*', ' ', title).strip() return { 'title': title, 'brand': (row.get('brand') or '').strip(), 'gtin': ean, 'link': (row.get('link') or '').strip(), 'price': str(price) if price else '', 'sale_price': '', 'image_link': (row.get('image_large') or row.get('image_default') or row.get('image_small') or '').strip(), 'product_type': (row.get('category_path') or '').strip(), 'google_product_category': (row.get('google_category_id') or '').strip(), 'google_product_category_text': (row.get('google_category_path') or '').strip(), 'item_group_id': (row.get('model') or '').strip(), # model field acts as parent grouping 'color': (row.get('color_primary') or '').strip(), 'size': size, 'id': (row.get('sku') or '').strip(), 'description': (row.get('description') or row.get('description_short') or '')[:500], 'gender': (row.get('gender_target') or '').strip(), '_original_price_str': str(original_price) if original_price else '', } 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 # ── Main ── def main(): clean_first = '--clean' in sys.argv only_feed = None for arg in sys.argv[1:]: if arg.startswith('--feed='): only_feed = arg.split('=')[1] print(f"=== SneakerPicks Feed Sync v4 (CSV) ===") print(f" clean={clean_first}, only_feed={only_feed}\n") conn = psycopg2.connect(DB_URL) conn.autocommit = False cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) if clean_first: print("Cleaning old data...") cur.execute(""" TRUNCATE product_offers, price_history, featured_products, collection_products, collection_overrides, product_categories, product_tags, price_alerts, import_rejects RESTART IDENTITY CASCADE """) cur.execute("DELETE FROM products") cur.execute("DELETE FROM feeds") cur.execute("DELETE FROM sync_logs") cur.execute("DELETE FROM brands WHERE slug != 'unknown'") conn.commit() print("Done.\n") cur.execute("SELECT id FROM providers WHERE slug = 'webgains'") row = cur.fetchone() if not row: print("ERROR: No 'webgains' provider!"); return provider_id = row["id"] brand_cache = {} cur.execute("SELECT id, slug FROM brands") for r in cur.fetchall(): brand_cache[r["slug"]] = r["id"] ean_cache = {} fp_cache = {} if not clean_first: cur.execute("SELECT id, ean, match_fingerprint FROM products WHERE ean IS NOT NULL OR match_fingerprint IS NOT NULL") for r in cur.fetchall(): if r["ean"]: ean_cache[r["ean"]] = r["id"] if r["match_fingerprint"]: fp_cache[r["match_fingerprint"]] = r["id"] total_new = total_offers = total_rejected = total_ph = 0 for feed_ext_id, feed_cfg in FEEDS.items(): if only_feed and feed_ext_id != only_feed: continue shop_name = feed_cfg["name"] prog_id = feed_cfg["program_id"] print(f"\n{'='*60}") print(f"Shop: {shop_name} (feed {feed_ext_id}, program {prog_id})") print(f"{'='*60}") # Upsert feed cur.execute(""" INSERT INTO feeds (provider_id, external_id, name, merchant_name, product_count) VALUES (%s, %s, %s, %s, 0) ON CONFLICT (provider_id, external_id) DO UPDATE SET name=EXCLUDED.name, merchant_name=EXCLUDED.merchant_name RETURNING id """, (provider_id, f"wg-{prog_id}", shop_name, shop_name)) db_feed_id = cur.fetchone()["id"] conn.commit() print(f" Downloading CSV...") rows = download_feed_csv(feed_ext_id) if not rows: print(f" No data, skipping"); continue print(f" Downloaded {len(rows)} rows") # 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 item_group_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 item_group_id, {nm_count} by name)") new_products = shop_offers = 0 ph_batch = [] feed_used_pids = set() # Track product_ids used in THIS feed to prevent same-feed dupes 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) # Collect sizes/prices 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 raw_size = (v.get("size") or "").strip() or None if not raw_size: vt = (v.get("variant.title") or "").strip() if vt and 'maat' in vt.lower(): raw_size = re.sub(r'^Maat:\s*', '', vt, flags=re.I).strip() # Handle comma-separated size lists (e.g. "36,37,38,39" or "38,38 1/2") # But NOT Dutch decimals like "42,5" parsed_sizes = [] if raw_size and ',' in raw_size: parts = [p.strip() for p in raw_size.split(',')] is_dutch_decimal = len(parts) == 2 and re.match(r'^\d{1,2}$', parts[0]) and re.match(r'^\d$', parts[1]) if not is_dutch_decimal and len(parts) >= 2: normalized_parts = [normalize_size(p) for p in parts] valid = [n for n in normalized_parts if n is not None] if len(valid) >= 2 and len(valid) >= len(parts) * 0.5: parsed_sizes = valid if not parsed_sizes: ns = normalize_size(raw_size) parsed_sizes = [ns] if ns else [None] else: ns = normalize_size(raw_size) if raw_size else None parsed_sizes = [ns] if ns else [None] if ean: all_eans.add(ean) if price: for size in (parsed_sizes or [None]): 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 # Prevent same-feed duplicates: if product_id already used in this feed, force new product if product_id and product_id in feed_used_pids: product_id = None if not product_id: # Create new product 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'{prog_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,google_product_category, 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,%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, gcat_text or ptype, ptype, gcat or None, 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), google_product_category=COALESCE(google_product_category,%s), gender=COALESCE(gender,%s) WHERE id=%s""", (model, model_name, gcat_text or ptype, ptype, gcat or None, gender or None, product_id)) except: conn.rollback() # Upsert offer 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 product_id=EXCLUDED.product_id, 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 feed_used_pids.add(product_id) 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() # Price history 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() # 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 ── # Get or create Awin provider cur.execute("INSERT INTO providers (name, slug, adapter_type) VALUES ('Awin', 'awin', 'csv') ON CONFLICT (slug) DO NOTHING RETURNING id") row = cur.fetchone() if row: awin_provider_id = row["id"] else: 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"].format(AWIN_API_KEY=AWIN_API_KEY) print(f"\n{'='*60}") print(f"Awin: {shop_name} (fid={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() try: raw_rows = download_awin_csv(feed_url) except Exception as e: print(f" ERROR downloading: {e}") cur.execute("UPDATE feeds SET last_sync_status='error' WHERE id=%s", (db_feed_id,)) conn.commit() continue if not raw_rows: print(f" No data, skipping"); continue # Normalize Awin rows to Webgains-compatible format rows = [normalize_awin_row(r) for r in raw_rows] rows = [r for r in rows if r['title'] and r['price']] print(f" {len(rows)} valid items after normalization") # Filter sneakers (same logic as Webgains) accepted = [] rejected_count = 0 reject_batch = [] for row in rows: title = row['title'] brand_name = row['brand'] ptype = row['product_type'] gcat = row['google_product_category'] gcat_text = row['google_product_category_text'] 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 (same as Webgains item_group_id) groups = defaultdict(list) for row in accepted: ig = (row.get("item_group_id") or "").strip() if ig: key = f"ig:{ig}" else: t = row['title'] b = row['brand'] 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 = [] feed_used_pids = set() # Track product_ids used in THIS feed to prevent same-feed dupes for gkey, variants in groups.items(): primary = variants[0] title = normalize_product_name(primary['title'], primary['brand']) brand_name = primary['brand'] image = primary['image_link'] colour = primary.get('color', '') description = primary.get('description', '') ptype = primary.get('product_type', '') gcat = primary.get('google_product_category', '') gcat_text = primary.get('google_product_category_text', '') gender = primary.get('gender', '') model, model_name = extract_model(title, brand_name) sizes_data = [] all_eans = set() min_price = min_orig = None best_link = primary['link'] for v in variants: price = parse_price(v.get('price')) orig = parse_price(v.get('_original_price_str')) if v.get('_original_price_str') else None link = v.get('link', '') ean = v.get('gtin') or None size = v.get('size', '') 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 (same as Webgains) 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 # Prevent same-feed duplicates: if product_id already used in this feed, force new product if product_id and product_id in feed_used_pids: product_id = None 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,google_product_category, 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,%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, gcat_text or ptype, ptype, gcat or None, 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), google_product_category=COALESCE(google_product_category,%s), gender=COALESCE(gender,%s) WHERE id=%s""", (model, model_name, gcat_text or ptype, ptype, gcat or None, 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 product_id=EXCLUDED.product_id, 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 feed_used_pids.add(product_id) 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() # 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") # ── TradeTracker feeds ── # Get or create TradeTracker provider cur.execute("INSERT INTO providers (name, slug, adapter_type) VALUES ('TradeTracker', 'tradetracker', 'csv') ON CONFLICT (slug) DO NOTHING RETURNING id") row = cur.fetchone() if row: tt_provider_id = row["id"] else: cur.execute("SELECT id FROM providers WHERE slug = 'tradetracker'") tt_provider_id = cur.fetchone()["id"] conn.commit() for tt_key, tt_cfg in TRADETRACKER_FEEDS.items(): if only_feed and tt_key != only_feed: continue shop_name = tt_cfg["name"] feed_url = tt_cfg["url"] campaign_id = tt_cfg["campaign_id"] print(f"\n{'='*60}") print(f"TradeTracker: {shop_name} (campaign={campaign_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 """, (tt_provider_id, f"tt-{campaign_id}", shop_name, shop_name, feed_url)) db_feed_id = cur.fetchone()["id"] conn.commit() try: raw_rows = download_tradetracker_csv(feed_url) except Exception as e: print(f" ERROR downloading: {e}") cur.execute("UPDATE feeds SET last_sync_status='error' WHERE id=%s", (db_feed_id,)) conn.commit() continue if not raw_rows: print(f" No data, skipping"); continue # Print first row keys for debugging if raw_rows: print(f" CSV columns: {list(raw_rows[0].keys())[:10]}...") rows = [normalize_tradetracker_row(r) for r in raw_rows] rows = [r for r in rows if r['title'] and r['price']] print(f" {len(rows)} valid items after normalization") # Filter sneakers accepted = [] rejected_count = 0 reject_batch = [] for row in rows: title = row['title'] brand_name = row['brand'] ptype = row['product_type'] gcat = row['google_product_category'] gcat_text = row['google_product_category_text'] 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 brand+name (TradeTracker has no item_group_id) groups = defaultdict(list) for row in accepted: t = row['title'] b = row['brand'] key = f"nm:{b.lower()}||{normalize_product_name(t, b).lower()}" groups[key].append(row) print(f" Grouped: {len(groups)} products (all by name)") new_products = shop_offers = 0 ph_batch = [] feed_used_pids = set() for gkey, variants in groups.items(): primary = variants[0] title = normalize_product_name(primary['title'], primary['brand']) brand_name = primary['brand'] image = primary['image_link'] colour = primary.get('color', '') description = primary.get('description', '') ptype = primary.get('product_type', '') gcat = primary.get('google_product_category', '') gcat_text = primary.get('google_product_category_text', '') gender = primary.get('gender', '') model, model_name = extract_model(title, brand_name) sizes_data = [] all_eans = set() min_price = min_orig = None best_link = primary['link'] for v in variants: price = parse_price(v.get('price')) orig = parse_price(v.get('_original_price_str')) if v.get('_original_price_str') else None link = v.get('link', '') ean = v.get('gtin') or None size = v.get('size', '') 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 product_id and product_id in feed_used_pids: product_id = None 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'tt-{campaign_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,google_product_category, 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,%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, gcat_text or ptype, ptype, gcat or None, 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), google_product_category=COALESCE(google_product_category,%s), gender=COALESCE(gender,%s) WHERE id=%s""", (model, model_name, gcat_text or ptype, ptype, gcat or None, 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 product_id=EXCLUDED.product_id, 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 feed_used_pids.add(product_id) 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() 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") # ── Daisycon feeds ── cur.execute("INSERT INTO providers (name, slug, adapter_type) VALUES ('Daisycon', 'daisycon', 'csv') ON CONFLICT (slug) DO NOTHING RETURNING id") row = cur.fetchone() if row: dc_provider_id = row["id"] else: cur.execute("SELECT id FROM providers WHERE slug = 'daisycon'") dc_provider_id = cur.fetchone()["id"] conn.commit() for dc_prog_id, dc_cfg in DAISYCON_FEEDS.items(): if only_feed and dc_prog_id != only_feed: continue shop_name = dc_cfg["name"] feed_url = dc_cfg["url"] print(f"\n{'='*60}") print(f"Daisycon: {shop_name} (program={dc_prog_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 """, (dc_provider_id, f"dc-{dc_prog_id}", shop_name, shop_name, feed_url)) db_feed_id = cur.fetchone()["id"] conn.commit() try: raw_rows = download_daisycon_csv(feed_url) except Exception as e: print(f" ERROR downloading: {e}") cur.execute("UPDATE feeds SET last_sync_status='error' WHERE id=%s", (db_feed_id,)) conn.commit() continue if not raw_rows: print(f" No data, skipping"); continue if raw_rows: print(f" CSV columns: {list(raw_rows[0].keys())[:10]}...") rows = [normalize_daisycon_row(r) for r in raw_rows] rows = [r for r in rows if r['title'] and r['price']] print(f" {len(rows)} valid items after normalization") # Pre-filter for feeds with sneaker_keyword filter (e.g. OFM clothing store) pre_filter = dc_cfg.get("pre_filter") if pre_filter == "sneaker_keyword": pre_count = len(rows) filtered_rows = [] for i, row in enumerate(rows): raw = raw_rows[i] if i < len(raw_rows) else {} keywords = raw.get('keywords', '') first_kw = keywords.split('|')[0].strip().lower() if keywords else '' title_lower = (row['title'] or '').lower() gcat_id = (row['google_product_category'] or '').strip() if gcat_id == '187' and (first_kw == 'sneakers' or 'sneaker' in title_lower): filtered_rows.append(row) print(f" Pre-filter ({pre_filter}): {pre_count} -> {len(filtered_rows)} sneakers") rows = filtered_rows # Filter sneakers (Daisycon) accepted = [] rejected_count = 0 reject_batch = [] for row in rows: title = row['title'] brand_name = row['brand'] ptype = row['product_type'] gcat = row['google_product_category'] gcat_text = row['google_product_category_text'] 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 model (item_group_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['title'] b = row['brand'] 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 model, {nm_count} by name)") new_products = shop_offers = 0 ph_batch = [] feed_used_pids = set() for gkey, variants in groups.items(): primary = variants[0] title = normalize_product_name(primary['title'], primary['brand']) brand_name = primary['brand'] image = primary['image_link'] colour = primary.get('color', '') description = primary.get('description', '') ptype = primary.get('product_type', '') gcat = primary.get('google_product_category', '') gcat_text = primary.get('google_product_category_text', '') gender = primary.get('gender', '') model, model_name = extract_model(title, brand_name) sizes_data = [] all_eans = set() min_price = min_orig = None best_link = primary['link'] for v in variants: price = parse_price(v.get('price')) orig = parse_price(v.get('_original_price_str')) if v.get('_original_price_str') else None link = v.get('link', '') ean = v.get('gtin') or None size = v.get('size', '') 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) 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 product_id and product_id in feed_used_pids: product_id = None 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'dc-{dc_prog_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,google_product_category, 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,%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, gcat_text or ptype, ptype, gcat or None, 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), google_product_category=COALESCE(google_product_category,%s), gender=COALESCE(gender,%s) WHERE id=%s""", (model, model_name, gcat_text or ptype, ptype, gcat or None, 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 product_id=EXCLUDED.product_id, 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 feed_used_pids.add(product_id) 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() 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 print(f"\n{'='*60}\nSYNC COMPLETE\n{'='*60}") print(f"New products: {total_new}\nTotal offers: {total_offers}\nRejected: {total_rejected}\nPrice history: {total_ph}") 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 f.name, f.external_id, COUNT(po.id) as offers, COUNT(DISTINCT po.product_id) as products FROM feeds f LEFT JOIN product_offers po ON po.feed_id=f.id GROUP BY f.name, f.external_id ORDER BY offers DESC""") print("\nPer shop:") for r in cur.fetchall(): print(f" {r['name']} ({r['external_id']}): {r['offers']} offers, {r['products']} products") cur.execute("SELECT brand, COUNT(*) c FROM products GROUP BY brand ORDER BY c DESC LIMIT 15") print("\nTop brands:") for r in cur.fetchall(): print(f" {r[0] or '(empty)'}: {r[1]}") cur.execute("SELECT COUNT(*) FROM products WHERE model IS NOT NULL"); wm = cur.fetchone()[0] cur.execute("SELECT COUNT(*) FROM products"); tp = cur.fetchone()[0] print(f"\nModel extraction: {wm}/{tp}") cur.execute("SELECT AVG(jsonb_array_length(sizes)) FROM product_offers WHERE sizes != '[]'::jsonb") avg = cur.fetchone()[0] if avg: print(f"Avg sizes per offer: {avg:.1f}") cur.close(); conn.close() if __name__ == "__main__": main()