#!/usr/bin/env python3
"""
SneakerPicks Feed Sync — Webgains
Fetches all products from Webgains feeds and inserts into PostgreSQL.
"""
import requests
import psycopg2
import psycopg2.extras
import re
import hashlib
import time
import sys

# ── 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"

# Use all 4 campaigns to discover feeds
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",
}

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

def get_feeds():
    """Get all unique feeds across all 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_products_for_feed(campaign_id, feed_id, max_products=5000):
    """Fetch products from a single feed. Try pagination, fall back to large batch."""
    url = f"{WEBGAINS_API}/auth/publishers/{PUBLISHER_ID}/campaigns/{campaign_id}/feeds/products"
    all_products = []
    
    # Try fetching in one big batch first (offset may be ignored)
    params = {
        "feeds[]": [feed_id],
        "size": 1000,
        "fields[]": ["title", "image_link", "link", "price", "brand", "description", "id", 
                      "sale_price", "gtin", "mpn", "colour", "size", "availability", "condition"],
    }
    
    try:
        r = requests.get(url, headers=HEADERS, params=params, timeout=60)
        r.raise_for_status()
        data = r.json()
        products = data.get("data", [])
        all_products.extend(products)
        total = data.get("recordsTotal", len(products))
        print(f"    Got {len(products)}/{total} products (batch 1)")
        
        # Try pagination if there are more
        if total > len(all_products) and len(products) > 0:
            for offset in range(len(products), min(total, max_products), 1000):
                params["offset"] = offset
                try:
                    r = requests.get(url, headers=HEADERS, params=params, timeout=60)
                    r.raise_for_status()
                    data = r.json()
                    batch = data.get("data", [])
                    if not batch:
                        break
                    # Check if we're getting duplicates (pagination broken)
                    if batch[0].get("id") == all_products[0].get("id"):
                        print(f"    Pagination returning duplicates, stopping at {len(all_products)}")
                        break
                    all_products.extend(batch)
                    print(f"    Got {len(batch)} more (total: {len(all_products)})")
                    time.sleep(0.5)
                except Exception as e:
                    print(f"    Pagination error at offset {offset}: {e}")
                    break
    except Exception as e:
        print(f"    Fetch error: {e}")
    
    return all_products

def main():
    print("=== SneakerPicks Feed Sync ===")
    print()
    
    # Connect to DB
    print("Connecting to database...")
    conn = psycopg2.connect(DB_URL)
    conn.autocommit = False
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    
    # Get webgains provider_id
    cur.execute("SELECT id FROM providers WHERE slug = 'webgains'")
    provider_row = cur.fetchone()
    if not provider_row:
        print("ERROR: webgains provider not found!")
        return
    provider_id = provider_row["id"]
    print(f"Provider ID: {provider_id}")
    
    # Get feeds from API
    print("\nFetching feeds from Webgains API...")
    feeds = get_feeds()
    print(f"\nFound {len(feeds)} unique feeds")
    
    # Brand cache
    brand_cache = {}
    cur.execute("SELECT id, slug FROM brands")
    for row in cur.fetchall():
        brand_cache[row["slug"]] = row["id"]
    
    total_products = 0
    total_offers = 0
    
    for feed_ext_id, feed_info in sorted(feeds.items()):
        fname = feed_info["name"]
        pcount = feed_info["product_count"]
        camp_id = feed_info["campaign_id"]
        
        print(f"\n--- Feed: {fname} (ext_id={feed_ext_id}, ~{pcount} products) ---")
        
        # Ensure feed exists in DB
        cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s",
                     (provider_id, str(feed_ext_id)))
        row = cur.fetchone()
        if row:
            db_feed_id = row["id"]
        else:
            cur.execute("""
                INSERT INTO feeds (provider_id, external_id, name, merchant_name, product_count)
                VALUES (%s, %s, %s, %s, %s) RETURNING id
            """, (provider_id, str(feed_ext_id), fname, feed_info["program_name"], pcount or 0))
            db_feed_id = cur.fetchone()["id"]
            conn.commit()
        
        # Fetch products
        products = fetch_products_for_feed(camp_id, feed_ext_id)
        if not products:
            print("    No products, skipping")
            continue
        
        feed_new = 0
        feed_offers = 0
        
        for p in products:
            title = (p.get("title") or "").strip()
            brand_name = (p.get("brand") or "").strip()
            image = (p.get("image_link") or "").strip()
            link = (p.get("link") or "").strip()
            price = parse_price(p.get("sale_price") or p.get("price"))
            original_price = parse_price(p.get("price")) if p.get("sale_price") else None
            ext_id = str(p.get("id", "")).strip()
            gtin_val = p.get("gtin") or ""
            if isinstance(gtin_val, list):
                gtin_val = gtin_val[0] if gtin_val else ""
            ean = str(gtin_val).strip() or None
            
            if not title or not link or not price:
                continue
            
            # Brand
            brand_slug = slugify(brand_name) if brand_name else "unknown"
            if brand_slug not in brand_cache:
                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"]
            brand_id = brand_cache[brand_slug]
            
            # Product - find or create
            product_slug = slugify(f"{brand_name}-{title}")[:200]
            # Make slug unique by appending ext_id hash
            slug_hash = hashlib.md5(f"{feed_ext_id}-{ext_id}".encode()).hexdigest()[:6]
            product_slug = f"{product_slug}-{slug_hash}"
            
            # Try EAN match first
            product_id = None
            if ean:
                cur.execute("SELECT id FROM products WHERE ean = %s", (ean,))
                row = cur.fetchone()
                if row:
                    product_id = row["id"]
            
            if not product_id:
                # Insert new product
                try:
                    cur.execute("""
                        INSERT INTO products (name, slug, brand_id, brand, image_url, description, ean, 
                                              brand_normalized, first_seen_at)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
                        ON CONFLICT (slug) DO UPDATE SET updated_at = NOW()
                        RETURNING id
                    """, (title, product_slug, brand_id, brand_name, image, 
                          (p.get("description") or "")[:500], ean,
                          brand_name.lower() if brand_name else None))
                    product_id = cur.fetchone()["id"]
                    feed_new += 1
                except Exception as e:
                    conn.rollback()
                    # Try with more unique slug
                    product_slug = f"{product_slug}-{hashlib.md5(title.encode()).hexdigest()[:8]}"
                    try:
                        cur.execute("""
                            INSERT INTO products (name, slug, brand_id, brand, image_url, description, ean,
                                                  brand_normalized, first_seen_at)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
                            ON CONFLICT (slug) DO UPDATE SET updated_at = NOW()
                            RETURNING id
                        """, (title, product_slug, brand_id, brand_name, image,
                              (p.get("description") or "")[:500], ean,
                              brand_name.lower() if brand_name else None))
                        product_id = cur.fetchone()["id"]
                        feed_new += 1
                    except Exception as e2:
                        print(f"    Skip product '{title[:40]}': {e2}")
                        conn.rollback()
                        continue
            
            # Insert offer
            try:
                cur.execute("""
                    INSERT INTO product_offers (product_id, feed_id, external_id, price, original_price, 
                                                 currency, product_url, image_url, in_stock, last_seen_at)
                    VALUES (%s, %s, %s, %s, %s, 'EUR', %s, %s, true, 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,
                        last_seen_at = NOW(),
                        updated_at = NOW()
                """, (product_id, db_feed_id, ext_id, price, original_price, link, image))
                feed_offers += 1
            except Exception as e:
                print(f"    Skip offer '{title[:40]}': {e}")
                conn.rollback()
                continue
        
        # Commit per feed
        conn.commit()
        
        # Update feed stats
        cur.execute("""
            UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success'
            WHERE id = %s
        """, (feed_offers, db_feed_id))
        conn.commit()
        
        total_products += feed_new
        total_offers += feed_offers
        print(f"    ✓ {feed_new} new products, {feed_offers} offers inserted")
    
    print(f"\n=== DONE ===")
    print(f"Total new products: {total_products}")
    print(f"Total offers: {total_offers}")
    
    # Final counts
    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 brands")
    print(f"Brands in DB: {cur.fetchone()[0]}")
    cur.execute("SELECT COUNT(*) FROM feeds")
    print(f"Feeds in DB: {cur.fetchone()[0]}")
    
    cur.close()
    conn.close()

if __name__ == "__main__":
    main()
