alter table auth.users
  add column if not exists is_system_admin boolean not null default false;

update auth.users
set is_system_admin = true
where id = (
  select u.id
  from auth.users u
  order by u.created_at
  limit 1
)
and not exists (select 1 from auth.users where is_system_admin = true);

drop function if exists app.me();

create or replace function app.me()
returns table (
  user_id    uuid,
  full_name  text,
  is_active  boolean,
  is_system_admin boolean,
  is_owner_anywhere boolean,
  emp_id     text,
  shops      jsonb
) language sql
  security definer
  set search_path = app, public
  stable
as $$
  select
    auth.uid()                  as user_id,
    coalesce(p.full_name, u.full_name, '') as full_name,
    coalesce(p.is_active, u.is_active, true) as is_active,
    coalesce(u.is_system_admin, false) as is_system_admin,
    app.is_owner_anywhere()     as is_owner_anywhere,
    e.emp_id                    as emp_id,
    coalesce((
      select jsonb_agg(jsonb_build_object(
        'shop_id', a.shop_id, 'shop_name', s.name, 'role', a.role))
      from app.user_shop_assignments a
      join app.shops s on s.id = a.shop_id
      where a.user_id = auth.uid()
    ), '[]'::jsonb) as shops
  from auth.users u
  left join app.user_profiles p on p.user_id = u.id
  left join app.employees e on lower(e.email) = lower(u.email::text)
  where u.id = auth.uid()
  limit 1;
$$;
revoke all on function app.me() from public;
grant execute on function app.me() to authenticated;