-- Primera parte: Configurar la variable de producto
SET @product_id = :product_id;
-- Segunda parte: Consulta principal
SELECT pwp.product_id,
    pwp.date,
    COALESCE(pwp.price, pwp.prev_price, 0) AS day_price,
    COALESCE(ds.day_sales, 0) AS day_sales,
    ROUND(
        COALESCE(pwp.price, pwp.prev_price, 0) * COALESCE(ds.day_sales, 0),
        2
    ) AS day_revenue
FROM (
        WITH RECURSIVE date_range AS (
            SELECT @product_id AS product_id,
                COALESCE(MIN(DATE(ps.timestamp)), CURDATE()) AS start_date,
                COALESCE(MAX(DATE(ps.timestamp)), CURDATE()) AS end_date
            FROM product_sales ps
            WHERE ps.product_id = @product_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 DAY)
            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_new_prices
WHERE product_id = @product_id
),
daily_prices AS (
    SELECT ds.product_id,
        ds.date,
        COALESCE(
            (
                SELECT value
                FROM price_ranges pr
                WHERE pr.product_id = ds.product_id
                    AND ds.date >= pr.start
                    AND 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 price
FROM date_series ds
),
price_with_previous AS (
    SELECT dp.product_id,
        dp.date,
        dp.price,
        @prev_price := IF(dp.price != 0, dp.price, @prev_price) AS prev_price
    FROM daily_prices dp
        CROSS JOIN (
            SELECT @prev_price := 0
        ) AS vars
    ORDER BY dp.date
)
SELECT *
FROM price_with_previous
) AS pwp
LEFT JOIN (
    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)
) AS ds ON pwp.product_id = ds.product_id
AND pwp.date = ds.sale_date
ORDER BY pwp.date;