create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  display_name text,
  avatar_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.profiles enable row level security;

drop policy if exists "profiles_select_own" on public.profiles;
create policy "profiles_select_own"
  on public.profiles for select to authenticated
  using ((select auth.uid()) = id);

drop policy if exists "profiles_insert_own" on public.profiles;
create policy "profiles_insert_own"
  on public.profiles for insert to authenticated
  with check ((select auth.uid()) = id);

drop policy if exists "profiles_update_own" on public.profiles;
create policy "profiles_update_own"
  on public.profiles for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

grant select, insert, update on table public.profiles to authenticated;

-- Keep the profile row in sync with Supabase Auth. The trigger functions live
-- outside the exposed schemas so their SECURITY DEFINER privileges cannot be
-- reached through the Data API or an RPC call.
create schema if not exists catake_private;
revoke all on schema catake_private from public, anon, authenticated;

create or replace function catake_private.handle_new_catake_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, nullif(new.raw_user_meta_data->>'display_name', ''))
  on conflict (id) do update
    set display_name = coalesce(excluded.display_name, public.profiles.display_name),
        updated_at = now();
  return new;
end;
$$;

revoke all on function catake_private.handle_new_catake_user() from public, anon, authenticated;

drop trigger if exists on_auth_user_created_catake on auth.users;
create trigger on_auth_user_created_catake
  after insert on auth.users
  for each row execute function catake_private.handle_new_catake_user();

create or replace function catake_private.touch_catake_profile()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

revoke all on function catake_private.touch_catake_profile() from public, anon, authenticated;

drop trigger if exists profiles_updated_at on public.profiles;
create trigger profiles_updated_at
  before update on public.profiles
  for each row execute function catake_private.touch_catake_profile();

-- The project currently contains a helper named rls_auto_enable() that the
-- Supabase security advisor reports as an anonymously executable SECURITY
-- DEFINER function.  It is not part of Catake and must not be callable through
-- the Data API.  Keep this guard idempotent so the migration also works on a
-- fresh project where the helper does not exist.
do $$
begin
  if exists (
    select 1
    from pg_proc p
    join pg_namespace n on n.oid = p.pronamespace
    where n.nspname = 'public'
      and p.proname = 'rls_auto_enable'
      and p.pronargs = 0
  ) then
    execute 'revoke execute on function public.rls_auto_enable() from public, anon, authenticated';
  end if;
end
$$;