acmcc/supabase/migrations/20260320211221_f7b5e2ac-7d6d-409d-9168-47626d74d3ae.sql

CREATE TABLE public.support_chats (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  association_id uuid REFERENCES public.associations(id) ON DELETE CASCADE,
  status text NOT NULL DEFAULT 'open',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.support_chat_messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  chat_id uuid NOT NULL REFERENCES public.support_chats(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'user',
  content text NOT NULL,
  escalated boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.support_chats ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.support_chat_messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own support chats" ON public.support_chats
  FOR ALL TO authenticated USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users can read own chat messages" ON public.support_chat_messages
  FOR SELECT TO authenticated
  USING (chat_id IN (SELECT id FROM public.support_chats WHERE user_id = auth.uid()));

CREATE POLICY "Users can insert own chat messages" ON public.support_chat_messages
  FOR INSERT TO authenticated
  WITH CHECK (chat_id IN (SELECT id FROM public.support_chats WHERE user_id = auth.uid()));

CREATE POLICY "Admins can read all support chats" ON public.support_chats
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Admins can read all chat messages" ON public.support_chat_messages
  FOR SELECT TO authenticated USING (
    chat_id IN (SELECT id FROM public.support_chats)
    AND public.has_role(auth.uid(), 'admin')
  );