WITH RECURSIVE 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
        AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.domain_id')) = 'es'
),
ranked_products AS (
    SELECT 
        p.id, 
        p.asin
    FROM (
        SELECT 
            id, 
            asin, 
            category,
            created_at,
            ROW_NUMBER() OVER (PARTITION BY asin ORDER BY created_at) AS rn
        FROM products
    ) p
    JOIN job_categories jc ON p.category = jc.category_id
    WHERE p.created_at >= jc.job_created_at AND p.rn = 1
),
date_range AS (
    SELECT 
        rp.id AS product_id,
        DATE_FORMAT(COALESCE(MIN(ps.timestamp), CURDATE()), '%Y-%m-01') AS start_date,
        LAST_DAY(COALESCE(MAX(ps.timestamp), CURDATE())) AS end_date
    FROM ranked_products rp
    LEFT JOIN product_sales ps ON rp.id = ps.product_id
    GROUP BY rp.id
),
date_series AS (
    SELECT product_id, start_date AS date
    FROM date_range
    UNION ALL
    SELECT ds.product_id, DATE_ADD(ds.date, INTERVAL 1 MONTH)
    FROM date_series ds
    JOIN date_range dr ON ds.product_id = dr.product_id
    WHERE ds.date < dr.end_date
),
price_ranges AS (
    SELECT 
        product_id,
        start,
        COALESCE(end, LEAD(start) OVER (PARTITION BY product_id ORDER BY start)) AS end,
        CAST(RIGHT(CONVERT(value USING utf8mb4), 5) AS DECIMAL(10,2)) AS value
    FROM product_list_prices
    WHERE product_id IN (SELECT id FROM ranked_products)
),
monthly_prices AS (
    SELECT 
        ds.product_id,
        ds.date,
        AVG(COALESCE(
            (SELECT value 
             FROM price_ranges pr
             WHERE pr.product_id = ds.product_id
               AND DATE(ds.date) >= pr.start AND DATE(ds.date) < COALESCE(pr.end, '9999-12-31')
             ORDER BY pr.start DESC 
             LIMIT 1),
            (SELECT value FROM price_ranges pr2 WHERE pr2.product_id = ds.product_id ORDER BY start DESC LIMIT 1),
            0
        )) AS avg_price
    FROM 
        date_series ds
    GROUP BY 
        ds.product_id, ds.date
),
monthly_sales AS (
    SELECT 
        product_id,
        DATE_FORMAT(timestamp, '%Y-%m-01') AS sale_month,
        SUM(value) AS month_sales
    FROM 
        product_sales
    WHERE 
        product_id IN (SELECT id FROM ranked_products)
    GROUP BY 
        product_id, DATE_FORMAT(timestamp, '%Y-%m-01')
),
price_with_previous AS (
    SELECT 
        mp.product_id,
        mp.date,
        mp.avg_price,
        @prev_price := IF(@prev_product_id = mp.product_id, 
                          IF(mp.avg_price != 0, mp.avg_price, @prev_price), 
                          IF(mp.avg_price != 0, mp.avg_price, 0)) AS prev_price,
        @prev_product_id := mp.product_id
    FROM 
        monthly_prices mp
    CROSS JOIN 
        (SELECT @prev_price := 0, @prev_product_id := NULL) AS vars
    ORDER BY 
        mp.product_id, mp.date
),
sales_data AS (
    SELECT 
        pwp.product_id,
        pwp.date,
        COALESCE(pwp.avg_price, pwp.prev_price, 0) AS month_price,
        COALESCE(ms.month_sales, 0) AS month_sales,
        ROUND(COALESCE(pwp.avg_price, pwp.prev_price, 0) * COALESCE(ms.month_sales, 0), 2) AS month_revenue
    FROM 
        price_with_previous pwp
    LEFT JOIN 
        monthly_sales ms ON pwp.product_id = ms.product_id AND pwp.date = ms.sale_month
)
SELECT 
    rp.id,
    rp.asin,
    sd.date,
    sd.month_price AS price,
    sd.month_sales AS sales,
    sd.month_revenue AS revenue
FROM ranked_products rp
JOIN sales_data sd ON rp.id = sd.product_id
ORDER BY rp.id, sd.date;