-- ============================================================================
-- EduAdvise CRM — Phase 1: Row-Level Security
-- Branch isolation + permission helpers. These helpers are SECURITY DEFINER so
-- they bypass RLS internally (avoids recursion when a policy reads public.users).
-- ============================================================================

-- Is the current user a super admin? (sees all branches)
create or replace function public.is_super_admin()
returns boolean
language sql stable security definer set search_path = public
as $$
  select exists (
    select 1
    from public.users u
    join public.roles r on r.id = u.role_id
    where u.id = auth.uid() and r.slug = 'super-admin'
  );
$$;

-- Branch ids the current user belongs to (primary + assigned)
create or replace function public.current_user_branch_ids()
returns setof uuid
language sql stable security definer set search_path = public
as $$
  select branch_id from public.user_branches where user_id = auth.uid()
  union
  select primary_branch_id from public.users
    where id = auth.uid() and primary_branch_id is not null;
$$;

-- Does the current user's role grant resource.action?
create or replace function public.has_permission(p_resource text, p_action text)
returns boolean
language sql stable security definer set search_path = public
as $$
  select public.is_super_admin() or exists (
    select 1
    from public.users u
    join public.role_permissions rp on rp.role_id = u.role_id
    join public.permissions p on p.id = rp.permission_id
    where u.id = auth.uid()
      and p.resource = p_resource
      and p.action = p_action
  );
$$;

-- ---------------------------------------------------------------------------
-- Enable RLS
-- ---------------------------------------------------------------------------
alter table public.branches        enable row level security;
alter table public.roles           enable row level security;
alter table public.permissions     enable row level security;
alter table public.role_permissions enable row level security;
alter table public.users           enable row level security;
alter table public.user_branches   enable row level security;
alter table public.audit_log       enable row level security;

-- ---------------------------------------------------------------------------
-- Reference data: any authenticated user may read; only super admin writes
-- ---------------------------------------------------------------------------
create policy "read branches" on public.branches
  for select to authenticated using (true);
create policy "manage branches" on public.branches
  for all to authenticated
  using (public.has_permission('branches','update'))
  with check (public.has_permission('branches','update'));

create policy "read roles" on public.roles
  for select to authenticated using (true);
create policy "manage roles" on public.roles
  for all to authenticated
  using (public.has_permission('roles','update'))
  with check (public.has_permission('roles','update'));

create policy "read permissions" on public.permissions
  for select to authenticated using (true);

create policy "read role_permissions" on public.role_permissions
  for select to authenticated using (true);
create policy "manage role_permissions" on public.role_permissions
  for all to authenticated
  using (public.has_permission('roles','update'))
  with check (public.has_permission('roles','update'));

-- ---------------------------------------------------------------------------
-- Users: see self, plus users sharing a branch (super admin sees all)
-- ---------------------------------------------------------------------------
create policy "read users in my branch" on public.users
  for select to authenticated
  using (
    public.is_super_admin()
    or id = auth.uid()
    or primary_branch_id in (select public.current_user_branch_ids())
    or id in (
      select ub.user_id from public.user_branches ub
      where ub.branch_id in (select public.current_user_branch_ids())
    )
  );

create policy "manage users" on public.users
  for all to authenticated
  using (public.has_permission('users','update'))
  with check (public.has_permission('users','update'));

create policy "update own profile" on public.users
  for update to authenticated
  using (id = auth.uid())
  with check (id = auth.uid());

-- ---------------------------------------------------------------------------
-- user_branches: readable to those who can read users; managed by user admins
-- ---------------------------------------------------------------------------
create policy "read user_branches" on public.user_branches
  for select to authenticated
  using (
    public.is_super_admin()
    or user_id = auth.uid()
    or branch_id in (select public.current_user_branch_ids())
  );
create policy "manage user_branches" on public.user_branches
  for all to authenticated
  using (public.has_permission('users','update'))
  with check (public.has_permission('users','update'));

-- ---------------------------------------------------------------------------
-- audit_log: read within branch scope; inserts happen via service role
-- ---------------------------------------------------------------------------
create policy "read audit in my branch" on public.audit_log
  for select to authenticated
  using (
    public.is_super_admin()
    or branch_id in (select public.current_user_branch_ids())
    or actor_id = auth.uid()
  );