-- Per-click identity for affiliate/partner links (subid / clickref attribution).
--
-- The base tracking pipeline (2026_06_16) counts clicks at the TARGET level
-- (offer) — good enough for volume, but it cannot answer "WHO clicked, and from
-- WHICH media?". This table fills that gap without touching the high-volume
-- buffered counter path.
--
-- Flow:
--   1. A partner/outbound link is rendered through `/go/offer/{id}/media/{mediaId}`.
--   2. The redirect mints an opaque `click_ref` (UUIDv7 hex, time-ordered for
--      index locality), persists ONE row here mapping the ref to the visited
--      media (mandatory), the authenticated user (nullable — anonymous clicks
--      are first-class) and an optional long-lived visitor id (cookie), then
--      appends the ref to the merchant URL as the network `subid`.
--   3. The affiliate network echoes that subid back in its conversion postback
--      (`POST /admin/tracking/conversions` with `clickRef`), letting us tie the
--      money to the exact click → media → user.
--
-- Only the opaque `click_ref` ever leaves our system: no userId / PII is exposed
-- to the partner. `media_id` is public, so it MAY also be passed as a secondary
-- subid for the partner dashboard, but attribution always rides on `click_ref`.
--
-- Volume note: this is one INSERT per outbound partner click — heavier than the
-- buffered counter, but partner clicks are a small fraction of all traffic and
-- the row is written synchronously before the 302 (it must exist before the
-- visitor can possibly convert).
CREATE TABLE `tracking_click` (
    `click_ref`   CHAR(32)    NOT NULL,                                  -- UUIDv7 hex, the network subid
    `target_type` VARCHAR(32) NOT NULL,                                  -- polymorphic, e.g. 'offer'
    `target_id`   VARCHAR(64) NOT NULL,                                  -- e.g. 32-hex offer id
    `media_id`    CHAR(32)    NOT NULL,                                  -- 32-hex media id (mandatory context)
    `user_id`     BINARY(16)  NULL,                                      -- NULL = anonymous click
    `visitor_id`  CHAR(32)    NULL,                                      -- long-lived visitor cookie (UUIDv7 hex)
    `created_at`  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`click_ref`),
    KEY `idx_media`   (`media_id`),
    KEY `idx_user`    (`user_id`),
    KEY `idx_visitor` (`visitor_id`),
    KEY `idx_target`  (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Link a conversion back to the precise click that earned it. NULL for legacy /
-- target-level postbacks that carry no subid. Not unique: one click can in
-- theory yield several conversions (e.g. multi-item orders the network splits).
ALTER TABLE `tracking_conversion`
    ADD COLUMN `click_ref` CHAR(32) NULL DEFAULT NULL AFTER `external_ref`,
    ADD KEY `idx_click_ref` (`click_ref`);
