{"-- Chat system migration: threads, participants, and triggers

-- 1) Create chat_threads table if it does not exist
CREATE TABLE IF NOT EXISTS public.chat_threads (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  name text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
)","-- Enable RLS on chat_threads
ALTER TABLE public.chat_threads ENABLE ROW LEVEL SECURITY","-- Super admin can manage all chat threads
DROP POLICY IF EXISTS \"Super admin can manage all chat threads\" ON public.chat_threads","CREATE POLICY \"Super admin can manage all chat threads\"
ON public.chat_threads
FOR ALL
USING (is_current_user_super_admin())
WITH CHECK (is_current_user_super_admin())","-- Participants can view their threads
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 (
  EXISTS (
    SELECT 1 FROM public.chat_thread_participants p
    WHERE p.thread_id = chat_threads.id AND p.user_id = auth.uid()
  )
)","-- Org members can create threads in their org
DROP POLICY IF EXISTS \"Org members can create threads in their org\" ON public.chat_threads","CREATE POLICY \"Org members can create threads in their org\"
ON public.chat_threads
FOR INSERT
WITH CHECK (
  organization_id IN (
    SELECT organization_id FROM public.user_roles WHERE user_id = auth.uid()
  )
)","-- Participants can update their threads (e.g., name)
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 (
  EXISTS (
    SELECT 1 FROM public.chat_thread_participants p
    WHERE p.thread_id = chat_threads.id AND p.user_id = auth.uid()
  )
)","-- Auto-update updated_at on chat_threads
DROP TRIGGER IF EXISTS set_updated_at_chat_threads ON public.chat_threads","CREATE TRIGGER set_updated_at_chat_threads
BEFORE UPDATE ON public.chat_threads
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()","-- 2) Create chat_thread_participants table if it does not exist
CREATE TABLE IF NOT EXISTS public.chat_thread_participants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  thread_id uuid NOT NULL,
  user_id uuid NOT NULL,
  added_at timestamptz NOT NULL DEFAULT now()
)","-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_chat_participants_thread ON public.chat_thread_participants(thread_id)","CREATE INDEX IF NOT EXISTS idx_chat_participants_user ON public.chat_thread_participants(user_id)","-- Enable RLS on chat_thread_participants
ALTER TABLE public.chat_thread_participants ENABLE ROW LEVEL SECURITY","-- Super admin can manage all participants
DROP POLICY IF EXISTS \"Super admin can manage all participants\" ON public.chat_thread_participants","CREATE POLICY \"Super admin can manage all participants\"
ON public.chat_thread_participants
FOR ALL
USING (is_current_user_super_admin())
WITH CHECK (is_current_user_super_admin())","-- Participants can view participants in their threads
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 (
  EXISTS (
    SELECT 1 FROM public.chat_thread_participants p2
    WHERE p2.thread_id = chat_thread_participants.thread_id AND p2.user_id = auth.uid()
  )
)","-- Org members can add participants to org threads (allows creator to add others)
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 (
  EXISTS (
    SELECT 1
    FROM public.chat_threads t
    JOIN public.user_roles ur ON ur.organization_id = t.organization_id
    WHERE t.id = chat_thread_participants.thread_id
      AND ur.user_id = auth.uid()
  )
)","-- 3) Ensure trigger to bump thread.updated_at when a new message is inserted
DROP TRIGGER IF EXISTS touch_thread_on_message_insert ON public.chat_messages","CREATE TRIGGER touch_thread_on_message_insert
AFTER INSERT ON public.chat_messages
FOR EACH ROW
EXECUTE FUNCTION public.touch_chat_thread()"}