{"-- Face recognition tables for Frigate 0.16
CREATE TABLE public.frigate_faces (
  id TEXT PRIMARY KEY,
  camera_id UUID REFERENCES public.cameras(id) ON DELETE CASCADE,
  embedding JSONB, -- Face embedding as JSON array
  label TEXT, -- Person name/identifier
  confidence FLOAT NOT NULL DEFAULT 0.0,
  thumbnail_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- License plates table for Frigate 0.16  
CREATE TABLE public.frigate_license_plates (
  id TEXT PRIMARY KEY,
  plate_number TEXT NOT NULL,
  camera_id UUID REFERENCES public.cameras(id) ON DELETE CASCADE,
  confidence FLOAT NOT NULL DEFAULT 0.0,
  region TEXT, -- License plate region/state
  vehicle_make TEXT,
  vehicle_model TEXT,
  vehicle_color TEXT,
  thumbnail_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- Detection zones for Frigate cameras
CREATE TABLE public.frigate_zones (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  camera_id UUID REFERENCES public.cameras(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  coordinates JSONB NOT NULL, -- Array of [x,y] coordinates
  zone_type TEXT NOT NULL DEFAULT 'detection', -- detection, activity, etc.
  enabled BOOLEAN NOT NULL DEFAULT true,
  color TEXT DEFAULT '#3b82f6',
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- Enhanced events table with Frigate 0.16 attributes
ALTER TABLE public.events ADD COLUMN IF NOT EXISTS attributes JSONB DEFAULT '{}'","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS face_id TEXT","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS license_plate TEXT","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS zones TEXT[]","-- Array of zone names
ALTER TABLE public.events ADD COLUMN IF NOT EXISTS sub_label TEXT","-- For more detailed categorization
ALTER TABLE public.events ADD COLUMN IF NOT EXISTS top_score FLOAT","-- Confidence score
ALTER TABLE public.events ADD COLUMN IF NOT EXISTS start_time TIMESTAMP WITH TIME ZONE","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS end_time TIMESTAMP WITH TIME ZONE","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS has_clip BOOLEAN DEFAULT false","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS has_snapshot BOOLEAN DEFAULT false","ALTER TABLE public.events ADD COLUMN IF NOT EXISTS frigate_id TEXT","-- Original Frigate event ID

-- Timeline segments for video scrubbing
CREATE TABLE public.frigate_timeline (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  camera_id UUID REFERENCES public.cameras(id) ON DELETE CASCADE,
  start_time TIMESTAMP WITH TIME ZONE NOT NULL,
  end_time TIMESTAMP WITH TIME ZONE NOT NULL,
  segment_type TEXT NOT NULL DEFAULT 'recording', -- recording, motion, detection
  file_path TEXT,
  duration_seconds INTEGER,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
)","-- Alert rules for enhanced notifications
CREATE TABLE public.frigate_alert_rules (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  camera_id UUID REFERENCES public.cameras(id) ON DELETE CASCADE,
  rule_name TEXT NOT NULL,
  object_types TEXT[] DEFAULT '{}', -- person, car, etc.
  zones TEXT[] DEFAULT '{}', -- Zone names to monitor
  min_confidence FLOAT DEFAULT 0.7,
  cooldown_seconds INTEGER DEFAULT 60,
  notification_methods JSONB DEFAULT '{\"push\": true, \"email\": false}',
  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()
)","-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_frigate_faces_camera_id ON public.frigate_faces(camera_id)","CREATE INDEX IF NOT EXISTS idx_frigate_faces_label ON public.frigate_faces(label)","CREATE INDEX IF NOT EXISTS idx_frigate_license_plates_camera_id ON public.frigate_license_plates(camera_id)","CREATE INDEX IF NOT EXISTS idx_frigate_license_plates_plate_number ON public.frigate_license_plates(plate_number)","CREATE INDEX IF NOT EXISTS idx_frigate_zones_camera_id ON public.frigate_zones(camera_id)","CREATE INDEX IF NOT EXISTS idx_frigate_timeline_camera_id ON public.frigate_timeline(camera_id)","CREATE INDEX IF NOT EXISTS idx_frigate_timeline_time ON public.frigate_timeline(start_time, end_time)","CREATE INDEX IF NOT EXISTS idx_events_face_id ON public.events(face_id) WHERE face_id IS NOT NULL","CREATE INDEX IF NOT EXISTS idx_events_license_plate ON public.events(license_plate) WHERE license_plate IS NOT NULL","CREATE INDEX IF NOT EXISTS idx_events_frigate_id ON public.events(frigate_id) WHERE frigate_id IS NOT NULL","-- Enable RLS for new tables
ALTER TABLE public.frigate_faces ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_license_plates ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_zones ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_timeline ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_alert_rules ENABLE ROW LEVEL SECURITY","-- RLS policies for frigate_faces
CREATE POLICY \"Users can view faces in their organization cameras\" ON public.frigate_faces
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_faces.camera_id AND ur.user_id = auth.uid()
    )
  )","CREATE POLICY \"Admins can manage faces in their organization\" ON public.frigate_faces
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_faces.camera_id 
        AND ur.user_id = auth.uid() 
        AND ur.role IN ('admin', 'manager')
    )
  )","-- RLS policies for frigate_license_plates  
CREATE POLICY \"Users can view license plates in their organization cameras\" ON public.frigate_license_plates
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_license_plates.camera_id AND ur.user_id = auth.uid()
    )
  )","-- RLS policies for frigate_zones
CREATE POLICY \"Users can view zones in their organization cameras\" ON public.frigate_zones
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_zones.camera_id AND ur.user_id = auth.uid()
    )
  )","CREATE POLICY \"Admins can manage zones in their organization cameras\" ON public.frigate_zones
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_zones.camera_id 
        AND ur.user_id = auth.uid() 
        AND ur.role IN ('admin', 'manager')
    )
  )","-- RLS policies for frigate_timeline
CREATE POLICY \"Users can view timeline in their organization cameras\" ON public.frigate_timeline
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id  
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_timeline.camera_id AND ur.user_id = auth.uid()
    )
  )","-- RLS policies for frigate_alert_rules
CREATE POLICY \"Users can view alert rules in their organization cameras\" ON public.frigate_alert_rules
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_alert_rules.camera_id AND ur.user_id = auth.uid()
    )
  )","CREATE POLICY \"Admins can manage alert rules in their organization cameras\" ON public.frigate_alert_rules
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.cameras c
      JOIN public.branches b ON c.branch_id = b.id
      JOIN public.user_roles ur ON ur.organization_id = b.organization_id
      WHERE c.id = frigate_alert_rules.camera_id 
        AND ur.user_id = auth.uid() 
        AND ur.role IN ('admin', 'manager')
    )
  )","-- Super admin policies for all tables
CREATE POLICY \"Super admin can manage all frigate faces\" ON public.frigate_faces
  FOR ALL USING (is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all frigate license plates\" ON public.frigate_license_plates
  FOR ALL USING (is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all frigate zones\" ON public.frigate_zones
  FOR ALL USING (is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all frigate timeline\" ON public.frigate_timeline
  FOR ALL USING (is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all frigate alert rules\" ON public.frigate_alert_rules
  FOR ALL USING (is_current_user_super_admin())","-- Triggers for updated_at timestamps
CREATE TRIGGER set_updated_at BEFORE UPDATE ON public.frigate_faces
  FOR EACH ROW EXECUTE FUNCTION public.update_modified_column()","CREATE TRIGGER set_updated_at BEFORE UPDATE ON public.frigate_license_plates
  FOR EACH ROW EXECUTE FUNCTION public.update_modified_column()","CREATE TRIGGER set_updated_at BEFORE UPDATE ON public.frigate_zones
  FOR EACH ROW EXECUTE FUNCTION public.update_modified_column()","CREATE TRIGGER set_updated_at BEFORE UPDATE ON public.frigate_alert_rules
  FOR EACH ROW EXECUTE FUNCTION public.update_modified_column()"}