-- Users can only see their own notifications
CREATE POLICY "notification_deliveries_select_policy"
  ON public.notification_deliveries FOR SELECT
  USING (recipient_user_id = auth.uid());

-- Users can update their own notification status
CREATE POLICY "notification_deliveries_update_policy"
  ON public.notification_deliveries FOR UPDATE
  USING (recipient_user_id = auth.uid());

CREATE OR REPLACE FUNCTION public.create_notification_event(
  p_category text,
  p_actor_user_id uuid,
  p_title text,
  p_body text,
  p_data jsonb DEFAULT '{}'::jsonb,
  p_recipient_user_ids uuid[] DEFAULT NULL,
  p_channel_mask integer DEFAULT 3
)
RETURNS uuid

const { data, error } = await supabase.rpc('create_notification_event', {
  p_category: 'assignments',
  p_actor_user_id: currentUserId,
  p_title: 'New Assignment',
  p_body: `You have been assigned to Ticket #${ticketId}`,
  p_data: { ticket_id: ticketId, url: `/tickets/${ticketId}` },
  p_recipient_user_ids: [assigneeUserId],
  p_channel_mask: 3 // Both in-app and push
});

CREATE OR REPLACE FUNCTION public.send_self_test_notification(
  p_title text DEFAULT 'Test Notification',
  p_body text DEFAULT 'This is a test notification',
  p_push_only boolean DEFAULT false
)
RETURNS uuid

CREATE TRIGGER notification_event_trigger
  AFTER INSERT ON public.notification_events
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_notification_event();

CREATE TRIGGER ticket_comment_notification_trigger
  AFTER INSERT ON public.ticket_comments
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_ticket_comment_added();

Attempt 1: Immediate (next_attempt_at = now)
Attempt 2: +30 seconds
Attempt 3: +60 seconds
Attempt 4: +120 seconds (max backoff)
Attempt 5+: +120 seconds (max backoff)
Max attempts: 5 (configurable via Edge Function env var)

CREATE VIEW public.v_notification_feed AS
SELECT
  d.id AS delivery_id,
  d.recipient_user_id,
  d.status,
  d.delivered_at,
  d.seen_at,
  d.read_at,
  e.id AS event_id,
  e.category,
  e.actor_user_id,
  e.title,
  e.body,
  e.data,
  e.created_at
FROM public.notification_deliveries d
JOIN public.notification_events e ON d.event_id = e.id
ORDER BY e.created_at DESC;

SELECT status, count(*)
FROM public.notification_outbox
GROUP BY status
ORDER BY status;

SELECT id, recipient_user_id, attempts, last_error, created_at
FROM public.notification_outbox
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 20;

SELECT id, endpoint, user_agent, created_at, last_used_at
FROM public.notification_push_subscriptions
WHERE user_id = 'user-uuid-here';

SELECT
  e.category,
  COUNT(DISTINCT d.id) AS total_deliveries,
  COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'read') AS read_count,
  COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'seen') AS seen_count
FROM public.notification_events e
JOIN public.notification_deliveries d ON e.id = d.event_id
WHERE e.created_at > now() - interval '7 days'
GROUP BY e.category;