-- Attaches a WWF biome to each media. `biome_id` is derived once at upload
-- (and on any manual re-geolocation) from the media's GPS pair via the
-- geo_v2 `get_biome(lat, lng)` stored procedure, which returns the biome
-- number 1..15 — exactly `biome.id`.
--
-- Nullable: a media may have no GPS (EXIF stripped) or its point may fall
-- in the ocean / outside every biome polygon, in which case the procedure
-- returns no row and `biome_id` stays NULL. The admin backfill endpoint
-- (`POST /admin/media/backfill-biome`) populates the long tail of legacy
-- rows that have coords but no biome yet.
--
-- FK to the frozen referential with ON DELETE SET NULL (a biome is never
-- deleted, but the constraint documents the relationship and guards typos).
-- The index backs the "media in biome X" filter on the SQL side; Meili owns
-- the public faceting (see MeilisearchMediaSync + bin/media-meili-apply-settings).

ALTER TABLE `media`
    ADD COLUMN `biome_id` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `country_id`,
    ADD KEY `idx_media_biome` (`biome_id`),
    ADD CONSTRAINT `fk_media_biome`
        FOREIGN KEY (`biome_id`) REFERENCES `biome` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE;
