{"-- Create workflow forms table for system admin to create default forms
CREATE TABLE public.workflow_forms (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  name_ar TEXT NOT NULL,
  description TEXT,
  description_ar TEXT,
  activity_type TEXT NOT NULL,
  form_fields JSONB NOT NULL DEFAULT '[]'::jsonb,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by UUID,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- Create form assignments table for assigning forms to representatives
CREATE TABLE public.form_assignments (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  workflow_form_id UUID NOT NULL REFERENCES public.workflow_forms(id) ON DELETE CASCADE,
  organization_id UUID NOT NULL,
  representative_id UUID NOT NULL, -- user_roles.id
  assigned_by UUID,
  due_date TIMESTAMP WITH TIME ZONE,
  reminder_frequency TEXT DEFAULT 'daily', -- daily, weekly, monthly
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- Create form submissions table for tracking completed forms
CREATE TABLE public.form_submissions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  form_assignment_id UUID NOT NULL REFERENCES public.form_assignments(id) ON DELETE CASCADE,
  submitted_data JSONB NOT NULL DEFAULT '{}'::jsonb,
  submitted_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  submitted_by UUID
)","-- Enable RLS
ALTER TABLE public.workflow_forms ENABLE ROW LEVEL SECURITY","ALTER TABLE public.form_assignments ENABLE ROW LEVEL SECURITY","ALTER TABLE public.form_submissions ENABLE ROW LEVEL SECURITY","-- RLS Policies for workflow_forms
CREATE POLICY \"Super admin can manage workflow forms\" 
ON public.workflow_forms 
FOR ALL 
USING (is_current_user_super_admin())","CREATE POLICY \"Users can view active workflow forms\" 
ON public.workflow_forms 
FOR SELECT 
USING (is_active = true)","-- RLS Policies for form_assignments
CREATE POLICY \"Admins and managers can manage form assignments\" 
ON public.form_assignments 
FOR ALL 
USING (EXISTS (
  SELECT 1 FROM user_roles ur 
  WHERE ur.user_id = auth.uid() 
  AND ur.organization_id = form_assignments.organization_id 
  AND ur.role IN ('admin', 'manager')
))","CREATE POLICY \"Representatives can view their assignments\" 
ON public.form_assignments 
FOR SELECT 
USING (representative_id IN (
  SELECT id FROM user_roles WHERE user_id = auth.uid()
))","-- RLS Policies for form_submissions
CREATE POLICY \"Admins and managers can view submissions in their organization\" 
ON public.form_submissions 
FOR SELECT 
USING (EXISTS (
  SELECT 1 FROM form_assignments fa 
  JOIN user_roles ur ON ur.organization_id = fa.organization_id 
  WHERE fa.id = form_submissions.form_assignment_id 
  AND ur.user_id = auth.uid() 
  AND ur.role IN ('admin', 'manager')
))","CREATE POLICY \"Representatives can manage their own submissions\" 
ON public.form_submissions 
FOR ALL 
USING (EXISTS (
  SELECT 1 FROM form_assignments fa 
  JOIN user_roles ur ON ur.id = fa.representative_id 
  WHERE fa.id = form_submissions.form_assignment_id 
  AND ur.user_id = auth.uid()
))","-- Create update triggers
CREATE TRIGGER update_workflow_forms_updated_at
BEFORE UPDATE ON public.workflow_forms
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()","CREATE TRIGGER update_form_assignments_updated_at
BEFORE UPDATE ON public.form_assignments
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_column()"}