WITH RECURSIVE date_bounds AS (
    SELECT
        DATE_FORMAT(MIN(d), '%Y-%m-01') AS min_month,
        DATE_FORMAT(CURDATE(), '%Y-%m-01') AS max_month
    FROM (
        SELECT CAST(PostDate AS CHAR) AS d
        FROM orders
        WHERE CAST(PostDate AS CHAR) NOT IN ('0000-00-00', '0000-00-00 00:00:00')

        UNION ALL

        SELECT CAST(StartDate AS CHAR) AS d
        FROM order_products
        WHERE CAST(StartDate AS CHAR) NOT IN ('0000-00-00', '0000-00-00 00:00:00')
    ) x
    WHERE d IS NOT NULL
),

months AS (
    SELECT min_month AS month_start
    FROM date_bounds
    WHERE min_month IS NOT NULL

    UNION ALL

    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM months, date_bounds
    WHERE month_start < date_bounds.max_month
),

sales AS (
    SELECT
        DATE_FORMAT(o.PostDate, '%Y-%m-01') AS month_start,
        CASE
            WHEN o.PostDate < '2026-01-01' THEN 'BGN'
            ELSE 'EUR'
        END AS currency,
        COUNT(DISTINCT o.ID) AS confirmed_orders_count,
        SUM(o.TotalPrice) AS confirmed_orders_turnover
    FROM orders o
    WHERE
        CAST(o.PostDate AS CHAR) NOT IN ('0000-00-00', '0000-00-00 00:00:00')
        AND ((o.Status & 32) <> 0 OR (o.Status & 64) <> 0)
        AND (o.Status & 256) = 0
    GROUP BY
        DATE_FORMAT(o.PostDate, '%Y-%m-01'),
        CASE
            WHEN o.PostDate < '2026-01-01' THEN 'BGN'
            ELSE 'EUR'
        END
),

active_subscriptions AS (
    SELECT
        m.month_start,
        COUNT(DISTINCT CASE
            WHEN s.Digital = 1 THEN CONCAT(op.OrderID, '-', op.ProductID, '-', op.StartDate)
        END) AS active_online_subscriptions,
        COUNT(DISTINCT CASE
            WHEN s.Digital = 0 THEN CONCAT(op.OrderID, '-', op.ProductID, '-', op.StartDate)
        END) AS active_print_subscriptions
    FROM months m
    INNER JOIN order_products op
        ON CAST(op.StartDate AS CHAR) NOT IN ('0000-00-00', '0000-00-00 00:00:00')
        AND CAST(op.EndDate AS CHAR) NOT IN ('0000-00-00', '0000-00-00 00:00:00')
        AND op.StartDate < DATE_ADD(m.month_start, INTERVAL 1 MONTH)
        AND op.EndDate >= m.month_start
        AND op.Months > 1
    INNER JOIN orders o
        ON o.ID = op.OrderID
        AND ((o.Status & 32) <> 0 OR (o.Status & 64) <> 0)
        AND (o.Status & 256) = 0
    INNER JOIN subscriptions s
        ON s.ProductID = op.ProductID
        AND s.Months > 1
    GROUP BY m.month_start
)

SELECT
    DATE_FORMAT(m.month_start, '%Y-%m') AS month,
    CASE
        WHEN m.month_start < '2026-01-01' THEN 'BGN'
        ELSE 'EUR'
    END AS currency,
    COALESCE(s.confirmed_orders_count, 0) AS confirmed_orders_count,
    COALESCE(s.confirmed_orders_turnover, 0) AS confirmed_orders_turnover,
    COALESCE(a.active_online_subscriptions, 0) AS active_online_subscriptions,
    COALESCE(a.active_print_subscriptions, 0) AS active_print_subscriptions
FROM months m
LEFT JOIN sales s
    ON s.month_start = m.month_start
    AND s.currency = CASE
        WHEN m.month_start < '2026-01-01' THEN 'BGN'
        ELSE 'EUR'
    END
LEFT JOIN active_subscriptions a ON a.month_start = m.month_start
ORDER BY m.month_start;