{"-- Create a trigger to handle new user sign-ups
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
DECLARE
  new_org_id UUID;
BEGIN
  -- Create the organization first
  INSERT INTO public.organizations (name, subscription_tier)
  VALUES (
    COALESCE(new.raw_user_meta_data->>'organization_name', 'Default Organization'),
    'basic'
  )
  RETURNING id INTO new_org_id;
  
  -- Create default branch for the organization
  INSERT INTO public.branches (organization_id, name, address)
  VALUES (new_org_id, 'Headquarters', 'Main Location');
  
  -- Add user role as admin for the organization
  INSERT INTO public.user_roles (user_id, organization_id, role)
  VALUES (new.id, new_org_id, 'admin');
  
  RETURN new;
END;
$$","-- Create the trigger to execute on new user sign-up
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user()","-- Create additional tables for advanced features

-- AI Models table to track available models
CREATE TABLE ai_models (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL,
  version TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true,
  configuration JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)","-- Security Zones table for camera coverage areas
CREATE TABLE security_zones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  branch_id UUID REFERENCES branches(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  coordinates JSONB, -- Polygon coordinates
  sensitivity_level INTEGER NOT NULL DEFAULT 5, -- 1-10 scale
  active_hours JSONB, -- Time ranges when zone is active
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)","-- Detection rules table for custom alerts
CREATE TABLE detection_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  object_types TEXT[], -- person, vehicle, animal, etc.
  min_confidence DECIMAL(5,2) NOT NULL DEFAULT 0.6,
  cooldown_seconds INTEGER DEFAULT 60,
  notification_channels TEXT[], -- email, sms, push, webhook
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)","-- Create index for better performance
CREATE INDEX idx_events_camera_id ON events(camera_id)","CREATE INDEX idx_events_detected_at ON events(detected_at)","CREATE INDEX idx_cameras_branch_id ON cameras(branch_id)"}