thrilltrack-explorer/supabase/migrations/20250928182415_47556b14-e1fb-4a14-b379-1567a5fdc057.sql

-- Create function to check if user is superuser
CREATE OR REPLACE FUNCTION public.is_superuser(_user_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path TO 'public'
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.user_roles
    WHERE user_id = _user_id
      AND role = 'superuser'
  )
$$;

-- Create function to check if user can manage another user
CREATE OR REPLACE FUNCTION public.can_manage_user(_manager_id uuid, _target_user_id uuid)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  manager_roles app_role[];
  target_roles app_role[];
  has_superuser boolean := false;
  has_admin boolean := false;
  has_moderator boolean := false;
BEGIN
  -- Get manager roles
  SELECT ARRAY_AGG(role) INTO manager_roles
  FROM public.user_roles
  WHERE user_id = _manager_id;

  -- Get target user roles
  SELECT ARRAY_AGG(role) INTO target_roles
  FROM public.user_roles
  WHERE user_id = _target_user_id;

  -- Check manager permissions
  IF 'superuser' = ANY(manager_roles) THEN
    has_superuser := true;
  END IF;
  
  IF 'admin' = ANY(manager_roles) THEN
    has_admin := true;
  END IF;
  
  IF 'moderator' = ANY(manager_roles) THEN
    has_moderator := true;
  END IF;

  -- Superuser can manage anyone except other superusers
  IF has_superuser AND NOT ('superuser' = ANY(target_roles)) THEN
    RETURN true;
  END IF;

  -- Admin can manage moderators and users, but not admins or superusers
  IF has_admin AND NOT ('admin' = ANY(target_roles) OR 'superuser' = ANY(target_roles)) THEN
    RETURN true;
  END IF;

  -- Moderator can only ban users with no roles or user role
  IF has_moderator AND (target_roles IS NULL OR (ARRAY_LENGTH(target_roles, 1) IS NULL) OR (target_roles = ARRAY['user'::app_role])) THEN
    RETURN true;
  END IF;

  RETURN false;
END;
$$;

-- Create function to get user management permissions for current user
CREATE OR REPLACE FUNCTION public.get_user_management_permissions(_user_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
STABLE SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  user_roles app_role[];
  permissions jsonb := '{}';
BEGIN
  -- Get user roles
  SELECT ARRAY_AGG(role) INTO user_roles
  FROM public.user_roles
  WHERE user_id = _user_id;

  -- Set permissions based on roles
  IF 'superuser' = ANY(user_roles) THEN
    permissions := jsonb_build_object(
      'can_ban_any_user', true,
      'can_manage_admin_roles', true,
      'can_manage_moderator_roles', true,
      'can_view_all_profiles', true,
      'can_assign_superuser', false,
      'role_level', 'superuser'
    );
  ELSIF 'admin' = ANY(user_roles) THEN
    permissions := jsonb_build_object(
      'can_ban_any_user', true,
      'can_manage_admin_roles', false,
      'can_manage_moderator_roles', true,
      'can_view_all_profiles', true,
      'can_assign_superuser', false,
      'role_level', 'admin'
    );
  ELSIF 'moderator' = ANY(user_roles) THEN
    permissions := jsonb_build_object(
      'can_ban_any_user', false,
      'can_manage_admin_roles', false,
      'can_manage_moderator_roles', false,
      'can_view_all_profiles', true,
      'can_assign_superuser', false,
      'role_level', 'moderator'
    );
  ELSE
    permissions := jsonb_build_object(
      'can_ban_any_user', false,
      'can_manage_admin_roles', false,
      'can_manage_moderator_roles', false,
      'can_view_all_profiles', false,
      'can_assign_superuser', false,
      'role_level', 'user'
    );
  END IF;

  RETURN permissions;
END;
$$;

-- Update RLS policies for profiles to allow admin access
DROP POLICY IF EXISTS "Moderators can view all profiles" ON public.profiles;
CREATE POLICY "Admins and moderators can view all profiles"
ON public.profiles
FOR SELECT
USING (
  (privacy_level = 'public') OR 
  (auth.uid() = user_id) OR 
  is_moderator(auth.uid())
);

-- Allow admins and superusers to update profiles (including ban status)
CREATE POLICY "Admins can update any profile"
ON public.profiles
FOR UPDATE
USING (
  (auth.uid() = user_id) OR 
  has_role(auth.uid(), 'admin') OR 
  is_superuser(auth.uid())
);

-- Create audit log table for tracking admin actions
CREATE TABLE IF NOT EXISTS public.admin_audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  admin_user_id uuid NOT NULL,
  target_user_id uuid NOT NULL,
  action text NOT NULL,
  details jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Enable RLS on audit log
ALTER TABLE public.admin_audit_log ENABLE ROW LEVEL SECURITY;

-- Only admins and superusers can view audit log
CREATE POLICY "Admins can view audit log"
ON public.admin_audit_log
FOR SELECT
USING (is_moderator(auth.uid()));

-- Only admins and superusers can insert into audit log
CREATE POLICY "Admins can insert audit log"
ON public.admin_audit_log
FOR INSERT
WITH CHECK (is_moderator(auth.uid()));

-- Create function to log admin actions
CREATE OR REPLACE FUNCTION public.log_admin_action(
  _admin_user_id uuid,
  _target_user_id uuid,
  _action text,
  _details jsonb DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
  INSERT INTO public.admin_audit_log (admin_user_id, target_user_id, action, details)
  VALUES (_admin_user_id, _target_user_id, _action, _details);
END;
$$;