"""
Sync product categories from Cin7 to yudao product_category table

Cin7 ProductCategories fields: id, name, parentId, sort, isActive
yudao product_category: id, parent_id, name, sort, status, ...
"""
from db import query_all, insert_many, update_many


CATEGORY_ID_MAP = {}           # cin7_id -> yudao_id
CATEGORY_NAME_TO_ID_MAP = {}   # name -> yudao_id


def sync_categories(cin7_api, tenant_id=0):
    """
    Fetch all Cin7 categories and sync to yudao.

    Strategy:
      1. Load ALL existing category ids in one query.
      2. Sort categories by depth (parents before children) — no recursion mid-sync.
      3. One insert_many for new categories.
      4. One update_many for changed categories.
    No per-row DB round-trips, no threading needed.
    """
    global CATEGORY_ID_MAP, CATEGORY_NAME_TO_ID_MAP
    CATEGORY_ID_MAP = {}
    CATEGORY_NAME_TO_ID_MAP = {}

    print("\n=== Sync Categories ===")
    raw = cin7_api.get_categories()
    categories = raw.get("d", []) if isinstance(raw, dict) else raw
    print(f"[category] Fetched {len(categories)} categories from Cin7")

    # Load all existing category ids in one shot
    existing_rows = query_all("SELECT id FROM product_category")
    existing_ids = {r["id"] for r in existing_rows}

    # Sort by depth (parents first) so parent_id references are always resolved
    by_id = {c["id"]: c for c in categories}

    def get_depth(cid, visited=None):
        if visited is None:
            visited = set()
        if cid in visited or cid not in by_id:
            return 0
        visited.add(cid)
        pid = by_id[cid].get("parentId")
        if not pid or pid not in by_id:
            return 0
        return 1 + get_depth(pid, visited)

    categories_sorted = sorted(categories, key=lambda c: get_depth(c["id"]))

    to_insert = []
    to_update = []

    for cat in categories_sorted:
        cin7_id = cat.get("id")
        name = (cat.get("name") or "").strip()
        if not name or not cin7_id:
            continue

        parent_cin7_id = cat.get("parentId")
        parent_id = parent_cin7_id if (parent_cin7_id and parent_cin7_id in by_id) else 0
        sort_ord = cat.get("sort") or 0
        status   = 0 if cat.get("isActive", True) else 1

        # yudao id == cin7 id for categories
        CATEGORY_ID_MAP[cin7_id] = cin7_id
        CATEGORY_NAME_TO_ID_MAP[name] = cin7_id

        if cin7_id in existing_ids:
            to_update.append((name, parent_id, sort_ord, status, cin7_id))
        else:
            to_insert.append((cin7_id, name, parent_id, sort_ord, status, tenant_id))

    # Insert in depth order so FK constraints (if any) are satisfied
    if to_insert:
        insert_many(
            "INSERT INTO product_category "
            "(id, name, parent_id, sort, status, tenant_id, "
            "creator, create_time, updater) "
            "VALUES (%s, %s, %s, %s, %s, %s, 'cin7_sync', NOW(), 'cin7_sync')",
            to_insert,
        )
        print(f"[category] Inserted {len(to_insert)} new categories")

    if to_update:
        update_many(
            "UPDATE product_category SET name=%s, parent_id=%s, sort=%s, status=%s, "
            "update_time=NOW(), updater='cin7_sync' WHERE id=%s",
            to_update,
        )
        print(f"[category] Updated {len(to_update)} existing categories")

    print(f"[category] Done. {len(to_insert)} created, {len(to_update)} updated.")
    return CATEGORY_ID_MAP


def ensure_category(cin7_cat, tenant_id=0):
    """
    Lightweight helper for mid-sync single-category upsert (fallback path).
    """
    from db import query_one, execute_one, insert_and_get_id

    cin7_id = cin7_cat.get("id")
    name = (cin7_cat.get("name") or "").strip()
    if not name or not cin7_id:
        return None, False

    if cin7_id in CATEGORY_ID_MAP:
        return CATEGORY_ID_MAP[cin7_id], False

    parent_cin7_id = cin7_cat.get("parentId")
    parent_id = parent_cin7_id if (parent_cin7_id and parent_cin7_id in CATEGORY_ID_MAP) else 0
    sort_ord = cin7_cat.get("sort") or 0
    status   = 0 if cin7_cat.get("isActive", True) else 1

    existing = query_one("SELECT id FROM product_category WHERE id=%s", (cin7_id,))
    if existing:
        execute_one(
            "UPDATE product_category SET name=%s, parent_id=%s, sort=%s, status=%s, "
            "update_time=NOW(), updater='cin7_sync' WHERE id=%s",
            (name, parent_id, sort_ord, status, cin7_id),
        )
        yudao_id = existing["id"]
    else:
        yudao_id = insert_and_get_id(
            "INSERT INTO product_category "
            "(id, name, parent_id, sort, status, tenant_id, "
            "creator, create_time, updater) "
            "VALUES (%s, %s, %s, %s, %s, %s, 'cin7_sync', NOW(), 'cin7_sync')",
            (cin7_id, name, parent_id, sort_ord, status, tenant_id),
        )

    CATEGORY_ID_MAP[cin7_id] = yudao_id
    CATEGORY_NAME_TO_ID_MAP[name] = yudao_id
    return yudao_id, not existing