-- Collaborative documents table
CREATE TABLE public.collaborative_documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  association_id uuid REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
  title text NOT NULL,
  content text DEFAULT '',
  created_by uuid NOT NULL,
  locked_by uuid DEFAULT NULL,
  locked_at timestamptz DEFAULT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Edit history log
CREATE TABLE public.document_edit_history (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id uuid REFERENCES public.collaborative_documents(id) ON DELETE CASCADE NOT NULL,
  user_id uuid NOT NULL,
  user_name text NOT NULL,
  user_color text NOT NULL DEFAULT '#3b82f6',
  change_summary text,
  content_snapshot text,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.collaborative_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.document_edit_history ENABLE ROW LEVEL SECURITY;

-- Staff can do anything
CREATE POLICY "Staff full access to collaborative_documents"
  ON public.collaborative_documents FOR ALL TO authenticated
  USING (
    public.has_role(auth.uid(), 'admin') OR
    public.has_role(auth.uid(), 'manager') OR
    public.has_role(auth.uid(), 'employee')
  );

-- Board members can access docs for their associations
CREATE POLICY "Board members access collaborative_documents"
  ON public.collaborative_documents FOR ALL TO authenticated
  USING (association_id IN (SELECT public.get_user_association_ids()));

-- Staff full access to edit history
CREATE POLICY "Staff full access to document_edit_history"
  ON public.document_edit_history FOR ALL TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.collaborative_documents d
      WHERE d.id = document_id AND (
        public.has_role(auth.uid(), 'admin') OR
        public.has_role(auth.uid(), 'manager') OR
        public.has_role(auth.uid(), 'employee')
      )
    )
  );

-- Board members access edit history for their association docs
CREATE POLICY "Board members access document_edit_history"
  ON public.document_edit_history FOR ALL TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.collaborative_documents d
      WHERE d.id = document_id AND d.association_id IN (SELECT public.get_user_association_ids())
    )
  );

-- Enable realtime for edit history
ALTER PUBLICATION supabase_realtime ADD TABLE public.document_edit_history;
ALTER PUBLICATION supabase_realtime ADD TABLE public.collaborative_documents;

-- Updated_at trigger
CREATE TRIGGER update_collaborative_documents_updated_at
  BEFORE UPDATE ON public.collaborative_documents
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();