{"-- Enable RLS on all tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY","ALTER TABLE branches ENABLE ROW LEVEL SECURITY","ALTER TABLE cameras ENABLE ROW LEVEL SECURITY","ALTER TABLE events ENABLE ROW LEVEL SECURITY","-- Drop existing policies if they exist to make this migration idempotent
DROP POLICY IF EXISTS \"Users can view their organization\" ON organizations","DROP POLICY IF EXISTS \"Users can view branches in their organization\" ON branches","DROP POLICY IF EXISTS \"Users can view cameras in their organization\" ON cameras","DROP POLICY IF EXISTS \"Users can view events in their organization\" ON events","-- 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
$$","-- 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
    JOIN organizations o ON ur.organization_id = o.id
    WHERE ur.user_id = auth.uid()
    AND o.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
    JOIN organizations o ON b.organization_id = o.id
    WHERE ur.user_id = auth.uid()
    AND ur.organization_id = o.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
    JOIN organizations o ON b.organization_id = o.id
    WHERE ur.user_id = auth.uid()
    AND ur.organization_id = o.id
  )
)"}