-- Backs the admin `POST /admin/media/describe` endpoint, which ingests the
-- AI-pipeline enrichment payload (flag, focus, objects, title/meta_title/
-- description) for one media.
--
-- 1. `media.flag` — moderation bitmask set by the AI worker. Bits:
--      0  valid          (no flag)
--      1  illegal
--      2  violent
--      4  sexual
--      8  selfie
--      16 screenshot
--      32 AI-generated
--    Several bits can combine (e.g. 12 = sexual + selfie). The value is the
--    rejection cause and is exposed in the API to the media OWNER only — but
--    it IS pushed to Meilisearch (filterable) so back-office tooling can slice
--    on it.
--
-- 2. `media.is_rejected` — denormalised decision: TRUE as soon as the media is
--    flagged for ANY reason OTHER than "selfie". We do NOT reject selfies for
--    now, so a pure selfie (flag = 8) stays published; a sexual selfie
--    (flag = 12) is rejected because bit 4 is set. Formula: (flag & ~8) > 0.
--    Kept as a stored column (not a generated one) so the application owns the
--    policy and can evolve the "which bits reject" rule without an ALTER.
--
-- 3. `media_description.title` / `meta_title` — the human title and the SEO
--    meta-title produced alongside the existing free-form `description`.
--
-- 4. `media_object` — the detected objects with their probability (0..1). One
--    row per (media, object name); replaced wholesale on each describe call.
--
-- `media_focus` and `focus` already exist (seeded reference set) so this
-- migration does NOT touch them — the endpoint just resolves focus names to
-- ids and fills `media_focus`.

ALTER TABLE `media`
    ADD COLUMN `flag`        TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `status`,
    ADD COLUMN `is_rejected` TINYINT(1)       NOT NULL DEFAULT 0 AFTER `flag`;

-- Lets back-office listings find rejected media cheaply without scanning.
CREATE INDEX `idx_media_is_rejected` ON `media` (`is_rejected`);

ALTER TABLE `media_description`
    ADD COLUMN `title`      VARCHAR(250) NULL DEFAULT NULL AFTER `media_id`,
    ADD COLUMN `meta_title` VARCHAR(255) NULL DEFAULT NULL AFTER `title`;

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