{"-- Update user roles enum to include proper hierarchy
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'super_admin'","ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'company_admin'","ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'branch_manager'","-- Create a table to map branch managers to specific branches
CREATE TABLE IF NOT EXISTS public.user_branch_access (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    branch_id UUID REFERENCES branches(id) ON DELETE CASCADE,
    granted_by UUID REFERENCES auth.users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, branch_id)
)","-- Enable RLS on user_branch_access
ALTER TABLE public.user_branch_access ENABLE ROW LEVEL SECURITY","-- Create policies for user_branch_access
CREATE POLICY \"Super admin can manage all branch access\" 
ON public.user_branch_access 
FOR ALL 
USING (is_current_user_super_admin())","CREATE POLICY \"Company admins can manage branch access in their organization\" 
ON public.user_branch_access 
FOR ALL 
USING (
  EXISTS (
    SELECT 1 FROM user_roles ur 
    JOIN branches b ON user_branch_access.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
    AND ur.role IN ('super_admin', 'company_admin')
  )
)","CREATE POLICY \"Users can view their own branch access\" 
ON public.user_branch_access 
FOR SELECT 
USING (user_id = auth.uid())","-- Update organizations policies for proper hierarchy
DROP POLICY IF EXISTS \"Users can view their organization\" ON organizations","CREATE POLICY \"Users can view their organization\" ON organizations
FOR SELECT USING (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = auth.uid() 
    AND organization_id = organizations.id
  )
)","-- Update branches policies for proper hierarchy
DROP POLICY IF EXISTS \"Users can view branches in their organization\" ON branches","CREATE POLICY \"Users can view branches in their organization\" ON branches
FOR SELECT USING (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles ur
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = branches.organization_id
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = branches.id
  )
)","-- Update cameras policies for proper hierarchy
DROP POLICY IF EXISTS \"Users can view cameras in their organization\" ON cameras","CREATE POLICY \"Users can view cameras in their organization\" ON cameras
FOR SELECT USING (
  is_current_user_super_admin() OR
  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
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = cameras.branch_id
  )
)","-- Update security zones policies for proper hierarchy
DROP POLICY IF EXISTS \"Users can view security zones in their organization\" ON security_zones","CREATE POLICY \"Users can view security zones in their organization\" ON security_zones
FOR SELECT USING (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN branches b ON security_zones.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = security_zones.branch_id
  )
)","DROP POLICY IF EXISTS \"Users can create security zones in their organization\" ON security_zones","CREATE POLICY \"Users can create security zones in their organization\" ON security_zones
FOR INSERT WITH CHECK (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN branches b ON security_zones.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
    AND ur.role IN ('company_admin', 'branch_manager')
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = security_zones.branch_id
  )
)","DROP POLICY IF EXISTS \"Admins and managers can update security zones\" ON security_zones","CREATE POLICY \"Admins and managers can update security zones\" ON security_zones
FOR UPDATE USING (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN branches b ON security_zones.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
    AND ur.role IN ('company_admin', 'branch_manager')
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = security_zones.branch_id
  )
)","DROP POLICY IF EXISTS \"Admins and managers can delete security zones\" ON security_zones","CREATE POLICY \"Admins and managers can delete security zones\" ON security_zones
FOR DELETE USING (
  is_current_user_super_admin() OR
  EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN branches b ON security_zones.branch_id = b.id
    WHERE ur.user_id = auth.uid() 
    AND ur.organization_id = b.organization_id
    AND ur.role IN ('company_admin', 'branch_manager')
  ) OR
  EXISTS (
    SELECT 1 FROM user_branch_access uba
    WHERE uba.user_id = auth.uid()
    AND uba.branch_id = security_zones.branch_id
  )
)"}