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

Cin7 ProductCategories fields: id, name, parentId, sortOrder, isActive
yudao product_category: id, parent_id, name, pic_url, big_pic_url, sort,
                        status, creator, create_time, updater, tenant_id
"""
from db import query_one, insert_and_get_id, execute_one


# Map Cin7 category → yudao category_id (cache)
CATEGORY_ID_MAP = {}  # cin7_id -> yudao_id


def ensure_category(cin7_cat, tenant_id=0):
    """
    Recursively ensure category exists in yudao.
    Returns (yudao_id, created_bool)
    """
    cin7_id = cin7_cat["id"]
    if cin7_id in CATEGORY_ID_MAP:
        return CATEGORY_ID_MAP[cin7_id], False

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

    # Check if already exists by cin7 metadata (we store cin7_id in a comment or via name matching)
    # Since yudao doesn't have a cin7_id column, we match by name + parent_id
    parent_id = 0
    parent_cin7_id = cin7_cat.get("parentId")
    if parent_cin7_id and parent_cin7_id in CATEGORY_ID_MAP:
        parent_id = CATEGORY_ID_MAP[parent_cin7_id]

    # TODO: currently just inserts — add update logic if needed
    sql = """
        INSERT INTO product_category
            (name, parent_id, sort, status, tenant_id, creator, create_time, updater)
        VALUES (%s, %s, %s, %s, %s, 'cin7_sync', NOW(), 'cin7_sync')
    """
    sort_order = cin7_cat.get("sortOrder") or 0
    is_active = cin7_cat.get("isActive", True)
    status = 0 if is_active else 1

    yudao_id = insert_and_get_id(sql, (name, parent_id, sort_order, status, tenant_id))
    CATEGORY_ID_MAP[cin7_id] = yudao_id

    print(f"  [category] created id={yudao_id} name='{name}' parent={parent_id}")
    return yudao_id, True


def sync_categories(cin7_api, tenant_id=0):
    """Fetch all Cin7 categories and sync to yudao"""
    print("\n=== Sync Categories ===")
    raw = cin7_api.get_categories()

    # Cin7 returns {d: [...]} typically
    categories = raw.get("d", []) if isinstance(raw, dict) else raw

    # Cin7 may return flat list — build tree first to handle parents before children
    by_id = {c["id"]: c for c in categories}

    # Process in order: roots first, then children
    def process(cat):
        # Ensure parent first
        parent_id = cat.get("parentId")
        if parent_id and parent_id in by_id and parent_id not in CATEGORY_ID_MAP:
            process(by_id[parent_id])
        ensure_category(cat, tenant_id)

    for cat in categories:
        if cat["id"] not in CATEGORY_ID_MAP:
            process(cat)

    print(f"[category] Done. {len(CATEGORY_ID_MAP)} categories mapped.")
    return CATEGORY_ID_MAP