-- =============================================================================
-- Personal notification channels (Phase 9 — CHAN-01)
-- =============================================================================
-- Adds owner_user_id to notification_channels so a row can be either:
--   - Global (owner_user_id IS NULL)         — existing rows, admin-managed
--   - Personal (owner_user_id = "user".id)   — created via /api/me/channels
--
-- Cascade delete: removing a Better Auth user removes their personal channels.
--
-- Defense-in-depth: a partial unique index enforces one-personal-channel-per-
-- type-per-user at the DB layer (D-02 / CHAN-02). The API-layer WITH-CTE UPSERT
-- in Plan 02 still has a small race window for concurrent saves; this index
-- closes that gap. The WHERE clause keeps existing global rows (NULL owner)
-- exempt — multiple global rows of the same type can still coexist.
-- =============================================================================

ALTER TABLE notification_channels
  ADD COLUMN IF NOT EXISTS owner_user_id TEXT
    REFERENCES "user"(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_notification_channels_owner
  ON notification_channels(owner_user_id, channel_type)
  WHERE owner_user_id IS NOT NULL;

-- One personal channel per (owner, type). Globals (NULL owner) unaffected.
CREATE UNIQUE INDEX IF NOT EXISTS notification_channels_owner_user_id_channel_type_uniq
  ON notification_channels (owner_user_id, channel_type)
  WHERE owner_user_id IS NOT NULL;

COMMENT ON COLUMN notification_channels.owner_user_id IS
  'Personal channel owner. NULL = global channel (admin-managed). NOT NULL = personal channel reachable only by the owner and admins.';