-- Adds `country_id`, `region_id`, `subregion_id` next to `city_id` so the
-- full administrative cascade returned by `geo.locate(lat, lng)` can be
-- persisted on the media row.
--
-- Until now {@see GeoLookupService::locateCity()} discarded the three upper
-- levels because the front `media` table only had a slot for the city. The
-- procedure already cascades city → subregion → region → country and exposes
-- the four IDs in a single call, so storing the upper levels is a NULL→NULL
-- backfill cost and unlocks listings/filters by region or country without an
-- extra JOIN against the `geo` schema at read time.
--
-- Types follow the **ISO standards** used everywhere else for these levels
-- (and matching the Meilisearch `countries` / `regions` indexes which key
-- on the same identifiers):
--   - country_id    CHAR(2)    — ISO 3166-1 alpha-2 (e.g. "FR", "US", "AD")
--   - region_id     VARCHAR(7) — ISO 3166-2 subdivision (e.g. "FR-IDF", "AD-02")
--   - subregion_id  VARCHAR(7) — same shape as region_id
--
-- Nullable: legacy rows and uploads without EXIF GPS keep `NULL`, same
-- semantics as `city_id`.
--
-- No FK constraint: the referenced administrative tables live in the external
-- `geo` schema, which is queried through a dedicated PDO connection
-- ({@see GeoConnection}) — cross-database FKs are not used anywhere in this
-- project.
--
-- No index: like `city_id`, these columns are written but not used as primary
-- filter axes inside MySQL — public listings filter on the Meilisearch facets,
-- not directly on `media`.

ALTER TABLE `media`
    ADD COLUMN `subregion_id` VARCHAR(7) NULL DEFAULT NULL AFTER `city_id`,
    ADD COLUMN `region_id`    VARCHAR(7) NULL DEFAULT NULL AFTER `subregion_id`,
    ADD COLUMN `country_id`   CHAR(2)    NULL DEFAULT NULL AFTER `region_id`;
