CREATE TABLE public.arc_committee_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT,
  notes TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_arc_committee_members_assoc ON public.arc_committee_members(association_id);

ALTER TABLE public.arc_committee_members ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff and association members can view ARC committee"
  ON public.arc_committee_members FOR SELECT
  USING (public.user_belongs_to_association(auth.uid(), association_id));

CREATE POLICY "Admins and managers can insert ARC committee"
  ON public.arc_committee_members FOR INSERT
  WITH CHECK (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));

CREATE POLICY "Admins and managers can update ARC committee"
  ON public.arc_committee_members FOR UPDATE
  USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));

CREATE POLICY "Admins and managers can delete ARC committee"
  ON public.arc_committee_members FOR DELETE
  USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));

CREATE TRIGGER update_arc_committee_members_updated_at
  BEFORE UPDATE ON public.arc_committee_members
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();