app/api/seed.py

from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import text
import json
import random
import os
import hashlib
import requests

from app.db.base import get_db
from app.models.product import Product, ProductImage, Category
from app.models.cart import Coupon
from app.models.user import User, UserRole, AuthProvider
from app.core.security import hash_password
from app.schemas.auth import AuthResponse
from app.services.external_catalog import ExternalCatalogService

router = APIRouter(prefix="/seed", tags=["Seed Data"])

# ============================================================
# Category Hierarchy — matching Extra_Scraper_Kaggle.py exactly
# ============================================================
SUB_ICONS = {
    "جوالات": "📲",
    "لابتوب": "💻",
    "أجهزة لوحية": "📟",
    "تلفزيونات": "📺",
    "كاميرات": "📷",
    "طابعات": "🖨️",
    "أجهزة الصوت والسماعات": "🎧",
    "ساعات ذكية": "⌚",
    "أجهزة ألعاب": "🎮",
    "ثلاجات": "❄️",
    "غسالات ومجففات": "🫧",
    "مكيفات": "🌀",
    "أجهزة منزلية صغيرة": "🍳",
    "أجهزة منزلية كبيرة": "🏗️",
}

CATEGORY_HIERARCHY = {
    "الأجهزة الإلكترونية": {
        "name_en": "Electronics",
        "icon": "📱",
        "subs": [
            {"ar": "جوالات", "en": "Smartphones", "icon": "📲"},
            {"ar": "لابتوب", "en": "Laptops", "icon": "💻"},
            {"ar": "أجهزة لوحية", "en": "Tablets", "icon": "📟"},
            {"ar": "تلفزيونات", "en": "TVs", "icon": "📺"},
            {"ar": "كاميرات", "en": "Cameras", "icon": "📷"},
            {"ar": "طابعات", "en": "Printers", "icon": "🖨️"},
        ]
    },
    "ملحقات واكسسوارات": {
        "name_en": "Accessories",
        "icon": "🔌",
        "subs": [
            {"ar": "أجهزة الصوت والسماعات", "en": "Audio & Headphones", "icon": "🎧"},
            {"ar": "ساعات ذكية", "en": "Smartwatches", "icon": "⌚"},
        ]
    },
    "ألعاب جيمنج": {
        "name_en": "Gaming",
        "icon": "🎮",
        "subs": [
            {"ar": "أجهزة ألعاب", "en": "Gaming Consoles", "icon": "🎮"},
        ]
    },
    "الأجهزة المنزلية": {
        "name_en": "Home Appliances",
        "icon": "🏠",
        "subs": [
            {"ar": "ثلاجات", "en": "Refrigerators", "icon": "❄️"},
            {"ar": "غسالات ومجففات", "en": "Washing Machines", "icon": "🫧"},
            {"ar": "مكيفات", "en": "Air Conditioners", "icon": "🌀"},
            {"ar": "أجهزة منزلية صغيرة", "en": "Small Appliances", "icon": "🍳"},
            {"ar": "أجهزة منزلية كبيرة", "en": "Large Appliances", "icon": "🏗️"},
        ]
    }
}

# Reverse map for scraped data → parent category
SCRAPED_TO_TARGET_MAP = {
    "Electronics": "الأجهزة الإلكترونية",
    "Smartphones": "الأجهزة الإلكترونية",
    "Laptops": "الأجهزة الإلكترونية",
    "Tablets": "الأجهزة الإلكترونية",
    "TVs": "الأجهزة الإلكترونية",
    "Cameras": "الأجهزة الإلكترونية",
    "Printers": "الأجهزة الإلكترونية",
    "Accessories": "ملحقات واكسسوارات",
    "Audio & Headphones": "ملحقات واكسسوارات",
    "Smartwatches": "ملحقات واكسسوارات",
    "Gaming": "ألعاب جيمنج",
    "Gaming Consoles": "ألعاب جيمنج",
    "Home Appliances": "الأجهزة المنزلية",
    "Refrigerators": "الأجهزة المنزلية",
    "Washing Machines": "الأجهزة المنزلية",
    "Air Conditioners": "الأجهزة المنزلية",
    "Small Appliances": "الأجهزة المنزلية",
    "Large Appliances": "الأجهزة المنزلية",
    "Security": "ملحقات واكسسوارات",
    "Car": "ملحقات واكسسوارات",
    "Audio": "ملحقات واكسسوارات",
    "Personal Care": "الأجهزة المنزلية",
}

@router.api_route("", methods=["GET", "POST"], response_model=AuthResponse)
def seed_database(db: Session = Depends(get_db), is_background_task: bool = False):
    """Populate database with proper product data. Only adds new products, does not clear existing data."""
    if is_background_task:
        from app.core.state import import_progress
    # 0. Ensure Store Settings exist
    from app.models.settings import StoreSettings
    settings = db.query(StoreSettings).first()
    if not settings:
        print(">>> [SEED] Creating default store settings...")
        settings = StoreSettings(
            store_name="أفق",
            primary_color="#046c4e",
            secondary_color="#d97706",
            logo_url="/logo.png"
        )
        db.add(settings)
        db.commit()

    # 0. Create Default Admin if matches criteria
    admin_email = "admin@vortex.com"
    admin_user = db.query(User).filter(User.email == admin_email).first()
    if not admin_user:
        print(f">>> [SEED] Creating default admin: {admin_email}")
        admin_user = User(
            email=admin_email,
            name="Platform Administrator",
            password_hash=hash_password("admin123"),
            role=UserRole.ADMIN,
            auth_provider=AuthProvider.LOCAL
        )
        db.add(admin_user)
        db.commit()

    # 1. Create Categories (Hierarchy)
    all_sub_cats = {} # Map used for product assignment
    
    for p_ar, info in CATEGORY_HIERARCHY.items():
        # Create Parent
        parent_cat = db.query(Category).filter(Category.name_ar == p_ar).first()
        if not parent_cat:
            parent_cat = Category(
                name_ar=p_ar,
                name_en=info["name_en"],
                icon=info["icon"]
            )
            db.add(parent_cat)
            db.commit()
            db.refresh(parent_cat)
        
        # Add parent to map so products can be assigned directly to it
        all_sub_cats[p_ar] = parent_cat
        all_sub_cats[info["name_en"]] = parent_cat
            
        # Create Subs
        for s in info["subs"]:
            sub_cat = db.query(Category).filter(Category.name_ar == s["ar"]).first()
            if not sub_cat:
                sub_cat = Category(
                    name_ar=s["ar"],
                    name_en=s["en"],
                    icon=s.get("icon", SUB_ICONS.get(s["ar"], "📦")),
                    parent_id=parent_cat.id
                )
                db.add(sub_cat)
                db.commit()
                db.refresh(sub_cat)
            all_sub_cats[s["ar"]] = sub_cat
            all_sub_cats[s["en"]] = sub_cat # Map both names to the same object


    API_VERSION = "2026.03.16.V2"
    
    possible_paths = [
        # Primary Target (Fixed location inside backend so upload_to_hf works)
        os.path.normpath(os.path.join(os.path.dirname(__file__), "..", "data", "extra_products_vortex.json")),
        os.path.abspath("app/data/extra_products_vortex.json"),
        
        # Standard Hugging Face / Docker location (WORKDIR /app)
        "/app/app/data/extra_products_vortex.json",
        "/app/data/extra_products_vortex.json",

        # Fallbacks (Old paths just in case)
        r"c:\react_projects\VortexCommerce\kaggle\extra_products_vortex.json",
        os.path.normpath(os.path.join(os.path.dirname(__file__), "..", "..", "kaggle", "extra_products_vortex.json")),
        os.path.abspath("kaggle/extra_products_vortex.json"),
        os.path.abspath("extra_products_vortex.json"),
    ]
    
    json_path = None
    checked_info = []
    for p in possible_paths:
        exists = os.path.exists(p)
        checked_info.append(f"{p}: {'FOUND' if exists else 'NOT FOUND'}")
        if exists:
            json_path = p
            break
            
    if not json_path:
        return AuthResponse(
            isSuccess=False,
            value={
                "message": "Source file not found.", 
                "checked": checked_info,
                "version": API_VERSION
            },
            statusCode=404,
            error="File not found"
        )
        
    try:
        with open(json_path, "r", encoding="utf-8") as f:
            data = json.load(f)
            
        if isinstance(data, dict) and "products" in data:
            products_data = data["products"]
        elif isinstance(data, list):
            products_data = data
        else:
            return AuthResponse(isSuccess=False, value={"message": "Invalid JSON format"}, statusCode=400)

        # Cache existing products
        existing_products_ar = set(r[0] for r in db.execute(text("SELECT name_ar FROM products")).fetchall())
        existing_products_en = set(r[0] for r in db.execute(text("SELECT name_en FROM products")).fetchall())

        # 3. Create Products
        inserted_count: int = 0
        skipped_count: int = 0
        
        total_items = len(products_data)
        if is_background_task:
            import_progress.total = total_items
            import_progress.current = 0
        
        for idx, item in enumerate(products_data):
            if is_background_task and idx % 25 == 0:
                import_progress.current = idx
                import_progress.message = f"Processing item {idx} of {total_items}..."

            if not isinstance(item, dict):
                skipped_count += 1
                continue

            product_name_ar = item.get("name_ar", "").strip()
            product_name_en = item.get("name_en", "").strip()

            # 1. Resolve Category (Hierarchical)
            main_cat_ar = item.get("main_category")
            main_cat_en = item.get("main_category_en")
            sub_cat_ar = item.get("category")
            sub_cat_en = item.get("category_en")
            cat_icon = item.get("category_icon", "📦")

            target_sub_cat = None

            if main_cat_ar and sub_cat_ar:
                # Ensure Parent exists
                parent = db.query(Category).filter(Category.name_ar == main_cat_ar).first()
                if not parent:
                    parent = Category(
                        name_ar=main_cat_ar,
                        name_en=main_cat_en or main_cat_ar,
                        icon=cat_icon
                    )
                    db.add(parent)
                    db.commit()
                    db.refresh(parent)
                
                # Ensure Sub exists
                sub = db.query(Category).filter(
                    Category.name_ar == sub_cat_ar,
                    Category.parent_id == parent.id
                ).first()
                if not sub:
                    sub = Category(
                        name_ar=sub_cat_ar,
                        name_en=sub_cat_en or sub_cat_ar,
                        icon=cat_icon,
                        parent_id=parent.id
                    )
                    db.add(sub)
                    db.commit()
                    db.refresh(sub)
                target_sub_cat = sub
            else:
                # Fallback for old flat format or missing data
                cat_name = item.get("category_name") or item.get("category")
                if cat_name in SCRAPED_TO_TARGET_MAP:
                    cat_name = SCRAPED_TO_TARGET_MAP[cat_name]
                
                if not cat_name or cat_name not in all_sub_cats:
                    cat_name = "الأجهزة الإلكترونية"
                target_sub_cat = all_sub_cats.get(cat_name)

            if not target_sub_cat:
                skipped_count += 1
                continue
            
            if product_name_ar in existing_products_ar or product_name_en in existing_products_en:
                skipped_count += 1
                continue
            
            specs = item.get("specs", {})
            if not isinstance(specs, dict): specs = {}

            # Handle Variants
            if "variants" not in specs and "options" in item:
                options = item.get("options", [])
                if isinstance(options, list):
                    variants = []
                    for opt in options:
                        if not isinstance(opt, dict): continue
                        variants.append({
                            "id": opt.get("sku", opt.get("id", "v_default")),
                            "name_en": opt.get("name_en", ""),
                            "name_ar": opt.get("name_ar", ""),
                            "price_modifier": float(opt.get("price", 0)) - float(item.get("price", 0)) if "price" in opt else 0,
                            "stock": opt.get("stock", 10),
                            "image_url": opt.get("image", opt.get("image_url", ""))
                        })
                    if variants: specs["variants"] = variants

            import time
            fallback_slug = f"vortex-{int(time.time()*1000)}-{idx}"
            product_slug = item.get("legacy_ref") or item.get("sku_base") or fallback_slug

            prod = Product(
                slug=product_slug,
                name_ar=product_name_ar,
                name_en=product_name_en,
                description_ar=item.get("description_ar", ""),
                description_en=item.get("description_en", ""),
                price=float(item.get("price", 0)),
                compare_price=round(float(item.get("compare_price", float(item.get("price", 0)) * 1.3)), 2),
                stock=item.get("stock", random.randint(10, 100)),
                category_id=target_sub_cat.id,
                rating=item.get("rating", 4.5),
                rating_count=item.get("rating_count", 20),
                is_featured=item.get("is_featured", False),
                is_active=item.get("is_active", True),
                specs=specs if specs else None
            )
            db.add(prod)
            db.flush()
            
            # Handle Images
            for i, img_url in enumerate(item.get("images", [])):
                # Sanitize URL: Remove complex query parameters that might cause loading issues
                # Extra.com URLs often look like: ...Black?locale=en-GB,en-*,*&$Listing-Product-2x$
                # We want to keep the base image but simplify the request
                clean_url = img_url
                if "media.extra.com" in img_url and "?" in img_url:
                    clean_url = img_url.split("?")[0]
                
                db.add(ProductImage(
                    product_id=prod.id,
                    image_url=clean_url,
                    alt_text=prod.name_ar,
                    sort_order=i
                ))
            
            inserted_count = inserted_count + 1
            existing_products_ar.add(product_name_ar)
            existing_products_en.add(product_name_en)
            
            # Commit every 20 products for real-time progress and stability
            if inserted_count % 20 == 0:
                db.commit()
                print(f">>> [SEED] Progress: {inserted_count} products committed...")

        # Ensure Demo Coupons
        for code, discount in [("WELCOME10", 10.0), ("VORTEX20", 20.0)]:
            if not db.query(Coupon).filter(Coupon.code == code).first():
                db.add(Coupon(code=code, discount_percent=discount, is_active=True))

        db.commit()
        print(f">>> [SEED] FINAL: {inserted_count} products inserted.")

        return AuthResponse(
            isSuccess=True,
            value={
                "message": "تمت عملية المزامنة بنجاح",
                "total_products": len(products_data),
                "inserted_new": inserted_count,
                "skipped": skipped_count,
                "source_file": os.path.basename(json_path),
                "path": json_path,
                "version": API_VERSION
            },
            statusCode=201,
        )

    except Exception as e:
        db.rollback()
        return AuthResponse(
            isSuccess=False,
            value={"message": str(e)},
            statusCode=500,
            error=str(e)
        )