{"-- Idempotent version: skip if already created
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='branch_roles') THEN
    -- Create a branch-level roles table to support branch manager visibility
    CREATE TABLE public.branch_roles (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
      branch_id UUID NOT NULL REFERENCES public.branches(id) ON DELETE CASCADE,
      role TEXT NOT NULL CHECK (role IN ('branch_manager')),
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );

    -- Enable RLS on branch_roles
    ALTER TABLE public.branch_roles ENABLE ROW LEVEL SECURITY;

    -- Allow users to view their own branch roles
    CREATE POLICY \"Users can view their own branch roles\" ON public.branch_roles
    FOR SELECT USING (user_id = auth.uid());

    -- Allow super admin to manage branch roles
    CREATE POLICY \"Super admin can manage branch roles\" ON public.branch_roles
    FOR ALL USING (is_current_user_super_admin()) WITH CHECK (is_current_user_super_admin());
  END IF;
END $$","-- Drop pinned_dashboards policies that conflict (recreated later)
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT policyname FROM pg_policies WHERE tablename='pinned_dashboards' AND schemaname='public' LOOP
    EXECUTE format('DROP POLICY %I ON public.pinned_dashboards', r.policyname);
  END LOOP;
END $$","-- Expand visibility for pinned_dashboards based on roles
CREATE POLICY \"Super admin can view all pinned dashboards\" ON public.pinned_dashboards
FOR SELECT USING (is_current_user_super_admin())","CREATE POLICY \"Org admins and managers can view org pinned dashboards\" ON public.pinned_dashboards
FOR SELECT USING (
  EXISTS (
    SELECT 1
    FROM public.aman_instances ai
    JOIN public.user_roles ur ON ur.organization_id = ai.organization_id
    WHERE ai.id = pinned_dashboards.instance_id
      AND ur.user_id = auth.uid()
      AND ur.role IN ('admin', 'manager')
  )
)","CREATE POLICY \"Branch managers can view branch pinned dashboards\" ON public.pinned_dashboards
FOR SELECT USING (
  EXISTS (
    SELECT 1
    FROM public.aman_instances ai
    JOIN public.branch_roles br ON br.branch_id = ai.branch_id
    WHERE ai.id = pinned_dashboards.instance_id
      AND br.user_id = auth.uid()
      AND br.role = 'branch_manager'
  )
)"}