-- @database: hxa_bo
--
-- Pre-aggregated breakdown of live sessions (country / browser / OS / type),
-- read by `GET /admin/stats/sessions`.
--
-- WHY a snapshot rather than aggregating on demand: `user_session` grows with
-- the user base, and a `GROUP BY user_agent` over it is a full scan. Doing that
-- per dashboard load ties response time to account count — exactly what must
-- not happen as the platform grows. Here the endpoint reads a few dozen rows of
-- ONE capture, in constant time, whatever the session count behind it.
--
-- Same division of labour as `platform_metric_daily`: lives in `hxa_bo`, and
-- `bin/session-stats-rollup.php` is the ONLY writer. Application code reads.
--
-- Counts are APPROXIMATE by design — they are as old as the last capture, and
-- the worker may sample when the volume justifies it. `captured_at` is part of
-- the payload so a reader always knows how stale the figure is.

CREATE TABLE IF NOT EXISTS `session_stat_snapshot` (
    -- One capture = one `captured_at`, several dimensions, several buckets.
    `captured_at` DATETIME    NOT NULL,
    -- 'country' | 'browser' | 'os' | 'type'
    `dimension`   VARCHAR(16) NOT NULL,
    -- Bucket key within the dimension: 'FR', 'Safari', 'iOS', 'mobile'.
    -- Empty string = UNKNOWN (no country resolved, unparseable user agent).
    -- A real bucket rather than a hole: hiding it would falsify every share
    -- and conceal precisely the cases worth fixing.
    `bucket`      VARCHAR(64) NOT NULL,
    -- Sessions not yet expired.
    `active`      BIGINT      NOT NULL DEFAULT 0,
    -- Subset of the above seen in the last 24h — "connected" rather than
    -- "has an unexpired token", since a session lives for weeks.
    `recent`      BIGINT      NOT NULL DEFAULT 0,

    PRIMARY KEY (`captured_at`, `dimension`, `bucket`),
    -- Reading the latest capture: MAX(captured_at) then its rows.
    KEY `idx_dimension_captured` (`dimension`, `captured_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
