{"-- Create groups table for organizing users
CREATE TABLE public.user_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  organization_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  group_type TEXT NOT NULL DEFAULT 'custom', -- 'custom', 'branch', 'department'
  metadata JSONB DEFAULT '{}',
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)","-- Create group members junction table
CREATE TABLE public.user_group_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.user_groups(id) ON DELETE CASCADE,
  user_role_id UUID REFERENCES public.user_roles(id) ON DELETE CASCADE,
  added_by UUID,
  added_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(group_id, user_role_id)
)","-- Create form distribution settings table
CREATE TABLE public.form_distribution_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_form_id UUID REFERENCES public.workflow_forms(id) ON DELETE CASCADE,
  distribution_type TEXT NOT NULL, -- 'users', 'groups', 'both'
  target_groups JSONB DEFAULT '[]', -- array of group IDs
  target_users JSONB DEFAULT '[]', -- array of user_role IDs
  schedule_type TEXT DEFAULT 'immediate', -- 'immediate', 'scheduled', 'recurring'
  schedule_data JSONB DEFAULT '{}',
  notification_settings JSONB DEFAULT '{\"email\": true, \"push\": true}',
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)","-- Enable RLS
ALTER TABLE public.user_groups ENABLE ROW LEVEL SECURITY","ALTER TABLE public.user_group_members ENABLE ROW LEVEL SECURITY","ALTER TABLE public.form_distribution_settings ENABLE ROW LEVEL SECURITY","-- RLS Policies for user_groups
CREATE POLICY \"Users can view groups in their organization\"
ON public.user_groups FOR SELECT
USING (EXISTS (
  SELECT 1 FROM user_roles ur 
  WHERE ur.user_id = auth.uid() 
  AND ur.organization_id = user_groups.organization_id
))","CREATE POLICY \"Admins and managers can manage groups\"
ON public.user_groups FOR ALL
USING (EXISTS (
  SELECT 1 FROM user_roles ur 
  WHERE ur.user_id = auth.uid() 
  AND ur.organization_id = user_groups.organization_id 
  AND ur.role IN ('admin', 'manager')
))","-- RLS Policies for user_group_members
CREATE POLICY \"Users can view group members in their organization\"
ON public.user_group_members FOR SELECT
USING (EXISTS (
  SELECT 1 FROM user_groups ug
  JOIN user_roles ur ON ur.organization_id = ug.organization_id
  WHERE ug.id = user_group_members.group_id
  AND ur.user_id = auth.uid()
))","CREATE POLICY \"Admins and managers can manage group members\"
ON public.user_group_members FOR ALL
USING (EXISTS (
  SELECT 1 FROM user_groups ug
  JOIN user_roles ur ON ur.organization_id = ug.organization_id
  WHERE ug.id = user_group_members.group_id
  AND ur.user_id = auth.uid()
  AND ur.role IN ('admin', 'manager')
))","-- RLS Policies for form_distribution_settings
CREATE POLICY \"Users can view form distribution in their organization\"
ON public.form_distribution_settings FOR SELECT
USING (EXISTS (
  SELECT 1 FROM workflow_forms wf
  WHERE wf.id = form_distribution_settings.workflow_form_id
))","CREATE POLICY \"Super admin can manage form distribution\"
ON public.form_distribution_settings FOR ALL
USING (is_current_user_super_admin())","-- Add triggers for updated_at
CREATE TRIGGER update_user_groups_updated_at
  BEFORE UPDATE ON public.user_groups
  FOR EACH ROW
  EXECUTE FUNCTION public.update_modified_column()","CREATE TRIGGER update_form_distribution_settings_updated_at
  BEFORE UPDATE ON public.form_distribution_settings
  FOR EACH ROW
  EXECUTE FUNCTION public.update_modified_column()"}