CREATE TABLE public.mail_messages (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  folder TEXT NOT NULL DEFAULT 'inbox' CHECK (folder IN ('inbox','sent')),
  from_email TEXT NOT NULL,
  to_emails TEXT[] NOT NULL DEFAULT '{}',
  subject TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  attachments JSONB NOT NULL DEFAULT '[]'::jsonb,
  read BOOLEAN NOT NULL DEFAULT false,
  starred BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.mail_messages TO authenticated;
GRANT ALL ON public.mail_messages TO service_role;
ALTER TABLE public.mail_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage their own mail" ON public.mail_messages FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE INDEX mail_messages_user_folder_idx ON public.mail_messages (user_id, folder, created_at DESC);

CREATE TABLE public.chatbud_conversations (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  session_id TEXT NOT NULL,
  user_id UUID REFERENCES auth.users ON DELETE SET NULL,
  title TEXT NOT NULL DEFAULT 'New conversation',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX chatbud_conversations_session_idx ON public.chatbud_conversations (session_id, updated_at DESC);
GRANT SELECT ON public.chatbud_conversations TO authenticated;
GRANT ALL ON public.chatbud_conversations TO service_role;
ALTER TABLE public.chatbud_conversations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins read chatbud conversations" ON public.chatbud_conversations FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

CREATE TABLE public.chatbud_messages (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  conversation_id UUID NOT NULL REFERENCES public.chatbud_conversations ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user','assistant')),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX chatbud_messages_conv_idx ON public.chatbud_messages (conversation_id, created_at);
GRANT SELECT ON public.chatbud_messages TO authenticated;
GRANT ALL ON public.chatbud_messages TO service_role;
ALTER TABLE public.chatbud_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins read chatbud messages" ON public.chatbud_messages FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Users read own mail attachments" ON storage.objects FOR SELECT TO authenticated USING (bucket_id = 'mail-attachments' AND auth.uid()::text = (storage.foldername(name))[1]);
CREATE POLICY "Users upload own mail attachments" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'mail-attachments' AND auth.uid()::text = (storage.foldername(name))[1]);
CREATE POLICY "Users delete own mail attachments" ON storage.objects FOR DELETE TO authenticated USING (bucket_id = 'mail-attachments' AND auth.uid()::text = (storage.foldername(name))[1]);