142 lines
3.8 KiB
PL/PgSQL
142 lines
3.8 KiB
PL/PgSQL
create table if not exists public.user_profiles (
|
|
user_id uuid primary key references auth.users(id) on delete cascade,
|
|
first_name text not null default '',
|
|
last_name text not null default '',
|
|
country text not null default '',
|
|
tests_visible boolean not null default false,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
drop trigger if exists trg_user_profiles_updated_at on public.user_profiles;
|
|
create trigger trg_user_profiles_updated_at
|
|
before update on public.user_profiles
|
|
for each row execute function public.set_updated_at();
|
|
|
|
alter table public.user_profiles enable row level security;
|
|
|
|
drop policy if exists "Allow users to insert own user profile" on public.user_profiles;
|
|
create policy "Allow users to insert own user profile"
|
|
on public.user_profiles
|
|
for insert
|
|
with check (auth.uid() = user_id);
|
|
|
|
drop policy if exists "Allow users to select own user profile" on public.user_profiles;
|
|
create policy "Allow users to select own user profile"
|
|
on public.user_profiles
|
|
for select
|
|
using (auth.uid() = user_id);
|
|
|
|
drop policy if exists "Allow users to update own user profile" on public.user_profiles;
|
|
create policy "Allow users to update own user profile"
|
|
on public.user_profiles
|
|
for update
|
|
using (auth.uid() = user_id)
|
|
with check (auth.uid() = user_id);
|
|
|
|
insert into public.user_profiles (user_id)
|
|
select id
|
|
from auth.users
|
|
on conflict (user_id) do nothing;
|
|
|
|
create or replace function public.handle_new_user_profile()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
insert into public.user_profiles (user_id)
|
|
values (new.id)
|
|
on conflict (user_id) do nothing;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists on_auth_user_created_profile on auth.users;
|
|
create trigger on_auth_user_created_profile
|
|
after insert on auth.users
|
|
for each row execute function public.handle_new_user_profile();
|
|
|
|
create or replace function public.create_or_get_assessment_share(p_assessment_id uuid)
|
|
returns table (
|
|
assessment_id uuid,
|
|
share_token text
|
|
)
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_user_id uuid;
|
|
begin
|
|
v_user_id := auth.uid();
|
|
|
|
if v_user_id is null then
|
|
raise exception 'Not authenticated';
|
|
end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.assessments a
|
|
where a.id = p_assessment_id
|
|
and a.user_id = v_user_id
|
|
) then
|
|
raise exception 'Assessment not found';
|
|
end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.user_profiles p
|
|
where p.user_id = v_user_id
|
|
and p.tests_visible = true
|
|
) then
|
|
raise exception 'Tests are hidden in the profile';
|
|
end if;
|
|
|
|
insert into public.assessment_shares (assessment_id, user_id, share_token)
|
|
values (p_assessment_id, v_user_id, public.generate_share_token())
|
|
on conflict on constraint assessment_shares_pkey do nothing;
|
|
|
|
return query
|
|
select s.assessment_id, s.share_token
|
|
from public.assessment_shares s
|
|
where s.assessment_id = p_assessment_id
|
|
and s.user_id = v_user_id
|
|
limit 1;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.get_shared_assessment(p_share_token text)
|
|
returns table (
|
|
id uuid,
|
|
pat_type text,
|
|
datum date,
|
|
name text,
|
|
exercises jsonb
|
|
)
|
|
language sql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select
|
|
a.id,
|
|
a.pat_type,
|
|
a.datum,
|
|
a.name,
|
|
a.exercises
|
|
from public.assessment_shares s
|
|
join public.assessments a on a.id = s.assessment_id
|
|
join public.user_profiles p on p.user_id = a.user_id
|
|
where s.share_token = p_share_token
|
|
and p.tests_visible = true
|
|
limit 1;
|
|
$$;
|
|
|
|
revoke all on function public.create_or_get_assessment_share(uuid) from public;
|
|
grant execute on function public.create_or_get_assessment_share(uuid) to authenticated;
|
|
|
|
revoke all on function public.get_shared_assessment(text) from public;
|
|
grant execute on function public.get_shared_assessment(text) to anon, authenticated;
|