-- Indexes backing the back-office media dashboard (`GET /admin/media/stats`).
--
-- That endpoint runs two aggregate scans over `hxa.media`:
--   - uploads-per-day : GROUP BY DATE(`created_at`) over a rolling window
--                       (`WHERE created_at >= …`).
--   - media-per-country: GROUP BY `country_id` ORDER BY COUNT(*) DESC.
--
-- Both were full table scans because neither column was indexed (`country_id`
-- was deliberately left unindexed when added — cf.
-- `2026_06_14_180000_add_media_country_region_subregion.sql` — since public
-- listings filter on Meilisearch facets, not on `media` directly). The
-- dashboard is the first MySQL-side consumer of these axes, so we add the two
-- covering-ish indexes here.
--
-- `idx_created_at` also turns the window `WHERE created_at >= :from` into a
-- range scan, and serves the same column read by `users.registered`-style
-- rollups should they ever move to `media`.
--
-- `idx_country_id` is a plain single-column index: the GROUP BY can read it in
-- order, and NULL rows (uploads without GPS) cluster together so the
-- "withoutCountry" bucket is cheap too.
--
-- These are pure read-path optimisations; the write cost (one extra B-tree
-- entry per insert on an append-mostly table) is negligible.

ALTER TABLE `media`
    ADD INDEX `idx_created_at` (`created_at`),
    ADD INDEX `idx_country_id` (`country_id`);
