-- Versions the `biome` referential catalogue (added by hand on the `hxa`
-- database) so a fresh environment can rebuild it from migrations.
--
-- The 15 rows are the WWF terrestrial biome classification (Olson et al.
-- 2001). The `id` (1..15) matches the `biome_number` returned by the
-- geo_v2 `get_biome(lat, lng)` stored procedure one-to-one, so a media's
-- `biome_id` is exactly what the procedure yields for its GPS pair.
--
-- Referential & FROZEN — no `slug`, no timestamps, no `is_active`: the WWF
-- list does not change. Human-readable, TOURISTIC labels are NOT stored
-- here; they live in the file-based i18n catalogues
-- (`resources/lang/<locale>/biomes.php`, keyed `biomes.names.<id>`), same
-- as every other localized label in the app. The English `name` below is
-- kept only as the canonical source-of-truth / debugging aid — the API
-- never serves it directly.
--
-- Idempotent: `CREATE TABLE IF NOT EXISTS` + `INSERT ... ON DUPLICATE KEY
-- UPDATE` so re-running over the hand-made table is a no-op that also
-- repairs any drifted label.

CREATE TABLE IF NOT EXISTS `biome` (
    `id`   TINYINT UNSIGNED NOT NULL,
    `name` VARCHAR(150)     NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `biome` (`id`, `name`) VALUES
    (1,  'Tropical & Subtropical Moist Broadleaf Forests'),
    (2,  'Tropical & Subtropical Dry Broadleaf Forests'),
    (3,  'Tropical & Subtropical Coniferous Forests'),
    (4,  'Temperate Broadleaf & Mixed Forests'),
    (5,  'Temperate Conifer Forests'),
    (6,  'Boreal Forests/Taiga'),
    (7,  'Tropical & Subtropical Grasslands, Savannas & Shrublands'),
    (8,  'Temperate Grasslands, Savannas & Shrublands'),
    (9,  'Flooded Grasslands & Savannas'),
    (10, 'Montane Grasslands & Shrublands'),
    (11, 'Tundra'),
    (12, 'Mediterranean Forests, Woodlands & Scrub'),
    (13, 'Deserts & Xeric Shrublands'),
    (14, 'Mangroves'),
    (15, 'Rock and Ice')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
