-- Per-event × per-channel notification preferences.
--
-- One row per (user, kind, channel) the user has turned OFF. Absence of a row
-- means enabled — the table stores opt-outs only, so new kinds/channels are
-- enabled for everyone with no backfill, and existing users keep receiving
-- everything they receive today.
--
-- `kind` is the notifications.kind machine string (follow_request, post_like,
-- chant_reminder, …); the Settings UI groups kinds into coarser categories
-- but storage stays kind-level so backfilled opt-outs map exactly.
CREATE TABLE IF NOT EXISTS notification_prefs (
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    kind       TEXT NOT NULL,
    channel    TEXT   NOT NULL CHECK (channel IN ('in_app', 'email', 'push')),
    enabled    BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, kind, channel)
);

-- Backfill the existing like-email opt-out at exactly its old granularity:
-- post_like × email only (NOT the whole post-interactions category, so these
-- users don't accidentally lose anything else).
INSERT INTO notification_prefs (user_id, kind, channel, enabled)
SELECT id, 'post_like', 'email', FALSE
FROM users WHERE like_emails_enabled = FALSE
ON CONFLICT DO NOTHING;

-- Backfill the reminder opt-out: the old toggle silenced all channels at
-- once, so it maps to every reminder kind × every channel.
INSERT INTO notification_prefs (user_id, kind, channel, enabled)
SELECT u.id, k.kind, c.channel, FALSE
FROM users u
CROSS JOIN (VALUES ('chant_reminder'), ('quote_reminder'), ('chant_incomplete_reminder')) AS k(kind)
CROSS JOIN (VALUES ('in_app'), ('email'), ('push')) AS c(channel)
WHERE u.sadhna_reminders_enabled = FALSE
ON CONFLICT DO NOTHING;

-- users.like_emails_enabled and users.sadhna_reminders_enabled stay for now:
-- the prefs endpoints dual-write them so an old frontend (or a rollback)
-- keeps working. They get dropped in a later migration once the new
-- endpoints are fully rolled out.