{"-- 1) Tables for chat system
-- Create chat threads
CREATE TABLE IF NOT EXISTS public.chat_threads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  created_by UUID,
  name TEXT,
  is_private BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)","-- Create participants
CREATE TABLE IF NOT EXISTS public.chat_thread_participants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  thread_id UUID NOT NULL REFERENCES public.chat_threads(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(thread_id, user_id)
)","-- Create messages
CREATE TABLE IF NOT EXISTS public.chat_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  thread_id UUID NOT NULL REFERENCES public.chat_threads(id) ON DELETE CASCADE,
  organization_id UUID NOT NULL,
  sender_id UUID NOT NULL,
  content TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
)","-- Indexes
CREATE INDEX IF NOT EXISTS idx_chat_messages_thread_created ON public.chat_messages(thread_id, created_at)","CREATE INDEX IF NOT EXISTS idx_chat_participants_thread ON public.chat_thread_participants(thread_id)","CREATE INDEX IF NOT EXISTS idx_chat_threads_org ON public.chat_threads(organization_id)","-- Updated_at trigger for chat_threads
CREATE OR REPLACE FUNCTION public.update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql","DROP TRIGGER IF EXISTS trg_chat_threads_updated_at ON public.chat_threads","CREATE TRIGGER trg_chat_threads_updated_at
BEFORE UPDATE ON public.chat_threads
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()","-- Touch thread when a new message arrives
CREATE OR REPLACE FUNCTION public.touch_chat_thread()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  UPDATE public.chat_threads SET updated_at = now() WHERE id = NEW.thread_id;
  RETURN NEW;
END;
$$","DROP TRIGGER IF EXISTS trg_touch_thread_after_message_insert ON public.chat_messages","CREATE TRIGGER trg_touch_thread_after_message_insert
AFTER INSERT ON public.chat_messages
FOR EACH ROW
EXECUTE FUNCTION public.touch_chat_thread()","-- Enable RLS
ALTER TABLE public.chat_threads ENABLE ROW LEVEL SECURITY","ALTER TABLE public.chat_thread_participants ENABLE ROW LEVEL SECURITY","ALTER TABLE public.chat_messages ENABLE ROW LEVEL SECURITY","-- Policies for 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())","DROP POLICY IF EXISTS \"Users can view threads they participate in\" ON public.chat_threads","CREATE POLICY \"Users can view threads they participate in\"
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()
  )
)","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 ur.organization_id FROM public.user_roles ur WHERE ur.user_id = auth.uid()
  )
)","-- Policies for chat_thread_participants
DROP POLICY IF EXISTS \"Super admin can manage participants\" ON public.chat_thread_participants","CREATE POLICY \"Super admin can manage participants\"
ON public.chat_thread_participants
FOR ALL
USING (is_current_user_super_admin())
WITH CHECK (is_current_user_super_admin())","DROP POLICY IF EXISTS \"Participants can view participants of their threads\" ON public.chat_thread_participants","CREATE POLICY \"Participants can view participants of their threads\"
ON public.chat_thread_participants
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.chat_thread_participants self
    WHERE self.thread_id = chat_thread_participants.thread_id AND self.user_id = auth.uid()
  )
)","DROP POLICY IF EXISTS \"Org members can add participants to their org threads\" ON public.chat_thread_participants","CREATE POLICY \"Org members can add participants to their 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()
  )
)","-- Policies for chat_messages
DROP POLICY IF EXISTS \"Super admin can view and manage all messages\" ON public.chat_messages","CREATE POLICY \"Super admin can view and manage all messages\"
ON public.chat_messages
FOR ALL
USING (is_current_user_super_admin())
WITH CHECK (is_current_user_super_admin())","DROP POLICY IF EXISTS \"Participants can view messages in their threads\" ON public.chat_messages","CREATE POLICY \"Participants can view messages in their threads\"
ON public.chat_messages
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.chat_thread_participants p
    WHERE p.thread_id = chat_messages.thread_id AND p.user_id = auth.uid()
  )
)","DROP POLICY IF EXISTS \"Participants can send messages to their threads\" ON public.chat_messages","CREATE POLICY \"Participants can send messages to their threads\"
ON public.chat_messages
FOR INSERT
WITH CHECK (
  sender_id = auth.uid()
  AND EXISTS (
    SELECT 1 FROM public.chat_thread_participants p
    WHERE p.thread_id = chat_messages.thread_id AND p.user_id = auth.uid()
  )
  AND organization_id = (
    SELECT t.organization_id FROM public.chat_threads t WHERE t.id = chat_messages.thread_id
  )
)"}