#!/usr/bin/env python3
"""Fix size dedup issues in feed-sync-v4.py:
1. Strip "- Maat XX - Material" from Foot Locker names
2. Prevent multiple offers per product per feed (merge sizes)
3. Better name normalization
"""

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

# 1. Fix normalize_product_name to strip "- Maat XX" patterns from middle/end
old_norm = '''def normalize_product_name(title, brand):
    name = re.sub(r'\\s+(?:eu|eur|maat|size)\\s*\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', title.strip(), flags=re.I)
    name = re.sub(r'\\s+\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name)
    # Remove size like "- US3.0" or "- EU 42" from end
    name = re.sub(r'\\s*[-/]\\s*(?:US|EU)\\s*\\d+(?:\\.\\d+)?\\s*$', '', name, flags=re.I)
    return name.strip(' -')'''

new_norm = '''def normalize_product_name(title, brand):
    name = title.strip()
    # Strip "- Maat XX" or "- Maat XX 2/3" from anywhere (Foot Locker pattern)
    name = re.sub(r'\\s*-\\s*Maat\\s+\\d+(?:\\s+\\d+/\\d+|[.,]\\d+)?\\s*', ' ', name, flags=re.I)
    # Strip "- Material" suffix like "- Leer", "- Mesh/Synthetisch", "- Textil"
    name = re.sub(r'\\s*-\\s*(?:Leer|Mesh[/\\w]*|Textil|Synthetisch|Suede|Canvas|Nubuck|Rubber)\\s*$', '', name, flags=re.I)
    # Strip "EU XX", "Maat XX", "Size XX" from end
    name = re.sub(r'\\s+(?:eu|eur|maat|size)\\s*\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name, flags=re.I)
    # Strip bare size number from end
    name = re.sub(r'\\s+\\d{2}(?:[.,]\\d{1,2})?\\s*$', '', name)
    # Strip "- US3.0,EU42.3,UK9.0" (Patta pattern)
    name = re.sub(r'\\s*-\\s*US\\d+[.,]\\d+.*$', '', name, flags=re.I)
    # Strip "- US3.0" or "- EU 42" from end
    name = re.sub(r'\\s*[-/]\\s*(?:US|EU)\\s*\\d+(?:\\.\\d+)?\\s*$', '', name, flags=re.I)
    # Clean up double spaces and trailing dashes
    name = re.sub(r'\\s+', ' ', name).strip(' -')
    return name'''

if old_norm in code:
    code = code.replace(old_norm, new_norm)
    print("PATCHED: normalize_product_name")
else:
    print("WARNING: Could not find normalize_product_name to patch")

# 2. Add a dedup step after all offers are created: merge offers with same product_id+feed_id
# We'll add this as a post-processing function and call it at the end of each shop's processing

dedup_func = '''
def merge_duplicate_offers(cur, conn, db_feed_id, shop_name):
    """Merge offers that point to the same product from the same feed.
    This happens when different group keys resolve to the same product via EAN/fingerprint.
    Keeps the offer with the most sizes, merges sizes from others, then deletes dupes."""
    cur.execute("""
        SELECT product_id, array_agg(id ORDER BY jsonb_array_length(sizes) DESC) as offer_ids,
               COUNT(*) as cnt
        FROM product_offers WHERE feed_id = %s
        GROUP BY product_id HAVING COUNT(*) > 1
    """, (db_feed_id,))
    
    dupes = cur.fetchall()
    if not dupes:
        return 0
    
    merged = 0
    for row in dupes:
        product_id = row['product_id']
        offer_ids = row['offer_ids']
        keep_id = offer_ids[0]  # the one with most sizes
        delete_ids = offer_ids[1:]
        
        # Collect all sizes from all offers
        cur.execute("""
            SELECT sizes, price, original_price FROM product_offers 
            WHERE id = ANY(%s) ORDER BY jsonb_array_length(sizes) DESC
        """, (offer_ids,))
        
        all_sizes = []
        seen_size_keys = set()
        best_price = None
        best_orig = None
        
        for offer_row in cur.fetchall():
            import json
            sizes = offer_row['sizes'] if isinstance(offer_row['sizes'], list) else json.loads(offer_row['sizes'] or '[]')
            price = offer_row['price']
            orig = offer_row['original_price']
            
            if best_price is None or (price and price < best_price):
                best_price = price
            if orig and (best_orig is None or orig < best_orig):
                best_orig = orig
                
            for s in sizes:
                size_key = s.get('size', '') + '|' + s.get('ean', '')
                if size_key not in seen_size_keys:
                    seen_size_keys.add(size_key)
                    all_sizes.append(s)
        
        # Update the keeper with merged sizes
        import json
        cur.execute("""
            UPDATE product_offers SET sizes = %s::jsonb, price = %s, original_price = %s
            WHERE id = %s
        """, (json.dumps(all_sizes, ensure_ascii=False), best_price, best_orig, keep_id))
        
        # Delete the duplicates
        cur.execute("DELETE FROM product_offers WHERE id = ANY(%s)", (delete_ids,))
        merged += len(delete_ids)
    
    conn.commit()
    if merged > 0:
        print(f"  Merged {merged} duplicate offers for {len(dupes)} products")
    return merged
'''

# Insert the function before "# ── Main ──"
main_marker = "\n# ── Main ──"
main_idx = code.find(main_marker)
if main_idx >= 0:
    code = code[:main_idx] + dedup_func + code[main_idx:]
    print("PATCHED: Added merge_duplicate_offers function")
else:
    print("WARNING: Could not find Main section")

# 3. Call merge_duplicate_offers after each shop's offers are committed
# For Webgains shops
old_wg_end = '''        total_new += new_products; total_offers += shop_offers; total_ph += ph_count
        print(f"  => {new_products} new products, {shop_offers} offers, {ph_count} price records")

    # ── Awin Feeds ──'''

new_wg_end = '''        # Merge duplicate offers (same product, same feed)
        merged = merge_duplicate_offers(cur, conn, db_feed_id, shop_name)
        shop_offers -= merged

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

    # ── Awin Feeds ──'''

if old_wg_end in code:
    code = code.replace(old_wg_end, new_wg_end)
    print("PATCHED: Added merge call for Webgains shops")
else:
    print("WARNING: Could not find Webgains end section to patch")

# For Awin shops - find the similar pattern
old_aw_end = '''        total_new += new_products; total_offers += shop_offers; total_ph += ph_count
        print(f"  => {new_products} new products, {shop_offers} offers, {ph_count} price records")

    # Summary'''

new_aw_end = '''        # Merge duplicate offers (same product, same feed)
        merged = merge_duplicate_offers(cur, conn, db_feed_id, shop_name)
        shop_offers -= merged

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

    # Summary'''

if old_aw_end in code:
    code = code.replace(old_aw_end, new_aw_end)
    print("PATCHED: Added merge call for Awin shops")
else:
    print("WARNING: Could not find Awin end section to patch")

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

print("\nAll patches applied!")
