{"-- Create a function to check if user is super admin
CREATE OR REPLACE FUNCTION public.is_super_admin(user_email text)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $function$
  SELECT user_email = 'ashraffarid@gmail.com'
$function$","-- Add super admin check function for authenticated users
CREATE OR REPLACE FUNCTION public.is_current_user_super_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $function$
  SELECT public.is_super_admin((SELECT email FROM auth.users WHERE id = auth.uid()))
$function$","-- Update organizations table to add more subscription details
ALTER TABLE public.organizations 
ADD COLUMN IF NOT EXISTS max_branches INTEGER DEFAULT 1,
ADD COLUMN IF NOT EXISTS features JSONB DEFAULT '{\"ai_detection\": true, \"live_streaming\": true, \"recording\": false, \"analytics\": false}',
ADD COLUMN IF NOT EXISTS subscription_status TEXT DEFAULT 'pending'","-- Update subscribers table with more detailed information
ALTER TABLE public.subscribers 
ADD COLUMN IF NOT EXISTS subscription_status TEXT DEFAULT 'pending',
ADD COLUMN IF NOT EXISTS approved_by UUID REFERENCES auth.users(id),
ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP WITH TIME ZONE,
ADD COLUMN IF NOT EXISTS last_activity TIMESTAMP WITH TIME ZONE DEFAULT now(),
ADD COLUMN IF NOT EXISTS notes TEXT","-- Update user_roles table to support more detailed permissions
ALTER TABLE public.user_roles 
ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '{\"view_cameras\": true, \"manage_users\": false, \"view_analytics\": true, \"manage_settings\": false}',
ADD COLUMN IF NOT EXISTS granted_by UUID REFERENCES auth.users(id),
ADD COLUMN IF NOT EXISTS granted_at TIMESTAMP WITH TIME ZONE DEFAULT now()","-- Create RLS policy for super admin to manage everything
CREATE POLICY \"Super admin can manage all subscribers\" 
ON public.subscribers 
FOR ALL 
USING (public.is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all organizations\" 
ON public.organizations 
FOR ALL 
USING (public.is_current_user_super_admin())","CREATE POLICY \"Super admin can manage all user roles\" 
ON public.user_roles 
FOR ALL 
USING (public.is_current_user_super_admin())","-- Update cameras table to include model and feature information
ALTER TABLE public.cameras 
ADD COLUMN IF NOT EXISTS model TEXT DEFAULT 'AI-CAM-4K',
ADD COLUMN IF NOT EXISTS features JSONB DEFAULT '{\"night_vision\": true, \"motion_detection\": true, \"ai_analysis\": true, \"audio_recording\": false}',
ADD COLUMN IF NOT EXISTS max_resolution TEXT DEFAULT '4K'","-- Create a trigger to update last_activity for subscribers
CREATE OR REPLACE FUNCTION public.update_subscriber_activity()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
    UPDATE public.subscribers 
    SET last_activity = now() 
    WHERE user_id = NEW.user_id OR user_id = OLD.user_id;
    RETURN COALESCE(NEW, OLD);
END;
$function$","-- Add trigger for auth activity updates (when user logs in)
CREATE OR REPLACE TRIGGER update_subscriber_activity_trigger
    AFTER INSERT OR UPDATE ON auth.sessions
    FOR EACH ROW
    EXECUTE FUNCTION public.update_subscriber_activity()"}