#!/usr/bin/env python3
"""
SneakerPicks — Import specific Webgains feeds one by one.
Uses feedIds[] param + search_cursor pagination.
Filters for sneakers only, excludes clothing/accessories.
"""
import requests
import psycopg2
import psycopg2.extras
import re
import hashlib
import time
import sys
import json

# ── 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"
CAMPAIGN_ID = "1622230"

DB_DSN = "host=172.18.0.5 port=5432 dbname=sneakerpicks user=sneakerpicks password=SP.r4nD0m-sEcuRe99"

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

# ── Sneaker filter ──
EXCLUDE_TITLE_WORDS = [
    't-shirt', 'tshirt', 'tee ', 'hoodie', 'hoody', 'sweater', 'sweatshirt',
    'jacket', 'coat', 'pants', 'trouser', 'jogger', 'shorts', 'jeans',
    'shirt', 'polo shirt', 'vest', 'cap ', 'hat ', 'beanie', 'scarf', 'glove',
    'sock ', 'socks', 'backpack', 'bag ', 'wallet', 'belt ', 'watch ', 'sunglasses',
    'underwear', 'boxer', 'brief', 'bikini', 'swim', 'towel',
    'perfume', 'fragrance', 'deodorant', 'candle', 'keychain', 'keyring',
    'phone case', 'airpod', 'insole', 'laces',
    'cleaner', 'protector', 'spray', 'brush',
    'tracksuit', 'track pant', 'windbreaker', 'puffer',
    'fleece', 'cardigan', 'crewneck', 'crew neck',
    'longsleeve', 'long sleeve', 'tank top', 'legging',
    'skirt', 'dress ', 'jumpsuit', 'blanket', 'pillow',
    'duvet', 'cushion', 'rug ', 'mat ', 'poster', 'print ',
    'mug ', 'cup ', 'bottle', 'incense', 'book ', 'magazine',
    'sticker', 'pin ', 'patch', 'lanyard', 'tote',
]

FOOTWEAR_CATEGORIES = ['footwear', 'shoe', 'sneaker', 'schoen', 'trainer']
NON_FOOTWEAR_CATEGORIES = ['clothing', 'apparel', 'accessori', 'bag', 'hat', 'cap',
                            'lifestyle', 'home', 'fragrance', 'art', 'book']


def is_sneaker(product):
    """Determine if product is footwear."""
    title = (product.get('title') or '').lower()
    cat_raw = product.get('product_type') or product.get('category') or ''
    if isinstance(cat_raw, list):
        category = ' '.join(str(x) for x in cat_raw).lower()
    else:
        category = str(cat_raw).lower()

    # Category-based (most reliable)
    if category:
        if any(w in category for w in FOOTWEAR_CATEGORIES):
            return True
        if any(w in category for w in NON_FOOTWEAR_CATEGORIES):
            return False

    # Title-based exclusion
    for word in EXCLUDE_TITLE_WORDS:
        if word in title:
            return False

    # If no category info and no exclusion matched, check title for shoe indicators
    shoe_words = ['sneaker', 'shoe', 'trainer', 'runner', 'boot', 'slide', 'sandal',
                  'slipper', 'mule', 'clog', 'foam', 'dunk', 'force 1', 'air max',
                  'jordan', 'yeezy', 'ultraboost', 'superstar', 'stan smith',
                  'old skool', 'gel-', '990', '550', '2002r', '327', '574',
                  'speedcat', 'spezial']
    for word in shoe_words:
        if word in title:
            return True

    # Unknown — exclude to be safe
    return False


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 fetch_all_products(feed_id):
    """Fetch ALL products from a feed using cursor 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(20):  # Max 20 pages = 20k products
        params = {"feedIds[]": [feed_id], "size": 1000}
        if cursor:
            params["search_cursor"] = cursor

        try:
            r = requests.get(url, headers=HEADERS, params=params, timeout=60)
            r.raise_for_status()
            data = r.json()
            batch = data.get("data", [])
            pag = data.get("pagination", {})
            total = pag.get("total", "?")
            cursor = pag.get("search_cursor")

            if not batch:
                break

            # Deduplicate
            new_count = 0
            for p in batch:
                pid = str(p.get("id", ""))
                if pid not in seen_ids:
                    seen_ids.add(pid)
                    all_products.append(p)
                    new_count += 1

            print(f"  Page {page+1}: {len(batch)} fetched, {new_count} new, total unique: {len(all_products)}/{total}")

            if len(batch) < 1000 or new_count == 0:
                break

            time.sleep(0.3)
        except Exception as e:
            print(f"  ERROR page {page+1}: {e}")
            break

    return all_products


def import_feed(feed_id, merchant_name, feed_name):
    """Import a single feed into the database."""
    print(f"\n{'='*60}")
    print(f"IMPORTING: {merchant_name} — Feed {feed_id} ({feed_name})")
    print(f"{'='*60}")

    # 1. Fetch all products
    print(f"\n1. Fetching products...")
    products = fetch_all_products(feed_id)
    print(f"   Total unique products: {len(products)}")

    if not products:
        print("   ❌ No products!")
        return

    # 2. Filter sneakers
    print(f"\n2. Filtering sneakers...")
    sneakers = [p for p in products if is_sneaker(p)]
    excluded = [p for p in products if not is_sneaker(p)]

    print(f"   ✅ Sneakers: {len(sneakers)}")
    print(f"   ✗ Excluded:  {len(excluded)}")

    # Show category breakdown
    cats = {}
    for p in products:
        c = p.get('product_type') or ['Unknown']
        if isinstance(c, list):
            c = c[0] if c else 'Unknown'
        c = str(c).strip()[:40]
        cats[c] = cats.get(c, 0) + 1
    print(f"   Categories: {sorted(cats.items(), key=lambda x: -x[1])[:8]}")

    if excluded:
        print(f"\n   Sample excluded (first 15):")
        for p in excluded[:15]:
            print(f"   ✗ {p.get('brand','?'):20s} | {p.get('title','?')[:55]}")

    if not sneakers:
        print("   ❌ No sneakers after filtering!")
        return

    # Show sample sneakers
    print(f"\n   Sample sneakers (first 10):")
    for p in sneakers[:10]:
        price = parse_price(p.get('sale_price') or p.get('price'))
        print(f"   ✓ {p.get('brand','?'):20s} | €{price or '?':>7} | {p.get('title','?')[:50]}")

    # 3. Import to DB
    print(f"\n3. Importing {len(sneakers)} sneakers...")
    conn = psycopg2.connect(DB_DSN)
    conn.autocommit = False
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

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

    # Feed record
    cur.execute("SELECT id FROM feeds WHERE provider_id = %s AND external_id = %s",
                (provider_id, str(feed_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, 0) RETURNING id
        """, (provider_id, str(feed_id), feed_name, merchant_name))
        db_feed_id = cur.fetchone()["id"]
        conn.commit()

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

    imported = 0
    skipped = 0

    for p in sneakers:
        title = (p.get("title") or "").strip().rstrip('|').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
        size_val = (p.get("size") or "").strip()

        if not title or not link or not price:
            skipped += 1
            continue

        # Clean title: remove trailing pipe, size info already in separate field
        title = re.sub(r'\s*\|\s*$', '', title)

        # 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 slug
        slug_base = slugify(f"{brand_name}-{title}")[:180]
        slug_hash = hashlib.md5(f"{feed_id}-{ext_id}".encode()).hexdigest()[:8]
        product_slug = f"{slug_base}-{slug_hash}"

        # Find by EAN or create
        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:
            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"]
            except Exception as e:
                conn.rollback()
                skipped += 1
                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))
            imported += 1
        except Exception as e:
            conn.rollback()
            skipped += 1
            continue

    conn.commit()

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

    # Brand stats for this import
    cur.execute("""
        SELECT b.name, COUNT(*) as cnt
        FROM products p JOIN brands b ON p.brand_id = b.id
        JOIN product_offers po ON po.product_id = p.id
        WHERE po.feed_id = %s
        GROUP BY b.name ORDER BY cnt DESC LIMIT 10
    """, (db_feed_id,))
    brand_stats = cur.fetchall()

    print(f"\n{'='*60}")
    print(f"RESULTS: {merchant_name}")
    print(f"{'='*60}")
    print(f"  Total in feed:     {len(products)}")
    print(f"  After filter:      {len(sneakers)}")
    print(f"  Imported:          {imported}")
    print(f"  Skipped:           {skipped}")
    print(f"  Top brands:")
    for row in brand_stats:
        print(f"    {row['name']:20s} {row['cnt']}")

    cur.execute("SELECT COUNT(*) FROM products")
    print(f"\n  DB totals: {cur.fetchone()[0]} products", end="")
    cur.execute("SELECT COUNT(*) FROM product_offers")
    print(f", {cur.fetchone()[0]} offers", end="")
    cur.execute("SELECT COUNT(*) FROM feeds")
    print(f", {cur.fetchone()[0]} feeds")

    cur.close()
    conn.close()


if __name__ == "__main__":
    if len(sys.argv) < 4:
        print("Usage: python import-feeds.py <feed_id> <merchant_name> <feed_name>")
        sys.exit(1)
    import_feed(int(sys.argv[1]), sys.argv[2], sys.argv[3])
