WITH RECURSIVE initial_categories AS (
    SELECT jt.child_id COLLATE utf8mb4_unicode_ci AS child_id
    FROM kaibos_category_finder.amazon_categories,
    JSON_TABLE(children, '$[*]' COLUMNS (
        child_id VARCHAR(255) PATH '$'
    )) AS jt
    WHERE category_id IN ('4772050031', '667049031','599364031','599391031')
),
category_tree AS (
    -- Caso base: selecciona las categorías iniciales (children de 4772050031)
    SELECT 
        category_id COLLATE utf8mb4_unicode_ci AS category_id, 
        parent_id COLLATE utf8mb4_unicode_ci AS parent_id, 
        name,
        domain,
        contextFreeName,
        children,
        JSON_LENGTH(children) AS child_count,
        0 AS level
    FROM 
        kaibos_category_finder.amazon_categories
    WHERE parent_id IN (SELECT child_id FROM initial_categories)
    
    UNION ALL
    
    -- Caso recursivo: selecciona los hijos y cuenta sus children
    SELECT 
        c.category_id COLLATE utf8mb4_unicode_ci,
        c.parent_id COLLATE utf8mb4_unicode_ci,
        c.name,
        c.domain,
        c.contextFreeName,
        c.children,
        JSON_LENGTH(c.children) AS child_count,
        ct.level + 1
    FROM 
        kaibos_category_finder.amazon_categories c
    INNER JOIN 
        category_tree ct ON c.parent_id COLLATE utf8mb4_unicode_ci = ct.category_id
)

SELECT 
    category_id,
    parent_id,
    name,
    domain,
    contextFreeName,
    children,
    child_count,
    level
FROM 
    category_tree
ORDER BY 
    level, category_id;