{"-- Create detection_types enum
CREATE TYPE detection_method AS ENUM ('file', 'api')","-- Create alert_templates table for managing different types of alerts
CREATE TABLE public.alert_templates (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  name_ar TEXT NOT NULL,
  description TEXT,
  description_ar TEXT,
  detection_method detection_method NOT NULL,
  
  -- For file-based detection
  model_file_url TEXT,
  model_type TEXT, -- 'yolov8', 'yolov5', etc.
  
  -- For API-based detection
  huggingface_api_key TEXT,
  model_api_endpoint TEXT,
  
  enabled 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(),
  created_by UUID REFERENCES auth.users(id)
)","-- Create camera_alert_assignments table to link cameras with alert templates
CREATE TABLE public.camera_alert_assignments (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  camera_id UUID NOT NULL,
  alert_template_id UUID NOT NULL REFERENCES public.alert_templates(id) ON DELETE CASCADE,
  enabled BOOLEAN NOT NULL DEFAULT true,
  confidence_threshold NUMERIC DEFAULT 0.7,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  UNIQUE(camera_id, alert_template_id)
)","-- Enable RLS on both tables
ALTER TABLE public.alert_templates ENABLE ROW LEVEL SECURITY","ALTER TABLE public.camera_alert_assignments ENABLE ROW LEVEL SECURITY","-- RLS policies for alert_templates
CREATE POLICY \"Super admin can manage all alert templates\"
ON public.alert_templates
FOR ALL
TO authenticated
USING (is_current_user_super_admin())","CREATE POLICY \"Users can view alert templates\"
ON public.alert_templates
FOR SELECT
TO authenticated
USING (enabled = true)","-- RLS policies for camera_alert_assignments
CREATE POLICY \"Super admin can manage all camera alert assignments\"
ON public.camera_alert_assignments
FOR ALL
TO authenticated
USING (is_current_user_super_admin())","CREATE POLICY \"Users can view camera alert assignments in their organization\"
ON public.camera_alert_assignments
FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM cameras c
    JOIN branches b ON c.branch_id = b.id
    JOIN user_roles ur ON ur.organization_id = b.organization_id
    WHERE c.id = camera_alert_assignments.camera_id
    AND ur.user_id = auth.uid()
  )
)","-- Create update trigger for alert_templates
CREATE TRIGGER update_alert_templates_updated_at
  BEFORE UPDATE ON public.alert_templates
  FOR EACH ROW
  EXECUTE FUNCTION public.update_modified_column()","-- Insert some default alert templates
INSERT INTO public.alert_templates (name, name_ar, description, description_ar, detection_method, model_type) VALUES
('Fire Detection', 'كشف الحرائق', 'Detects fire and smoke in camera feeds', 'يكشف النار والدخان في لقطات الكاميرا', 'file', 'yolov8'),
('Person Detection', 'كشف الأشخاص', 'Detects people in restricted areas', 'يكشف الأشخاص في المناطق المحظورة', 'file', 'yolov8'),
('Violence Detection', 'كشف العنف', 'Detects violent behavior and fights', 'يكشف السلوك العنيف والمشاجرات', 'api', null),
('Weapon Detection', 'كشف الأسلحة', 'Detects weapons like guns and knives', 'يكشف الأسلحة مثل المسدسات والسكاكين', 'file', 'yolov8')"}