-- ===========================================================================
-- Hydrogen consolidated migrations — database: hxa (key: main)
-- Generated: 2026-07-10T07:18:05+00:00
-- Delta since watermark: 2026_07_10_120000
-- Migrations bundled: 1
--
-- Run this whole file on the `hxa` database, then on the project run
--   php bin/migrations-bundle.php --mark
-- and delete this file. No `USE` is emitted on purpose — pick the database
-- yourself (phpMyAdmin selection or `mysql hxa < main.sql`).
-- ===========================================================================

-- ---------------------------------------------------------------------------
-- 2026_07_10_130000_add_scope_to_user_notification_preference.sql
-- ---------------------------------------------------------------------------
-- Generalize `user_notification_preference` from a flat (type, channel) opt-out
-- store into a three-tier (scope, key, channel) cascade.
--
-- The engine now resolves a notification through three AND-strict tiers:
--   effective(type, channel) = global(channel)
--                          AND category(type.category, channel)
--                          AND type(type, channel)
-- Each tier defaults to ENABLED; only deviations are stored.
--
-- Storage identity is `(scope, key, channel)`:
--   - scope='global'   , key='*'                  → per-channel master switch
--   - scope='category' , key=<category slug>      → social / progression / system
--   - scope='type'     , key=<notification type>  → e.g. follow.received
--
-- Existing rows were `(type, channel)` overrides at the type tier, so they are
-- migrated verbatim to `scope='type'` with the old `type` value becoming `key`.

ALTER TABLE `user_notification_preference`
    DROP PRIMARY KEY,
    ADD COLUMN `scope` VARCHAR(16) NOT NULL DEFAULT 'type' AFTER `user_id`,
    CHANGE COLUMN `type` `key` VARCHAR(64) NOT NULL,
    ADD PRIMARY KEY (`user_id`, `scope`, `key`, `channel`);

-- Drop the transitional default now that legacy rows are backfilled; new rows
-- always specify an explicit scope.
ALTER TABLE `user_notification_preference`
    ALTER COLUMN `scope` DROP DEFAULT;

