{"-- Fix RLS recursion by using SECURITY DEFINER helper functions

-- 1) Helper: check if current user participates in a thread
CREATE OR REPLACE FUNCTION public.user_is_thread_participant(thread_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.chat_thread_participants p
    WHERE p.thread_id = user_is_thread_participant.thread_id
      AND p.user_id = auth.uid()
  );
$$","-- 2) Helper: check if current user is org member for thread's org
CREATE OR REPLACE FUNCTION public.user_is_org_member_for_thread(thread_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.chat_threads t
    JOIN public.user_roles ur ON ur.organization_id = t.organization_id
    WHERE t.id = user_is_org_member_for_thread.thread_id
      AND ur.user_id = auth.uid()
  );
$$","-- 3) Update chat_threads policies to use helper functions
DROP POLICY IF EXISTS \"Participants can view their threads\" ON public.chat_threads","CREATE POLICY \"Participants can view their threads\"
ON public.chat_threads
FOR SELECT
USING (public.user_is_thread_participant(chat_threads.id))","DROP POLICY IF EXISTS \"Participants can update their threads\" ON public.chat_threads","CREATE POLICY \"Participants can update their threads\"
ON public.chat_threads
FOR UPDATE
USING (public.user_is_thread_participant(chat_threads.id))","-- 4) Update chat_thread_participants policies to use helper functions
DROP POLICY IF EXISTS \"Participants can view participants in their threads\" ON public.chat_thread_participants","CREATE POLICY \"Participants can view participants in their threads\"
ON public.chat_thread_participants
FOR SELECT
USING (public.user_is_thread_participant(chat_thread_participants.thread_id))","DROP POLICY IF EXISTS \"Org members can add participants to org threads\" ON public.chat_thread_participants","CREATE POLICY \"Org members can add participants to org threads\"
ON public.chat_thread_participants
FOR INSERT
WITH CHECK (public.user_is_org_member_for_thread(chat_thread_participants.thread_id))"}