"""
Sync products (SPU + SKU) from Cin7 to yudao

Cin7 Products fields: id, status, createdDate, modifiedDate, styleCode,
  name, description, images[].link, supplierId, brand (string),
  category (string), subCategory, weight, height, width, length, volume,
  optionLabel1="Color", optionLabel2="Size", optionLabel3="Printed",
  productOptions[]

yudao product_spu: id, name, category_id, brand_id, price, cost_price,
  stock, pic_url, slider_pic_urls, status, description, unit,
  recommend_*, sort, spec_type, delivery_template_id, ...

yudao product_sku: id, spu_id, properties (JSON), price, market_price,
  cost_price, bar_code, stock, weight, volume, pic_url, ...

Key business rules (confirmed with user):
1. status: channels == "Magento 2" → ENABLE (0), else DISABLE (1)
2. Prices: yudao uses 分 (cents), Cin7 uses dollars → multiply by 100
3. Brand: Cin7 brand is a string name → look up yudao brand_id
4. Category: Cin7 category is a string name → look up yudao category_id
"""
import json
import re
import time
from db import query_one, insert_and_get_id, execute_one, query_all
from sync_brand import ensure_brand, BRAND_ID_MAP
from sync_category import ensure_category, CATEGORY_ID_MAP
from config import TENANT_ID


# Map cin7 product_id → yudao spu_id
SPU_ID_MAP = {}   # cin7_product_id -> yudao_spu_id


def _price_fen(dollar_val):
    """Convert dollar to fen (cents), handle None/invalid"""
    if dollar_val is None:
        return -1
    try:
        return int(float(dollar_val) * 100)
    except (ValueError, TypeError):
        return -1


def _int(val, default=0):
    try:
        return int(val)
    except (ValueError, TypeError):
        return default


def _str(val):
    if val is None:
        return ""
    return str(val).strip()


def _join_pics(images):
    """Join list of image URLs into comma-separated string"""
    if not images:
        return ""
    links = []
    for img in images:
        link = img.get("link") or img.get("url") or img.get("src") or ""
        if link:
            links.append(link.strip())
    return ",".join(links[:15])  # max 15


def _load_existing_spu(cin7_id):
    """Check if SPU already synced (by cin7 product id stored in description)"""
    # Try to find by matching description contains cin7 id
    row = query_one(
        "SELECT id FROM product_spu WHERE description LIKE %s AND deleted=0 LIMIT 1",
        (f"%[cin7:{cin7_id}]%",)
    )
    if row:
        return row["id"]
    return None


def _sync_spu(sp, tenant_id=TENANT_ID):
    """Sync a single Cin7 product as one SPU. Returns yudao_spu_id."""
    cin7_id = sp["id"]

    if cin7_id in SPU_ID_MAP:
        return SPU_ID_MAP[cin7_id]

    # Brand
    brand_name = _str(sp.get("brand"))
    brand_id = None
    if brand_name:
        # ensure brand exists and get yudao id
        brand_id, _ = ensure_brand({"id": 0, "company": brand_name}, tenant_id)
        # If BRAND_ID_MAP already has this name, use it
        for cid, bid in BRAND_ID_MAP.items():
            from sync_brand import BRAND_ID_MAP as B
            pass  # just ensure brand is in map

    # Category — look up by name
    cat_name = _str(sp.get("category"))
    category_id = 0
    if cat_name and cat_name in CATEGORY_ID_MAP:
        category_id = CATEGORY_ID_MAP[cat_name]

    # Images
    images = sp.get("images") or []
    pic_url = images[0].get("link", "") if images else ""
    slider_pics = _join_pics(images)

    # Status: Magento 2 → enable, else disable (客户要求)
    channels = _str(sp.get("channels") or sp.get("Channels") or "")
    status = 0 if channels == "Magento 2" else 1

    # Description with cin7 id for matching
    description = sp.get("description") or ""
    description = f"{description}\n\n[cin7:{cin7_id}]"

    # Unit: default to piece (4=件)
    unit = 4
    sort = 0

    # Check existing
    existing_id = _load_existing_spu(cin7_id)

    if existing_id:
        sql = """
            UPDATE product_spu SET
                name=%s, category_id=%s, brand_id=%s, pic_url=%s,
                slider_pic_urls=%s, status=%s, description=%s,
                update_time=NOW(), updater='cin7_sync'
            WHERE id=%s
        """
        execute_one(sql, (
            _str(sp.get("name")), category_id, brand_id or 0,
            pic_url, slider_pics, status, description, existing_id
        ))
        SPU_ID_MAP[cin7_id] = existing_id
        print(f"  [spu] updated id={existing_id} name='{_str(sp.get('name'))}'")
        return existing_id, False
    else:
        sql = """
            INSERT INTO product_spu
                (name, category_id, brand_id, pic_url, slider_pic_urls,
                 status, description, unit, sort, price, cost_price,
                 market_price, stock, delivery_template_id, spec_type,
                 creator, create_time, updater, tenant_id)
            VALUES
                (%s, %s, %s, %s, %s, %s, %s, %s, %s, -1, -1, NULL, 0, 0, 0,
                 'cin7_sync', NOW(), 'cin7_sync', %s)
        """
        new_id = insert_and_get_id(sql, (
            _str(sp.get("name")), category_id, brand_id or 0,
            pic_url, slider_pics, status, description,
            unit, sort, tenant_id
        ))
        SPU_ID_MAP[cin7_id] = new_id
        print(f"  [spu] created id={new_id} name='{_str(sp.get('name'))}'")
        return new_id, True


def _sync_skus(spu_id, sp, tenant_id=TENANT_ID):
    """Sync productOptions as SKUs under the given SPU."""
    options = sp.get("productOptions") or []
    if not options:
        # No options = single SKU
        _upsert_sku(spu_id, None, sp, tenant_id)
        return

    for opt in options:
        _upsert_sku(spu_id, opt, sp, tenant_id)


def _upsert_sku(spu_id, opt, sp, tenant_id):
    """Insert or update a single SKU row."""
    if opt:
        code = _str(opt.get("code"))
        barcode = _str(opt.get("barcode"))
        price = _price_fen(opt.get("sellingPrice"))
        cost = _price_fen(opt.get("costPrice"))
        stock = _int(opt.get("stockOnHand"))
        status_val = 0 if opt.get("status") == "Active" else 1
        opt_id = opt.get("id")
    else:
        code = _str(sp.get("styleCode"))
        barcode = _str(sp.get("barCode"))
        price = _price_fen(sp.get("sellingPrice"))
        cost = _price_fen(sp.get("costPrice"))
        stock = _int(sp.get("stockOnHand"))
        status_val = 0 if sp.get("status") == "Active" else 1
        opt_id = None

    # Properties JSON
    properties = []
    for label_key in ("optionLabel1", "optionLabel2", "optionLabel3"):
        val = _str(opt.get(label_key)) if opt else ""
        if val and val not in ("Color", "Size", "Printed", ""):
            # Store as property json: {propertyId, propertyName, valueId, valueName}
            # Simplified: use valueName directly as property value
            prop_name = sp.get(label_key, label_key)
            properties.append({
                "propertyName": prop_name,
                "propertyValueName": val
            })

    props_json = json.dumps(properties) if properties else None

    # Check existing SKU by bar_code or code
    existing = None
    if barcode:
        existing = query_one(
            "SELECT id FROM product_sku WHERE bar_code=%s AND deleted=0 LIMIT 1",
            (barcode,)
        )
    if not existing and code:
        existing = query_one(
            "SELECT id FROM product_sku WHERE spu_id=%s AND deleted=0 LIMIT 1",
            (spu_id,)
        )

    if existing:
        sql = """
            UPDATE product_sku SET
                price=%s, cost_price=%s, bar_code=%s, stock=%s,
                properties=%s, update_time=NOW(), updater='cin7_sync'
            WHERE id=%s
        """
        execute_one(sql, (
            price, cost, barcode or code, stock,
            props_json, existing["id"]
        ))
    else:
        sql = """
            INSERT INTO product_sku
                (spu_id, price, market_price, cost_price, bar_code,
                 stock, properties, status, creator, create_time, updater, tenant_id)
            VALUES (%s, %s, -1, %s, %s, %s, %s, %s, 'cin7_sync', NOW(), 'cin7_sync', %s)
        """
        insert_and_get_id(sql, (
            spu_id, price, cost, barcode or code, stock,
            props_json, status_val, tenant_id
        ))


def sync_spus(cin7_api, tenant_id=TENANT_ID):
    """Fetch all Cin7 products and sync to yudao"""
    print("\n=== Sync SPU + SKU ===")
    raw = cin7_api.get_products()
    products = raw.get("d", []) if isinstance(raw, dict) else raw

    created, updated = 0, 0
    for i, p in enumerate(products):
        print(f"  [{i+1}/{len(products)}] ", end="")
        _, is_new = _sync_spu(p, tenant_id)
        spu_id = SPU_ID_MAP[p["id"]]
        _sync_skus(spu_id, p, tenant_id)
        if is_new:
            created += 1
        else:
            updated += 1

    print(f"\n[spu] Done. {created} created, {updated} updated. Total: {len(SPU_ID_MAP)}")
    return SPU_ID_MAP