#!/usr/bin/env python3
"""Import Patta feed (25578) into SneakerPicks — additive, no truncation."""
import requests, psycopg2, psycopg2.extras, re, hashlib, time, sys

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"

FEED_ID = 25578
CAMPAIGN_ID = "1622230"  # any campaign works

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 keywords for filtering
SNEAKER_KEYWORDS = [
    'sneaker', 'sneakers', 'trainer', 'trainers', 'running shoe',
    'air max', 'air force', 'jordan', 'dunk', 'yeezy', 'new balance',
    'converse', 'chuck taylor', 'vans old skool', 'vans sk8',
    'adidas originals', 'stan smith', 'superstar', 'forum',
    'puma suede', 'puma rs', 'reebok classic', 'club c',
    'asics gel', 'saucony', 'on cloud', 'hoka',
    'nb 550', 'nb 990', 'nb 574', 'nb 2002',
]

SHOE_CATEGORIES = ['shoes', 'footwear', 'sneakers', 'schoenen', 'sportschoenen']


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)
    return float(match.group()) if match else None


def is_sneaker(p):
    """Filter: is this product a sneaker?"""
    title = (p.get("title") or "").lower() if isinstance(p.get("title"), str) else ""
    pt_raw = p.get("product_type") or ""
    ptype = " ".join(pt_raw) if isinstance(pt_raw, list) else str(pt_raw)
    ptype = ptype.lower()
    gc_raw = p.get("google_product_category") or ""
    gcat = " ".join(gc_raw) if isinstance(gc_raw, list) else str(gc_raw)
    gcat = gcat.lower()
    desc = (p.get("description") or "").lower() if isinstance(p.get("description"), str) else ""
    combined = f"{title} {ptype} {gcat} {desc}"
    
    # Check category signals
    for cat in SHOE_CATEGORIES:
        if cat in ptype or cat in gcat:
            return True
    
    # Check keywords
    for kw in SNEAKER_KEYWORDS:
        if kw in combined:
            return True
    
    return False


def fetch_all_products(max_pages=200):
    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
        
        r = requests.get(url, headers=HEADERS, params=params, timeout=60)
        r.raise_for_status()
        data = r.json()
        
        products = data.get("data", [])
        if not products:
            break
        
        pagination = data.get("pagination", {})
        new_cursor = pagination.get("search_cursor")
        
        new = [p for p in products if p.get("id") and p["id"] not in seen_ids]
        for p in new:
            seen_ids.add(p["id"])
        all_products.extend(new)
        
        if page % 5 == 0:
            print(f"  Page {page}: total {len(all_products)}")
        
        if new_cursor == cursor or not new_cursor or len(new) < 900:
            break
        cursor = new_cursor
        time.sleep(0.3)
    
    return all_products


def main():
    print("=== Patta Feed Import ===\n")
    
    # Fetch products
    print("Fetching Patta feed products...")
    all_products = fetch_all_products()
    print(f"Total in feed: {len(all_products)}")
    
    # Filter sneakers
    sneakers = [p for p in all_products if is_sneaker(p)]
    print(f"Sneakers after filter: {len(sneakers)}")
    
    # If very few sneakers, Patta is a sneaker store so import all footwear-like items
    # Actually let's be less strict — Patta is primarily a sneaker/streetwear store
    # Import all products that have a price and look like shoes
    # Let's check what product_types exist
    ptypes = {}
    for p in all_products:
        pt_raw = p.get("product_type") or "unknown"
        pt = " > ".join(pt_raw).strip() if isinstance(pt_raw, list) else str(pt_raw).strip()
        ptypes[pt] = ptypes.get(pt, 0) + 1
    
    print("\nProduct types:")
    for pt, c in sorted(ptypes.items(), key=lambda x: -x[1])[:30]:
        print(f"  {pt}: {c}")
    
    # Also filter by product_type containing Footwear or sneakers
    footwear = []
    for p in all_products:
        pt_raw = p.get("product_type") or ""
        ptype = " ".join(pt_raw).lower() if isinstance(pt_raw, list) else str(pt_raw).lower()
        if "footwear" in ptype or "sneaker" in ptype or "shoe" in ptype:
            footwear.append(p)
    print(f"Footwear by product_type: {len(footwear)}")
    
    # Use footwear filter (more precise than keyword matching)
    if len(footwear) >= 100:
        sneakers = footwear
    
    print(f"\nImporting {len(sneakers)} products...")
    
    conn = psycopg2.connect(DB_URL)
    conn.autocommit = False
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    
    # Get provider
    cur.execute("SELECT id FROM providers WHERE slug = 'webgains'")
    provider_id = cur.fetchone()["id"]
    
    # Check if feed already exists
    cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s", (provider_id, str(FEED_ID)))
    existing = cur.fetchone()
    if existing:
        db_feed_id_tmp = existing["id"]
        # Delete old offers for this feed to reimport cleanly
        cur.execute("DELETE FROM product_offers WHERE feed_id = %s", (db_feed_id_tmp,))
        cur.execute("DELETE FROM feeds WHERE id = %s", (db_feed_id_tmp,))
        conn.commit()
    
    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_ID), "Patta", "Patta", len(sneakers)))
    db_feed_id = cur.fetchone()["id"]
    conn.commit()
    
    # Brand cache
    brand_cache = {}
    cur.execute("SELECT id, slug FROM brands")
    for row in cur.fetchall():
        brand_cache[row["slug"]] = row["id"]
    
    # EAN cache from existing products
    ean_cache = {}
    cur.execute("SELECT id, ean FROM products WHERE ean IS NOT NULL AND ean != ''")
    for row in cur.fetchall():
        ean_cache[row["ean"]] = row["id"]
    
    new_products = 0
    new_offers = 0
    ean_matches = 0
    
    for i, p in enumerate(sneakers):
        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
        colour = (p.get("colour") or "").strip()
        
        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:
            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"]
                conn.commit()
            except:
                conn.rollback()
                cur.execute("SELECT id FROM brands WHERE slug = %s", (brand_slug,))
                row = cur.fetchone()
                brand_cache[brand_slug] = row["id"] if row else None
                if not brand_cache[brand_slug]:
                    continue
        brand_id = brand_cache[brand_slug]
        
        # Check EAN match with existing products
        product_id = None
        if ean and ean in ean_cache:
            product_id = ean_cache[ean]
            ean_matches += 1
        
        if not product_id:
            base_slug = slugify(f"{brand_name}-{title}")[:180]
            slug_hash = hashlib.md5(f"{FEED_ID}-{ext_id}".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, first_seen_at)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                    ON CONFLICT (slug) DO NOTHING RETURNING id
                """, (title, product_slug, brand_id, brand_name, image,
                      (p.get("description") or "")[:500], ean, colour,
                      brand_name.lower() if brand_name else None))
                row = cur.fetchone()
                if row:
                    product_id = row["id"]
                    new_products += 1
                    if ean:
                        ean_cache[ean] = product_id
                else:
                    cur.execute("SELECT id FROM products WHERE slug = %s", (product_slug,))
                    row = cur.fetchone()
                    product_id = row["id"] if row else None
                conn.commit()
            except Exception as e:
                conn.rollback()
                continue
        
        if not product_id:
            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, in_stock = true, last_seen_at = NOW()
            """, (product_id, db_feed_id, ext_id, price, original_price, link, image))
            new_offers += 1
            conn.commit()
        except Exception as e:
            conn.rollback()
            continue
        
        if (i + 1) % 500 == 0:
            print(f"  Processed {i+1}/{len(sneakers)}")
    
    # Update feed
    cur.execute("UPDATE feeds SET product_count = %s, last_sync_at = NOW(), last_sync_status = 'success' WHERE id = %s",
                (new_offers, db_feed_id))
    conn.commit()
    
    print(f"\n=== RESULTS ===")
    print(f"Total in feed: {len(all_products)}")
    print(f"Sneakers filtered: {len(sneakers)}")
    print(f"EAN matches (dedup): {ean_matches}")
    print(f"New products: {new_products}")
    print(f"Offers created: {new_offers}")
    
    # DB totals
    cur.execute("SELECT COUNT(*) FROM products")
    print(f"\nDB Total products: {cur.fetchone()[0]}")
    cur.execute("SELECT COUNT(*) FROM product_offers")
    print(f"DB Total offers: {cur.fetchone()[0]}")
    cur.execute("SELECT f.name, COUNT(po.id) FROM feeds f LEFT JOIN product_offers po ON po.feed_id = f.id GROUP BY f.name ORDER BY COUNT(po.id) DESC")
    print("\nOffers per feed:")
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]}")
    
    cur.close()
    conn.close()
    print("\nDone!")


if __name__ == "__main__":
    main()
