#!/usr/bin/env python3
"""Patch feed-sync-v4.py to add Awin feed support."""

with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'r') as f:
    code = f.read()

# 1. Add gzip import
code = code.replace(
    'import requests, psycopg2, psycopg2.extras, re, hashlib, time, json, sys, csv, io',
    'import requests, psycopg2, psycopg2.extras, re, hashlib, time, json, sys, csv, io, gzip'
)

# 2. Add AWIN_FEEDS config after FEEDS dict
awin_config = '''

# ── Awin Feeds (gzip compressed CSV, no auth needed) ──
AWIN_FEEDS = {
    "77079": {
        "name": "JD Sports NL",
        "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/language/nl/cid/139,614,189,194/fid/77079/rid/0/hasEnhancedFeeds/0/columns/aw_deep_link,product_name,aw_product_id,merchant_product_id,merchant_image_url,description,merchant_category,search_price,merchant_name,merchant_id,category_name,category_id,aw_image_url,currency,store_price,delivery_cost,merchant_deep_link,language,last_updated,display_price,data_feed_id,commission_group,merchant_product_category_path,merchant_product_second_category,merchant_product_third_category,brand_name,brand_id,colour,product_short_description,specifications,condition,product_model,model_number,dimensions,keywords,promotional_text,product_type,merchant_thumb_url,large_image,alternate_image,aw_thumb_url,alternate_image_two,alternate_image_three,alternate_image_four,ean,isbn,upc,mpn,parent_product_id,product_GTIN,basket_link,Fashion%3Asuitable_for,Fashion%3Acategory,Fashion%3Asize,Fashion%3Amaterial,Fashion%3Apattern,Fashion%3Aswatch/format/csv/delimiter/%2C/compression/gzip/adultcontent/1/",
    },
    "29905": {
        "name": "Foot Locker NL",
        "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/29905/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cean%2Cmpn%2Cisbn%2Cmodel_number%2Cproduct_name%2Cdescription%2Cspecifications%2Clanguage%2Cmerchant_deep_link%2Cmerchant_thumb_url%2Cmerchant_image_url%2Cdelivery_time%2Cvalid_from%2Cvalid_to%2Ccurrency%2Csearch_price%2Cstore_price%2Cdelivery_cost%2Cweb_offer%2Cpre_order%2Cin_stock%2Cstock_quantity%2Cwarranty%2Ccondition%2Cparent_product_id%2Ccommission_group%2Clast_updated%2Cdimensions%2Ccolour%2Ckeywords%2Ccustom_1%2Ccustom_2%2Csaving%2CFashion%3Asuitable_for%2CFashion%3Asize%2CFashion%3Amaterial%2CFashion%3Apattern%2CFashion%3Aswatch%2Crating%2Calternate_image%2Clarge_image%2Cbasket_link%2Cproduct_short_description%2Cmerchant_product_category_path%2Cmerchant_product_second_category%2Cmerchant_product_third_category%2Csavings_percent%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Calternate_image_four/",
    },
    "92135": {
        "name": "adidas NL",
        "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/92135/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cupc%2Cean%2Cmpn%2Cproduct_name%2Cdescription%2Cspecifications%2Cmerchant_deep_link%2Cmerchant_image_url%2Cdelivery_time%2Ccurrency%2Csearch_price%2Cdelivery_cost%2Cin_stock%2Cstock_quantity%2Ccolour%2Ccustom_1%2Ccustom_2%2Ccustom_3%2Ccustom_4%2Ccustom_5%2CFashion%3Asuitable_for%2CFashion%3Asize%2Crating%2Calternate_image%2Cbase_price%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Ccustom_6%2Calternate_image_four/",
    },
    "19187": {
        "name": "Schuurman Schoenen",
        "url": "https://productdata.awin.com/datafeed/download/apikey/489bdc79c6362be4f7d60e6b2506a2de/fid/19187/format/csv/language/nl/delimiter/%2C/compression/gzip/columns/data_feed_id%2Cmerchant_id%2Cmerchant_name%2Caw_product_id%2Caw_deep_link%2Caw_image_url%2Caw_thumb_url%2Ccategory_id%2Ccategory_name%2Cbrand_id%2Cbrand_name%2Cmerchant_product_id%2Cmerchant_category%2Cean%2Cmpn%2Cisbn%2Cmodel_number%2Cproduct_name%2Cdescription%2Cspecifications%2Clanguage%2Cmerchant_deep_link%2Cmerchant_thumb_url%2Cmerchant_image_url%2Cdelivery_time%2Cvalid_from%2Cvalid_to%2Ccurrency%2Csearch_price%2Cstore_price%2Cdelivery_cost%2Cweb_offer%2Cpre_order%2Cin_stock%2Cstock_quantity%2Cwarranty%2Ccondition%2Cparent_product_id%2Ccommission_group%2Clast_updated%2Cdimensions%2Ccolour%2Ckeywords%2Ccustom_1%2Ccustom_2%2Csaving%2CFashion%3Asuitable_for%2CFashion%3Asize%2CFashion%3Amaterial%2CFashion%3Apattern%2CFashion%3Aswatch%2Crating%2Calternate_image%2Clarge_image%2Cbasket_link%2Cproduct_short_description%2Cmerchant_product_category_path%2Cmerchant_product_second_category%2Cmerchant_product_third_category%2Csavings_percent%2Cproduct_price_old%2Calternate_image_two%2Calternate_image_three%2Calternate_image_four/",
    },
}
'''

# Insert after the FEEDS dict closing brace
feeds_end = code.find("}\n\n# ── Sneaker Detection")
if feeds_end == -1:
    print("ERROR: Could not find FEEDS dict end")
    exit(1)
code = code[:feeds_end+1] + awin_config + code[feeds_end+1:]

# 3. Add download_awin_csv function after download_feed_csv
awin_download = '''

def download_awin_csv(feed_url):
    """Download gzip-compressed CSV from Awin."""
    try:
        r = requests.get(feed_url, timeout=300)
        r.raise_for_status()
        decompressed = gzip.decompress(r.content).decode('utf-8', errors='replace')
        return list(csv.DictReader(io.StringIO(decompressed)))
    except Exception as e:
        print(f"  Error downloading Awin feed: {e}")
        return []

def normalize_awin_row(row):
    """Normalize Awin CSV row to match Webgains-like dict for processing."""
    ean = (row.get('ean') or row.get('product_GTIN') or '').strip() or None
    price_str = row.get('search_price') or ''
    store_price_str = row.get('store_price') or row.get('product_price_old') or ''
    price = parse_price(price_str)
    store_price = parse_price(store_price_str)
    
    # sale_price/price logic: if store_price > search_price, it's on sale
    sale_price = None
    orig_price = None
    if store_price and price and store_price > price:
        sale_price = str(price)
        orig_price = str(store_price)
    
    return {
        'title': (row.get('product_name') or '').strip(),
        'brand': (row.get('brand_name') or '').strip(),
        'gtin': ean,
        'link': (row.get('aw_deep_link') or '').strip(),
        'price': orig_price or str(price) if price else '',
        'sale_price': sale_price or '',
        'image_link': (row.get('merchant_image_url') or row.get('aw_image_url') or '').strip(),
        'google_product_category': '',  # Awin doesn't have gcat IDs
        'google_product_category_text': '',
        'product_type': (row.get('product_type') or row.get('merchant_product_category_path') or row.get('merchant_category') or '').strip(),
        'item_group_id': (row.get('parent_product_id') or '').strip(),
        'color': (row.get('colour') or '').strip(),
        'colour': (row.get('colour') or '').strip(),
        'size': (row.get('Fashion:size') or '').strip(),
        'id': (row.get('aw_product_id') or row.get('merchant_product_id') or '').strip(),
        'description': (row.get('description') or '')[:500],
        'gender': (row.get('Fashion:suitable_for') or '').strip(),
        # Extra for model extraction
        '_model_hint': (row.get('product_model') or '').strip(),
    }
'''

# Insert after download_feed_csv
dl_end = code.find("\n# ── Main ──")
if dl_end == -1:
    print("ERROR: Could not find Main section")
    exit(1)
code = code[:dl_end] + awin_download + code[dl_end:]

# 4. Add Awin processing loop before the Summary section
# Find the summary marker
summary_marker = "    # Summary\n"
summary_idx = code.find(summary_marker)
if summary_idx == -1:
    print("ERROR: Could not find Summary section")
    exit(1)

awin_loop = '''    # ── Awin Feeds ──
    # Ensure awin provider exists
    cur.execute("INSERT INTO providers (name, slug, adapter_type) VALUES ('Awin', 'awin', 'csv') ON CONFLICT (slug) DO NOTHING")
    cur.execute("SELECT id FROM providers WHERE slug = 'awin'")
    awin_provider_id = cur.fetchone()["id"]
    conn.commit()

    for aw_feed_id, aw_cfg in AWIN_FEEDS.items():
        if only_feed and aw_feed_id != only_feed:
            continue

        shop_name = aw_cfg["name"]
        feed_url = aw_cfg["url"]

        print(f"\\n{'='*60}")
        print(f"Awin: {shop_name} (feed {aw_feed_id})")
        print(f"{'='*60}")

        cur.execute("""
            INSERT INTO feeds (provider_id, external_id, name, merchant_name, feed_url, product_count)
            VALUES (%s, %s, %s, %s, %s, 0)
            ON CONFLICT (provider_id, external_id) DO UPDATE SET
                name=EXCLUDED.name, merchant_name=EXCLUDED.merchant_name, feed_url=EXCLUDED.feed_url
            RETURNING id
        """, (awin_provider_id, f"aw-{aw_feed_id}", shop_name, shop_name, feed_url))
        db_feed_id = cur.fetchone()["id"]
        conn.commit()

        print(f"  Downloading Awin CSV (gzip)...")
        raw_rows = download_awin_csv(feed_url)
        if not raw_rows:
            print(f"  No data, skipping"); continue
        print(f"  Downloaded {len(raw_rows)} rows")

        # Normalize to Webgains-like format
        rows = [normalize_awin_row(r) for r in raw_rows]
        rows = [r for r in rows if r['title'] and (r['price'] or r['sale_price'])]
        print(f"  {len(rows)} valid after normalization")

        # Filter sneakers
        accepted = []
        rejected_count = 0
        reject_batch = []
        for row in rows:
            title = (row.get("title") or "").strip()
            brand_name = (row.get("brand") or "").strip()
            ptype = (row.get("product_type") or "").strip()
            gcat = (row.get("google_product_category") or "").strip()
            gcat_text = (row.get("google_product_category_text") or "").strip()

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

        if reject_batch:
            psycopg2.extras.execute_batch(cur, """
                INSERT INTO import_rejects (feed_id, external_id, name, brand, category, reason)
                VALUES (%s,%s,%s,%s,%s,%s)
            """, reject_batch, page_size=500)
            conn.commit()

        print(f"  Filtered: {len(accepted)} sneakers, {rejected_count} rejected")
        total_rejected += rejected_count

        # Group by parent_product_id or brand+name
        groups = defaultdict(list)
        for row in accepted:
            ig = (row.get("item_group_id") or "").strip()
            if ig:
                key = f"ig:{ig}"
            else:
                t = (row.get("title") or "").strip()
                b = (row.get("brand") or "").strip()
                key = f"nm:{b.lower()}||{normalize_product_name(t, b).lower()}"
            groups[key].append(row)

        ig_count = sum(1 for k in groups if k.startswith("ig:"))
        nm_count = len(groups) - ig_count
        print(f"  Grouped: {len(groups)} products ({ig_count} by parent_product_id, {nm_count} by name)")

        new_products = shop_offers = 0
        ph_batch = []

        for gkey, variants in groups.items():
            primary = variants[0]
            title = normalize_product_name((primary.get("title") or "").strip(), (primary.get("brand") or "").strip())
            brand_name = (primary.get("brand") or "").strip()
            image = (primary.get("image_link") or "").strip()
            colour = (primary.get("color") or primary.get("colour") or "").strip()
            description = (primary.get("description") or "")[:500]
            ptype = (primary.get("product_type") or "").strip()
            gcat = (primary.get("google_product_category") or "").strip()
            gcat_text = (primary.get("google_product_category_text") or "").strip()
            gender = (primary.get("gender") or "").strip()
            model, model_name = extract_model(title, brand_name)
            # Use Awin model hint if no model extracted
            if not model and primary.get('_model_hint'):
                model = model_name = primary['_model_hint']

            sizes_data = []
            all_eans = set()
            min_price = min_orig = None
            best_link = (primary.get("link") or "").strip()

            for v in variants:
                sp = (v.get("sale_price") or "").strip()
                pp = (v.get("price") or "").strip()
                price = parse_price(sp) if sp else parse_price(pp)
                orig = parse_price(pp) if sp else None
                link = (v.get("link") or "").strip()
                ean = (v.get("gtin") or "").strip() or None
                size = (v.get("size") or "").strip() or None
                if ean: all_eans.add(ean)
                if price:
                    entry = {"size": size or "One Size", "price": price}
                    if ean: entry["ean"] = ean
                    if link: entry["url"] = link
                    sizes_data.append(entry)
                    if min_price is None or price < min_price:
                        min_price = price; best_link = link
                    if orig and (min_orig is None or orig < min_orig):
                        min_orig = orig

            if not min_price or not title or not best_link:
                continue

            primary_ean = next(iter(all_eans), None)

            # Cross-shop matching
            product_id = None
            for ean in all_eans:
                if ean in ean_cache:
                    product_id = ean_cache[ean]; break
                cur.execute("SELECT id FROM products WHERE ean = %s", (ean,))
                r = cur.fetchone()
                if r:
                    product_id = r["id"]; ean_cache[ean] = product_id; break

            if not product_id:
                fp = make_fingerprint(brand_name, title, colour)
                if fp in fp_cache:
                    product_id = fp_cache[fp]
                else:
                    cur.execute("SELECT id FROM products WHERE match_fingerprint = %s", (fp,))
                    r = cur.fetchone()
                    if r:
                        product_id = r["id"]; fp_cache[fp] = product_id

            if not product_id:
                brand_slug = slugify(brand_name) if brand_name else "unknown"
                if brand_slug not in brand_cache:
                    try:
                        cur.execute("INSERT INTO brands (name,slug) VALUES (%s,%s) ON CONFLICT (slug) DO UPDATE SET name=EXCLUDED.name RETURNING id",
                                    (brand_name or "Unknown", brand_slug))
                        brand_cache[brand_slug] = cur.fetchone()["id"]
                    except:
                        conn.rollback()
                        cur.execute("SELECT id FROM brands WHERE slug=%s", (brand_slug,))
                        r = cur.fetchone()
                        if r: brand_cache[brand_slug] = r["id"]
                brand_id = brand_cache.get(brand_slug)
                fp = make_fingerprint(brand_name, title, colour)
                product_slug = f"{slugify(f'{brand_name}-{title}')[:180]}-{hashlib.md5(f'aw-{aw_feed_id}-{gkey}'.encode()).hexdigest()[:8]}"

                try:
                    cur.execute("""
                        INSERT INTO products (name,slug,brand_id,brand,image_url,description,ean,
                            colorway,gender,brand_normalized,model_normalized,colorway_normalized,
                            match_fingerprint,category,product_type,
                            model,model_name,needs_cutout,first_seen_at)
                        VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,true,NOW())
                        RETURNING id
                    """, (title, product_slug, brand_id, brand_name, image, description,
                          primary_ean, colour, gender or None,
                          brand_name.lower() if brand_name else None,
                          title.lower() if title else None,
                          colour.lower() if colour else None,
                          fp, ptype, ptype, model, model_name))
                    product_id = cur.fetchone()["id"]
                    new_products += 1
                    fp_cache[fp] = product_id
                    for ean in all_eans: ean_cache[ean] = product_id
                except psycopg2.errors.UniqueViolation:
                    conn.rollback()
                    cur.execute("SELECT id FROM products WHERE slug=%s", (product_slug,))
                    r = cur.fetchone()
                    if r: product_id = r["id"]
                    else: continue
                except Exception as e:
                    conn.rollback()
                    print(f"    Error creating '{title}': {e}"); continue
            else:
                try:
                    cur.execute("""UPDATE products SET model=COALESCE(model,%s), model_name=COALESCE(model_name,%s),
                        category=COALESCE(NULLIF(category,''),%s), product_type=COALESCE(NULLIF(product_type,''),%s),
                        gender=COALESCE(gender,%s)
                        WHERE id=%s""",
                        (model, model_name, ptype, ptype, gender or None, product_id))
                except: conn.rollback()

            ext_id = str(variants[0].get("id","")).strip()
            try:
                cur.execute("""
                    INSERT INTO product_offers (product_id,feed_id,external_id,price,original_price,
                        currency,product_url,image_url,in_stock,sizes,last_seen_at)
                    VALUES (%s,%s,%s,%s,%s,'EUR',%s,%s,true,%s::jsonb,NOW())
                    ON CONFLICT (feed_id,external_id) DO UPDATE SET
                        price=EXCLUDED.price,original_price=EXCLUDED.original_price,
                        product_url=EXCLUDED.product_url,image_url=EXCLUDED.image_url,
                        in_stock=true,sizes=EXCLUDED.sizes,last_seen_at=NOW(),updated_at=NOW()
                """, (product_id, db_feed_id, ext_id, min_price, min_orig,
                      best_link, image, json.dumps(sizes_data, ensure_ascii=False)))
                shop_offers += 1
                ph_batch.append((product_id, db_feed_id, min_price))
            except Exception as e:
                conn.rollback()
                print(f"    Error offer '{title}': {e}"); continue

            if shop_offers % 200 == 0: conn.commit()

        conn.commit()

        ph_count = 0
        for pid, fid, price in ph_batch:
            try:
                cur.execute("""INSERT INTO price_history (product_id,feed_id,price,recorded_at)
                    SELECT %s,%s,%s,NOW() WHERE NOT EXISTS (
                        SELECT 1 FROM price_history WHERE product_id=%s AND feed_id=%s AND price=%s
                        AND recorded_at > NOW() - INTERVAL '23 hours')""",
                    (pid, fid, price, pid, fid, price))
                if cur.rowcount > 0: ph_count += 1
            except: conn.rollback()
        conn.commit()

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

        total_new += new_products; total_offers += shop_offers; total_ph += ph_count
        print(f"  => {new_products} new products, {shop_offers} offers, {ph_count} price records")

'''

code = code[:summary_idx] + awin_loop + code[summary_idx:]

with open('/var/www/sneakerpicks/scripts/feed-sync-v4.py', 'w') as f:
    f.write(code)

print("PATCHED: Added Awin feed support with 4 feeds (JD Sports, Foot Locker, adidas, Schuurman)")
