#!/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["AWIN_API_KEY"]

# 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",
    },
}

# 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/{AWIN_API_KEY}/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/{AWIN_API_KEY}/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/{AWIN_API_KEY}/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/{AWIN_API_KEY}/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/",
    },
    "89598": {
        "name": "Lounge by Zalando NL",
        "url": "https://productdata.awin.com/datafeed/download/apikey/{AWIN_API_KEY}/fid/89598/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%2Cproduct_name%2Cdescription%2Cmerchant_deep_link%2Cmerchant_image_url%2Csearch_price%2Cin_stock%2Ccondition%2Cparent_product_id%2Ccolour%2CFashion%3Asuitable_for%2CFashion%3Apattern%2Calternate_image%2Calternate_image_two%2Calternate_image_three%2Calternate_image_four/",
    },
}

# â”€â”€ 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

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
    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 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()
    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 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
                size = (v.get("size") or "").strip() or None
                if not size:
                    vt = (v.get("variant.title") or "").strip()
                    if vt and 'maat' in vt.lower():
                        size = re.sub(r'^Maat:\s*', '', vt, flags=re.I).strip()
                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

            # 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
                        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
                        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
                        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()
