caterium-app/ops/sql/SUPABASE-V17.8-DEVELOPER-PANEL.sql

-- Sun Catering v17.8 developer-panel additions.
-- Applied to the current project on 2026-09-01.

create or replace function public.sun_platform_list_users()
returns table(
  user_id uuid,
  email text,
  last_sign_in_at timestamptz,
  created_at timestamptz,
  workspace_id uuid,
  workspace_name text,
  role text,
  display_name text,
  is_active boolean,
  is_platform_admin boolean
)
language plpgsql
stable
security definer
set search_path='public'
as $$
begin
  if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
  return query
  select u.id,u.email::text,u.last_sign_in_at,u.created_at,m.workspace_id,w.name,m.role,m.display_name,m.is_active,
         exists(select 1 from public.sun_platform_admins p where p.user_id=u.id)
  from auth.users u
  left join public.sun_workspace_members m on m.user_id=u.id
  left join public.sun_workspaces w on w.id=m.workspace_id
  order by coalesce(w.name,''),coalesce(m.display_name,u.email),u.email;
end;
$$;
revoke all on function public.sun_platform_list_users() from public;
grant execute on function public.sun_platform_list_users() to authenticated;

create or replace function public.sun_v17_create_backup(p_workspace uuid, p_kind text default 'manual'::text, p_label text default ''::text)
returns uuid
language plpgsql
security definer
set search_path='public'
as $$
declare v_id uuid; v_kind text:=lower(coalesce(p_kind,'manual'));
begin
  if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then raise exception 'Access denied'; end if;
  if v_kind not in ('daily','manual','pre_restore','migration') then raise exception 'Invalid backup kind'; end if;
  if v_kind<>'daily' and not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
  if v_kind='daily' and exists(select 1 from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' and created_at::date=current_date) then
    select id into v_id from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' and created_at::date=current_date order by created_at desc limit 1;
    return v_id;
  end if;
  insert into public.sun_v17_backups(workspace_id,kind,label,snapshot,created_by)
  values(p_workspace,v_kind,left(coalesce(p_label,''),240),public.sun_v17_build_snapshot(p_workspace),auth.uid()) returning id into v_id;
  insert into public.sun_v17_workspace_meta(workspace_id,last_backup_on,updated_at) values(p_workspace,current_date,now())
  on conflict(workspace_id) do update set last_backup_on=current_date,updated_at=now();
  return v_id;
end;
$$;

create or replace function public.sun_v17_restore_backup(p_workspace uuid, p_backup uuid)
returns void
language plpgsql
security definer
set search_path='public'
as $$
declare s jsonb; legacy jsonb;
begin
  if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
  perform public.sun_v17_create_backup(p_workspace,'pre_restore','Автоматически перед восстановлением');
  select snapshot into s from public.sun_v17_backups where id=p_backup and workspace_id=p_workspace;
  if s is null then raise exception 'Backup not found'; end if;
  legacy:=s->'legacy';
  if legacy is null or jsonb_typeof(legacy)<>'object' then raise exception 'Backup has no legacy state'; end if;
  perform public.sun_save_app_state(p_workspace,legacy,'backup-restore');
  perform public.sun_v17_mirror_legacy(p_workspace,legacy,'backup-restore');
  insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,client_id,created_by)
  values(p_workspace,'workspace','backup','restore','backup-restore',auth.uid());
end;
$$;
revoke all on function public.sun_v17_create_backup(uuid,text,text) from public;
revoke all on function public.sun_v17_restore_backup(uuid,uuid) from public;
grant execute on function public.sun_v17_create_backup(uuid,text,text) to authenticated;
grant execute on function public.sun_v17_restore_backup(uuid,uuid) to authenticated;

-- Company owner can attach an already registered account by email without invite codes.
create or replace function public.sun_owner_add_existing_member(p_workspace uuid,p_email text,p_role text default 'manager')
returns uuid
language plpgsql
security definer
set search_path='public'
as $$
declare
  v_user uuid;
  v_role text:=lower(coalesce(p_role,'manager'));
  v_display text;
  v_max integer;
  v_count integer;
begin
  if auth.uid() is null then raise exception 'Authentication required'; end if;
  if public.sun_member_role(p_workspace)<>'admin' and not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Нет права управлять сотрудниками'; end if;
  if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
  if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Сотрудники недоступны на текущем тарифе'; end if;
  if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Для сотрудника выберите рабочую роль'; end if;
  select id,coalesce(nullif(raw_user_meta_data->>'name',''),split_part(email,'@',1)) into v_user,v_display
  from auth.users where lower(email)=lower(trim(p_email)) limit 1;
  if v_user is null then raise exception 'Аккаунт с таким email ещё не зарегистрирован'; end if;
  select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace;
  select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
  if not exists(select 1 from public.sun_workspace_members where workspace_id=p_workspace and user_id=v_user and is_active=true) then
    if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%)',v_max; end if;
  end if;
  insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
  values(p_workspace,v_user,v_role,v_display,true,public.sun_role_default_permissions(v_role),now())
  on conflict(workspace_id,user_id) do update set role=excluded.role,display_name=coalesce(public.sun_workspace_members.display_name,excluded.display_name),is_active=true,permissions=excluded.permissions,updated_at=now();
  return v_user;
end;
$$;
revoke all on function public.sun_owner_add_existing_member(uuid,text,text) from public;
grant execute on function public.sun_owner_add_existing_member(uuid,text,text) to authenticated;