WITH job_categories AS (
    SELECT DISTINCT 
        CAST(JSON_EXTRACT(properties, '$.node_id') AS UNSIGNED) AS category_id,
        created_at AS job_created_at,
        JSON_UNQUOTE(JSON_EXTRACT(properties, '$.domain_id')) AS domain_id
    FROM job_statuses
    WHERE id >= 9712
        AND type = 'best_seller'
        AND status != 'failed'
        AND JSON_EXTRACT(properties, '$.node_id') IS NOT NULL
),
ranked_products AS (
    SELECT 
        p.id, 
        p.asin, 
        p.title, 
        p.description, 
        p.features, 
        p.category,
        jc.domain_id,
        JSON_UNQUOTE(JSON_EXTRACT(pbbs.productData, '$.imageUrl')) AS imageUrl,
        JSON_UNQUOTE(JSON_EXTRACT(pbbs.productData, '$.fulfillmentType')) AS logistics_type,
        CONCAT('https://www.amazon.', 
               CASE jc.domain_id
                   WHEN 'es' THEN 'es'
                   WHEN 'de' THEN 'de'
                   WHEN 'fr' THEN 'fr'
                   WHEN 'it' THEN 'it'
                   ELSE 'com'
               END, 
               '/dp/', p.asin) AS amazon_link,
        ROW_NUMBER() OVER (PARTITION BY p.asin ORDER BY p.id) AS rn
    FROM products p
    JOIN job_categories jc ON p.category = jc.category_id
    LEFT JOIN product_black_box_sales pbbs ON p.id = pbbs.product_id
    WHERE p.created_at >= jc.job_created_at
)
SELECT 
    id, asin, title, description, features, category, domain_id, 
    imageUrl, logistics_type, amazon_link
FROM ranked_products
WHERE rn = 1
ORDER BY id;