-- Create custom_variables table for user-defined template variables
CREATE TABLE public.custom_variables (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
  variable_name TEXT NOT NULL,
  display_label TEXT NOT NULL,
  default_value TEXT DEFAULT '',
  description TEXT,
  category TEXT DEFAULT 'general',
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(association_id, variable_name)
);

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

-- Staff (admin, manager, employee) full access
CREATE POLICY "Staff full access on custom_variables"
ON public.custom_variables
FOR ALL
TO authenticated
USING (
  public.has_role(auth.uid(), 'admin'::public.app_role)
  OR public.has_role(auth.uid(), 'manager'::public.app_role)
  OR public.has_role(auth.uid(), 'employee'::public.app_role)
)
WITH CHECK (
  public.has_role(auth.uid(), 'admin'::public.app_role)
  OR public.has_role(auth.uid(), 'manager'::public.app_role)
  OR public.has_role(auth.uid(), 'employee'::public.app_role)
);

-- Board members can read variables for their associations
CREATE POLICY "Board members read custom_variables"
ON public.custom_variables
FOR SELECT
TO authenticated
USING (
  association_id IN (SELECT public.get_user_association_ids())
);

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