-- Run after 002_cloud_state.sql. Existing CRM records are preserved.
begin;
alter table public.atila_memberships add column if not exists active boolean not null default true;
alter table public.atila_memberships add column if not exists display_name text not null default '';
alter table public.atila_memberships add column if not exists revision integer not null default 1;

create or replace function public.atila_is_member(target text)
returns boolean language sql stable security definer set search_path='' as $$
  select exists(select 1 from public.atila_memberships m
    where m.workspace_id::text=target and m.user_id=(select auth.uid()) and m.active);
$$;
-- A disabled user may see their own membership status, but no CRM data.
drop policy if exists atila_workspace_read on public.atila_memberships;
create policy atila_workspace_read on public.atila_memberships for select to authenticated
  using(user_id=(select auth.uid()) or public.atila_is_member(workspace_id::text));

create or replace function public.atila_bootstrap_workspace(workspace_name text default 'Atila CRM')
returns uuid language plpgsql security definer set search_path='' as $$
declare member public.atila_memberships%rowtype;
begin
  if auth.uid() is null then raise exception 'Sign in first' using errcode='42501'; end if;
  select * into member from public.atila_memberships where user_id=auth.uid() order by workspace_id limit 1;
  if member.user_id is null or not member.active then
    raise exception 'Ask your administrator for access' using errcode='42501';
  end if;
  return member.workspace_id;
end;
$$;

create or replace function public.atila_list_users(target_workspace uuid, actor uuid)
returns table(user_id uuid,email text,display_name text,role text,active boolean,revision integer,last_sign_in_at timestamptz)
language plpgsql security definer set search_path='' as $$
begin
  if (select auth.role()) is distinct from 'service_role' or not exists(
    select 1 from public.atila_memberships m where m.workspace_id=target_workspace
      and m.user_id=actor and m.active and m.role='super_admin'
  ) then raise exception 'Only active super admin' using errcode='42501'; end if;
  return query select m.user_id,u.email::text,m.display_name,m.role,m.active,m.revision,u.last_sign_in_at
    from public.atila_memberships m join auth.users u on u.id=m.user_id
    where m.workspace_id=target_workspace order by lower(u.email);
end;
$$;

create or replace function public.atila_save_user(
  target_workspace uuid, actor uuid, target_email text, member_name text,
  member_role text, is_active boolean, expected_revision integer default 0
) returns jsonb language plpgsql security definer set search_path='' as $$
declare target uuid; previous public.atila_memberships%rowtype; new_revision integer;
begin
  if (select auth.role()) is distinct from 'service_role' then raise exception 'Server only' using errcode='42501'; end if;
  perform 1 from public.atila_workspaces where id=target_workspace for update;
  if not exists(select 1 from public.atila_memberships m where m.workspace_id=target_workspace
    and m.user_id=actor and m.active and m.role='super_admin') then
    raise exception 'Only active super admin' using errcode='42501';
  end if;
  if member_role is null or member_role not in ('super_admin','manager') or is_active is null
    or member_name is null or length(trim(member_name)) not between 1 and 100
    or target_email is null or length(target_email)>254 or expected_revision is null or expected_revision<0 then
    raise exception 'Invalid user details' using errcode='22023';
  end if;
  select id into target from auth.users where lower(email)=lower(trim(target_email));
  if target is null then raise exception 'User does not exist' using errcode='P0002'; end if;
  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended(target::text,1));
  if target=actor and (not is_active or member_role<>'super_admin') then
    raise exception 'Cannot disable or demote yourself' using errcode='42501';
  end if;
  -- This UI manages one workspace per account; never silently choose another.
  if exists(select 1 from public.atila_memberships where user_id=target and workspace_id<>target_workspace) then
    raise exception 'User already belongs to another workspace' using errcode='23505';
  end if;
  select * into previous from public.atila_memberships where workspace_id=target_workspace and user_id=target;
  if expected_revision=0 and previous.user_id is not null then
    if previous.display_name=trim(member_name) and previous.role=member_role and previous.active=is_active then
      return jsonb_build_object('user_id',target,'replayed',true);
    end if;
    raise exception 'User already added; reload list' using errcode='40001';
  end if;
  if expected_revision<>coalesce(previous.revision,0) then
    raise exception 'User changed; reload list' using errcode='40001';
  end if;
  new_revision:=coalesce(previous.revision,0)+1;
  insert into public.atila_memberships(workspace_id,user_id,role,active,display_name,revision)
    values(target_workspace,target,member_role,is_active,trim(member_name),new_revision)
    on conflict(workspace_id,user_id) do update set role=excluded.role,active=excluded.active,
      display_name=excluded.display_name,revision=excluded.revision;
  insert into public.atila_audit_log(workspace_id,actor_id,action,before_data,after_data)
    values(target_workspace,actor,'user_access',
      case when previous.user_id is null then null else jsonb_build_object('user_id',target,'role',previous.role,'active',previous.active,'name',previous.display_name) end,
      jsonb_build_object('user_id',target,'role',member_role,'active',is_active,'name',trim(member_name),'revision',new_revision));
  return jsonb_build_object('user_id',target,'revision',new_revision);
end;
$$;
revoke all on function public.atila_list_users(uuid,uuid) from public,anon,authenticated;
revoke all on function public.atila_save_user(uuid,uuid,text,text,text,boolean,integer) from public,anon,authenticated;
grant execute on function public.atila_list_users(uuid,uuid) to service_role;
grant execute on function public.atila_save_user(uuid,uuid,text,text,text,boolean,integer) to service_role;

-- Recheck active membership on every state write, even if a request was already
-- in flight when access was disabled. The same workspace lock orders both writes.
create or replace function public.atila_check_state_actor()
returns trigger language plpgsql security definer set search_path='' as $$
begin
  if not exists(select 1 from public.atila_memberships m where m.workspace_id=new.workspace_id
    and m.user_id=new.updated_by and m.active) then
    raise exception 'Access disabled' using errcode='42501';
  end if;
  return new;
end;
$$;
revoke all on function public.atila_check_state_actor() from public,anon,authenticated;
drop trigger if exists atila_state_actor on public.atila_workspace_states;
create trigger atila_state_actor before insert or update on public.atila_workspace_states
  for each row execute function public.atila_check_state_actor();
update public.atila_installation set version=greatest(version,3) where singleton;
notify pgrst,'reload schema';
commit;