{"-- Create user_roles table first
CREATE TABLE user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    role user_role NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, organization_id)
)","-- Enable RLS on user_roles
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY","-- Create a function to check user roles
CREATE OR REPLACE FUNCTION public.get_user_role(user_id UUID)
RETURNS user_role
LANGUAGE SQL SECURITY DEFINER
AS $$
  SELECT role FROM user_roles WHERE user_id = $1 LIMIT 1
$$","-- Policy for user_roles
CREATE POLICY \"Users can view their own roles\" ON user_roles
FOR SELECT USING (user_id = auth.uid())","-- Policies for Organizations
CREATE POLICY \"Users can view their organization\" ON organizations
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = auth.uid() 
    AND organization_id = organizations.id
  )
)","-- Policies for Branches  
CREATE POLICY \"Users can view branches in their organization\" ON branches
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_roles ur
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = branches.organization_id
  )
)","-- Policies for Cameras
CREATE POLICY \"Users can view cameras in their organization\" ON cameras
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN branches b ON cameras.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
  )
)","-- Policies for Events
CREATE POLICY \"Users can view events in their organization\" ON events
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN cameras c ON events.camera_id = c.id
    JOIN branches b ON c.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
  )
)"}