-- View + impression counter pipeline for media.
--
-- Design goals:
--   - Robust under high volume WITHOUT Redis.
--   - Non-blocking on the hot path: HTTP handlers buffer events in-process
--     and flush them as a single multi-row INSERT into `media_counter_event`
--     (append-only tampon), typically via `register_shutdown_function()` so
--     the user already has their response when the write happens.
--   - A dedicated worker (`bin/media-counters-flush.php`, tick driven by
--     `MEDIA_COUNTERS_FLUSH_TICK_SECONDS`) drains the tampon in batches,
--     UPDATEs `media_stats` and UPSERTs `media_view_daily`, then deletes the
--     processed event ids in the same transaction.
--   - 1-hour view dedup per (media, viewer) is enforced by INSERT IGNORE on
--     `media_view_dedup`. The viewer key is a SHA-256 of `(user_hex | ip)` so
--     the dedup table holds no raw PII.
--
-- Counter columns on `media_stats`:
--   - `views_count` already exists (INT UNSIGNED) but was never incremented —
--     we widen it to BIGINT UNSIGNED now because the same column will hold the
--     running total for years and INT (max 4.29B) is borderline at scale.
--   - `impressions_count` is added now (BIGINT UNSIGNED). Impressions are
--     emitted once per collection serialization (every media in a search/list
--     result gets +1) so the column grows ~10× faster than `views_count`.

ALTER TABLE `media_stats`
    MODIFY `views_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ADD    `impressions_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER `views_count`;

-- Tampon (append-only). Each row carries a pre-aggregated `delta` so a single
-- HTTP request flushes at most one row per (media_id, kind) — not one per
-- event. AUTO_INCREMENT id powers a stable drain order (FIFO) and gives the
-- worker a cheap `WHERE id <= :high_water` cut point.
CREATE TABLE `media_counter_event` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `media_id`   BINARY(16)                  NOT NULL,
    `kind`       ENUM('view','impression')   NOT NULL,
    `delta`      INT UNSIGNED                NOT NULL DEFAULT 1,
    `created_at` DATETIME(3)                 NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 1-hour rolling view dedup. The PK is the dedup contract:
-- `(media_id, viewer_key, hour_bucket)` collapses repeat hits inside the same
-- hour bucket to a single row. INSERT IGNORE → no error path on the hot path.
-- The `idx_gc` index lets the flush worker garbage-collect rows older than
-- `MEDIA_COUNTERS_VIEW_DEDUP_WINDOW_SECONDS * 2` (kept twice the window so
-- pre-flush events still see their dedup mate).
CREATE TABLE `media_view_dedup` (
    `media_id`    BINARY(16) NOT NULL,
    `viewer_key`  BINARY(32) NOT NULL,
    `hour_bucket` DATETIME   NOT NULL,
    PRIMARY KEY (`media_id`, `viewer_key`, `hour_bucket`),
    KEY `idx_gc` (`hour_bucket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daily aggregate per (media, day). Powers trending re-rank (Meili) and
-- long-term analytics queries. The flush worker UPSERTs this in the same
-- transaction as the `media_stats` UPDATE so the daily series and the
-- lifetime total cannot drift.
CREATE TABLE `media_view_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,
    PRIMARY KEY (`media_id`, `day`),
    KEY `idx_day_views`       (`day`, `views`),
    KEY `idx_day_impressions` (`day`, `impressions`),
    CONSTRAINT `fk_mvd_media` FOREIGN KEY (`media_id`) REFERENCES `media`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
