-- @database: bo
--
-- Per-media daily engagement rollup.
--
-- Sibling of `media_country_daily` (cf.
-- `2026_06_21_140000_create_media_country_daily.sql`) but the dimension is the
-- MEDIA, not the country: one FLOW time-series per media, five measures per
-- (media, day). Lets the back-office draw an engagement curve for ONE media
-- over time — impossible from `hxa.media_stats`, which only keeps the running
-- LIFETIME total (no per-day breakdown).
--
-- Lives in `hxa_bo`: like every rollup, the worker
-- (`bin/media-engagement-rollup.php`) is the ONLY writer. It READS the
-- authoritative per-day sources on `hxa` off-peak and UPSERTs here:
--   - `views` / `impressions` ← `hxa.media_view_daily` (already pre-aggregated
--     per (media, day) by the counter flush pipeline — near-zero read cost);
--   - `likes` / `dislikes`    ← `hxa.media_reaction`  GROUP BY media, DATE(created_at);
--   - `comments`              ← `hxa.media_comment`   GROUP BY media, DATE(created_at),
--                                non-deleted only.
-- Read by `GET /admin/media/{hex}/trends`. Application/request code MUST NOT
-- write here.
--
-- No cross-database FK to `hxa.media` (impossible from `hxa_bo`, same as
-- `media_country_daily`): an orphan row only means the media was deleted and
-- the retention purge will drop it within RETENTION_DAYS anyway.
--
-- FLOW semantics: a value is "events that happened on that day". Missing days
-- mean zero and are zero-filled at read time, never stored. Caveat on
-- likes/dislikes: an un-like DELETEs its `media_reaction` row, so the daily
-- count reflects reactions still alive that were created that day (same-day
-- cancellations are undercounted). Lifetime totals stay exact via
-- `media_stats` and are surfaced separately by the endpoint.
--
-- Retention: the worker also PURGEs rows older than
-- `MEDIA_ENGAGEMENT_RETENTION_DAYS` (default ~1 year) via a bounded
-- `DELETE ... WHERE day < :cutoff`, backed by `idx_day`, so the table stays
-- small and every read/scan stays fast in perpetuity.

CREATE TABLE `media_engagement_daily` (
    `media_id`    BINARY(16)      NOT NULL,
    `day`         DATE            NOT NULL,
    `views`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `likes`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `dislikes`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `comments`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- (media, day): one time-series per media, tight range scan on a single one
    -- (`WHERE media_id = ? AND day BETWEEN ? AND ?`).
    PRIMARY KEY (`media_id`, `day`),
    -- (day): bounded retention purge (`DELETE WHERE day < :cutoff`).
    KEY `idx_day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
