SET @product_id := 314358;

WITH RECURSIVE date_range AS (
    SELECT 
        (SELECT MIN(DATE(timestamp)) FROM product_sales WHERE product_id = @product_id) AS start_date,
        (SELECT MAX(DATE(timestamp)) FROM product_sales WHERE product_id = @product_id) AS end_date
),
date_series AS (
    SELECT start_date AS date FROM date_range
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM date_series, date_range
    WHERE date < date_range.end_date
),
price_ranges AS (
    SELECT 
        start,
        COALESCE(end, LEAD(start) OVER (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 = @product_id
),
daily_prices AS (
    SELECT 
        @product_id AS product_id,
        ds.date,
        COALESCE(
            (SELECT value 
             FROM price_ranges 
             WHERE ds.date >= start AND ds.date < COALESCE(end, '9999-12-31')
             ORDER BY start DESC 
             LIMIT 1),
            (SELECT value FROM price_ranges ORDER BY start DESC LIMIT 1)
        ) AS price
    FROM 
        date_series ds
),
daily_sales AS (
    SELECT 
        product_id,
        DATE(timestamp) AS sale_date,
        sum(value) AS day_sales
    FROM 
        product_sales
    WHERE 
        product_id = @product_id
    GROUP BY 
        product_id, DATE(timestamp)
)
SELECT 
    dp.product_id,
    dp.date,
    dp.price AS day_price,
    COALESCE(ds.day_sales, 0) AS day_sales,
    ROUND(dp.price * COALESCE(ds.day_sales, 0), 2) AS day_revenue
FROM 
    daily_prices dp
LEFT JOIN 
    daily_sales ds ON dp.product_id = ds.product_id AND dp.date = ds.sale_date
ORDER BY 
    dp.date;