-- @database: bo
--
-- Pre-aggregated age / sex / joining-cohort breakdown of the user base, read by
-- `GET /admin/stats/demographics`.
--
-- WHY a snapshot rather than aggregating on demand: age is DERIVED from
-- `birthdate`, so no index can answer `GROUP BY <age bracket>` — the database
-- has to compute the expression for every row. Measured on ~987k accounts, that
-- is a 5.3 s full scan. Per dashboard load, growing with the user base. Same
-- answer as `session_stat_snapshot` and `platform_metric_daily`: the scan lives
-- in a worker, on a schedule, and the endpoint reads a few hundred rows.
--
-- DIFFERENT FROM `session_stat_snapshot` ON ONE POINT, deliberately: captures
-- are keyed by DATE and kept. Sessions are volatile, so only "now" matters
-- there; demographics move slowly, and the question worth answering is not
-- "how old is my audience" but "is it ageing, or am I recruiting young?".
-- History costs a few hundred rows a day and answers both.
--
-- THE STORED VALUE IS ALWAYS A COUNT OF USERS AT AN **EXACT** AGE — never a
-- bracket. Bracketing ("18-24", "65+") happens at read time. Two reasons:
-- changing a boundary must not require re-capturing (the past cannot be
-- recaptured), and exact ages are what make median / p25 / p75 exact rather
-- than interpolated from a histogram.
--
-- Written ONLY by `bin/demographics-rollup.php`. Application code reads.

CREATE TABLE IF NOT EXISTS `user_demographic_snapshot` (
    -- One capture per day. DATE, not DATETIME: two captures on the same day
    -- overwrite each other, which is what re-running the worker should do.
    `captured_on` DATE            NOT NULL,
    -- 'ageSex'    : bucket = exact age,   sub_bucket = sex slug
    -- 'cohortAge' : bucket = joining year, sub_bucket = exact age
    `dimension`   VARCHAR(16)     NOT NULL,
    `bucket`      VARCHAR(16)     NOT NULL,
    -- '' is never used as a hole: an absent birthdate or sex is the explicit
    -- bucket 'unknown'. Hiding those rows would falsify every share and
    -- conceal exactly the accounts worth looking at.
    `sub_bucket`  VARCHAR(16)     NOT NULL,
    `users`       BIGINT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`captured_on`, `dimension`, `bucket`, `sub_bucket`),
    -- Reading one dimension's history: the trend queries.
    KEY `idx_dimension_captured` (`dimension`, `captured_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
