-- @database: main
--
-- Extends the AI-describe pipeline with the two enrichment fields the vision
-- model returns but that had no storage yet: an estimated head-count and the
-- recognised points of interest (monuments/landmarks).
--
-- 1. `media.person_count` — the model's estimate of how many people appear in
--    the image (0 when none). Small unsigned integer; nullable so "not
--    evaluated" (no describe run yet) stays distinct from an explicit 0.
--
-- 2. `media_poi` — the recognised points of interest, one row per (media, name)
--    with the model's confidence (0..1). Mirrors `media_object`'s shape and is
--    likewise replaced wholesale on each describe call.

ALTER TABLE `media`
    ADD COLUMN `person_count` SMALLINT UNSIGNED NULL DEFAULT NULL AFTER `is_rejected`;

CREATE TABLE `media_poi` (
    `media_id`    BINARY(16)    NOT NULL,
    `name`        VARCHAR(160)  NOT NULL,
    `probability` FLOAT         NOT NULL DEFAULT 0,
    `created_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`media_id`, `name`),
    CONSTRAINT `fk_media_poi_media`
        FOREIGN KEY (`media_id`) REFERENCES `media` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
