-- ---------------------------------------------------------------
-- user_notifications: in-app alert center
-- Surfaces rare grades, cascade events, steam moves, morning results.
-- Read-tier RLS: a user can only ever see/update their own rows.
-- ---------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.user_notifications (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type        TEXT NOT NULL CHECK (type IN (
                'rare_grade', 'cascade', 'steam', 'morning_results',
                'line_movement', 'injury', 'system'
              )),
  title       TEXT NOT NULL,
  body        TEXT,
  link        TEXT,
  read        BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_notif_user_unread_created
  ON public.user_notifications (user_id, read, created_at DESC);

-- Trim noise: a soft cap of ~200 rows per user via periodic cleanup elsewhere.
CREATE INDEX IF NOT EXISTS idx_notif_user_created
  ON public.user_notifications (user_id, created_at DESC);

ALTER TABLE public.user_notifications ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users read own notifications" ON public.user_notifications;
CREATE POLICY "Users read own notifications"
  ON public.user_notifications
  FOR SELECT
  USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users update own notifications" ON public.user_notifications;
CREATE POLICY "Users update own notifications"
  ON public.user_notifications
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Inserts/deletes are server-only (service role bypasses RLS).
-- We do NOT grant INSERT/DELETE to anon or authenticated; the server inserts
-- system notifications and the user only reads/marks-read via the policies above.

GRANT SELECT, UPDATE ON public.user_notifications TO authenticated;