-- Persistent notification feed for end users.
--
-- Polymorphic shape: every event type writes a row with a stable `type` (e.g.
-- `follow.received`) and a frozen JSON `data` payload that captures the
-- context at write time. The localized title/body are rendered at read time
-- by the application using `Translator` against keys
-- `notifications.<type>.title|body`, so a later rename of the actor's
-- username does NOT rewrite history.
--
-- Lifecycle flags:
--   - `read_at`   : when the recipient marked it as read (NULL while unread).
--   - `pushed_at` : when the cron digest pushed it to OneSignal (NULL while
--                   still waiting in the queue). Decoupling this from
--                   `created_at` is what lets us batch several pending
--                   notifications into a single digest push.
--
-- Soft-dedup (g2): on dispatch the service looks for a recent
-- `(user_id, dedup_key)` non-read row inside the dedup window — if present,
-- it BUMPs `created_at` instead of inserting a duplicate.

CREATE TABLE `notification` (
    `id`         BINARY(16)   NOT NULL,
    `user_id`    BINARY(16)   NOT NULL,
    `type`       VARCHAR(64)  NOT NULL,
    `data`       JSON         NOT NULL,
    `dedup_key`  VARCHAR(128) NULL,
    `read_at`    DATETIME     NULL,
    `pushed_at`  DATETIME     NULL,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_notif_user_created` (`user_id`, `created_at`),
    KEY `idx_notif_user_read`    (`user_id`, `read_at`),
    KEY `idx_notif_user_dedup`   (`user_id`, `dedup_key`, `read_at`),
    KEY `idx_notif_pushed`       (`pushed_at`, `user_id`),
    CONSTRAINT `fk_notif_user`
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
