51 lines
1.5 KiB
PL/PgSQL
51 lines
1.5 KiB
PL/PgSQL
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; |