-- Per-user, per-(type, channel) opt-out overrides for notifications.
--
-- Default for every (type, channel) is ENABLED — only the deviations live
-- here. A row's mere presence means "the user actively flipped this knob";
-- absence means "use the registry default".
--
-- Channels currently understood by the engine:
--   - `inApp` : show in `GET /api/users/me/notifications`. Disabling this
--               does NOT prevent persistence (the audit row is still
--               written) — it filters the listing endpoint only.
--   - `push`  : forward to OneSignal during the digest cron flush.
--
-- Composite PK enforces uniqueness of the override triple and gives MySQL
-- the perfect index for the lookup `WHERE user_id = ? AND (type, channel) IN (...)`.

CREATE TABLE `user_notification_preference` (
    `user_id`    BINARY(16)   NOT NULL,
    `type`       VARCHAR(64)  NOT NULL,
    `channel`    VARCHAR(16)  NOT NULL,
    `enabled`    TINYINT(1)   NOT NULL,
    `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `type`, `channel`),
    CONSTRAINT `fk_notif_pref_user`
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
