-- View + impression counter pipeline for user profiles.
--
-- Mirrors the media counters pipeline (cf.
-- `2026_06_13_120000_create_media_counters_pipeline.sql`) but targets the
-- `user` row instead of `media`. Same async architecture: HTTP handlers
-- buffer events in-process and flush them as a single multi-row INSERT into
-- `user_counter_event`; a dedicated worker (`bin/user-counters-flush.php`)
-- drains the tampon, UPDATEs `user_stats`, UPSERTs `user_view_daily`.
--
-- Why a SEPARATE pipeline (not a shared `counter_event` polymorphic table):
--   - The dedup contract is per-target: a viewer hitting User A and Media B
--     in the same hour MUST count both; a polymorphic dedup PK would need
--     `(target_type, target_id, viewer_key, hour_bucket)`, which is heavier
--     for no gain.
--   - The tampon drain is the worker's hot path. Keeping two narrow tables
--     keeps the worker SQL trivial and lets each one be sharded later
--     without changing the other.
--
-- Definitions:
--   - **view**       : a single user profile was loaded by a viewer. Hooks
--                      on any GET `/api/users/{userId}/*` sub-route (gallery,
--                      followers, badges, …). Dedup 1h per viewer.
--   - **impression** : a user surfaced in a discovery collection
--                      (`/api/users/search`). Every user in the result set
--                      gets `+1`. No dedup.
--
-- Counter columns added to `user_stats`:
--   - `num_views`       : lifetime views on this user's profile. BIGINT
--                         UNSIGNED — the existing counters use INT, but views
--                         grow ~100× faster than `num_user_follower`.
--   - `num_impressions` : lifetime impressions in discovery collections.

ALTER TABLE `user_stats`
    ADD `num_views`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ADD `num_impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0;

-- Tampon. Pre-aggregated `delta` so a request flushes at most one row per
-- (user_id, kind). Drained FIFO by autoincrement id by the worker; the
-- highest id of a batch is the high-water mark for `DELETE FROM ... WHERE id <= hw`.
CREATE TABLE `user_counter_event` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_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;

-- 1h rolling view dedup. PK is the contract: `(user_id, viewer_key, hour_bucket)`
-- collapses repeat hits inside the bucket. `INSERT IGNORE` → no error path on
-- the hot path. `viewer_key` is a SHA-256 digest of `u:<userHex>|ip:<ip>` (auth)
-- or `a:<ip>` (anon) so no raw PII lands here.
CREATE TABLE `user_view_dedup` (
    `user_id`     BINARY(16) NOT NULL,
    `viewer_key`  BINARY(32) NOT NULL,
    `hour_bucket` DATETIME   NOT NULL,
    PRIMARY KEY (`user_id`, `viewer_key`, `hour_bucket`),
    KEY `idx_gc` (`hour_bucket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daily aggregate per (user, day). Powers analytics ("top profiles this
-- week") without re-querying the lifetime totals. Owned exclusively by the
-- worker — application code MUST NOT write here.
CREATE TABLE `user_view_daily` (
    `user_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 (`user_id`, `day`),
    KEY `idx_day_views`       (`day`, `views`),
    KEY `idx_day_impressions` (`day`, `impressions`),
    CONSTRAINT `fk_uvd_user` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
