-- Adds `media.orientation` so the API can expose the framing of each photo and
-- /media/nearby & /media/in-bounds can filter on it without recomputing
-- width/height at every read.
--
-- Values (kept as VARCHAR to stay debug-friendly and to map 1:1 to the Meili
-- `orientation` facet):
--   'landscape' — width  >  height, ratio < panorama threshold
--   'portrait'  — height >  width
--   'square'    — width  == height (or either dimension unknown)
--   'panorama'  — width / height >= 2.0 (very wide shots)
--
-- Backfill is computed from the published dimensions (`width`/`height`) so the
-- value stays in sync with what `MediaResourceSerializer` exposes. Rows where
-- either dimension is NULL (legacy media before width/height was captured)
-- fall back to 'square' rather than blocking the migration.
--
-- No dedicated index: cardinality is 4, MySQL won't use it. Meili owns the
-- filtering side via its own index (see MeilisearchMediaSync::applySettings()).

ALTER TABLE `media`
    ADD COLUMN `orientation` VARCHAR(16) NULL DEFAULT NULL
        AFTER `height`;

UPDATE `media`
SET `orientation` = CASE
    WHEN `width` IS NULL OR `height` IS NULL OR `width` = `height` THEN 'square'
    WHEN `width` >= 2 * `height`                                    THEN 'panorama'
    WHEN `width`  >  `height`                                       THEN 'landscape'
    ELSE                                                                 'portrait'
END
WHERE `orientation` IS NULL;

ALTER TABLE `media`
    MODIFY COLUMN `orientation` VARCHAR(16) NOT NULL;
