supabase/db/migrations/core/62-functions-triggers.sql

--[ 8. Auth Functions ]--
-- Create a secure function to check admin status
create or replace function auth.is_admin()
returns boolean as $$
  select coalesce(
    (select true
     from public.profiles
     where id = auth.uid()
     and user_type = 'admin'),
    false
  );
$$ language sql security definer;

-- Create a secure function to check super admin status
create or replace function auth.is_super_admin()
returns boolean as $$
  select coalesce(
    (select role = 'supabase_admin'
     from auth.users
     where id = auth.uid()),
    false
  );
$$ language sql security definer;

-- Create public wrappers for the auth functions
create or replace function public.is_admin()
returns boolean as $$
  select auth.is_admin();
$$ language sql security definer;

create or replace function public.is_super_admin()
returns boolean as $$
  select auth.is_super_admin();
$$ language sql security definer;

-- Grant execute permissions
grant execute on function public.is_admin to authenticated;
grant execute on function public.is_super_admin to authenticated;
grant execute on function auth.is_admin to authenticated;
grant execute on function auth.is_super_admin to authenticated;

-- Initial admin setup function
create or replace function public.setup_initial_admin(admin_email text)
returns json
language plpgsql
security definer
as $$
declare
    result json;
begin
    -- Only allow this to run as service role or supabase_admin
    if not (
        current_user = 'service_role' 
        or exists (
            select 1 from pg_roles 
            where rolname = current_user 
            and rolsuper
        )
    ) then
        raise exception 'Must be run as service_role or superuser';
    end if;

    -- Update user_type and username for admin
    update public.profiles
    set user_type = 'admin',
        username = coalesce(username, 'superadmin'),
        display_name = coalesce(display_name, 'Super Admin')
    where email = admin_email
    returning json_build_object(
        'id', id,
        'email', email,
        'user_type', user_type,
        'username', username,
        'display_name', display_name
    ) into result;

    if result is null then
        raise exception 'Admin user with email % not found', admin_email;
    end if;

    return result;
end;
$$;

-- Grant execute permissions
revoke execute on function public.setup_initial_admin from public;
grant execute on function public.setup_initial_admin to authenticated, service_role, supabase_admin;

-- Create RPC wrapper for REST API access
create or replace function rpc.setup_initial_admin(admin_email text)
returns json
language plpgsql
security definer
as $$
begin
    return public.setup_initial_admin(admin_email);
end;
$$;

-- Grant execute permissions for RPC wrapper
grant execute on function rpc.setup_initial_admin to authenticated, service_role, supabase_admin;

--[ 9. Utility Functions ]--
-- Check if database is ready
create or replace function check_db_ready()
returns boolean
language plpgsql
security definer
as $$
begin
    -- Check if essential schemas exist
    if not exists (
        select 1 
        from information_schema.schemata 
        where schema_name in ('auth', 'storage', 'public')
    ) then
        return false;
    end if;

    -- Check if essential tables exist
    if not exists (
        select 1 
        from information_schema.tables 
        where table_schema = 'auth' 
        and table_name = 'users'
    ) then
        return false;
    end if;

    -- Check if RLS is enabled on public.profiles
    if not exists (
        select 1 
        from pg_tables 
        where schemaname = 'public' 
        and tablename = 'profiles' 
        and rowsecurity = true
    ) then
        return false;
    end if;

    return true;
end;
$$;

-- Grant execute permission
grant execute on function check_db_ready to anon, authenticated, service_role;

-- Function to handle new user registration
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
    default_user_type text := 'email_student';
    default_username text;
begin
    -- Generate username from email
    default_username := split_part(new.email, '@', 1);
    
    insert into public.profiles (
        id,
        email,
        user_type,
        username,
        display_name
    )
    values (
        new.id,
        new.email,
        coalesce(new.raw_user_meta_data->>'user_type', default_user_type),
        coalesce(new.raw_user_meta_data->>'username', default_username),
        coalesce(new.raw_user_meta_data->>'display_name', default_username)
    );
    return new;
end;
$$;

-- Trigger for new user creation
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
    after insert on auth.users
    for each row execute procedure public.handle_new_user();

--[ 11. Database Triggers ]--
drop trigger if exists handle_profiles_updated_at on public.profiles;
create trigger handle_profiles_updated_at
    before update on public.profiles
    for each row execute function public.handle_updated_at();

drop trigger if exists handle_institute_memberships_updated_at on public.institute_memberships;
create trigger handle_institute_memberships_updated_at
    before update on public.institute_memberships
    for each row execute function public.handle_updated_at();

drop trigger if exists handle_membership_requests_updated_at on public.institute_membership_requests;
create trigger handle_membership_requests_updated_at
    before update on public.institute_membership_requests
    for each row execute function public.handle_updated_at();