-- Per-country daily media-upload rollup.
--
-- Sibling of `platform_metric_daily` (cf.
-- `2026_06_20_120000_create_platform_metric_daily.sql`) but with a SECOND
-- dimension: the country. The platform rollup is one value per (metric, day);
-- here the cardinality is country × day (~200 countries), which would blow up
-- the fixed `PlatformMetric` enum — so it gets its own narrow table instead of
-- a dynamic `media.uploaded.country.FR` metric key.
--
-- Holds a single FLOW measure for now (`uploaded` = media uploaded that day,
-- per country). Adding another per-country measure later = a new column, not a
-- new table.
--
-- Lives in `hxa_bo`: like every rollup, the worker
-- (`bin/platform-metrics-rollup.php`) is the ONLY writer; it READS
-- `hxa.media` off-peak (GROUP BY country_id, DATE(created_at) — now index-
-- backed, cf. `2026_06_21_120000_add_media_stats_indexes.sql`) and UPSERTs
-- here. Read by `GET /admin/media/stats/countries`. Application/request code
-- MUST NOT write here.
--
-- `country_id` is CHAR(2) ISO 3166-1 alpha-2, matching `media.country_id` and
-- the Meilisearch `countries` index id. Uploads without GPS (NULL country) are
-- NOT stored here — they have no country dimension; the live
-- `GET /admin/media/stats` already surfaces them as `withoutCountry`.

CREATE TABLE `media_country_daily` (
    `country_id` CHAR(2)  NOT NULL,
    `day`        DATE     NOT NULL,
    `uploaded`   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,
    -- (country, day): one time-series per country, range scan on a single one.
    PRIMARY KEY (`country_id`, `day`),
    -- (day, uploaded): "top countries for a given day/range" leaderboard scans.
    KEY `idx_day_uploaded` (`day`, `uploaded`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
