-- Sun Catering v17.9 developer settings.
-- Safe, additive migration for the existing v17 schema.

begin;

-- The platform administrator can inspect normalized health for any company.
-- Ordinary users remain limited to companies where they are active members.
create or replace function public.sun_v17_entity_snapshot(p_workspace uuid)
returns jsonb
language plpgsql
stable
security definer
set search_path='public'
as $$
begin
  if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then
    raise exception 'Access denied';
  end if;
  return jsonb_build_object(
    'orders',coalesce((
      select jsonb_agg(jsonb_build_object('id',order_id,'version',version,'data',data,'updated_at',updated_at) order by order_id)
      from public.sun_v17_orders where workspace_id=p_workspace
    ),'[]'::jsonb),
    'catalog',coalesce((
      select jsonb_agg(jsonb_build_object('id',item_id,'version',version,'data',data,'updated_at',updated_at) order by item_id)
      from public.sun_v17_catalog_items where workspace_id=p_workspace
    ),'[]'::jsonb),
    'meta',coalesce((
      select to_jsonb(meta) from public.sun_v17_workspace_meta meta where workspace_id=p_workspace
    ),'{}'::jsonb)
  );
end;
$$;

-- The platform administrator can list backups for any company from the
-- protected developer panel. Members can still list their own company's rows.
create or replace function public.sun_v17_list_backups(p_workspace uuid, p_limit integer default 30)
returns table(id uuid, kind text, label text, created_at timestamptz, created_by uuid)
language plpgsql
stable
security definer
set search_path='public'
as $$
begin
  if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then
    raise exception 'Access denied';
  end if;
  return query
  select backup.id,backup.kind,backup.label,backup.created_at,backup.created_by
  from public.sun_v17_backups backup
  where backup.workspace_id=p_workspace
  order by backup.created_at desc
  limit greatest(1,least(coalesce(p_limit,30),100));
end;
$$;

-- Global technical error directory. It intentionally excludes stack/context
-- from the browser result; those fields can contain sensitive implementation data.
create or replace function public.sun_platform_list_errors(p_workspace uuid default null, p_limit integer default 80)
returns table(
  id uuid,
  workspace_id uuid,
  workspace_name text,
  user_id uuid,
  client_id text,
  app_version text,
  level text,
  message text,
  created_at timestamptz
)
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 err.id,err.workspace_id,company.name,err.user_id,err.client_id,err.app_version,err.level,err.message,err.created_at
  from public.sun_v17_error_events err
  left join public.sun_workspaces company on company.id=err.workspace_id
  where p_workspace is null or err.workspace_id=p_workspace
  order by err.created_at desc
  limit greatest(1,least(coalesce(p_limit,80),200));
end;
$$;

revoke all on function public.sun_v17_entity_snapshot(uuid) from public, anon;
revoke all on function public.sun_v17_list_backups(uuid,integer) from public, anon;
revoke all on function public.sun_platform_list_errors(uuid,integer) from public, anon;
grant execute on function public.sun_v17_entity_snapshot(uuid) to authenticated;
grant execute on function public.sun_v17_list_backups(uuid,integer) to authenticated;
grant execute on function public.sun_platform_list_errors(uuid,integer) to authenticated;

commit;