#!/usr/bin/env python3
"""
SneakerPicks Feed Sync v3 — Category filtering + Size dedup + Cross-shop matching + Price history
Handles Webgains feeds with:
- Sneaker-only filtering (category + name-based)
- Size variant aggregation (one offer per product-per-shop)
- Cross-shop product matching (EAN/fingerprint)
- Price history logging
"""
import requests
import psycopg2
import psycopg2.extras
import re
import hashlib
import time
import json
import sys
from collections import defaultdict

# ── Config ──
WEBGAINS_API = "https://platform-api.webgains.com"
PUBLISHER_ID = "1347930"
WEBGAINS_TOKEN = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhdWQiOiIxMDAwMzEiLCJqdGkiOiIwNjM5ODEwYzQ0YzM1MTBlZmZiMGRkYWNiM2EyNWFlNTg2YzFmNmQ5ODNjNGQ2MDA4NzkwZmM0NTkzY2EyNjg4ZGViZDE4NTgwOThmYjJjYiIsImlhdCI6MTc3MTUxNjc4OS44MzAxMzUsIm5iZiI6MTc3MTUxNjc4OS44MzAxMzcsImV4cCI6MjA4NzA0OTU4OS44MjcwODgsInN1YiI6IjIwMTcwOCIsInNjb3BlcyI6W119.ZxlCDsyBI7_SNUWjO-PUxDYKvQVfuUBzU5n-SgqHdAZCnOj9253yVcJIS5NRXJLrnyx0E_9bn8ymfXS71cvPISA4hPAlKCSMjdkv9f2k2FFwdAlQQ6mdhBB2m9dzHUIn34QwA8s4-ikB1ATKIZeqiJL-3Gh9eiMYPpvaJdCCjN-oGPMhhlehkgA04cZSgwy6HfKgb6uPvUSG9w_tvN8U06uEHKV11DedQHNWWGzuXpd2FEL3baV0z9KjSNMZsJZHx2yHUb6gVWJwaR7_louahMvrPXG-YLgnGvNMcPDPjm-z5HJ-JKcPv9_FBqAYGItZxgeGgVoS8MVhW6lc1a-6CvYtVanfXF42MPPNP-Qq76qRtHPLRBdkIATMuBxVJrMzlprT3n4F0sUUltGR4ixZuzMUm5LeyPMHgmjy2UfYzsl9eVqx0oJCmKUyY9maBXwFLJa8J1NHM_nWp3hPpCGkMyhzHK1DIf_z8Wirg1hp5vKts93eERWJBlQwhucUUS_KnZeC_gVaQ7kT4jCwaEWkpPxPxyXWRoK0mcYS_JGz3JbpVhRLj5DVYIB9k54DXqjYlCx8ezJZE9sf2SX3VWvPvD1xSQD1caTCm0To0B-MkeU1YpEfWdUslpCJXrpOy2Wbs8iDHR1BzNrMnOEr4zbz_EoB4JJe3tGTGjzNLNIw6XI"

DB_URL = "postgresql://supabase_admin:HzWrn2rMMxUYWO6OT5X0PScIVEvSjQMup9YSLIrkPQ@172.18.0.5:5432/sneakerpicks"

CAMPAIGNS = {
    "manify.nl": "1622230",
    "ladify.nl": "1622250",
    "thehike.nl": "1622240",
    "mellowed.nl": "1622260",
}

HEADERS = {
    "Authorization": f"Bearer {WEBGAINS_TOKEN}",
    "Content-Type": "application/json",
    "Accept": "application/json",
}

PRODUCT_FIELDS = [
    "title", "image_link", "link", "price", "brand", "description", "id",
    "sale_price", "gtin", "mpn", "colour", "size", "availability", "condition",
    "product_type", "google_product_category"
]

# ── Sneaker Detection ──

# Known sneaker brands (always accept footwear from these)
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',
}

# Category patterns that indicate sneakers/footwear
SNEAKER_CATEGORY_PATTERNS = [
    'sneaker', 'trainer', 'running shoe', 'sportschoenen', 'footwear',
    'athletic shoe', 'casual shoe', 'schoen', 'shoes', 'boots',  # boots ok for sneaker brands
    'apparel & accessories > shoes',
]

# Category patterns that indicate NOT sneakers
NON_SNEAKER_CATEGORY_PATTERNS = [
    'clothing', 'apparel > clothing', 'accessori', 'bag', 'hat', 'cap',
    'fragrance', 'home', 'art', 'book', 'candle', 'poster',
    'shirt', 'jacket', 'trouser', 'pant', 'hoodie', 'sweater',
]

# Title exclusion patterns (definitely not sneakers)
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',  # debatable but not a sneaker
]

# Known sneaker model patterns (accept even without category)
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',
]


def is_sneaker(title, brand, product_type, google_category):
    """Determine if a product is a sneaker. Returns (bool, reason)."""
    title_lower = (title or '').lower()
    brand_lower = (brand or '').lower()
    ptype_lower = (product_type or '').lower()
    gcat_lower = (google_category or '').lower()
    
    # Step 1: Exclude by title (clothing, accessories, etc.)
    for pat in EXCLUDE_TITLE_PATTERNS:
        if re.search(pat, title_lower):
            return False, f'title_exclude:{pat}'
    
    # Step 2: Check category exclusions
    for pat in NON_SNEAKER_CATEGORY_PATTERNS:
        if pat in ptype_lower or pat in gcat_lower:
            return False, f'category_exclude:{pat}'
    
    # Step 3: Check category inclusions
    for pat in SNEAKER_CATEGORY_PATTERNS:
        if pat in ptype_lower or pat in gcat_lower:
            return True, f'category_match:{pat}'
    
    # Step 4: Known sneaker brand → accept (footwear from sneaker brands)
    if brand_lower in SNEAKER_BRANDS:
        # But reject if it's clearly not footwear
        return True, f'brand_match:{brand_lower}'
    
    # Step 5: Known sneaker model patterns in name
    for pat in SNEAKER_MODEL_PATTERNS:
        if re.search(pat, title_lower):
            return True, f'model_match:{pat}'
    
    # Step 6: No signal → reject
    return False, 'no_match'


# ── Size Normalization ──

SIZE_PATTERNS = [
    # "EU 42", "EU42", "Maat 42"
    r'\b(?:eu|eur|maat)\s*(\d{2}(?:[.,]\d)?)\b',
    # "42.5", "43" at end of string or followed by non-alpha
    r'\b(\d{2}(?:[.,]\d{1,2})?)\s*$',
    # "Size 42"
    r'\bsize\s*(\d{2}(?:[.,]\d)?)\b',
    # US sizes
    r'\bus\s*(\d{1,2}(?:[.,]\d)?)\b',
]

def extract_size(title, size_field):
    """Extract size from size field or title."""
    if size_field and size_field.strip():
        return size_field.strip()
    
    for pat in SIZE_PATTERNS:
        m = re.search(pat, title.lower())
        if m:
            return f"EU {m.group(1)}"
    return None


def normalize_product_name(title, brand):
    """Remove size info and normalize product name for grouping."""
    name = title.strip()
    # Remove size suffixes like "42", "EU 42", "Maat 42.5"
    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 trailing whitespace/dashes
    name = name.strip(' -–—')
    return name


def make_fingerprint(brand, name, colorway):
    """Create a match fingerprint for cross-shop dedup."""
    parts = [
        (brand or '').lower().strip(),
        re.sub(r'[^\w\s]', '', (name or '').lower()).strip(),
        (colorway or '').lower().strip(),
    ]
    raw = '|'.join(parts)
    return hashlib.md5(raw.encode()).hexdigest()


# ── Utilities ──

def slugify(text):
    text = text.lower().strip()
    text = re.sub(r'[^\w\s-]', '', text)
    text = re.sub(r'[\s_]+', '-', text)
    text = re.sub(r'-+', '-', text)
    return text[:200]


def parse_price(val):
    if not val:
        return None
    val = str(val).replace(',', '.').strip()
    match = re.search(r'[\d.]+', val)
    if match:
        try:
            return float(match.group())
        except:
            return None
    return None


# ── Webgains API ──

def get_feeds():
    """Get all unique feeds across campaigns."""
    all_feeds = {}
    for site, camp_id in CAMPAIGNS.items():
        url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{camp_id}/feeds"
        try:
            r = requests.get(url, headers=HEADERS, params={"size": 100}, timeout=30)
            r.raise_for_status()
            data = r.json()
            for f in data.get("data", []):
                fid = f["id"]
                if fid not in all_feeds:
                    all_feeds[fid] = {
                        "id": fid,
                        "name": f.get("name", ""),
                        "program_name": f.get("program", {}).get("name", ""),
                        "program_id": str(f.get("program", {}).get("id", "")),
                        "product_count": f.get("products_successfully_stored", 0),
                        "campaign_id": camp_id,
                    }
            print(f"  {site} ({camp_id}): {len(data.get('data', []))} feeds")
        except Exception as e:
            print(f"  {site} ERROR: {e}")
    return all_feeds


def fetch_all_products_cursor(campaign_id, feed_id, max_pages=200):
    """Fetch ALL products using cursor-based pagination."""
    url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{campaign_id}/feeds/products"
    all_products = []
    seen_ids = set()
    cursor = None

    for page in range(max_pages):
        params = {
            "feeds[]": [feed_id],
            "size": 1000,
            "fields[]": PRODUCT_FIELDS,
        }
        if cursor:
            params["search_cursor"] = cursor

        try:
            r = requests.get(url, headers=HEADERS, params=params, timeout=60)
            r.raise_for_status()
            data = r.json()
        except Exception as e:
            print(f"    Page {page} error: {e}")
            break

        products = data.get("data", [])
        if not products:
            break

        pagination = data.get("pagination", {})
        new_cursor = pagination.get("search_cursor")

        new_products = []
        for p in products:
            pid = p.get("id")
            if pid and pid not in seen_ids:
                seen_ids.add(pid)
                new_products.append(p)

        if not new_products:
            break

        all_products.extend(new_products)

        if page % 10 == 0 or len(new_products) < 900:
            print(f"    Page {page}: +{len(new_products)} (total: {len(all_products)})")

        if new_cursor == cursor or not new_cursor:
            break

        cursor = new_cursor
        time.sleep(0.3)

    return all_products


# ── Import Pipeline ──

def process_feed(cur, conn, feed_info, provider_id, brand_cache, ean_product_cache, fingerprint_cache, only_feed=None):
    """Process a single feed: fetch → filter → dedupe sizes → upsert."""
    feed_ext_id = feed_info["id"]
    fname = feed_info["name"]
    camp_id = feed_info["campaign_id"]

    if only_feed and str(feed_ext_id) != str(only_feed):
        return 0, 0, 0

    print(f"\n{'='*60}")
    print(f"Feed: {fname} (ext_id={feed_ext_id})")
    print(f"{'='*60}")

    # Upsert feed in DB
    cur.execute("""
        INSERT INTO feeds (provider_id, external_id, name, merchant_name, product_count)
        VALUES (%s, %s, %s, %s, %s)
        ON CONFLICT (provider_id, external_id) DO UPDATE SET
            name = EXCLUDED.name, merchant_name = EXCLUDED.merchant_name
        RETURNING id
    """, (provider_id, str(feed_ext_id), fname, feed_info["program_name"], feed_info.get("product_count") or 0))
    db_feed_id = cur.fetchone()["id"]
    conn.commit()

    # Fetch products
    products = fetch_all_products_cursor(camp_id, feed_ext_id)
    if not products:
        print("  No products, skipping")
        return 0, 0, 0

    print(f"  Fetched: {len(products)} raw products")

    # ── Step 1: Filter for sneakers ──
    accepted = []
    rejected_count = 0
    reject_batch = []

    for p in products:
        title = (p.get("title") or "").strip()
        brand_name = (p.get("brand") or "").strip()
        ptype_raw = p.get("product_type") or ""
        if isinstance(ptype_raw, list):
            ptype_raw = ptype_raw[0] if ptype_raw else ""
        ptype = str(ptype_raw).strip()
        gcat_raw = p.get("google_product_category") or ""
        if isinstance(gcat_raw, list):
            gcat_raw = gcat_raw[0] if gcat_raw else ""
        gcat = str(gcat_raw).strip()

        is_snkr, reason = is_sneaker(title, brand_name, ptype, gcat)

        if is_snkr:
            accepted.append(p)
        else:
            rejected_count += 1
            reject_batch.append((db_feed_id, str(p.get("id", "")), title, brand_name, ptype or gcat, reason))

    # Log rejects in batches
    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)} accepted, {rejected_count} rejected")

    # ── Step 2: Group by product (size dedup) ──
    # Group key: brand_lower + normalized_name
    groups = defaultdict(list)
    for p in accepted:
        title = (p.get("title") or "").strip()
        brand_name = (p.get("brand") or "").strip()
        norm_name = normalize_product_name(title, brand_name)
        key = f"{brand_name.lower()}||{norm_name.lower()}"
        groups[key].append(p)

    print(f"  Grouped into {len(groups)} unique products (from {len(accepted)} variants)")

    # ── Step 3: Upsert products + offers ──
    new_products = 0
    total_offers = 0
    price_history_batch = []

    for group_key, variants in groups.items():
        # Pick the "best" variant for product info (first one with most data)
        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("colour") or "").strip()
        description = (primary.get("description") or "")[:500]
        ptype_raw2 = primary.get("product_type") or ""
        if isinstance(ptype_raw2, list):
            ptype_raw2 = ptype_raw2[0] if ptype_raw2 else ""
        ptype = str(ptype_raw2).strip()
        gcat_raw2 = primary.get("google_product_category") or ""
        if isinstance(gcat_raw2, list):
            gcat_raw2 = gcat_raw2[0] if gcat_raw2 else ""
        gcat = str(gcat_raw2).strip()

        # Collect all EANs and sizes
        sizes_data = []
        all_eans = set()
        min_price = None
        min_original_price = None
        best_link = (primary.get("link") or "").strip()

        for v in variants:
            price = parse_price(v.get("sale_price") or v.get("price"))
            orig_price = parse_price(v.get("price")) if v.get("sale_price") else None
            link = (v.get("link") or "").strip()

            gtin_val = v.get("gtin") or ""
            if isinstance(gtin_val, list):
                gtin_val = gtin_val[0] if gtin_val else ""
            ean = str(gtin_val).strip() or None

            size_val = extract_size((v.get("title") or ""), (v.get("size") or ""))

            if ean:
                all_eans.add(ean)

            if price:
                size_entry = {"size": size_val or "One Size", "price": price}
                if ean:
                    size_entry["ean"] = ean
                if link:
                    size_entry["url"] = link
                sizes_data.append(size_entry)

                if min_price is None or price < min_price:
                    min_price = price
                    best_link = link
                if orig_price and (min_original_price is None or orig_price < min_original_price):
                    min_original_price = orig_price

        if not min_price or not title or not best_link:
            continue

        primary_ean = next(iter(all_eans), None)

        # ── Cross-shop matching: find or create product ──
        product_id = None

        # Try EAN match first
        for ean in all_eans:
            if ean in ean_product_cache:
                product_id = ean_product_cache[ean]
                break
            cur.execute("SELECT id FROM products WHERE ean = %s", (ean,))
            row = cur.fetchone()
            if row:
                product_id = row["id"]
                ean_product_cache[ean] = product_id
                break

        # Try fingerprint match
        if not product_id:
            fp = make_fingerprint(brand_name, title, colour)
            if fp in fingerprint_cache:
                product_id = fingerprint_cache[fp]
            else:
                cur.execute("SELECT id FROM products WHERE match_fingerprint = %s", (fp,))
                row = cur.fetchone()
                if row:
                    product_id = row["id"]
                    fingerprint_cache[fp] = product_id

        # Create new product
        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 Exception:
                    conn.rollback()
                    cur.execute("SELECT id FROM brands WHERE slug = %s", (brand_slug,))
                    row = cur.fetchone()
                    if row:
                        brand_cache[brand_slug] = row["id"]
            brand_id = brand_cache.get(brand_slug)

            fp = make_fingerprint(brand_name, title, colour)
            base_slug = slugify(f"{brand_name}-{title}")[:180]
            slug_hash = hashlib.md5(f"{feed_ext_id}-{group_key}".encode()).hexdigest()[:8]
            product_slug = f"{base_slug}-{slug_hash}"

            try:
                cur.execute("""
                    INSERT INTO products (name, slug, brand_id, brand, image_url, description, ean,
                                          colorway, brand_normalized, model_normalized, colorway_normalized,
                                          match_fingerprint, category, product_type, first_seen_at)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                    RETURNING id
                """, (title, product_slug, brand_id, brand_name, image, description,
                      primary_ean, colour,
                      brand_name.lower() if brand_name else None,
                      title.lower() if title else None,
                      colour.lower() if colour else None,
                      fp, gcat or ptype, ptype))
                product_id = cur.fetchone()["id"]
                new_products += 1

                # Cache
                fingerprint_cache[fp] = product_id
                for ean in all_eans:
                    ean_product_cache[ean] = product_id
            except psycopg2.errors.UniqueViolation:
                conn.rollback()
                cur.execute("SELECT id FROM products WHERE slug = %s", (product_slug,))
                row = cur.fetchone()
                if row:
                    product_id = row["id"]
                else:
                    continue
            except Exception as e:
                conn.rollback()
                print(f"    Error creating product '{title}': {e}")
                continue

        # ── Upsert offer with aggregated sizes ──
        # Use first external_id as the canonical one for the offer
        ext_id = str(variants[0].get("id", "")).strip()
        sizes_json = json.dumps(sizes_data, ensure_ascii=False)

        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_original_price,
                  best_link, image, sizes_json))
            total_offers += 1

            # Price history
            price_history_batch.append((product_id, db_feed_id, min_price))
        except Exception as e:
            conn.rollback()
            print(f"    Error upserting offer for '{title}': {e}")
            continue

        # Commit every 200 products
        if total_offers % 200 == 0:
            conn.commit()

    conn.commit()

    # ── Price history (only insert if price changed) ──
    for product_id, feed_id, price in price_history_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'
                )
            """, (product_id, feed_id, price, product_id, feed_id, price))
        except Exception:
            conn.rollback()
    conn.commit()

    # Update feed stats
    cur.execute("""
        UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success'
        WHERE id = %s
    """, (total_offers, db_feed_id))
    conn.commit()

    print(f"  ✓ {new_products} new products, {total_offers} offers, {len(price_history_batch)} price records")
    return new_products, total_offers, rejected_count


def main():
    only_feed = None
    clean_first = False

    for arg in sys.argv[1:]:
        if arg == '--clean':
            clean_first = True
        elif arg.startswith('--feed='):
            only_feed = arg.split('=')[1]

    print("=== SneakerPicks Feed Sync v3 ===")
    print(f"  Options: only_feed={only_feed}, clean={clean_first}")
    print()

    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")

    # Get provider
    cur.execute("SELECT id FROM providers WHERE slug = 'webgains'")
    provider_id = cur.fetchone()["id"]

    # Caches
    brand_cache = {}
    cur.execute("SELECT id, slug FROM brands")
    for row in cur.fetchall():
        brand_cache[row["slug"]] = row["id"]

    ean_product_cache = {}
    fingerprint_cache = {}

    # If not clean, pre-load caches
    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 row in cur.fetchall():
            if row["ean"]:
                ean_product_cache[row["ean"]] = row["id"]
            if row["match_fingerprint"]:
                fingerprint_cache[row["match_fingerprint"]] = row["id"]

    print("Fetching feeds from Webgains API...")
    feeds = get_feeds()
    print(f"Found {len(feeds)} unique feeds\n")

    total_new = 0
    total_offers = 0
    total_rejected = 0

    for feed_ext_id, feed_info in sorted(feeds.items()):
        new, offers, rejected = process_feed(
            cur, conn, feed_info, provider_id,
            brand_cache, ean_product_cache, fingerprint_cache,
            only_feed=only_feed
        )
        total_new += new
        total_offers += offers
        total_rejected += rejected

    # ── Summary ──
    print(f"\n{'='*60}")
    print(f"SYNC COMPLETE")
    print(f"{'='*60}")
    print(f"New products: {total_new}")
    print(f"Total offers: {total_offers}")
    print(f"Rejected: {total_rejected}")

    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 COUNT(*) FROM import_rejects")
    print(f"Rejects logged: {cur.fetchone()[0]}")

    cur.execute("""
        SELECT f.name, 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 ORDER BY offers DESC
    """)
    print("\nPer feed:")
    for row in cur.fetchall():
        print(f"  {row['name']}: {row['offers']} offers, {row['products']} products")

    cur.execute("SELECT brand, COUNT(*) c FROM products GROUP BY brand ORDER BY c DESC LIMIT 15")
    print("\nTop brands:")
    for row in cur.fetchall():
        print(f"  {row[0] or '(empty)'}: {row[1]}")

    cur.close()
    conn.close()


if __name__ == "__main__":
    main()