
-- 1. Fix agent_test_results: remove public anonymous policies, restrict to admins
DROP POLICY IF EXISTS "Anonymous read access" ON public.agent_test_results;
DROP POLICY IF EXISTS "Service role full access" ON public.agent_test_results;

CREATE POLICY "agent_test_results_admin_select"
  ON public.agent_test_results FOR SELECT TO authenticated
  USING (public.is_admin(auth.uid()));

-- service_role bypasses RLS; no policy needed for it. Block all other writes (no policy = denied).

-- 2. Restrict ai_provider_configs SELECT to admins
DROP POLICY IF EXISTS "ai_provider_configs_select_all" ON public.ai_provider_configs;

CREATE POLICY "ai_provider_configs_admin_select"
  ON public.ai_provider_configs FOR SELECT TO authenticated
  USING (public.is_admin(auth.uid()));

-- 3. Replace is_owner() (GUC-based) with role-based super_admin check
CREATE OR REPLACE FUNCTION public.is_owner()
RETURNS boolean
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid() AND role = 'super_admin'
  );
$$;

-- 4. Harden get_admin_conversation_history with authz
CREATE OR REPLACE FUNCTION public.get_admin_conversation_history(admin_user_id uuid, limit_count integer DEFAULT 50)
RETURNS TABLE(message_id uuid, content text, role text, created_at timestamp with time zone, actions jsonb, admin_name text, admin_department text)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
BEGIN
  -- Caller must be viewing their own history OR be a super_admin
  IF auth.uid() IS NULL THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;
  IF admin_user_id <> auth.uid() AND NOT public.has_role(auth.uid(), 'super_admin'::app_role) THEN
    RAISE EXCEPTION 'Unauthorized: can only view your own conversation history';
  END IF;

  RETURN QUERY
  SELECT 
    acm.id,
    acm.content,
    acm.role,
    acm.created_at,
    acm.actions,
    p.display_name,
    ap.department
  FROM public.admin_chat_messages acm
  LEFT JOIN public.admin_profiles ap ON ap.user_id = acm.user_id
  LEFT JOIN public.profiles p ON p.user_id = acm.user_id
  WHERE acm.user_id = admin_user_id
  ORDER BY acm.created_at DESC
  LIMIT limit_count;
END;
$function$;
