-- Sun Catering SaaS v16 finalization
-- Run after SUPABASE-SAAS-V16.sql on a fresh project.

create or replace function public.sun_feature_for_read_storage_key(p_key text)
returns text
language sql
immutable
set search_path='public'
as $$
  select case
    when p_key='sunOrders' then 'orders'
    when p_key in ('sunBoxes','sunCatalogCategoriesV2','sunOfficialCatalogVersion') then 'catalog_view'
    when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients'
    when p_key='sunFinanceRecordsV2' then 'money'
    when p_key in ('sunStock','sunStockMoves') then 'stock'
    when p_key='sunEmployees' then 'team'
    when p_key='sunSuppliers' then 'suppliers'
    when p_key like 'sunRoute%' then 'routes'
    when p_key like 'sunMarketing%' or p_key='sunPromoCodesV1' then 'mailings'
    when p_key='sunBrandThemeV1' then 'branding'
    when p_key='sunClientOfferSettingsV1' then 'client_offers'
    when p_key='sunOfferTemplateV1' then 'offer_templates'
    when p_key='sunAuditLogV1' then 'audit'
    else 'settings'
  end;
$$;

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;
  v_feature text;
begin
  if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
  if public.sun_subscription_access_mode(p_workspace)='blocked' then raise exception 'Подписка закончилась. Данные сохранены, продлите подписку для доступа.'; 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
    v_feature := public.sun_feature_for_read_storage_key(kv.key);
    if public.sun_can_read_storage_key(p_workspace,kv.key)
       and (v_feature is null or public.sun_workspace_has_feature(p_workspace,v_feature)) 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;
$$;

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,''));
  v_max integer;
  v_active_count integer;
begin
  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 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;
  if coalesce(p_is_active,false) and not coalesce(v_old_active,false) then
    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_active_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
    if v_max is not null and v_active_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; 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;
$$;

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 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 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;
$$;

-- New SaaS helper RPCs are authenticated-only.
revoke execute on function public.sun_is_platform_admin() from public, anon;
revoke execute on function public.sun_subscription_access_mode(uuid) from public, anon;
revoke execute on function public.sun_workspace_has_feature(uuid,text) from public, anon;
revoke execute on function public.sun_subscription_snapshot(uuid) from public, anon;
revoke execute on function public.sun_platform_list_workspaces() from public, anon;
revoke execute on function public.sun_platform_set_subscription(uuid,text,integer,text) from public, anon;
revoke execute on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) from public, anon;

grant execute on function public.sun_is_platform_admin() to authenticated;
grant execute on function public.sun_subscription_access_mode(uuid) to authenticated;
grant execute on function public.sun_workspace_has_feature(uuid,text) to authenticated;
grant execute on function public.sun_subscription_snapshot(uuid) to authenticated;
grant execute on function public.sun_platform_list_workspaces() to authenticated;
grant execute on function public.sun_platform_set_subscription(uuid,text,integer,text) to authenticated;
grant execute on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) to authenticated;