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;