caterium-app/ops/sql/SUPABASE-RBAC-V3.sql

-- Sun Catering Cloud RBAC v3
-- Roles, granular permissions, administrator tools, secured state RPCs and realtime sync events.

alter table public.sun_workspace_members add column if not exists display_name text;
alter table public.sun_workspace_members add column if not exists is_active boolean not null default true;
alter table public.sun_workspace_members add column if not exists permissions jsonb not null default '{}'::jsonb;
alter table public.sun_workspace_members add column if not exists updated_at timestamptz not null default now();

alter table public.sun_workspace_invites add column if not exists permissions jsonb;

alter table public.sun_workspace_members drop constraint if exists sun_workspace_members_role_check;
update public.sun_workspace_members set role='admin' where role='owner';
alter table public.sun_workspace_members add constraint sun_workspace_members_role_check
  check (role in ('admin','manager','kitchen','courier','viewer'));

alter table public.sun_workspace_invites drop constraint if exists sun_workspace_invites_role_check;
alter table public.sun_workspace_invites add constraint sun_workspace_invites_role_check
  check (role in ('admin','manager','kitchen','courier','viewer'));

create or replace function public.sun_role_default_permissions(p_role text)
returns jsonb
language sql
immutable
set search_path = public
as $$
  select case lower(coalesce(p_role,''))
    when 'admin' then jsonb_build_object(
      'app.read',true,
      'orders.view',true,'orders.create',true,'orders.edit',true,'orders.delete',true,
      'clients.view',true,'clients.edit',true,
      'catalog.view',true,'catalog.edit',true,
      'calendar.view',true,'map.view',true,
      'production.view',true,'production.edit',true,
      'shopping.view',true,'shopping.edit',true,
      'stock.view',true,'stock.edit',true,
      'routes.view',true,'routes.edit',true,
      'mailings.view',true,'mailings.edit',true,
      'money.view',true,'money.edit',true,
      'stats.view',true,
      'team.view',true,'team.edit',true,
      'suppliers.view',true,'suppliers.edit',true,
      'print.view',true,
      'settings.view',true,'settings.edit',true,
      'users.manage',true,'audit.view',true,'backups.manage',true
    )
    when 'manager' then jsonb_build_object(
      'app.read',true,
      'orders.view',true,'orders.create',true,'orders.edit',true,'orders.delete',false,
      'clients.view',true,'clients.edit',true,
      'catalog.view',true,'catalog.edit',false,
      'calendar.view',true,'map.view',true,
      'production.view',false,'production.edit',false,
      'shopping.view',false,'shopping.edit',false,
      'stock.view',false,'stock.edit',false,
      'routes.view',true,'routes.edit',false,
      'mailings.view',true,'mailings.edit',true,
      'money.view',false,'money.edit',false,
      'stats.view',true,
      'team.view',true,'team.edit',false,
      'suppliers.view',false,'suppliers.edit',false,
      'print.view',true,
      'settings.view',false,'settings.edit',false,
      'users.manage',false,'audit.view',false,'backups.manage',false
    )
    when 'kitchen' then jsonb_build_object(
      'app.read',true,
      'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
      'clients.view',false,'clients.edit',false,
      'catalog.view',true,'catalog.edit',false,
      'calendar.view',true,'map.view',false,
      'production.view',true,'production.edit',true,
      'shopping.view',true,'shopping.edit',false,
      'stock.view',true,'stock.edit',true,
      'routes.view',false,'routes.edit',false,
      'mailings.view',false,'mailings.edit',false,
      'money.view',false,'money.edit',false,
      'stats.view',false,
      'team.view',true,'team.edit',false,
      'suppliers.view',false,'suppliers.edit',false,
      'print.view',true,
      'settings.view',false,'settings.edit',false,
      'users.manage',false,'audit.view',false,'backups.manage',false
    )
    when 'courier' then jsonb_build_object(
      'app.read',true,
      'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
      'clients.view',false,'clients.edit',false,
      'catalog.view',false,'catalog.edit',false,
      'calendar.view',true,'map.view',true,
      'production.view',false,'production.edit',false,
      'shopping.view',false,'shopping.edit',false,
      'stock.view',false,'stock.edit',false,
      'routes.view',true,'routes.edit',true,
      'mailings.view',false,'mailings.edit',false,
      'money.view',false,'money.edit',false,
      'stats.view',false,
      'team.view',false,'team.edit',false,
      'suppliers.view',false,'suppliers.edit',false,
      'print.view',false,
      'settings.view',false,'settings.edit',false,
      'users.manage',false,'audit.view',false,'backups.manage',false
    )
    else jsonb_build_object(
      'app.read',true,
      'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
      'clients.view',true,'clients.edit',false,
      'catalog.view',true,'catalog.edit',false,
      'calendar.view',true,'map.view',true,
      'production.view',false,'production.edit',false,
      'shopping.view',false,'shopping.edit',false,
      'stock.view',false,'stock.edit',false,
      'routes.view',false,'routes.edit',false,
      'mailings.view',false,'mailings.edit',false,
      'money.view',false,'money.edit',false,
      'stats.view',true,
      'team.view',false,'team.edit',false,
      'suppliers.view',false,'suppliers.edit',false,
      'print.view',true,
      'settings.view',false,'settings.edit',false,
      'users.manage',false,'audit.view',false,'backups.manage',false
    )
  end;
$$;

create or replace function public.sun_member_role(p_workspace uuid)
returns text
language sql
stable
security definer
set search_path = public
as $$
  select m.role
  from public.sun_workspace_members m
  where m.workspace_id = p_workspace
    and m.user_id = auth.uid()
    and m.is_active = true
  limit 1;
$$;

create or replace function public.sun_has_permission(p_workspace uuid, p_permission text)
returns boolean
language plpgsql
stable
security definer
set search_path = public
as $$
declare
  v_role text;
  v_permissions jsonb;
  v_active boolean;
begin
  select role, permissions, is_active
    into v_role, v_permissions, v_active
  from public.sun_workspace_members
  where workspace_id=p_workspace and user_id=auth.uid()
  limit 1;

  if not coalesce(v_active,false) then return false; end if;
  if v_role='admin' then return true; end if;
  if v_permissions ? p_permission then
    return coalesce((v_permissions->>p_permission)::boolean,false);
  end if;
  return coalesce((public.sun_role_default_permissions(v_role)->>p_permission)::boolean,false);
end;
$$;

revoke all on function public.sun_member_role(uuid) from public, anon;
grant execute on function public.sun_member_role(uuid) to authenticated;
revoke all on function public.sun_has_permission(uuid,text) from public, anon;
grant execute on function public.sun_has_permission(uuid,text) to authenticated;
revoke all on function public.sun_role_default_permissions(text) from public, anon;
grant execute on function public.sun_role_default_permissions(text) to authenticated;

create or replace function public.sun_create_workspace(p_name text default 'Солнце Кейтеринг')
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_workspace uuid;
  v_name text;
begin
  if v_user is null then raise exception 'Authentication required'; end if;
  v_name := coalesce(nullif(trim(p_name),''),'Солнце Кейтеринг');
  insert into public.sun_workspaces(name, created_by)
  values (v_name, v_user)
  returning id into v_workspace;
  insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
  values (v_workspace,v_user,'admin',coalesce((select raw_user_meta_data->>'name' from auth.users where id=v_user),split_part(coalesce((select email from auth.users where id=v_user),'Администратор'),'@',1)),true,public.sun_role_default_permissions('admin'));
  insert into public.sun_app_state(workspace_id,payload,client_id)
  values (v_workspace,'{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,'bootstrap')
  on conflict (workspace_id) do nothing;
  return v_workspace;
end;
$$;
revoke all on function public.sun_create_workspace(text) from public, anon;
grant execute on function public.sun_create_workspace(text) to authenticated;

create or replace function public.sun_create_invite(p_workspace uuid, p_role text default 'manager')
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_token uuid;
  v_role text := lower(coalesce(p_role,'manager'));
begin
  if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
  if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
  insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by)
  values (p_workspace,v_role,public.sun_role_default_permissions(v_role),auth.uid())
  returning token into v_token;
  return v_token;
end;
$$;
revoke all on function public.sun_create_invite(uuid,text) from public, anon;
grant execute on function public.sun_create_invite(uuid,text) to authenticated;

create or replace function public.sun_accept_invite(p_token uuid)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_invite public.sun_workspace_invites%rowtype;
  v_display text;
begin
  if v_user is null then raise exception 'Authentication required'; end if;
  select * into v_invite from public.sun_workspace_invites where token=p_token for update;
  if not found then raise exception 'Invite not found'; end if;
  if v_invite.used_at is not null then raise exception 'Invite already used'; end if;
  if v_invite.expires_at < now() then raise exception 'Invite expired'; end if;
  select coalesce(nullif(raw_user_meta_data->>'name',''),split_part(coalesce(email,'Сотрудник'),'@',1)) into v_display from auth.users where id=v_user;
  insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
  values (v_invite.workspace_id,v_user,v_invite.role,v_display,true,coalesce(v_invite.permissions,public.sun_role_default_permissions(v_invite.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();
  update public.sun_workspace_invites set used_by=v_user,used_at=now() where token=p_token;
  return v_invite.workspace_id;
end;
$$;
revoke all on function public.sun_accept_invite(uuid) from public, anon;
grant execute on function public.sun_accept_invite(uuid) to authenticated;

create or replace function public.sun_list_workspace_members(p_workspace uuid)
returns table(user_id uuid,email text,display_name text,role text,is_active boolean,permissions jsonb,created_at timestamptz,updated_at timestamptz)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
  return query
  select m.user_id,u.email,m.display_name,m.role,m.is_active,
         coalesce(m.permissions,public.sun_role_default_permissions(m.role)),m.created_at,m.updated_at
  from public.sun_workspace_members m
  join auth.users u on u.id=m.user_id
  where m.workspace_id=p_workspace
  order by (m.role='admin') desc,coalesce(m.display_name,u.email);
end;
$$;
revoke all on function public.sun_list_workspace_members(uuid) from public, anon;
grant execute on function public.sun_list_workspace_members(uuid) to authenticated;

create or replace function public.sun_admin_update_member(
  p_workspace uuid,
  p_user uuid,
  p_display_name text,
  p_role text,
  p_is_active boolean,
  p_permissions jsonb
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_old_role text;
  v_old_active boolean;
  v_admins int;
  v_role text := lower(coalesce(p_role,''));
begin
  if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
  if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
  if p_permissions is null or jsonb_typeof(p_permissions) <> 'object' then raise exception 'Permissions must be an object'; end if;

  select role,is_active into v_old_role,v_old_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
  if not found then raise exception 'Member not found'; end if;

  if v_old_role='admin' and coalesce(v_old_active,false) and (v_role<>'admin' or not coalesce(p_is_active,false)) then
    select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
    if v_admins <= 1 then raise exception 'Нельзя отключить или понизить последнего администратора'; end if;
  end if;

  update public.sun_workspace_members
  set display_name=nullif(trim(coalesce(p_display_name,'')),''),role=v_role,is_active=coalesce(p_is_active,false),permissions=p_permissions,updated_at=now()
  where workspace_id=p_workspace and user_id=p_user;
end;
$$;
revoke all on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) from public, anon;
grant execute on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) to authenticated;

create or replace function public.sun_admin_remove_member(p_workspace uuid,p_user uuid)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_role text;
  v_active boolean;
  v_admins int;
begin
  if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
  select role,is_active into v_role,v_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
  if not found then return; end if;
  if v_role='admin' and coalesce(v_active,false) then
    select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
    if v_admins <= 1 then raise exception 'Нельзя удалить последнего администратора'; end if;
  end if;
  delete from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user;
end;
$$;
revoke all on function public.sun_admin_remove_member(uuid,uuid) from public, anon;
grant execute on function public.sun_admin_remove_member(uuid,uuid) to authenticated;

create or replace function public.sun_can_read_storage_key(p_workspace uuid,p_key text)
returns boolean
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if public.sun_member_role(p_workspace) is null then return false; end if;
  return case
    when p_key='sunOrders' then public.sun_has_permission(p_workspace,'orders.view') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'orders.edit')
    when p_key='sunBoxes' then public.sun_has_permission(p_workspace,'catalog.view') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'production.view') or public.sun_has_permission(p_workspace,'stock.view')
    when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then public.sun_has_permission(p_workspace,'clients.view') or public.sun_has_permission(p_workspace,'clients.edit') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'orders.edit')
    when p_key='sunFinanceRecordsV2' then public.sun_has_permission(p_workspace,'money.view') or public.sun_has_permission(p_workspace,'money.edit')
    when p_key in ('sunStock','sunStockMoves') then public.sun_has_permission(p_workspace,'stock.view') or public.sun_has_permission(p_workspace,'stock.edit') or public.sun_has_permission(p_workspace,'shopping.view') or public.sun_has_permission(p_workspace,'production.view')
    when p_key='sunEmployees' then public.sun_has_permission(p_workspace,'team.view') or public.sun_has_permission(p_workspace,'team.edit') or public.sun_has_permission(p_workspace,'production.view') or public.sun_has_permission(p_workspace,'routes.view')
    when p_key='sunSuppliers' then public.sun_has_permission(p_workspace,'suppliers.view') or public.sun_has_permission(p_workspace,'suppliers.edit') or public.sun_has_permission(p_workspace,'shopping.view')
    when p_key like 'sunRoute%' then public.sun_has_permission(p_workspace,'routes.view') or public.sun_has_permission(p_workspace,'routes.edit') or public.sun_has_permission(p_workspace,'map.view')
    when p_key like 'sunMarketing%' then public.sun_has_permission(p_workspace,'mailings.view') or public.sun_has_permission(p_workspace,'mailings.edit')
    when p_key='sunAuditLogV1' then public.sun_has_permission(p_workspace,'audit.view')
    else public.sun_has_permission(p_workspace,'app.read')
  end;
end;
$$;
revoke all on function public.sun_can_read_storage_key(uuid,text) from public, anon, authenticated;

create or replace function public.sun_required_write_permission(p_key text)
returns text
language sql
immutable
as $$
  select case
    when p_key='sunBoxes' then 'catalog.edit'
    when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients.edit'
    when p_key='sunFinanceRecordsV2' then 'money.edit'
    when p_key in ('sunStock','sunStockMoves') then 'stock.edit'
    when p_key='sunEmployees' then 'team.edit'
    when p_key='sunSuppliers' then 'suppliers.edit'
    when p_key like 'sunRoute%' then 'routes.edit'
    when p_key like 'sunMarketing%' then 'mailings.edit'
    when p_key in ('sunPromoCodesV1') then 'mailings.edit'
    when p_key in ('sunCatalogCategoriesV2','sunDefaultOrderQrV1','sunLeadSourcesV1','sunEventTypesV1','sunOrderPaymentColorsV1','sunReceiptSettingsV2','sunPrintSettingsV1','sunYandexMapsSettings','sunEnterpriseSettingsV1','sunOrderStatusFeatureEnabledV1') then 'settings.edit'
    when p_key='sunOfficialCatalogVersion' then 'catalog.edit'
    when p_key='sunAuditLogV1' then '_member'
    else 'settings.edit'
  end;
$$;
revoke all on function public.sun_required_write_permission(text) from public, anon, authenticated;

create or replace function public.sun_fetch_app_state(p_workspace uuid)
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
language plpgsql
stable
security definer
set search_path = public
as $$
declare
  v_row public.sun_app_state%rowtype;
  v_storage jsonb := '{}'::jsonb;
  kv record;
begin
  if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
  select * into v_row from public.sun_app_state where sun_app_state.workspace_id=p_workspace;
  if not found then return; end if;
  for kv in select key,value from jsonb_each(coalesce(v_row.payload->'storage','{}'::jsonb)) loop
    if public.sun_can_read_storage_key(p_workspace,kv.key) then
      v_storage := v_storage || jsonb_build_object(kv.key,kv.value);
    end if;
  end loop;
  workspace_id := v_row.workspace_id;
  payload := jsonb_build_object('format',coalesce(v_row.payload->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(v_row.payload->'version','2'::jsonb),'storage',v_storage);
  revision := v_row.revision;
  updated_at := v_row.updated_at;
  client_id := v_row.client_id;
  return next;
end;
$$;
revoke all on function public.sun_fetch_app_state(uuid) from public, anon;
grant execute on function public.sun_fetch_app_state(uuid) to authenticated;

create or replace function public.sun_save_app_state(p_workspace uuid,p_payload jsonb,p_client_id text)
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_old jsonb := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb;
  v_old_storage jsonb;
  v_new_storage jsonb;
  v_key text;
  v_perm text;
  v_old_orders jsonb;
  v_new_orders jsonb;
  v_create boolean := false;
  v_delete boolean := false;
  v_edit boolean := false;
  v_saved public.sun_app_state%rowtype;
begin
  if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
  if p_payload is null or jsonb_typeof(p_payload) <> 'object' or jsonb_typeof(coalesce(p_payload->'storage','{}'::jsonb)) <> 'object' then raise exception 'Invalid payload'; end if;

  select a.payload into v_old from public.sun_app_state a where a.workspace_id=p_workspace;
  if v_old is null then v_old := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb; end if;
  v_old_storage := coalesce(v_old->'storage','{}'::jsonb);
  v_new_storage := coalesce(p_payload->'storage','{}'::jsonb);

  for v_key in
    select key from (select jsonb_object_keys(v_old_storage) key union select jsonb_object_keys(v_new_storage) key) q
  loop
    if coalesce(v_old_storage->v_key,'null'::jsonb) = coalesce(v_new_storage->v_key,'null'::jsonb) then continue; end if;
    if v_key='sunOrders' then
      v_old_orders := coalesce(v_old_storage #> array['sunOrders','v'],'[]'::jsonb);
      v_new_orders := coalesce(v_new_storage #> array['sunOrders','v'],'[]'::jsonb);
      if jsonb_typeof(v_old_orders)<>'array' or jsonb_typeof(v_new_orders)<>'array' then raise exception 'Invalid orders payload'; end if;
      select exists(select 1 from jsonb_array_elements(v_new_orders) n where not exists(select 1 from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id')) into v_create;
      select exists(select 1 from jsonb_array_elements(v_old_orders) o where not exists(select 1 from jsonb_array_elements(v_new_orders) n where n->>'id'=o->>'id')) into v_delete;
      select exists(select 1 from jsonb_array_elements(v_new_orders) n join lateral (select o from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id' limit 1) x on true where x.o<>n) into v_edit;
      if v_create and not public.sun_has_permission(p_workspace,'orders.create') then raise exception 'Нет права создавать заказы'; end if;
      if v_edit and not public.sun_has_permission(p_workspace,'orders.edit') then raise exception 'Нет права изменять заказы'; end if;
      if v_delete and not public.sun_has_permission(p_workspace,'orders.delete') then raise exception 'Нет права удалять заказы'; end if;
    else
      v_perm := public.sun_required_write_permission(v_key);
      if v_perm='_member' then
        null;
      elsif not public.sun_has_permission(p_workspace,v_perm) then
        raise exception 'Нет права изменять раздел: %',v_key;
      end if;
    end if;
  end loop;

  insert into public.sun_app_state(workspace_id,payload,client_id)
  values (p_workspace,p_payload,p_client_id)
  on conflict (workspace_id) do update set payload=excluded.payload,client_id=excluded.client_id
  returning * into v_saved;

  insert into public.sun_sync_events(workspace_id,revision,client_id) values (p_workspace,v_saved.revision,p_client_id);

  return query select * from public.sun_fetch_app_state(p_workspace);
end;
$$;

create table if not exists public.sun_sync_events (
  id bigint generated by default as identity primary key,
  workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
  revision bigint not null,
  client_id text,
  created_at timestamptz not null default now()
);
create index if not exists sun_sync_events_workspace_idx on public.sun_sync_events(workspace_id,id desc);
alter table public.sun_sync_events enable row level security;
revoke all on public.sun_sync_events from anon;
grant select on public.sun_sync_events to authenticated;
drop policy if exists sun_sync_events_read on public.sun_sync_events;
create policy sun_sync_events_read on public.sun_sync_events for select to authenticated using (public.sun_member_role(workspace_id) is not null);

revoke all on function public.sun_save_app_state(uuid,jsonb,text) from public, anon;
grant execute on function public.sun_save_app_state(uuid,jsonb,text) to authenticated;

-- The app state itself is accessible only through permission-aware RPCs.
revoke select,insert,update,delete on public.sun_app_state from authenticated, anon;
drop policy if exists sun_state_read on public.sun_app_state;
drop policy if exists sun_state_insert on public.sun_app_state;
drop policy if exists sun_state_update on public.sun_app_state;

-- Members can read their own membership; administrators can read their workspace members.
drop policy if exists sun_members_read on public.sun_workspace_members;
create policy sun_members_read on public.sun_workspace_members for select to authenticated
using (user_id=auth.uid() or public.sun_has_permission(workspace_id,'users.manage'));

-- Workspaces are visible only to active members.
drop policy if exists sun_workspaces_read on public.sun_workspaces;
create policy sun_workspaces_read on public.sun_workspaces for select to authenticated
using (public.sun_member_role(id) is not null);

-- Invitations visible only to administrators.
drop policy if exists sun_invites_read on public.sun_workspace_invites;
create policy sun_invites_read on public.sun_workspace_invites for select to authenticated
using (public.sun_has_permission(workspace_id,'users.manage'));

-- Storage: active members may read, catalogue editors may upload/change media.
drop policy if exists sun_media_read on storage.objects;
create policy sun_media_read on storage.objects for select to authenticated
using (bucket_id='sun-media' and public.sun_member_role(((storage.foldername(name))[1])::uuid) is not null);
drop policy if exists sun_media_insert on storage.objects;
create policy sun_media_insert on storage.objects for insert to authenticated
with check (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));
drop policy if exists sun_media_update on storage.objects;
create policy sun_media_update on storage.objects for update to authenticated
using (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'))
with check (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));
drop policy if exists sun_media_delete on storage.objects;
create policy sun_media_delete on storage.objects for delete to authenticated
using (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));

-- Realtime events, not the full payload, are published.
do $$
begin
  if exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sun_app_state') then
    alter publication supabase_realtime drop table public.sun_app_state;
  end if;
  if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sun_sync_events') then
    alter publication supabase_realtime add table public.sun_sync_events;
  end if;
end $$;

-- Bootstrap the approved administrator account if it has no workspace yet.
do $$
declare
  v_user uuid;
  v_workspace uuid;
  v_display text;
begin
  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('dpavlov346@bk.ru') limit 1;
  if v_user is not null then
    update public.sun_workspace_members
       set role='admin',is_active=true,permissions=public.sun_role_default_permissions('admin'),updated_at=now()
     where user_id=v_user;
    if not exists(select 1 from public.sun_workspace_members where user_id=v_user) then
      insert into public.sun_workspaces(name,created_by) values ('Солнце Кейтеринг',v_user) returning id into v_workspace;
      insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
      values (v_workspace,v_user,'admin',v_display,true,public.sun_role_default_permissions('admin'));
      insert into public.sun_app_state(workspace_id,payload,client_id)
      values (v_workspace,'{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,'bootstrap')
      on conflict (workspace_id) do nothing;
    end if;
  end if;
end $$;

-- Normalize existing members' empty permissions to their role template.
update public.sun_workspace_members
set permissions=public.sun_role_default_permissions(role),updated_at=now()
where permissions='{}'::jsonb or permissions is null;

-- FINAL RUNTIME FIXES (keep at end of file)
create or replace function public.sun_list_workspace_members(p_workspace uuid)
returns table(user_id uuid,email text,display_name text,role text,is_active boolean,permissions jsonb,created_at timestamptz,updated_at timestamptz)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
  return query
  select m.user_id,u.email::text,m.display_name,m.role,m.is_active,
         coalesce(m.permissions,public.sun_role_default_permissions(m.role)),m.created_at,m.updated_at
  from public.sun_workspace_members m
  join auth.users u on u.id=m.user_id
  where m.workspace_id=p_workspace
  order by (m.role='admin') desc,coalesce(m.display_name,u.email::text);
end;
$$;
revoke all on function public.sun_list_workspace_members(uuid) from public, anon;
grant execute on function public.sun_list_workspace_members(uuid) to authenticated;

create or replace function public.sun_required_write_permission(p_key text)
returns text
language sql
immutable
set search_path = public
as $$
  select case
    when p_key='sunBoxes' then 'catalog.edit'
    when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients.edit'
    when p_key='sunFinanceRecordsV2' then 'money.edit'
    when p_key in ('sunStock','sunStockMoves') then 'stock.edit'
    when p_key='sunEmployees' then 'team.edit'
    when p_key='sunSuppliers' then 'suppliers.edit'
    when p_key like 'sunRoute%' then 'routes.edit'
    when p_key like 'sunMarketing%' then 'mailings.edit'
    when p_key in ('sunPromoCodesV1') then 'mailings.edit'
    when p_key in ('sunCatalogCategoriesV2','sunDefaultOrderQrV1','sunLeadSourcesV1','sunEventTypesV1','sunOrderPaymentColorsV1','sunReceiptSettingsV2','sunPrintSettingsV1','sunYandexMapsSettings','sunEnterpriseSettingsV1','sunOrderStatusFeatureEnabledV1') then 'settings.edit'
    when p_key='sunOfficialCatalogVersion' then 'catalog.edit'
    when p_key='sunAuditLogV1' then '_member'
    else 'settings.edit'
  end;
$$;

create or replace function public.sun_save_app_state(p_workspace uuid,p_payload jsonb,p_client_id text)
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_old jsonb := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb;
  v_old_storage jsonb;
  v_new_storage jsonb;
  v_final_storage jsonb;
  v_final_payload jsonb;
  v_key text;
  v_perm text;
  v_old_orders jsonb;
  v_new_orders jsonb;
  v_create boolean;
  v_delete boolean;
  v_edit boolean;
  v_saved public.sun_app_state%rowtype;
begin
  if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
  if p_payload is null or jsonb_typeof(p_payload) <> 'object' or jsonb_typeof(coalesce(p_payload->'storage','{}'::jsonb)) <> 'object' then raise exception 'Invalid payload'; end if;
  select a.payload into v_old from public.sun_app_state a where a.workspace_id=p_workspace;
  if v_old is null then v_old := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb; end if;
  v_old_storage := coalesce(v_old->'storage','{}'::jsonb);
  v_new_storage := coalesce(p_payload->'storage','{}'::jsonb);
  v_final_storage := v_old_storage;
  for v_key in select key from (select jsonb_object_keys(v_old_storage) key union select jsonb_object_keys(v_new_storage) key) q loop
    if not public.sun_can_read_storage_key(p_workspace,v_key) and not (v_new_storage ? v_key) then continue; end if;
    if coalesce(v_old_storage->v_key,'null'::jsonb) = coalesce(v_new_storage->v_key,'null'::jsonb) then continue; end if;
    if v_key='sunOrders' then
      v_old_orders := coalesce(v_old_storage #> array['sunOrders','v'],'[]'::jsonb);
      v_new_orders := coalesce(v_new_storage #> array['sunOrders','v'],'[]'::jsonb);
      if jsonb_typeof(v_old_orders)<>'array' or jsonb_typeof(v_new_orders)<>'array' then raise exception 'Invalid orders payload'; end if;
      select exists(select 1 from jsonb_array_elements(v_new_orders) n where not exists(select 1 from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id')) into v_create;
      select exists(select 1 from jsonb_array_elements(v_old_orders) o where not exists(select 1 from jsonb_array_elements(v_new_orders) n where n->>'id'=o->>'id')) into v_delete;
      select exists(select 1 from jsonb_array_elements(v_new_orders) n join lateral (select o from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id' limit 1) x on true where x.o<>n) into v_edit;
      if v_create and not public.sun_has_permission(p_workspace,'orders.create') then raise exception 'Нет права создавать заказы'; end if;
      if v_edit and not public.sun_has_permission(p_workspace,'orders.edit') then raise exception 'Нет права изменять заказы'; end if;
      if v_delete and not public.sun_has_permission(p_workspace,'orders.delete') then raise exception 'Нет права удалять заказы'; end if;
    else
      v_perm := public.sun_required_write_permission(v_key);
      if v_perm='_member' then null;
      elsif not public.sun_has_permission(p_workspace,v_perm) then raise exception 'Нет права изменять раздел: %',v_key;
      end if;
    end if;
    if v_new_storage ? v_key then v_final_storage := v_final_storage || jsonb_build_object(v_key,v_new_storage->v_key);
    else v_final_storage := v_final_storage - v_key;
    end if;
  end loop;
  v_final_payload := jsonb_build_object('format',coalesce(p_payload->'format',v_old->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(p_payload->'version',v_old->'version','2'::jsonb),'storage',v_final_storage);
  insert into public.sun_app_state as app_state(workspace_id,payload,client_id)
  values (p_workspace,v_final_payload,p_client_id)
  on conflict on constraint sun_app_state_pkey do update set payload=excluded.payload,client_id=excluded.client_id
  returning app_state.* into v_saved;
  insert into public.sun_sync_events(workspace_id,revision,client_id) values (p_workspace,v_saved.revision,p_client_id);
  return query select * from public.sun_fetch_app_state(p_workspace);
end;
$$;
revoke all on function public.sun_save_app_state(uuid,jsonb,text) from public, anon;
grant execute on function public.sun_save_app_state(uuid,jsonb,text) to authenticated;