-- Platform-wide daily KPI rollup.
--
-- Unlike the PER-ENTITY daily aggregates already in the schema
-- (`user_view_daily`, `media_view_daily`, `tracking_daily` — one row per
-- user/media/target/day), this table holds ONE value per (metric, day) for
-- the WHOLE platform. It is the trend store behind the back-office
-- dashboards: "how many sign-ups / uploads / reports per day", "how does the
-- total user base grow", etc.
--
-- Long/narrow shape on purpose: a new KPI is a new `metric` string, never a
-- schema change. The canonical metric keys live in
-- `Hydrogen\Domain\Stats\PlatformMetric`.
--
-- Two families of metric (see `PlatformMetric::kind()`):
--   - FLOW     : events that happened ON that day (e.g. `users.registered`).
--                Backfillable from indexed timestamp columns via
--                GROUP BY DATE(<col>); re-running a rollup over a window
--                self-corrects late-arriving rows (idempotent UPSERT).
--   - SNAPSHOT : a stock COUNT taken once per day (e.g. `users.total`).
--                NOT backfillable historically — the series builds up one
--                point per daily run. This is what moves the heavy
--                `COUNT(*)` off the request hot path: handlers read the last
--                snapshot from `hxa_bo` instead of scanning `hxa.user`.
--
-- Lives in `hxa_bo` (back-office DB) by design: the rollup worker is the
-- ONLY writer, the main `hxa` tables are only ever READ (off-peak, indexed
-- GROUP BY), so the dashboard never disturbs the production user/media tables.
-- Application/request code MUST NOT write here — owned by
-- `bin/platform-metrics-rollup.php`.

CREATE TABLE `platform_metric_daily` (
    `metric`     VARCHAR(64) NOT NULL,
    `day`        DATE        NOT NULL,
    `value`      BIGINT      NOT NULL DEFAULT 0,
    `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- (metric, day): one time-series per metric, range scan on a single KPI.
    PRIMARY KEY (`metric`, `day`),
    -- (day, metric): cross-section of every KPI for a given day (dashboards).
    KEY `idx_day_metric` (`day`, `metric`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
